/// <summary>
/// 生成“汇总”sheet
/// </summary>
/// <param name="document"></param>
private void GenerateTotalSheet(XLWorkbook document)
{
try
{
var ws = document.Worksheets.Add("汇总");
ws.Cell(1, 1).Value = "HNCDI(" + _year + "年度)获奖成果与发表论著奖励明细(单位：万元)";
var title = ws.Range("A1:F1");
title.Row(1).Merge(); //合并标题行
title.Style.Font.FontSize = 14;
title.Style.Font.FontName = "宋体";
title.Style.Font.Bold = true;
title.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Cell(2, 1).Value = "序号";
ws.Cell(2, 2).Value = "奖励对象";
ws.Cell(2, 3).Value = "奖别";
ws.Cell(2, 4).Value = "单位";
ws.Cell(2, 5).Value = "奖金";
ws.Cell(2, 6).Value = "备注";
var colA = ws.Column("A");
colA.Width = 3;
ws.Column("B").Width = 29;
ws.Column("C").Width = 20.88;
ws.Column("D").Width = 12.35;
ws.Column("E").Width = 9.5;
ws.Column("F").Width = 4.38;
ws.Row(1).AdjustToContents(1);
var secondRow = ws.Range("A2:F2");
secondRow.Style.Font.FontSize = 10;
secondRow.Style.Font.FontName = "宋体";
secondRow.Style.Font.Bold = true;
secondRow.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Row(2).AdjustToContents();
int currentRow = 3;
int currentClassify = 1;
DigitToChnText digitToChnText = new DigitToChnText();
foreach (string prizeClassify in _prizeClasifies)
{
//计算该类总奖金
decimal classifyMoney =
_prizeses.Where(p => p.PrizeClassify.Equals(prizeClassify)).Sum(p => p.Prize) ?? 0;
ws.Cell(currentRow, 1).Value = digitToChnText.Convert(currentClassify.ToString(), false);
ws.Cell(currentRow, 2).Value = prizeClassify;
ws.Cell(currentRow, 5).Value = (classifyMoney / 10000).ToString("N4");
var classifyRow = ws.Range("A" + currentRow.ToString() + ":F" + currentRow.ToString());
classifyRow.Style.Font.Bold = true;
classifyRow.Style.Font.FontSize = 10;
classifyRow.Style.Font.FontName = "宋体";
classifyRow.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
classifyRow.Style.Fill.BackgroundColor = XLColor.Gainsboro;
ws.Row(currentRow).AdjustToContents();
currentRow++;
//统计该类各项目
int projectNo = 1;
List <Prizes> classifyPrizes = _prizeses.Where(p => p.PrizeClassify.Equals(prizeClassify)).ToList();
var prizeses =
classifyPrizes.Select(
p =>
new
{
PrizeClassify = p.PrizeClassify,
Project = p.Project,
AwardName = p.AwardName,
DeclareDepartment = p.DeclareDepartment
})
.Distinct()
.ToList();
foreach (var prizes in prizeses)
{
ws.Cell(currentRow, 1).Value = projectNo;
ws.Cell(currentRow, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
ws.Cell(currentRow, 2).Value = prizes.Project;
ws.Cell(currentRow, 2).Style.Alignment.WrapText = true;
ws.Cell(currentRow, 3).Value = prizes.AwardName;
ws.Cell(currentRow, 4).Value = prizes.DeclareDepartment;
//计算该项目奖金
decimal projectMoney =
_prizeses.Where(
p =>
p.Project.Equals(prizes.Project) && p.AwardName.Equals(prizes.AwardName) &&
p.DeclareDepartment.Equals(prizes.DeclareDepartment)).Sum(p => p.Prize) ?? 0;
ws.Cell(currentRow, 5).Value = (projectMoney / 10000).ToString("N4");
var projectRow = ws.Range("A" + currentRow.ToString() + ":F" + currentRow.ToString());
projectRow.Style.Font.FontSize = 9;
projectRow.Style.Font.FontName = "宋体";
ws.Range("C" + currentRow.ToString() + ":F" + currentRow.ToString()).Style.Alignment.Horizontal
=
XLAlignmentHorizontalValues.Center;
ws.Row(currentRow).AdjustToContents();
currentRow++;
projectNo++;
}
currentClassify++;
}
//添加总计行
ws.Cell(currentRow, 2).Value = "总 计";
ws.Cell(currentRow, 5).Value = (_totalMoney / 10000).ToString("N4");
var totalRow = ws.Range("A" + currentRow.ToString() + ":F" + currentRow.ToString());
totalRow.Style.Font.FontSize = 10;
totalRow.Style.Font.FontName = "宋体";
totalRow.Style.Font.Bold = true;
totalRow.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
totalRow.Style.Fill.BackgroundColor = XLColor.DarkOrange;
var mainBody = ws.Range("A2:F" + currentRow.ToString());
mainBody.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
mainBody.Style.Border.OutsideBorder = XLBorderStyleValues.Thick;
mainBody.Style.Border.OutsideBorderColor = XLColor.Black;
mainBody.Style.Border.InsideBorder = XLBorderStyleValues.Thin;
mainBody.Style.Border.InsideBorderColor = XLColor.Black;
currentRow++;
ws.Cell(currentRow, 1).Value = "编制";
ws.Cell(currentRow, 3).Value = "审核：";
ws.Cell(currentRow, 4).Value = "批准：";
ws.Row(currentRow).AdjustToContents();
ws.Columns().AdjustToContents();
ws.Rows().AdjustToContents();
ws.Rows().Height = 15;
ws.Row(1).Height = 20;
//MessageBox.Show("生成汇总表成功！");
}
catch (Exception error)
{
MessageBox.Show(error.Message);
}
}
/// <summary>
/// 生成“个人汇总”sheet
/// </summary>
/// <param name="document"></param>
private void GenaratePersonalTotalSheet(XLWorkbook document)
{
try
{
var ws = document.Worksheets.Add("个人汇总");
//标题行
ws.Cell(1, 1).Value = _year + "年度科技成果年终奖励个人汇总表（单位：元）";
var title = ws.Range(1, 1, 1, _totalPrizeClassify + 4);
title.Merge();
title.Style.Font.Bold = true;
title.Style.Font.FontName = "宋体";
title.Style.Font.FontSize = 14;
title.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
title.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
//第二行
ws.Cell(2, 1).Value = "序号";
ws.Cell(2, 2).Value = "部门";
ws.Cell(2, 3).Value = "姓名";
Dictionary <string, decimal> dictionaryClassifyMoney =
_prizeClasifies.ToDictionary <string, string, decimal>(classify => classify, classify => 0);
Dictionary <int, string> dictionaryClassify = new Dictionary <int, string>();
int currentCol = 4;
foreach (string classify in _prizeClasifies)
{
ws.Cell(2, currentCol).Value = classify;
dictionaryClassify.Add(currentCol, classify);
currentCol++;
}
ws.Cell(2, currentCol).Value = "合计";
ws.Cell(2, currentCol).Style.Font.Bold = true;
//填写个人数据
DigitToChnText dtc = new DigitToChnText();
int departmentNo = 1;
int currentRow = 3;
foreach (string department in _departmets)
{
var departmentStartrow = currentRow;
int currentEmployeeNo = 1;
string currentClassify = "";
decimal totalDepartmentMoney = 0;
ws.Cell(currentRow, 1).Value = dtc.Convert(departmentNo.ToString(), false);
ws.Cell(currentRow, 1).Style.Fill.BackgroundColor = XLColor.Orange;
ws.Cell(currentRow, 2).Value = department;
ws.Cell(currentRow, 2).Style.Alignment.WrapText = true;
ws.Cell(currentRow, 3).Value = "小计";
ws.Cell(currentRow, 3).Style.Fill.BackgroundColor = XLColor.Orange;
for (currentCol = 4; currentCol < _totalPrizeClassify + 4; currentCol++)
{
currentClassify = dictionaryClassify[currentCol];
var departmentMoney =
_prizeses.Where(
p => p.Department.Equals(department) && p.PrizeClassify.Equals(currentClassify))
.Sum(p => p.Prize) ?? 0;
totalDepartmentMoney += departmentMoney;
dictionaryClassifyMoney[dictionaryClassify[currentCol]] += departmentMoney;
ws.Cell(currentRow, currentCol).Value = departmentMoney == 0
? "0"
: departmentMoney.ToString("N0");
ws.Cell(currentRow, currentCol).Style.Fill.BackgroundColor = XLColor.Orange;
}
//部门合计
ws.Cell(currentRow, currentCol).Value = totalDepartmentMoney == 0
? "0"
: totalDepartmentMoney.ToString("N0");
ws.Cell(currentRow, currentCol).Style.Fill.BackgroundColor = XLColor.Orange;
currentRow++;
ws.Cell(currentRow, currentCol).Style.Font.Bold = true;
//部门员工汇总
var employees =
_prizeses.Where(p => p.Department.Equals(department))
.Select(
p => new { Name = p.Name, AccountName = p.AccountName, Department = p.Department })
.Distinct()
.ToList();
Dictionary <string, decimal> dictionaryEmSum = new Dictionary <string, decimal>();
foreach (var E in employees)
{
var thisMoney = _prizeses.Where(p => p.AccountName.Equals(E.AccountName) && p.Name.Equals(E.Name) && p.Department.Equals(E.Department)).Sum(p => p.Prize) ?? 0;
if (!string.IsNullOrWhiteSpace(E.AccountName))
{
dictionaryEmSum.Add(E.AccountName, thisMoney);
}
else
{
dictionaryEmSum.Add(E.Name, thisMoney);
}
}
dictionaryEmSum = dictionaryEmSum.OrderByDescending(E => E.Value).ToDictionary(E => E.Key, p => p.Value);
foreach (KeyValuePair <string, decimal> em in dictionaryEmSum)
{
ws.Cell(currentRow, 1).Value = currentEmployeeNo.ToString();
var employee = employees.FirstOrDefault(e => e.AccountName.Equals(em.Key) || e.Name.Equals(em.Key));
ws.Cell(currentRow, 3).Value = employee == null ? "" : employee.Name;
for (currentCol = 4; currentCol < _totalPrizeClassify + 4; currentCol++)
{
var employeeClassifyMoney = _prizeses.Where(
p =>
p.AccountName.Equals(em.Key) &&
p.PrizeClassify.Equals(dictionaryClassify[currentCol])).Sum(p => p.Prize) ?? 0;
ws.Cell(currentRow, currentCol).Value = employeeClassifyMoney == 0
? "0"
: employeeClassifyMoney.ToString("N0");
}
//合计栏
ws.Cell(currentRow, currentCol).Value = em.Value == 0
? "0"
: em.Value.ToString("N0");
ws.Cell(currentRow, currentCol).Style.Font.Bold = true;
currentRow++;
currentEmployeeNo++;
}
//合并部门名称列
var departmentRows = ws.Range(departmentStartrow, 2, currentRow - 1, 2);
departmentRows.Merge();
departmentNo++;
}
//总计行
ws.Cell(currentRow, 2).Value = "合计";
ws.Range(currentRow, 2, currentRow, 3).Merge();
for (currentCol = 4; currentCol < _totalPrizeClassify + 4; currentCol++)
{
ws.Cell(currentRow, currentCol).Value = dictionaryClassifyMoney[dictionaryClassify[currentCol]] == 0
? "0"
: dictionaryClassifyMoney[dictionaryClassify[currentCol]].ToString("N0");
}
ws.Cell(currentRow, currentCol).Value = _totalMoney == 0 ? "0" : _totalMoney.ToString("N0");
ws.Range(currentRow, 1, currentRow, currentCol).Style.Fill.BackgroundColor = XLColor.DarkOrange;
ws.Range(currentRow, 1, currentRow, currentCol).Style.Font.Bold = true;
var mainBody = ws.Range(2, 1, currentRow, currentCol);
mainBody.Style.Font.FontSize = 10;
mainBody.Style.Font.FontName = "宋体";
mainBody.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
mainBody.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
mainBody.Style.Alignment.WrapText = false;
mainBody.Style.Border.OutsideBorder = XLBorderStyleValues.Thick;
mainBody.Style.Border.OutsideBorderColor = XLColor.Black;
mainBody.Style.Border.InsideBorder = XLBorderStyleValues.Thin;
mainBody.Style.Border.InsideBorderColor = XLColor.Black;
currentRow++;
ws.Cell(currentRow, 1).Value = "编制：";
ws.Cell(currentRow, 4).Value = "复核：";
ws.Cell(currentRow, 7).Value = "审核：";
ws.Cell(currentRow, currentCol).Value = DateTime.Now.ToString("0:yyyy.MM.dd",
DateTimeFormatInfo.InvariantInfo);
var rowLast = ws.Range(currentRow, 1, currentRow, currentCol);
rowLast.Style.Font.FontName = "宋体";
rowLast.Style.Font.FontSize = 10;
rowLast.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
rowLast.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
ws.Columns().AdjustToContents();
ws.Column(2).Width = 30;
ws.Rows().AdjustToContents();
ws.Rows().Height = 15;
ws.Row(1).Height = 20;
//MessageBox.Show("生成个人汇总表成功！");
}
catch (Exception error)
{
MessageBox.Show(error.Message);
}
}