C# and read values from an Excel file

Question

I have an Excel file and the columns have names (data source is NOT controlled by me.. it's given to me by a client). Although the columns change, the column headers never change.

In the file, it's called "First Name"

How do I access the data in every cell within the same column?

Heads up: Excel column "headers" are not analogous to database column names--they are just regular cells with 'heading' text in them. So you need to read the text in the cells in the first row to get the "headers." Loop across the cells in the first row looking for one with the desired header text. Then once you find a cell whose text=column name, you know that the column below it contains the data — bernie2436
– bernie2436, Commented Dec 8, 2011 at 21:03

Olivier Jacot-Descombes · Accepted Answer · 2011-12-08 21:12:10Z

4

Open your Excel file as a database. Then you will not have to bother about the position of the columns:

string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\MyExcelFile.xls;Extended Properties=\"Excel 8.0;HDR=YES\"";
using (var conn = new System.Data.OleDb.OleDbConnection(connString)) {
    conn.Open();
    System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("Select * From [SheetName$]", conn);
    OleDbDataReader reader = cmd.ExecuteReader();
    int firstNameOrdinal = reader.GetOrdinal("First Name");
    int lastNameOrdinal = reader.GetOrdinal("Last Name");
    while (reader.Read()) {
        Console.WriteLine("First Name: {0}, Last Name: {1}", 
            reader.GetString(firstNameOrdinal), 
            reader.GetString(lastNameOrdinal));
    }
}

answered Dec 8, 2011 at 21:12

Olivier Jacot-Descombes

114k14 gold badges149 silver badges202 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Charlie Salts Over a year ago

This worked for me, though I had to change the build configuration from 'AnyCPU' to 'x86'. Apparently Microsoft.Jet.OLEDB.4.0 is not available when running x64.

Olivier Jacot-Descombes Over a year ago

Yes, unfortunately, Jet has no 64-bit interface.

Justin Niessner · Accepted Answer · 2011-12-08 21:00:04Z

2

I would take a look at this example from Microsoft:

How to query and display excel data by using ASP.NET, ADO.NET, and Visual C#.NET

answered Dec 8, 2011 at 21:00

Justin Niessner

246k40 gold badges416 silver badges546 bronze badges

Comments

Lester · Accepted Answer · 2011-12-08 21:08:22Z

0

I've used excelLibrary in the past and found it very easy to use.

answered Dec 8, 2011 at 21:08

Lester

4,4133 gold badges30 silver badges31 bronze badges

Comments

Paige Cook · Accepted Answer · 2011-12-08 21:08:22Z

0

I have successfully used the FileHelpers Library to read Excel files in the past.

answered Dec 8, 2011 at 21:08

Paige Cook

22.6k3 gold badges60 silver badges68 bronze badges

Comments

uadrive · Accepted Answer · 2011-12-08 21:19:49Z

You can do something like this where you use ODBC to connect to the file and download the content of a sheet.

 private bool DownloadExcelData(string fileName, ref DataTable informationDT)
            {
                // bool success
                bool success = true;

                // open the file via odbc
                string connection = ConfigurationManager.ConnectionStrings["xls"].ConnectionString;
                connection = String.Format(connection, FilePath + fileName);
                OleDbConnection conn = new OleDbConnection(connection);
                conn.Open();

                try
                {
                    // retrieve the records from the first page
                    OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Information$]", conn);
                    OleDbDataAdapter adpt = new OleDbDataAdapter(cmd);
                    adpt.Fill(informationDT);
                }
                catch { success = false; }

                // close the connection
                conn.Close();
                return success;
            }

Here are some sample ODBC connections for xls and xlsx files:

<add name="xls" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;'" />
    <add name="xlsx" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0" />

Collectives™ on Stack Overflow

C# and read values from an Excel file

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related