. Net reads xlsx files

Connect to the Excel table: string sqlconnection = "Provider = Microsoft. jet. oleDb.4.0; Data Source = D: \ Book \ Book.xls; Extended Properties = Excel 8.0 "; (1) Excel connection string: when reading an Excel file through OleDb, some fields are often read as null values. In fact, there is a value, because when reading a file, Excel usually uses the data type of the previous 10 rows as a reference, if they are different, some problems may occur. You can modify the connection string of an Excel file to forcibly read data of the string type. String xlsConnFormat = @ "Provider = Microsoft. jet. OLEDB.4.0; Data Source = '{0}'; Extended Properties = 'excel 8.0; HDR = NO; IMEX = 1 ';"; // 1. HDR indicates whether to use the first row as the data or column name, HDR = NO as the data, and HDR = YES as the column name; // 2. Use IMEX = 1 to read the hybrid data as the text type to avoid null values. Note: consider an Excel file as a database and a sheet as a table. Syntax: "SELECT * FROM [sheet1 $]". To use "[]" and "$", the default Excel table is used. The column name is "F1 ~ F99 ". If the message "unable to find the installable ISAM" is displayed ." The error is mainly caused by the Connection string. check whether there are spaces between Data sources and whether Extended Properties are correctly written. (2) set the connection string for reading Excel data of different versions using OLEDB. Using OLEDB, you can set the connection string to read the data in excel just like reading sqlserver, however, the connection strings of excel2003 and excel2007/2010 are different /// <summary> /// load data from Excel to DataTable /// </summary> /// <param name = "pathName"> Excel file name with path </param> /// <param name = "sheetName"> worksheet name </param> /// <param name =" tbContainer "> able </param> // <returns> </returns> public DataTable ExcelToDataTable (string pathName, string SheetName) {DataTable tbContainer = new DataTable (); string strConn = string. empty; if (string. isNullOrEmpty (sheetName) {sheetName = "Sheet1";} FileInfo file = new FileInfo (pathName); if (! File. exists) {throw new Exception ("the file does not exist");} string extension = file. extension; switch (extension) {case ". xls ": strConn =" Provider = Microsoft. jet. OLEDB.4.0; Data Source = "+ pathName +"; Extended Properties = 'excel 8.0; HDR = Yes; IMEX = 1; '"; break; case ". xlsx ": strConn =" Provider = Microsoft. ACE. OLEDB.12.0; Data Source = "+ pathName +"; Extended Properties = 'excel 12.0; HDR = Yes; IMEX = 1; '"; break; default: StrConn = "Provider = Microsoft. jet. OLEDB.4.0; Data Source = "+ pathName +"; Extended Properties = 'excel 8.0; HDR = Yes; IMEX = 1; '"; break ;} // link to Excel OleDbConnection cnnxls = new OleDbConnection (strConn); // read the table Sheet1 OleDbDataAdapter oda = new OleDbDataAdapter (string. format ("select * from [{0} $]", sheetName), cnnxls); DataSet ds = new DataSet (); // load the table content in Excel to the memory table! Oda. fill (tbContainer); return tbContainer;} here, when the file suffix is .xlsx (excel2007/2010), the connection string is "Provider = Microsoft. ACE. OLEDB.12.0 ;.... ", note that the red part in the middle is not" Jet ". (3). Net reading the xlsx file Excel2007. NET reading the xlsx file of Excel is the same as reading the old. xls file, which is read by Oledb and only the connection string is different. Microsoft is used to read xlsx. ace. oleDb.12.0; the procedure is as follows: public static DataTable GetExcelToDataTableBySheet (string FileFullPath, string SheetName) {// string strConn = "Provider = Microsoft. jet. oleDb.4.0; "+" data source = "+ FileFullPath +"; Extended Properties = 'excel 8.0; HDR = NO; IMEX = 1' "; // This connection can only operate on the top of excel2007(.xls) file string strConn = "Provider = Microsoft. ace. oleDb.12.0; "+" data source = "+ FileFullPath +"; Ex Tended Properties = 'excel 12.0; HDR = NO; IMEX = 1' "; // This connection can operate. xlsand. XLSX files OleDbConnection conn = new OleDbConnection (strConn); conn. open (); DataSet ds = new DataSet (); OleDbDataAdapter odda = new OleDbDataAdapter (string. format ("SELECT * FROM [{0}]", SheetName), conn); // ("select * from [Sheet1 $]", conn); odda. fill (ds, SheetName); conn. close (); return ds. tables [0];} when reading an Excel file, there may be multiple sheets in one file, so get Sh The eet name is very useful. The procedure is as follows: // obtain the public static String [] GetExcelSheetNames (string excelFile) {OleDbConnection objConn = null; System. data. dataTable dt = null; try {// string strConn = "Provider = Microsoft. jet. oleDb.4.0; "+" data source = "+ excelFile +"; Extended Properties = 'excel 8.0; HDR = NO; IMEX = 1' "; // This connection can only operate on the top of excel2007(.xls) file string strConn = "Provider = Microsoft. ace. oleDb.12.0; "+" data Source = "+ excelFile +"; Extended Properties = 'excel 12.0; HDR = NO; IMEX = 1' "; // you can operate the. xlsand. XLSX file objConn = new OleDbConnection (strConn); objConn. open (); dt = objConn. getOleDbSchemaTable (OleDbSchemaGuid. tables, null); if (dt = null) {return null;} String [] excelSheets = new String [dt. rows. count]; int I = 0; foreach (DataRow row in dt. rows) {excelSheets [I] = row ["TABLE_NAME"]. toString (); I ++ ;} Return excelSheets;} catch {return null;} finally {if (objConn! = Null) {objConn. Close (); objConn. Dispose ();} if (dt! = Null) {www.2cto.com dt. Dispose ();}}}