Accessing Excel 2007 file format with OLEDB

Question

I'm currently trying to access an excel 2007 file on a server that doesn't contain Microsoft Office on it. My connectionstring is something like this.

  String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
  "Data Source=" + file_path + ";Extended Properties=Excel 8.0;";

But this is for 2003 format, which works fine. I would like to take advantage of the 2007 xml format. But I'm unable to access the file through the following connection.(The target server doesn't have Office installed on it, not sure if thats the reason.

  String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
 "DataSource=" + file_path + ";HDR=Yes;IMEX=1;Extended Properties=Excel 12.0;";

I'm keep getting the "could not find installable isam." Error.

EDIT I'm using visual studio 2005 and developing in C# if this helps clear anything up.

Answer 1

You may try with this connection string:

string connectionString = 
    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + 
    file_name + 
    ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";

Please note though that this driver is not supported on x64 systems and it won't work.

Answer 2

Hi, Use

String connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= FilePath ;Extended Properties=\";Excel 8.0;HDR=Yes;IMEX=1\";"

Answer 3

SpreadsheetGear for .NET will let you open the workbook with no reliance on OleDB, COM Interop or any other API which can create problems on a server.

You can see live ASP.NET samples here and download the free trial here.

Disclaimer: I own SpreadsheetGear LLC

Answer 4

You should try using the Linq to Excel open source project to query data from Excel spreadsheets. You can use LINQ statements to query the data, and Linq to Excel takes care of making the OLEDB connection and creating the SQL statement.

Here's an example of how easy it is to query data from an Excel spreadsheet

var book = new ExcelQueryFactory("pathToExcelFile");
var rows = from x in book.Worksheet() 
           select new                
           {
             Name = x["Name"],
             BirthDate = x["BirthDate"].Cast<DateTime>()
           };

Checkout the Linq to Excel intro video for more information about the open source project.