使用 NPOI 設定欄位(CELL)樣式與寬度
底下是個 樣式類別檔案，把常用的欄位形態定義好，以後直接取用
DefXSSFstyle.cs
using System.Drawing;using NPOI.SS.UserModel;using NPOI.XSSF.UserModel;using NPOI.HSSF.Util;
namespace XssSTYLE{ /// <summary> /// 已經定義好的欄位樣式，如果需要新樣式，複製程式碼並賦與新名稱，修改樣式產生程式碼即可使用 /// </summary> class DefXSSFstyle { /// <summary> /// 抬頭樣式 - 01 /// 灰色底色，白色字，正黑體，無邊框 /// </summary> /// <param name="wb"></param> /// <returns></returns> public XSSFCellStyle Banner01(IWorkbook wb) { XSSFCellStyle csh = (XSSFCellStyle)wb.CreateCellStyle(); csh.Alignment = HorizontalAlignment.Center; csh.VerticalAlignment = VerticalAlignment.Center; csh.WrapText = true; csh.FillForegroundXSSFColor = new XSSFColor(Color.FromKnownColor(KnownColor.Gray)); csh.FillPattern = FillPattern.SolidForeground;
IFont font1 = wb.CreateFont(); font1.FontName = "正黑體"; font1.FontHeightInPoints = 14; font1.Color = HSSFColor.White.Index;
csh.SetFont(font1);
return csh; }
/// <summary> /// 標題樣式 - 01 /// 亮黃色底色，黑色字，正黑體，有邊框 /// </summary> /// <param name="wb">來源的IWorkbook</param> /// <returns></returns> public XSSFCellStyle Hander01(IWorkbook wb) { XSSFCellStyle csh = (XSSFCellStyle)wb.CreateCellStyle(); csh.Alignment = HorizontalAlignment.Center; csh.VerticalAlignment = VerticalAlignment.Center; csh.WrapText = true; csh.FillForegroundXSSFColor = new XSSFColor(Color.FromKnownColor(KnownColor.LightYellow)); csh.FillPattern = FillPattern.SolidForeground;
csh.BorderLeft = BorderStyle.Thin; csh.BorderTop = BorderStyle.Thin; csh.BorderRight = BorderStyle.Thin; csh.BorderBottom = BorderStyle.Thin;
csh.LeftBorderColor = HSSFColor.DarkBlue.Index; csh.TopBorderColor = HSSFColor.DarkBlue.Index; csh.RightBorderColor = HSSFColor.DarkBlue.Index; csh.BottomBorderColor = HSSFColor.Black.Index;
IFont font1 = wb.CreateFont(); font1.FontName = "正黑體"; font1.FontHeightInPoints = 12; font1.Color = HSSFColor.Automatic.Index;
csh.SetFont(font1);
return csh; }
public XSSFCellStyle Hander09(IWorkbook wb) { XSSFCellStyle csh = (XSSFCellStyle)wb.CreateCellStyle(); csh.Alignment = HorizontalAlignment.Center; csh.VerticalAlignment = VerticalAlignment.Center; csh.WrapText = true; csh.FillForegroundXSSFColor = new XSSFColor(Color.FromKnownColor(KnownColor.LightGreen)); csh.FillPattern = FillPattern.SolidForeground;
csh.BorderLeft = BorderStyle.Thin; csh.BorderTop = BorderStyle.Thin; csh.BorderRight = BorderStyle.Thin; csh.BorderBottom = BorderStyle.Thin;
csh.LeftBorderColor = HSSFColor.DarkBlue.Index; csh.TopBorderColor = HSSFColor.DarkBlue.Index; csh.RightBorderColor = HSSFColor.DarkBlue.Index; csh.BottomBorderColor = HSSFColor.Black.Index;
IFont font1 = wb.CreateFont(); font1.FontName = "正黑體"; font1.FontHeightInPoints = 12; font1.Color = HSSFColor.Automatic.Index;
csh.SetFont(font1);
return csh; }
public XSSFCellStyle Field01(IWorkbook wb) { XSSFCellStyle csh = (XSSFCellStyle)wb.CreateCellStyle();
csh.BorderLeft = BorderStyle.Thin; csh.BorderTop = BorderStyle.Thin; csh.BorderRight = BorderStyle.Thin; csh.BorderBottom = BorderStyle.Thin;
csh.LeftBorderColor = HSSFColor.Black.Index; csh.TopBorderColor = HSSFColor.Black.Index; csh.RightBorderColor = HSSFColor.Black.Index; csh.BottomBorderColor = HSSFColor.Black.Index;
IFont font1 = wb.CreateFont(); font1.FontName = "新細明體"; font1.FontHeightInPoints = 12; font1.Color = HSSFColor.Automatic.Index;
csh.SetFont(font1);
return csh; }
public XSSFCellStyle Field09(IWorkbook wb) { XSSFCellStyle csh = (XSSFCellStyle)wb.CreateCellStyle();
csh.FillForegroundXSSFColor = new XSSFColor(Color.FromKnownColor(KnownColor.LightGreen)); csh.FillPattern = FillPattern.SolidForeground;
csh.BorderLeft = BorderStyle.Thin; csh.BorderTop = BorderStyle.Thin; csh.BorderRight = BorderStyle.Thin; csh.BorderBottom = BorderStyle.Thin;
csh.LeftBorderColor = HSSFColor.Black.Index; csh.TopBorderColor = HSSFColor.Black.Index; csh.RightBorderColor = HSSFColor.Black.Index; csh.BottomBorderColor = HSSFColor.Black.Index;
IFont font1 = wb.CreateFont(); font1.FontName = "新細明體"; font1.FontHeightInPoints = 12; font1.Color = HSSFColor.Automatic.Index;
csh.SetFont(font1);
return csh; } }}這個類別裏面定義了5個樣式分別是: Banner01、Hander01、Hander09、Field01、Field09
使用方式如下：
static public void test2(IWorkbook wb){ // 樣式元件 DefXSSFstyle style = new DefXSSFstyle();
XSSFCellStyle css1 = style.Banner01(wb); XSSFCellStyle css2 = style.Field01(wb); XSSFCellStyle css3 = style.Field09(wb); XSSFCellStyle css4 = style.Hander01(wb); XSSFCellStyle css5 = style.Hander01(wb);
// 建立分頁賦予分頁名稱 ISheet ws1 = wb.CreateSheet("樣式表");
// 新增Row IRow row1 = ws1.CreateRow(0);
// 新增Cell ICell cell0 = row1.CreateCell(0); ICell cell1 = row1.CreateCell(1); ICell cell2 = row1.CreateCell(2); ICell cell3 = row1.CreateCell(3); ICell cell4 = row1.CreateCell(4);
// 設定文字與樣式與寬度 cell0.SetCellValue("Banner01"); cell0.CellStyle = css1; // 自動寬度 ws1.AutoSizeColumn(0);
cell1.SetCellValue("Field01"); cell1.CellStyle = css2; // 欄寬10 ws1.SetColumnWidth(1, (int)((10 + 0.71) * 256));
cell2.SetCellValue("Field09"); cell2.CellStyle = css3; // 欄寬11 ws1.SetColumnWidth(2, (int)((11 + 0.71) * 256));
cell3.SetCellValue("Hander01"); cell3.CellStyle = css4; // 欄寬13 ws1.SetColumnWidth(3, (int)((13 + 0.71) * 256));
cell4.SetCellValue("Hander01"); cell4.CellStyle = css5; // 欄寬14 ws1.SetColumnWidth(4, (int)((14 + 0.71) * 256));
}上面分別產生5個CELL，並設定上那5種 STYLE
在設定 CELL 寬度的公式 =
(int)((欄寬 + 0.71) * 256)
可以得到正確寬度
例如：
ws1.SetColumnWidth(1, (int)((10 + 0.71) * 256));
則如下：
留言