asp.net DataTable导出Excel自定义列名的方法

1、添加引用npoi.dll

2、cs文件头部添加

　　using npoi.hssf.usermodel;
　　using npoi.ss.usermodel;
　　using system.io;

3、代码如下：

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机种信息" + datetime.now.tostring("yyyymmddhhmmss");
 exportexcel(dt, strfilename, "smt机种信息");
 }
 /// <summary>
 /// datatable导出excel
 /// </summary>
 /// <param name="dt">datatable数据源</param>
 /// <param name="strfilename">文件名</param>
 /// <param name="strsheetname">工作簿名</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 = "主机种,子机种,5e料号,产线类型,ct（s）,rate/12h,线上人力,线外人力,总人力,面别,刮刀下限,刮刀上限";
 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"://字符串类型
   datarow.createcell(j).setcellvalue(value);
   break;
   case "system.datetime"://日期类型
   system.datetime datev;
   system.datetime.tryparse(value, out datev);
   datarow.createcell(j).setcellvalue(datev);
   break;
   case "system.boolean"://布尔型
   bool boolv = false;
   bool.tryparse(value, out boolv);
   datarow.createcell(j).setcellvalue(boolv);
   break;
   case "system.int16"://整型
   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"://浮点型
   case "system.double":
   double doubv = 0;
   double.tryparse(value, out doubv);
   datarow.createcell(j).setcellvalue(doubv);
   break;
   case "system.dbnull"://空值处理
   datarow.createcell(j).setcellvalue("");
   break;
   default:
   datarow.createcell(j).setcellvalue("");
   break;
  }
  datarow.getcell(j).cellstyle = style;
  //设置宽度
  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();
 }
}

以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，同时也希望多多支持！