asp.netDataTable如何導(dǎo)出Excel自定義列名-創(chuàng)新互聯(lián)
這篇文章主要為大家展示了“asp.net DataTable如何導(dǎo)出Excel自定義列名”，內(nèi)容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領(lǐng)大家一起研究并學(xué)習(xí)一下“asp.net DataTable如何導(dǎo)出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機(jī)種信息" + DateTime.Now.ToString("yyyyMMddHHmmss");
ExportExcel(dt, strFileName, "SMT機(jī)種信息");
}
///
/// DataTable導(dǎo)出Excel
///
/// datatable數(shù)據(jù)源
/// 文件名
/// 工作簿名
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 = "主機(jī)種,子機(jī)種,5E料號,產(chǎn)線類型,CT（S）,rate/12H,線上人力,線外人力,總?cè)肆?面別,刮刀下限,刮刀上限";
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;
//設(shè)置寬度
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如何導(dǎo)出Excel自定義列名”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學(xué)習(xí)更多知識，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！
當(dāng)前題目：asp.netDataTable如何導(dǎo)出Excel自定義列名-創(chuàng)新互聯(lián)
網(wǎng)站路徑：http://m.jiaotiyi.com/article/dpsjgp.html