Last Updated: December 18, 2022
·
1.037M
· 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);
        }
    }
}   

10 Responses
Add your response

Thank you!!

over 1 year ago ·

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 ·

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 ·

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 ·

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 ·

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 ·

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);

over 1 year ago ·

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

over 1 year ago ·

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

over 1 year ago ·

There is another C# Library that allows to read Excel files in C#/VB.NET, where you no need to install MS Excel. This API is known as Syncfusion Excel (XlsIO) library for .NET. You can try with number of code samples for many Excel features in this library.

https://www.syncfusion.com/excel-framework/net

over 1 year ago ·