/// <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); } }