C # Summary of methods for importing data from Excel files

Before importing, you need to save the uploaded files to the server, so avoid repeatedly writing the code, first post the upload file and save to the server to specify the path of the code.

protected voidBtnimport_click (Objectsender, EventArgs e) {Random random=NewRandom (); Importclass Import=NewImportclass (); //Save the virtual path to the file            stringPath ="import/"; //Gets the selected file name            stringFileName =Fileupload1.filename; //get file extension name            stringFileext =path.getextension (fileName); //generate a new file name            stringNewName = DateTime.Now.ToString ("yyyymmddhhmmssfff") + random. Next (0,9999).            ToString (); //gets the physical path of the specified virtual machine path            stringFullPath =HttpContext.Current.Server.MapPath (path); //Upload File Save path            stringSavepath = FullPath + NewName +Fileext; //save file to serverFileupload1.saveas (Savepath); Try            {                //get the imported dataDataSet ds =Import.importexcel (Savepath); if(ds! =NULL&& ds. Tables.count >0)                {                    //Here you can write the method of inserting the database                }            }            Catch(Exception ex) {Throw; }        }

View Code

First type: OLE DB

 PublicDataSet Importexcel (stringFilePath) {DataSet DS=NULL;            OleDbConnection Conn; stringstrconn =string.            Empty; stringSheetName =string.            Empty; Try            {                //Excel 2003 version connection stringstrconn ="Provider=Microsoft.Jet.OLEDB.4.0;Data source="+ FilePath +"; Extended properties= ' Excel 8.0; Hdr=yes; imex=1; '"; Conn=NewOleDbConnection (strconn); Conn.            Open (); }            Catch            {                //Excel 2007 or later connection stringstrconn ="Provider=microsoft.ace.oledb.12.0;data source="+ FilePath +"; Extended properties= ' Excel 12.0; hdr=yes;imex=1; '"; Conn=NewOleDbConnection (strconn); Conn.            Open (); }            //get all the sheet tablesDataTable dtsheetname = conn. GetOleDbSchemaTable (OleDbSchemaGuid.Tables,New Object[] {NULL,NULL,NULL,"Table" }); DS=NewDataSet ();  for(inti =0; i < DtSheetName.Rows.Count; i++) {DataTable dt=NewDataTable (); Dt. TableName="Table"+i.tostring (); //Get table nameSheetName = dtsheetname.rows[i]["table_name"].                ToString (); OleDbDataAdapter Oleda=NewOleDbDataAdapter ("select * FROM ["+ SheetName +"]", conn); Oleda.                Fill (DT); Ds.            Tables.add (DT); }            returnds; }

View Code

In addition to the reading process is not very flexible, this kind of reading is also a disadvantage, when the amount of Excel data is very large. Can be very memory-intensive, throwing out memory-overflow exceptions when memory is not enough, but generally applicable.

The second type: Microsoft.Office.Interop.Excel.dll

 PublicDataSet Importexcel (stringFilePath) {DataSet DS=NULL; DataTable DT=NULL; Microsoft.Office.Interop.Excel.Application Excel=NewMicrosoft.Office.Interop.Excel.Application (); Microsoft.Office.Interop.Excel.Workbook Workbook=NULL; Microsoft.Office.Interop.Excel.Worksheet Worksheet=NULL; Microsoft.Office.Interop.Excel.Sheets Sheets=NULL; Microsoft.Office.Interop.Excel.Range Range=NULL; ObjectMissing =System.Reflection.Missing.Value; Try            {                if(Excel = =NULL)                {                    return NULL; }                //Open Excel FileWorkbook =Excel. Workbooks.Open (FilePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missin                g, missing, missing, missing); //get all the sheet tablesSheets =workbook.                worksheets; DS=NewDataSet ();  for(inti =1; I <= sheets. Count; i++)                {                    //get the first tableWorksheet =(Microsoft.Office.Interop.Excel.Worksheet) Sheets.get_item (i); intRowCount =worksheet.                    UsedRange.Rows.Count; intColCount =worksheet.                    UsedRange.Columns.Count; intRowIndex =1;//starting behavior 1                    intColindex =1;//Start column 1DataColumn DC; DT=NewDataTable (); Dt. TableName="Table"+i.tostring (); //Read Column name                     for(intj =0; J < ColCount; J + +) {Range= worksheet. Cells[rowindex, Colindex +J]; DC=NewDataColumn (); dc. DataType= Type.GetType ("System.String"); dc. ColumnName=range. Text.tostring ().                        Trim (); //Adding Columnsdt.                    Columns.Add (DC); }                    //Reading row Data                     for(intK =1; K < RowCount; k++) {DataRow Dr=dt.                        NewRow ();  for(intL =0; L < ColCount; l++) {Range= worksheet. Cells[rowindex + K, Colindex +L]; //use range. Value.tostring (); or range. Value2.tostring (); or range. Text.tostring (); Can get the value of a cellDR[L] =range.                        Text.tostring (); } dt. Rows.Add (Dr.                    ItemArray); } ds.                Tables.add (DT); }            }            Catch(Exception ex) {Throw; }            returnds; }

View Code

This method first needs to be installed with Office Excel, and it is a cell read for cells, so performance will be poor.

Not to be continued ...

C # Summary of methods for importing data from Excel files