Thursday, June 14, 2012

C# Read Excel and Show in WPF DataGrid

In this example I will show how to read data from excel using C# and how to show it in WPF DataGrid.

In order to work with excel in C# you have to add reference to Microsoft.Office.Interop.Excel library.
As I said before I want also show the excel data in WPF DataGrid, so I created ExcelData class which contains Data property. My DataGrid will be bound to this Data property.
Let's see what we got inside of it:

using Excel = Microsoft.Office.Interop.Excel;

public class ExcelData
{
    public DataView Data
    {
        get
        {
            Excel.Application excelApp = new Excel.Application();
            Excel.Workbook workbook;
            Excel.Worksheet worksheet;
            Excel.Range range;
            workbook = excelApp.Workbooks.Open(Environment.CurrentDirectory + "\\Excel.xlsx");
            worksheet = (Excel.Worksheet)workbook.Sheets["Test Sheet"];

            int column = 0;
            int row = 0;

            range = worksheet.UsedRange;
            DataTable dt = new DataTable();
            dt.Columns.Add("ID");
            dt.Columns.Add("Name");
            dt.Columns.Add("Position");
            dt.Columns.Add("Web Site");
            for (row = 2; row <= range.Rows.Count; row++)
            {
                DataRow dr = dt.NewRow();
                for (column = 1; column <= range.Columns.Count; column++)
                {
                    dr[column - 1] = (range.Cells[row, column] as Excel.Range).Value2.ToString();
                }
                dt.Rows.Add(dr);
                dt.AcceptChanges();
            }
            workbook.Close(true, Missing.Value, Missing.Value);
            excelApp.Quit();
            return dt.DefaultView;
        }
    }
}
As you can see reading excel in .NET Framework 4 is pretty simple task to do. I read excel worksheet and put the data into DataTable. The tricky part of it is to bind my property to the DataGrid. You cannot bind DataTable to a DataGrid because DataGrid does not implement IEnumerable interface.
So instead of DataTable I'm simply returning DefaultView.
Let's now see what we have in XAML:
<Window x:Class="WPFReadExcel.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="Reading Excel - www.CodeArsenal.net"         
        Height="226" Width="346">
    <Grid>
        <DataGrid Name="dataGrid1" ItemsSource="{Binding Data}">
        </DataGrid>
    </Grid>
</Window>
ItemSource bound to the Data property. In the code behind I just specifying DataContext for the DataGrid:
ExcelData exceldata = new ExcelData();
this.dataGrid1.DataContext = exceldata;
You're welcome to download the source code of this example (Visual Studio 2010 Project)

22 comments:

  1. Instead of hardcoding column names in ExcelData class......get the column names from the worksheet (assuming headings are in row one)
    Then any table can be imported!
    // amend code as such

    DataTable dt = new DataTable();
    //dt.Columns.Add("ID");
    //dt.Columns.Add("Name");
    //dt.Columns.Add("Position");
    //dt.Columns.Add("Web Site");

    for (column = 1; column <= range.Columns.Count; column++)
    {
    //dr[column - 1] = (range.Cells[row, column] as Excel.Range).Value2.ToString();
    dt.Columns.Add((range.Cells[1, column] as Excel.Range).Value2.ToString());
    }

    ReplyDelete
    Replies
    1. Error 1 The name 'Environment' does not exist in the current context
      Error 2 The name 'Missing' does not exist in the current context
      Error 3 The name 'Missing' does not exist in the current context
      How to clear this errors ?

      Delete
  2. Thanks for the article!

    ReplyDelete
  3. I can not open Excel file.I got an error as "Microsoft Excel cannot access the file 'C:\Excel.xlsx'" in this line....
    workbook = excelApp.Workbooks.Open(Environment.CurrentDirectory + "\\Excel.xlsx");
    Plz help me...I spent lot of time in this

    ReplyDelete
    Replies
    1. Try using a direct path to the file:
      workbook = excelApp.Workbooks.Open("c:\\temp\\book.xlsx);

      Note that you will need to use \\ instead of singles ;)

      Delete
  4. There are several possible reasons:

    • The file name or path does not exist.
    • The file is being used by another program.

    ReplyDelete
  5. Thanks a lot! Very good example!

    ReplyDelete
  6. Nice Article ................

    ReplyDelete
  7. just a little advice you never create an instance of an object inside a "for"
    it should be like this
    DataRow dr=new DataRow();
    for(stuff in here)
    {
    dr.stuff= morestuff;
    }

    ReplyDelete
  8. If there's an empty cell, this line:
    range.Cells[row, column] as Excel.Range).Value2.ToString()
    breaks. I could put it in a try catch where the catch sets that one cell to an empty string, but that doesn't seem like it would be best practice. Any thoughts on how to handle this scenario?

    ReplyDelete
    Replies
    1. Just write this thing:
      dr[column - 1] = (range.Cells[row, column] as Excel.Range).Value2!=null ? (range.Cells[row, column] as Excel.Range).Value2.ToString() : "";

      Delete
    2. An easier way to handle this row:

      range.Cells[row, column] as Excel.Range).Value2.ToString()

      is to change it to:

      Convert.ToString((range.Cells[row, column] as Excel.Range).Value2);

      .ToString() can't handle nulls but Convert.ToString() can. It allows for less code too.

      Delete
  9. This is really nice article have a look of this article.
    http://www.dotnetpools.com/Article/ArticleDetiail/?articleId=381&title=How-to-Read-Excel-File-in-C#-Windows-Application-and-Show-Excel-Data-Into-DataGridView-Controls

    ReplyDelete
  10. excel sheet is not binding to datagrid bt no error appears

    ReplyDelete
    Replies
    1. You probably forgot to pass data context or type mismatch

      Delete
  11. Hi... Thanks a lot for the info. I tried the solution and it worked. There is only one limitation that I found. I used a button click to invoke the class Excel data. The button and the datagrid should be part of the the same xaml file window. Can someone please tell how to show the data in a different window than the one in which the button is placed. I tried this but was not working since the datagrid then is not a part of the xaml. The exact message is as under.
    Error 1 'AutoCode1.AutoCode1home' does not contain a definition for 'dataGrid2' and no extension method 'dataGrid2' accepting a first argument of type 'AutoCode1.AutoCode1home' could be found (are you missing a using directive or an assembly reference?)

    Thanks one again for sharing your replies

    ReplyDelete
    Replies
    1. I wanted to add code but it doesn;t allow me, mistaking xaml for html

      Delete

  12. <DataGrid is not cming using WPF :( please help

    ReplyDelete
  13. Display on this kind of error in runtime:


    Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).

    ReplyDelete