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

1、添加引用NPOI.dll

2、cs文件头部添加

using NPOI.HSSF.UserModel;

using NPOI.SS.UserModel;

using System.IO;

3、代码如下：

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

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

}

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

原文链接：http://www.cnblogs.com/lengzhan/p/6042911.html