When I try read Excel with OLE DB all values are e

问题:

I have done a little program to parser excel. It works fine only when before to execute it I open Excel file manually (is not it strange?). I.e. first I open excel file, second I execute program and I get good results

If I don't open excel before to execute it I get empty values

My connection string (excel file has extension .XLSX):

connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                   "Data Source=" + path + "\\" + f.Name + ";" +
                   "Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";

My code to open connection with oleDB:

using (OleDbConnection cnn = new OleDbConnection(connectionString))
{
    cnn.Open();
    ...
    String sql = "SELECT * FROM [" + sheetNames[i] + "]";
    OleDbDataAdapter da = new OleDbDataAdapter(sql, cnn);
    DataTable dt = new DataTable();
    da.Fill(dt); // Now 'dt' should has all data
}

Also, I have installed AccessDatabaseEngine.exe and AccessRuntime.exe

Obviously, my purpose is run the program without having to manually open the file. Any suggestion?

Thanks for your time.

回答1:

I found it a real pain when I tried to get OleDb and Excel to play nicely together. Fortunately, I found a much better approach: EPPlus

EPPlus is a .net library that reads and writes Excel 2007/2010 files using the Open Office Xml format (xlsx).

Open source, feature rich and easy to use. If at all possible, use it instead of OleDb.