第09章-Excel高级功能-合并单元格与冻结窗格
第九章：Excel高级功能-合并单元格与冻结窗格
9.1 合并单元格
9.1.1 合并单元格基础
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("合并示例");
// 合并单元格：A1:D1
// 参数：起始行, 结束行, 起始列, 结束列
CellRangeAddress mergedRegion = new CellRangeAddress(0, 0, 0, 3);
sheet.AddMergedRegion(mergedRegion);
// 设置合并单元格的值（只需设置左上角单元格）
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
cell.SetCellValue("这是合并后的标题");
9.1.2 合并单元格样式
// 合并单元格后，样式需要应用到左上角单元格
ICellStyle mergedStyle = workbook.CreateCellStyle();
mergedStyle.Alignment = HorizontalAlignment.Center;
mergedStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font = workbook.CreateFont();
font.IsBold = true;
font.FontHeightInPoints = 16;
mergedStyle.SetFont(font);
// 设置边框（合并区域的边框需要特殊处理）
mergedStyle.BorderTop = BorderStyle.Thin;
mergedStyle.BorderBottom = BorderStyle.Thin;
mergedStyle.BorderLeft = BorderStyle.Thin;
mergedStyle.BorderRight = BorderStyle.Thin;
cell.CellStyle = mergedStyle;
9.1.3 合并区域边框处理
/// <summary>
/// 为合并区域设置边框
/// </summary>
public static void SetMergedRegionBorder(ISheet sheet, CellRangeAddress region,
BorderStyle borderStyle, short borderColor, IWorkbook workbook)
{
ICellStyle borderStyle_ = workbook.CreateCellStyle();
borderStyle_.BorderTop = borderStyle;
borderStyle_.BorderBottom = borderStyle;
borderStyle_.BorderLeft = borderStyle;
borderStyle_.BorderRight = borderStyle;
borderStyle_.TopBorderColor = borderColor;
borderStyle_.BottomBorderColor = borderColor;
borderStyle_.LeftBorderColor = borderColor;
borderStyle_.RightBorderColor = borderColor;
for (int rowIdx = region.FirstRow; rowIdx <= region.LastRow; rowIdx++)
{
IRow row = sheet.GetRow(rowIdx) ?? sheet.CreateRow(rowIdx);
for (int colIdx = region.FirstColumn; colIdx <= region.LastColumn; colIdx++)
{
ICell cell = row.GetCell(colIdx) ?? row.CreateCell(colIdx);
ICellStyle cellStyle = workbook.CreateCellStyle();
if (cell.CellStyle != null)
{
cellStyle.CloneStyleFrom(cell.CellStyle);
}
// 设置边框
if (rowIdx == region.FirstRow)
cellStyle.BorderTop = borderStyle;
if (rowIdx == region.LastRow)
cellStyle.BorderBottom = borderStyle;
if (colIdx == region.FirstColumn)
cellStyle.BorderLeft = borderStyle;
if (colIdx == region.LastColumn)
cellStyle.BorderRight = borderStyle;
cellStyle.TopBorderColor = borderColor;
cellStyle.BottomBorderColor = borderColor;
cellStyle.LeftBorderColor = borderColor;
cellStyle.RightBorderColor = borderColor;
cell.CellStyle = cellStyle;
}
}
}
9.1.4 取消合并
// 获取所有合并区域
int mergedRegionCount = sheet.NumMergedRegions;
// 遍历合并区域
for (int i = 0; i < mergedRegionCount; i++)
{
CellRangeAddress region = sheet.GetMergedRegion(i);
Console.WriteLine($"合并区域 {i}: {region.FormatAsString()}");
}
// 取消特定的合并区域（通过索引）
sheet.RemoveMergedRegion(0);
// 取消所有合并
while (sheet.NumMergedRegions > 0)
{
sheet.RemoveMergedRegion(0);
}
9.1.5 合并单元格辅助类
/// <summary>
/// 合并单元格辅助类
/// </summary>
public static class MergeCellHelper
{
/// <summary>
/// 合并单元格并设置值和样式
/// </summary>
public static void MergeAndSetValue(ISheet sheet, int firstRow, int lastRow,
int firstCol, int lastCol, object value, ICellStyle style = null)
{
// 创建合并区域
CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
sheet.AddMergedRegion(region);
// 获取或创建左上角单元格
IRow row = sheet.GetRow(firstRow) ?? sheet.CreateRow(firstRow);
ICell cell = row.GetCell(firstCol) ?? row.CreateCell(firstCol);
// 设置值
if (value != null)
{
switch (value)
{
case string s:
cell.SetCellValue(s);
break;
case double d:
cell.SetCellValue(d);
break;
case DateTime dt:
cell.SetCellValue(dt);
break;
case bool b:
cell.SetCellValue(b);
break;
default:
cell.SetCellValue(value.ToString());
break;
}
}
// 设置样式
if (style != null)
{
cell.CellStyle = style;
}
}
/// <summary>
/// 创建带样式的合并标题
/// </summary>
public static void CreateMergedTitle(ISheet sheet, int row, int firstCol, int lastCol,
string title, IWorkbook workbook)
{
ICellStyle titleStyle = workbook.CreateCellStyle();
titleStyle.Alignment = HorizontalAlignment.Center;
titleStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font = workbook.CreateFont();
font.IsBold = true;
font.FontHeightInPoints = 14;
titleStyle.SetFont(font);
titleStyle.FillForegroundColor = IndexedColors.LightBlue.Index;
titleStyle.FillPattern = FillPattern.SolidForeground;
MergeAndSetValue(sheet, row, row, firstCol, lastCol, title, titleStyle);
}
/// <summary>
/// 检查单元格是否在合并区域内
/// </summary>
public static bool IsCellInMergedRegion(ISheet sheet, int row, int col)
{
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress region = sheet.GetMergedRegion(i);
if (region.IsInRange(row, col))
{
return true;
}
}
return false;
}
/// <summary>
/// 获取单元格所在的合并区域
/// </summary>
public static CellRangeAddress GetMergedRegion(ISheet sheet, int row, int col)
{
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress region = sheet.GetMergedRegion(i);
if (region.IsInRange(row, col))
{
return region;
}
}
return null;
}
}
9.2 冻结窗格
9.2.1 冻结行
// 冻结首行
sheet.CreateFreezePane(0, 1); // 冻结0列，1行
// 冻结前3行
sheet.CreateFreezePane(0, 3);
9.2.2 冻结列
// 冻结首列
sheet.CreateFreezePane(1, 0); // 冻结1列，0行
// 冻结前2列
sheet.CreateFreezePane(2, 0);
9.2.3 同时冻结行和列
// 冻结首行和首列
sheet.CreateFreezePane(1, 1);
// 冻结前2列和前3行
sheet.CreateFreezePane(2, 3);
// 带滚动位置的冻结
// CreateFreezePane(冻结列数, 冻结行数, 右侧首列, 下方首行)
sheet.CreateFreezePane(2, 3, 2, 3);
9.2.4 拆分窗格
// 水平拆分
// CreateSplitPane(x位置, y位置, 左列, 上行, 激活区域)
sheet.CreateSplitPane(0, 2000, 0, 10, PanePosition.LowerLeft);
// 垂直拆分
sheet.CreateSplitPane(2000, 0, 5, 0, PanePosition.UpperRight);
// 同时水平和垂直拆分
sheet.CreateSplitPane(2000, 2000, 5, 10, PanePosition.LowerRight);
// 拆分位置说明：x和y使用twip单位（1点 = 20 twip）
9.3 自动筛选
9.3.1 设置自动筛选区域
// 设置自动筛选区域
CellRangeAddress filterRange = new CellRangeAddress(0, 100, 0, 5);
sheet.SetAutoFilter(filterRange);
// 也可以使用字符串格式
// sheet.SetAutoFilter(CellRangeAddress.ValueOf("A1:F101"));
9.3.2 筛选示例
public static void CreateFilterExample()
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("筛选示例");
// 创建表头
IRow header = sheet.CreateRow(0);
header.CreateCell(0).SetCellValue("姓名");
header.CreateCell(1).SetCellValue("部门");
header.CreateCell(2).SetCellValue("职位");
header.CreateCell(3).SetCellValue("薪资");
// 创建数据
var data = new[]
{
("张三", "技术部", "工程师", 15000),
("李四", "技术部", "高级工程师", 25000),
("王五", "市场部", "经理", 20000),
("赵六", "人事部", "主管", 18000),
("钱七", "技术部", "工程师", 16000)
};
for (int i = 0; i < data.Length; i++)
{
IRow row = sheet.CreateRow(i + 1);
row.CreateCell(0).SetCellValue(data[i].Item1);
row.CreateCell(1).SetCellValue(data[i].Item2);
row.CreateCell(2).SetCellValue(data[i].Item3);
row.CreateCell(3).SetCellValue(data[i].Item4);
}
// 设置自动筛选
sheet.SetAutoFilter(new CellRangeAddress(0, data.Length, 0, 3));
// 保存
using (FileStream fs = new FileStream("筛选示例.xlsx", FileMode.Create))
{
workbook.Write(fs);
}
}
9.4 分组与大纲
9.4.1 行分组
// 行分组（将第3-5行分为一组）
sheet.GroupRow(2, 4); // 索引从0开始
// 设置分组层级
sheet.GroupRow(2, 4);
sheet.GroupRow(5, 7);
sheet.GroupRow(2, 7); // 嵌套分组
// 折叠分组
sheet.SetRowGroupCollapsed(2, true);
// 展开分组
sheet.SetRowGroupCollapsed(2, false);
9.4.2 列分组
// 列分组（将B-D列分为一组）
sheet.GroupColumn(1, 3);
// 折叠列分组
sheet.SetColumnGroupCollapsed(1, true);
// 展开列分组
sheet.SetColumnGroupCollapsed(1, false);
9.4.3 大纲设置
// 设置大纲级别的摘要行位置
// true: 摘要行在下方；false: 摘要行在上方
sheet.RowSumsBelow = true;
// 设置大纲级别的摘要列位置
// true: 摘要列在右侧；false: 摘要列在左侧
sheet.RowSumsRight = true;
9.4.4 分组示例
public static void CreateGroupingExample()
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("分组示例");
// 创建季度销售报表数据
string[] rows = {
"Q1", " 一月", " 二月", " 三月",
"Q2", " 四月", " 五月", " 六月",
"Q3", " 七月", " 八月", " 九月",
"Q4", " 十月", " 十一月", " 十二月",
"年度合计"
};
Random rand = new Random();
for (int i = 0; i < rows.Length; i++)
{
IRow row = sheet.CreateRow(i);
row.CreateCell(0).SetCellValue(rows[i]);
row.CreateCell(1).SetCellValue(rand.Next(10000, 50000));
}
// 设置分组
// Q1详细数据
sheet.GroupRow(1, 3);
// Q2详细数据
sheet.GroupRow(5, 7);
// Q3详细数据
sheet.GroupRow(9, 11);
// Q4详细数据
sheet.GroupRow(13, 15);
// 默认折叠
sheet.SetRowGroupCollapsed(1, true);
sheet.SetRowGroupCollapsed(5, true);
sheet.SetRowGroupCollapsed(9, true);
sheet.SetRowGroupCollapsed(13, true);
// 保存
using (FileStream fs = new FileStream("分组示例.xlsx", FileMode.Create))
{
workbook.Write(fs);
}
}
9.5 打印设置
9.5.1 基本打印设置
// 获取打印设置对象
IPrintSetup printSetup = sheet.PrintSetup;
// 纸张大小
printSetup.PaperSize = (short)PaperSize.A4;
// 打印方向
printSetup.Landscape = true; // 横向
printSetup.Landscape = false; // 纵向
// 缩放比例
printSetup.Scale = 80; // 80%
// 适合页面
printSetup.FitWidth = 1; // 适合1页宽
printSetup.FitHeight = 0; // 高度不限
// 首页页码
printSetup.PageStart = 1;
printSetup.UsePage = true;
// 打印质量（DPI）
printSetup.HResolution = 300;
printSetup.VResolution = 300;
// 页边距（单位：英寸）
sheet.SetMargin(MarginType.TopMargin, 1.0);
sheet.SetMargin(MarginType.BottomMargin, 1.0);
sheet.SetMargin(MarginType.LeftMargin, 0.75);
sheet.SetMargin(MarginType.RightMargin, 0.75);
sheet.SetMargin(MarginType.HeaderMargin, 0.5);
sheet.SetMargin(MarginType.FooterMargin, 0.5);
9.5.2 打印区域
// 设置打印区域
workbook.SetPrintArea(0, 0, 10, 0, 100); // sheetIndex, 起始列, 结束列, 起始行, 结束行
// 或使用字符串格式
workbook.SetPrintArea(0, "$A$1:$K$101");
// 获取打印区域
string printArea = workbook.GetPrintArea(0);
9.5.3 重复打印行（标题行）
// 设置重复打印的行（每页都打印的标题行）
sheet.RepeatingRows = new CellRangeAddress(0, 0, 0, 10); // 第1行
// 设置重复打印的列
sheet.RepeatingColumns = new CellRangeAddress(0, 100, 0, 0); // 第A列
9.5.4 页眉页脚
// 获取页眉页脚对象
IHeader header = sheet.Header;
IFooter footer = sheet.Footer;
// 设置页眉
header.Left = "公司名称";
header.Center = "报表标题";
header.Right = "&D"; // 日期
// 设置页脚
footer.Left = "机密文件";
footer.Center = "第 &P 页，共 &N 页";
footer.Right = "&T"; // 时间
// 特殊代码
// &P - 页码
// &N - 总页数
// &D - 日期
// &T - 时间
// &F - 文件名
// &A - 工作表名
// &B - 粗体开关
// &I - 斜体开关
// &U - 下划线开关
// &"字体名" - 字体
// &数字 - 字号
9.5.5 打印设置示例
public static void SetupPrinting(IWorkbook workbook, ISheet sheet)
{
// 打印设置
IPrintSetup printSetup = sheet.PrintSetup;
printSetup.PaperSize = (short)PaperSize.A4;
printSetup.Landscape = false;
printSetup.FitWidth = 1;
printSetup.FitHeight = 0;
// 页边距
sheet.SetMargin(MarginType.TopMargin, 1.0);
sheet.SetMargin(MarginType.BottomMargin, 1.0);
sheet.SetMargin(MarginType.LeftMargin, 0.75);
sheet.SetMargin(MarginType.RightMargin, 0.75);
// 重复标题行
sheet.RepeatingRows = new CellRangeAddress(0, 0, 0, 0);
// 打印区域
int lastRow = sheet.LastRowNum;
int lastCol = sheet.GetRow(0)?.LastCellNum ?? 0;
workbook.SetPrintArea(workbook.GetSheetIndex(sheet), 0, lastCol - 1, 0, lastRow);
// 页眉页脚
IHeader header = sheet.Header;
header.Center = "月度销售报表";
header.Right = "&D";
IFooter footer = sheet.Footer;
footer.Center = "第 &P 页，共 &N 页";
footer.Right = "机密";
// 打印网格线
sheet.IsPrintGridlines = true;
// 打印行列标题
sheet.IsPrintRowAndColumnHeadings = false;
// 居中打印
sheet.HorizontallyCenter = true;
sheet.VerticallyCenter = false;
}
9.6 显示设置
9.6.1 网格线和标题
// 显示/隐藏网格线
sheet.DisplayGridlines = false;
// 显示/隐藏行列标题
sheet.DisplayRowColHeadings = false;
// 显示/隐藏零值
sheet.DisplayZeros = false;
// 显示公式而不是结果
sheet.DisplayFormulas = true;
9.6.2 缩放设置
// 设置缩放比例（10-400%）
sheet.SetZoom(150); // 150%
// 设置缩放为适合选定区域
// 这需要通过打印设置实现
9.6.3 选择设置
// 设置活动单元格
// 通过创建选择区域实现
if (sheet is XSSFSheet xssfSheet)
{
// XSSF特有功能
xssfSheet.SetActiveCell(new CellAddress("A1"));
}
9.7 名称管理器
9.7.1 创建命名范围
// 创建工作簿级别的命名范围
IName globalName = workbook.CreateName();
globalName.NameName = "SalesData";
globalName.RefersToFormula = "Sheet1!$A$1:$D$100";
// 创建工作表级别的命名范围
IName localName = workbook.CreateName();
localName.NameName = "LocalRange";
localName.RefersToFormula = "Sheet1!$B$2:$B$50";
localName.SheetIndex = 0; // 关联到第一个工作表
9.7.2 使用命名范围
// 获取命名范围
IName existingName = workbook.GetName("SalesData");
if (existingName != null)
{
string formula = existingName.RefersToFormula;
Console.WriteLine($"范围: {formula}");
}
// 获取所有命名范围
var allNames = workbook.GetAllNames();
foreach (IName name in allNames)
{
Console.WriteLine($"{name.NameName}: {name.RefersToFormula}");
}
// 删除命名范围
workbook.RemoveName("SalesData");
9.7.3 命名范围在公式中的使用
// 使用命名范围的公式
cell.SetCellFormula("SUM(SalesData)");
cell.SetCellFormula("AVERAGE(SalesData)");
cell.SetCellFormula("VLOOKUP(A1, SalesData, 2, FALSE)");
9.8 条件格式
9.8.1 基于值的条件格式
ISheetConditionalFormatting cf = sheet.SheetConditionalFormatting;
// 定义应用范围
CellRangeAddress[] ranges = { CellRangeAddress.ValueOf("B2:B100") };
// 大于80显示绿色背景
IConditionalFormattingRule rule1 = cf.CreateConditionalFormattingRule(
ComparisonOperator.GreaterThan, "80", null);
IPatternFormatting pf1 = rule1.CreatePatternFormatting();
pf1.FillBackgroundColor = IndexedColors.LightGreen.Index;
pf1.FillPattern = FillPattern.SolidForeground;
// 小于60显示红色背景
IConditionalFormattingRule rule2 = cf.CreateConditionalFormattingRule(
ComparisonOperator.LessThan, "60", null);
IPatternFormatting pf2 = rule2.CreatePatternFormatting();
pf2.FillBackgroundColor = IndexedColors.Rose.Index;
pf2.FillPattern = FillPattern.SolidForeground;
// 应用规则
cf.AddConditionalFormatting(ranges, new[] { rule1, rule2 });
9.8.2 基于公式的条件格式
// 基于公式的条件格式
IConditionalFormattingRule formulaRule = cf.CreateConditionalFormattingRule(
"AND($A2>0,$B2>100)");
IPatternFormatting pfFormula = formulaRule.CreatePatternFormatting();
pfFormula.FillBackgroundColor = IndexedColors.LightYellow.Index;
pfFormula.FillPattern = FillPattern.SolidForeground;
// 设置字体
IFontFormatting ff = formulaRule.CreateFontFormatting();
ff.SetFontStyle(true, false); // 粗体，非斜体
ff.FontColorIndex = IndexedColors.DarkRed.Index;
cf.AddConditionalFormatting(ranges, formulaRule);
9.9 综合示例
9.9.1 创建专业报表模板
public class ProfessionalReportExample
{
public static void CreateReport()
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("销售报表");
// 创建样式
var styles = CreateStyles(workbook);
int rowIndex = 0;
// 1. 报表标题（合并单元格）
MergeCellHelper.CreateMergedTitle(sheet, rowIndex++, 0, 6,
"2024年度销售业绩报表", workbook);
// 空行
rowIndex++;
// 2. 创建表头
IRow headerRow = sheet.CreateRow(rowIndex++);
string[] headers = { "序号", "月份", "销售额", "成本", "利润", "增长率", "备注" };
for (int i = 0; i < headers.Length; i++)
{
ICell cell = headerRow.CreateCell(i);
cell.SetCellValue(headers[i]);
cell.CellStyle = styles["header"];
}
// 3. 填充数据
string[] months = { "1月", "2月", "3月", "4月", "5月", "6月",
"7月", "8月", "9月", "10月", "11月", "12月" };
Random rand = new Random(42);
int dataStartRow = rowIndex;
for (int i = 0; i < months.Length; i++)
{
IRow row = sheet.CreateRow(rowIndex++);
row.CreateCell(0).SetCellValue(i + 1);
row.CreateCell(1).SetCellValue(months[i]);
double sales = rand.Next(80000, 150000);
double cost = sales * 0.6;
row.CreateCell(2).SetCellValue(sales);
row.CreateCell(3).SetCellValue(cost);
row.CreateCell(4).SetCellFormula($"C{rowIndex}-D{rowIndex}");
if (i > 0)
{
row.CreateCell(5).SetCellFormula($"(C{rowIndex}-C{rowIndex - 1})/C{rowIndex - 1}");
}
else
{
row.CreateCell(5).SetCellValue(0);
}
row.CreateCell(6).SetCellValue("");
// 应用样式
for (int j = 0; j < 7; j++)
{
ICell cell = row.GetCell(j);
cell.CellStyle = j == 5 ? styles["percent"] :
j >= 2 && j <= 4 ? styles["currency"] : styles["body"];
}
}
// 4. 汇总行
IRow summaryRow = sheet.CreateRow(rowIndex++);
summaryRow.CreateCell(0).SetCellValue("");
summaryRow.CreateCell(1).SetCellValue("合计");
summaryRow.CreateCell(2).SetCellFormula($"SUM(C{dataStartRow + 1}:C{rowIndex - 1})");
summaryRow.CreateCell(3).SetCellFormula($"SUM(D{dataStartRow + 1}:D{rowIndex - 1})");
summaryRow.CreateCell(4).SetCellFormula($"SUM(E{dataStartRow + 1}:E{rowIndex - 1})");
summaryRow.CreateCell(5).SetCellFormula($"AVERAGE(F{dataStartRow + 2}:F{rowIndex - 1})");
summaryRow.CreateCell(6).SetCellValue("");
for (int i = 0; i < 7; i++)
{
summaryRow.GetCell(i).CellStyle = styles["summary"];
}
// 5. 设置列宽
sheet.SetColumnWidth(0, 8 * 256);
sheet.SetColumnWidth(1, 10 * 256);
for (int i = 2; i <= 5; i++)
{
sheet.SetColumnWidth(i, 15 * 256);
}
sheet.SetColumnWidth(6, 20 * 256);
// 6. 冻结窗格
sheet.CreateFreezePane(0, 3);
// 7. 设置自动筛选
sheet.SetAutoFilter(new CellRangeAddress(2, rowIndex - 1, 0, 6));
// 8. 添加条件格式
AddConditionalFormatting(sheet, dataStartRow, rowIndex - 2);
// 9. 设置打印
SetupPrintSettings(workbook, sheet);
// 10. 计算公式
IFormulaEvaluator evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator();
evaluator.EvaluateAll();
// 保存
using (FileStream fs = new FileStream("专业报表.xlsx", FileMode.Create))
{
workbook.Write(fs);
}
Console.WriteLine("报表创建成功！");
}
private static Dictionary<string, ICellStyle> CreateStyles(IWorkbook workbook)
{
var styles = new Dictionary<string, ICellStyle>();
IDataFormat format = workbook.CreateDataFormat();
// 表头样式
ICellStyle headerStyle = workbook.CreateCellStyle();
IFont headerFont = workbook.CreateFont();
headerFont.IsBold = true;
headerFont.FontHeightInPoints = 11;
headerStyle.SetFont(headerFont);
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.VerticalAlignment = VerticalAlignment.Center;
headerStyle.FillForegroundColor = IndexedColors.Grey25Percent.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
SetAllBorders(headerStyle, BorderStyle.Thin);
styles["header"] = headerStyle;
// 正文样式
ICellStyle bodyStyle = workbook.CreateCellStyle();
bodyStyle.VerticalAlignment = VerticalAlignment.Center;
SetAllBorders(bodyStyle, BorderStyle.Thin);
styles["body"] = bodyStyle;
// 货币样式
ICellStyle currencyStyle = workbook.CreateCellStyle();
currencyStyle.CloneStyleFrom(bodyStyle);
currencyStyle.Alignment = HorizontalAlignment.Right;
currencyStyle.DataFormat = format.GetFormat("¥#,##0.00");
styles["currency"] = currencyStyle;
// 百分比样式
ICellStyle percentStyle = workbook.CreateCellStyle();
percentStyle.CloneStyleFrom(bodyStyle);
percentStyle.Alignment = HorizontalAlignment.Right;
percentStyle.DataFormat = format.GetFormat("0.00%");
styles["percent"] = percentStyle;
// 汇总行样式
ICellStyle summaryStyle = workbook.CreateCellStyle();
IFont summaryFont = workbook.CreateFont();
summaryFont.IsBold = true;
summaryStyle.SetFont(summaryFont);
summaryStyle.FillForegroundColor = IndexedColors.LightYellow.Index;
summaryStyle.FillPattern = FillPattern.SolidForeground;
summaryStyle.Alignment = HorizontalAlignment.Right;
summaryStyle.DataFormat = format.GetFormat("¥#,##0.00");
SetAllBorders(summaryStyle, BorderStyle.Thin);
styles["summary"] = summaryStyle;
return styles;
}
private static void SetAllBorders(ICellStyle style, BorderStyle borderStyle)
{
style.BorderTop = borderStyle;
style.BorderBottom = borderStyle;
style.BorderLeft = borderStyle;
style.BorderRight = borderStyle;
}
private static void AddConditionalFormatting(ISheet sheet, int startRow, int endRow)
{
ISheetConditionalFormatting cf = sheet.SheetConditionalFormatting;
// 增长率条件格式
CellRangeAddress[] ranges = { new CellRangeAddress(startRow, endRow, 5, 5) };
// 正增长显示绿色
IConditionalFormattingRule rule1 = cf.CreateConditionalFormattingRule(
ComparisonOperator.GreaterThan, "0", null);
IFontFormatting ff1 = rule1.CreateFontFormatting();
ff1.FontColorIndex = IndexedColors.Green.Index;
// 负增长显示红色
IConditionalFormattingRule rule2 = cf.CreateConditionalFormattingRule(
ComparisonOperator.LessThan, "0", null);
IFontFormatting ff2 = rule2.CreateFontFormatting();
ff2.FontColorIndex = IndexedColors.Red.Index;
cf.AddConditionalFormatting(ranges, new[] { rule1, rule2 });
}
private static void SetupPrintSettings(IWorkbook workbook, ISheet sheet)
{
IPrintSetup printSetup = sheet.PrintSetup;
printSetup.PaperSize = (short)PaperSize.A4;
printSetup.Landscape = true;
printSetup.FitWidth = 1;
printSetup.FitHeight = 0;
sheet.SetMargin(MarginType.TopMargin, 0.75);
sheet.SetMargin(MarginType.BottomMargin, 0.75);
sheet.SetMargin(MarginType.LeftMargin, 0.5);
sheet.SetMargin(MarginType.RightMargin, 0.5);
sheet.RepeatingRows = new CellRangeAddress(2, 2, 0, 6);
IHeader header = sheet.Header;
header.Center = "销售业绩报表";
header.Right = "&D";
IFooter footer = sheet.Footer;
footer.Center = "第 &P 页，共 &N 页";
sheet.HorizontallyCenter = true;
}
}
9.10 本章小结
本章详细介绍了NPOI中的Excel高级功能。通过本章学习，你应该掌握：
- 合并单元格的创建、样式设置和取消
- 冻结窗格和拆分窗格的使用
- 自动筛选的设置
- 行列分组和大纲功能
- 打印设置（纸张、页边距、打印区域、页眉页脚）
- 显示设置（网格线、缩放等）
- 命名范围的创建和使用
- 条件格式的应用
这些高级功能可以让Excel文档更加专业和易用，是创建企业级报表的重要工具。
下一章预告：第十章将介绍Word文档基础操作，包括文档的创建、打开、保存以及基本结构操作。
浙公网安备 33010602011771号