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" >< ;/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