C # conn. open () External table is not in the expected format

Environment: win7 + iis7 + office2007

After exporting the Excel file on the Asp.net website, import the file to the database. An error occurred while reading the Excel file.

Error: The External table is not in the expected format.

Solution: Check that the exported Excel file is a standard file instead of HTML. That's right. The Excel file is normal.

DebuggingCodeThe connection object oledbconnection is normal, but an error occurs when the connection is opened in conn. open.

After carefully reading the link string, check whether an error occurs. If the Excel version is incorrect, the exce connection string version is office2003. If it is changed to excel2007, the data is imported normally.

The summary rules are as follows:

Using system. Data. oledb;
Using system. Data;

Public void readexcelfiless ()
{< br> // string strconn = "provider = Microsoft. jet. oledb.4.0; "+" Data Source = "+ server. mappath ("excelfiles/myexcelfile.xls") + "; extended properties = 'excel 8.0; HDR = yes; IMEX = 1'";
// This connection can only operate the file
string strconn =" provider = Microsoft. ace. oledb.12.0; "+" Data Source = "+ server. mappath ("excelfiles/mydata2007.x LSX ") +"; extended properties = 'excel 12.0; HDR = yes; IMEX = 1' ";
// This connection can operate the. xlsand. XLSX files (supports connection strings of excel2003 and excel2007)
// remarks: "HDR = yes;" indicates that the first row of an Excel file is a column name rather than data. "HDR = no;" is the opposite of the previous one.
// "IMEX = 1" if the data types in the column are inconsistent, use "IMEX = 1" to avoid data type conflicts.

Oledbconnection conn = new oledbconnection (strconn );
Oledbdataadapter ADP = new oledbdataadapter ("select * from [sheet1 $]", Conn );
Dataset DS = new dataset ();
ADP. Fill (DS, "book1 ");
This. gridview1.datasource = Ds. Tables ["book1"]. defaultview;
This. gridview1.databind ();
}

 

 

 

That is:

/// 2003 (Microsoft. Jet. oledb.4.0)
String strconn = string. format ("provider = Microsoft. jet. oledb.4.0; Data Source = {0}; extended properties = 'excel 8.0; HDR = yes; IMEX = 1; '", excelfilepath );

/// 2010 (Microsoft. Ace. oledb.12.0)
String strconn = string. format ("provider = Microsoft. ace. oledb.12.0; Data Source = {0}; extended properties = 'excel 12.0; HDR = yes; IMEX = 1; '", excelfilepath );