asp. net DataTable Method for Exporting Excel Custom Column Names

1. Add references to NPOI. dll

2. Add 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["LocalConnectionString"].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 pcbapn = '{0}' order by smtpicsmdl.model asc,smtpicsmdl.submodel asc,PrdType asc", this.txtMdmitem.Text.Trim());
 DataTable dt = sql.Query(strSql);
 string strFileName = "SMT Model information " + DateTime.Now.ToString("yyyyMMddHHmmss");
 ExportExcel(dt, strFileName, "SMT Model information ");
 }
 /// <summary>
 /// DataTable Export Excel
 /// </summary>
 /// <param name="dt">datatable Data source </param>
 /// <param name="strFileName"> Filename </param>
 /// <param name="strSheetName"> Workbook name </param>
 public void ExportExcel(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 = VerticalAlignment.Center;
 
 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
 string strColumns = " Host species , Sub-model ,5E Material number , Production line type ,CT ( S ) ,rate/12H, Online manpower , Off-line manpower , Total manpower , Face parting , Scraper lower limit , Scraper upper limit ";
 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 type 
   bool boolV = false;
   bool.TryParse(Value, out boolV);
   dataRow.CreateCell(j).SetCellValue(boolV);
   break;
   case "System.Int16":// Integer type 
   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 point type 
   case "System.Double":
   double doubV = 0;
   double.TryParse(Value, out doubV);
   dataRow.CreateCell(j).SetCellValue(doubV);
   break;
   case "System.DBNull":// Null value processing 
   dataRow.CreateCell(j).SetCellValue("");
   break;
   default:
   dataRow.CreateCell(j).SetCellValue("");
   break;
  }
  dataRow.GetCell(j).CellStyle = style;
  // Set Width 
  sheet.SetColumnWidth(j, (Value.Length + 10) * 256);
  }
 }
 MemoryStream ms = new MemoryStream();
 book.Write(ms);
 Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8)));
 Response.BinaryWrite(ms.ToArray());
 Response.End();
 book = null;
 ms.Close();
 ms.Dispose();
 }
}