欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-03 15:07:50
1、添加引用npoi.dll 2、cs文件头部添加   using npoi.hssf.usermodel;   using npoi.ss.usermode...

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();
 }
}

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