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 void ExportSingle(
DateTime startDate,
DateTime endDate,
Excel._Workbook m_objBook,
Excel.Sheets m_objSheets,
Excel._Worksheet m_objSheet,
Excel.Range m_objRange,
Excel.Font m_objFont,
System.Reflection.Missing m_objOpt)
{
using (DataTable dt = new DutyInfoDA().GetTeaDutyNormal(string.Empty, string.Empty, string.Empty, string.Empty, startDate, endDate, 100).Tables[0])
{
if (dt != null && dt.Rows.Count > 0)
{
DataView dv = dt.DefaultView;
using (DataTable dtBaseInfo = new TeacherBaseDataAccess().GetTcBaseInfo(string.Empty, string.Empty, string.Empty, string.Empty).Tables[0])
{
object[, ] objData = null;
foreach(DataRow dr in dtBaseInfo.Rows)
{
dv.RowFilter = "T_Number = " + dr[1].ToString();
objData = new object[dv.Count, 6];
if (dv.Count > 0)
{
for (int i = 0; i < dv.Count; i++)
{
objData[i,0] = dv[i][3].ToString();
objData[i,1] = dv[i][2].ToString();
objData[i,2] = dv[i][0].ToString();
objData[i,3] = dv[i][1].ToString();
objData[i,4] = dv[i][4].ToString();
objData[i,5] = dv[i][5].ToString();
}
m_objSheets = (Excel.Sheets)m_objBook.Sheets;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(4));
m_objSheet.Copy(Type.Missing, m_objSheet);
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(5));
m_objSheet.Name = objData[0, 0].ToString() + string.Format("({0})", objData[0, 1]);
m_objRange = m_objSheet.get_Range("A3",m_objOpt);
m_objRange = m_objRange.get_Resize(dv.Count, 6);
m_objRange.Value = objData;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objFont = m_objRange.Font;
m_objFont.Size = 9;
m_objRange = m_objSheet.get_Range("A"+(dv.Count+4).ToString(),m_objOpt);
m_objRange.Value = "园所:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("B"+(dv.Count+4).ToString(),m_objOpt);
m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("A"+(dv.Count+5).ToString(),m_objOpt);
m_objRange.Value = "统计开始日期:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("B"+(dv.Count+5).ToString(),m_objOpt);
m_objRange.Value = startDate.ToString("yyyy.MM.dd");
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("A"+(dv.Count+6).ToString(),m_objOpt);
m_objRange.Value = "统计结束日期:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("B"+(dv.Count+6).ToString(),m_objOpt);
m_objRange.Value = endDate.ToString("yyyy.MM.dd");
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
}
}
}
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(4));
m_objSheet.Delete();
}
}
}
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();
}
/// <summary>
/// Copy the row to a new worksheet
/// </summary>
/// <param name="sheet">The sheet to copy to</param>
/// <param name="row">The row to copy</param>
/// <param name="count">The times to copy</param>
/// <param name="index">The index of row to copy to</param>
protected void CopyFormat(Excel.Worksheet sheet, Excel.Range row, int count, int index)
{
int rowindex = index;
for (int i = 0; i < count; i++)
{
row.Copy(sheet.get_Range(sheet.Cells[rowindex, 1], sheet.Cells[rowindex, 1]));
rowindex++;
}
}
