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(
        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.

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)

    // 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];

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.

13 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


    • 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.

  2. Dexter says:

    This example is really helpful so thankyou however I do have two problems and wondered if you could give me some advice. I have the reference you explained at the begining of the post and the rest of the code seems to be reconized and understood but not these two errors. I would greatly appreciate any help you can give me?

    First CS0103 C# The name ‘txtFile’ does not exist in the current context.

    Second CS0103 C# The name ‘dgvbooks’ does not exist in the current context

    • RodStephens says:

      There are often things in an example that are not explained in the text of the post. The posts focus on the most interesting bits. You need to download the example to see all of the details.

      In this case, txtFile is a TextBox and dgvBooks is a DataGridView control. They are defined on the example’s form. If you download it, you should see them.

  3. Rohana B says:

    My Program shows error ”System.Runtime.InteropServices.COMException: ‘Exception from HRESULT: 0x800AC472’ ”
    Please can you guide me to solve the problem?

    • RodStephens says:

      The only thing I can think of off hand is that you should be sure to add a reference to the most recent version of the Excel object library. When I made the post, on my system, it was version 14.0. You should use whatever version is available on your computer.

      If that doesn’t work, I don’t know what the problem is.

  4. Rohana B says:

    Thank you for your previous reply.
    I am sure I added Excel object library 16.0 in my computer and followed all the steps
    as mentioned in above example.
    Further, I intend to provide more details about the above error to solve it.
    Error shown code is
    // Get the used range.
    Excel.Range used_range = sheet.UsedRange;

    • RodStephens says:

      Sorry but COM exceptions are particularly hard to debug. Basically the Excel server is saying, “Something went wrong,” but it doesn’t give you any clues about what.

      Often the best you can do is review every step to make sure you didn’t miss anything. In this case, step through the code in the debugger and make sure the document gets loaded correctly. Use the Excel objects to manipulate the document in the Immediate window to make sure it loads. For example, try getting a cell’s value.

      It’s hard to know where with this error.

  5. Rohana B says:

    Thank you very much for your comments.
    I am working to correct my code contentiously . Your C# vs Excel combinations are greatly useful for me. Can we find a method to embed Excel File inside our Main Form?
    ( I mean Open Excel File inside Our Main Form)

  6. Liam Bego says:

    Hey, Did you try zetexcel.com . I found it easy and helpful

    [Editor’s note: ZetExcel is an Excel SDK for .NET applications. It lets you create and edit Excel spreadsheets without using Excel, and costs $299 and up.]

  7. First of all I would like to say great blog! I had a quick question which I’d like to ask if you do not mind. I was interested to find out how you center yourself and clear your head prior to writing. I have had a hard time clearing my thoughts in getting my ideas out there. I truly do take pleasure in writing however it just seems like the first 10 to 15 minutes are generally lost simply just trying to figure out how to begin. Any recommendations or tips? Appreciate it!

    • RodStephens says:

      When I’m writing posts, the only real problem for me is to find time.

      When I’m writing books, I sometimes have trouble getting started. One trick is to just start writing something. Anything. Ask yourself, “What do I want to say?” Then start writing. After a couple of minutes, I usually find that I’ve fallen back into the groove and it’s easy to continue. Sometimes I need to rewrite or discard what I wrote at the beginning, but that’s okay.

      Another trick is to stop writing while in the middle of writing something easy. Then it’s easy to start up again later. For example, don’t stop at the end of a chapter. Instead stop before writing the summary so you can start with that next time.

      Basically both of those rules help me get centered for writing by writing.

      I hope that helps.

Comments are closed.