Environment: win7+iis7+office2007
After you export the Excel file in the ASP. NET Web site, import the file into the database. Error opening connection when reading Excel file.
Error: External table is not in expected format
FIX: Checked, the exported Excel is standard file is not HTML, yes, Excel file is normal.
Debug code, create Connection object OleDbConnection is also normal, but in Conn. Open () An error occurred while opening the link.
Look closely at the link string, check out the error, Excel version problem, EXCE connection string version is office2003, change to Excel2007 version is normal import.
The summary rules are as follows:
Using System.Data.OleDb;
Using System.Data;
public void readexcelfiless ()
{
//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 Excel2007 before (. xls) file
String strconn = "provider=microsoft.ace.oledb.12.0;" + "Data source=" + Server.MapPath ("excelfiles/ Mydata2007.xlsx ") +"; Extended properties= ' Excel 12.0; Hdr=yes; Imex=1 ' "; //this connection can manipulate. xls and. xlsx files (connection strings that support Excel2003 and Excel2007)
//Remarks:" Hdr=yes; " Is that the first line of the Excel file is the column name instead of the data, "HDR=NO;" It's just the opposite of the front.
// "Imex=1" if the data type in the column is inconsistent, use "imex=1" to avoid data type collisions.
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
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);
(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; ' ",
Transfer from http://blog.csdn.net/firefly_2002/article/details/9010873
C # conn.open () external table is not in the expected format (error reading excel file)