I need to pull data from an xls, I also need have the user be able to change the location of the file it will. So an OleDbConnection seemed like a good start, and it was until the first merged cell.
This works for all but the merged cells:
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=F:\test.xls;Extended Properties=Excel 8.0;");
cmd.CommandText = "SELECT * FROM [Sheet$]";
cmd.Connection.Open();
I found that this should allow access to the merged cells:
OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\test.xls;Extended Properties=Excel 8.0;HDR=Yes;IMEX=1;");
But then I get a Could not find installable ISAM exception on cmd.conn.open();
I followed the advice here:
http://support.microsoft.com/kb/209805
And here:
Error: "Could Not Find Installable ISAM"
No luck.
I’m open to other ways of pulling data from the xls. Or even if there was a command I could run on the xls to remove the mirged cells that might work.
I think it's just because you have to enclose the Extended Properties in quotes if you have more than one
OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\test.xls;
Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';");
Or if single quotes don't work (you get the idea)
OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\test.xls;
Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;"";");
While your example doesn't show it, this error can also be caused by spaces in the file path. In which case you would need to wrap the file path in quotes as well.
OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""F:\test.xls"";...
Assuming your system requirements include an installation of Excel, you can use the Excel Object Library
Excel.Sheets sheets = m_Excel.Worksheets;
Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);
Excel.Range range = worksheet.get_Range("A1", "E1".ToString());
etc.
See also VSTO
Try this
I had this problem. just because of quotes
string sConnection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sFilePath + ";" + "Extended Properties='Excel 8.0;HDR=YES;'";
SpreadsheetGear for .NET is a royalty free spreadsheet component for .NET and should do everything you want for 32 bit and 64 bit .NET with no dependency on Excel (or anything else other than .NET 2.0+).
You can see live ASP.NET samples here and download the free trial here if you want to try it yourself.
Disclaimer: I own SpreadsheetGear LLC