opening Excel Worksheets in C#

[averyoo7]

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

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

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:

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.

[CreganTur]

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.

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

[CreganTur]

Oh, and this is what I used to create the connection string. (referenced by Utilities.ConnectionString in the above 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 "";
        }