第18章-实战案例与综合应用
第十八章：实战案例与综合应用
18.1 概述
本章通过实际项目案例，展示 NPOI 在企业级应用中的综合运用。涵盖数据导入导出、报表生成、文档处理等常见场景。
18.2 案例一：企业报表自动化系统
18.2.1 需求分析
构建一个通用的报表生成系统：
- 支持多数据源（数据库、API、文件）
- 动态模板配置
- 自动化定时生成
- 支持多种导出格式（Excel、PDF）
18.2.2 核心实现
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
/// <summary>
/// 报表生成器
/// </summary>
public class ReportGenerator
{
/// <summary>
/// 生成销售报表
/// </summary>
public byte[] GenerateSalesReport(List<SalesData> salesData, DateTime startDate, DateTime endDate)
{
IWorkbook workbook = new XSSFWorkbook();
// 创建汇总表
CreateSummarySheet(workbook, salesData, startDate, endDate);
// 创建明细表
CreateDetailSheet(workbook, salesData);
// 创建图表
CreateChartSheet(workbook, salesData);
// 转换为字节数组
using (var ms = new MemoryStream())
{
workbook.Write(ms);
return ms.ToArray();
}
}
private void CreateSummarySheet(IWorkbook workbook, List<SalesData> data,
DateTime startDate, DateTime endDate)
{
ISheet sheet = workbook.CreateSheet("销售汇总");
// 创建标题
IRow titleRow = sheet.CreateRow(0);
ICell titleCell = titleRow.CreateCell(0);
titleCell.SetCellValue($"销售报表 ({startDate:yyyy-MM-dd} 至 {endDate:yyyy-MM-dd})");
// 设置标题样式
ICellStyle titleStyle = workbook.CreateCellStyle();
IFont titleFont = workbook.CreateFont();
titleFont.FontHeightInPoints = 16;
titleFont.IsBold = true;
titleStyle.SetFont(titleFont);
titleStyle.Alignment = HorizontalAlignment.Center;
titleCell.CellStyle = titleStyle;
// 合并标题单元格
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 4));
// 统计数据
var summary = new
{
TotalSales = data.Sum(s => s.Amount),
TotalOrders = data.Count,
AverageOrderValue = data.Average(s => s.Amount),
TopProduct = data.GroupBy(s => s.ProductName)
.OrderByDescending(g => g.Sum(s => s.Amount))
.FirstOrDefault()?.Key
};
// 创建汇总信息
int rowIndex = 2;
CreateSummaryRow(sheet, rowIndex++, "总销售额", summary.TotalSales, "¥#,##0.00");
CreateSummaryRow(sheet, rowIndex++, "订单总数", summary.TotalOrders, "#,##0");
CreateSummaryRow(sheet, rowIndex++, "平均订单金额", summary.AverageOrderValue, "¥#,##0.00");
CreateSummaryRow(sheet, rowIndex++, "最畅销产品", summary.TopProduct ?? "无");
// 按产品分类统计
rowIndex += 2;
IRow categoryHeaderRow = sheet.CreateRow(rowIndex++);
categoryHeaderRow.CreateCell(0).SetCellValue("产品类别");
categoryHeaderRow.CreateCell(1).SetCellValue("销售数量");
categoryHeaderRow.CreateCell(2).SetCellValue("销售金额");
categoryHeaderRow.CreateCell(3).SetCellValue("占比");
var categoryStats = data.GroupBy(s => s.Category)
.Select(g => new
{
Category = g.Key,
Quantity = g.Sum(s => s.Quantity),
Amount = g.Sum(s => s.Amount),
Percentage = g.Sum(s => s.Amount) / summary.TotalSales * 100
})
.OrderByDescending(c => c.Amount);
ICellStyle percentStyle = workbook.CreateCellStyle();
percentStyle.DataFormat = workbook.CreateDataFormat().GetFormat("0.00%");
ICellStyle currencyStyle = workbook.CreateCellStyle();
currencyStyle.DataFormat = workbook.CreateDataFormat().GetFormat("¥#,##0.00");
foreach (var stat in categoryStats)
{
IRow row = sheet.CreateRow(rowIndex++);
row.CreateCell(0).SetCellValue(stat.Category);
row.CreateCell(1).SetCellValue(stat.Quantity);
ICell amountCell = row.CreateCell(2);
amountCell.SetCellValue((double)stat.Amount);
amountCell.CellStyle = currencyStyle;
ICell percentCell = row.CreateCell(3);
percentCell.SetCellValue(stat.Percentage / 100);
percentCell.CellStyle = percentStyle;
}
// 自动调整列宽
for (int i = 0; i < 5; i++)
{
sheet.AutoSizeColumn(i);
}
}
private void CreateSummaryRow(ISheet sheet, int rowIndex, string label, object value,
string format = null)
{
IRow row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue(label);
ICell valueCell = row.CreateCell(1);
if (value is decimal || value is double || value is float)
{
valueCell.SetCellValue(Convert.ToDouble(value));
if (!string.IsNullOrEmpty(format))
{
ICellStyle style = sheet.Workbook.CreateCellStyle();
style.DataFormat = sheet.Workbook.CreateDataFormat().GetFormat(format);
valueCell.CellStyle = style;
}
}
else
{
valueCell.SetCellValue(value?.ToString() ?? "");
}
}
private void CreateDetailSheet(IWorkbook workbook, List<SalesData> data)
{
ISheet sheet = workbook.CreateSheet("销售明细");
// 创建表头
IRow headerRow = sheet.CreateRow(0);
string[] headers = { "订单号", "日期", "产品名称", "类别", "数量", "单价", "金额", "客户" };
ICellStyle headerStyle = workbook.CreateCellStyle();
headerStyle.FillForegroundColor = IndexedColors.Grey25.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
IFont headerFont = workbook.CreateFont();
headerFont.IsBold = true;
headerStyle.SetFont(headerFont);
for (int i = 0; i < headers.Length; i++)
{
ICell cell = headerRow.CreateCell(i);
cell.SetCellValue(headers[i]);
cell.CellStyle = headerStyle;
}
// 创建数据行
ICellStyle dateStyle = workbook.CreateCellStyle();
dateStyle.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy-MM-dd");
ICellStyle currencyStyle = workbook.CreateCellStyle();
currencyStyle.DataFormat = workbook.CreateDataFormat().GetFormat("¥#,##0.00");
int rowIndex = 1;
foreach (var item in data)
{
IRow row = sheet.CreateRow(rowIndex++);
row.CreateCell(0).SetCellValue(item.OrderId);
ICell dateCell = row.CreateCell(1);
dateCell.SetCellValue(item.OrderDate);
dateCell.CellStyle = dateStyle;
row.CreateCell(2).SetCellValue(item.ProductName);
row.CreateCell(3).SetCellValue(item.Category);
row.CreateCell(4).SetCellValue(item.Quantity);
ICell priceCell = row.CreateCell(5);
priceCell.SetCellValue((double)item.UnitPrice);
priceCell.CellStyle = currencyStyle;
ICell amountCell = row.CreateCell(6);
amountCell.SetCellValue((double)item.Amount);
amountCell.CellStyle = currencyStyle;
row.CreateCell(7).SetCellValue(item.CustomerName);
}
// 设置筛选
sheet.SetAutoFilter(new CellRangeAddress(0, data.Count, 0, headers.Length - 1));
// 冻结首行
sheet.CreateFreezePane(0, 1);
// 自动调整列宽
for (int i = 0; i < headers.Length; i++)
{
sheet.AutoSizeColumn(i);
}
}
private void CreateChartSheet(IWorkbook workbook, List<SalesData> data)
{
ISheet sheet = workbook.CreateSheet("趋势图表");
// 准备图表数据
var dailySales = data.GroupBy(s => s.OrderDate.Date)
.OrderBy(g => g.Key)
.Select(g => new
{
Date = g.Key,
Amount = g.Sum(s => s.Amount)
})
.ToList();
// 创建数据表
IRow headerRow = sheet.CreateRow(0);
headerRow.CreateCell(0).SetCellValue("日期");
headerRow.CreateCell(1).SetCellValue("销售额");
int rowIndex = 1;
foreach (var item in dailySales)
{
IRow row = sheet.CreateRow(rowIndex++);
row.CreateCell(0).SetCellValue(item.Date.ToString("yyyy-MM-dd"));
row.CreateCell(1).SetCellValue((double)item.Amount);
}
}
}
/// <summary>
/// 销售数据模型
/// </summary>
public class SalesData
{
public string OrderId { get; set; }
public DateTime OrderDate { get; set; }
public string ProductName { get; set; }
public string Category { get; set; }
public int Quantity { get; set; }
public decimal UnitPrice { get; set; }
public decimal Amount { get; set; }
public string CustomerName { get; set; }
}
18.2.3 定时任务集成
using Quartz;
using System.Threading.Tasks;
/// <summary>
/// 报表生成定时任务
/// </summary>
public class ReportGenerationJob : IJob
{
private readonly ReportGenerator _reportGenerator;
private readonly IEmailService _emailService;
public ReportGenerationJob(ReportGenerator reportGenerator, IEmailService emailService)
{
_reportGenerator = reportGenerator;
_emailService = emailService;
}
public async Task Execute(IJobExecutionContext context)
{
try
{
// 获取数据
var salesData = await GetSalesDataAsync();
// 生成报表
var reportBytes = _reportGenerator.GenerateSalesReport(
salesData,
DateTime.Now.AddDays(-30),
DateTime.Now
);
// 发送邮件
await _emailService.SendEmailWithAttachmentAsync(
to: "manager@company.com",
subject: $"月度销售报表 - {DateTime.Now:yyyy-MM}",
body: "请查收附件中的销售报表。",
attachmentName: $"销售报表_{DateTime.Now:yyyyMMdd}.xlsx",
attachmentData: reportBytes
);
Console.WriteLine($"报表生成并发送成功：{DateTime.Now}");
}
catch (Exception ex)
{
Console.WriteLine($"报表生成失败：{ex.Message}");
throw;
}
}
private async Task<List<SalesData>> GetSalesDataAsync()
{
// 从数据库获取数据
return new List<SalesData>();
}
}
18.3 案例二：批量数据导入验证系统
18.3.1 数据验证框架
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
/// <summary>
/// 数据导入验证器
/// </summary>
public class DataImportValidator<T> where T : class, new()
{
public class ValidationResult
{
public bool IsValid { get; set; }
public List<string> Errors { get; set; } = new List<string>();
public List<T> ValidData { get; set; } = new List<T>();
public List<ImportError> InvalidRows { get; set; } = new List<ImportError>();
}
public class ImportError
{
public int RowIndex { get; set; }
public T Data { get; set; }
public List<string> Errors { get; set; }
}
/// <summary>
/// 验证导入的数据
/// </summary>
public ValidationResult ValidateImportData(List<T> data)
{
var result = new ValidationResult();
for (int i = 0; i < data.Count; i++)
{
var item = data[i];
var errors = new List<string>();
// 使用 DataAnnotations 验证
var validationContext = new ValidationContext(item);
var validationResults = new List<ValidationResult>();
bool isValid = Validator.TryValidateObject(item, validationContext,
validationResults, true);
if (!isValid)
{
errors.AddRange(validationResults.Select(vr => vr.ErrorMessage));
}
// 自定义业务验证
var customErrors = ValidateBusinessRules(item);
errors.AddRange(customErrors);
if (errors.Any())
{
result.InvalidRows.Add(new ImportError
{
RowIndex = i + 2, // +2 因为Excel从1开始且有表头
Data = item,
Errors = errors
});
}
else
{
result.ValidData.Add(item);
}
}
result.IsValid = !result.InvalidRows.Any();
if (!result.IsValid)
{
result.Errors.Add($"发现 {result.InvalidRows.Count} 行数据存在错误");
}
return result;
}
/// <summary>
/// 业务规则验证
/// </summary>
protected virtual List<string> ValidateBusinessRules(T item)
{
return new List<string>();
}
/// <summary>
/// 生成错误报告
/// </summary>
public byte[] GenerateErrorReport(ValidationResult validationResult)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("错误报告");
// 创建表头
IRow headerRow = sheet.CreateRow(0);
headerRow.CreateCell(0).SetCellValue("行号");
headerRow.CreateCell(1).SetCellValue("错误信息");
ICellStyle headerStyle = workbook.CreateCellStyle();
headerStyle.FillForegroundColor = IndexedColors.Red.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
IFont headerFont = workbook.CreateFont();
headerFont.Color = IndexedColors.White.Index;
headerFont.IsBold = true;
headerStyle.SetFont(headerFont);
headerRow.GetCell(0).CellStyle = headerStyle;
headerRow.GetCell(1).CellStyle = headerStyle;
// 填充错误信息
int rowIndex = 1;
foreach (var error in validationResult.InvalidRows)
{
IRow row = sheet.CreateRow(rowIndex++);
row.CreateCell(0).SetCellValue(error.RowIndex);
row.CreateCell(1).SetCellValue(string.Join("; ", error.Errors));
}
// 自动调整列宽
sheet.AutoSizeColumn(0);
sheet.AutoSizeColumn(1);
using (var ms = new MemoryStream())
{
workbook.Write(ms);
return ms.ToArray();
}
}
}
/// <summary>
/// 员工数据导入验证器
/// </summary>
public class EmployeeImportValidator : DataImportValidator<EmployeeImportModel>
{
private readonly HashSet<string> _existingEmployeeIds;
public EmployeeImportValidator(HashSet<string> existingEmployeeIds)
{
_existingEmployeeIds = existingEmployeeIds;
}
protected override List<string> ValidateBusinessRules(EmployeeImportModel item)
{
var errors = new List<string>();
// 检查员工ID是否重复
if (_existingEmployeeIds.Contains(item.EmployeeId))
{
errors.Add($"员工ID '{item.EmployeeId}' 已存在");
}
// 检查入职日期
if (item.HireDate > DateTime.Now)
{
errors.Add("入职日期不能晚于当前日期");
}
// 检查工资范围
if (item.Salary < 0 || item.Salary > 1000000)
{
errors.Add("工资必须在 0 到 1,000,000 之间");
}
return errors;
}
}
/// <summary>
/// 员工导入模型
/// </summary>
public class EmployeeImportModel
{
[Required(ErrorMessage = "员工ID不能为空")]
[StringLength(20, ErrorMessage = "员工ID长度不能超过20个字符")]
public string EmployeeId { get; set; }
[Required(ErrorMessage = "姓名不能为空")]
[StringLength(50, ErrorMessage = "姓名长度不能超过50个字符")]
public string Name { get; set; }
[Required(ErrorMessage = "部门不能为空")]
public string Department { get; set; }
[Required(ErrorMessage = "职位不能为空")]
public string Position { get; set; }
[EmailAddress(ErrorMessage = "邮箱格式不正确")]
public string Email { get; set; }
[Phone(ErrorMessage = "电话号码格式不正确")]
public string PhoneNumber { get; set; }
[Required(ErrorMessage = "入职日期不能为空")]
public DateTime HireDate { get; set; }
[Range(0, double.MaxValue, ErrorMessage = "工资必须大于0")]
public decimal Salary { get; set; }
}
18.4 案例三：Word 文档批量生成
using NPOI.XWPF.UserModel;
using System.IO;
/// <summary>
/// 合同文档生成器
/// </summary>
public class ContractGenerator
{
/// <summary>
/// 根据模板生成合同
/// </summary>
public byte[] GenerateContract(ContractData data, string templatePath)
{
using (FileStream templateStream = new FileStream(templatePath, FileMode.Open,
FileAccess.Read))
{
XWPFDocument doc = new XWPFDocument(templateStream);
// 替换文档中的占位符
ReplacePlaceholders(doc, data);
// 插入签名图片
if (!string.IsNullOrEmpty(data.SignatureImagePath))
{
InsertSignature(doc, data.SignatureImagePath);
}
// 转换为字节数组
using (MemoryStream ms = new MemoryStream())
{
doc.Write(ms);
return ms.ToArray();
}
}
}
private void ReplacePlaceholders(XWPFDocument doc, ContractData data)
{
var replacements = new Dictionary<string, string>
{
{ "{{合同编号}}", data.ContractNumber },
{ "{{甲方名称}}", data.PartyAName },
{ "{{乙方名称}}", data.PartyBName },
{ "{{签订日期}}", data.SignDate.ToString("yyyy年MM月dd日") },
{ "{{生效日期}}", data.EffectiveDate.ToString("yyyy年MM月dd日") },
{ "{{合同金额}}", data.Amount.ToString("C") },
{ "{{付款方式}}", data.PaymentMethod }
};
// 替换段落中的文本
foreach (var para in doc.Paragraphs)
{
foreach (var run in para.Runs)
{
string text = run.Text;
if (!string.IsNullOrEmpty(text))
{
foreach (var replacement in replacements)
{
text = text.Replace(replacement.Key, replacement.Value);
}
run.SetText(text, 0);
}
}
}
// 替换表格中的文本
foreach (var table in doc.Tables)
{
foreach (var row in table.Rows)
{
foreach (var cell in row.GetTableCells())
{
foreach (var para in cell.Paragraphs)
{
foreach (var run in para.Runs)
{
string text = run.Text;
if (!string.IsNullOrEmpty(text))
{
foreach (var replacement in replacements)
{
text = text.Replace(replacement.Key, replacement.Value);
}
run.SetText(text, 0);
}
}
}
}
}
}
}
private void InsertSignature(XWPFDocument doc, string imagePath)
{
// 在文档末尾添加签名
var para = doc.CreateParagraph();
para.Alignment = ParagraphAlignment.RIGHT;
var run = para.CreateRun();
using (FileStream picStream = new FileStream(imagePath, FileMode.Open,
FileAccess.Read))
{
run.AddPicture(picStream, (int)PictureType.PNG, "signature",
Units.ToEMU(100), Units.ToEMU(50));
}
}
/// <summary>
/// 批量生成合同
/// </summary>
public void BatchGenerateContracts(List<ContractData> contracts, string templatePath,
string outputDir)
{
Directory.CreateDirectory(outputDir);
foreach (var contract in contracts)
{
try
{
byte[] contractBytes = GenerateContract(contract, templatePath);
string fileName = $"合同_{contract.ContractNumber}_{contract.PartyBName}.docx";
string outputPath = Path.Combine(outputDir, fileName);
File.WriteAllBytes(outputPath, contractBytes);
Console.WriteLine($"✓ 生成合同: {fileName}");
}
catch (Exception ex)
{
Console.WriteLine($"✗ 生成合同失败 ({contract.ContractNumber}): {ex.Message}");
}
}
}
}
/// <summary>
/// 合同数据模型
/// </summary>
public class ContractData
{
public string ContractNumber { get; set; }
public string PartyAName { get; set; }
public string PartyBName { get; set; }
public DateTime SignDate { get; set; }
public DateTime EffectiveDate { get; set; }
public decimal Amount { get; set; }
public string PaymentMethod { get; set; }
public string SignatureImagePath { get; set; }
}
18.5 性能优化案例
/// <summary>
/// 大数据量Excel导出优化
/// </summary>
public class OptimizedExcelExporter
{
/// <summary>
/// 使用 SXSSFWorkbook 优化内存使用
/// </summary>
public void ExportLargeDataset(IEnumerable<DataRow> dataRows, string outputPath)
{
// 使用 SXSSFWorkbook 进行流式写入，仅保留100行在内存中
using (var workbook = new SXSSFWorkbook(100))
{
ISheet sheet = workbook.CreateSheet("数据");
int rowIndex = 0;
// 创建表头
IRow headerRow = sheet.CreateRow(rowIndex++);
// ... 设置表头
// 分批处理数据
foreach (var dataRow in dataRows)
{
IRow row = sheet.CreateRow(rowIndex++);
// ... 填充数据
// 每10000行强制刷新
if (rowIndex % 10000 == 0)
{
workbook.FlushSheets();
Console.WriteLine($"已处理 {rowIndex} 行");
}
}
// 写入文件
using (FileStream fs = new FileStream(outputPath, FileMode.Create,
FileAccess.Write))
{
workbook.Write(fs);
}
// 清理临时文件
workbook.Dispose();
}
}
}
18.6 本章小结
本章通过实战案例展示了 NPOI 的综合应用：
- 企业报表系统：自动化生成、格式化、发送报表
- 数据导入验证：完整的验证框架和错误处理
- 文档批量生成：基于模板的合同文档生成
- 性能优化：大数据量处理的最佳实践
这些案例涵盖了企业级应用的常见场景，可以直接应用到实际项目中。
相关资源：
浙公网安备 33010602011771号