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")); }
public void GenerateReportsByGrade(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 reportTemplate = new ReportTemplate(); //按月分组 foreach (var date in dates) { var hierarchy = new GrowUpReportDataAccess().GetGrowUpReports(date); try { 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\GrowUpReport2.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 groupByGrade in hierarchy.Root.GetCollection()) { 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}部统计汇总", groupByGrade.Key); m_objRange = m_objSheet.get_Range("A1", m_objOpt); m_objRange.Value = string.Format("{0}{1}部统计表", gardenName, groupByGrade.Key); int offset = 1; foreach (var groupByClass in groupByGrade.Value.GetCollection()) { try { 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 itemCount = DoGenerateReportsByGrade(groupByClass.Key, groupByClass.Value, reportTemplate, date, offset); m_objSheet.get_Range(string.Format("C{0}", 5 + offset)).Value = itemCount; offset++; //offset多加了一次,下面计算的时候,OFFSET都会减一 var ignorePos = reportTemplate.GetIgnorePos(groupByGrade.Key, 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")); } catch (Exception ex) { Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE); } } } templateSheet.Delete(); m_objBook.SaveAs(string.Format("{0}\\年级统计汇总({1}).xls", dir, date.ToString("yyyy.MM")), 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(); } } }
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(); } }
private void WriteStatsCell(string gradeName, ReportTemplate reportTemplate, string category, string item, string val, int offset) { string column = string.Empty; string row = string.Empty; var range = reportTemplate.GetPos(string.Format("{0}_{1}", category, item), ReportTemplate.ReportType.Report3); var ignorePos = reportTemplate.GetIgnorePos(gradeName, ReportTemplate.IgnoreType.Ignore2); if (!string.IsNullOrEmpty(range)) { if (string.IsNullOrEmpty(ignorePos) || !ignorePos.Contains(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 = val; } } }