/// <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; }
//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); }
/// <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; } } }
//边框分解 上 右 下 左 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); }
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"); } }
//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); }
//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; } } } } } }
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); }