opening Excel Worksheets in C#
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 3 of 3

Thread: opening Excel Worksheets in C#

  1. #1
    Join Date
    Jan 2011
    Posts
    1

    Question opening Excel Worksheets in C#

    I have found many sources of information on how to open an Excel 2003 file, but all of them assume the .xls is an entire workbook. The Excel file I must work with only has a single worksheet in it and does not contain a workbook in the file (as far as I can tell). How can I read the contents of such a file? (add references as necessary)

    I'd like to manipulate the contents in a DataSet if possible, or alternatively an XML object, but I won't be too picky. Here's what I've tried so far and why they don't work

    Method 1: Use a Microsoft Jet OLE DB 4.0 connection
    Code:
    using System.Data.OleDb;
    
    string Path = @"C:\Test.xls";
    String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
        "Data Source=" + Path + ";Extended Properties=Excel 8.0;";
    
    // Create connection object by using the preceding connection string.
    OleDbConnection oledbConn = new OleDbConnection(sConnectionString);
    
    // Open connection with the database.
    oledbConn.Open();   // ERROR: External table is not in the expected format.
                
    // The code to follow uses a SQL SELECT command to display the data from the worksheet.
    // Create new OleDbCommand to return data from worksheet.
    OleDbCommand oledbCmdSelect = new OleDbCommand("SELECT * FROM ExcelData$", oledbConn);
    
    // Create new OleDbDataAdapter that is used to build a DataSet
    // based on the preceding SQL SELECT statement.
    OleDbDataAdapter oledbAdapter = new OleDbDataAdapter();
    
    // Pass the Select command to the adapter.
    oledbAdapter.SelectCommand = oledbCmdSelect;
    
    // Create new DataSet to hold information from the worksheet.
    DataSet oledbDataset = new DataSet();
    
    // Fill the DataSet with the information from the worksheet.
    oledbAdapter.Fill(oledbDataset, "XLData");
    Method 2: Instantiate the object in the correct format explicitly
    Code:
    using Excel = Microsoft.Office.Interop.Excel;
    
    object objExcel;
    objExcel = CreateObjRef((Excel.ApplicationClass));   //  ERROR: " 'Microsoft.Office.Interop.Excel.ApplicationClass' is a 'type', which is not valid in the given context."
                                 // ...even though CreateObjRef(...)'s definition says it asks for:
                                 // public virtual ObjRef CreateObjRef(Type requestedType);
    
    // I don't have anything past this for this Method.
    Method 3: Use a third-party solution, in this case ExcelDataReader (http://exceldatareader.codeplex.com/).
    As per the site's example:
    Code:
    using Excel;
    
    string filePath = "C:\test.xsl";
    FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
    
    //1. Reading from a binary Excel file ('97-2003 format; *.xls)
    IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
    //...
    //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
    // not doing this
    //...
    //3. DataSet - The result of each spreadsheet will be created in the result.Tables
    // not doing this
    //...
    //4. DataSet - Create column names from first row
    excelReader.IsFirstRowAsColumnNames = true;
    DataSet result = excelReader.AsDataSet();   // result is always null, basically it's not finding what it's expecting
    //...
    //5. Data Reader methods
    while (excelReader.Read())
    {
    	//excelReader.GetInt32(0);
    }
    //...
    //6. Free resources (IExcelDataReader is IDisposable)
    excelReader.Close();
    Method 4: Use an SSIS package to open and read the contents, and hand the data to my program...

    I create an Excel Connection Manager, with Excel Version set to "Microsoft Excel 97-2005". I create an Excel Source as my Data Flow Source, select my Excel Connection Manager, set Data Access Mode to "Table or View", and try to select the Name of the Excel Sheet, but get "External table is not in the expected format." when I click the dropdown box.

  2. #2
    Join Date
    Dec 2008
    Posts
    144

    Re: opening Excel Worksheets in C#

    I use this in an older application I wrote to return a string array of worksheet names. In the app I used this to populate a listbox on the UI for the user to choose from.

    Code:
    public static string[] GetWorksheetNames(string fileName)
            {
                string connString = "";
                OleDbConnection conn = null;
                DataTable dt = null;
    
                try
                {
                    //determine the correct connection string for the file
                    connString = Utilities.ConnectionString(fileName);
                    //open a new connection to the specified excel file
                    conn = new OleDbConnection(connString);
                    conn.Open();
                    //create a new DataTable and populate it with the worksheet names within the excel file
                    dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    
                    //return a null valued array if the DataTable contains no value
                    if (dt == null)
                        return null;
    
                    //create a new string array to hold all of the gathered worksheet names
                    String[] Worksheets = new String[dt.Rows.Count];
                    int i = 0;  //<<<Counter for the foreach loop for the string array index
    
                    //loop through every row in the DataTable and add the worksheet name to the string array
                    foreach (DataRow row in dt.Rows)
                    {
                        Worksheets[i] = row["TABLE_NAME"].ToString();
                        i++;    //<<<Increment array index counter
                    }
                    //return the string array for use in the calling method
                    return Worksheets;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    return null;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        conn.Dispose();
                        if (dt != null)
                            dt.Dispose();
                    }
                }
            }
    HTH

  3. #3
    Join Date
    Dec 2008
    Posts
    144

    Re: opening Excel Worksheets in C#

    Oh, and this is what I used to create the connection string. (referenced by Utilities.ConnectionString in the above code)

    Code:
     public static string ConnectionString(string filePath)
            {
                string fileExtension = Path.GetExtension(filePath);
    
                if (fileExtension == ".xls")
                    return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath +
                        ";Extended Properties=Excel 8.0;";
                else if (fileExtension == ".xlsx")
                    return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath +
                        ";Extended Properties=\"Excel 12.0 Xml;HDR=Yes;IMEX=1\";";
                //else if (fileExtension == ".csv")
                //{
                //    string sourceDirectory = Path.GetDirectoryName(filePath);
                //    return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sourceDirectory +
                //        ";Extended Properties=\"text;HDR=Yes;FMT=Delimited(,)\";";
                //}
                else
                    return "";
            }

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center