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(); } }
以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,同时也希望多多支持!
推荐阅读
-
asp.net实现导出DataTable数据到Word或者Excel的方法
-
【C#常用方法】2.DataTable(或DataSet)与Excel文件之间的导出与导入(使用NPOI)
-
asp.net 利用NPOI导出Excel通用类的方法
-
asp.net DataTable导出Excel自定义列名的方法
-
asp.net DataGridView导出到Excel的三个方法[亲测]
-
C#实现Json转DataTable并导出Excel的方法示例
-
Asp.Net Core实现Excel导出功能的实现方法
-
.net中将DataTable导出到word、Excel、txt、htm的方法
-
asp.net 利用NPOI导出Excel通用类的方法
-
【C#常用方法】2.DataTable(或DataSet)与Excel文件之间的导出与导入(使用NPOI)