C# HSSFWorkbook.CreateCellStyle方法代碼示例

        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;
        }

    /// <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 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();
        }

        private static void SetCellValue(HSSFWorkbook workbook, ICell workCell, Type type, dynamic row, Cell cell)
        {
            var value = type.GetProperty(cell.Field).GetValue(row);
            if (value == null)
            {
                return;
            }

            if (value is DateTime)
            {
                workCell.SetCellValue((DateTime)value);
            }
            else if (value is int)
            {
                workCell.SetCellValue((int)value);
            }
            else if (value is double)
            {
                workCell.SetCellValue((double)value);
            }
            else
            {
                workCell.SetCellValue(value.ToString());
            }

            if (!string.IsNullOrWhiteSpace(cell.Format))
            {
                var cellStyle = workbook.CreateCellStyle();
                var format = workbook.CreateDataFormat();
                cellStyle.DataFormat = format.GetFormat(cell.Format);
                workCell.CellStyle = cellStyle;
            }
        }

        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");
        }

        //數據導出為Excel
        private void btnExport_Click(object sender, EventArgs e)
        {
            string sql = "select CC_AutoId,CC_CustomerName,CC_CellPhone,CC_Landline,CC_BuyDate,CC_CarNum,CC_BracketNum from T_Customers";
            using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, CommandType.Text))
            {
                if (reader.HasRows)
                {

                    IWorkbook wb = new HSSFWorkbook();
                    ISheet sheet = wb.CreateSheet("customers");
                    int rowIndex = 0;//手動處理行索引
                    while (reader.Read())
                    {
                        int autoId = reader.GetInt32(0);
                        string name = reader.GetString(1);
                        string cellPhone = reader.GetString(2);
                        //數據為空,那麽就不能Get..,所以要在這裏判斷
                        string landLine = reader.IsDBNull(3) ? null : reader.GetString(3);//可為空

                        DateTime BuyDate = reader.GetDateTime(4);
                        string carNum = reader.GetString(5);
                        string bracketNum = reader.GetString(6);
                        IRow row = sheet.CreateRow(rowIndex);
                        rowIndex++;

                        row.CreateCell(0).SetCellValue(autoId);
                        row.CreateCell(1).SetCellValue(name);
                        row.CreateCell(2).SetCellValue(cellPhone);
                        //如果為空或Null,把Excel這個單元格設置為值為空
                        if (string.IsNullOrEmpty(landLine))
                        {
                            row.CreateCell(3).SetCellType(CellType.BLANK);
                        }
                        else
                        {
                            row.CreateCell(3).SetCellValue(landLine);
                        }

                        //============設置日期格式=======
                        //創建單元格樣式對象
                        ICellStyle cellStyle = wb.CreateCellStyle();
                        //設置單元格樣式對象屬性
                        cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm");
                        ICell BuyDateRow = row.CreateCell(4);
                        BuyDateRow.CellStyle = cellStyle;
                        BuyDateRow.SetCellValue(BuyDate);
                        //==============設置完成========

                        row.CreateCell(5).SetCellValue(carNum);
                        row.CreateCell(6).SetCellValue(bracketNum);

                    }
                    using (FileStream fs = File.OpenWrite("Customers.xls"))
                    {
                        wb.Write(fs);
                        MessageBox.Show("ok");
                    }
                }
            }
        }

        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);
                }
            }
        }

        private void FillRows(HSSFWorkbook workbook, ISheet workSheet, List<Cell> cells, dynamic rows)
        {
            var dateStyle = workbook.CreateCellStyle();
            var format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

            var currentRow = 1;

            if (rows == null)
            {
                return;
            }
            foreach (var row in rows)
            {
                var workRow = workSheet.CreateRow(currentRow);
                var currentCol = 0;
                var type = row.GetType();

                foreach (var cell in cells)
                {
                    var workCell = workRow.CreateCell(currentCol);
                    SetCellValue(workbook, workCell, type, row, cell);

                    currentCol++;
                }
                currentRow++;
            }
        }

        public void TestSetAlignment()
        {
            // Create initial cell with default style
            IWorkbook wkb = new HSSFWorkbook();
            ISheet sheet = wkb.CreateSheet();
            IRow row = sheet.CreateRow(0);
            ICell cell = row.CreateCell(0);

            // Create a new cell with default style
            ICell cell2 = row.CreateCell(1);

            // Init a new cell style cloned from the one of cell 0
            cell2.CellStyle = wkb.CreateCellStyle();
            cell2.CellStyle.CloneStyleFrom(cell.CellStyle);

            // At this time cell style index should be different
            Assert.AreNotEqual(cell.CellStyle.Index, cell2.CellStyle.Index);

            // Set an arbitraty cell style property to differentiate the two styles
            cell.CellStyle.Alignment = HorizontalAlignment.Right;

            // Try to make the same change so that CellUtil will get existing style
            CellUtil.SetAlignment(cell2, wkb, (short)HorizontalAlignment.Right);

            // Check that cell style has properly been set to HorizontalAlignment.Right
            Assert.AreEqual(cell2.CellStyle.Alignment, HorizontalAlignment.Right);

            // Check that cell style index are the same again
            Assert.AreEqual(cell.CellStyle.Index, cell2.CellStyle.Index);

            // Init a new cell style cloned from the one of cell 0
            cell2.CellStyle = wkb.CreateCellStyle();
            cell2.CellStyle.CloneStyleFrom(cell.CellStyle);

            // Set an arbitraty cell style property to differentiate the two styles
            cell.CellStyle.Alignment = HorizontalAlignment.Left;

            // Try to make different change so that CellUtil will get new style
            CellUtil.SetAlignment(cell2, wkb, (short)HorizontalAlignment.Center);

            // Check that cell style has alignement property set to HorizontalAlignment.Center
            Assert.AreEqual(cell2.CellStyle.Alignment, HorizontalAlignment.Center);

            // Check that cell style index are different
            Assert.AreNotEqual(cell.CellStyle.Index, cell2.CellStyle.Index);
        }

        /// <summary>
        /// 導出內容到excel下載
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void ExportToExcel()
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            var sheet = workbook.CreateSheet();

            //創建一個單元格樣式
            var backgroundColorStyle = workbook.CreateCellStyle();
            backgroundColorStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND;
            backgroundColorStyle.FillForegroundColor = HSSFColor.BLUE.index;

            //創建表頭行列及賦值樣式
            var headTitles = new[] { "StudentCode", "ClassCode", "RoomName" };
            var headRow = sheet.CreateRow(0);
            for (var i = 0; i < 3; i++)
            {
                var cell = headRow.CreateCell(i);
                if (i == 0)
                    cell.CellStyle = backgroundColorStyle;
                cell.SetCellValue(headTitles[i]);
            }

            //創建數據行列
            for (int i = 1; i < this._lessenList.Count + 1; i++)
            {
                var lesson = this._lessenList[i - 1];

                var row = sheet.CreateRow(i);
                if (lesson.StudentCode == "BJ02")
                    row.Height = 50 * 20;

                //設置背景色
                var cellStudentCode = row.CreateCell(0);
                cellStudentCode.CellStyle = backgroundColorStyle;
                cellStudentCode.SetCellValue(lesson.StudentCode);

                row.CreateCell(1).SetCellValue(lesson.ClassCode);
                row.CreateCell(2).SetCellValue(lesson.RoomName);
            }

            //數據寫進內存流
            MemoryStream memoryStream = new MemoryStream();
            workbook.Write(memoryStream);

            //銷毀對象
            workbook = null;
            sheet = null;
            headRow = null;

            
            //將內存流以附件形式輸出到客戶端
            var bytes = memoryStream.ToArray();
            this._response.AddHeader("Content-Disposition", "attachment;filename=1.xls");
            this._response.AddHeader("Content-Length", bytes.Length.ToString());
            this._response.OutputStream.Write(bytes, 0, bytes.Length);
            this._response.Flush();
        }

 public ICellStyle CreateBorderCellStyle(HSSFWorkbook wb)
 {
     ICellStyle style = wb.CreateCellStyle();
     style.Alignment = HorizontalAlignment.Center;
     style.VerticalAlignment = VerticalAlignment.Center;
     style.BorderTop = BorderStyle.Thin;
     style.BorderBottom = BorderStyle.Thin;
     style.BorderLeft = BorderStyle.Thin;
     style.BorderRight = BorderStyle.Thin;
     return style;
 }

        protected void btnExportExcel_Click(object sender,EventArgs e)
        {
            HSSFWorkbook workBook = new HSSFWorkbook();

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "杭州農副產品物流網絡科技有限公司";
            workBook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "采購配送係統配送價格單頁";
            workBook.SummaryInformation = si;

            HSSFSheet sheet = workBook.CreateSheet("價格單頁");

            DataTable dt = logic.product.listWithPriceByBuyer(companyId);

            int rowIndex = 0;
            int colIndex = 0;

            HSSFRow newRow = null;
            HSSFCellStyle style = null;
            HSSFCell cell=null;

            foreach (DataRow row in dt.Rows)
            {
                newRow = sheet.CreateRow(rowIndex++);
                colIndex = 0;
                foreach (DataColumn col in dt.Columns)
                {
                    cell=newRow.CreateCell(colIndex++);
                    cell.SetCellValue(row[col].ToString());
                    if (rowIndex % 2 == 1)
                    {
                        style = workBook.CreateCellStyle();
                        style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.GREY_80_PERCENT.index;
                        style.FillPattern = HSSFCellStyle.SOLID_FOREGROUND;
                        cell.CellStyle = style;
                    }
                }
            }

            string filename = ddlCompanyList.SelectedItem.Text + ".xls";
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8)));
            Response.Clear();

            MemoryStream memoryStream = new MemoryStream();
            workBook.Write(memoryStream);

            Response.BinaryWrite(memoryStream.GetBuffer());
            Response.End();
        }

        public void SetUp()
        {
            wb = new HSSFWorkbook();
            ISheet sheet = wb.CreateSheet("new sheet");
            style = wb.CreateCellStyle();
            IDataFormat fmt = wb.CreateDataFormat();
            style.DataFormat=(fmt.GetFormat("hh:mm:ss"));

            cell11 = sheet.CreateRow(0).CreateCell(0);
            form = new DataFormatter();

            Evaluator = new HSSFFormulaEvaluator(wb);
        }