小盒子的星空

1. 將NPOI相關dll放入網站的 bin 資料夾
    或是用 nuget下載。在「套件管理器主控台」輸入：Install-Package NPOI

2. using 相關 namespace

using NPOI;
using NPOI.HSSF;
using NPOI.HSSF.Util;
using NPOI.HSSF.Model;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

3. 相關程式

        //使用一個已設定好樣式的excel的檔，填入值，在提供下載的做法
        //好處是不用在程式中，指定每個儲存格的格式
        string path = HttpContext.Current.Request.PhysicalApplicationPath.ToString();

        DataTable dt = ds.Tables[0];

        string strFilePath = path + "Folder\\" + string.Format("sample.xls");
        HSSFWorkbook wb;
        using (FileStream fs = new FileStream(strFilePath, FileMode.Open, FileAccess.ReadWrite))
        {
            wb = new HSSFWorkbook(fs);
        }

        HSSFSheet sheet1 = (HSSFSheet)wb.GetSheetAt(0);  
        
        //如果是要產生新的Excel檔，就用下面這幾行
        //HSSFWorkbook wb = new HSSFWorkbook();
        //HSSFSheet sheet1 = (HSSFSheet)wb.CreateSheet("Sheet1");
        MemoryStream ms = new MemoryStream();

        //設定儲存格樣式
        HSSFCell cell = null;
        HSSFCellStyle wrapStyle = null;
        HSSFCellStyle wrapStyle10 = null;
        HSSFCellStyle wrapStyleR10 = null;
        HSSFCellStyle colorStyle = null;
        HSSFCellStyle RightStyle = null;
        HSSFCellStyle CenterStyle = null;
        wrapStyle = (HSSFCellStyle)wb.CreateCellStyle();
        wrapStyle10 = (HSSFCellStyle)wb.CreateCellStyle();
        wrapStyleR10 = (HSSFCellStyle)wb.CreateCellStyle();
        colorStyle = (HSSFCellStyle)wb.CreateCellStyle();
        RightStyle = (HSSFCellStyle)wb.CreateCellStyle();

        HSSFFont font1 = (HSSFFont)wb.CreateFont();
        //字體尺寸
        font1.FontHeightInPoints = 12;

        HSSFFont font10 = (HSSFFont)wb.CreateFont();
        //字體尺寸
        font10.FontHeightInPoints = 10;
        font10.FontName = "細明體";


        RightStyle.BorderTop = BorderStyle.Thin;
        RightStyle.BorderLeft = BorderStyle.Thin;
        RightStyle.BorderBottom = BorderStyle.Thin;
        RightStyle.BorderRight = BorderStyle.Thin;
        RightStyle.Alignment = HorizontalAlignment.Right;
        RightStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,##0");
        //RightStyle.SetFont(font1);

        CenterStyle = (HSSFCellStyle)wb.CreateCellStyle();
        CenterStyle.BorderTop = BorderStyle.Thin;
        CenterStyle.BorderLeft = BorderStyle.Thin;
        CenterStyle.BorderBottom = BorderStyle.Thin;
        CenterStyle.BorderRight = BorderStyle.Thin;
        CenterStyle.Alignment = HorizontalAlignment.Center;
        CenterStyle.WrapText = true;
        CenterStyle.VerticalAlignment = VerticalAlignment.Center;
        //CenterStyle.SetFont(font1);

        //wrapStyle.SetFont(font1);
        wrapStyle.WrapText = true;
        wrapStyle.BorderTop = BorderStyle.Thin;
        wrapStyle.BorderLeft = BorderStyle.Thin;
        wrapStyle.BorderBottom = BorderStyle.Thin;
        wrapStyle.BorderRight = BorderStyle.Thin;
        wrapStyle.FillForegroundColor = HSSFColor.Red.Index;
        wrapStyle.VerticalAlignment = VerticalAlignment.Center;

        wrapStyle10.SetFont(font10);
        wrapStyle10.WrapText = true;
        wrapStyle10.BorderTop = BorderStyle.Thin;
        wrapStyle10.BorderLeft = BorderStyle.Thin;
        wrapStyle10.BorderBottom = BorderStyle.Thin;
        wrapStyle10.BorderRight = BorderStyle.Thin;
        wrapStyle10.FillForegroundColor = HSSFColor.Red.Index;
        wrapStyle10.VerticalAlignment = VerticalAlignment.Center;
        

        wrapStyleR10.SetFont(font10);
        wrapStyleR10.WrapText = true;
        wrapStyleR10.BorderTop = BorderStyle.Thin;
        wrapStyleR10.BorderLeft = BorderStyle.Thin;
        wrapStyleR10.BorderBottom = BorderStyle.Thin;
        wrapStyleR10.BorderRight = BorderStyle.Thin;
        wrapStyleR10.FillForegroundColor = HSSFColor.Red.Index;
        wrapStyleR10.Alignment = HorizontalAlignment.Right;
        wrapStyleR10.VerticalAlignment = VerticalAlignment.Center;
        
        colorStyle.FillPattern = FillPattern.SolidForeground;        
        colorStyle.FillBackgroundColor = HSSFColor.Red.Index;
        colorStyle.FillForegroundColor = 10;
        sheet1.PrintSetup.Landscape = true;

        DataTable dt = getData(); //準備好要寫入的資料

        try
        {
            for (int i = 0; i < dt.Rows.Count; i++) //逐筆資料寫入
            {
              
                string field1 = dt.Rows[i]["field_name1"].ToString().Trim();
                string field2 = = dt.Rows[i]["field_name2"].ToString().Trim();
  
                cell = (HSSFCell)sheet1.GetRow(rowIndex ).CreateCell(0);
                cell.CellStyle = wrapStyle;  //指定樣式
                cell.SetCellValue(field1);   //寫入值
                cell = (HSSFCell)sheet1.GetRow(rowIndex).CreateCell(1);
                cell.CellStyle = wrapStyle;
                cell.SetCellValue(field2);
  
                rowIndex++;
            }
        }
        catch (Exception e)
        {
            sheet1.GetRow(1).GetCell(0).SetCellValue("發生錯誤：" + e.Message);
        }

        //產生下載的檔案
        wb.Write(ms);
        wb = null;
        ms.Close();
        ms.Dispose();
        HttpContext.Current.Response.Buffer = true;
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.ContentType = "application/octet-stream";
        HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode("檔案名稱", System.Text.Encoding.UTF8) + ".xls\"");
        HttpContext.Current.Response.BinaryWrite(ms.ToArray());
        HttpContext.Current.Response.Flush();
        HttpContext.Current.Response.End();

我是用將上述程式寫在 「泛行處理常式」的檔案 (ashx) 中，然用 javascript (window.open) 開啟這個 ashx 檔，就會自動下載 excel 了