public ActionResult AllTotal(VM_BoxSta_TotalIndex m)
{
OperationAllData(m);
return(View(m));
}
public FileResult ExportAllData(VM_BoxSta_TotalIndex m)
{
string _title = "总体数据统计.xls";
OperationAllData(m);
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
ICellStyle cellstyle = book.CreateCellStyle();
cellstyle.VerticalAlignment = VerticalAlignment.Center;
cellstyle.Alignment = HorizontalAlignment.Center;
if (m.grid.Chinese.Count != 0 || m.grid.Math.Count != 0 || m.grid.English.Count != 0)
{
int _engNum = 0;
int _matNum = 0;
int _chiNum = 0;
if (m.grid.English.Count > 0)
{
_engNum = m.grid.English.Count;
}
if (m.grid.Math.Count > 0)
{
_matNum = m.grid.Math.Count;
}
if (m.grid.Chinese.Count > 0)
{
_chiNum = m.grid.Chinese.Count;
}
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("学科");
row1.CreateCell(1).SetCellValue("版本");
Dictionary <int, string> dict = BoxOms.Dict.Course.Get();
for (int i = 0; i < dict.Count; i++)
{
row1.CreateCell(2 + i).SetCellValue(dict[i].ToString());
}
row1.CreateCell(2 + dict.Count).SetCellValue("版本小计");
row1.CreateCell(3 + dict.Count).SetCellValue("学科小计");
if (m.grid.English.Count > 0)
{
for (int i = 0; i < m.grid.English.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet1.CreateRow(i + 1);
if (i == 0)
{
SetCellRangeAddress(sheet1, 1, _engNum, 0, 0);
SetCellRangeAddress(sheet1, 1, _engNum, 3 + dict.Count, 3 + dict.Count);
SetStyle(cellstyle, row2, 0, "英语");
SetStyle(cellstyle, row2, 3 + dict.Count, BoxOms.Web.BLL.BoxStaBLL.BackNumTotal(m.list, 3).ToString());
}
row2.CreateCell(1).SetCellValue(BoxOms.Dict.Edition.GetVal(Convert.ToInt32(m.grid.English[i])));
for (int j = 0; j < dict.Count; j++)
{
row2.CreateCell(j + 2).SetCellValue(BoxOms.Web.BLL.BoxStaBLL.BackNum(m.list, 3, Convert.ToInt32(m.grid.English[i]), j));
}
row2.CreateCell(2 + dict.Count).SetCellValue(BoxOms.Web.BLL.BoxStaBLL.BackNumTotal(m.list, 3, Convert.ToInt32(m.grid.English[i])));
}
}
if (m.grid.Math.Count > 0)
{
for (int i = 0; i < m.grid.Math.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet1.CreateRow(i + 1 + _engNum);
if (i == 0)
{
SetCellRangeAddress(sheet1, _engNum + 1, _engNum + _matNum, 0, 0);
SetCellRangeAddress(sheet1, _engNum + 1, _engNum + _matNum, 3 + dict.Count, 3 + dict.Count);
SetStyle(cellstyle, row2, 0, "数学");
SetStyle(cellstyle, row2, 3 + dict.Count, BoxOms.Web.BLL.BoxStaBLL.BackNumTotal(m.list, 2).ToString());
}
row2.CreateCell(1).SetCellValue(BoxOms.Dict.Edition.GetVal(Convert.ToInt32(m.grid.Math[i])));
for (int j = 0; j < dict.Count; j++)
{
row2.CreateCell(j + 2).SetCellValue(BoxOms.Web.BLL.BoxStaBLL.BackNum(m.list, 2, Convert.ToInt32(m.grid.Math[i]), j));
}
row2.CreateCell(2 + dict.Count).SetCellValue(BoxOms.Web.BLL.BoxStaBLL.BackNumTotal(m.list, 2, Convert.ToInt32(m.grid.Math[i])));
}
}
if (m.grid.Chinese.Count > 0)
{
for (int i = 0; i < m.grid.Chinese.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet1.CreateRow(i + 1 + _engNum + _matNum);
if (i == 0)
{
SetCellRangeAddress(sheet1, _engNum + _matNum + 1, _engNum + _matNum + _chiNum, 0, 0);
SetCellRangeAddress(sheet1, _engNum + _matNum + 1, _engNum + _matNum + _chiNum, 3 + dict.Count, 3 + dict.Count);
SetStyle(cellstyle, row2, 0, "语文");
SetStyle(cellstyle, row2, 3 + dict.Count, BoxOms.Web.BLL.BoxStaBLL.BackNumTotal(m.list, 1).ToString());
}
row2.CreateCell(1).SetCellValue(BoxOms.Dict.Edition.GetVal(Convert.ToInt32(m.grid.Chinese[i])));
for (int j = 0; j < dict.Count; j++)
{
row2.CreateCell(j + 2).SetCellValue(BoxOms.Web.BLL.BoxStaBLL.BackNum(m.list, 1, Convert.ToInt32(m.grid.Chinese[i]), j));
}
row2.CreateCell(2 + dict.Count).SetCellValue(BoxOms.Web.BLL.BoxStaBLL.BackNumTotal(m.list, 1, Convert.ToInt32(m.grid.Chinese[i])));
}
}
}
else
{
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("学科");
row1.CreateCell(1).SetCellValue("版本");
Dictionary <int, string> dict = BoxOms.Dict.Course.Get();
for (int i = 0; i < dict.Count; i++)
{
row1.CreateCell(2 + i).SetCellValue(dict[i].ToString());
}
row1.CreateCell(2 + dict.Count).SetCellValue("版本小计");
row1.CreateCell(3 + dict.Count).SetCellValue("学科小计");
}
//// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", _title));
}
/// <summary>
/// 操作所有数据
/// </summary>
/// <param name="m"></param>
private static void OperationAllData(VM_BoxSta_TotalIndex m)
{
if (m.grid == null)
{
m.grid = new VM_BoxSta_Index_Grid();
}
m.grid.Chinese = new ArrayList();
m.grid.English = new ArrayList();
m.grid.Math = new ArrayList();
//查询出所有的盒子
using (var db = new box_omsEntities())
{
var query = db.box_good.OrderBy(o => o.Code).AsQueryable();
var _li = db.box_resource_statist.OrderBy(_ => _.CreateDate).ToList();
if (m.SDate != null && m.EDate != null)
{
_li = _li.Where(_ => _.CreateDate >= m.SDate && _.CreateDate <= m.EDate).ToList();
}
else
{
m.SDate = null;
m.EDate = null;
}
m.list = query.Select(s => new VM_BoxRunStat_Index_Grid
{
BoxId = s.BoxId,
SchoolName = s.SchoolName,
FirstRunTime = s.FirstRunTime,
UseUserName = s.UseUserName,
State = s.State
}).ToList();
//找出每个学科所对应的版本
if (m.list.Count > 0 && _li.Count > 0)
{
foreach (var item in m.list)
{
var _listE = db.box_subject_edition.Where(_ => _.BoxId == item.BoxId).ToList();
if (_listE.Count == 0)
{
continue;
}
foreach (var it in _listE)
{
if (it.Subject == 1 && !m.grid.Chinese.Contains(it.Edition))
{
m.grid.Chinese.Add(it.Edition);
}
if (it.Subject == 2 && !m.grid.Math.Contains(it.Edition))
{
m.grid.Math.Add(it.Edition);
}
if (it.Subject == 3 && !m.grid.English.Contains(it.Edition))
{
m.grid.English.Add(it.Edition);
}
}
}
}
else
{
m.list = new List <VM_BoxRunStat_Index_Grid>();
}
}
}
