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(,  * );

            sh.SetColumnWidth(,  * );

            sh.SetColumnWidth(,  * );

            sh.SetColumnWidth(,  * );

            int i = ;

            #region 练习合并单元格

            sh.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(, , , ));

            //CellRangeAddress（）该方法的参数次序是：开始行号，结束行号，开始列号，结束列号。

            IRow row0 = sh.CreateRow();

            row0.Height =  * ;

            ICell icell1top0 = row0.CreateCell();

            icell1top0.CellStyle = Getcellstyle(wb, stylexls.头);

            icell1top0.SetCellValue("标题合并单元");

            #endregion

            i++;

            #region 设置表头

            IRow row1 = sh.CreateRow();

            row1.Height =  * ;

            ICell icell1top = row1.CreateCell();

            icell1top.CellStyle = Getcellstyle(wb, stylexls.头);

            icell1top.SetCellValue("网站名");

            ICell icell2top = row1.CreateCell();

            icell2top.CellStyle = Getcellstyle(wb, stylexls.头);

            icell2top.SetCellValue("网址");

            ICell icell3top = row1.CreateCell();

            icell3top.CellStyle = Getcellstyle(wb, stylexls.头);

            icell3top.SetCellValue("百度快照");

            ICell icell4top = row1.CreateCell();

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

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

            //上面基本都是设共公的设置

            //下面列出了常用的字段类型

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

                    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
秒客网

NPOI设置单元格格式

相关文章
