NPOI 合并单元格设置边框背景黑掉问题

最新推荐文章于 2024-01-09 16:15:31 发布

闪耀星星

最新推荐文章于 2024-01-09 16:15:31 发布

阅读量945

点赞数

文章标签： NPOI

本文链接：https://blog.csdn.net/zhchfsky/article/details/117714030

版权

本文详细介绍了如何使用NPOI库在Excel中创建带有合并单元格和自定义边框的表格，包括设置单元格样式、颜色和边框规则。通过扩展方法SetBorder，可以灵活控制单元格边框的样式和颜色，确保生成的Excel表格符合需求。

摘要由CSDN通过智能技术生成

需要的效果：

源码：

XSSFWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("测试");
sheet.DefaultRowHeightInPoints = 19.5F;
//行1
IRow row = sheet.CreateRow(0);
//列1
ICell cell = row.CreateCell(0);
//列2
cell = row.CreateCell(1);
cell.SetCellValue("表头");
XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.SetFillForegroundColor(new XSSFColor(new byte[] { 2, 162, 116 }));
style.FillPattern = FillPattern.SolidForeground;
byte[] rgb = style.FillForegroundXSSFColor.ARGB;
Console.WriteLine(System.Drawing.Color.FromArgb(rgb[0], rgb[1], rgb[2], rgb[3]).ColorToString());
cell.CellStyle = style;
//行2
row = sheet.CreateRow(1);
//列1
cell = row.CreateCell(0);
//列2
cell = row.CreateCell(1);
cell.SetCellValue("区域");
//列3
cell = row.CreateCell(2);
cell.SetCellValue("省份");
//列4
cell = row.CreateCell(3);
cell.SetCellValue("1月");

//合并单元格
CellRangeAddress merge = new CellRangeAddress(0, 0, 1, 9);
sheet.AddMergedRegion(merge);
sheet.AddMergedRegion(new CellRangeAddress(1, 2, 1, 1));
sheet.AddMergedRegion(new CellRangeAddress(1, 2, 2, 2));
sheet.AddMergedRegion(new CellRangeAddress(1, 1, 3, 5));

//设置合并单元格边框
RegionUtil.SetBorderBottom((int)BorderStyle.Thin, merge, sheet);
RegionUtil.SetBorderRight((int)BorderStyle.Thin, merge, sheet);

row = sheet.GetRow(0);
cell = row.GetCell(1);
Console.WriteLine(cell.StringCellValue);

using (FileStream fs = File.Create("E:\\test.xlsx"))
{
	workbook.Write(fs, false);
}

生成的Excel，单元格黑底了

去掉设置边框的代码后：

//设置合并单元格边框
//RegionUtil.SetBorderBottom((int)BorderStyle.Thin, merge, sheet);
//RegionUtil.SetBorderRight((int)BorderStyle.Thin, merge, sheet);

解决方法：

[Flags]
public enum JmBorderSide : int
{
    None = 0,
    Left = 1,
    Top = 2,
    Right = 4,
    Bottom = 8
}

扩展方法：

using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;

/// <summary>
/// 设置边框
/// </summary>
/// <param name="region">合并区域</param>
/// <param name="side">边框</param>
/// <param name="borderStyle">边框样式</param>
/// <param name="color">颜色</param>
/// <param name="sheet">表单</param>
public static void SetBorder(this CellRangeAddress region, JmBorderSide side, NPOI.SS.UserModel.BorderStyle borderStyle, XSSFColor color,ISheet sheet)
{
	if(region == null || side == JmBorderSide.None || sheet == null)
	{
		return;
	}

	bool init = false;
	XSSFCellStyle cellStyle = null;
	for(int i=region.FirstRow,j = region.LastRow;i<=j;i++)
	{
		IRow row = sheet.GetRow(i);
		if (row == null) continue;
		for (int m=region.FirstColumn,n=region.LastColumn;m<=n;m++)
		{
			ICell cell = CellUtil.GetCell(row,m);
			if (cell == null) continue;

			if(cellStyle == null)
			{
				cellStyle = (XSSFCellStyle)cell.CellStyle;
			}

			if(cellStyle == null)
			{
				cellStyle = (XSSFCellStyle)sheet.Workbook.CreateCellStyle();
			}

			if(!init)
			{

				init = true;
				if((side & JmBorderSide.Left) == JmBorderSide.Left)
				{
					cellStyle.BorderLeft = borderStyle;
					if(color != null)
					{
						cellStyle.SetLeftBorderColor(color);
					}
				}

				if ((side & JmBorderSide.Top) == JmBorderSide.Top)
				{
					cellStyle.BorderTop = borderStyle;
					if (color != null)
					{
						cellStyle.SetTopBorderColor(color);
					}
				}

				if ((side & JmBorderSide.Right) == JmBorderSide.Right)
				{
					cellStyle.BorderRight = borderStyle;
					if (color != null)
					{
						cellStyle.SetRightBorderColor(color);
					}
				}

				if ((side & JmBorderSide.Bottom) == JmBorderSide.Bottom)
				{
					cellStyle.BorderBottom = borderStyle;
					if (color != null)
					{
						cellStyle.SetBottomBorderColor(color);
					}
				}
			}

			cell.CellStyle = cellStyle;
		}
	}
}

设置边框：

//设置合并单元格边框
//RegionUtil.SetBorderBottom((int)BorderStyle.Thin, merge, sheet);
//RegionUtil.SetBorderRight((int)BorderStyle.Thin, merge, sheet);

merge.SetBorder(JmBorderSide.Bottom | JmBorderSide.Right, BorderStyle.Thin, new XSSFColor(IndexedColors.Red), sheet);

效果：