NPOI設定單元格格式

轉自：http://www.cr173.com/html/18143_2.html

//建立一個常用的xls檔案
        private void button3_Click(object sender, EventArgs e)
        {          
            IWorkbook wb = new HSSFWorkbook();
            //建立表  
            ISheet sh = wb.CreateSheet("zhiyuan");
            //設定單元的寬度  
            sh.SetColumnWidth(0, 15 * 256);
            sh.SetColumnWidth(1, 35 * 256);
            sh.SetColumnWidth(2, 15 * 256);
            sh.SetColumnWidth(3, 10 * 256);
            int i = 0;
            #region 練習合并單元格
            sh.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 3));

            //CellRangeAddress（）該方法的參數次序是：開始行号，結束行号，開始列号，結束列号。
  
            IRow row0 = sh.CreateRow(0);
            row0.Height = 20 * 20;
            ICell icell1top0 = row0.CreateCell(0);
            icell1top0.CellStyle = Getcellstyle(wb, stylexls.頭);
            icell1top0.SetCellValue("标題合并單元");
            #endregion
            i++;
            #region 設定表頭
            IRow row1 = sh.CreateRow(1);
            row1.Height = 20 * 20;

            ICell icell1top = row1.CreateCell(0);
            icell1top.CellStyle = Getcellstyle(wb, stylexls.頭);
            icell1top.SetCellValue("網站名");

            ICell icell2top = row1.CreateCell(1);
            icell2top.CellStyle = Getcellstyle(wb, stylexls.頭);
            icell2top.SetCellValue("網址");

            ICell icell3top = row1.CreateCell(2);
            icell3top.CellStyle = Getcellstyle(wb, stylexls.頭);
            icell3top.SetCellValue("百度快照");

            ICell icell4top = row1.CreateCell(3);
            icell4top.CellStyle = Getcellstyle(wb, stylexls.頭);
            icell4top.SetCellValue("百度收錄");
            #endregion  
  
            using(FileStream stm=File.OpenWrite(@"c:/myMergeCell.xls"))
            {
                wb.Write(stm); 
                MessageBox.Show("提示：建立成功！");
            }
        }



        #region 定義單元格常用到樣式的枚舉
        public enum stylexls
        {
            頭,
            url,
            時間,
            數字,
            錢,
            百分比,
            中文大寫,
            科學計數法,
            預設
        }
        #endregion


        #region 定義單元格常用到樣式
        static ICellStyle Getcellstyle(IWorkbook wb, stylexls str)
        {
            ICellStyle cellStyle = wb.CreateCellStyle();

            //定義幾種字型  
            //也可以一種字型，寫一些公共屬性，然後在下面需要時加特殊的  
            IFont font12 = wb.CreateFont();
            font12.FontHeightInPoints = 10;
            font12.FontName = "微軟雅黑";


            IFont font = wb.CreateFont();
            font.FontName = "微軟雅黑";
            //font.Underline = 1;下劃線  


            IFont fontcolorblue = wb.CreateFont();
            fontcolorblue.Color = HSSFColor.OLIVE_GREEN.BLUE.index;
            fontcolorblue.IsItalic = true;//下劃線  
            fontcolorblue.FontName = "微軟雅黑";


            //邊框  
            cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
            cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.HAIR;
            cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.HAIR;
            cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.DOTTED;
            //邊框顔色  
            cellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.index;
            cellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.index;

            //背景圖形，我沒有用到過。感覺很醜  
            //cellStyle.FillBackgroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;  
            //cellStyle.FillForegroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;  
            cellStyle.FillForegroundColor = HSSFColor.WHITE.index;
            // cellStyle.FillPattern = FillPatternType.NO_FILL;  
            cellStyle.FillBackgroundColor = HSSFColor.BLUE.index;

            //水準對齊  
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;

            //垂直對齊  
            cellStyle.VerticalAlignment = VerticalAlignment.CENTER;

            //自動換行  
            cellStyle.WrapText = true;

            //縮進;當設定為1時，前面留的空白太大了。希旺官網改進。或者是我設定的不對  
            cellStyle.Indention = 0;

            //上面基本都是設共公的設定  
            //下面列出了常用的字段類型  
            switch (str)
            {
                case stylexls.頭:
                    // cellStyle.FillPattern = FillPatternType.LEAST_DOTS;  
                    cellStyle.SetFont(font12);
                    break;
                case stylexls.時間:
                    IDataFormat datastyle = wb.CreateDataFormat();

                    cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd");
                    cellStyle.SetFont(font);
                    break;
                case stylexls.數字:
                    cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
                    cellStyle.SetFont(font);
                    break;
                case stylexls.錢:
                    IDataFormat format = wb.CreateDataFormat();
                    cellStyle.DataFormat = format.GetFormat("￥#,##0");
                    cellStyle.SetFont(font);
                    break;
                case stylexls.url:
                    fontcolorblue.Underline = 1;
                    cellStyle.SetFont(fontcolorblue);
                    break;
                case stylexls.百分比:
                    cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
                    cellStyle.SetFont(font);
                    break;
                case stylexls.中文大寫:
                    IDataFormat format1 = wb.CreateDataFormat();
                    cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
                    cellStyle.SetFont(font);
                    break;
                case stylexls.科學計數法:
                    cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
                    cellStyle.SetFont(font);
                    break;
                case stylexls.預設:
                    cellStyle.SetFont(font);
                    break;
            }
            return cellStyle;


        }
        #endregion