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