app3ya
Last Updated: August 07, 2017
·
193.1K
· teddy
Face2

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);
        }
    }
}   
Say Thanks
Respond

7 Responses
Add your response

12735
3e872142e5ef10a235b3cd2c64b88f00

Thank you!!

over 1 year ago ·
13255
A11e43606d3004599d9dd80452494608 normal

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

6 months 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.

6 months 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 !!

3 months 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.

3 months 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?

28 days ago ·