System.Data.OleDb.OleDbException: Could not find i

问题:

I have scoured the net, and found many people asking this, yet none have fixed my answer.

I have a Connection Class, and a Method that uses that Class in a page.

DataConn.cs

public static OleDbConnection ConnectExcel()
{
    //Store the connection details as a string
    string connstr =
        String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=pricelist.xlsx;Extended Properties=Excel 12.0 Xml;HDR=YES");

    //Initialise the connection to the server using the connection string.
    OleDbConnection oledbConn = new OleDbConnection(connstr);

    //Open the connection, we do this here so we can instantly be able to use SQL commands in the code.
    oledbConn.Open();

    return oledbConn;
}

public static void DisconnectExcel()
{
    _oledbConn.Dispose();
    _oledbConn.Close();
}

And the code that calls it

protected void Page_Load(object sender, EventArgs e)
{
    // Connection String
    const string xlStr = "SELECT * FROM [Sheet2$]";

    // Create OleDbCommand object and select data from worksheet Food
    OleDbCommand cmd = new OleDbCommand(xlStr, DataConn.ConnectExcel());

    // Create new OleDbDataAdapter
    OleDbDataAdapter oleda = new OleDbDataAdapter();

    oleda.SelectCommand = cmd;

    // Create a DataSet which will hold the data extracted from the worksheet.
    DataSet ds = new DataSet();

    // Fill the DataSet from the data extracted from the worksheet.
    oleda.Fill(ds);

    // Bind the data to the GridView
    gridPricelist.DataSource = ds;
    gridPricelist.DataBind();
}

Yes I STILL get:

System.Data.OleDb.OleDbException: Could not find installable ISAM.

Can anyone please help?

回答1:

If you use more than 1 extended property then the value tokens must be quoted, otherwise there is no way for the driver to distinguish them from the other non-extended properties in the connection string;

...Extended Properties=""Excel 8.0;IMEX=1"""

modify your connection string

String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=pricelist.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES""");

reference: Could not find installable ISAM

回答2:

please "Extended Properties" put it in ' '.

That is, like the following statement:

string connStr = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=pricelist.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES'")

回答3:

If you had installed LibreOffice look for cli_basetypes.dll, cli_cppuhelper.dll, cli_oootypes.dll, cli_uno.dll, cli_ure.dll, cli_uretypes.dll then add references to your project (to work with LibreOffice API's), I also installed "Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint File Formats" and "Microsoft Access Database Engine 2010 Redistributable" (to get ACE.OLEDB.12.O connection without complete Office installation). This is a part of VB Sample in which I got the connection to oledb to create some queries.

    OpenFileDialog.Filter = "Spreadsheets (*.xls*)|*.xls*"
    OpenFileDialog.Multiselect = False
    Try
        If (OpenFileDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK) Then
            objOffice = CreateObject("com.sun.star.ServiceManager") 'preparar instancia libreOffice (prepare libreOffice instance)
            instOffice = objOffice.createInstance("com.sun.star.frame.Desktop")
            Dim obj(-1) As Object
            Dim myDoc = instOffice.loadComponentFromURL("file:///" & OpenFileDialog.FileName.Replace("\", "/"), "_default", 0, obj)
            Dim hojas = myDoc.getSheets().getElementNames() 'Obtener nombres de las hojas de calculo (get Spreadsheet names)
            System.Threading.Thread.Sleep(1000) 'Esperar a que termine la instancia Office (await libreOffice thread)
            myDoc.Close(True)

            Dim MyConnection As System.Data.OleDb.OleDbConnection 'Preparar conexión para realizar consulta tipo sql (preparing connection)
            Dim DtSet As System.Data.DataSet
            Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

            If OpenFileDialog.FileName.ToUpper.Contains(".XLSX") Then
                MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & OpenFileDialog.FileName & "';Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;'")
            Else
                MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & OpenFileDialog.FileName & "';Extended Properties='Excel 12.0;HDR=YES;IMEX=1'")
            End If