在實務上,經常需要寫輸出報表的功能
本篇記錄常見的報表格式之一,Excel的程式碼
範例會使用到NPOI套件來操作Excel
以及介紹儲存格樣式、合併儲存格、字體顏色等常見的操作
首先透過NuGet安裝NPOI
範例使用的版本為2.5.2
安裝好後程式碼如下:
//建立檔名
string filename = "test.xlsx";
//建立一個excel
IWorkbook workBook = new XSSFWorkbook(); //Office2007之後(包含2007)的版本,副檔名為xlsx
//IWorkbook workBook = new HSSFWorkbook(); //Office2007之前的版本,副檔名為xls
//建立sheet,名稱不可重複
ISheet sheet = workBook.CreateSheet("Info");
//設定格式(可以設定多個ICellStyle,再選擇其中一個設定給儲存格)
ICellStyle cellStyle = workBook.CreateCellStyle();
//文字水平置中
cellStyle.Alignment = HorizontalAlignment.Center;
//設定邊界線條為圓點
cellStyle.BorderBottom = BorderStyle.Dotted;
//建立第一列,通常拿來設定標題
sheet.CreateRow(0);
//取得第一列.建立儲存格.設定儲存格的值
//SetCellValue可以放入string、bool、DateTime、double(其實Excel接受numeric,所以數字類都行)
sheet.GetRow(0).CreateCell(0).SetCellValue("姓名");
sheet.GetRow(0).CreateCell(1).SetCellValue("身高");
sheet.GetRow(0).CreateCell(2).SetCellValue("體重");
//處理第二列
//HeightInPoints設定列高,單位是點
sheet.CreateRow(1).HeightInPoints = 20;
sheet.GetRow(1).CreateCell(0).SetCellValue("小肥豬");
sheet.GetRow(1).CreateCell(1).SetCellValue(167.5);
sheet.GetRow(1).CreateCell(2).SetCellValue(57.5);
//處理第三列
//Height也是設定列高,但單位是1/20個點,所以要乘上20
sheet.CreateRow(2).Height = 40 * 20;
sheet.GetRow(2).CreateCell(0).SetCellValue("大胖豬");
sheet.GetRow(2).CreateCell(1).SetCellValue(177.5);
sheet.GetRow(2).CreateCell(2).SetCellValue(82.5);
//這裡只設定標題,來顯示出差異,一般字串會自動偏左
//使用LastCellNum來計算一列有幾個儲存格
//若要取得有幾列可以使用sheet.LastRowNum
for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++)
{
//標題設定cellStyle所設定的屬性,所以會水平置中且底線變為圓點
sheet.GetRow(0).GetCell(i).CellStyle = cellStyle;
//設定欄寬,單位為1/256個字元寬度,所以要乘上256
sheet.SetColumnWidth(i, 8 * 256);
}
//合併儲存格,不用建立列和欄就可以直接合併,參數順序為:起始列、結束列、起始欄、結束欄
CellRangeAddress mergeRegion = new CellRangeAddress(3, 4, 0, 2);
sheet.AddMergedRegion(mergeRegion);
//如果要在合併儲存格中設定值與屬性,還是需要建立欄和列
//但只需建立左上角的位置,因為值與屬性只能設定到該位置,設定到其他位置不會顯示
sheet.CreateRow(3);
sheet.GetRow(3).CreateCell(0).SetCellValue("合併");
//設定顏色
ICellStyle mergeCellStyle = workBook.CreateCellStyle();
//字體顏色
IFont font = (XSSFFont)workBook.CreateFont();
font.Color = IndexedColors.Red.Index;
mergeCellStyle.SetFont(font);
//儲存格背景顏色
mergeCellStyle.FillForegroundColor = IndexedColors.SkyBlue.Index;
mergeCellStyle.FillPattern = FillPattern.SolidForeground;
//合併儲存格設定mergeCellStyle的格式
sheet.GetRow(3).GetCell(0).CellStyle = mergeCellStyle;
//設定報表的預設欄寬與預設高度,若儲存格沒有設定樣式,就會使用預設高度和預設欄寬
//sheet.DefaultColumnWidth = 100 * 256;
//sheet.DefaultRowHeight = 30 * 20;
//可以設定欄位自動調整大小
//sheet.AutoSizeColumn(i);
//設定標頭
Response.AddHeader("Content-disposition", "attachment; filename=\"" + filename + "" + "\"");
//設定回傳媒體型別(MIME)
Response.ContentType = "application/vnd.ms-excel";
//取得Response的OutputStream
Stream stream = Response.OutputStream;
//寫入資料
workBook.Write(stream);
//關閉工作簿
workBook.Close();
//送出Response
Response.End();
輸出結果:
這裡要注意的是Excel版本問題
2007之前的是.xls,要使用HSSF開頭的類別操作
2007(含)之後的是.xlsx,要使用XSSF開頭的類別操作
若是要讀取Excel檔案,需要注意副檔名是什麼
為了能減少程式碼的修改,所以變數使用interface來宣告
實例的部分再依照副檔名來建立