About OLE DB's read to Excel

These two days the project needs to retrieve the contents of Excel, so I studied, not much to say, I directly paste code 1. First, connect to Excel.

 Public DataTable Searchsheettodt (string strsearch, String sheetname) {//file path string Pat            h = Server.MapPath ("~/content/custom/excel1.xlsx"); Connection Table String excelconnection = "provider=microsoft.ace.oledb.12.0;" + "Data source=" + @path + "; Extended properties= ' Excel 12.0;            Hdr=yes;imex=2;importmixedtypes=text ' "; using (OleDbConnection ole_conn = new OleDbConnection (excelconnection)) {Ole_conn.                Open (); using (OleDbCommand ole_cmd = Ole_conn.                    CreateCommand ()) {ole_cmd.commandtext = Strsearch;                    OleDbDataAdapter adapter = new OleDbDataAdapter (ole_cmd);                    DataSet ds = new DataSet (); Adapter.                    Fill (ds, SheetName);//sheetname is the sheet table name in Excel datatable dt = new DataTable (); DT = ds.                    Tables[0];                return DT; }            }        }

Simply explain the connection string
Hdr=yes, this means that the first row is the title, not as data use, if used Hdr=no, then the first row is not the title, as data to use. The system default is Yes
IMEX parameters because different modes represent different read and write behaviors:
When imex=0 is "Export mode", the Excel file opened by this mode can only be used for "write" purposes.
When Imex=1 is "Import Mode", the Excel file opened in this mode can only be used for "read" purposes.
When imex=2 is "connected mode", the Excel file opened in this mode can support both "read" and "write" purposes.
provider=microsoft.ace.oledb.12.0; This description is excel2007 above version, XLS file is generally 03 version, Provider=Microsoft.Jet.OLEDB.4.0;
Details the connection character according to the version how to write can refer to the website: http://www.connectionstrings.com/excel/
When writing may appear "can not find installable ISAM" This time the situation is more likely to be your connection statement Quejinshaoliang, you carefully check extended properties= ' Excel 12.0; Hdr=yes;imex=2 ' "This is not a semicolon or something, I was wrong because of the data source this, less a space, written datasource, looking for a long time. If you're sure it's true, check if the computer has Excel, reinstall ISAM. Specific steps: In the Run dialog box, enter the following: Regsvr32 C:\WINDOWS\system32\msexcl40.dll and then enter.
After that, there's no problem, it's connected. Just look at the action statement

12345678910111213141516

public boolSearchIndexSheet(stringk)        {            stringstrIndex = "select * from [sheet1$]";//这里sheet1就是你的表名是什么就写什么，例如学生表就是 学生表$            stringsheetName = "[sheet1$]";            DataTable indexdt = newDataTable();            //读取excel            indexdt = SearchSheetToDT(strIndex, sheetName);            //indexdt.DefaultView.RowFilter = "列名1=‘上证A股指数‘";            //查询条件            varreslut = indexdt.Select("列名1=‘"+ k + "‘ or 列名2=‘"+ k  + "‘").ToList();           if(reslut.Count != 0)            {                returntrue;            }            elsereturnfalse;        }

Some people may write string strindex = "SELECT * from [sheet1$] where [column name 1]= '" +k "'"; this is OK, but sometimes it's an error.
For example, the data types in this error standard expression do not match, not because your statement was wrong, but because there is an expression in Excel, so I took the data out and put it in a DataTable.
This is basically the case with Excel reading.
This is imported, export has not been written.

<summary>        ///Query document-field return query        ///</summary>//<param name= "K" ></param>/ <returns></returns> public        list<datarow> searchmatchsheet (string k)        {            string Strmatch = "SELECT * FROM [<span style=" line-height:19.2000007629395px; " > table name 1</span>$] ";            string sheetname = "[table name 1$]";            DataTable MATCHDT = new DataTable ();            MATCHDT = Searchsheettodt (Strmatch, sheetname);            var reslut = Matchdt. Select ("Column name 1= '" + K  + "'"). ToList ();            return reslut;        }

About OLE DB reading to Excel (GO)