Use Nopi to upload Excel, convert to DataTable, use SqlBulkCopy to write data to database table, configure add default column and value, write data to corresponding database field

Front Desk Related:

<! DOCTYPE HTML PUBLIC "-//W3C//DTD XHTML 1.0 transitional//en" "Http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd ">
   Background 
 
Using System.data;using system.io;using Npoi. Ss. Usermodel;using Npoi. XSSF. Usermodel;using Npoi. HSSF.        Usermodel;using system.data.sqlclient;using maticsoft.dbutility; <summary>///upload the Excel file to the server and insert the database///</summary>//<param name= "Sender" >&lt ;/param>//<param name= "E" ></param> protected void Buttonupclick (object sender, EventArgs E            ) {int id = converthelper.cint0 (hidid.text); if (id = = 0) {try {#region background to verify file format V AR filename = this.                    Fileupload1.filename; if (string. IsNullOrEmpty (filename) {FineUI.Alert.ShowInParent ("Please select the uploaded file!)                        ", FineUI.MessageBoxIcon.Warning);                    Return } if (! ( FileName. IndexOf (". xls") > 0 | | FileName.        IndexOf (". xlsx") > 0)) {                FineUI.Alert.ShowInParent ("file format is incorrect, please upload Excel file", FineUI.MessageBoxIcon.Warning);                    Return } #endregion//upload path, string path = Server.MapPath ("~/") + "Webmana                    Ge\\basetotalweb\\uploadweb\\filetmp\\ ";                    Save the file to a temporary path under string timeStamp = System.DateTime.Now.ToString ("Yyyymmddhhmmssfff"); String retstr = Uploadfiles.fileupload (path, this.                    FileUpload1, TimeStamp); if (Retstr.equals ("Upload failed")) {FineUI.Alert.ShowInParent ("service busy, please retry later", Fineui.messa                        geboxicon.warning);                    Return }//Get datatable datatable dt = this. Exceltodatatable (path + TimeStamp + this.                    Fileupload1.filename, True); Delete the temporary file Dirfilehelper.deletefile (path + TimeStamp + this.  Fileupload1.filename);                  if (dt = = null) {FineUI.Alert.ShowInParent ("Cannot get file contents", Fineui .                        MESSAGEBOXICON.ERROR);                    Return                    }//Upload order number DateTime dtime = System.DateTime.Now; String Strno = "Up" + basetotalbll.getinstence ().                    Nextcheckcard (Dtime); #region Add the appropriate column//Add a column upordernum and assign a value DataColumn DC = new DataColumn ("Upordernum", Typ                    EOF (string)); dc.                    DefaultValue = Strno; Dt.                    Columns.Add (DC);                    Add Upman upload person DataColumn dcupman = new DataColumn ("Upman", typeof (String)); Dcupman.defaultvalue = Onlineusersbll.getinstence (). Getonlineusersmodel (). manager_loginname;//gets the system current user name DT.                    Columns.Add (Dcupman);   Add isOk whether to use DataColumn Dcisok = new DataColumn ("IsOk", typeof (int));                 Dcisok.defaultvalue = 0; Dt.                    Columns.Add (Dcisok); Add Cardtype card type 0 for Set 1 1-year card 2, 2-year card 3, 3-year card DataColumn Dccardtype = new DataColumn ("Cardtype", typeof (int)                    );                    Dccardtype.defaultvalue = 0; Dt.                    Columns.Add (Dccardtype);                    Add Isbegin whether to activate DataColumn Dcisbegin = new DataColumn ("Isbegin", typeof (int));                    Dcisbegin.defaultvalue = 0; Dt.                    Columns.Add (Dcisbegin);                    Add Isdown whether to export DataColumn Dcisdown = new DataColumn ("Isdown", typeof (int));                    Dcisdown.defaultvalue = 0; Dt.                    Columns.Add (Dcisdown);                    Add Isdel whether to delete DataColumn Dcisdel = new DataColumn ("Isdel", typeof (int));                    Dcisdel.defaultvalue = 0; Dt.                    Columns.Add (Dcisdel); Add Uploader Createman DatacOlumn Dccreateman = new DataColumn ("Createman", typeof (String)); Dccreateman.defaultvalue = Onlineusersbll.getinstence (). Getonlineusersmodel ().                    Manager_loginname; Dt.                    Columns.Add (Dccreateman);                    #endregion SqlTransaction tran = null; using (SqlConnection conn = new SqlConnection (dbhelpersql.connectionstring)) {C Onn. Open ();//Turn on link using (TRAN = conn. BeginTransaction ()) {using (SqlBulkCopy copy = new SqlBulkCopy (conn, Sq Lbulkcopyoptions.keepidentity, Tran)) {copy.                                Bulkcopytimeout = 60; Copy. batchsize = dt.                                Rows.Count; Copy. DestinationTableName = "slwifi.dbo.                                [T_base_cardlist] "; Copy. Columnmappings.add ("Upordernum", "UpOrdernum "); Copy.                                Columnmappings.add ("Iccid", "CARDCTCC"); Copy.                                Columnmappings.add ("Access number", "cardaccess"); Copy.                                Columnmappings.add ("Upman", "Upman"); Copy.                                Columnmappings.add ("IsOk", "isOk"); Copy.                                Columnmappings.add ("Cardtype", "Cardtype"); Copy.                                Columnmappings.add ("Isbegin", "Isbegin"); Copy.                                Columnmappings.add ("Isdown", "Isdown"); Copy.                                Columnmappings.add ("Isdel", "Isdel"); Copy.                                Columnmappings.add ("Createman", "Createman"); Copy.                                WriteToServer (DT);                                Tran.commit (); Copy.                            Close (); }} conn.                    Close (); }} catch(Exception ex) {string str = ex.                    ToString ();                    FineUI.Alert.ShowInParent ("Upload failed", FineUI.MessageBoxIcon.Error);                Return                } FineUI.Alert.ShowInParent ("Upload succeeded", FineUI.MessageBoxIcon.Information);            Return  }}///<summary>//import Excel into a DataTable///</summary>//<param Name= "FilePath" >excel path </param>///<param Name= "Iscolumnname" > First row is column name </param>//&L        t;returns> return datatable</returns> public DataTable exceltodatatable (string filePath, bool iscolumnname)            {datatable datatable = null;            FileStream fs = null;            DataColumn column = null;            DataRow datarow = null;            Iworkbook workbook = null;            Isheet sheet = null;            IRow row = null;            Icell cell = null;    int startrow = 0;        try {using (fs = File.openread (FilePath)) {//2007 version                    if (Filepath.indexof (". xlsx") > 0) workbook = new Xssfworkbook (FS); 2003 version Else if (Filepath.indexof (". xls") > 0) workbook = new H                    Ssfworkbook (FS); if (workbook! = null) {sheet = workbook.                        Getsheetat (0);//Read the first sheet, of course, you can also iterate through each sheet datatable = new DataTable (); if (sheet! = null) {int rowCount = sheet.                                lastrownum;//Total Rows if (RowCount > 0) { IRow firstrow = sheet. GetRow (0);//First line int cellcount = firstrow.lastcellnum;//number of columns//                       To build a column for a DataTable         if (iscolumnname) {StartRow = 1;//If the first row is a column name, from                                    Two lines start reading for (int i = firstrow.firstcellnum; i < cellcount; ++i)                                        {cell = Firstrow.getcell (i); if (cell! = null) {if (cell. Stringcellvalue! = null) {Colum n = new DataColumn (cell.                                                Stringcellvalue);                                            DATATABLE.COLUMNS.ADD (column);                                }                                        }                                    }                                } else {for (int i = FirstRow. Firstcellnum; i < Cellcount; ++i) {column = new DataColumn ("column" + (i + 1                                        ));                                    DATATABLE.COLUMNS.ADD (column); }}//Fill line for (int i = STARTR ow I <= RowCount; ++i) {row = sheet.                                    GetRow (i);                                    if (row = = null) continue;                                    DataRow = Datatable.newrow (); for (int j = row. Firstcellnum; J < Cellcount; ++J) {cell = row.                                        Getcell (j);                        if (cell = = null) {Datarow[j] = "";                } else {                                            Celltype (Unknown = -1,numeric = 0,string = 1,formula = 2,blank = 3,boolean = 4,error = 5,) Switch (cell.                                                     Celltype) {case Celltype.blank:                                                    DATAROW[J] = "";                                                Break Case CellType.Numeric:short format = cell.                                                    Cellstyle.dataformat; Processing of the time format (2015.12.5, 2015/12/5, 2015-12-5, etc.) if (format = = | | format = = 31 | | Format = = 57 | | Format = = (+) datarow[j] = cell. DatecellvAlue; else datarow[j] = cell.                                                    Numericcellvalue;                                                Break Case Celltype.string:datarow[j] = cell.                                                    Stringcellvalue;                                            Break }}} Datatab Le.                                Rows.Add (DataRow); }}}}} return Datata            ble } catch (Exception) {if (fs! = NULL) {fs.                Close ();            } return null;     }        }
Use Nopi to upload Excel, convert to DataTable, use SqlBulkCopy to write data to database table, configure add default column and value, write data to corresponding database field