Title: Read Excel data in C#

To read Excel data, you can open the Excel application and use it as a server to manipulate Excel workbooks.

First open the Add References dialog. On the COM tab, select "Microsoft Excel 14.0 Object Library" (or whatever version you have installed on your system).

Add the following using statement to make working with the Excel namespace easier. The Excel = part means you can use Excel as an alias for the namespace.

using Excel = Microsoft.Office.Interop.Excel;

This example uses the following code to open a workbook and read data from it.

// Read from the Excel workbook. private void btnRead_Click(object sender, EventArgs e) { // Get the Excel application object. Excel.Application excel_app = new Excel.ApplicationClass(); // Make Excel visible (optional). excel_app.Visible = true; // Open the workbook read-only. Excel.Workbook workbook = excel_app.Workbooks.Open( txtFile.Text, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); // Get the first worksheet. Excel.Worksheet sheet = (Excel.Worksheet)workbook.Sheets[1]; // Get the titles and values. SetTitleAndListValues(sheet, 1, 1, lblTitle1, lstItems1); SetTitleAndListValues(sheet, 1, 2, lblTitle2, lstItems2); // Close the workbook without saving changes. workbook.Close(false, Type.Missing, Type.Missing); // Close the Excel server. excel_app.Quit(); }

First the code creates an Excel.Application object to control Excel. It makes that object visible so you can watch it work. Often you will not want to make the server visible.

Next the code opens a workbook. Most of the Open method's parameters are optional (things such as passwords) so they are passed the values Type.Missing. This example opens the workbook read-only (that's the single true parameter) because it doesn't need to write into the workbook.

The code uses the workbook's first worksheet. Note that indexes in Excel tend to start with 1 not 0.

The code calls the SetTitleAndListValues method twice to get data from the worksheet and then closes Excel. The most interesting work is performed by that method.

// Set a title Label and the values in a ListBox. // Get the title from cell (row, col). Get the values from // cell (row + 1, col) to the end of the column. private void SetTitleAndListValues(Excel.Worksheet sheet, int row, int col, Label lbl, ListBox lst) { Excel.Range range; // Set the title. range = (Excel.Range)sheet.Cells[row, col]; lbl.Text = (string)range.Value2; lbl.ForeColor = System.Drawing.ColorTranslator.FromOle( (int)(double)range.Font.Color); lbl.BackColor = System.Drawing.ColorTranslator.FromOle( (int)(double)range.Interior.Color); // Get the values. // Find the last cell in the column. range = (Excel.Range)sheet.Columns[col, Type.Missing]; Excel.Range last_cell = range.get_End(Excel.XlDirection.xlDown); // Get a Range holding the values. Excel.Range first_cell = (Excel.Range)sheet.Cells[row + 1, col]; Excel.Range value_range = (Excel.Range)sheet.get_Range(first_cell, last_cell); // Get the values. object[,] range_values = (object[,])value_range.Value2; // Convert this into a 1-dimensional array. // Note that the Range's array has lower bounds 1. int num_items = range_values.GetUpperBound(0); string[] values1 = new string[num_items]; for (int i = 0; i < num_items; i++) { values1[i] = (string)range_values[i + 1, 1]; } // Display the values in the ListBox. lst.DataSource = values1; }

The SetTitleAndListValues method is fairly specific to this example. It gets a title from a cell and gets values from the cells below that one.

The code first gets a Range representing the title cell. It uses the Range object's Value2 property to get the title text. It converts the Range object's Font.Color and Interior.Color values into .NET Color objects and uses them to color the title Label control.

Next the code gets the values below the title. It first gets a Range representing the entire column. It then uses the Range object's get_End method to get a range representing the column's last used cell. The code then creates another Range representing the first cell containing values and uses the two cells to get yet another new Range representing all of the value cells. (This is easier if you know where the cells are ahead of time so you don't need to mess around trying to find the last cell.)

The code then uses the Range object's Value2 method to get a 2-dimensional array of values. Note that this is a 2-dimensional array even though it contains only one column.

To make using the values easier, the code copies the values into a 1-dimensional C# array. It sets the ListBox control's DataSource property to the new array and the control displays the values automatically.

Download the example to experiment with it and to see additional details.