asp.net DataTable導出Excel 自定義列名，asp.netdatatable

asp.net DataTable導出Excel 自定義列名，asp.netdatatable

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