Asp.net DataTable: How to export a Custom column name in Excel, datatableexcel

Asp.net DataTable: How to export a Custom column name in Excel, datatableexcel

1. Add reference NPOI. dll

2. Add the cs File Header

　　using NPOI.HSSF.UserModel;　　using NPOI.SS.UserModel;　　using System.IO;

3. The Code is as follows:

Using System; using System. collections. generic; using System. web; using System. web. UI; using System. web. UI. webControls; using System. configuration; using WSC. framework; using System. data; using WSC. common; using NPOI. HSSF. userModel; using NPOI. SS. userModel; using System. IO; public partial class WorkManage_SMT_SMTMaintain: WSC. framePage {SQLHelper SQL = new SQLHelper (ConfigurationManager. appSettings ["LocalConn EctionString "]. ToString (); protected void Page_Load (object sender, EventArgs e) {if (! IsPostBack) {}} protected void btnReport_Click (object sender, EventArgs e) {string strSql = string. format (@ "select smtpicsmdl. model, smtmdl. submodel, pcbapn, PrdType, cycle, cast (12*3600/cycle) as int) as 'rate', onlineMan, offlineMan, reserve3, ptype_desc, minsqg, maxsqg from smtmdl left join smtpicsmdl on smtpicsmdl. submodel = smtmdl. submodel where partition APN = '{0}' order by smtpicsmdl. model asc, smtpicsmdl. Submodel asc, PrdType asc ", this.txt Mdmitem. text. trim (); DataTable dt = SQL. query (strSql); string strFileName = "SMT host information" + DateTime. now. toString ("yyyyMMddHHmmss"); ExportExcel (dt, strFileName, "SMT host information ");} /// <summary> /// DataTable export Excel /// </summary> /// <param name = "dt"> datatable data source </param> // <param name = "strFileName"> file name </param> // <param name = "strSheetName"> workbook name </param> public void Ex PortExcel (DataTable dt, string strFileName, string strSheetName) {HSSFWorkbook book = new HSSFWorkbook (); ISheet sheet = book. createSheet (strSheetName); IRow headerrow = sheet. createRow (0); ICellStyle style = book. createCellStyle (); style. alignment = HorizontalAlignment. center; style. verticalAlignment = verticalignment. center; HSSFRow dataRow = (HSSFRow) sheet. createRow (0); string strColumns =" Host type, sub-host type, 5E item number, production line type, CT (S), rate/12 H, online manpower, offline labor, total labor, face, minimum scraper, upper Limit of scraper "; string [] strArry = strColumns. split (','); for (int I = 0; I <strArry. length; I ++) {dataRow. createCell (I ). setCellValue (strArry [I]); dataRow. getCell (I ). cellStyle = style;} for (int I = 0; I <dt. rows. count; I ++) {dataRow = (HSSFRow) sheet. createRow (I + 1); for (int j = 0; j <dt. columns. count; j ++) {string ValueType = ""; string Value = ""; If (dt. Rows [I] [j]. ToString ()! = Null) {ValueType = dt. rows [I] [j]. getType (). toString (); Value = dt. rows [I] [j]. toString ();} switch (ValueType) {case "System. string ": // String type dataRow. createCell (j ). setCellValue (Value); break; case "System. dateTime ": // Date type System. dateTime dateV; System. dateTime. tryParse (Value, out dateV); dataRow. createCell (j ). setCellValue (dateV); break; case "System. boolean ": // Boolean bool boolV = false; bool. tryParse (Value, out boolV); dataRow. createCell (j ). setCellValue (boolV); break; case "System. int16 ": // integer case" System. int32 ": case" System. int64 ": case" System. byte ": int intV = 0; int. tryParse (Value, out intV); dataRow. createCell (j ). setCellValue (intV); break; case "System. decimal ": // floating case" System. double ": double doubV = 0; double. tryParse (Value, out doubV); dataRow. createCell (j ). setCellValue (doubV); break; case "System. DBNull ": // processing dataRow for null values. createCell (j ). setCellValue (""); break; default: dataRow. createCell (j ). setCellValue (""); break;} dataRow. getCell (j ). cellStyle = style; // set the width of sheet. setColumnWidth (j, (Value. length + 10) * 256);} MemoryStream MS = new MemoryStream (); book. write (MS); Response. addHeader ("Content-Disposition", string. format ("attachment; filename1_1_02.16.xls", HttpUtility. urlEncode (strFileName, System. text. encoding. UTF8); Response. binaryWrite (ms. toArray (); Response. end (); book = null; ms. close (); ms. dispose ();}}

The above is all the content of this article. I hope this article will help you in your study or work. I also hope to provide more support to the customer's home!