none
Excel in c#

Answers

  • My my, accessing Excel data seems to be a popular problem these days.

    Here's what I do:
    string filename = ...; // Set to your xls file     
    string Type = "Excel 8.0"; // For Excel 97/2000/2003     
    // string Type = "Excel 5.0"; // For Excel 5.0/95     
    bool Header = true; // True if the first row in the Excel file is a header     
                        //  (used for column names, not data)     
    bool TreatIntermixedAsText = true; // True if columns containing different data types are     
                                       //  treated as text (note that columns containing only     
                                       //  integer types are still treated as integer, etc)     
        
    OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();     
    builder.DataSource = filename;     
    builder.Provider = "Microsoft.Jet.OLEDB.4.0";     
    builder["Extended Properties"] = Type +     
        ";HDR=" + (Header ? "Yes" : "No") +     
        ";IMEX=" + (TreatIntermixedAsText ? "1" : "0");     
        
    OleDbConnection connection = new OleDbConnection(builder.ConnectionString);     
    connection.Open();  

    This will establish an OLEDB connection to an existing Excel file. You can then use that connection to create a reader, fill tables, etc.

           -Steve
    • Proposed as answer by Harry Zhu Friday, December 26, 2008 4:31 AM
    • Marked as answer by Harry Zhu Tuesday, December 30, 2008 2:21 AM
    Tuesday, December 23, 2008 1:44 PM
  • Here's a more complete solution that also handles Excel 2007 files:
    string filename = ...; // Set to your xls file  
    string Type = "Excel 12.0 Xml"; // For Excel 2007 XML (*.xlsx)  
    // string Type = "Excel 12.0"; // For Excel 2007 Binary (*.xlsb)  
    // string Type = "Excel 12.0 Macro"; // For Excel 2007 Macro-enabled (*.xlsm)  
    // string Type = "Excel 8.0"; // For Excel 97/2000/2003 (*.xls)  
    // string Type = "Excel 5.0"; // For Excel 5.0/95 (*.xls)  
    bool Header = true; // True if the first row in the Excel file is a header  
                        //  (used for column names, not data)  
    bool TreatIntermixedAsText = true; // True if columns containing different data types are  
                                       //  treated as text (note that columns containing only  
                                       //  integer types are still treated as integer, etc)  
     
    OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();  
    builder.DataSource = filename;  
    if (Type == "Excel 5.0" || Type == "Excel 8.0")  
        builder.Provider = "Microsoft.Jet.OLEDB.4.0";  
    else  
        builder.Provider = "Microsoft.ACE.OLEDB.12.0";  
    builder["Extended Properties"] = Type +  
        ";HDR=" + (Header ? "Yes" : "No") +  
        ";IMEX=" + (TreatIntermixedAsText ? "1" : "0");  
     
    OleDbConnection connection = new OleDbConnection(builder.ConnectionString);  
    connection.Open(); 

    Note that you can open Excel 2007 files even if you don't have Office 2007 installed, by redistributing the Office 2007 Data Connectivity Components.

    The connection strings used for Excel files are not clearly documented; see the following links for more information:

            -Steve

    • Proposed as answer by Harry Zhu Friday, December 26, 2008 4:31 AM
    • Marked as answer by Harry Zhu Tuesday, December 30, 2008 2:21 AM
    Tuesday, December 23, 2008 2:18 PM
  • Hello Emrullah_Kuştaşı,
        Here is my previous post to a similar question verbatim: Here is another way, which I use, that does not requre odbc.

    1. Right click on References (in the Project's Solution Explorer), click on the COM tab, and scroll down until you see "Microsoft Excel 12.0 object library". Add it as a reference.
    2. Once added, add the following line to the "using" statements at the top of the code window: using Microsoft.Office.Interop.Excel;.
    3. Add the following code to initialize the excel document:
      Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application();  
      Workbook theWorkbook = ExcelObj.Workbooks.Open(@"C:\test.xlsx", 0, false, 5, """"true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t"truefalse, 0, truefalsefalse);  
      Sheets sheets = theWorkbook.Worksheets;  

      Worksheet worksheet = (Worksheet)sheets.get_Item(1); // Optionally pass excel.Visible = true, if you want the Excel document to visibly open

    4. To access the value in the cells, here is one method of doing it:
      int row = 1;  
      char column = 'A';  
      Range cellRang = worksheet.get_Range(row, column);  
      label1.Text = cellRang.Value2.ToString(); 
    5. If you want to set the value, just assign to the Value2 of the range. Alternatively, you can assign by using the following code:
      int row = 1;  
      int column = 1;  
      worksheet.Cells.set_Item(row, column, "alpha"); 
    6. Once you have finished reading/writing to the Excel document, run the following commands to save and close it:
      workbook.Save();  
      workbook.Close(false"test.xlsx"false);  
      ExcelObj.Quit(); 

    Hope this helps!
    Thanks!
    chukrum47


    How are a plum and a rabbit similar? They're both purple, except for the rabbit.
    • Proposed as answer by Harry Zhu Friday, December 26, 2008 4:31 AM
    • Marked as answer by Harry Zhu Tuesday, December 30, 2008 2:21 AM
    Tuesday, December 23, 2008 3:16 PM

All replies

  • My my, accessing Excel data seems to be a popular problem these days.

    Here's what I do:
    string filename = ...; // Set to your xls file     
    string Type = "Excel 8.0"; // For Excel 97/2000/2003     
    // string Type = "Excel 5.0"; // For Excel 5.0/95     
    bool Header = true; // True if the first row in the Excel file is a header     
                        //  (used for column names, not data)     
    bool TreatIntermixedAsText = true; // True if columns containing different data types are     
                                       //  treated as text (note that columns containing only     
                                       //  integer types are still treated as integer, etc)     
        
    OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();     
    builder.DataSource = filename;     
    builder.Provider = "Microsoft.Jet.OLEDB.4.0";     
    builder["Extended Properties"] = Type +     
        ";HDR=" + (Header ? "Yes" : "No") +     
        ";IMEX=" + (TreatIntermixedAsText ? "1" : "0");     
        
    OleDbConnection connection = new OleDbConnection(builder.ConnectionString);     
    connection.Open();  

    This will establish an OLEDB connection to an existing Excel file. You can then use that connection to create a reader, fill tables, etc.

           -Steve
    • Proposed as answer by Harry Zhu Friday, December 26, 2008 4:31 AM
    • Marked as answer by Harry Zhu Tuesday, December 30, 2008 2:21 AM
    Tuesday, December 23, 2008 1:44 PM
  • I use Microsoft Interopability, I don't use an OLEDB Connection, if you're still interested in knowing how to do it this way, let me know.
    Tuesday, December 23, 2008 2:14 PM
  • Here's a more complete solution that also handles Excel 2007 files:
    string filename = ...; // Set to your xls file  
    string Type = "Excel 12.0 Xml"; // For Excel 2007 XML (*.xlsx)  
    // string Type = "Excel 12.0"; // For Excel 2007 Binary (*.xlsb)  
    // string Type = "Excel 12.0 Macro"; // For Excel 2007 Macro-enabled (*.xlsm)  
    // string Type = "Excel 8.0"; // For Excel 97/2000/2003 (*.xls)  
    // string Type = "Excel 5.0"; // For Excel 5.0/95 (*.xls)  
    bool Header = true; // True if the first row in the Excel file is a header  
                        //  (used for column names, not data)  
    bool TreatIntermixedAsText = true; // True if columns containing different data types are  
                                       //  treated as text (note that columns containing only  
                                       //  integer types are still treated as integer, etc)  
     
    OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();  
    builder.DataSource = filename;  
    if (Type == "Excel 5.0" || Type == "Excel 8.0")  
        builder.Provider = "Microsoft.Jet.OLEDB.4.0";  
    else  
        builder.Provider = "Microsoft.ACE.OLEDB.12.0";  
    builder["Extended Properties"] = Type +  
        ";HDR=" + (Header ? "Yes" : "No") +  
        ";IMEX=" + (TreatIntermixedAsText ? "1" : "0");  
     
    OleDbConnection connection = new OleDbConnection(builder.ConnectionString);  
    connection.Open(); 

    Note that you can open Excel 2007 files even if you don't have Office 2007 installed, by redistributing the Office 2007 Data Connectivity Components.

    The connection strings used for Excel files are not clearly documented; see the following links for more information:

            -Steve

    • Proposed as answer by Harry Zhu Friday, December 26, 2008 4:31 AM
    • Marked as answer by Harry Zhu Tuesday, December 30, 2008 2:21 AM
    Tuesday, December 23, 2008 2:18 PM
  • Hello Emrullah_Kuştaşı,
        Here is my previous post to a similar question verbatim: Here is another way, which I use, that does not requre odbc.

    1. Right click on References (in the Project's Solution Explorer), click on the COM tab, and scroll down until you see "Microsoft Excel 12.0 object library". Add it as a reference.
    2. Once added, add the following line to the "using" statements at the top of the code window: using Microsoft.Office.Interop.Excel;.
    3. Add the following code to initialize the excel document:
      Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application();  
      Workbook theWorkbook = ExcelObj.Workbooks.Open(@"C:\test.xlsx", 0, false, 5, """"true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t"truefalse, 0, truefalsefalse);  
      Sheets sheets = theWorkbook.Worksheets;  

      Worksheet worksheet = (Worksheet)sheets.get_Item(1); // Optionally pass excel.Visible = true, if you want the Excel document to visibly open

    4. To access the value in the cells, here is one method of doing it:
      int row = 1;  
      char column = 'A';  
      Range cellRang = worksheet.get_Range(row, column);  
      label1.Text = cellRang.Value2.ToString(); 
    5. If you want to set the value, just assign to the Value2 of the range. Alternatively, you can assign by using the following code:
      int row = 1;  
      int column = 1;  
      worksheet.Cells.set_Item(row, column, "alpha"); 
    6. Once you have finished reading/writing to the Excel document, run the following commands to save and close it:
      workbook.Save();  
      workbook.Close(false"test.xlsx"false);  
      ExcelObj.Quit(); 

    Hope this helps!
    Thanks!
    chukrum47


    How are a plum and a rabbit similar? They're both purple, except for the rabbit.
    • Proposed as answer by Harry Zhu Friday, December 26, 2008 4:31 AM
    • Marked as answer by Harry Zhu Tuesday, December 30, 2008 2:21 AM
    Tuesday, December 23, 2008 3:16 PM
  • Hello,

    I am trying to use the above code....and I am getting this exception:
    (I just want to load a Table up with data from a Excel file)

    System.InvalidOperationException was unhandled
      Message="The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine."

    How can I get this to work?

    BTW I am using a Vista machine, using WPF and VS 2008...if any of those matter.
    Friday, April 03, 2009 6:08 PM
  • You need either Microsoft Office installed, or:
      Office 2007 Data Connectivity Components

    BTW, my sample code above is now a sample application:
      http://code.msdn.microsoft.com/NitoExcel

            -Steve
    Friday, April 03, 2009 7:12 PM
  • Hi,

    You can also easily C# read Excel like this:

    ExcelFile ef = new ExcelFile();
     
    // Loads Excel file.
    ef.LoadXls("filename.xls");
     
    // Selects first worksheet.
    ExcelWorksheet ws = ef.Worksheets[0];
     
    // Displays the value of first cell in the messageBox.
    MessageBox.Show(ws.Cells["A1"].Value.ToString());
    by using this Excel C# library.

    Wednesday, May 30, 2012 8:30 AM