public ActionResult ExportStats(Schools school, int year)
{
var stats = new ECAStatistic {
school = school, year = year
};
stats.CalculateStats(repository);
var ms = new MemoryStream();
using (var fs =
new FileStream(
AppDomain.CurrentDomain.BaseDirectory + "/Content/templates/NPOITemplate.xls",
FileMode.Open, FileAccess.Read, FileShare.Read))
{
HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);
var sheet = templateWorkbook.CreateSheet(school.ToString());
// create fonts
var boldStyle = templateWorkbook.CreateCellStyle();
var boldFont = templateWorkbook.CreateFont();
boldFont.Boldweight = (short)FontBoldWeight.BOLD;
boldStyle.SetFont(boldFont);
var rowcount = 0;
var row = sheet.CreateRow(rowcount++);
// show general stats first
var namecell = row.CreateCell(0);
namecell.SetCellValue("Name");
namecell.CellStyle = boldStyle;
var malecell = row.CreateCell(1);
malecell.SetCellValue("Male");
malecell.CellStyle = boldStyle;
var femalecell = row.CreateCell(2);
femalecell.SetCellValue("Female");
femalecell.CellStyle = boldStyle;
foreach (var entry in stats.entries.OrderBy(x => x.name))
{
row = sheet.CreateRow(rowcount++);
row.CreateCell(0).SetCellValue(entry.name);
row.CreateCell(1).SetCellValue(entry.male);
row.CreateCell(2).SetCellValue(entry.female);
}
// resize
sheet.AutoSizeColumn(0);
sheet.AutoSizeColumn(1);
sheet.AutoSizeColumn(2);
// delete first sheet
templateWorkbook.RemoveSheetAt(0);
templateWorkbook.Write(ms);
}
// return created file path);
return(File(ms.ToArray(), "application/vnd.ms-excel", string.Format("Statistics_ECA_{0}_{1}.xls", school, year)));
}
public ActionResult Statistics(int to_day, int to_month, int to_year, Schools school,
int from_day, int from_month, int from_year)
{
var from = new DateTime(from_year, from_month, from_day);
var to = new DateTime(to_year, to_month, to_day);
// get matching entries
var viewmodel = new ConductStatistics
{
from = from.ToShortDateString(),
to = to.ToShortDateString(),
school = school.ToString()
};
viewmodel.PopulateStats(from, to, school);
return(View(viewmodel));
}
public ActionResult ExportStats(string from, string to, Schools school)
{
var start = DateTime.Parse(from);
var end = DateTime.Parse(to);
var stats = new ConductStatistics();
stats.PopulateStats(start, end, school);
var ms = new MemoryStream();
using (var fs =
new FileStream(
AppDomain.CurrentDomain.BaseDirectory + "/Content/templates/NPOITemplate.xls",
FileMode.Open, FileAccess.Read, FileShare.Read))
{
var templateWorkbook = new HSSFWorkbook(fs, true);
var sheet = templateWorkbook.CreateSheet(school.ToString());
// create fonts
var boldStyle = templateWorkbook.CreateCellStyle();
var boldFont = templateWorkbook.CreateFont();
boldFont.Boldweight = (short)FontBoldWeight.BOLD;
boldStyle.SetFont(boldFont);
var rowcount = 0;
var row = sheet.CreateRow(rowcount++);
row.CreateCell(0).SetCellValue("Behaviour Type");
row.GetCell(0).CellStyle = boldStyle;
row.CreateCell(1).SetCellValue("Students");
row.GetCell(1).CellStyle = boldStyle;
row.CreateCell(2).SetCellValue("Incidents");
row.GetCell(2).CellStyle = boldStyle;
foreach (var stat in stats.demerits.OrderByDescending(x => x.totalIncidents))
{
row = sheet.CreateRow(rowcount++);
row.CreateCell(0).SetCellValue(stat.name);
row.CreateCell(1).SetCellValue(stat.totalStudents);
row.CreateCell(2).SetCellValue(stat.totalIncidents);
}
foreach (var stat in stats.merits.OrderByDescending(x => x.totalIncidents))
{
row = sheet.CreateRow(rowcount++);
row.CreateCell(0).SetCellValue(stat.name);
row.CreateCell(1).SetCellValue(stat.totalStudents);
row.CreateCell(2).SetCellValue(stat.totalIncidents);
}
// adjust column
sheet.AutoSizeColumn(0);
// delete first sheet
templateWorkbook.RemoveSheetAt(0);
templateWorkbook.Write(ms);
}
// return created file path);
return(File(ms.ToArray(), "application/vnd.ms-excel", string.Format("Conduct_{0}_{1}.xls", from.Replace("/", ""), to.Replace("/", ""))));
}
public ActionResult ExportStats(Schools school, UserGroup ugroup, int year)
{
var stats = new ClassStatistics {
school = school, usergroup = ugroup, year = year
};
stats.CalculateStats(repository);
var ms = new MemoryStream();
using (var fs =
new FileStream(
AppDomain.CurrentDomain.BaseDirectory + "/Content/templates/NPOITemplate.xls",
FileMode.Open, FileAccess.Read, FileShare.Read))
{
var templateWorkbook = new HSSFWorkbook(fs, true);
var sheet = templateWorkbook.CreateSheet(school.ToString());
// create fonts
var boldStyle = templateWorkbook.CreateCellStyle();
var boldFont = templateWorkbook.CreateFont();
boldFont.Boldweight = (short)FontBoldWeight.BOLD;
boldStyle.SetFont(boldFont);
var rowcount = 0;
var row = sheet.CreateRow(rowcount++);
var colcount = 0;
// show general stats first
row.CreateCell(colcount).SetCellValue(SecurityElement.Escape("Malaysian"));
colcount += 2;
row.CreateCell(colcount).SetCellValue(SecurityElement.Escape("Foreigners"));
colcount += 2;
row.CreateCell(colcount).SetCellValue(SecurityElement.Escape("SubTotal"));
colcount += 2;
row.CreateCell(colcount).SetCellValue(SecurityElement.Escape("Total"));
row = sheet.CreateRow(rowcount++);
for (int i = 0; i < 3; i++)
{
row.CreateCell(i * 2).SetCellValue(SecurityElement.Escape("M"));
row.CreateCell(i * 2 + 1).SetCellValue(SecurityElement.Escape("F"));
}
row = sheet.CreateRow(rowcount++);
row.CreateCell(0, CellType.NUMERIC).SetCellValue(stats.msian_male);
row.CreateCell(1, CellType.NUMERIC).SetCellValue(stats.msian_female);
row.CreateCell(2, CellType.NUMERIC).SetCellValue(stats.foreign_male);
row.CreateCell(3, CellType.NUMERIC).SetCellValue(stats.foreign_female);
row.CreateCell(4, CellType.NUMERIC).SetCellValue(stats.msian_male + stats.foreign_male);
row.CreateCell(5, CellType.NUMERIC).SetCellValue(stats.msian_female + stats.foreign_female);
row.CreateCell(6, CellType.NUMERIC).SetCellValue(stats.msian_male + stats.foreign_male + stats.msian_female + stats.foreign_female);
foreach (var entry in stats.collections)
{
// class row
row = sheet.CreateRow(rowcount++);
row.CreateCell(0).SetCellValue(SecurityElement.Escape(entry.name));
row.GetCell(0).CellStyle = boldStyle;
// header row1
row = sheet.CreateRow(rowcount++);
colcount = 0;
foreach (var race in entry.GetList())
{
row.CreateCell(colcount).SetCellValue(SecurityElement.Escape(race.name));
colcount += 2;
}
row.CreateCell(colcount).SetCellValue(SecurityElement.Escape("Total"));
// header row2
row = sheet.CreateRow(rowcount++);
for (int i = 0; i < entry.GetList().Count(); i++)
{
row.CreateCell(i * 2).SetCellValue(SecurityElement.Escape("M"));
row.CreateCell(i * 2 + 1).SetCellValue(SecurityElement.Escape("F"));
}
// stats row
row = sheet.CreateRow(rowcount++);
colcount = 0;
foreach (var race in entry.GetList())
{
row.CreateCell(colcount++).SetCellValue(race.male);
row.CreateCell(colcount++).SetCellValue(race.female);
}
row.CreateCell(colcount).SetCellValue(entry.GetList().Sum(x => x.male + x.female));
}
// delete first sheet
templateWorkbook.RemoveSheetAt(0);
templateWorkbook.Write(ms);
}
// return created file path);
return(File(ms.ToArray(), "application/vnd.ms-excel", string.Format("Statistics_{0}_{1}.xls", ugroup, school)));
}
