NPOI设置单元格格式

NPOI设置单元格格式

设置水平居中

//设置style
ICellStyle cellstyle = workbook.CreateCellStyle();
cellstyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellstyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;

设置行高

IRow row1 = sh.CreateRow(1);
row1.Height = 20 * 20;

设置单元的宽度

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

设置单元格颜色(固定色彩)

使用NPOI设置Excel单元格背景颜色时,应该设置FillForegroundColor属性,而且还要设置FillPattern才行。

style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.PINK.index;
style.FillPattern = FillPatternType.SOLID_FOREGROUND;

设置单元格颜色(RGB)

HSSFPalette palette = wk.GetCustomPalette(); //调色板实例
palette.SetColorAtIndex((short)8, 141, 180, 226);  //第一个参数:设置调色板新增颜色的编号,自已设置即可;取值范围8-64
HSSFColor hssFColor = palette.FindColor(141, 180, 226);
style_2.FillForegroundColor = hssFColor.Indexed;
style_2.FillPattern = FillPattern.SolidForeground;
//合并操作

sheet.AddMergedRegion(new CellRangeAddress(index["firstRow"], index["lastRow"], index["firstCol"], index["lastCol"]));//起始行,结束行,起始列,结束列

//设置合并后style
var cell = sheet.GetRow(index["firstRow"]).GetCell(index["firstCol"]);
cell.CellStyle = cellstyle;

参考文献:

https://blog.csdn.net/qq_38370387/article/details/113258871


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

ICell icell2top = row1.CreateCell(1);
icell2top.CellStyle = Getcellstyle(wb, stylexls.);
//创建一个常用的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
  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 酷酷鲨 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值