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