asp.net DataTable导出Excel自定义列名的方法
1、添加引用NPOI.dll
2、cs文件头部添加
usingNPOI.HSSF.UserModel; usingNPOI.SS.UserModel; usingSystem.IO;
3、代码如下：
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Web;
usingSystem.Web.UI;
usingSystem.Web.UI.WebControls;
usingSystem.Configuration;
usingWSC.Framework;
usingSystem.Data;
usingWSC.Common;
usingNPOI.HSSF.UserModel;
usingNPOI.SS.UserModel;
usingSystem.IO;
publicpartialclassWorkManage_SMT_SMTMaintain:WSC.FramePage
{
SQLHelpersql=newSQLHelper(ConfigurationManager.AppSettings["LocalConnectionString"].ToString());
protectedvoidPage_Load(objectsender,EventArgse)
{
if(!IsPostBack)
{
}
}
protectedvoidbtnReport_Click(objectsender,EventArgse)
{
stringstrSql=string.Format(@"selectsmtpicsmdl.model,smtmdl.submodel,pcbapn,PrdType,cycle,cast((12*3600/cycle)asint)as'rate',onlineMan,offlineMan,reserve3,ptype_desc,minsqg,maxsqgfromsmtmdlleftjoinsmtpicsmdlonsmtpicsmdl.submodel=smtmdl.submodelwherepcbapn='{0}'orderbysmtpicsmdl.modelasc,smtpicsmdl.submodelasc,PrdTypeasc",this.txtMdmitem.Text.Trim());
DataTabledt=sql.Query(strSql);
stringstrFileName="SMT机种信息"+DateTime.Now.ToString("yyyyMMddHHmmss");
ExportExcel(dt,strFileName,"SMT机种信息");
}
///<summary>
///DataTable导出Excel
///</summary>
///<paramname="dt">datatable数据源</param>
///<paramname="strFileName">文件名</param>
///<paramname="strSheetName">工作簿名</param>
publicvoidExportExcel(DataTabledt,stringstrFileName,stringstrSheetName)
{
HSSFWorkbookbook=newHSSFWorkbook();
ISheetsheet=book.CreateSheet(strSheetName);
IRowheaderrow=sheet.CreateRow(0);
ICellStylestyle=book.CreateCellStyle();
style.Alignment=HorizontalAlignment.Center;
style.VerticalAlignment=VerticalAlignment.Center;
HSSFRowdataRow=(HSSFRow)sheet.CreateRow(0);
stringstrColumns="主机种,子机种,5E料号,产线类型,CT（S）,rate/12H,线上人力,线外人力,总人力,面别,刮刀下限,刮刀上限";
string[]strArry=strColumns.Split(',');
for(inti=0;i<strArry.Length;i++)
{
dataRow.CreateCell(i).SetCellValue(strArry[i]);
dataRow.GetCell(i).CellStyle=style;
}
for(inti=0;i<dt.Rows.Count;i++)
{
dataRow=(HSSFRow)sheet.CreateRow(i+1);
for(intj=0;j<dt.Columns.Count;j++)
{
stringValueType="";
stringValue="";
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.DateTimedateV;
System.DateTime.TryParse(Value,outdateV);
dataRow.CreateCell(j).SetCellValue(dateV);
break;
case"System.Boolean"://布尔型
boolboolV=false;
bool.TryParse(Value,outboolV);
dataRow.CreateCell(j).SetCellValue(boolV);
break;
case"System.Int16"://整型
case"System.Int32":
case"System.Int64":
case"System.Byte":
intintV=0;
int.TryParse(Value,outintV);
dataRow.CreateCell(j).SetCellValue(intV);
break;
case"System.Decimal"://浮点型
case"System.Double":
doubledoubV=0;
double.TryParse(Value,outdoubV);
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);
}
}
MemoryStreamms=newMemoryStream();
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();
}
}
以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，同时也希望多多支持毛票票！