[NPOI]以ASP .NET配上NPOI 2.2.1產生xlsx檔
NPOI是一個很好用的Excel檔案Library,可以在ASP .NET中快速的產生/讀取xls及xlsx格式,以下說明基本語法,使用的版本是2.2.1。
如要直接下載檔案,建立MemoryStream,如要儲存檔案,則建立FileStream。
//直接下載檔案
MemoryStream MS = new MemoryStream();
//儲存實體路徑
FileStream FS = new FileStream(Path.Combine("C:/", "Output.xlsx"), FileMode.Create, System.IO.FileAccess.Write);
開始設定Excel內容,建立新的WorkBook => 建立一個Excel。
IWorkbook _WorkBook = new XSSFWorkbook();
在Workbook中建立新的Sheet,並設定Sheet名稱,名稱不可重複。
ISheet sheet0 = _WorkBook.CreateSheet("目錄");
ISheet sheet1 = _WorkBook.CreateSheet("Tables");
ISheet sheet2 = _WorkBook.CreateSheet("Views");
在Sheet中新增一列(IRow),0=第一列、1=第二列...。
IRow tempIRow = sheet0.CreateRow(0);
//取得要處理的一列
tempIRow = sheet0.GetRow(0);
在該列(IRow)中新增一格(ICell),0=第一格、1=第二格...,建立時可直接設定該Cell的資料型態,如不設定,則像手機號碼(09xx)會被轉成數字,前面的0就會被自動移除。
ICell tempICell = tempIRow.CreateCell(0, CellType.String);
//取得要處理的Cell
tempICell = tempIRow.GetCell(0);
設定Cell的樣式(ICellStyle),注意不需要每個Cell都CreateCellStyle,要先建立好樣式後再餵給Cell,因一份Excel可設定的樣式數量有上限,超過上限就會報錯。
ICellStyle CellsStyle = _WorkBook.CreateCellStyle();
//設定好各種Style
ICellStyle tblCellStyle = this.SetCellsStyle(_WorkBook, CellStyleType.table_title);
ICellStyle colCellStyle = this.SetCellsStyle(_WorkBook, CellStyleType.column_title);
ICellStyle lnkCellStyle = this.SetCellsStyle(_WorkBook, CellStyleType.hyperlink);
ICellStyle mlineCellStyle = this.SetCellsStyle(_WorkBook, CellStyleType.muiltiline);
ICellStyle normalCellStyle = this.SetCellsStyle(_WorkBook, CellStyleType.normal);
//賦予Cell樣式
tempICell.CellStyle = colCellStyle;
設定Cell內容。
//設定Cell內文字。
tempICell.SetCellValue("回目錄");
//設定Cell內公式。
tempICell.SetCellFormula("公式");
//設定Cell內連結,先建立連結(IHyperlink)
IHyperlink templink = new XSSFHyperlink(HyperlinkType.Document);
//設定連結的路徑,內容可以參考Excel中輸入的文字
templink.Address = "目錄!A1"; //建立可以轉跳到Sheet(目錄)A1格子的連結
//把連結繫結到Cell
tempICell.Hyperlink = templink;
建立跨欄置中(合併欄位)的區塊,依照要合併的起始Row Index / 結束Row Index / 起始欄位Index / 結束欄位Index產生,要顯示的文字以下方Code為例,要在sheet0.GetRow(1).GetCell(1)中填入文字,另外要注意如合併的Cell已經有設定文字的話,會被清空,所以先設定好合併區塊後,再設定文字內容。
//數字都是從0開始計算
tempISheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 1, 5));
將資料依序填入後,設定欄寬。
//設定自動調整欄寬,0=第一欄,1=第二欄...
sheet0.AutoSizeColumn(0);
//設定固定欄寬,0=第一欄,1=第二欄...
//後面是要設定的高*寬,單位是一個字的1/256
sheet0.SetColumnWidth(1, 25 * 450);
產生檔案直接輸出,這邊要特別注意如果輸出中文檔名的話,要用UrlEncode編碼過才不會出現亂碼。
//將WorkBook寫入MemoryStream
_WorkBook.Write(MS);
//輸出
System.Web.HttpContext.Current.Response.Clear();
System.Web.HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=Output.xlsx");
System.Web.HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
System.Web.HttpContext.Current.Response.BinaryWrite(MS.ToArray());
_WorkBook = null;
MS.Close();
MS.Dispose();
//以下兩行一定要有,否則產出的檔案size會比較大,且用Excel開啟會報錯(雖然還是可以修復後開啟)
System.Web.HttpContext.Current.Response.Flush();
System.Web.HttpContext.Current.Response.End();
儲存到實體路徑。
//儲存到實體路徑
FileStream FS = new FileStream(Path.Combine("C:/", "Output.xlsx"), FileMode.Create, System.IO.FileAccess.Write);
_WorkBook.Write(FS);
FS.Close();
上方的SetCellStyle以下方的各項設定產生。
//建立新的CellStyle
ICellStyle CellsStyle = pWorkBook.CreateCellStyle();
//建立字型
IFont StyleFont = pWorkBook.CreateFont();
//設定文字字型
StyleFont.FontName = "微軟正黑體";
//設定文字大小
StyleFont.FontHeightInPoints = 10; //設定文字大小為10pt
CellsStyle.SetFont(StyleFont);
//文字位置
CellsStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
//文字顏色
StyleFont.Color = NPOI.SS.UserModel.IndexedColors.Black.Index;
//自訂RBG顏色
IColor rbgColor = new XSSFColor(new byte[] { 200, 140, 20 });
StyleFont.Color = rbgColor.Indexed;
//背景顏色(一定要和圖樣同時存在才有作用)
CellsStyle.FillForegroundColor = NPOI.SS.UserModel.IndexedColors.LightYellow.Index;
//背景圖樣
CellsStyle.FillPattern = FillPattern.SolidForeground;
//文字自動換列
CellsStyle.WrapText = true;
//Cell框線
CellsStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
CellsStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
CellsStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
CellsStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
//Cell框線顏色
CellsStyle.BottomBorderColor = NPOI.SS.UserModel.IndexedColors.Black.Index;
CellsStyle.LeftBorderColor = NPOI.SS.UserModel.IndexedColors.Black.Index;
CellsStyle.RightBorderColor = NPOI.SS.UserModel.IndexedColors.Black.Index;
CellsStyle.TopBorderColor = NPOI.SS.UserModel.IndexedColors.Black.Index;
參考資料