第20章-实战案例与综合应用
第二十章：实战案例与综合应用
20.1 案例一：数据分析报表系统
20.1.1 需求分析
创建一个数据分析报表系统，支持：
- 数据导入（Excel、CSV、数据库）
- 数据统计分析
- 图表可视化
- 报表导出
20.1.2 实现代码
public class DataAnalysisSystem
{
private ReoGridControl grid;
private Worksheet dataSheet;
private Worksheet summarySheet;
public DataAnalysisSystem(ReoGridControl gridControl)
{
grid = gridControl;
Initialize();
}
private void Initialize()
{
// 创建数据表和汇总表
dataSheet = grid.CreateWorksheet("原始数据");
summarySheet = grid.CreateWorksheet("数据分析");
grid.CurrentWorksheet = dataSheet;
}
public void ImportData(string filePath)
{
grid.Load(filePath);
AnalyzeData();
}
private void AnalyzeData()
{
summarySheet.BeginUpdate();
try
{
// 统计分析
summarySheet["A1"] = "统计项";
summarySheet["B1"] = "结果";
summarySheet["A2"] = "总记录数";
summarySheet["B2"] = $"=COUNTA(原始数据.A:A)";
summarySheet["A3"] = "数值总和";
summarySheet["B3"] = $"=SUM(原始数据.B:B)";
summarySheet["A4"] = "平均值";
summarySheet["B4"] = $"=AVERAGE(原始数据.B:B)";
summarySheet["A5"] = "最大值";
summarySheet["B5"] = $"=MAX(原始数据.B:B)";
summarySheet["A6"] = "最小值";
summarySheet["B6"] = $"=MIN(原始数据.B:B)";
// 设置样式
summarySheet.SetRangeStyles("A1:B1", new WorksheetRangeStyle
{
Flag = PlainStyleFlag.FontBold | PlainStyleFlag.BackColor,
Bold = true,
BackColor = Color.LightBlue
});
// 创建图表
CreateChart();
}
finally
{
summarySheet.EndUpdate();
}
}
private void CreateChart()
{
var chart = summarySheet.CreateChart(
ChartType.Column,
new RangePosition("A2:B6"),
new RangePosition("D2:K15")
);
chart.Title = "数据分析图表";
}
public void ExportReport(string filePath)
{
grid.Save(filePath, IO.FileFormat.Excel2007);
}
}
20.2 案例二：进销存管理系统
public class InventoryManagementSystem
{
private Worksheet inventorySheet;
private Worksheet transactionSheet;
public void Initialize(ReoGridControl grid)
{
inventorySheet = grid.CreateWorksheet("库存");
transactionSheet = grid.CreateWorksheet("出入库记录");
SetupInventorySheet();
SetupTransactionSheet();
}
private void SetupInventorySheet()
{
// 设置列头
var headers = new[] { "商品编号", "商品名称", "库存数量", "单价", "库存金额" };
for (int i = 0; i < headers.Length; i++)
{
inventorySheet[0, i] = headers[i];
}
// 设置公式（库存金额 = 数量 × 单价）
for (int row = 1; row <= 100; row++)
{
inventorySheet[row, 4] = $"=C{row+1}*D{row+1}";
}
// 设置样式
inventorySheet.SetRangeStyles("A1:E1", new WorksheetRangeStyle
{
Flag = PlainStyleFlag.FontBold | PlainStyleFlag.BackColor | PlainStyleFlag.HorizontalAlign,
Bold = true,
BackColor = Color.FromArgb(68, 114, 196),
HAlign = ReoGridHorAlign.Center
});
// 设置货币格式
inventorySheet.SetRangeDataFormat("D2:E101", CellDataFormatFlag.Currency,
new CurrencyDataFormatter.CurrencyFormatArgs
{
Symbol = "¥",
DecimalPlaces = 2
});
}
private void SetupTransactionSheet()
{
var headers = new[] { "日期", "商品编号", "类型", "数量", "备注" };
for (int i = 0; i < headers.Length; i++)
{
transactionSheet[0, i] = headers[i];
}
// 数据验证
transactionSheet.SetRangeDataValidation("C2:C1000", DataValidationType.List,
new ListValidationRule
{
Items = new[] { "入库", "出库" }
});
}
public void AddTransaction(DateTime date, string productId, string type, int quantity, string notes)
{
int nextRow = FindNextEmptyRow(transactionSheet);
transactionSheet[nextRow, 0] = date;
transactionSheet[nextRow, 1] = productId;
transactionSheet[nextRow, 2] = type;
transactionSheet[nextRow, 3] = quantity;
transactionSheet[nextRow, 4] = notes;
UpdateInventory(productId, type, quantity);
}
private void UpdateInventory(string productId, string type, int quantity)
{
// 查找商品并更新库存
for (int row = 1; row < inventorySheet.RowCount; row++)
{
var id = inventorySheet[row, 0]?.ToString();
if (id == productId)
{
var currentQty = Convert.ToInt32(inventorySheet[row, 2] ?? 0);
if (type == "入库")
{
inventorySheet[row, 2] = currentQty + quantity;
}
else if (type == "出库")
{
inventorySheet[row, 2] = currentQty - quantity;
}
break;
}
}
}
private int FindNextEmptyRow(Worksheet sheet)
{
for (int row = 1; row < 10000; row++)
{
if (sheet[row, 0] == null)
{
return row;
}
}
return 1;
}
}
20.3 案例三：考勤管理系统
public class AttendanceSystem
{
private Worksheet attendanceSheet;
public void Initialize(ReoGridControl grid)
{
attendanceSheet = grid.CreateWorksheet("考勤记录");
SetupAttendanceSheet();
}
private void SetupAttendanceSheet()
{
// 月度考勤表头
attendanceSheet["A1"] = "员工姓名";
attendanceSheet["B1"] = "部门";
// 日期列（1-31日）
for (int day = 1; day <= 31; day++)
{
attendanceSheet[0, day + 1] = day.ToString();
}
attendanceSheet[0, 33] = "出勤天数";
attendanceSheet[0, 34] = "缺勤天数";
attendanceSheet[0, 35] = "出勤率";
// 出勤天数公式
for (int row = 1; row <= 100; row++)
{
attendanceSheet[row, 33] = $"=COUNTIF(C{row+1}:AG{row+1},"√")";
attendanceSheet[row, 34] = $"=COUNTIF(C{row+1}:AG{row+1},"×")";
attendanceSheet[row, 35] = $"=AH{row+1}/(AH{row+1}+AI{row+1})";
}
// 设置百分比格式
attendanceSheet.SetRangeDataFormat("AJ2:AJ101", CellDataFormatFlag.Percent,
new NumberDataFormatter.NumberFormatArgs { DecimalPlaces = 1 });
// 条件格式（低出勤率标红）
SetupConditionalFormatting();
}
private void SetupConditionalFormatting()
{
// 当出勤率低于80%时，设置为红色背景
for (int row = 1; row <= 100; row++)
{
var cell = attendanceSheet.GetCell(row, 35);
if (cell != null)
{
// 可以通过事件处理动态设置样式
attendanceSheet.CellDataChanged += (s, e) =>
{
if (e.Cell.Position.Col == 35)
{
var value = Convert.ToDouble(e.Cell.Data ?? 0);
if (value < 0.8)
{
attendanceSheet.SetCellStyle(e.Cell.Position, new WorksheetRangeStyle
{
Flag = PlainStyleFlag.BackColor | PlainStyleFlag.FontColor,
BackColor = Color.FromArgb(255, 199, 206),
FontColor = Color.FromArgb(156, 0, 6)
});
}
}
};
}
}
}
public void MarkAttendance(int employeeRow, int day, bool present)
{
attendanceSheet[employeeRow, day + 1] = present ? "√" : "×";
}
}
20.4 案例四：财务预算表
public class BudgetSheet
{
public void CreateBudgetSheet(ReoGridControl grid)
{
var sheet = grid.CurrentWorksheet;
// 创建预算表结构
var categories = new[] { "工资", "租金", "水电", "办公用品", "差旅费", "其他" };
var months = new[] { "1月", "2月", "3月", "4月", "5月", "6月",
"7月", "8月", "9月", "10月", "11月", "12月", "合计" };
// 设置列头
sheet["A1"] = "费用类别";
for (int i = 0; i < months.Length; i++)
{
sheet[0, i + 1] = months[i];
}
// 设置行头
for (int i = 0; i < categories.Length; i++)
{
sheet[i + 1, 0] = categories[i];
}
// 设置合计公式
for (int row = 1; row <= categories.Length; row++)
{
sheet[row, 13] = $"=SUM(B{row+1}:M{row+1})";
}
// 设置月度合计
sheet[categories.Length + 1, 0] = "月度合计";
for (int col = 1; col <= 13; col++)
{
char colLetter = (char)('A' + col);
sheet[categories.Length + 1, col] = $"=SUM({colLetter}2:{colLetter}{categories.Length + 1})";
}
// 设置样式
sheet.SetRangeStyles("A1:N1", new WorksheetRangeStyle
{
Flag = PlainStyleFlag.FontBold | PlainStyleFlag.BackColor,
Bold = true,
BackColor = Color.FromArgb(79, 129, 189)
});
sheet.SetRangeStyles("N2:N8", new WorksheetRangeStyle
{
Flag = PlainStyleFlag.FontBold,
Bold = true
});
// 设置货币格式
sheet.SetRangeDataFormat("B2:N8", CellDataFormatFlag.Currency,
new CurrencyDataFormatter.CurrencyFormatArgs
{
Symbol = "¥",
DecimalPlaces = 2
});
}
}
20.5 本章小结
本章通过实际案例展示了ReoGrid在各种业务场景中的应用，包括：
- 数据分析报表系统
- 进销存管理
- 考勤管理
- 财务预算表
这些案例展示了ReoGrid的强大功能和实用价值。
20.6 系列教程总结
通过本系列20章的学习，您应该已经掌握：
- ReoGrid的基础概念和架构
- 环境配置和项目创建
- 数据操作和格式化
- 样式和外观定制
- 公式计算和图表
- 文件导入导出
- 事件处理和交互
- 性能优化和最佳实践
- 高级功能和扩展开发
- 实战案例应用
🎓 继续学习
- 官方文档：https://reogrid.net/document
- GitHub源码：https://github.com/unvell/ReoGrid
- 示例代码：https://github.com/unvell/ReoGrid/tree/master/Demo
💡 社区资源
- QQ群：289280914
- GitHub：@znlgis
- Gitee：@znlgis
- Bilibili：space/161342702
🎉 恭喜完成ReoGrid完整学习教程！
浙公网安备 33010602011771号