使用 NPOI 設定欄位(CELL)樣式與寬度


如何使用 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));

則如下:




留言

這個網誌中的熱門文章

【研究】列印的條碼為什麼很難刷(掃描)

統一發票列印小程式

C# 使用 Process.Start 執行外部程式