using NPOI.HSSF.UserModel;　　using NPOI.SS.UserModel;　　using System.IO;

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