Title: Load Excel records into a DataGridView in C#

This example shows how you can load Excel records into a DataGridView control. You can use similar techniques to load Excel data into other controls such as other kinds of grid controls or a TreeView control.

This example uses the Microsoft Excel interop library to build an Excel server. before you can use the library, you need to add a reference to it. To do that, open the Properties window, right-click References, and select Add Reference. On the COM tab, double-click the Microsoft Excel 14.0 Object Library entry.

To make using the library easier, add the following using directive to the top of the code file.

using Excel = Microsoft.Office.Interop.Excel;

To make it easier to find the Excel workbook, I added it to the project. To do that, open the Project menu and select Add Existing Item. Select the file and click Add.

Then in Solution Explorer, click on the workbook. in the Properties window, set its "Build Action" property to Content and set its "Copy to Output Directory" property to "Copy if newer." Now when you run the program, the file will be copied into the executable directory so the program can find it easily.

When the program starts, the following code places the file's location in the program's text box.

// Initialize the File TextBox. private void Form1_Load(object sender, EventArgs e) { txtFile.Text = Application.StartupPath + "\\Books.xlsx"; }

When you click the Load button, the following code executes.

private void btnLoad_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 used range. Excel.Range used_range = sheet.UsedRange; // Get the maximum row and column number. int max_row = used_range.Rows.Count; int max_col = used_range.Columns.Count; // Get the sheet's values. object[,] values = (object[,])used_range.Value2; // Get the column titles. SetGridColumns(dgvBooks, values, max_col); // Get the data. SetGridContents(dgvBooks, values, max_row, max_col); // Close the workbook without saving changes. workbook.Close(false, Type.Missing, Type.Missing); // Close the Excel server. excel_app.Quit(); }

This code creates an Excel application object and uses it to open the workbook. Notice that the Workbooks.Open method (and many other Excel application methods) take parameters that are non-specific objects. Even though it is common to use only a few of those parameters, you cannot simply set the others to null. Instead you should use the special value Type.Missing to indicate that you are not using a parameter.

The code then gets a references to the workbook's first worksheet. If the data that you want is on one of the workbook's other worksheets, select that one instead.

Next, the program needs to find out where the data is on the worksheet. It uses the sheet's UsedRange property to get a Range that represents the cells that are used by the worksheet. Note that the range does not include completely empty rows and columns. This example's workbook contains data in columns B through E and rows 3 through 12. The used range does not include the blank column A or the blank rows 1 and 2. That means the program will not waste space displaying empty rows and columns in the DataGridView.

Having found the used range, the code gets its number of rows and columns.

Next, the program uses the used range's Value2 method to copy the values in the range into a two-dimensional array. It calls the helper methods SetGridColumns and SetGridContents to define the grid's columns and to load Excel records into the grid.

After it has loaded the data, the program closes the workbook and the Excel server.

The following code shows the SetGridColumns helper method.

// Set the grid's column names from row 1. private void SetGridColumns(DataGridView dgv, object[,] values, int max_col) { dgvBooks.Columns.Clear(); // Get the title values. for (int col = 1; col <= max_col; col++) { string title = (string)values[1, col]; dgv.Columns.Add("col_" + title, title); } }

This method loops through the cells in the data array's first row and adds each value to the DatagridView control's Columns collection. It sets a column's name to the cell's text with "col_" prepended in front of it. It sets the column's text to the cell's value.

The following code shows the SetGridContents helper method.

// Set the grid's contents. private void SetGridContents(DataGridView dgv, object[,] values, int max_row, int max_col) { // Copy the values into the grid. for (int row = 2; row <= max_row; row++) { object[] row_values = new object[max_col]; for (int col = 1; col <= max_col; col++) row_values[col - 1] = values[row, col]; dgv.Rows.Add(row_values); } }

This method loops through the value array's rows. It skips the first row because it contains the column headings. For each row, the code creates a row_values array to hold the values for the row. It then loops through that row's columns and copies the corresponding values from the data array into the row_values array. After it has filled in the row_values array, the code adds the values to the DataGridView control's row data.

That's all there is to it.

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