public Paras CreateCashFile([FromBody] Paras paras) { string webRootPath = _hostingEnvironment.WebRootPath; var addrUrl = webRootPath + $@"\temp\{paras.FileName}"; if (System.IO.File.Exists(addrUrl)) { System.IO.File.Delete(addrUrl); } string sqlstring = $"Select * from {paras.ViewName} where {paras.Where}"; var dataList = _context.Set <CashBill>().FromSql(sqlstring).ToList(); //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //获取list数据 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.CreateCell(0).SetCellValue("单号"); row1.CreateCell(1).SetCellValue("状态"); row1.CreateCell(2).SetCellValue("开票日期"); row1.CreateCell(3).SetCellValue("购买性质"); row1.CreateCell(4).SetCellValue("购买资质"); row1.CreateCell(5).SetCellValue("客户属性"); row1.CreateCell(6).SetCellValue("付款方式"); row1.CreateCell(7).SetCellValue("保险公司"); row1.CreateCell(8).SetCellValue("优惠金额"); row1.CreateCell(9).SetCellValue("新车款金额"); row1.CreateCell(10).SetCellValue("分期首付款金额"); row1.CreateCell(11).SetCellValue("保险费金额"); row1.CreateCell(12).SetCellValue("购置税金额"); row1.CreateCell(13).SetCellValue("金融服务费金额"); row1.CreateCell(14).SetCellValue("装饰费金额"); row1.CreateCell(15).SetCellValue("交车综合服务费"); row1.CreateCell(16).SetCellValue("安心服务费"); row1.CreateCell(17).SetCellValue("贴心服务费"); row1.CreateCell(18).SetCellValue("玻璃保障服务费"); row1.CreateCell(19).SetCellValue("刷卡费金额"); row1.CreateCell(20).SetCellValue("其他金额"); row1.CreateCell(21).SetCellValue("应收金额总计"); row1.CreateCell(22).SetCellValue("开票金额"); row1.CreateCell(23).SetCellValue("已付订金"); row1.CreateCell(24).SetCellValue("旧车置换金额"); row1.CreateCell(25).SetCellValue("金融分期尾款金额"); row1.CreateCell(26).SetCellValue("其他金额"); row1.CreateCell(27).SetCellValue("实收金额总计"); row1.CreateCell(28).SetCellValue("保险费"); row1.CreateCell(29).SetCellValue("装修费"); row1.CreateCell(30).SetCellValue("佣金"); row1.CreateCell(31).SetCellValue("维修费"); row1.CreateCell(32).SetCellValue("汽油费"); row1.CreateCell(33).SetCellValue("其他"); row1.CreateCell(34).SetCellValue("审核人"); row1.CreateCell(35).SetCellValue("审核结果"); row1.CreateCell(36).SetCellValue("审核意见"); row1.CreateCell(37).SetCellValue("审核时间"); row1.CreateCell(38).SetCellValue("备注"); //将数据逐步写入sheet1各个行 for (int i = 0; i < dataList.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(dataList[i].OrderId); rowtemp.CreateCell(1).SetCellValue(dataList[i].Status); rowtemp.CreateCell(2).SetCellValue(dataList[i].InvoiceDate); rowtemp.CreateCell(3).SetCellValue(dataList[i].BuyType); rowtemp.CreateCell(4).SetCellValue(dataList[i].BuyLicense); rowtemp.CreateCell(5).SetCellValue(dataList[i].CustAttr); rowtemp.CreateCell(6).SetCellValue(dataList[i].PayType); rowtemp.CreateCell(7).SetCellValue(dataList[i].InsureCompany); rowtemp.CreateCell(8).SetCellValue(dataList[i].Discount); rowtemp.CreateCell(9).SetCellValue((double)dataList[i].NewCarFee); rowtemp.CreateCell(10).SetCellValue((double)dataList[i].FirstFee); rowtemp.CreateCell(11).SetCellValue((double)dataList[i].InsureFee); rowtemp.CreateCell(12).SetCellValue((double)dataList[i].BuyTaxFee); rowtemp.CreateCell(13).SetCellValue((double)dataList[i].FinanceSerFee); rowtemp.CreateCell(14).SetCellValue((double)dataList[i].DecorateFee); rowtemp.CreateCell(15).SetCellValue((double)dataList[i].TakeAllFee); rowtemp.CreateCell(16).SetCellValue((double)dataList[i].TakeCareFee); rowtemp.CreateCell(17).SetCellValue((double)dataList[i].IntimateFee); rowtemp.CreateCell(18).SetCellValue((double)dataList[i].GlassSerFee); rowtemp.CreateCell(19).SetCellValue((double)dataList[i].CardCashFee); rowtemp.CreateCell(20).SetCellValue((double)dataList[i].OtherFee); rowtemp.CreateCell(21).SetCellValue((double)dataList[i].ShouldAllFee); rowtemp.CreateCell(22).SetCellValue((double)dataList[i].InvoiceFee); rowtemp.CreateCell(23).SetCellValue((double)dataList[i].Deposit); rowtemp.CreateCell(24).SetCellValue((double)dataList[i].OldChangeFee); rowtemp.CreateCell(25).SetCellValue((double)dataList[i].LastFee); rowtemp.CreateCell(26).SetCellValue((double)dataList[i].OtherFee2); rowtemp.CreateCell(27).SetCellValue((double)dataList[i].RealAllFee); rowtemp.CreateCell(28).SetCellValue((double)dataList[i].BaoxianFee); rowtemp.CreateCell(29).SetCellValue((double)dataList[i].ZhuangxFee); rowtemp.CreateCell(30).SetCellValue((double)dataList[i].Commission); rowtemp.CreateCell(31).SetCellValue((double)dataList[i].MaintainFee); rowtemp.CreateCell(32).SetCellValue((double)dataList[i].GasFee); rowtemp.CreateCell(33).SetCellValue((double)dataList[i].OtherFee3); rowtemp.CreateCell(34).SetCellValue(dataList[i].Auditor); rowtemp.CreateCell(35).SetCellValue(dataList[i].AuditResult); rowtemp.CreateCell(36).SetCellValue(dataList[i].AuditSuggest); rowtemp.CreateCell(37).SetCellValue(dataList[i].AuditTime); rowtemp.CreateCell(38).SetCellValue(dataList[i].Remark); } // 写入到客户端 using (FileStream fs = new FileStream(addrUrl, FileMode.Create)) { book.Write(fs); } book = null; return(paras); }
public Paras CreateSaleFile([FromBody] Paras paras) { string webRootPath = _hostingEnvironment.WebRootPath; var addrUrl = webRootPath + $@"\temp\{paras.FileName}"; if (System.IO.File.Exists(addrUrl)) { System.IO.File.Delete(addrUrl); } string sqlstring = $"Select * from {paras.ViewName} where {paras.Where}"; var dataList = _context.Set <SaleBill>().FromSql(sqlstring).ToList(); //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //获取list数据 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.CreateCell(0).SetCellValue("单号"); row1.CreateCell(1).SetCellValue("日期"); row1.CreateCell(2).SetCellValue("状态"); row1.CreateCell(3).SetCellValue("销售顾问"); row1.CreateCell(4).SetCellValue("DMS号"); row1.CreateCell(5).SetCellValue("客户"); row1.CreateCell(6).SetCellValue("地址"); row1.CreateCell(7).SetCellValue("联系人"); row1.CreateCell(8).SetCellValue("电话"); row1.CreateCell(9).SetCellValue("证件号码"); row1.CreateCell(10).SetCellValue("牌照属性"); row1.CreateCell(11).SetCellValue("证件地址"); row1.CreateCell(12).SetCellValue("客户性质"); row1.CreateCell(13).SetCellValue("车型"); row1.CreateCell(14).SetCellValue("车身颜色"); row1.CreateCell(15).SetCellValue("内饰颜色"); row1.CreateCell(16).SetCellValue("指导价"); row1.CreateCell(17).SetCellValue("新车销售价"); row1.CreateCell(18).SetCellValue("定金"); row1.CreateCell(19).SetCellValue("车架号"); row1.CreateCell(20).SetCellValue("增值预估费"); row1.CreateCell(21).SetCellValue("整单费用合计"); row1.CreateCell(22).SetCellValue("个性化配置"); row1.CreateCell(23).SetCellValue("优惠点"); row1.CreateCell(24).SetCellValue("预计交车日期"); row1.CreateCell(25).SetCellValue("交车地点"); row1.CreateCell(26).SetCellValue("联系电话"); row1.CreateCell(27).SetCellValue("提车方式"); row1.CreateCell(28).SetCellValue("接车人"); row1.CreateCell(29).SetCellValue("接车电话"); row1.CreateCell(30).SetCellValue("付款方式"); row1.CreateCell(31).SetCellValue("预定付款天数"); row1.CreateCell(32).SetCellValue("现车付款天数"); row1.CreateCell(33).SetCellValue("分期签约机构"); row1.CreateCell(34).SetCellValue("首付金额"); row1.CreateCell(35).SetCellValue("分期期数"); row1.CreateCell(36).SetCellValue("首付时效天数"); row1.CreateCell(37).SetCellValue("审核结果"); row1.CreateCell(38).SetCellValue("审核人"); row1.CreateCell(39).SetCellValue("审核意见"); row1.CreateCell(40).SetCellValue("审核时间"); row1.CreateCell(41).SetCellValue("备注"); //将数据逐步写入sheet1各个行 for (int i = 0; i < dataList.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(dataList[i].OrderId); rowtemp.CreateCell(1).SetCellValue(dataList[i].OrderDate); rowtemp.CreateCell(2).SetCellValue(dataList[i].Status); rowtemp.CreateCell(3).SetCellValue(dataList[i].SaleMan); rowtemp.CreateCell(4).SetCellValue(dataList[i].DMSNo); rowtemp.CreateCell(5).SetCellValue(dataList[i].Name); rowtemp.CreateCell(6).SetCellValue(dataList[i].Address); rowtemp.CreateCell(7).SetCellValue(dataList[i].LinkMan); rowtemp.CreateCell(8).SetCellValue(dataList[i].Phone); rowtemp.CreateCell(9).SetCellValue(dataList[i].IdCard); rowtemp.CreateCell(10).SetCellValue(dataList[i].LicenseType); rowtemp.CreateCell(11).SetCellValue(dataList[i].IdAddress); rowtemp.CreateCell(12).SetCellValue(dataList[i].CustType); rowtemp.CreateCell(13).SetCellValue(dataList[i].CarType); rowtemp.CreateCell(14).SetCellValue(dataList[i].CarColor); rowtemp.CreateCell(15).SetCellValue(dataList[i].CarTrim); rowtemp.CreateCell(16).SetCellValue((double)dataList[i].GuidePrice); rowtemp.CreateCell(17).SetCellValue((double)dataList[i].SalePrice); rowtemp.CreateCell(18).SetCellValue((double)dataList[i].Deposit); rowtemp.CreateCell(19).SetCellValue(dataList[i].Vinno); rowtemp.CreateCell(20).SetCellValue((double)dataList[i].PredictFee); rowtemp.CreateCell(21).SetCellValue((double)dataList[i].WholeFee); rowtemp.CreateCell(22).SetCellValue(dataList[i].SelfConfig); rowtemp.CreateCell(23).SetCellValue((double)dataList[i].Discount); rowtemp.CreateCell(24).SetCellValue(dataList[i].PreCarDate); rowtemp.CreateCell(25).SetCellValue(dataList[i].TakeCarSite); rowtemp.CreateCell(26).SetCellValue(dataList[i].TakePhone); rowtemp.CreateCell(27).SetCellValue(dataList[i].PickCarType); rowtemp.CreateCell(28).SetCellValue(dataList[i].PickCarMan); rowtemp.CreateCell(29).SetCellValue(dataList[i].PickCarMobile); rowtemp.CreateCell(30).SetCellValue(dataList[i].PayType); rowtemp.CreateCell(31).SetCellValue(dataList[i].Days2); rowtemp.CreateCell(32).SetCellValue(dataList[i].Days1); rowtemp.CreateCell(33).SetCellValue(dataList[i].FinaceCompany); rowtemp.CreateCell(34).SetCellValue((double)dataList[i].FirstFee); rowtemp.CreateCell(35).SetCellValue(dataList[i].Stages); rowtemp.CreateCell(36).SetCellValue(dataList[i].Days3); rowtemp.CreateCell(37).SetCellValue(dataList[i].AuditResult); rowtemp.CreateCell(38).SetCellValue(dataList[i].Auditor); rowtemp.CreateCell(39).SetCellValue(dataList[i].AuditSuggest); rowtemp.CreateCell(40).SetCellValue(dataList[i].AuditTime); rowtemp.CreateCell(41).SetCellValue(dataList[i].Remark); } // 写入到客户端 using (FileStream fs = new FileStream(addrUrl, FileMode.Create)) { book.Write(fs); } book = null; return(paras); }