How to Loop Through an Excel Spreadsheet in a .NET Application

MS Excel is a great tool that a lot of employees at a company are very familiar with using. It helps structure and organize data in a way that is very intuitive to users without the users needing to be especially technical. So it should come as no surprise that sometimes you will want to write code to interface with data from Excel.

In the following example, I’ve written some code that you can copy and paste into pretty much any .NET project type. The spreadsheet that I’m connecting to is an imaginary list of users that exist in a WorkSheet called Users that exists in an Excel SpreadSheet called TestExcel.xlsx

It’s important to note that if you have Excel columns with data that is sometimes intermixed numbers or letters, you need to set an IMEX=1 Extended Property in your connection string. Otherwise, by default your query will return blank values, which is really not optimal default behavior in my opinion.

What’s happening in this code is you are setting up an OleDb connection to the Excel file you wish to read. Once you’ve done so, reading in data from the file is as simple as reading from a regular database table.

Note that the code is reading into a data Adapter and is then looping through each line. This is great if you are interested in having granular control over the information being read from the Excel file. In particular, this is useful if you want to exclude things like titles, or footnotes, or if you want to analyze the data coming in (always a good idea).

Unlike a database query, as the code loops through each row in the adapter, we address each column by number. This has the advantage that it doesn’t require the user to properly name each column header… we just go by row index.

For simplicity, the code writes out to a system Event Log when it starts and when an error happens. If there is a problem with the tool, check in the Application Event logs for details.

Here is the VB.NET Version of the Code:

Imports System.Data
Imports System.Data.Common
Imports System.Data.OleDb
Private Sub checkExcel()
Dim outLog As New EventLog(strLog, strMachine, strSource)
outLog.WriteEntry("Start Reading Excel User List.")
Dim strEmpsNotInHR As String = ""
Try
Dim strProvider As String = "System.Data.OleDb"
Dim factory As DbProviderFactory = DbProviderFactories.GetFactory(strProvider)
Dim strExcelConnection As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test\TestExcel.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';"
Using connection As DbConnection = factory.CreateConnection()
connection.ConnectionString = strExcelConnection
Dim objDataSet As New DataSet()
Using command As DbCommand = connection.CreateCommand()
command.CommandText = "SELECT * FROM [Users$] "
command.CommandType = CommandType.Text
Using adapter As New OleDbDataAdapter()
adapter.SelectCommand = command
adapter.Fill(objDataSet)
For Each dr As DataRow In objDataSet.Tables(0).Rows
Dim strTmpUserName As String = "" & (dr(0)).trim()
Next
End Using
End Using
End Using
Catch ex As Exception
outLog.WriteEntry("Error Reading Excel User List. " & ex.ToString())
End Try
End Sub

Here is the Example Code in C#

private void checkExcel()
{
    EventLog outLog = new EventLog(strLog, strMachine, strSource);
    outLog.WriteEntry("Start Reading Excel User List.");
    string strEmpsNotInHR = "";
    try
    {
        string strProvider = "System.Data.OleDb";
        DbProviderFactory factory = DbProviderFactories.GetFactory(strProvider);
        string strExcelConnection = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test\TestExcel.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';";
        using (DbConnection connection = factory.CreateConnection())
        {
            connection.ConnectionString = strExcelConnection;
            DataSet objDataSet = new DataSet();
            using (DbCommand command = connection.CreateCommand())
            {
                command.CommandText = "SELECT * FROM [Users$] ";
                command.CommandType = CommandType.Text;
                using (OleDbDataAdapter adapter = new OleDbDataAdapter())
                {
                    adapter.SelectCommand = command;
                    adapter.Fill(objDataSet);
                    foreach (DataRow dr in objDataSet.Tables(0).Rows)
                        string strTmpUserName = "" + (dr(0)).trim();
                }
            }
        }
    }
    catch (Exception ex)
    {
        outLog.WriteEntry("Error Reading Excel User List. " + ex.ToString());
    }
}