C # conn.open () external table is not in the expected format (error reading excel file)

Source: Internet
Author: User

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.