NPOI使用

會使用這個元件的主要原因是因為要在一個Excel檔裡建立多個sheet的資料，底下是整理出的範例

using System;
using System.Web;
using System.IO;
using NPOI;
using NPOI.HSSF;
using NPOI.HSSF.Util;
using NPOI.HSSF.Model;
using NPOI.HSSF.UserModel;

public class filedownload : IHttpHandler
{
public void ProcessRequest (HttpContext context)
{
    MemoryStream ms = new MemoryStream();
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.CreateSheet("sample");
    HSSFCell cell = null;
    HSSFCellStyle borderStyle = null;
    HSSFCellStyle colorStyle = null;
    HSSFCellStyle fontStyle = null;
    HSSFCellStyle heightStyle = null;
    HSSFCellStyle spanStyle = null;
    HSSFCellStyle wrapStyle = null;
    HSSFFont font = null;

    borderStyle = workbook.CreateCellStyle();
    colorStyle = workbook.CreateCellStyle();
    fontStyle = workbook.CreateCellStyle();
    heightStyle = workbook.CreateCellStyle();
    spanStyle = workbook.CreateCellStyle();
    wrapStyle = workbook.CreateCellStyle();

    //Style設定    
    borderStyle.BorderTop = HSSFCellStyle.BORDER_THIN;
    borderStyle.BorderLeft = HSSFCellStyle.BORDER_THIN;
    borderStyle.BorderBottom = HSSFCellStyle.BORDER_THIN;
    borderStyle.BorderRight = HSSFCellStyle.BORDER_THIN;
    colorStyle.FillForegroundColor = HSSFColor.LIGHT_CORNFLOWER_BLUE.index;
    colorStyle.FillPattern = HSSFCellStyle.SOLID_FOREGROUND;
    fontStyle.Alignment = HSSFCellStyle.ALIGN_CENTER;
    fontStyle.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
    heightStyle.Alignment = HSSFCellStyle.ALIGN_CENTER;
    heightStyle.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
    spanStyle.Alignment = HSSFCellStyle.ALIGN_CENTER;
    spanStyle.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
    wrapStyle.WrapText = true;

    //指定紙張大小 A3=8, A4=9, Letter=1
    sheet.PrintSetup.PaperSize = 9;

    //指定直式或橫式 true=橫式 false=直式
    sheet.PrintSetup.Landscape = true;

    //藏隱格線
    sheet.DisplayGridlines = false;

    //設定欄寬 
    sheet.SetColumnWidth(0, 24 * 256);
    sheet.SetColumnWidth(1, 24 * 256);

    //指定列高    
    cell = sheet.CreateRow(0).CreateCell(0);
    cell.SetCellValue("指定列高");
    cell.CellStyle = heightStyle;
    sheet.GetRow(0).HeightInPoints = 50;

    //字型大小
    font = workbook.CreateFont();
    font.FontHeightInPoints = 14;
    font.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
    fontStyle.SetFont(font);
    cell = sheet.CreateRow(1).CreateCell(0);
    cell.CellStyle = fontStyle;
    cell.SetCellValue("字型大小14粗體");

    //合併儲存格
    cell = sheet.CreateRow(2).CreateCell(0);
    cell.SetCellValue("合併儲存格");
    cell.CellStyle = spanStyle;
    sheet.AddMergedRegion(new Region(2, 0, 3, 1));

    //Wrap
    cell = sheet.CreateRow(4).CreateCell(0);
    cell.SetCellValue(string.Format("換行{0}測試", System.Environment.NewLine));
    cell.CellStyle = wrapStyle;

    //增加邊框
    cell = sheet.CreateRow(5).CreateCell(1);
    cell.SetCellValue("邊框                  ");
    cell.CellStyle = borderStyle;

    //背景
    cell = sheet.CreateRow(6).CreateCell(0);
    cell.SetCellValue("背景");
    cell.CellStyle = colorStyle;

    //插入分頁
    sheet.SetRowBreak(cell.RowIndex);

    //下一頁資料
    cell = sheet.CreateRow(7).CreateCell(0);
    cell.SetCellValue("下一頁資料");

    workbook.Write(ms);    

    context.Response.ContentType = "application/vnd.ms-excel";
    context.Response.AddHeader("Content-Disposition", "attachment; filename=myExcel.xls");
    context.Response.BinaryWrite(ms.ToArray());
}

public bool IsReusable
{
    get
    {
        return false;
    }
}
}

使用NPOI要注意的地方是如果你要設定每個儲存格不同的儲格格式，需要建立多個HSSFCellStyle並且將它指派給儲存格


*相關參考


NPOI ( Web C+ + + + - IT技術文章 )


NPOI 1.2教程（目录） - tonyqus.cn - 博客园


NPOI - Discussions - New line in cell


[C#]NPOI匯出Excel遇到資料換行的問題 - gipi的學習筆記-我的職場觀念、IT部落格- 點部落