public PartialViewResult PriceProductList(BaoYangPriceSelectModel param, string pageType, int pageIndex, int pageSize)
{
PagerModel pager = new PagerModel(pageIndex, pageSize);
var manager = new BaoYangPriceGuideManager();
var result = new List <BaoYangPriceGuideList>();
var data = manager.SelectBaoYangPriceGuide(param, pager);
if (data.Item1)
{
result = data.Item2;
}
ViewBag.SelectModel = param;
ViewBag.PageType = pageType;
return(PartialView(new ListModel <BaoYangPriceGuideList>()
{
Pager = pager, Source = result
}));
}
public ActionResult ExportExcel(BaoYangPriceSelectModel selectModel, string pageType)
{
PagerModel pager = new PagerModel(1, 100000);
var manager = new BaoYangPriceGuideManager();
var list = manager.SelectBaoYangPriceGuide(selectModel, pager);
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//获取list数据
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row = sheet1.CreateRow(0);
var fileName = $"保养价格指导{ThreadIdentity.Operator.Name.Split('@')[0]}.xls";
var rowNum = 0;
row.CreateCell(rowNum).SetCellValue("品牌");
row.CreateCell(rowNum++).SetCellValue("PID");
row.CreateCell(rowNum++).SetCellValue("二级类目");
row.CreateCell(rowNum++).SetCellValue("三级类目");
row.CreateCell(rowNum++).SetCellValue("产品名称");
row.CreateCell(rowNum++).SetCellValue("总库存");
row.CreateCell(rowNum++).SetCellValue("仓库库存");
row.CreateCell(rowNum++).SetCellValue("工场店库存");
row.CreateCell(rowNum++).SetCellValue("近7天销量");
row.CreateCell(rowNum++).SetCellValue("近30天销量");
row.CreateCell(rowNum++).SetCellValue("周转天数");
if (!pageType.Equals("see"))
{
row.CreateCell(rowNum++).SetCellValue("进货价");
row.CreateCell(rowNum++).SetCellValue("最近一次采购价");
}
if (string.IsNullOrWhiteSpace(pageType))
{
row.CreateCell(rowNum++).SetCellValue("理论指导价");
row.CreateCell(rowNum++).SetCellValue("实际指导价");
}
row.CreateCell(rowNum++).SetCellValue("官网价格");
if (string.IsNullOrWhiteSpace(pageType))
{
row.CreateCell(rowNum++).SetCellValue("活动价");
}
if (!pageType.Equals("see"))
{
row.CreateCell(rowNum++).SetCellValue("途虎毛利率");
row.CreateCell(rowNum++).SetCellValue("途虎毛利额");
}
row.CreateCell(rowNum++).SetCellValue("汽配龙");
if (!pageType.Equals("see"))
{
row.CreateCell(rowNum++).SetCellValue("汽配龙毛利率");
row.CreateCell(rowNum++).SetCellValue("汽配龙毛利额");
row.CreateCell(rowNum++).SetCellValue("工场店毛利率");
row.CreateCell(rowNum++).SetCellValue("工场店毛利额");
}
row.CreateCell(rowNum++).SetCellValue("京东自营");
row.CreateCell(rowNum++).SetCellValue("特维轮天猫");
row.CreateCell(rowNum++).SetCellValue("养车无忧官网");
row.CreateCell(rowNum++).SetCellValue("汽车超人零售");
row.CreateCell(rowNum++).SetCellValue("康众官网");
row.CreateCell(rowNum++).SetCellValue("汽车超人批发");
row.CreateCell(rowNum++).SetCellValue("途虎淘宝");
row.CreateCell(rowNum++).SetCellValue("途虎淘宝2");
row.CreateCell(rowNum++).SetCellValue("途虎天猫1");
row.CreateCell(rowNum++).SetCellValue("途虎天猫2");
row.CreateCell(rowNum++).SetCellValue("途虎天猫3");
row.CreateCell(rowNum++).SetCellValue("途虎天猫4");
row.CreateCell(rowNum++).SetCellValue("途虎京东");
row.CreateCell(rowNum).SetCellValue("途虎京东旗舰");
if (list.Item1 && list.Item2.Any())
{
var i = 0;
foreach (var item in list.Item2)
{
var rowtempNum = 0;
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(++i);
rowtemp.CreateCell(rowtempNum).SetCellValue(item.Brand);
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.PID);
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.SecondType);
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.ThirdType);
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.ProductName);
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.totalstock == null ? "" : item.totalstock.Value.ToString());
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.totalstock == null ? "" : (item.totalstock.Value - item.ShopStock).ToString());
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.ShopStock);
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.num_week == null ? "" : item.num_week.Value.ToString());
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.num_month == null ? "" : item.num_month.Value.ToString());
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.num_month == null || item.totalstock == null || item.num_month <= 0 || item.totalstock <= 0
? "" :
Math.Ceiling((decimal)item.totalstock / item.num_month.Value * 30).ToString(CultureInfo.InvariantCulture));
if (!pageType.Equals("see"))
{
rowtemp.CreateCell(rowtempNum++)
.SetCellValue(item.cost == null ? "" : item.cost.Value.ToString("0.00"));
rowtemp.CreateCell(rowtempNum++)
.SetCellValue(item.PurchasePrice == null ? "" : item.PurchasePrice.Value.ToString("0.00"));
}
if (string.IsNullOrWhiteSpace(pageType))
{
rowtemp.CreateCell(rowtempNum++)
.SetCellValue(item.TheoryGuidePrice == null
? ""
: item.TheoryGuidePrice.Value.ToString("0.00"));
rowtemp.CreateCell(rowtempNum++)
.SetCellValue(item.ActualGuidePrice == null
? ""
: item.ActualGuidePrice.Value.ToString("0.00"));
}
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.Price.ToString("0.00"));
if (string.IsNullOrWhiteSpace(pageType))
{
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.FlashSalePrice != null ? item.FlashSalePrice.Value.ToString("0.00") : "-");
}
if (!pageType.Equals("see"))
{
rowtemp.CreateCell(rowtempNum++)
.SetCellValue(item.cost.GetValueOrDefault(0) > 0 && item.Price > 0
? ((item.Price - item.cost.Value) / item.Price).ToString("0.00%")
: "");
rowtemp.CreateCell(rowtempNum++)
.SetCellValue(item.cost.GetValueOrDefault(0) > 0 && item.Price > 0
? (item.Price - item.cost.Value).ToString("0.00")
: "");
}
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.QPLPrice == null ? "" : item.QPLPrice.Value.ToString("0.00"));
if (!pageType.Equals("see"))
{
rowtemp.CreateCell(rowtempNum++)
.SetCellValue(item.QPLPrice > 0 && item.cost > 0 ? (((decimal)item.QPLPrice - item.cost.Value) / (decimal)item.QPLPrice).ToString("0.00%") : "");
rowtemp.CreateCell(rowtempNum++)
.SetCellValue(item.QPLPrice > 0 && item.cost > 0 ? ((decimal)item.QPLPrice - item.cost.Value).ToString("0.00") : "");
rowtemp.CreateCell(rowtempNum++)
.SetCellValue(item.QPLPrice > 0 && item.Price > 0 ? ((item.Price - (decimal)item.QPLPrice) / item.Price).ToString("0.00%") : "");
rowtemp.CreateCell(rowtempNum++)
.SetCellValue(item.QPLPrice > 0 ? (item.Price - (decimal)item.QPLPrice).ToString("0.00") : "");
}
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.JDSelfPrice == null ? "" : item.JDSelfPrice.Value.ToString("0.00"));
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.TWLTMPrice == null ? "" : item.TWLTMPrice.Value.ToString("0.00"));
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.QccrlPrice == null ? "" : item.QccrlPrice.Value.ToString("0.00"));
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.YcwyPrice == null ? "" : item.YcwyPrice.Value.ToString("0.00"));
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.KzPrice == null ? "" : item.KzPrice.Value.ToString("0.00"));
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.QccrpPrice == null ? "" : item.QccrpPrice.Value.ToString("0.00"));
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.TBPrice == null ? "" : item.TBPrice.Value.ToString("0.00"));
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.TB2Price == null ? "" : item.TB2Price.Value.ToString("0.00"));
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.TM1Price == null ? "" : item.TM1Price.Value.ToString("0.00"));
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.TM2Price == null ? "" : item.TM2Price.Value.ToString("0.00"));
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.TM3Price == null ? "" : item.TM3Price.Value.ToString("0.00"));
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.TM4Price == null ? "" : item.TM4Price.Value.ToString("0.00"));
rowtemp.CreateCell(rowtempNum++).SetCellValue(item.JDPrice == null ? "" : item.JDPrice.Value.ToString("0.00"));
rowtemp.CreateCell(rowtempNum).SetCellValue(item.JDFlagShipPrice == null ? "" : item.JDFlagShipPrice.Value.ToString("0.00"));
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", fileName));
}