Import Excel into your data

There are two kinds of common ways:

1. by microsoft.jet.oledb.4.0 or microsoft.ace.oledb.12.0

microsoft.ace.oledb.12.0 need to install Accessdatatbaseengine

Http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe

 PublicDataTable exceltodatatable (stringStrexcelfilename,stringstrSheetName) {    stringPath = Server.MapPath (".")+"\\Common\\"+Strexcelfilename; stringCONN1 =@"Provider=Microsoft.Jet.OLEDB.4.0;Data source="+ Path +"; Extended Properties=excel 8.0;"; stringCONN2 =@"Provider=microsoft.ace.oledb.12.0;data source="+ Path +"; Extended properties= ' Excel 12.0; Hdr=yes;imex=1 '"; stringStrexcel =string. Format ("SELECT * FROM [{0}$]", strSheetName); DataSet DS=NewDataSet (); using(OleDbConnection conn =NewOleDbConnection (CONN2)) {Conn.        Open (); OleDbDataAdapter adapter=NewOleDbDataAdapter (STREXCEL, conn); Adapter.        Fill (ds, strSheetName); Conn.        Close (); returnds.    Tables[strsheetname]; }}//invocation mode: DataTable dt = exceltodatatable ("Report.xls", "List");

2. By Npoi 2.1.1

 PublicDataTable exceltodatatable (stringstrFileName,intSheetindex =0){    stringPath = Server.MapPath (".") +"\\Common\\"+strFileName; DataTable DT=NewDataTable (); Hssfworkbook Hssfworkbook=NULL; Xssfworkbook Xssfworkbook=NULL; stringFileext =path.getextension (Path); using(FileStream file =NewFileStream (Path, FileMode.Open, FileAccess.Read)) {        if(Fileext = =". xls") Hssfworkbook=Newhssfworkbook (file); Else if(Fileext = =". xlsx") Xssfworkbook=Newxssfworkbook (file); }    if(Hssfworkbook! =NULL) {Hssfsheet sheet=(Hssfsheet) Hssfworkbook.        Getsheetat (Sheetindex); if(Sheet! =NULL) {System.Collections.IEnumerator rows=sheet.            Getrowenumerator (); Hssfrow HeaderRow= (hssfrow) sheet. GetRow (0); intCellcount =Headerrow.lastcellnum;  for(intj =0; J < Cellcount; J + +) {Hssfcell cell=(Hssfcell) Headerrow.getcell (j); Dt. Columns.Add (cell.            ToString ()); }             for(inti = (sheet. Firstrownum +1); I <= sheet. Lastrownum; i++) {Hssfrow row=(hssfrow) sheet.                GetRow (i); DataRow DataRow=dt.                NewRow ();  for(intj = row. Firstcellnum; J < Cellcount; J + +)                {                    if(Row. Getcell (j)! =NULL) Datarow[j]=row. Getcell (j).                ToString (); } dt.            Rows.Add (DataRow); }        }    }    Else if(Xssfworkbook! =NULL) {Xssfsheet Xsheet=(Xssfsheet) Xssfworkbook.        Getsheetat (Sheetindex); if(Xsheet! =NULL) {System.Collections.IEnumerator rows=Xsheet.getrowenumerator (); Xssfrow HeaderRow= (Xssfrow) Xsheet.getrow (0); intCellcount =Headerrow.lastcellnum;  for(intj =0; J < Cellcount; J + +) {Xssfcell cell=(Xssfcell) Headerrow.getcell (j); Dt. Columns.Add (cell.            ToString ()); }             for(inti = (Xsheet.firstrownum +1); I <= xsheet.lastrownum; i++) {Xssfrow row=(Xssfrow) Xsheet.getrow (i); DataRow DataRow=dt.                NewRow ();  for(intj = row. Firstcellnum; J < Cellcount; J + +)                {                    if(Row. Getcell (j)! =NULL) Datarow[j]=row. Getcell (j).                ToString (); } dt.            Rows.Add (DataRow); }        }    }    returnDT;}//invocation mode: DataTable dt = exceltodatatable ("Report.xls");

Import Excel into your data