How to Read Data of an Excel File Using C#

How to read data from excel file using c#

There is the option to use OleDB and use the Excel sheets like datatables in a database...

Just an example.....

string con =
  @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\temp\test.xls;" + 
  @"Extended Properties='Excel 8.0;HDR=Yes;'";    
using(OleDbConnection connection = new OleDbConnection(con))
{
    connection.Open();
    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection); 
    using(OleDbDataReader dr = command.ExecuteReader())
    {
         while(dr.Read())
         {
             var row1Col0 = dr[0];
             Console.WriteLine(row1Col0);
         }
    }
}

This example use the Microsoft.Jet.OleDb.4.0 provider to open and read the Excel file. However, if the file is of type xlsx (from Excel 2007 and later), then you need to download the Microsoft Access Database Engine components and install it on the target machine.

The provider is called Microsoft.ACE.OLEDB.12.0;. Pay attention to the fact that there are two versions of this component, one for 32bit and one for 64bit. Choose the appropriate one for the bitness of your application and what Office version is installed (if any). There are a lot of quirks to have that driver correctly working for your application. See this question for example.

Of course you don't need Office installed on the target machine.

While this approach has some merits, I think you should pay particular attention to the link signaled by a comment in your question Reading excel files from C#. There are some problems regarding the correct interpretation of the data types and when the length of data, present in a single excel cell, is longer than 255 characters

Read excel file with c#

You can use interop but that requires that Excel is installed on the computer that runs the tool.

You have to reference Office or just Excel and then use these usings.

using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel; 

Then you can read and write to it.

        Excel.Application xlsApp = new Excel.Application();
        Excel._Workbook wrk = xlsApp.Workbooks.Open(@"C:\test.xlsx", 0, true, 5, Missing.Value, Missing.Value, true, Excel.XlPlatform.xlWindows, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

        int j=1;
        while (j < 100) {
            xlsApp.Cells[j, 1] = j;
            j = j + 1;
        }
        xlsApp.Visible = true;

Or you can use EPPlus. This is what i use. http://epplus.codeplex.com/ It is free and you don't need excel on a workstation to use it.

Download it. Reference the dll. Use these usings

using OfficeOpenXml;
using OfficeOpenXml.Style;

Read from a cell.

            FileInfo AddressList = new FileInfo("c:\temp\test.xlsx");
            using (ExcelPackage package = new ExcelPackage(AddressList))
            {
                // Get the work book in the file
                ExcelWorkbook workBook = package.Workbook;
                if (workBook != null)
                {
                    if (workBook.Worksheets.Count > 0)
                    {

                        // Get the first worksheet
                        //ExcelWorksheet Worksheet = workBook.Worksheets.First();
                        var worksheet = package.Workbook.Worksheets[1];

                        if (worksheet.Cells["A1"].Value.ToString() != "Address")
                        {
                            MessageBox.Show("The cell A1 should say Address. Aborting.");
                            return;
                        }
                        // This is a safe way to make sure a null cell will not cause you an error.
                        string callValue = worksheet.Cells["E2"].Value == null ? string.Empty : worksheet.Cells["E2"].Value.ToString();
                        if (string.IsNullOrEmpty(strTerminal.Trim()) == false)
                        {
                            MessageBox.Show(callValue.ToString());
                        }
                    }
                }
                package.Dispose();
            }

Or write to it such as.

            FileInfo AddressList = new FileInfo("c:\\temp\\test.xlsx");
            using (ExcelPackage package = new ExcelPackage(AddressList))
            {
                // Get the work book in the file
                ExcelWorkbook workBook = package.Workbook;
                if (workBook != null)
                {
                    if (workBook.Worksheets.Count > 0)
                    {
                        // Get the first worksheet
                        var worksheet = package.Workbook.Worksheets[1];

                        worksheet.Cells["D2"].Value = "Some other string";
                        worksheet.Cells["E2"].Value = "Some string";
                    }
                }
                try
                {
                    package.Save();
                }
                catch (Exception ex)
                {
                    //MessageBox.Show("Error saving the spreadsheet.     " + ex);
                    MessageBox.Show("Error saving the spreadsheet.  Do you have it open?");
                    return;
                }
            }

I usually create an adodb recordset and store the data i need there as i read it from the spreadsheet. Then it can be used in any manor to analyse, show to a user, or output in a format you need.

To do that you can add a reference to adodb. Add using

using ADODB;

Depending on the scope of your code, declare a recordset

private ADODB.Recordset rsAddress = new ADODB.Recordset();

Again depending on your scope, in the appropriate place build the recordset fields.

rsAddress.Fields.Append("Row", DataTypeEnum.adInteger);
rsAddress.Fields.Append("Address", DataTypeEnum.adVarChar, 75);
rsAddress.Fields.Append("CustomerNumber", DataTypeEnum.adVarChar, 75);
rsAddress.Open();

Then as you read values from Excel you can add records(rows) to your recordset. Here is some code where i am looping through the "used range" of a spreadsheet and saving the data to a recordset.

//Find the "real" last used row.
var rowRun = worksheet.Dimension.End.Row;
while (rowRun >= 1)
{
    var range = worksheet.Cells[rowRun, 1, rowRun, worksheet.Dimension.End.Column];
    if (range.Any(c => !string.IsNullOrEmpty(c.Text)))
    {
        break;
    }
    rowRun--;
}

// Loop through the worksheet and record the values we need.
//var start = worksheet.Dimension.Start;
for (int row = 2; row <= rowRun; row++)
{
    //Check if we already have the current address
    string strHouseAddress = worksheet.Cells["A" + row.ToString()].Value == null ? string.Empty : worksheet.Cells["A" + row.ToString()].Value.ToString();
    rsAddress.Filter = "";
    rsAddress.Filter = "Address='" + strHouseAddress.Trim() + "'";
    if (rsAddress.RecordCount == 0)
    {
        //Record this address
        rsAddress.Filter = "";
        rsAddress.AddNew();
        rsAddress.Fields["Row"].Value = row;
        try
        {
            if (string.IsNullOrEmpty(strHouseAddress.Trim()) == false)
            {
                rsAddress.Fields["Address"].Value = strHouseAddress.Trim();
            }
            else
            {
                rsAddress.Fields["Address"].Value = "0 MISSING ST";
                MessageBox.Show("Missing address at row " + row.ToString() + ".  Fix the spreadsheet and reload.");
            }

            string strTerminal = worksheet.Cells["E" + row.ToString()].Value == null ? string.Empty : worksheet.Cells["E" + row.ToString()].Value.ToString();
            if (string.IsNullOrEmpty(strTerminal.Trim()) == false)
            {
                rsAddress.Fields["CustomerNumber"].Value = strTerminal.Trim();
            }

            rsAddress.Update();
        }
        catch
        {
            MessageBox.Show("Error reading data from column A on row " + row.ToString());
        }

    }
    else
    {
        MessageBox.Show("Duplicate address found on the Address list and row " + row.ToString() + ".");
    }
}

You can then loop through the records you created.

rsAddress.MoveFirst();
for (; !rsAddress.EOF; rsAddress.MoveNext())
{
    if ( rsAddress.Fields["CustomerNumber"].Value = "SomeValue"){
        //Do something
    }
}

Get Excel data in DataTable

While the JET/Access driver can read Excel files as if they were databases, it has several problems, especially in the .NET Core era:

1. It's Windows-only
2. It needs to be installed. It can't be packaged with your application
3. The installed version must match the bitness (32/64-bit) of any Office components. This in turn means that your application must match Office's bitness.

There are libraries that can read Excel files directly. One such option is ExcelDataReader, which opens a DbDataReader over an Excel sheet. It can handle both the obsolete xls format and the 16 year old xlsx format (yes, the "new" xlsx format was introduce in 2006, 16 years ago).

The generated data reader can be used to read the data or load a DataTable the same as any other data reader.

using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
    using (var reader = ExcelReaderFactory.CreateReader(stream))
    {
        var table=new DataTable();
        table.Load(reader);
        ...
    }
}

ExcelDataReader has an extension that allows reading all sheets in a workbook into a DataSet, with one DataTable for each sheet.

using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
    using (var reader = ExcelReaderFactory.CreateReader(stream))
    {
        var dataset= reader.AsDataSet();

        // The result of each spreadsheet is in dataset.Tables
    }
}

---

Related Topics