C# HSSFWorkbook.CreateFont方法代码示例

        public void AddDateRangetoExcelSheet(HSSFWorkbook workbook, ISheet sheet)
        {
            //Create a Title row
            var titleFont = workbook.CreateFont();
            titleFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
            titleFont.FontHeightInPoints = 11;
            titleFont.Underline = NPOI.SS.UserModel.FontUnderlineType.Single;

            var titleStyle = workbook.CreateCellStyle();
            titleStyle.SetFont(titleFont);

            var row = sheet.CreateRow(rowCount++);
            var cell = row.CreateCell(0);
            cell.CellStyle = titleStyle;
            cell.SetCellValue("Date Range");

            row = sheet.CreateRow(rowCount++);
            cell = row.CreateCell(0);
            var value = string.Format("Start Date: {0}", StartDate.ToString("MM-dd-yyyy"));
            cell.SetCellValue(value);
            row = sheet.CreateRow(rowCount++);
            cell = row.CreateCell(0);
            value = string.Format("End Date: {0}", EndDate.ToString("MM-dd-yyyy"));
            cell.SetCellValue(value);
        }

        public void TestDoesNoHarmIfNothingToDo()
        {
            HSSFWorkbook wb = new HSSFWorkbook();

            IFont f = wb.CreateFont();
            f.FontName = ("Testing");
            NPOI.SS.UserModel.ICellStyle s = wb.CreateCellStyle();
            s.SetFont(f);

            Assert.AreEqual(5, wb.NumberOfFonts);
            Assert.AreEqual(22, wb.NumCellStyles);

            // Optimise fonts
            HSSFOptimiser.OptimiseFonts(wb);

            Assert.AreEqual(5, wb.NumberOfFonts);
            Assert.AreEqual(22, wb.NumCellStyles);

            Assert.AreEqual(f, s.GetFont(wb));

            // Optimise styles
            HSSFOptimiser.OptimiseCellStyles(wb);

            Assert.AreEqual(5, wb.NumberOfFonts);
            Assert.AreEqual(22, wb.NumCellStyles);

            Assert.AreEqual(f, s.GetFont(wb));
        }

        public static StyleContainer CreateStyles(HSSFWorkbook workbook)
        {
            var styles = new StyleContainer();

            var h1Font = workbook.CreateFont();
            h1Font.FontHeightInPoints = (short)24;
            h1Font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;

            var h2Font = workbook.CreateFont();
            h2Font.FontHeightInPoints = (short)16;
            h2Font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;

            var h3Font = workbook.CreateFont();
            h3Font.FontHeightInPoints = (short)12;
            h3Font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;

            var titleFont = workbook.CreateFont();
            titleFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;

            styles.RightAligned = workbook.CreateCellStyle();
            styles.RightAligned.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;

            styles.Date = workbook.CreateCellStyle();
            styles.Date.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
            styles.Date.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm");

            styles.Currency = workbook.CreateCellStyle();
            styles.Currency.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT;
            //styles.Currency.DataFormat = HSSFDataFormat.GetBuiltinFormat("$#,##0.00");
            styles.Currency.DataFormat = (short)7;

            styles.LeftAligned = workbook.CreateCellStyle();
            styles.LeftAligned.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;

            styles.TitleStyle = workbook.CreateCellStyle();
            styles.TitleStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
            styles.TitleStyle.SetFont(titleFont);

            // Fonts are set into a style so create a new one to use.
            styles.HeaderStyle = workbook.CreateCellStyle();
            styles.HeaderStyle.SetFont(h1Font);
            styles.HeaderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;

            // Fonts are set into a style so create a new one to use.
            styles.Header2Style = workbook.CreateCellStyle();
            styles.Header2Style.SetFont(h2Font);

            // Fonts are set into a style so create a new one to use.
            styles.Header3Style = workbook.CreateCellStyle();
            styles.Header3Style.SetFont(h3Font);

            return styles;
        }

        public void TestWriteSheetFont()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
            IRow r = null;
            //ICell c = null;
            IFont fnt = wb.CreateFont();
            NPOI.SS.UserModel.ICellStyle cs = wb.CreateCellStyle();

            fnt.Color=(NPOI.HSSF.Util.HSSFColor.RED.index);
            fnt.Boldweight=(short)FontBoldWeight.BOLD;
            cs.SetFont(fnt);
            for (short rownum = (short)0; rownum < 100; rownum++)
            {
                r = s.CreateRow(rownum);
                r.RowStyle=(cs);
                r.CreateCell(0);
            }
            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);

            SanityChecker sanityChecker = new SanityChecker();
            sanityChecker.CheckHSSFWorkbook(wb);
            Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
            Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
        }

    /// <summary>
    /// 加边框
    /// </summary>
    /// <param Name="rowindex">1开始</param>
    /// <param Name="cellIndex">1开始</param>
    public void AddBorder( ISheet sheet, HSSFWorkbook workbook)
    {
        ICellStyle styel = workbook.CreateCellStyle();
        styel.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // ------------------
        IFont font1 = workbook.CreateFont();
        font1.FontHeightInPoints = 11;

        font1.Boldweight = 600;
        font1.FontName = "宋体";
        styel.SetFont(font1);
        for (int rowindex=1;rowindex<sheet.LastRowNum+1;rowindex++)
        {
            for (int cellIndex =0; cellIndex < dcs.Count;cellIndex++ )
            {
                sheet.GetRow(rowindex).RowStyle = styel;
                ICell cell = sheet.GetRow(rowindex ).GetCell(cellIndex );

                HSSFCellStyle Style = workbook.CreateCellStyle() as HSSFCellStyle;

                Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                Style.VerticalAlignment = VerticalAlignment.Center;
                Style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                Style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                Style.DataFormat = 0;
                Style.SetFont(font1);
                cell.CellStyle = Style;
            }
         }
    }

        public void AddListToExcelSheet(HSSFWorkbook workbook, ISheet sheet, string Title, Dictionary<string, bool> list)
        {
            //Create a Title row
            var titleFont = workbook.CreateFont();
            titleFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
            titleFont.FontHeightInPoints = 11;
            titleFont.Underline = NPOI.SS.UserModel.FontUnderlineType.Single;

            var titleStyle = workbook.CreateCellStyle();
            titleStyle.SetFont(titleFont);

            var row = sheet.CreateRow(rowCount++);
            row = sheet.CreateRow(rowCount++);
            var cell = row.CreateCell(0);
            cell.CellStyle = titleStyle;
            cell.SetCellValue(Title);
            foreach (var org in list)
            {
                if (org.Value == true)
                {
                    row = sheet.CreateRow(rowCount++);
                    cell = row.CreateCell(0);
                    cell.SetCellValue(org.Key);
                }
            }
        }

        public WorkbookExcelRender()
        {
            _workbook = new HSSFWorkbook();
            
            _isDisposed = false;

            OddCellStyle = _workbook.CreateCellStyle();
            EvenCellStyle = _workbook.CreateCellStyle();
            OddCellStyleCenterAligned = _workbook.CreateCellStyle();
            EvenCellStyleCenterAligned = _workbook.CreateCellStyle();

            _nextCellIndex = -1;

            RTFHelper = new RTFHelper()
            {
                NegFont = _workbook.CreateFont(),
                NormalFont = _workbook.CreateFont(),
                PosFont = _workbook.CreateFont(),
                NegFontCrossed = _workbook.CreateFont(),
                NormalFontCrossed = _workbook.CreateFont(),
                PosFontCrossed = _workbook.CreateFont(),
                Pallete = _workbook.GetCustomPalette(),
                RTFRenderer = new RtfTextRender()
            };

            InitDefaultRTFHelper();
            InitDefaultCellStyles();
        }

        public byte[] WriteExcel(IEnumerable data, string[] columns)
        {
            MemoryStream output = new MemoryStream();
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet();
            IFont headerFont = workbook.CreateFont();
            headerFont.Boldweight = (short)FontBoldWeight.Bold;
            ICellStyle headerStyle = workbook.CreateCellStyle();
            headerStyle.SetFont(headerFont);
            headerStyle.Alignment = HorizontalAlignment.Center;

            //(Optional) freeze the header row so it is not scrolled
            sheet.CreateFreezePane(0, 1, 0, 1);

            IEnumerator foo = data.GetEnumerator();
            foo.MoveNext();
            Type t = foo.Current.GetType();

            IRow header = sheet.CreateRow(0);
            PropertyInfo[] properties = t.GetProperties();
            int colIndex = 0;
            for (int i = 0; i < properties.Length; i++)
            {
                if (columns.Contains(properties[i].Name))
                {
                    ICell cell = header.CreateCell(colIndex);
                    cell.CellStyle = headerStyle;
                    cell.SetCellValue(properties[i].Name);
                    colIndex++;
                }
            }

            int rowIndex = 0;
            foreach (object o in data)
            {
                colIndex = 0;
                IRow row = sheet.CreateRow(rowIndex + 1);
                for (int i = 0; i < properties.Length; i++)
                {
                    if (columns.Contains(properties[i].Name))
                    {
                        row.CreateCell(colIndex).SetCellValue(properties[i].GetValue(o, null).ToString());
                        colIndex++;
                    }
                }
                rowIndex++;
            }

            workbook.Write(output);
            return output.ToArray();
        }

        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public bool ToExcel(DataTable table)
        {
            FileStream fs = new FileStream(this._filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            IWorkbook workBook = new HSSFWorkbook();
            this._sheetName = this._sheetName.IsEmpty() ? "sheet1" : this._sheetName;
            ISheet sheet = workBook.CreateSheet(this._sheetName);

            //处理表格标题
            IRow row = sheet.CreateRow(0);
            row.CreateCell(0).SetCellValue(this._title);
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, table.Columns.Count - 1));
            row.Height = 500;

            ICellStyle cellStyle = workBook.CreateCellStyle();
            IFont font = workBook.CreateFont();
            font.FontName = "微软雅黑";
            font.FontHeightInPoints = 17;
            cellStyle.SetFont(font);
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            cellStyle.Alignment = HorizontalAlignment.Center;
            row.Cells[0].CellStyle = cellStyle;

            //处理表格列头
            row = sheet.CreateRow(1);
            for (int i = 0; i < table.Columns.Count; i++)
            {
                row.CreateCell(i).SetCellValue(table.Columns[i].ColumnName);
                row.Height = 350;
                sheet.AutoSizeColumn(i);
            }

            //处理数据内容
            for (int i = 0; i < table.Rows.Count; i++)
            {
                row = sheet.CreateRow(2 + i);
                row.Height = 250;
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    row.CreateCell(j).SetCellValue(table.Rows[i][j].ToString());
                    sheet.SetColumnWidth(j, 256 * 15);
                }
            }

            //写入数据流
            workBook.Write(fs);
            fs.Flush();
            fs.Close();

            return true;
        }

        public byte[] Create(List<string> columns, List<List<string>> rows, HttpServerUtilityBase server)
        {
            var workbook = new HSSFWorkbook();
            var sheet = workbook.CreateSheet("Sheet1");

            // create header style
            var headerFont = workbook.CreateFont();
            headerFont.Boldweight = (short)FontBoldWeight.BOLD;
            var headerStyle = workbook.CreateCellStyle();
            headerStyle.FillBackgroundColor = HSSFColor.GREY_40_PERCENT.index;
            headerStyle.FillForegroundColor = HSSFColor.GREY_40_PERCENT.index;
            headerStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
            headerStyle.SetFont(headerFont);

            // Getting the row... 0 is the first row.
            var dataRow = sheet.CreateRow(0);
            for (int i = 0; i < columns.Count; i++)
            {
                dataRow.CreateCell(i).SetCellValue(columns.ElementAt(i));
                dataRow.GetCell(i).CellStyle = headerStyle;
            }

            var rowCount = 0;
            foreach (var a in rows)          // gets the array that represents a row
            {
                rowCount++;
                dataRow = sheet.CreateRow(rowCount);

                for (var i = 0; i < a.Count; i++)   // gets the individual cell
                {
                    dataRow.CreateCell(i).SetCellValue(a[i]);
                }
            }

            // Forcing formula recalculation...
            sheet.ForceFormulaRecalculation = true;

            // adjust column widths
            for (int i = 0; i < columns.Count; i++)
            {
                sheet.AutoSizeColumn(i);
            }

            using (var ms = new MemoryStream())
            {
                workbook.Write(ms);
                return ms.ToArray();
            }
        }

        public void TestColorStyleCopy()
        {
            HSSFWorkbook bookA = new HSSFWorkbook();
            HSSFWorkbook bookB = new HSSFWorkbook();

            bookA.Workbook.CustomPalette.ClearColors();
            bookA.Workbook.CustomPalette.SetColor(0x8, 12, 15, 255); //0x8 is blueish
            bookA.Workbook.CustomPalette.SetColor(0x9, 200, 200, 200); //0x9 is light gray
            bookB.Workbook.CustomPalette.ClearColors();
            bookB.Workbook.CustomPalette.SetColor(0x8, 192, 168, 0); //Throw a color into the destination book so we can see color merge working.

            HSSFSheet sheetA = bookA.CreateSheet("Sheet A") as HSSFSheet;
            ICell cell = sheetA.CreateRow(0).CreateCell(0);
            cell.SetCellValue("I'm a stylish cell!");
            IFont myFont = bookA.CreateFont();
            myFont.FontName = "Times New Roman";
            myFont.IsItalic = true;
            myFont.FontHeightInPoints = 12;
            myFont.Color = 0x8;
            ICellStyle myStyle = bookA.CreateCellStyle();
            myStyle.SetFont(myFont);
            myStyle.FillForegroundColor = 0x9;
            cell.CellStyle = myStyle;

            HSSFSheet sheetB = bookB.CreateSheet("BookB Sheet") as HSSFSheet;
            ICell beeCell = sheetB.CreateRow(0).CreateCell(0);
            ICellStyle styleB = bookB.CreateCellStyle();
            styleB.FillForegroundColor = 0x8;
            beeCell.CellStyle = styleB;
            beeCell.SetCellValue("Hello NPOI");

            //Copy the sheet, and make sure the color, style, and font is correct
            sheetA.CopyTo(bookB, "Copied Sheet A", true, true);
            HSSFSheet theCopy = bookB.GetSheetAt(1) as HSSFSheet;
            ICell copiedCell = theCopy.GetRow(0).GetCell(0);
            //Check that the fill color got copied
            byte[] srcColor = bookA.Workbook.CustomPalette.GetColor(0x9);
            byte[] destColor = bookB.Workbook.CustomPalette.GetColor(copiedCell.CellStyle.FillForegroundColor);
            Assert.IsTrue(srcColor[0]==destColor[0] && srcColor[1]==destColor[1] && srcColor[2]==destColor[2]);
            //Check that the font color got copied
            srcColor = bookA.Workbook.CustomPalette.GetColor(0x8);
            destColor = bookB.Workbook.CustomPalette.GetColor(copiedCell.CellStyle.GetFont(bookB).Color);
            Assert.IsTrue(srcColor[0] == destColor[0] && srcColor[1] == destColor[1] && srcColor[2] == destColor[2]);
            //Check that the fill color of the cell originally in the destination book is still intact
            srcColor = bookB.Workbook.CustomPalette.GetColor(0x8);
            Assert.IsTrue(srcColor[0] == 192 && srcColor[1] == 168 && srcColor[2] == 0);
            //Check that the font made it over okay
            Assert.AreEqual(copiedCell.CellStyle.GetFont(bookB).FontName, myFont.FontName);
        }

    public static ICellStyle CreateHeaderStyle(HSSFWorkbook wb, short foreColor, short backgroundColor)
    {
        var style = wb.CreateCellStyle();
        style.FillForegroundColor = (short)17;
        var font = wb.CreateFont();
        font.Color = foreColor;
        font.FontHeightInPoints = 12;
        style.SetFont(font);

        style.FillBackgroundColor = 16;
        style.FillForegroundColor = backgroundColor;
        style.FillPattern = FillPatternType.SOLID_FOREGROUND;


        return style;
    }

        /// <summary>
        /// 创建Excel标题行
        /// </summary>
        /// <param name="workbook">工作簿对象</param>
        /// <param name="sheet">Excel单页</param>
        /// <param name="strHeaderText">标题行文本</param>
        /// <param name="dtSource">DataTable数据源</param>
        public void CreateRowHeader(HSSFWorkbook workbook, Sheet sheet, int startRowIndex, string strHeaderText, DataTable dtSource)
        {
            #region 表头及样式
            {
                Row headerRow = sheet.CreateRow(startRowIndex);
                headerRow.HeightInPoints = 25;
                headerRow.CreateCell(0).SetCellValue(strHeaderText);

                CellStyle headStyle = workbook.CreateCellStyle();
                headStyle.Alignment = HorizontalAlignment.CENTER;
                Font font = workbook.CreateFont();
                font.FontHeightInPoints = 20;
                font.Boldweight = 700;
                headStyle.SetFont(font);
                headStyle.WrapText = true;

                headerRow.GetCell(0).CellStyle = headStyle;
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
            }
            #endregion

            #region 列头及样式
            {
                int[] arrColWidth = GetColumnWidth(dtSource);

                Row headerRow = sheet.CreateRow(startRowIndex+1);

                CellStyle headStyle = workbook.CreateCellStyle();
                headStyle.Alignment = HorizontalAlignment.CENTER;
                Font font = workbook.CreateFont();
                font.FontHeightInPoints = 10;
                font.Boldweight = 700;
                headStyle.SetFont(font);

                foreach (DataColumn column in dtSource.Columns)
                {
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                    headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

                    //设置列宽
                    sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);

                }
            }
            #endregion
        }

        /// <summary>
        /// 為資料建立成NPOI 的Workbook和Sheet
        /// </summary>
        /// <param name="sourceTable">要匯出的內容</param>
        /// <param name="workbook">The workbook.</param>
        /// <param name="sheet">The sheet.</param>
        /// <param name="headerRowIndex">標題欄的index起始位置</param>
        /// <param name="rowIndexStart">第一筆實際資料的起始位置</param>
        private void CreatWorksheetForSingleDataTable(DataTable sourceTable, ref HSSFWorkbook workbook,
            ref ISheet sheet, int headerRowIndex = 0, int rowIndexStart = 1)
        {
            IRow headerRow = sheet.CreateRow(headerRowIndex);

            // Set up the Header Coloumn cell style

            var headerCellStyle = workbook.CreateCellStyle();

            headerCellStyle.BorderTop = BorderStyle.Thin;

            headerCellStyle.BorderBottom = BorderStyle.Thin;

            headerCellStyle.Alignment = HorizontalAlignment.Center;

            // Set up the Header Coloumn cell font style

            var headerCellFontStyle = workbook.CreateFont();

            headerCellFontStyle.Boldweight = (short)FontBoldWeight.Bold;

            headerCellStyle.SetFont(headerCellFontStyle);

            // handling header.
            foreach (DataColumn column in sourceTable.Columns)
            {
                var headerCell = headerRow.CreateCell(column.Ordinal);
                headerCell.SetCellValue(column.ColumnName);
                headerCell.CellStyle = headerCellStyle;
            }

            // handling value.
            int rowIndex = rowIndexStart;

            foreach (DataRow row in sourceTable.Rows)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);

                foreach (DataColumn column in sourceTable.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                }

                rowIndex++;
            }
        }

        public void TestWriteSheetFont()
    {
        string             filepath = TempFile.GetTempFilePath("TestWriteSheetFont",
                                                    ".xls");
        FileStream out1  = new FileStream(filepath,FileMode.OpenOrCreate);
        HSSFWorkbook     wb   = new HSSFWorkbook();
        NPOI.SS.UserModel.ISheet        s    = wb.CreateSheet();
        IRow          r    = null;
        ICell         c    = null;
        IFont         fnt  = wb.CreateFont();
        NPOI.SS.UserModel.ICellStyle    cs   = wb.CreateCellStyle();

        fnt.Color=(NPOI.HSSF.Util.HSSFColor.Red.Index);
        fnt.Boldweight= (short)FontBoldWeight.Bold;
        cs.SetFont(fnt);
        for (short rownum = ( short ) 0; rownum < 100; rownum++)
        {
            r = s.CreateRow(rownum);

            // r.SetRowNum(( short ) rownum);
            for (short cellnum = ( short ) 0; cellnum < 50; cellnum += 2)
            {
                c = r.CreateCell(cellnum);
                c.SetCellValue(rownum * 10000 + cellnum
                               + ((( double ) rownum / 1000)
                                  + (( double ) cellnum / 10000)));
                c = r.CreateCell(cellnum + 1);
                c.SetCellValue("TEST");
                c.CellStyle = (cs);
            }
        }
        wb.Write(out1);
        out1.Close();
        SanityChecker sanityChecker = new SanityChecker();
        sanityChecker.CheckHSSFWorkbook(wb);
        Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
        Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");

        // assert((s.LastRowNum == 99));
    }