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.