January 11th, 2011, 02:19 PM
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
Method 2: Instantiate the object in the correct format explicitly
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.
Method 3: Use a third-party solution, in this case ExcelDataReader (http://exceldatareader.codeplex.com/).
using Excel = Microsoft.Office.Interop.Excel;
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.
As per the site's example:
Method 4: Use an SSIS package to open and read the contents, and hand the data to my program...
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
//6. Free resources (IExcelDataReader is IDisposable)
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.
January 14th, 2011, 04:20 PM
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.
public static string GetWorksheetNames(string fileName)
string connString = "";
OleDbConnection conn = null;
DataTable dt = null;
//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);
//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)
//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
catch (Exception ex)
if (conn.State == ConnectionState.Open)
if (dt != null)
January 14th, 2011, 04:23 PM
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)
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(,)\";";
Tags for this Thread
Click Here to Expand Forum to Full Width