MVC Import, exporting Excel

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