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