C#下載自訂格式的Excel

2020-05-26

之前用過的匯出Excel都是dataTable內建的功能

它可以匯出標準格式的Excel，不過如果需要有類似table的colspan效果或是需要大標題

就需要自訂格式，剛好工作上有用到，在這邊紀錄一下

首先須先安裝NuGet套件---->NPOI

using需要的項目

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

程式碼如下

public ActionResult TEST_EXCEL()
{
  try
  {

    //建立Excel
    HSSFWorkbook hssfworkbook = new HSSFWorkbook(); //建立活頁簿
    ISheet sheet = hssfworkbook.CreateSheet("sheet"); //建立sheet

    //設定標題樣式
    ICellStyle headerStyle = hssfworkbook.CreateCellStyle();
    IFont headerfont = hssfworkbook.CreateFont();
    headerStyle.Alignment = HorizontalAlignment.CENTER; //水平置中
    headerStyle.VerticalAlignment = VerticalAlignment.CENTER; //垂直置中
    headerfont.FontName = "微軟正黑體";
    headerfont.FontHeightInPoints = 12;
    headerfont.Boldweight = (short)FontBoldWeight.BOLD;//粗體
    headerStyle.SetFont(headerfont);

    

    //主標題
    sheet.CreateRow(1);//先CreateRow建立,才可GetRow取得該欄位
    sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 6)); // 合併第2行(1,1) A~F列儲存格(0,6)
    sheet.GetRow(1).CreateCell(0).SetCellValue("我是主標題");
    sheet.GetRow(1).GetCell(0).CellStyle = headerStyle; //套用樣式


    //副標題
    sheet.CreateRow(2);
    sheet.AddMergedRegion(new CellRangeAddress(2, 2, 0, 6)); // 合併第3行(2,2) A~F列儲存格(0,6)
    sheet.GetRow(2).CreateCell(0).SetCellValue("我是副標題");
    sheet.GetRow(2).GetCell(0).CellStyle = headerStyle; 


    //設定欄位樣式
    ICellStyle headerStyle_02 = hssfworkbook.CreateCellStyle();
    IFont headerfont_02 = hssfworkbook.CreateFont();
    headerStyle_02.Alignment = HorizontalAlignment.CENTER; //水平置中
    headerStyle_02.VerticalAlignment = VerticalAlignment.CENTER; //垂直置中
    headerStyle_02.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;//設定框限線
    headerStyle_02.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
    headerStyle_02.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
    headerStyle_02.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
    headerfont_02.FontName = "微軟正黑體";
    headerfont_02.FontHeightInPoints = 12;
    headerfont_02.Boldweight = (short)FontBoldWeight.BOLD;//粗體
    headerStyle_02.SetFont(headerfont_02);



    sheet.CreateRow(3).CreateCell(0).SetCellValue("序號");
    sheet.GetRow(3).GetCell(0).CellStyle = headerStyle_02;


    sheet.AddMergedRegion(new CellRangeAddress(3, 3, 1, 5));
    sheet.GetRow(3).CreateCell(1).SetCellValue("項目");
    sheet.GetRow(3).GetCell(1).CellStyle = headerStyle_02;
    //合併欄位設定border需連同被合併的座標一起設
    sheet.GetRow(3).CreateCell(2).CellStyle = headerStyle_02;
    sheet.GetRow(3).CreateCell(3).CellStyle = headerStyle_02;
    sheet.GetRow(3).CreateCell(4).CellStyle = headerStyle_02;
    sheet.GetRow(3).CreateCell(5).CellStyle = headerStyle_02;


    sheet.GetRow(3).CreateCell(6).SetCellValue("總計");
    sheet.GetRow(3).GetCell(6).CellStyle = headerStyle_02;


    //設定資料樣式(序號、總計)
    ICellStyle dataStyle = hssfworkbook.CreateCellStyle();
    IFont datafont = hssfworkbook.CreateFont();
    dataStyle.Alignment = HorizontalAlignment.CENTER; //水平置中
    dataStyle.VerticalAlignment = VerticalAlignment.CENTER; //垂直置中
    dataStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
    dataStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
    dataStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
    dataStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
    datafont.FontName = "微軟正黑體";
    datafont.FontHeightInPoints = 12;
    dataStyle.SetFont(datafont);





    //塞資料
    //下方為寫死資料,可利用迴圈塞資料

    //序號
    sheet.CreateRow(4).CreateCell(0).SetCellValue("1");
    sheet.GetRow(4).GetCell(0).CellStyle = dataStyle;

    //案件項目
    sheet.AddMergedRegion(new CellRangeAddress(4, 4, 1, 5));
    sheet.GetRow(4).CreateCell(1).SetCellValue("測試資料");
    sheet.GetRow(4).GetCell(1).CellStyle = dataStyle;
    //合併欄位設定border需連同被合併的座標一起設
    sheet.GetRow(4).CreateCell(2).CellStyle = dataStyle;
    sheet.GetRow(4).CreateCell(3).CellStyle = dataStyle;
    sheet.GetRow(4).CreateCell(4).CellStyle = dataStyle;
    sheet.GetRow(4).CreateCell(5).CellStyle = dataStyle;

    //總計
    sheet.GetRow(4).CreateCell(6).SetCellValue("100");
    sheet.GetRow(4).GetCell(6).CellStyle = dataStyle;



    var excelDatas = new MemoryStream();
    hssfworkbook.Write(excelDatas);



    Response.Clear();
    Response.AddHeader("content-disposition", "attachment;filename=" + Server.UrlEncode("XXX統計表.xls"));
    Response.ContentType = "application/octet-stream";
    Response.Charset = "gb2312";
    Response.ContentEncoding = Encoding.UTF8;
    Response.BinaryWrite(excelDatas.GetBuffer());
    Response.Flush();
    Response.End();


    return new EmptyResult();
  }
  catch (Exception ex)
  { 
    return null;
  }
}

結果如下

回首頁