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