Пример #1
0
 /// <summary>
 /// 设置边框线
 /// </summary>
 /// <param name="style"></param>
 /// <param name="top"></param>
 /// <param name="bottom"></param>
 /// <param name="left"></param>
 /// <param name="right"></param>
 private static void SetBorder(HSSFCellStyle style, BorderStyle top, BorderStyle bottom, BorderStyle left, BorderStyle right)
 {
     style.BorderTop    = top;
     style.BorderBottom = bottom;
     style.BorderLeft   = left;
     style.BorderRight  = right;
 }
 public void setBorders(string name, NPOI.SS.UserModel.BorderStyle type)
 {
     ExcelStyles[name].BorderBottom = type;
     ExcelStyles[name].BorderLeft   = type;
     ExcelStyles[name].BorderRight  = type;
     ExcelStyles[name].BorderTop    = type;
 }
Пример #3
0
        //SetStyleCell cho nhung truong hop nhu Onsite, Vang...
        private HSSFCellStyle xsSetStyle(HSSFCellStyle setStyle, short Color, NPOI.SS.UserModel.BorderStyle Border)
        {
            setStyle.Alignment           = NPOI.SS.UserModel.HorizontalAlignment.Center;
            setStyle.FillForegroundColor = Color;
            setStyle.FillPattern         = FillPattern.SolidForeground;
            setStyle.BorderBottom        = Border;
            setStyle.BorderRight         = NPOI.SS.UserModel.BorderStyle.Thin;

            return(setStyle);
        }
Пример #4
0
        /// <summary>
        /// 设置四边边框样式
        /// </summary>
        /// <param name="row"></param>
        /// <param name="col"></param>
        /// <param name="borderStyle"></param>
        /// <param name="wb"></param>
        public static void SetCellBorderAll(int row, int col, NPOI.SS.UserModel.BorderStyle borderStyle, ref HSSFWorkbook wb)
        {
            HSSFCellStyle cellStyle = (HSSFCellStyle)wb.CreateCellStyle();

            HSSFSheet sheet = (HSSFSheet)wb.GetSheetAt(0);

            if (sheet.GetRow(row - 1) == null)
            {
                HSSFRow t_row = (HSSFRow)sheet.CreateRow(row - 1);

                HSSFCell t_cell = (HSSFCell)t_row.CreateCell(col - 1);

                cellStyle.CloneStyleFrom(t_cell.CellStyle);
                cellStyle.BorderBottom = borderStyle;
                cellStyle.BorderTop    = borderStyle;
                cellStyle.BorderLeft   = borderStyle;
                cellStyle.BorderRight  = borderStyle;

                t_cell.CellStyle = cellStyle;
            }
            else
            {
                HSSFRow t_row = (HSSFRow)sheet.GetRow(row - 1);

                if (t_row.GetCell(col - 1) == null)
                {
                    HSSFCell t_cell = (HSSFCell)t_row.CreateCell(col - 1);


                    cellStyle.CloneStyleFrom(t_cell.CellStyle);
                    cellStyle.BorderBottom = borderStyle;
                    cellStyle.BorderTop    = borderStyle;
                    cellStyle.BorderLeft   = borderStyle;
                    cellStyle.BorderRight  = borderStyle;

                    t_cell.CellStyle = cellStyle;
                }
                else
                {
                    HSSFCell t_cell = (HSSFCell)t_row.GetCell(col - 1);


                    cellStyle.CloneStyleFrom(t_cell.CellStyle);
                    cellStyle.BorderBottom = borderStyle;
                    cellStyle.BorderTop    = borderStyle;
                    cellStyle.BorderLeft   = borderStyle;
                    cellStyle.BorderRight  = borderStyle;

                    t_cell.CellStyle = cellStyle;
                }
            }
        }
Пример #5
0
        //边框分解 上 右 下 左
        private string GetBoderStyle(ICellStyle cellstyle)
        {
            NPOI.SS.UserModel.BorderStyle boderstyle = cellstyle.BorderTop;
            string Result          = "border-type:";
            string topboderstyle   = ConvertBorderStyleToString(cellstyle.BorderTop);
            string rightboderstyle = ConvertBorderStyleToString(cellstyle.BorderRight);
            string bomboderstyle   = ConvertBorderStyleToString(cellstyle.BorderBottom);
            string leftboderstyle  = ConvertBorderStyleToString(cellstyle.BorderLeft);

            if ((topboderstyle == "") && (rightboderstyle == "") && (bomboderstyle == "") && (leftboderstyle == ""))
            {
                return("");
            }
            Result = Result + topboderstyle + " " + rightboderstyle + " " + bomboderstyle + " " + leftboderstyle + ";";
            return(Result);
        }
Пример #6
0
        private string ConvertBorderStyleToString(NPOI.SS.UserModel.BorderStyle boderstyle)
        {
            switch (boderstyle)
            {
            case NPOI.SS.UserModel.BorderStyle.Thin:
                return("THIN");

            case NPOI.SS.UserModel.BorderStyle.Medium:
                return("MEDIUM");

            case NPOI.SS.UserModel.BorderStyle.Dashed:
                return("DASHED");

            case NPOI.SS.UserModel.BorderStyle.Hair:
                return("HAIR");

            case NPOI.SS.UserModel.BorderStyle.Thick:
                return("THICK");

            case NPOI.SS.UserModel.BorderStyle.Double:
                return("DOUBLE");

            case NPOI.SS.UserModel.BorderStyle.Dotted:
                return("DOTTED");

            case NPOI.SS.UserModel.BorderStyle.MediumDashed:
                return("MEDIUMDASHED");

            case NPOI.SS.UserModel.BorderStyle.DashDot:
                return("DASHDOT");

            case NPOI.SS.UserModel.BorderStyle.MediumDashDot:
                return("MEDIUMDASHDOT");

            case NPOI.SS.UserModel.BorderStyle.DashDotDot:
                return("DASHDOTDOT");

            case NPOI.SS.UserModel.BorderStyle.MediumDashDotDot:
                return("MEDIUMDASHDOTDOT");

            case NPOI.SS.UserModel.BorderStyle.SlantedDashDot:
                return("SLANTEDDASHDOT");

            default:
                return("None");
            }
        }
Пример #7
0
        //npoi中单个工作薄最多可以创建4000个单元格样式,当时我也是卡在单元格样式这块卡了半天
        //npoi中会默认创建一个单元格样式,也就是我们在新建excel中看到的那种样式
        //每当我们创建一个单元格时,单元格样式默认为上述的单元格样式
        //为什么要强调这个默认的单元格样式呢?因为我被他坑过(泪奔。。。)
        //如果你要设置某个单元格的单元格样式,一定要先创建新的单元格样式,切勿直接修改,因为你会发现影响的可不只是这一个单元格
        //当时我傻傻的认为每个单元格都有自己的样式,结果他们的样式都指向系统创建的默认的样式,而不是自己new的
        //具体的使用后面呈现

        #endregion

        #region 四、单元格边框问题

        //我们在excel中设置边框非常方便,鼠标拖一下,右击设置单元格样式,设置边框即可,但是在npoi可没这么简单,这个过程我们要用代码写出来
        //主观上边框分为外边框和内边框
        //但实际上投射到单元格就是16种单元格样式的组合!!

        private ICellStyle GetCellBorderStyle(int style, NPOI.SS.UserModel.BorderStyle outStyle, NPOI.SS.UserModel.BorderStyle inStyle)
        {
            ICellStyle cellStyle = _hssfWorkbook.CreateCellStyle();

            cellStyle.BorderTop    = inStyle;
            cellStyle.BorderLeft   = inStyle;
            cellStyle.BorderRight  = inStyle;
            cellStyle.BorderBottom = inStyle;

            switch (style)
            {
            //九宫格中间的样式
            case 0:
                break;

            //九宫格左上角样式
            case 1:
                cellStyle.BorderTop  = outStyle;
                cellStyle.BorderLeft = outStyle;
                break;

            //九宫格上方样式
            case 2:
                cellStyle.BorderTop = outStyle;
                break;

            //九宫格右上角样式
            case 3:
                cellStyle.BorderTop   = outStyle;
                cellStyle.BorderRight = outStyle;
                break;

            //九宫格右边样式
            case 4:
                cellStyle.BorderRight = outStyle;
                break;

            //九宫格右下角样式
            case 5:
                cellStyle.BorderBottom = outStyle;
                cellStyle.BorderRight  = outStyle;
                break;

            //九宫格下方样式
            case 6:
                cellStyle.BorderBottom = outStyle;
                break;

            //九宫格左下角样式
            case 7:
                cellStyle.BorderBottom = outStyle;
                cellStyle.BorderLeft   = outStyle;
                break;

            //九宫格左边样式
            case 8:
                cellStyle.BorderLeft = outStyle;
                break;

            //单行单列样式
            case 9:
                cellStyle.BorderTop    = outStyle;
                cellStyle.BorderLeft   = outStyle;
                cellStyle.BorderRight  = outStyle;
                cellStyle.BorderBottom = outStyle;
                break;

            //单列多行上方样式
            case 10:
                cellStyle.BorderTop   = outStyle;
                cellStyle.BorderLeft  = outStyle;
                cellStyle.BorderRight = outStyle;
                break;
            //单列多行中间样式

            case 11:
                cellStyle.BorderLeft  = outStyle;
                cellStyle.BorderRight = outStyle;
                break;

            //单列多行下方样式
            case 12:
                cellStyle.BorderLeft   = outStyle;
                cellStyle.BorderRight  = outStyle;
                cellStyle.BorderBottom = outStyle;
                break;

            //单行多列右边样式
            case 13:
                cellStyle.BorderTop    = outStyle;
                cellStyle.BorderRight  = outStyle;
                cellStyle.BorderBottom = outStyle;
                break;

            //单行多列中间样式
            case 14:
                cellStyle.BorderTop    = outStyle;
                cellStyle.BorderBottom = outStyle;
                break;

            //单行多列下方样式
            case 15:
                cellStyle.BorderTop    = outStyle;
                cellStyle.BorderLeft   = outStyle;
                cellStyle.BorderBottom = outStyle;
                break;

            default:
                break;
            }

            return(cellStyle);
        }
Пример #8
0
        //outStyle为外边框样式, inStyle为内边框样式,颜色如果想换的话可以自己写函数,这里例子就不再写了
        public void SetCellBorder(HSSFSheet sheet, NPOI.SS.UserModel.BorderStyle outStyle, NPOI.SS.UserModel.BorderStyle inStyle)
        {
            ICellStyle style       = GetCellBorderStyle(0, outStyle, inStyle);
            ICellStyle styleTop    = GetCellBorderStyle(2, outStyle, inStyle);
            ICellStyle styleRight  = GetCellBorderStyle(4, outStyle, inStyle);
            ICellStyle styleBottom = GetCellBorderStyle(6, outStyle, inStyle);
            ICellStyle styleLeft   = GetCellBorderStyle(8, outStyle, inStyle);

            int firstRowNum = sheet.FirstRowNum;
            int lastRowNum  = sheet.LastRowNum;

            for (int i = firstRowNum; i <= lastRowNum; i++)
            {
                IRow row          = sheet.GetRow(i);
                int  firstCellNum = row.FirstCellNum;
                int  lastCellNum  = row.LastCellNum;

                for (int j = firstCellNum; j < lastCellNum; j++)
                {
                    ICell cell = row.GetCell(j);

                    if (lastCellNum == 0)
                    {
                        //单行单列
                        if (lastRowNum == 0)
                        {
                            cell.CellStyle = GetCellBorderStyle(9, outStyle, inStyle);
                        }
                        else
                        {
                            //单列多行
                            if (i == 0)
                            {
                                cell.CellStyle = GetCellBorderStyle(10, outStyle, inStyle);
                            }
                            else if (i == lastRowNum)
                            {
                                cell.CellStyle = GetCellBorderStyle(12, outStyle, inStyle);
                            }
                            else
                            {
                                cell.CellStyle = GetCellBorderStyle(15, outStyle, inStyle);
                            }
                        }
                    }
                    else if (lastRowNum == 0)
                    {
                        //单行多列

                        if (j == 0)
                        {
                            cell.CellStyle = GetCellBorderStyle(11, outStyle, inStyle);
                        }
                        else if (j == lastCellNum - 1)
                        {
                            cell.CellStyle = GetCellBorderStyle(13, outStyle, inStyle);
                        }
                        else
                        {
                            cell.CellStyle = GetCellBorderStyle(14, outStyle, inStyle);
                        }
                    }
                    //多行多列
                    else
                    {
                        if (i == 0)
                        {
                            if (j == 0)
                            {
                                cell.CellStyle = GetCellBorderStyle(1, outStyle, inStyle);
                            }
                            else if (j == lastCellNum - 1)
                            {
                                cell.CellStyle = GetCellBorderStyle(3, outStyle, inStyle);
                            }
                            else
                            {
                                cell.CellStyle = styleTop;
                            }
                        }
                        else if (i == lastRowNum)
                        {
                            if (j == 0)
                            {
                                cell.CellStyle = GetCellBorderStyle(7, outStyle, inStyle);
                            }
                            else if (j == lastCellNum - 1)
                            {
                                cell.CellStyle = GetCellBorderStyle(5, outStyle, inStyle);
                            }
                            else
                            {
                                cell.CellStyle = styleBottom;
                            }
                        }
                        else
                        {
                            if (j == 0)
                            {
                                cell.CellStyle = styleLeft;
                            }
                            else if (j == lastCellNum - 1)
                            {
                                cell.CellStyle = styleRight;
                            }
                            else
                            {
                                cell.CellStyle = style;
                            }
                        }
                    }
                }
            }
        }
Пример #9
0
        private ICellStyle getCellStyleWithSpBorder(XSSFWorkbook book, HorizontalAlignment alignment, IFont font, NPOI.SS.UserModel.BorderStyle bottom, NPOI.SS.UserModel.BorderStyle left, NPOI.SS.UserModel.BorderStyle right, NPOI.SS.UserModel.BorderStyle top)
        {
            ICellStyle cellStyle = book.CreateCellStyle();

            cellStyle.WrapText     = true;
            cellStyle.BorderBottom = bottom;
            cellStyle.BorderLeft   = left;
            cellStyle.BorderRight  = right;
            cellStyle.BorderTop    = top;

            cellStyle.SetFont(font);

            cellStyle.Alignment         = alignment;
            cellStyle.VerticalAlignment = VerticalAlignment.Center;

            cellStyle.IsLocked = true;

            return(cellStyle);
        }