Load Excel records into a DataGridView in C#


[load Excel records]

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 see the program in action.


Download Example   Follow me on Twitter   RSS feed   Donate




About RodStephens

Rod Stephens is a software consultant and author who has written more than 30 books and 250 magazine articles covering C#, Visual Basic, Visual Basic for Applications, Delphi, and Java.
This entry was posted in controls, database, Excel, interoperability and tagged , , , , , , , , , , , , . Bookmark the permalink.

2 Responses to Load Excel records into a DataGridView in C#

  1. Ziz says:

    Interesting example as usual – not sure if it matters but I am using VS2015Profesional and Excel 16.0, I kept getting “interop type cannot be embedded” so to make it work for me I had to change

    Excel.ApplicationClass()
    to
    Excel.Application()

    • RodStephens says:

      I haven’t seen that before, but it’s not too surprising. I have had to change the class used to represent the application in some other combinations.

      Thanks for the note. I’m sure it will help others.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.