Referencing an Excel file workbook worksheet in C

0 votes

I have been finding it difficult to simply refer to an Excel file so that I may immediately copy its contents into a new worksheet.

I understand that this refers to creating a new Excel file and its contents:

        Excel.Application oXL;
        Excel._Workbook oWB;
        Excel._Worksheet oSheet;
        Excel.Range oRng;



            //Start Excel and get Application object.
            oXL = new Excel.Application();
            oXL.Visible = true;
            //Get a new workbook.
            oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
            oSheet = (Excel._Worksheet)oWB.ActiveSheet;

However, this is where I become lost.

I want to replicate data from another Excel file into the new worksheet above when the user chooses it through a file dialogue box.

Nov 19 in Others by Kithuzzz
• 20,660 points
22 views

1 answer to this question.

0 votes

Try the following code.

using System;
using System.Data;
using Excel = Microsoft.Office.Interop.Excel;


    class Program
    {
        static void Main(string[] args)
        {
            string path = "D:\\t.csv";
            DataTable table = ReadCsv(path);
            table= new DataView(table).ToTable(false, "Tests");
            ExportToExcel(table, "D:\\t.xlsx");
        }
        public static DataTable ReadCsv(string path)
        {
            Microsoft.Office.Interop.Excel.Application objXL = null;
            Microsoft.Office.Interop.Excel.Workbook objWB = null;
            objXL = new Microsoft.Office.Interop.Excel.Application();
            objWB = objXL.Workbooks.Open(path);
            Microsoft.Office.Interop.Excel.Worksheet objSHT = objWB.Worksheets[1];

            int rows = objSHT.UsedRange.Rows.Count;
            int cols = objSHT.UsedRange.Columns.Count;
            DataTable dt = new DataTable();
            int noofrow = 1;

            for (int c = 1; c <= cols; c++)
            {
                string colname = objSHT.Cells[1, c].Text;
                dt.Columns.Add(colname);
                noofrow = 2;
            }

            for (int r = noofrow; r <= rows; r++)
            {
                DataRow dr = dt.NewRow();
                for (int c = 1; c <= cols; c++)
                {
                    dr[c - 1] = objSHT.Cells[r, c].Text;
                }

                dt.Rows.Add(dr);
            }

            objWB.Close();
            objXL.Quit();
            return dt;
        }

        public static void ExportToExcel( DataTable tbl, string excelFilePath = null)
        {
            try
            {
                if (tbl == null || tbl.Columns.Count == 0)
                    throw new Exception("ExportToExcel: Null or empty input table!\n");

                // load excel, and create a new workbook
                var excelApp = new Excel.Application();
                excelApp.Workbooks.Add();

                // single worksheet
                Excel._Worksheet workSheet = excelApp.ActiveSheet;

                // column headings
                for (var i = 0; i < tbl.Columns.Count; i++)
                {
                    workSheet.Cells[1, i + 1] = tbl.Columns[i].ColumnName;
                }

                // rows
                for (var i = 0; i < tbl.Rows.Count; i++)
                {
                    // to do: format datetime values before printing
                    for (var j = 0; j < tbl.Columns.Count; j++)
                    {
                        workSheet.Cells[i + 2, j + 1] = tbl.Rows[i][j];
                    }
                }

                // check file path
                if (!string.IsNullOrEmpty(excelFilePath))
                {
                    try
                    {
                        workSheet.SaveAs(excelFilePath);
                        excelApp.Quit();
                        Console.WriteLine("Excel file saved!");
                    }
                    catch (Exception ex)
                    {
                        throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
                                            + ex.Message);
                    }
                }
                else
                { // no file path is given
                    excelApp.Visible = true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception("ExportToExcel: \n" + ex.Message);
            }
        }
    }

I hope this helps you.

answered Nov 19 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

How to open an Excel file in C#?

You must have Microsoft Visual Studio Tools ...READ MORE

answered Nov 13 in Others by narikkadan
• 37,660 points
43 views
0 votes
1 answer

Removing specific rows in an Excel file using Azure Data Factory

Under the 'Source' tab, choose the number ...READ MORE

answered Sep 23 in Others by narikkadan
• 37,660 points
219 views
0 votes
1 answer

Create Graph from data in an excel file

Your first step would be to become ...READ MORE

answered Oct 17 in Others by narikkadan
• 37,660 points
40 views
0 votes
1 answer
0 votes
1 answer

How to install C#

Hi @Akanksha, C# is a programming language ...READ MORE

answered Feb 28, 2019 in Others by Pratibha
• 3,690 points
290 views
0 votes
1 answer

How to run C# code on Ubuntu

Hey @Abha, you can create and run ...READ MORE

answered Mar 1, 2019 in Others by Anvi
• 14,170 points
5,306 views
+1 vote
2 answers

RPA C# - Process developments in C #

Hey Conell, you can use Visual Studio's ...READ MORE

answered May 24, 2019 in RPA by Abha
• 28,120 points
980 views
0 votes
1 answer

How do I protect all worksheet in an Excel workbook with a single click?

VBA Code : Dim ws as Worksheet Dim pwd ...READ MORE

answered Oct 22 in Others by narikkadan
• 37,660 points
33 views
0 votes
1 answer

Password Protecting an Excel file in C#

Try this: using Microsoft.Office.Interop.Excel //create your spreadsheet here... WorkbookObject.Password = ...READ MORE

answered Oct 30 in Others by narikkadan
• 37,660 points
51 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP