Recent major Import Excel
Check the code on the Internet
PublicFileresult Downloadexceljizuchaxungenrenxiaofeijilu () {DataTable dt= (DataTable) session["DataTable"];//get DataTable data that needs to be exported//create an object for an Excel fileNpoi. HSSF. Usermodel.hssfworkbook book =NewNpoi. HSSF. Usermodel.hssfworkbook (); //Add a sheetNpoi. Ss. Usermodel.isheet Sheet1 = Book. Createsheet ("Sheet1"); //Add the header of the first row to Sheet1Npoi. Ss. Usermodel.irow Row1 = Sheet1. CreateRow (0); //Row1. Rowstyle.fillbackgroundcolor = ""; for(inti =0; i < dt. Columns.count; i++) {Row1. Createcell (i). Setcellvalue (dt. Columns[i]. ColumnName); } //progressively writing data to Sheet1 individual rows for(inti =0; i < dt. Rows.Count; i++) {Npoi. Ss. Usermodel.irow rowtemp= Sheet1. CreateRow (i +1); for(intj =0; J < dt. Columns.count; J + +) {rowtemp. Createcell (j). Setcellvalue (dt. ROWS[I][J]. ToString (). Trim ()); } } stringStrdate = DateTime.Now.ToString ("YYYYMMDDHHMMSS");//Get current Time//Write to clientSystem.IO.MemoryStream ms =NewSystem.IO.MemoryStream (); Book. Write (MS); Ms. Seek (0, Seekorigin.begin); returnFile (MS,"Application/vnd.ms-excel", Strdate +"Excel.xls"); }
First export the Npoi plugin used
Returns a Fileresult
The first step is to get the DataTable data that needs to be exported
Then create an Excel file object
Add one more sheet
Add the header of the first row to sheet
The data is then progressively written to Sheet1 rows, and finally to the client
============================================
Now is the function I need to import
There is a <input type= "file" Name= "file1" id= "file" in the View page >
This is the Excel file you choose to import.
And then there's a <button type= "submit" > This button is to confirm the import commit action
Code written in the controller:
First get to the view passed files httppostfilebase file =request. files["File1"];
Httppostfilebase represents separate access to files that have been uploaded by the client
Stream Streamfile=file.inputstream; Gets a stream object that points to an uploaded file that is ready to read the contents of the file.
The import file is also used by the Npoi plugin.
Download Npoi plugin, there are five DLLs, all to reference, because it contains the upload ". xls" and upload ". xlsx"
Where Hssfworkbook is used for the XLS file import class.
Xssfworkbook is used for the xlsx file import class.
I made my own judgment when importing, and when different types of files were executed, the code was roughly the same, but the classes were different.
/// <summary> ///Excel Import/// </summary> /// <returns></returns> PublicDataTable Importexcelfile (stringFilePath) { //Initializing Information #regionhttppostedfilebase file= request.files["file1"]; Stream Streamfile=file. InputStream; DataTable DT=NewDataTable (); varHouzhuiname =path.getextension (file. FileName); Try { if(Houzhuiname = =". xls") {Hssfworkbook Hssfworkbook=NewHssfworkbook (Streamfile); DT=imexport (DT, hssfworkbook); } Else{Xssfworkbook Hssfworkbook=NewXssfworkbook (Streamfile); DT=imexport (DT, hssfworkbook); } } Catch { } #endregiontjpackagerequest Request=Newtjpackagerequest {creattime=DateTime.Now,}; This. HOSSERVICE.IMPORTDB (dt, request); returnDT; }
The Imexport () is a method for importing different files.
#regionTwo different versions of operations Excel/// <summary> ///version of Excel2007, the extension is. xlsx/// </summary> /// <param name= "DT" ></param> /// <param name= "Hssfworkbook" ></param> /// <returns></returns> Private StaticDataTable Imexport (DataTable DT, Xssfworkbook Hssfworkbook) {Npoi. Ss. Usermodel.isheet sheet= Hssfworkbook. Getsheetat (0); System.Collections.IEnumerator rows=sheet. Getrowenumerator (); for(intj =0; J < (sheet. GetRow (0). Lastcellnum); J + +) { //dt. Columns.Add (Convert.tochar (((int) ' A ') + j). ToString ());] Dt. Columns.Add (sheet. GetRow (0). CELLS[J]. ToString ()); } while(rows. MoveNext ()) {Xssfrow row=(xssfrow) rows. Current; DataRow Dr=dt. NewRow (); for(inti =0; I < row. Lastcellnum; i++) {Npoi. Ss. Usermodel.icell Cell=row. Getcell (i); if(Cell = =NULL) {Dr[i]=NULL; } Else{Dr[i]=cell. ToString (); }} dt. Rows.Add (DR); } dt. Rows.removeat (0); returnDT; } /// <summary> ///Excel2003 Previous (including 2003) versions/// </summary> /// <param name= "DT" ></param> /// <param name= "Hssfworkbook" ></param> /// <returns></returns> Private StaticDataTable Imexport (DataTable DT, Hssfworkbook Hssfworkbook) {Npoi. Ss. Usermodel.isheet sheet= Hssfworkbook. Getsheetat (0); System.Collections.IEnumerator rows=sheet. Getrowenumerator (); for(intj =0; J < (sheet. GetRow (0). Lastcellnum); J + +) {dt. Columns.Add (sheet. GetRow (0). CELLS[J]. ToString ()); //dt. Columns.Add (Convert.tochar (((int) ' A ') + j). ToString ()); } while(rows. MoveNext ()) {Hssfrow row=(hssfrow) rows. Current; DataRow Dr=dt. NewRow (); for(inti =0; I < row. Lastcellnum; i++) {Npoi. Ss. Usermodel.icell Cell=row. Getcell (i); if(Cell = =NULL) {Dr[i]=NULL; } Else{Dr[i]=cell. ToString (); }} dt. Rows.Add (DR); } dt. Rows.removeat (0); returnDT; } #endregion
The above code is a bit repetitive, but the processing class is different. I wrote it two times.
Importdb () This method is the operation of the database operation. In the previous steps, the contents of the Excel file have been stored in a DataTable, followed by adding data from the DataTable in the database.
In the import operation, there is a last sentence of dt. Rows.removeat (0); This sentence is to delete the first line of the header in the Excel file, leaving only the data.
In the DAL is the data in the Loop DT is added.
MVC Import, exporting Excel