private void DoGenerateStatsReports( string gardenName, string gradeName, string className, int studentCount, GrowUpReportDataAccess.ReportGroupByCategory grouping, ReportTemplate reportTemplate) { int offset = 0; foreach (var groupByCategory in grouping.GetCollection()) { int k = 0; var list = groupByCategory.Value.GetStatsCollection().ToList(); for (int i = 0; i < list.Count; i++) { var groupByDate = list[i]; if (k >= 1) { offset++; //向上插入一行 m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, m_objRange.Column], (Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, m_objRange.Column]).EntireRow.Insert(Excel.XlDirection.xlDown); } WriteStatsCell(gradeName, reportTemplate, groupByCategory.Key, "(T)", groupByDate.Key, offset); var report = groupByDate.Value; var val = string.IsNullOrEmpty(report.Item1) ? "0" : studentCount == 0 ? "0" : string.Format("{0}({1})", report.Item1Count, (report.Item1Count/(double)studentCount).ToString("0.00%")); WriteStatsCell(gradeName, reportTemplate, groupByCategory.Key, "(1)", val, offset); val = string.IsNullOrEmpty(report.Item2) ? "0" : studentCount == 0 ? "0" : string.Format("{0}({1})", report.Item2Count, (report.Item2Count / (double)studentCount).ToString("0.00%")); WriteStatsCell(gradeName, reportTemplate, groupByCategory.Key, "(2)", val, offset); val = string.IsNullOrEmpty(report.Item3) ? "0" : studentCount == 0 ? "0" : string.Format("{0}({1})", report.Item3Count, (report.Item3Count / (double)studentCount).ToString("0.00%")); WriteStatsCell(gradeName, reportTemplate, groupByCategory.Key, "(3)", val, offset); val = string.IsNullOrEmpty(report.Item4) ? "0" : studentCount == 0 ? "0" : string.Format("{0}({1})", report.Item4Count, (report.Item4Count / (double)studentCount).ToString("0.00%")); WriteStatsCell(gradeName, reportTemplate, groupByCategory.Key, "(4)", val, offset); val = string.IsNullOrEmpty(report.Item5) ? "0" : studentCount == 0 ? "0" : string.Format("{0}({1})", report.Item5Count, (report.Item5Count / (double)studentCount).ToString("0.00%")); WriteStatsCell(gradeName, reportTemplate, groupByCategory.Key, "(5)", val, offset); val = string.IsNullOrEmpty(report.Item6) ? "0" : studentCount == 0 ? "0" : string.Format("{0}({1})", report.Item6Count, (report.Item6Count / (double)studentCount).ToString("0.00%")); WriteStatsCell(gradeName, reportTemplate, groupByCategory.Key, "(6)", val, offset); if (i == list.Count - 1) { if (k >= 1) { //将各级别单元格合并 var currentRow = m_objRange.Row; MergeLevel(currentRow, "A"); MergeLevel(currentRow, "B"); MergeLevel(currentRow, "C", k); MergeLevel(currentRow, "F", k); MergeLevel(currentRow, "G", k); MergeLevel(currentRow, "H", k); MergeLevel(currentRow, "I", k); MergeLevel(currentRow, "J", k); MergeLevel(currentRow, "K", k); } } k++; } } m_objSheet.Range[string.Format("A2:K{0}", 38 + offset)].Borders.LineStyle = Excel.XlLineStyle.xlContinuous; m_objSheet.get_Range(string.Format("A{0}", 39 + offset)).Value = string.Format("统计时间:{0}", DateTime.Now.ToString("yyyy.MM.dd")); }
private void DoGenerateReportsByClass(string gradeName, GrowUpReportDataAccess.ReportGroupByStudent grouping, ReportTemplate reportTemplate, DateTime date) { int offset = 1; foreach (var groupByStudent in grouping.GetCollection()) { if (offset >= 2) m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, 1], (Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, 1]).EntireRow.Insert(Excel.XlDirection.xlDown); var stuName = groupByStudent.Value.Key; var regex = new Regex(@"(?<col>\w+)(?<row>\d)"); m_objRange = (Excel.Range)m_objSheet.get_Range(string.Format("A{0}", 5 + offset)); m_objRange.Value = groupByStudent.Key; m_objSheet.get_Range(string.Format("B{0}", 5 + offset)).Value = stuName; int itemCount = 0; foreach (var groupByCategory in groupByStudent.Value.Value.GetCollection()) { var category = groupByCategory.Key; var report = groupByCategory.Value.GetAllReports().FirstOrDefault(); if (report != null && !string.IsNullOrEmpty(report.Item)) { var split = report.Item.Split(')'); for (int j = 0; j < split.Length; j++) { if (!string.IsNullOrEmpty(split[j])) { string column = string.Empty; string row = string.Empty; var range = reportTemplate.GetPos(string.Format("{0}_{1})", category, split[j]), ReportTemplate.ReportType.Report2); if (!string.IsNullOrEmpty(range)) { var match = regex.Match(range); column = match.Groups["col"].Value; row = match.Groups["row"].Value; m_objRange = m_objSheet.get_Range(string.Format("{0}{1}", column, Convert.ToInt32(row) + offset), m_objOpt); m_objRange.Value = 1; itemCount++; } } } } } m_objSheet.get_Range(string.Format("C{0}", 5 + offset)).Value = itemCount; itemCount = 0; offset++; } //offset多加了一次,下面计算的时候,OFFSET都会减一 var ignorePos = reportTemplate.GetIgnorePos(gradeName, ReportTemplate.IgnoreType.Ignore1); if (!string.IsNullOrEmpty(ignorePos)) { var regex = new Regex(@"(?<col>\w+)(?<row>\d)"); foreach (var split in ignorePos.Split(',')) { var match = regex.Match(split); var column = match.Groups["col"].Value; var row = match.Groups["row"].Value; var range = m_objSheet.Range[string.Format("{0}6:{0}{1}", column, Convert.ToInt32(row) + offset)]; range.Value = "/"; range = m_objSheet.Range[string.Format("{0}5:{0}{1}", column, Convert.ToInt32(row) + offset)]; range.Interior.ColorIndex = 27; //http://dmcritchie.mvps.org/excel/colors.htm range.EntireColumn.Hidden = true; } } m_objSheet.get_Range(string.Format("A{0}", 6 + offset)).Value = string.Format("统计日期:{0}", date.ToString("yyyy.MM")); }
private int DoGenerateReportsByGrade(string className, GrowUpReportDataAccess.ReportGroupByStudent grouping, ReportTemplate reportTemplate, DateTime date, int offset) { int itemCount = 0; foreach (var groupByStudent in grouping.GetCollection()) { var classNumber = groupByStudent.Key.ToString().Substring(0, 2); var regex = new Regex(@"(?<col>\w+)(?<row>\d)"); m_objRange = (Excel.Range)m_objSheet.get_Range(string.Format("A{0}", 5 + offset)); m_objRange.Value = classNumber; m_objSheet.get_Range(string.Format("B{0}", 5 + offset)).Value = className; foreach (var groupByCategory in groupByStudent.Value.Value.GetCollection()) { var category = groupByCategory.Key; var report = groupByCategory.Value.GetAllReports().FirstOrDefault(); if (report != null && !string.IsNullOrEmpty(report.Item)) { var split = report.Item.Split(')'); for (int j = 0; j < split.Length; j++) { if (!string.IsNullOrEmpty(split[j])) { string column = string.Empty; string row = string.Empty; var range = reportTemplate.GetPos(string.Format("{0}_{1})", category, split[j]), ReportTemplate.ReportType.Report2); if (!string.IsNullOrEmpty(range)) { var match = regex.Match(range); column = match.Groups["col"].Value; row = match.Groups["row"].Value; m_objRange = m_objSheet.get_Range(string.Format("{0}{1}", column, Convert.ToInt32(row) + offset), m_objOpt); var val = m_objRange.Value; if (val == null) m_objRange.Value = 1; else m_objRange.Value = Convert.ToInt32(val) + 1; itemCount++; } } } } } } return itemCount; }
private void DoGenerateReportsPersonByPerson( string gardenName, string className, GrowUpReportDataAccess.ReportGroupByStudent grouping, Excel._Worksheet templateSheet, ReportTemplate reportTemplate, DateTime beginDate, DateTime endDate) { foreach (var groupByStudent in grouping.GetCollection()) { var stuName = groupByStudent.Value.Key; var lastSheet = m_objSheets.get_Item(m_objSheets.Count); templateSheet.Copy(m_objOpt, lastSheet); m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count); m_objSheet.Name = stuName; m_objRange = m_objSheet.get_Range("A1", m_objOpt); m_objRange.Value = string.Format("{0}({1}){2} {3} \"快乐成长\"记录情况汇总", gardenName, className, groupByStudent.Key, stuName); int offset = 0; foreach (var groupByCategory in groupByStudent.Value.Value.GetCollection()) { int k = 0; var category = groupByCategory.Key; var reportDateList = groupByCategory.Value.GetAllReports().ToList(); for (int i = 0; i < reportDateList.Count; i++) { var report = reportDateList[i]; if (!string.IsNullOrEmpty(report.Item)) { if (k >= 1) { offset++; //向上插入一行 m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, m_objRange.Column], (Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, m_objRange.Column]).EntireRow.Insert(Excel.XlDirection.xlDown); } string column = string.Empty; string row = string.Empty; var range = reportTemplate.GetPos(string.Format("{0}_Item", category), ReportTemplate.ReportType.Report1); if (!string.IsNullOrEmpty(range)) { column = range[0].ToString(); row = range.Substring(1); m_objRange = m_objSheet.get_Range(string.Format("{0}{1}", column, Convert.ToInt32(row) + offset), m_objOpt); m_objRange.Value = report.Item; } range = reportTemplate.GetPos(string.Format("{0}_Time", category), ReportTemplate.ReportType.Report1); if (!string.IsNullOrEmpty(range)) { column = range[0].ToString(); row = range.Substring(1); m_objRange = m_objSheet.get_Range(string.Format("{0}{1}", column, Convert.ToInt32(row) + offset), m_objOpt); m_objRange.Value = reportDateList[i].Date.ToString("yyyy年MM月"); } range = reportTemplate.GetPos(string.Format("{0}_Desc", category), ReportTemplate.ReportType.Report1); if (!string.IsNullOrEmpty(range)) { column = range[0].ToString(); row = range.Substring(1); m_objRange = m_objSheet.get_Range(string.Format("{0}{1}", column, Convert.ToInt32(row) + offset), m_objOpt); m_objRange.Value = report.Content; } range = reportTemplate.GetPos(string.Format("{0}_Pic", category), ReportTemplate.ReportType.Report1); if (!string.IsNullOrEmpty(range)) { column = range[0].ToString(); row = range.Substring(1); m_objRange = m_objSheet.get_Range(string.Format("{0}{1}", column, Convert.ToInt32(row) + offset), m_objOpt); if (!string.IsNullOrEmpty(report.PicUrl) && !report.PicUrl.Contains("http")) { m_objRange.Select(); float PicLeft, PicTop; PicLeft = Convert.ToSingle(m_objRange.Left); PicTop = Convert.ToSingle(m_objRange.Top); var fileName = AppDomain.CurrentDomain.BaseDirectory + report.PicUrl; if (!File.Exists(fileName)) { if (!string.IsNullOrEmpty(report.RawUrl) && report.RawUrl.Contains("http")) { var buffer = new WebClient().DownloadData(report.RawUrl); using (var stream = new MemoryStream(buffer)) { using (var img = Image.FromStream(stream)) { img.Save(fileName); } } } } if (File.Exists(fileName)) { m_objSheet.Shapes.AddPicture(fileName, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, PicLeft, PicTop, (float)m_objRange.Width, (float)m_objRange.Height); } } else m_objRange.Value = report.PicUrl; } if (i == reportDateList.Count - 1) { if (k >= 1) { //将各级别单元格合并 var currentRow = m_objRange.Row; MergeLevel(currentRow, "C"); MergeLevel(currentRow, "H"); MergeLevel(currentRow, "B"); MergeLevel(currentRow, "A"); } } k++; } } } m_objSheet.Range[string.Format("A2:H{0}", 38 + offset)].Borders.LineStyle = Excel.XlLineStyle.xlContinuous; m_objSheet.get_Range(string.Format("A{0}", 39 + offset), m_objOpt).Value = string.Format("汇总周期:{0}---{1}", beginDate.ToString("yyyy.MM.dd"), endDate.ToString("yyyy.MM.dd")); } templateSheet.Delete(); }
private int ComputeTotalByClass(GrowUpReportDataAccess.CheckReportGroupByStudent groupByStudent, string state) { return groupByStudent.GetCollection().Sum(a => a.Value.Value.GetAllReports().Sum(p => p.GetCount(state))); }
private void DoGenerateCheckReportsGradeByGrade( string className, GrowUpReportDataAccess.CheckReportGroupByStudent grouping, ReportTemplate reportTemplate, int checkInDays, int offset, ref int total1, ref int total2, ref int total3, ref int total4, ref int total5, ref int total6, ref int total7, ref int total8, ref int total9, ref int total10, ref int total11, ref int total12, ref int total13, ref int total14, ref int total15, ref int total16, ref int total17, ref int total18, ref int total19, ref int total20, ref int total21, ref int total22, ref int totalStudentCount) { checkInDays = checkInDays == 0 ? 1 : checkInDays; var sum1 = ComputeTotalByClass(grouping, "0"); var sum2 = ComputeTotalByClass(grouping, "3"); var sum3 = ComputeTotalByClass(grouping, "2"); var sum4 = ComputeTotalByClass(grouping, "-1"); var sum5 = ComputeTotalByClass(grouping, "1_1"); var sum6 = ComputeTotalByClass(grouping, "1_2"); var sum7 = ComputeTotalByClass(grouping, "1_3"); var sum8 = ComputeTotalByClass(grouping, "2_1"); var sum9 = ComputeTotalByClass(grouping, "2_2"); var sum10 = ComputeTotalByClass(grouping, "2_3"); var sum11 = ComputeTotalByClass(grouping, "3_1"); var sum12 = ComputeTotalByClass(grouping, "3_2"); var sum13 = ComputeTotalByClass(grouping, "3_3"); var sum14 = ComputeTotalByClass(grouping, "4_1"); var sum15 = ComputeTotalByClass(grouping, "4_2"); var sum16 = ComputeTotalByClass(grouping, "4_3"); var sum17 = ComputeTotalByClass(grouping, "5_1"); var sum18 = ComputeTotalByClass(grouping, "5_2"); var sum19 = ComputeTotalByClass(grouping, "5_3"); var sum20 = ComputeTotalByClass(grouping, "6_1"); var sum21 = ComputeTotalByClass(grouping, "6_2"); var sum22 = ComputeTotalByClass(grouping, "6_3"); total1 += sum1; total2 += sum2; total3 += sum3; total4 += sum4; total5 += sum5; total6 += sum6; total7 += sum7; total8 += sum8; total9 += sum9; total10 += sum10; total11 += sum11; total12 += sum12; total13 += sum13; total14 += sum14; total15 += sum15; total16 += sum16; total17 += sum17; total18 += sum18; total19 += sum19; total20 += sum20; total21 += sum21; total22 += sum22; int studentCount = grouping.GetCollection().Count(); totalStudentCount += studentCount; WriteCheckReportCell(reportTemplate, "no", className, offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "name", studentCount.ToString(), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "0_sum", string.Format("{0}({1})", sum1, (sum1 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "3_sum", string.Format("{0}({1})", sum2, (sum2 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "2_sum", string.Format("{0}({1})", sum3, (sum3 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "-1_sum", string.Format("{0}({1})", sum4, (sum4 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "1_1_sum", string.Format("{0}({1})", sum5, (sum5 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "1_2_sum", string.Format("{0}({1})", sum6, (sum6 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "1_3_sum", string.Format("{0}({1})", sum7, (sum7 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "2_1_sum", string.Format("{0}({1})", sum8, (sum8 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "2_2_sum", string.Format("{0}({1})", sum9, (sum9 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "2_3_sum", string.Format("{0}({1})", sum10, (sum10 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "3_1_sum", string.Format("{0}({1})", sum11, (sum11 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "3_2_sum", string.Format("{0}({1})", sum12, (sum12 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "3_3_sum", string.Format("{0}({1})", sum13, (sum13 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "4_1_sum", string.Format("{0}({1})", sum14, (sum14 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "4_2_sum", string.Format("{0}({1})", sum15, (sum15 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "4_3_sum", string.Format("{0}({1})", sum16, (sum16 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "5_1_sum", string.Format("{0}({1})", sum17, (sum17 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "5_2_sum", string.Format("{0}({1})", sum18, (sum18 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "5_3_sum", string.Format("{0}({1})", sum19, (sum19 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "6_1_sum", string.Format("{0}({1})", sum20, (sum20 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "6_2_sum", string.Format("{0}({1})", sum21, (sum21 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "6_3_sum", string.Format("{0}({1})", sum22, (sum22 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); }
private void DoGenerateCheckReportsClassByClass(string gardenName, string className, GrowUpReportDataAccess.CheckReportGroupByStudent grouping, Excel._Worksheet templateSheet, ReportTemplate reportTemplate, DateTime date, int checkInDays) { checkInDays = checkInDays == 0 ? 1 : checkInDays; int offset = 1; int total1 = 0; int total2 = 0;int total3 = 0; int total4 = 0; int total5 = 0; int total6 = 0; int total7 = 0; int total8 = 0; int total9 = 0; int total10 = 0; int total11 = 0; int total12 = 0; int total13 = 0; int total14 = 0; int total15 = 0; int total16 = 0; int total17 = 0; int total18 = 0; int total19 = 0; int total20 = 0; int total21 = 0; int total22 = 0; foreach (var groupByStudent in grouping.GetCollection()) { if (offset >= 2) { m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, m_objRange.Column], (Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, m_objRange.Column]).EntireRow.Insert(Excel.XlDirection.xlDown); } var sum1 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("0")); var sum2 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("3")); var sum3 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("2")); var sum4 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("-1")); var sum5 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("1_1")); var sum6 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("1_2")); var sum7 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("1_3")); var sum8 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("2_1")); var sum9 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("2_2")); var sum10 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("2_3")); var sum11 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("3_1")); var sum12 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("3_2")); var sum13 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("3_3")); var sum14 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("4_1")); var sum15 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("4_2")); var sum16 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("4_3")); var sum17 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("5_1")); var sum18 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("5_2")); var sum19 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("5_3")); var sum20 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("6_1")); var sum21 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("6_2")); var sum22 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("6_3")); total1 += sum1; total2 += sum2; total3 += sum3; total4 += sum4; total5 += sum5; total6 += sum6; total7 += sum7; total8 += sum8; total9 += sum9; total10 += sum10; total11 += sum11; total12 += sum12; total13 += sum13; total14 += sum14; total15 += sum15; total16 += sum16; total17 += sum17; total18 += sum18; total19 += sum19; total20 += sum20; total21 += sum21; total22 += sum22; WriteCheckReportCell(reportTemplate, "no", groupByStudent.Key.ToString(), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "name", groupByStudent.Value.Key, offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "0_sum", string.Format("{0}({1})", sum1, (sum1 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "3_sum", string.Format("{0}({1})", sum2, (sum2 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "2_sum", string.Format("{0}({1})", sum3, (sum3 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "-1_sum", string.Format("{0}({1})", sum4, (sum4 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "1_1_sum", string.Format("{0}({1})", sum5, (sum5 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "1_2_sum", string.Format("{0}({1})", sum6, (sum6 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "1_3_sum", string.Format("{0}({1})", sum7, (sum7 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "2_1_sum", string.Format("{0}({1})", sum8, (sum8 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "2_2_sum", string.Format("{0}({1})", sum9, (sum9 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "2_3_sum", string.Format("{0}({1})", sum10, (sum10 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "3_1_sum", string.Format("{0}({1})", sum11, (sum11 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "3_2_sum", string.Format("{0}({1})", sum12, (sum12 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "3_3_sum", string.Format("{0}({1})", sum13, (sum13 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "4_1_sum", string.Format("{0}({1})", sum14, (sum14 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "4_2_sum", string.Format("{0}({1})", sum15, (sum15 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "4_3_sum", string.Format("{0}({1})", sum16, (sum16 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "5_1_sum", string.Format("{0}({1})", sum17, (sum17 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "5_2_sum", string.Format("{0}({1})", sum18, (sum18 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "5_3_sum", string.Format("{0}({1})", sum19, (sum19 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "6_1_sum", string.Format("{0}({1})", sum20, (sum20 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "6_2_sum", string.Format("{0}({1})", sum21, (sum21 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "6_3_sum", string.Format("{0}({1})", sum22, (sum22 / (double)checkInDays).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); offset++; } offset = offset - 2; int studentCount = grouping.GetCollection().Count(); WriteCheckReportCell(reportTemplate, "0_total", string.Format("{0}({1})", total1, (total1 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "3_total", string.Format("{0}({1})", total2, (total2 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "2_total", string.Format("{0}({1})", total3, (total3 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "-1_total", string.Format("{0}({1})", total4, ((checkInDays * studentCount - total1 - total2 - total3) / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "1_1_total", string.Format("{0}({1})", total5, (total5 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "1_2_total", string.Format("{0}({1})", total6, (total6 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "1_3_total", string.Format("{0}({1})", total7, (total7 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "2_1_total", string.Format("{0}({1})", total8, (total8 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "2_2_total", string.Format("{0}({1})", total9, (total9 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "2_3_total", string.Format("{0}({1})", total10, (total10 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "3_1_total", string.Format("{0}({1})", total11, (total11 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "3_2_total", string.Format("{0}({1})", total12, (total12 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "3_3_total", string.Format("{0}({1})", total13, (total13 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "4_1_total", string.Format("{0}({1})", total14, (total14 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "4_2_total", string.Format("{0}({1})", total15, (total15 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "4_3_total", string.Format("{0}({1})", total16, (total16 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "5_1_total", string.Format("{0}({1})", total17, (total17 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "5_2_total", string.Format("{0}({1})", total18, (total18 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "5_3_total", string.Format("{0}({1})", total19, (total19 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "6_1_total", string.Format("{0}({1})", total20, (total20 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "6_2_total", string.Format("{0}({1})", total21, (total21 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "6_3_total", string.Format("{0}({1})", total22, (total22 / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "0_all", string.Format("{0}({1})", total1 + total2 + total3, ((total1 + total2 + total3) / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "1_all", string.Format("{0}({1})", total4, ((checkInDays * studentCount - total1 - total2 - total3) / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "2_all", string.Format("{0}({1})", total5 + total6 + total7, ((total5 + total6 + total7) / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "3_all", string.Format("{0}({1})", total8 + total9 + total10, ((total8 + total9 + total10) / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "4_all", string.Format("{0}({1})", total11 + total12 + total13, ((total11 + total12 + total13) / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "5_all", string.Format("{0}({1})", total14 + total15 + total16, ((total14 + total15 + total16) / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "6_all", string.Format("{0}({1})", total17 + total18 + total19, ((total17 + total18 + total9) / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); WriteCheckReportCell(reportTemplate, "7_all", string.Format("{0}({1})", total20 + total21 + total22, ((total20 + total21 + total22) / ((double)checkInDays * studentCount)).ToString("0.00%")), offset, ReportTemplate.ReportType.Report5); m_objSheet.get_Range(string.Format("A{0}", 7 + offset)).Value = string.Format("统计日期:{0}", date.ToString("yyyy.MM.dd")); }
private void DoGenerateCheckReportsPersonByPerson(string gardenName, string className, GrowUpReportDataAccess.CheckReportGroupByStudent grouping, Excel._Worksheet templateSheet, ReportTemplate reportTemplate, DateTime date, int checkInDays) { foreach (var groupByStudent in grouping.GetCollection()) { var stuName = groupByStudent.Value.Key; var lastSheet = m_objSheets.get_Item(m_objSheets.Count); templateSheet.Copy(m_objOpt, lastSheet); m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count); m_objSheet.Name = stuName; m_objRange = m_objSheet.get_Range("A1", m_objOpt); m_objRange.Value = string.Format("{0}{1}({2}) {3} 在园体验汇总表 ({4})应出勤{5}天", gardenName, className, groupByStudent.Key, stuName, date.ToString("yyyy-MM月"), checkInDays); int offset = 1; foreach (var report in groupByStudent.Value.Value.GetAllReports()) { if (offset >= 2) { m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, m_objRange.Column], (Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, m_objRange.Column]).EntireRow.Insert(Excel.XlDirection.xlDown); } WriteCheckReportCell(reportTemplate, "date", report.Date, offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, report.State1, "1", offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, report.State2, "1", offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, report.State3, "1", offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, report.State4, "1", offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, report.State5, "1", offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, report.State6, "1", offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, report.State7, "1", offset, ReportTemplate.ReportType.Report4); offset++; } offset = offset - 2; var sum1 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("0")); var sum2 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("3")); var sum3 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("2")); var sum4 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("-1")); var sum5 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("1_1")); var sum6 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("1_2")); var sum7 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("1_3")); var sum8 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("2_1")); var sum9 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("2_2")); var sum10 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("2_3")); var sum11 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("3_1")); var sum12 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("3_2")); var sum13 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("3_3")); var sum14 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("4_1")); var sum15 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("4_2")); var sum16 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("4_3")); var sum17 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("5_1")); var sum18 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("5_2")); var sum19 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("5_3")); var sum20 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("6_1")); var sum21 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("6_2")); var sum22 = groupByStudent.Value.Value.GetAllReports().Sum(p => p.GetCount("6_3")); WriteCheckReportCell(reportTemplate, "0_sum", sum1.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "3_sum", sum2.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "2_sum", sum3.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "-1_sum", sum4.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "1_1_sum", sum5.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "1_2_sum", sum6.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "1_3_sum", sum7.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "2_1_sum", sum8.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "2_2_sum", sum9.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "2_3_sum", sum10.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "3_1_sum", sum11.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "3_2_sum", sum12.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "3_3_sum", sum13.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "4_1_sum", sum14.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "4_2_sum", sum15.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "4_3_sum", sum16.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "5_1_sum", sum17.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "5_2_sum", sum18.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "5_3_sum", sum19.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "6_1_sum", sum20.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "6_2_sum", sum21.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "6_3_sum", sum22.ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "1_total", (sum1 + sum2 + sum3 + sum4).ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "2_total", (sum5 + sum6 + sum7).ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "3_total", (sum8 + sum9 + sum10).ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "4_total", (sum11 + sum12 + sum13).ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "5_total", (sum14 + sum15 + sum16).ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "6_total", (sum17 + sum18 + sum19).ToString(), offset, ReportTemplate.ReportType.Report4); WriteCheckReportCell(reportTemplate, "7_total", (sum20 + sum21 + sum22).ToString(), offset, ReportTemplate.ReportType.Report4); } templateSheet.Delete(); }