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 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 void WriteCheckReportCell(ReportTemplate reportTemplate, string state, string val, int offset, ReportTemplate.ReportType reportType) { if (!string.IsNullOrEmpty(state)) { string column = string.Empty; string row = string.Empty; var range = reportTemplate.GetPos(state, reportType); 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 = val; } } }
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 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; } } }