public void GenerateReportsPersonByPerson(string gardenName, DateTime[] dates, Action notify)
{
KillProcess();
var dir = excelPath + @"report\成长记录报表\" + dates[0].ToString("yyyy") + @"\班主任报表";
if (!Directory.Exists(dir))
Directory.CreateDirectory(dir);
var hierarchy = new GrowUpReportDataAccess().GetGrowUpReports(dates);
var reportTemplate = new ReportTemplate();
try
{
foreach (var groupByGrade in hierarchy.Root.GetCollection())
{
var gradeDir = string.Format(@"{0}\{1}个人汇总", dir, groupByGrade.Key);
if (!Directory.Exists(gradeDir))
Directory.CreateDirectory(gradeDir);
foreach (var groupByClass in groupByGrade.Value.GetCollection())
{
try
{
m_objExcel = new Excel.Application();
m_objExcel.DisplayAlerts = false;
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\GrowUpReport1.xls",
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
//生成年龄段表头
var kvp = reportTemplate.GetAgeDesc(groupByGrade.Key, "report1");
foreach (var pos in kvp.Value.Split(','))
{
m_objSheet.get_Range(pos).Value = kvp.Key;
}
DoGenerateReportsPersonByPerson(gardenName, groupByClass.Key, groupByClass.Value, m_objSheet, reportTemplate, dates[0], dates[dates.Length - 1]);
m_objBook.SaveAs(string.Format("{0}\\{1}.xls", gradeDir, groupByClass.Key), m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
notify();
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
}
}
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
public void GenerateStatsReports(string gardenName, DateTime[] dates, Action notify)
{
KillProcess();
var dir = excelPath + @"report\成长记录报表\" + dates[0].ToString("yyyy") + @"\管理员报表";
if (!Directory.Exists(dir))
Directory.CreateDirectory(dir);
dir += "\\年级汇总";
if (!Directory.Exists(dir))
Directory.CreateDirectory(dir);
var hierarchy = new GrowUpReportDataAccess().GetGrowUpReports(dates);
var reportTemplate = new ReportTemplate();
try
{
foreach (var groupByGrade in hierarchy.Root.GetCollection())
{
Excel._Worksheet templateSheet = null;
m_objExcel = new Excel.Application();
m_objExcel.DisplayAlerts = false;
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\GrowUpReport3.xls",
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt);
foreach (var groupByClass in groupByGrade.Value.GetStatsCollection())
{
try
{
int studentCount = groupByGrade.Value.GetStudentCount(groupByClass.Key);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
templateSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
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 = string.Format("{0}汇总", groupByClass.Key);
m_objRange = m_objSheet.get_Range("A1", m_objOpt);
m_objRange.Value = string.Format("{0}（{1}{2}人） “快乐成长”记录情况汇总（各项表现数量及所占比率）", gardenName, groupByClass.Key, studentCount);
//生成年龄段表头
var kvp = reportTemplate.GetAgeDesc(groupByGrade.Key, "report3");
foreach (var pos in kvp.Value.Split(','))
{
m_objSheet.get_Range(pos).Value = kvp.Key;
}
var ignorePos = reportTemplate.GetIgnorePos(groupByGrade.Key, ReportTemplate.IgnoreType.Ignore2);
if (!string.IsNullOrEmpty(ignorePos))
{
foreach (var split in ignorePos.Split(','))
{
if (!string.IsNullOrEmpty(split))
{
var column = split[0].ToString();
var row = split.Substring(1);
var lastRange = m_objSheet.get_Range(string.Format("J{0}", row));
var ignoreRange = m_objSheet.get_Range(split);
ignoreRange.Interior.ColorIndex = lastRange.Interior.ColorIndex;
ignoreRange.Value = lastRange.Value;
}
}
}
DoGenerateStatsReports(gardenName, groupByGrade.Key, groupByClass.Key, studentCount, groupByClass.Value, reportTemplate);
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
}
templateSheet.Delete();
m_objBook.SaveAs(string.Format("{0}\\{1}.xls", dir, groupByGrade.Key), m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
notify();
}
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
