asp.net DataTable导出Excel 自定义列名
2018-06-18 01:33:05来源:未知 阅读 ()
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(); } }
作者:小路 QQ:2490024434
出处:http://www.cnblogs.com/lengzhan/
本文版权归【冷战】和博客园所有,欢迎转载收藏,未经作者同意须保留此段声明,否则保留追究法律责任的权利。
标签:
版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有
- asp.net强大后台:learun混合工作流框架规范 2018-12-04
- DataTable与List<T>相互转换 2018-06-27
- Asp.Net MVC中Action跳转(转载) 2018-06-27
- Asp.Net初学小结 2018-06-27
- ASP.NET 中HttpRuntime.Cache缓存数据 2018-06-27
IDC资讯: 主机资讯 注册资讯 托管资讯 vps资讯 网站建设
网站运营: 建站经验 策划盈利 搜索优化 网站推广 免费资源
网络编程: Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它
服务器技术: Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护
软件技巧: 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷
网页制作: FrontPages Dreamweaver Javascript css photoshop fireworks Flash