public JsonResult LoadStatStorageAndForceFeeGrid(string sidx, string sord, int page, int rows, string startTime, string endTime, string customerName, string warehouse, string isConsigning) { //读取数据 string strErrText; StockSystem stock = new StockSystem(); List<StatStorageAndForceFee> listFee = stock.LoadStatStorageAndForceFeeByConditions(startTime, endTime, customerName, warehouse, isConsigning, LoginAccountId, LoginStaffName, out strErrText); if (listFee == null) { throw new Exception(strErrText); } //提取当前页面数据 int nTotalRows = listFee.Count; int nPageIndex = page; int nPageSize = rows; int nTotalPages = nTotalRows / nPageSize; if (nTotalRows % nPageSize > 0) nTotalPages++; string sortExpression = (sidx ?? "Id") + " " + (sord ?? "ASC"); var data = listFee.OrderBy(sortExpression).Skip((nPageIndex - 1) * nPageSize).Take(nPageSize).ToList(); //生成表格数据 var ret = new { total = nTotalPages, page = nPageIndex, records = nTotalRows, rows = ( from f in data select new { id = f.Id, cell = new string[] { f.Id.ToString(), f.CustomerName, f.CreateTime, f.TotalDeliverEnterWarehouseTunnages > 0 ? f.TotalDeliverEnterWarehouseTunnages.ToString("#0.######") : string.Empty, f.TotalDeliverEnterWarehousePiles > 0 ? f.TotalDeliverEnterWarehousePiles.ToString("#0.######") : string.Empty, f.UnloadingForceFeePrice.ToString(), f.DeliverEnterWarehouseForceFee > 0 ? f.DeliverEnterWarehouseForceFee.ToString() : string.Empty, f.TotalAllocateEnterWarehouseTunnages > 0 ? f.TotalAllocateEnterWarehouseTunnages.ToString("#0.######") : string.Empty, f.TotalDeliverOutWarehouseTunnages > 0 ? f.TotalDeliverOutWarehouseTunnages.ToString("#0.######") : string.Empty, f.TotalDeliverOutWarehousePiles > 0 ? f.TotalDeliverOutWarehousePiles.ToString("#0.######") : string.Empty, f.LoadingForceFeePrice.ToString(), f.DeliverOutWarehouseForceFee > 0 ? f.DeliverOutWarehouseForceFee.ToString() : string.Empty, f.TotalAllocateOutWarehouseTunnages > 0 ? f.TotalAllocateOutWarehouseTunnages.ToString("#0.######") : string.Empty, f.AllocateOutWarehouseForceFee > 0 ? f.AllocateOutWarehouseForceFee.ToString() : string.Empty, f.BalanceTunnages > 0 ? f.BalanceTunnages.ToString("#0.######") : string.Empty, f.BalancePiles > 0 ? f.BalancePiles.ToString("#0.######") : string.Empty, f.StorageFeePrice.ToString(), f.StorageFee > 0 ? f.StorageFee.ToString() : string.Empty } }).ToArray(), userdata = new { CreateTime = InnoSoft.LS.Resources.Labels.Total, TotalDeliverEnterWarehouseTunnages = data.Sum(s => s.TotalDeliverEnterWarehouseTunnages), TotalDeliverEnterWarehousePiles = data.Sum(s => s.TotalDeliverEnterWarehousePiles), DeliverEnterWarehouseForceFee = data.Sum(s => s.DeliverEnterWarehouseForceFee), TotalAllocateEnterWarehouseTunnages = data.Sum(s => s.TotalAllocateEnterWarehouseTunnages), TotalDeliverOutWarehouseTunnages = data.Sum(s => s.TotalDeliverOutWarehouseTunnages), TotalDeliverOutWarehousePiles = data.Sum(s => s.TotalDeliverOutWarehousePiles), DeliverOutWarehouseForceFee = data.Sum(s => s.DeliverOutWarehouseForceFee), TotalAllocateOutWarehouseTunnages = data.Sum(s => s.TotalAllocateOutWarehouseTunnages), AllocateOutWarehouseForceFee = data.Sum(s => s.AllocateOutWarehouseForceFee), BalanceTunnages = data.Count > 0 ? data[data.Count - 1].BalanceTunnages : 0, BalancePiles = data.Count > 0 ? data[data.Count - 1].BalancePiles : 0, StorageFee = data.Sum(s => s.StorageFee) } }; return Json(ret, JsonRequestBehavior.AllowGet); }
public ActionResult ExportStorageAndForceFee() { #region 生成数据源 //读取条件 var request = HttpContext.Request; string strStartTime = request.QueryString["startTime"] ?? string.Empty; string strEndTime = request.QueryString["endTime"] ?? string.Empty; string strCustomerName = request.QueryString["customerName"] ?? string.Empty; string strWarehouse = request.QueryString["warehouse"] ?? string.Empty; string strIsConsigning = request.QueryString["isConsigning"] ?? "false"; //读取数据 string strErrText; StockSystem stock = new StockSystem(); List<StatStorageAndForceFee> listFee = stock.LoadStatStorageAndForceFeeByConditions(strStartTime, strEndTime, strCustomerName, strWarehouse, strIsConsigning, LoginAccountId, LoginStaffName, out strErrText); if (listFee == null) { throw new Exception(strErrText); } //生成结果数据 List<StatStorageAndForceFee> listRet = new List<StatStorageAndForceFee>(); { decimal decBalanceTunnages = 0; decimal decBalancePiles = 0; //按客户分组 var grpCustomerNames = listFee.GroupBy(f => f.CustomerName).OrderBy(f => f.Key); foreach (var grpCustomerName in grpCustomerNames) { List<StatStorageAndForceFee> data = grpCustomerName.ToList<StatStorageAndForceFee>(); listRet.AddRange(data); //生成小计行 StatStorageAndForceFee subtotal = new StatStorageAndForceFee(); subtotal.CustomerName = grpCustomerName.Key; subtotal.CreateTime = InnoSoft.LS.Resources.Labels.Subtotal; subtotal.TotalDeliverEnterWarehouseTunnages = data.Sum(s => s.TotalDeliverEnterWarehouseTunnages); subtotal.TotalDeliverEnterWarehousePiles = data.Sum(s => s.TotalDeliverEnterWarehousePiles); subtotal.DeliverEnterWarehouseForceFee = data.Sum(s => s.DeliverEnterWarehouseForceFee); subtotal.TotalAllocateEnterWarehouseTunnages = data.Sum(s => s.TotalAllocateEnterWarehouseTunnages); subtotal.TotalDeliverOutWarehouseTunnages = data.Sum(s => s.TotalDeliverOutWarehouseTunnages); subtotal.TotalDeliverOutWarehousePiles = data.Sum(s => s.TotalDeliverOutWarehousePiles); subtotal.DeliverOutWarehouseForceFee = data.Sum(s => s.DeliverOutWarehouseForceFee); subtotal.TotalAllocateOutWarehouseTunnages = data.Sum(s => s.TotalAllocateOutWarehouseTunnages); subtotal.AllocateOutWarehouseForceFee = data.Sum(s => s.AllocateOutWarehouseForceFee); subtotal.BalanceTunnages = data.Last().BalanceTunnages; subtotal.BalancePiles = data.Last().BalancePiles; subtotal.StorageFee = data.Sum(s => s.StorageFee); listRet.Add(subtotal); decBalanceTunnages += subtotal.BalanceTunnages; decBalancePiles += subtotal.BalancePiles; } //生成合计行 StatStorageAndForceFee total = new StatStorageAndForceFee(); total.CustomerName = InnoSoft.LS.Resources.Labels.Total; total.TotalDeliverEnterWarehouseTunnages = listFee.Sum(s => s.TotalDeliverEnterWarehouseTunnages); total.TotalDeliverEnterWarehousePiles = listFee.Sum(s => s.TotalDeliverEnterWarehousePiles); total.DeliverEnterWarehouseForceFee = listFee.Sum(s => s.DeliverEnterWarehouseForceFee); total.TotalAllocateEnterWarehouseTunnages = listFee.Sum(s => s.TotalAllocateEnterWarehouseTunnages); total.TotalDeliverOutWarehouseTunnages = listFee.Sum(s => s.TotalDeliverOutWarehouseTunnages); total.TotalDeliverOutWarehousePiles = listFee.Sum(s => s.TotalDeliverOutWarehousePiles); total.DeliverOutWarehouseForceFee = listFee.Sum(s => s.DeliverOutWarehouseForceFee); total.TotalAllocateOutWarehouseTunnages = listFee.Sum(s => s.TotalAllocateOutWarehouseTunnages); total.AllocateOutWarehouseForceFee = listFee.Sum(s => s.AllocateOutWarehouseForceFee); total.BalanceTunnages = decBalanceTunnages; total.BalancePiles = decBalancePiles; total.StorageFee = listFee.Sum(s => s.StorageFee); listRet.Add(total); } #endregion #region 导出Excel #region 创建Excel工作簿 var workbook = new HSSFWorkbook(); var sheet = workbook.CreateSheet(); #endregion #region 设置列宽度 sheet.SetColumnWidth(0, 20 * 256);//客户名称 sheet.SetColumnWidth(1, 12 * 256);//日期 sheet.SetColumnWidth(2, 12 * 256);//总计移仓入库吨数 sheet.SetColumnWidth(3, 12 * 256);//总计移仓入库垛数 sheet.SetColumnWidth(4, 7 * 256);//下力支费价格 sheet.SetColumnWidth(5, 10 * 256);//移仓入库力支费 sheet.SetColumnWidth(6, 12 * 256);//总计划拨入库吨数 sheet.SetColumnWidth(7, 12 * 256);//总计发货出库吨数 sheet.SetColumnWidth(8, 12 * 256);//总计发货出库垛数 sheet.SetColumnWidth(9, 7 * 256);//上力支费价格 sheet.SetColumnWidth(10, 10 * 256);//发货出库力支费 sheet.SetColumnWidth(11, 12 * 256);//总计划拨出库吨数 sheet.SetColumnWidth(12, 10 * 256);//划拨出库力支费 sheet.SetColumnWidth(13, 12 * 256);//结存吨数 sheet.SetColumnWidth(14, 12 * 256);//结存垛数 sheet.SetColumnWidth(15, 7 * 256);//仓储费价格 sheet.SetColumnWidth(16, 10 * 256);//仓储费 #endregion #region 设置单元格样式 ICellStyle styleLeft = workbook.CreateCellStyle(); styleLeft.Alignment = HorizontalAlignment.LEFT; styleLeft.VerticalAlignment = VerticalAlignment.CENTER; styleLeft.BorderBottom = CellBorderType.THIN; styleLeft.BorderLeft = CellBorderType.THIN; styleLeft.BorderRight = CellBorderType.THIN; styleLeft.BorderTop = CellBorderType.THIN; styleLeft.WrapText = true; ICellStyle styleCenter = workbook.CreateCellStyle(); styleCenter.Alignment = HorizontalAlignment.CENTER; styleCenter.VerticalAlignment = VerticalAlignment.CENTER; styleCenter.BorderBottom = CellBorderType.THIN; styleCenter.BorderLeft = CellBorderType.THIN; styleCenter.BorderRight = CellBorderType.THIN; styleCenter.BorderTop = CellBorderType.THIN; styleCenter.WrapText = true; ICellStyle styleRight = workbook.CreateCellStyle(); styleRight.Alignment = HorizontalAlignment.RIGHT; styleRight.VerticalAlignment = VerticalAlignment.CENTER; styleRight.BorderBottom = CellBorderType.THIN; styleRight.BorderLeft = CellBorderType.THIN; styleRight.BorderRight = CellBorderType.THIN; styleRight.BorderTop = CellBorderType.THIN; styleRight.WrapText = true; #endregion #region 创建表头 //表头 var rowHeader = sheet.CreateRow(0); rowHeader.HeightInPoints = 40; rowHeader.CreateCell(0).SetCellValue(InnoSoft.LS.Resources.Labels.CustomerName);//客户名称 rowHeader.GetCell(0).CellStyle = styleCenter; rowHeader.CreateCell(1).SetCellValue(InnoSoft.LS.Resources.Labels.Date);//日期 rowHeader.GetCell(1).CellStyle = styleCenter; rowHeader.CreateCell(2).SetCellValue(InnoSoft.LS.Resources.Labels.TotalDeliverEnterWarehouse + InnoSoft.LS.Resources.Labels.Tunnages);//总计移仓入库吨数 rowHeader.GetCell(2).CellStyle = styleCenter; rowHeader.CreateCell(3).SetCellValue(InnoSoft.LS.Resources.Labels.TotalDeliverEnterWarehouse + InnoSoft.LS.Resources.Labels.Piles);//总计移仓入库垛数 rowHeader.GetCell(3).CellStyle = styleCenter; rowHeader.CreateCell(4).SetCellValue(InnoSoft.LS.Resources.Labels.UnloadingForceFeePrice);//下力支费价格 rowHeader.GetCell(4).CellStyle = styleCenter; rowHeader.CreateCell(5).SetCellValue(InnoSoft.LS.Resources.Labels.DeliverEnterWarehouseForceFee);//移仓入库力支费 rowHeader.GetCell(5).CellStyle = styleCenter; rowHeader.CreateCell(6).SetCellValue(InnoSoft.LS.Resources.Labels.TotalAllocateEnterWarehouseTunnages);//总计划拨入库吨数 rowHeader.GetCell(6).CellStyle = styleCenter; rowHeader.CreateCell(7).SetCellValue(InnoSoft.LS.Resources.Labels.TotalDeliverOutWarehouse + InnoSoft.LS.Resources.Labels.Tunnages);//总计发货出库吨数 rowHeader.GetCell(7).CellStyle = styleCenter; rowHeader.CreateCell(8).SetCellValue(InnoSoft.LS.Resources.Labels.TotalDeliverOutWarehouse + InnoSoft.LS.Resources.Labels.Piles);//总计发货出库垛数 rowHeader.GetCell(8).CellStyle = styleCenter; rowHeader.CreateCell(9).SetCellValue(InnoSoft.LS.Resources.Labels.LoadingForceFeePrice);//上力支费价格 rowHeader.GetCell(9).CellStyle = styleCenter; rowHeader.CreateCell(10).SetCellValue(InnoSoft.LS.Resources.Labels.DeliverOutWarehouseForceFee);//发货出库力支费 rowHeader.GetCell(10).CellStyle = styleCenter; rowHeader.CreateCell(11).SetCellValue(InnoSoft.LS.Resources.Labels.TotalAllocateOutWarehouseTunnages);//总计划拨出库吨数 rowHeader.GetCell(11).CellStyle = styleCenter; rowHeader.CreateCell(12).SetCellValue(InnoSoft.LS.Resources.Labels.AllocateOutWarehouseForceFee);//划拨出库力支费 rowHeader.GetCell(12).CellStyle = styleCenter; rowHeader.CreateCell(13).SetCellValue(InnoSoft.LS.Resources.Labels.BalanceTunnages);//结存吨数 rowHeader.GetCell(13).CellStyle = styleCenter; rowHeader.CreateCell(14).SetCellValue(InnoSoft.LS.Resources.Labels.BalancePiles);//结存垛数 rowHeader.GetCell(14).CellStyle = styleCenter; rowHeader.CreateCell(15).SetCellValue(InnoSoft.LS.Resources.Labels.StorageFeePrice);//仓储费价格 rowHeader.GetCell(15).CellStyle = styleCenter; rowHeader.CreateCell(16).SetCellValue(InnoSoft.LS.Resources.Labels.StorageFee);//仓储费 rowHeader.GetCell(16).CellStyle = styleCenter; #endregion #region 创建表体 int iRow = 1; foreach (var ret in listRet) { var rowBody = sheet.CreateRow(iRow++); rowBody.HeightInPoints = 20; rowBody.CreateCell(0).SetCellValue(ret.CustomerName);//客户名称 rowBody.GetCell(0).CellStyle = styleLeft; rowBody.CreateCell(1).SetCellValue(ret.CreateTime);//日期 rowBody.GetCell(1).CellStyle = styleCenter; rowBody.CreateCell(2).SetCellValue(ret.TotalDeliverEnterWarehouseTunnages > 0 ? ret.TotalDeliverEnterWarehouseTunnages.ToString("#0.######") : string.Empty);//总计移仓入库吨数 rowBody.GetCell(2).CellStyle = styleCenter; rowBody.CreateCell(3).SetCellValue(ret.TotalDeliverEnterWarehousePiles > 0 ? ret.TotalDeliverEnterWarehousePiles.ToString("#0.######") : string.Empty);//总计移仓入库垛数 rowBody.GetCell(3).CellStyle = styleCenter; rowBody.CreateCell(4).SetCellValue(ret.UnloadingForceFeePrice > 0 ? ret.UnloadingForceFeePrice.ToString("N") : string.Empty);//下力支费价格 rowBody.GetCell(4).CellStyle = styleRight; rowBody.CreateCell(5).SetCellValue(ret.DeliverEnterWarehouseForceFee > 0 ? ret.DeliverEnterWarehouseForceFee.ToString("N") : string.Empty);//移仓入库力支费 rowBody.GetCell(5).CellStyle = styleRight; rowBody.CreateCell(6).SetCellValue(ret.TotalAllocateEnterWarehouseTunnages > 0 ? ret.TotalAllocateEnterWarehouseTunnages.ToString("#0.######") : string.Empty);//总计划拨入库吨数 rowBody.GetCell(6).CellStyle = styleCenter; rowBody.CreateCell(7).SetCellValue(ret.TotalDeliverOutWarehouseTunnages > 0 ? ret.TotalDeliverOutWarehouseTunnages.ToString("#0.######") : string.Empty);//总计发货出库吨数 rowBody.GetCell(7).CellStyle = styleCenter; rowBody.CreateCell(8).SetCellValue(ret.TotalDeliverOutWarehousePiles > 0 ? ret.TotalDeliverOutWarehousePiles.ToString("#0.######") : string.Empty);//总计发货出库垛数 rowBody.GetCell(8).CellStyle = styleCenter; rowBody.CreateCell(9).SetCellValue(ret.LoadingForceFeePrice > 0 ? ret.LoadingForceFeePrice.ToString("N") : string.Empty);//上力支费价格 rowBody.GetCell(9).CellStyle = styleRight; rowBody.CreateCell(10).SetCellValue(ret.DeliverOutWarehouseForceFee > 0 ? ret.DeliverOutWarehouseForceFee.ToString("N") : string.Empty);//发货出库力支费 rowBody.GetCell(10).CellStyle = styleRight; rowBody.CreateCell(11).SetCellValue(ret.TotalAllocateOutWarehouseTunnages > 0 ? ret.TotalAllocateOutWarehouseTunnages.ToString("#0.######") : string.Empty);//总计划拨出库吨数 rowBody.GetCell(11).CellStyle = styleCenter; rowBody.CreateCell(12).SetCellValue(ret.AllocateOutWarehouseForceFee > 0 ? ret.AllocateOutWarehouseForceFee.ToString("N") : string.Empty);//划拨出库力支费 rowBody.GetCell(12).CellStyle = styleRight; rowBody.CreateCell(13).SetCellValue(ret.BalanceTunnages > 0 ? ret.BalanceTunnages.ToString("#0.######") : string.Empty);//结存吨数 rowBody.GetCell(13).CellStyle = styleCenter; rowBody.CreateCell(14).SetCellValue(ret.BalancePiles > 0 ? ret.BalancePiles.ToString("#0.######") : string.Empty);//结存垛数 rowBody.GetCell(14).CellStyle = styleCenter; rowBody.CreateCell(15).SetCellValue(ret.StorageFeePrice > 0 ? ret.StorageFeePrice.ToString("N") : string.Empty);//仓储费价格 rowBody.GetCell(15).CellStyle = styleRight; rowBody.CreateCell(16).SetCellValue(ret.StorageFee > 0 ? ret.StorageFee.ToString("N") : string.Empty);//仓储费 rowBody.GetCell(16).CellStyle = styleRight; } #endregion #region 输出Excel文件 MemoryStream output = new MemoryStream(); workbook.Write(output); return File(output.ToArray(), "application/vnd.ms-excel", "StorageAndForceFee.xls"); #endregion #endregion }