app3ya
Last Updated: November 12, 2018
·
383.9K
· teddy

Read Excel File in C#

.NET 4+ allows C# to read and manipulate Microsoft Excel files, for computers that have Excel installed (if you do not have Excel installed, see NPOI).

First, add the reference to Microsoft Excel XX.X Object Library, located in the COM tab of the Reference Manager. I have given this the using alias of Excel.

using Excel = Microsoft.Office.Interop.Excel;       //Microsoft Excel 14 object in references-> COM tab

Next, you'll need to create references for each COM object that is accessed. Each reference must be kept to effectively exit the application on completion.

//Create COM Objects. Create a COM object for everything that is referenced
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"sandbox_test.xlsx");
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;

Then you can read from the sheet, keeping in mind that indexing in Excel is not 0 based. This just reads the cells and prints them back just as they were in the file.

//iterate over the rows and columns and print to the console as it appears in the file
//excel is not zero based!!
for (int i = 1; i <= rowCount; i++)
{
    for (int j = 1; j <= colCount; j++)
    {
        //new line
        if (j == 1)
            Console.Write("\r\n");

        //write the value to the console
        if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
            Console.Write(xlRange.Cells[i, j].Value2.ToString() + "\t");

        //add useful things here!   
    }
}

Lastly, the references to the unmanaged memory must be released. If this is not properly done, then there will be lingering processes that will hold the file access writes to your Excel workbook.

//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();

//rule of thumb for releasing com objects:
//  never use two dots, all COM objects must be referenced and released individually
//  ex: [somthing].[something].[something] is bad

//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);

//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);

//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);

Further reading:

StackOverflow

Interop Marshaling

Excel and C#

Full Code:

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;           
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;       //microsoft Excel 14 object in references-> COM tab

namespace Sandbox
{
    public class Read_From_Excel
    {
        public static void getExcelFile()
        {

            //Create COM Objects. Create a COM object for everything that is referenced
            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\E56626\Desktop\Teddy\VS2012\Sandbox\sandbox_test - Copy - Copy.xlsx");
            Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
            Excel.Range xlRange = xlWorksheet.UsedRange;

            int rowCount = xlRange.Rows.Count;
            int colCount = xlRange.Columns.Count;

            //iterate over the rows and columns and print to the console as it appears in the file
            //excel is not zero based!!
            for (int i = 1; i <= rowCount; i++)
            {
                for (int j = 1; j <= colCount; j++)
                {
                    //new line
                    if (j == 1)
                        Console.Write("\r\n");

                    //write the value to the console
                    if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
                        Console.Write(xlRange.Cells[i, j].Value2.ToString() + "\t");
                }
            }

            //cleanup
            GC.Collect();
            GC.WaitForPendingFinalizers();

            //rule of thumb for releasing com objects:
            //  never use two dots, all COM objects must be referenced and released individually
            //  ex: [somthing].[something].[something] is bad

            //release com objects to fully kill excel process from running in the background
            Marshal.ReleaseComObject(xlRange);
            Marshal.ReleaseComObject(xlWorksheet);

            //close and release
            xlWorkbook.Close();
            Marshal.ReleaseComObject(xlWorkbook);

            //quit and release
            xlApp.Quit();
            Marshal.ReleaseComObject(xlApp);
        }
    }
}   

12 Responses
Add your response

12735

Thank you!!

over 1 year ago ·
13255

There is another C# Library that allows you to read excel file in C#/.NET and you don't have to install excel for this, This API is known as Aspose.Cells for .NET. Try it, you can find code samples for many excel features in this library.

http://www.aspose.com/.net/excel-component.aspx

over 1 year ago ·
28747

Hi!

=> For my Windows WPF Application I copied your code and get following exception
"COM object that has been separated from its underlying RCW cannot be used."
during " xlWorkbook.Close();" call.
=> For my Windows Console Application it works,.
I am Using VS2015, Excel 14 Object Library v14.

Best regards,
Otmar

over 1 year ago ·
28779

Error CS0234 The type or namespace name 'Application' does not exist in the namespace 'Excel' (are you missing an assembly reference?) MyTestdata

This is with the exact same reference and using directive you specify here. It simply doesn't work like that.

over 1 year ago ·
29032

Thank you!! It is really helpful........I 'll be grateful if you can also share the code to read the excel file on other remote pc ( not local pc), for which IP, username ans passwords are known.
Thanks in advance !!

over 1 year ago ·
29041

Great post, I'd just suggest xlWorkbook.Close(0); to avoid waiting for ever for it to invisibly prompt you for the filename to save as.

over 1 year ago ·
29174

Thank you for this.

I have some additional question, how about for the import to db the excel that been read by the app?

over 1 year ago ·
29485

nice to share the code... but there is huge mistake in it:

// never use two dots, all COM objects must be referenced and released individually

and yet, you do:

//Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\MyFile.xlsx");

while it should be:
Excel.WorkbookS xlWorkbookS = xlApp.Workbooks
Excel.Workbook xlWorkbook = xlWorkbookS.Open(@"C:\MyFile.xlsx");

and later in GC() you should have BOTH
Marshal.ReleaseComObject(xlWorkbook);
Marshal.ReleaseComObject(xlWorkbookS);

12 months ago ·
29497

Thanks , I have been used many approaches but, only this has helped...

12 months ago ·
30388

I would suggest you to try ZetExcel as it is really helpful.

4 months ago ·
31204

you can also do it without using interop in C#, using Oledb

//check if file exists
if (FileUpload1.HasFile)
{
string filePath = string.Empty;

    string path = Server.MapPath("~/Uploads/");
    if (!Directory.Exists(path))
    {
        Directory.CreateDirectory(path);
    }
    //get file path
    filePath = path + Path.GetFileName(FileUpload1.FileName);
    //get file extenstion
    string extension = Path.GetExtension(FileUpload1.FileName);
    //save file on "Uploads" folder of project
    FileUpload1.SaveAs(filePath);

    string conString = string.Empty;
    //check file extension
    switch (extension)
    {
        case ".xls": //Excel 97-03.
            conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Excel03ConString;Extended Properties='Excel 8.0;HDR=YES'";
            break;
        case ".xlsx": //Excel 07 and above.
            conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Excel07ConString;Extended Properties='Excel 8.0;HDR=YES'";
            break;
    }

    //create datatable object
    DataTable dt = new DataTable();
    conString = string.Format(conString, filePath);

    //Use OldDb to read excel
    using (OleDbConnection connExcel = new OleDbConnection(conString))
    {
        using (OleDbCommand cmdExcel = new OleDbCommand())
        {
            using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
            {
                cmdExcel.Connection = connExcel;

                //Get the name of First Sheet.
                connExcel.Open();
                DataTable dtExcelSchema;
                dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                connExcel.Close();

                //Read Data from First Sheet.
                connExcel.Open();
                cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
                odaExcel.SelectCommand = cmdExcel;
                odaExcel.Fill(dt);
                connExcel.Close();
            }
        }
    }

    //bind datatable with GridView
    GridView1.DataSource = dt;
    GridView1.DataBind();

}

Source : https://qawithexperts.com/article/asp-net/read-excel-file-and-import-data-into-gridview-using-datatabl/209

28 days ago ·
31207

Hello Guys try ZetExcel.com

28 days ago ·