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