private int InsertZhiChis(MSExcelUtil msExcelUtil, Worksheet sheet, List <QuotationDto> lst, int startIndex, string supplierName) { int index = 1; foreach (ZhiChi01Dto dto in lst) { msExcelUtil.SetCellValue(sheet, 1, startIndex, index); msExcelUtil.SetCellValue(sheet, 2, startIndex, supplierName); msExcelUtil.SetCellValue(sheet, 3, startIndex, dto.Province); msExcelUtil.SetCellValue(sheet, 4, startIndex, dto.City); msExcelUtil.SetCellValue(sheet, 5, startIndex, dto.fenlei); msExcelUtil.SetCellValue(sheet, 6, startIndex, dto.leixingpinpai); msExcelUtil.SetCellValue(sheet, 9, startIndex, dto.shuliang); if (index < lst.Count) { msExcelUtil.CopyRow(sheet, startIndex); startIndex++; index++; msExcelUtil.AddRow(sheet, startIndex); } else { msExcelUtil.DeleteRow(sheet, startIndex + 1); } } return(startIndex); }
public void ExportBankInfo(string path, List <SupplierDto> list) { MSExcelUtil msExcelUtil = new MSExcelUtil(); Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path); Worksheet sheet = workbook.ActiveSheet; var startIndex = 2; foreach (SupplierDto dto in list) { int colIndex = 1; msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, startIndex - 1); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ModifyDateTime); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.SupplierCode); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.SupplierType); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.SupplierName); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.CooperationState); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.AccountBankFullName); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.AccountName); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.AccountBankNo); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.VATRate); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ServiceTrade); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.RecommendDepartment); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.CooperationState); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Remark); startIndex++; msExcelUtil.AddRow(sheet, startIndex); } workbook.Save(); msExcelUtil.dispose(); }
public void ExportZhuijia2(string path, List <Juesuantongjibiao2_ShoudongDto> lst) { MSExcelUtil msExcelUtil = new MSExcelUtil(); Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path); Worksheet sheet = workbook.ActiveSheet; var rowIndex = 2; int index = 1; foreach (Juesuantongjibiao2_ShoudongDto dto in lst) { msExcelUtil.SetCellValue(sheet, 1, rowIndex, dto.FeeContent); msExcelUtil.SetCellValue(sheet, 2, rowIndex, dto.FeeContentSumAmt); msExcelUtil.SetCellValue(sheet, 3, rowIndex, dto.SettleCount); msExcelUtil.SetCellValue(sheet, 4, rowIndex, dto.FeeContent_Avg); if (index < lst.Count) { msExcelUtil.CopyRow(sheet, rowIndex); //msExcelUtil.SetCellValue(sheet, 4, rowIndex, dto.Execute_Avg); rowIndex++; index++; msExcelUtil.AddRow(sheet, rowIndex); } else { msExcelUtil.DeleteRow(sheet, rowIndex + 1); } } workbook.Save(); msExcelUtil.dispose(); }
public void ExportQita2(string path, List <Jiagetongjibiao_Qita2_Dto> lst, DateTime startDate, DateTime endDate) { MSExcelUtil msExcelUtil = new MSExcelUtil(); Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path); Worksheet sheet = workbook.ActiveSheet; //统计周期 msExcelUtil.SetCellValue(sheet, 2, 1, string.Format("{0} 至 {1}", startDate.ToString("yyyy年MM月dd日"), endDate.ToString("yyyy年MM月dd日"))); int startIndex = 3; int index = 1; foreach (Jiagetongjibiao_Qita2_Dto dto in lst) { int colIndex = 1; msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ModelType); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.PurchaseType); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.PurchaseMode); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.SupplyService); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.CostStruct); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ItemName); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.danjia_AVG); if (index < lst.Count) { msExcelUtil.CopyRow(sheet, startIndex); startIndex++; index++; msExcelUtil.AddRow(sheet, startIndex); } } workbook.Save(); msExcelUtil.dispose(); }
private int InsertZhiChis(MSExcelUtil msExcelUtil, Worksheet sheet, List <QuotationDto> lst, int startIndex) { int index = 1; foreach (ZhiChi01Dto dto in lst) { msExcelUtil.SetCellValue(sheet, 1, startIndex, index); msExcelUtil.SetCellValue(sheet, 2, startIndex, dto.SupplierName); msExcelUtil.SetCellValue(sheet, 3, startIndex, dto.Province); msExcelUtil.SetCellValue(sheet, 4, startIndex, dto.City); msExcelUtil.SetCellValue(sheet, 5, startIndex, dto.fenlei); msExcelUtil.SetCellValue(sheet, 6, startIndex, dto.leixingpinpai); msExcelUtil.SetCellValue(sheet, 7, startIndex, dto.danjia); msExcelUtil.SetCellValue(sheet, 9, startIndex, dto.shuliang); double count = string.IsNullOrEmpty(dto.shuliang) ? 0 : double.Parse(dto.shuliang); string xiaoji = dto.danjia.HasValue ? (dto.danjia.Value * new decimal(count)).ToString("##.00") : ""; msExcelUtil.SetCellValue(sheet, 10, startIndex, xiaoji); if (index < lst.Count) { msExcelUtil.CopyRow(sheet, startIndex); startIndex++; index++; msExcelUtil.AddRow(sheet, startIndex); } else { msExcelUtil.DeleteRow(sheet, startIndex + 1); } } return(startIndex); }
public void ExportAccruedCharges(string path, List <AccruedChargesDto> list) { MSExcelUtil msExcelUtil = new MSExcelUtil(); Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path); Worksheet sheet = workbook.ActiveSheet; var rowIndex = 2; foreach (AccruedChargesDto dto in list) { int colIndex = 1; msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, rowIndex - 1); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.CostType); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.ProjectName); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.ProjectShortName); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.ProjectCode); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.SupplierName); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.ExecuteCycle); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.AccruedChargesAmt); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.PayMonth); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.FlowInvoceAmt); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.SourceChk); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.DepartmentCode); rowIndex++; msExcelUtil.AddRow(sheet, rowIndex); } workbook.Save(); msExcelUtil.dispose(); }
public void ExportYewucaigoubiao2(string path, DateTime startDate, DateTime endDate, List <Juesuantongjibiao2Dto> lst) { MSExcelUtil msExcelUtil = new MSExcelUtil(); Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path); Worksheet sheet = workbook.ActiveSheet; //统计周期 msExcelUtil.SetCellValue(sheet, 2, 1, string.Format("{0} 至 {1}", startDate.ToString("yyyy年MM月dd日"), endDate.ToString("yyyy年MM月dd日"))); //动态列头 var rowIndex = 3; int index = 1; foreach (Juesuantongjibiao2Dto dto in lst) { msExcelUtil.SetCellValue(sheet, 1, rowIndex, dto.ExecuteMode); msExcelUtil.SetCellValue(sheet, 2, rowIndex, dto.ExecuteModeSumAmt); msExcelUtil.SetCellValue(sheet, 3, rowIndex, dto.SettleCount); if (index < lst.Count) { msExcelUtil.CopyRow(sheet, rowIndex); //msExcelUtil.SetCellValue(sheet, 4, rowIndex, dto.Execute_Avg); rowIndex++; index++; msExcelUtil.AddRow(sheet, rowIndex); } else { msExcelUtil.DeleteRow(sheet, rowIndex + 1); } } workbook.Save(); msExcelUtil.dispose(); }
private int InsertChezhans(MSExcelUtil msExcelUtil, Worksheet sheet, List <QuotationDto> lst, int startIndex, string supplierName) { int index = 1; foreach (Quotation_CheZhanDto dto in lst) { msExcelUtil.SetCellValue(sheet, 1, startIndex, index); msExcelUtil.SetCellValue(sheet, 2, startIndex, supplierName); msExcelUtil.SetCellValue(sheet, 3, startIndex, dto.Province); msExcelUtil.SetCellValue(sheet, 4, startIndex, dto.City); msExcelUtil.SetCellValue(sheet, 5, startIndex, dto.ExcuteType); msExcelUtil.SetCellValue(sheet, 6, startIndex, dto.Responsibilites); msExcelUtil.SetCellValue(sheet, 7, startIndex, dto.UserType); msExcelUtil.SetCellValue(sheet, 8, startIndex, dto.ExistingOrPotential); msExcelUtil.SetCellValue(sheet, 9, startIndex, dto.CustomerType); msExcelUtil.SetCellValue(sheet, 12, startIndex, dto.Count); if (index < lst.Count) { msExcelUtil.CopyRow(sheet, startIndex); startIndex++; index++; msExcelUtil.AddRow(sheet, startIndex); } else { msExcelUtil.DeleteRow(sheet, startIndex + 1); } } return(startIndex); }
public void ExportFuhe(string path, List <Object> lst) { MSExcelUtil msExcelUtil = new MSExcelUtil(); Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path); Worksheet sheet = workbook.ActiveSheet; int startIndex = 2; int index = 1; foreach (Huizongbiao_FuheDto dto in lst) { int colIndex = 1; msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, index); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.SupplierName); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ProjectName); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ProjectShortName); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.xiangmujingli); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.zhixingshengfen); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.zhixingchengshi); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ExecuteCycle); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ProjectType); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.fuheyaoqiu); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.abjuan); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.jishuwenjuanfuhe); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.wenjuanzuodaluyinfuhe); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.fuheyaoqiu1); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.fuheneirong); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.fuheshijian); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.dianhuazixun); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.dianhuhuifang); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.yanbenbianji); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.bianmaleixing); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.timushuliang); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.zishushuliang); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.dianhuyuyueyaoqiu); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.wenjuanbiancheng); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.yangbenluru); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.luruguanli); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.fuzhuyongpin); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.hesuandanwei); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.danjia); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.shuliang); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.heji); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Remark); if (index < lst.Count) { msExcelUtil.CopyRow(sheet, startIndex); startIndex++; index++; msExcelUtil.AddRow(sheet, startIndex); } } workbook.Save(); msExcelUtil.dispose(); }
public void ExportBiancheng(string path, List <Jiagetongjibiao_HeaderDto> lst_Head, List <Jiagetongjibiao_Left_Biancheng_Dto> lst_Left, List <Jiagetongjibiao_Data_Biancheng_Dto> lst_Data, DateTime startDate, DateTime endDate) { MSExcelUtil msExcelUtil = new MSExcelUtil(); Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path); Worksheet sheet = workbook.ActiveSheet; //统计周期 msExcelUtil.SetCellValue(sheet, 2, 1, string.Format("{0} 至 {1}", startDate.ToString("yyyy年MM月dd日"), endDate.ToString("yyyy年MM月dd日"))); //动态列头 var dStartColIndex = 9; foreach (Jiagetongjibiao_HeaderDto dto in lst_Head) { msExcelUtil.CopyColumn(sheet, dStartColIndex); msExcelUtil.AddColumn(sheet, dStartColIndex + 1); msExcelUtil.SetCellValue(sheet, dStartColIndex++, 2, dto.zhixingchengshi); } var rowIndex = 3; foreach (Jiagetongjibiao_Left_Biancheng_Dto dto in lst_Left) { msExcelUtil.SetCellValue(sheet, 1, rowIndex, rowIndex - 1); msExcelUtil.SetCellValue(sheet, 2, rowIndex, dto.ExcuteMode); msExcelUtil.SetCellValue(sheet, 3, rowIndex, dto.gongzuofenlei); msExcelUtil.SetCellValue(sheet, 4, rowIndex, dto.gongzuoneirong); msExcelUtil.SetCellValue(sheet, 5, rowIndex, dto.leixingpinpai); msExcelUtil.SetCellValue(sheet, 6, rowIndex, dto.guigexinghao); msExcelUtil.SetCellValue(sheet, 7, rowIndex, dto.zhiliangbiaozhun); msExcelUtil.SetCellValue(sheet, 8, rowIndex, dto.hesuandanwei); rowIndex++; msExcelUtil.AddRow(sheet, rowIndex); } msExcelUtil.DeleteRow(sheet, rowIndex); foreach (Jiagetongjibiao_Data_Biancheng_Dto dto in lst_Data) { dStartColIndex = 9; var dataRowIndex = 3; int dcolIndex = lst_Head.FindIndex(x => x.zhixingchengshi == dto.zhixingchengshi); int drowIndex = lst_Left.FindIndex(x => x.ExcuteMode == dto.ExcuteMode && x.gongzuofenlei == dto.gongzuofenlei && x.gongzuoneirong == dto.gongzuoneirong && x.leixingpinpai == dto.leixingpinpai && x.guigexinghao == dto.guigexinghao && x.zhiliangbiaozhun == dto.zhiliangbiaozhun && x.hesuandanwei == dto.hesuandanwei); dStartColIndex += dcolIndex; dataRowIndex += drowIndex; msExcelUtil.SetCellValue(sheet, dStartColIndex, dataRowIndex, dto.danjia_AVG); } workbook.Save(); msExcelUtil.dispose(); }
public void ExportSupplier(string path, List <SupplierDto> list) { MSExcelUtil msExcelUtil = new MSExcelUtil(); Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path); Worksheet sheet = workbook.ActiveSheet; var rowIndex = 3; foreach (SupplierDto dto in list) { int colIndex = 1; msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.SupplierCode); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.Province); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.City); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.ServiceTrade); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.SupplierType); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.PurchaseType); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.CooperationState); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.SupplierType1); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.NotServiceType); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.PayCycle); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.RecommendDepartment); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.SupplierName); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.SupplierShortName); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.ProvideService); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.BranchCompanyAddress); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.CorporateName); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.CorporateTel); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.CorporateFixTel); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.CorporateEmail); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.BussinessMainName); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.BussinessMainTel); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.BussinessMainFixTel); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.BussinessMainEmail); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.BussinessSecondName); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.BussinessSecondTel); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.BussinessSecondFixTel); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.BussinessSecondEmail); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.CompanyFaxCode); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.Address); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.PostCode); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.Remark); rowIndex++; msExcelUtil.AddRow(sheet, rowIndex); } workbook.Save(); msExcelUtil.dispose(); }
public void ExportQita2(string path, List <Object> lst) { MSExcelUtil msExcelUtil = new MSExcelUtil(); Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path); Worksheet sheet = workbook.ActiveSheet; int startIndex = 2; int index = 1; foreach (Huizongbiao_Qita2Dto dto in lst) { int colIndex = 1; msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Year); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ModelType); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ServiceRegion); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.DepartmentCode); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.CostDepartment); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.SupplierName); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ProjectName); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ProjectCode); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.PurchaseType); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.PurchaseMode); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.SupplyService); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.CostStruct); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ItemName); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Brand); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Specification); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Model); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Configuration); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.AfterSaleContent); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.AfterSalePeriod); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Warranty); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.OrderDate); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.DeliveryDate.HasValue ? dto.DeliveryDate.Value.ToString("yyyy-MM-dd"): ""); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.AcceptanceDate.HasValue ? dto.AcceptanceDate.Value.ToString("yyyy-MM-dd") : ""); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.danjia); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.shuliang); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.heji); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Remark); if (index < lst.Count) { msExcelUtil.CopyRow(sheet, startIndex); startIndex++; index++; msExcelUtil.AddRow(sheet, startIndex); } } workbook.Save(); msExcelUtil.dispose(); }
public void ExportQita1(string path, List <Object> lst) { MSExcelUtil msExcelUtil = new MSExcelUtil(); Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path); Worksheet sheet = workbook.ActiveSheet; int startIndex = 2; int index = 1; foreach (Huizongbiao_Qita1Dto dto in lst) { int colIndex = 1; msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Year); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ModelType); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ServiceRegion); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.DepartmentCode); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.CostDepartment); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.SupplierName); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ProjectName); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ProjectCode); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.PurchaseType); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.PurchaseMode); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.SupplyService); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ItemName); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Brand); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Specification); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Model); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Configuration); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Color); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Material); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.LogisticsRequirements); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.PackagingRequirements); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.InspectionCycle); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.AfterSalePeriod); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Warranty); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.danjia); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.shuliang); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.heji); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Remark); if (index < lst.Count) { msExcelUtil.CopyRow(sheet, startIndex); startIndex++; index++; msExcelUtil.AddRow(sheet, startIndex); } } workbook.Save(); msExcelUtil.dispose(); }
public void ExportCapitalRequirement(string path, DateTime startDate, DateTime endDate, List <HeaderDto> HeaderDtoList, List <LeftDto> LeftDtoList, List <DataDto> DataDtoList) { MSExcelUtil msExcelUtil = new MSExcelUtil(); Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path); Worksheet sheet = workbook.ActiveSheet; //统计周期 msExcelUtil.SetCellValue(sheet, 2, 1, string.Format("{0} 至 {1}", startDate.ToString("yyyy年MM月dd日"), endDate.ToString("yyyy年MM月dd日"))); //动态列头 var dStartColIndex = 6; foreach (HeaderDto dto in HeaderDtoList) { msExcelUtil.CopyColumn(sheet, dStartColIndex); msExcelUtil.AddColumn(sheet, dStartColIndex + 1); msExcelUtil.SetCellValue(sheet, dStartColIndex, 2, dto.Display); dStartColIndex++; } int index = 1; var rowIndex = 3; foreach (LeftDto dto in LeftDtoList) { msExcelUtil.SetCellValue(sheet, 1, rowIndex, dto.DepartmentCode); msExcelUtil.SetCellValue(sheet, 2, rowIndex, dto.ExpendType); msExcelUtil.SetCellValue(sheet, 3, rowIndex, dto.ProjectShortName); msExcelUtil.SetCellValue(sheet, 4, rowIndex, dto.ExpendPattern); msExcelUtil.SetCellValue(sheet, 5, rowIndex, dto.PaySumAmt); var dataColumnIndex = 6; foreach (DataDto dataDto in DataDtoList) { if (dataDto.ProjectId == dto.ProjectId) { msExcelUtil.SetCellValue(sheet, dataColumnIndex++, rowIndex, dataDto.SumAmt); } } if (index < LeftDtoList.Count) { msExcelUtil.CopyRow(sheet, rowIndex); rowIndex++; index++; msExcelUtil.AddRow(sheet, rowIndex); } } msExcelUtil.DeleteRow(sheet, rowIndex + 1); workbook.Save(); msExcelUtil.dispose(); }
public void ExportZhixing(string path, List <QuotationDto> lst, ProjectDto projectDto) { MSExcelUtil msExcelUtil = new MSExcelUtil(); Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path); Worksheet sheet = workbook.ActiveSheet; msExcelUtil.SetCellValue(sheet, 2, 1, projectDto.ProjectShortName); int startIndex = 0; startIndex += 3; int startRow = startIndex; int index = 1; foreach (Quotation_ZhiXingDto dto in lst) { msExcelUtil.SetCellValue(sheet, 1, startIndex, index); msExcelUtil.SetCellValue(sheet, 2, startIndex, dto.SupplierName); msExcelUtil.SetCellValue(sheet, 3, startIndex, dto.Province); msExcelUtil.SetCellValue(sheet, 4, startIndex, dto.City); msExcelUtil.SetCellValue(sheet, 5, startIndex, dto.IntoShopType); msExcelUtil.SetCellValue(sheet, 6, startIndex, dto.ExcuteType); msExcelUtil.SetCellValue(sheet, 7, startIndex, dto.UserType); msExcelUtil.SetCellValue(sheet, 8, startIndex, dto.ExistingOrPotential); msExcelUtil.SetCellValue(sheet, 9, startIndex, dto.CustomerType); msExcelUtil.SetCellValue(sheet, 11, startIndex, dto.UnitPrice); msExcelUtil.SetCellValue(sheet, 12, startIndex, dto.Count); double count = string.IsNullOrEmpty(dto.Count) ? 0 : double.Parse(dto.Count); string xiaoji = dto.UnitPrice.HasValue ? (dto.UnitPrice.Value * new decimal(count)).ToString("##.00") : ""; // string xiaoji = dto.UnitPrice.HasValue ? (dto.UnitPrice.Value * dto.Count.Value).ToString("##.00") : ""; msExcelUtil.SetCellValue(sheet, 13, startIndex, xiaoji); msExcelUtil.SetCellValue(sheet, 14, startIndex, dto.Remark); if (index < lst.Count) { msExcelUtil.CopyRow(sheet, startIndex); startIndex++; index++; msExcelUtil.AddRow(sheet, startIndex); } else { msExcelUtil.DeleteRow(sheet, startIndex + 1); } } workbook.Save(); msExcelUtil.dispose(); }
public void ExportFuhe(string path, List <QuotationDto> lst, ProjectDto projectDto) { MSExcelUtil msExcelUtil = new MSExcelUtil(); Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path); Worksheet sheet = workbook.ActiveSheet; msExcelUtil.SetCellValue(sheet, 2, 1, projectDto.ProjectShortName); int startIndex = 0; startIndex += 3; int index = 1; foreach (Quotation_FuHeDto dto in lst) { msExcelUtil.SetCellValue(sheet, 1, startIndex, index); msExcelUtil.SetCellValue(sheet, 2, startIndex, dto.SupplierName); msExcelUtil.SetCellValue(sheet, 3, startIndex, dto.Province); msExcelUtil.SetCellValue(sheet, 4, startIndex, dto.City); msExcelUtil.SetCellValue(sheet, 5, startIndex, dto.fuheyaoqiu); msExcelUtil.SetCellValue(sheet, 6, startIndex, dto.dianhuazixun); msExcelUtil.SetCellValue(sheet, 7, startIndex, dto.dianhuahuifang); msExcelUtil.SetCellValue(sheet, 8, startIndex, dto.yanbenbianji); msExcelUtil.SetCellValue(sheet, 9, startIndex, dto.fuzhuyongpin); msExcelUtil.SetCellValue(sheet, 10, startIndex, dto.bianmaleixing); msExcelUtil.SetCellValue(sheet, 11, startIndex, dto.wenjuanbiancheng); msExcelUtil.SetCellValue(sheet, 13, startIndex, dto.danjia); msExcelUtil.SetCellValue(sheet, 14, startIndex, dto.shuliang); double count = string.IsNullOrEmpty(dto.shuliang) ? 0 : double.Parse(dto.shuliang); string xiaoji = dto.danjia.HasValue ? (dto.danjia.Value * new decimal(count)).ToString("##.00") : ""; msExcelUtil.SetCellValue(sheet, 15, startIndex, xiaoji); msExcelUtil.SetCellValue(sheet, 16, startIndex, dto.beizhu); if (index < lst.Count) { msExcelUtil.CopyRow(sheet, startIndex); startIndex++; index++; msExcelUtil.AddRow(sheet, startIndex); } else { msExcelUtil.DeleteRow(sheet, startIndex + 1); } } workbook.Save(); msExcelUtil.dispose(); }
public void ExportWuxingshangpincaigou(string path, List <QuotationDto> lst, ProjectDto projectDto) { MSExcelUtil msExcelUtil = new MSExcelUtil(); Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path); Worksheet sheet = workbook.ActiveSheet; msExcelUtil.SetCellValue(sheet, 2, 1, projectDto.ProjectShortName); int startIndex = 0; startIndex += 3; int index = 1; foreach (Quotation_QiTa2Dto dto in lst) { msExcelUtil.SetCellValue(sheet, 1, startIndex, index); msExcelUtil.SetCellValue(sheet, 2, startIndex, dto.SupplierName); msExcelUtil.SetCellValue(sheet, 3, startIndex, dto.Province); msExcelUtil.SetCellValue(sheet, 4, startIndex, dto.City); msExcelUtil.SetCellValue(sheet, 5, startIndex, dto.caigoufenlei); msExcelUtil.SetCellValue(sheet, 6, startIndex, dto.caigoufangshi); msExcelUtil.SetCellValue(sheet, 7, startIndex, dto.tigongfuwu); msExcelUtil.SetCellValue(sheet, 8, startIndex, dto.feiyonggoucheng); msExcelUtil.SetCellValue(sheet, 9, startIndex, dto.pinming); msExcelUtil.SetCellValue(sheet, 10, startIndex, dto.guige); msExcelUtil.SetCellValue(sheet, 11, startIndex, dto.xinghao); msExcelUtil.SetCellValue(sheet, 12, startIndex, dto.danjia); msExcelUtil.SetCellValue(sheet, 13, startIndex, dto.shuliang); double count = string.IsNullOrEmpty(dto.shuliang) ? 0 : double.Parse(dto.shuliang); string xiaoji = dto.danjia.HasValue ? (dto.danjia.Value * new decimal(count)).ToString("##.00") : ""; msExcelUtil.SetCellValue(sheet, 14, startIndex, xiaoji); msExcelUtil.SetCellValue(sheet, 15, startIndex, dto.beizhu); if (index < lst.Count) { msExcelUtil.CopyRow(sheet, startIndex); startIndex++; index++; msExcelUtil.AddRow(sheet, startIndex); } else { msExcelUtil.DeleteRow(sheet, startIndex + 1); } } workbook.Save(); msExcelUtil.dispose(); }
public void ExportYanjiu(string path, List <Object> lst) { MSExcelUtil msExcelUtil = new MSExcelUtil(); Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path); Worksheet sheet = workbook.ActiveSheet; int startIndex = 2; int index = 1; foreach (Huizongbiao_YanjiuDto dto in lst) { int colIndex = 1; msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, index); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.SupplierName); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ProjectName); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ProjectShortName); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.xiangmujingli); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.zhixingshengfen); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.zhixingchengshi); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ExecuteCycle); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ProjectType); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ExcuteMode); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.gongzuofenlei); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.gongzuoneirong); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.renyuangangwei); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.leixingpinpai); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.guigeyaoqiu); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.zhiliangbiaozhun); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.qitashuoming); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.hesuandanwei); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.danjia); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.shuliang); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.heji); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Remark); if (index < lst.Count) { msExcelUtil.CopyRow(sheet, startIndex); startIndex++; index++; msExcelUtil.AddRow(sheet, startIndex); } } workbook.Save(); msExcelUtil.dispose(); }
public void ExportYewucaigoubiao1(string path, DateTime startDate, DateTime endDate, List <Juesuantongjibiao1_HeaderDto> HeaderDtoList, List <Juesuantongjibiao1_LeftDto> LeftDtoList, List <Juesuantongjibiao1_DataDto> DataDtoList) { MSExcelUtil msExcelUtil = new MSExcelUtil(); Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path); Worksheet sheet = workbook.ActiveSheet; //统计周期 msExcelUtil.SetCellValue(sheet, 2, 1, string.Format("{0} 至 {1}", startDate.ToString("yyyy年MM月dd日"), endDate.ToString("yyyy年MM月dd日"))); //动态列头 var dStartColIndex = 4; foreach (Juesuantongjibiao1_HeaderDto dto in HeaderDtoList) { msExcelUtil.CopyColumn(sheet, dStartColIndex); msExcelUtil.AddColumn(sheet, dStartColIndex + 1); msExcelUtil.SetCellValue(sheet, dStartColIndex++, 2, dto.ExecuteMode); } var rowIndex = 3; foreach (Juesuantongjibiao1_LeftDto dto in LeftDtoList) { msExcelUtil.SetCellValue(sheet, 1, rowIndex, dto.City); msExcelUtil.SetCellValue(sheet, 2, rowIndex, dto.SupplierName); msExcelUtil.SetCellValue(sheet, 3, rowIndex, dto.SupplierSumAmt); rowIndex++; msExcelUtil.AddRow(sheet, rowIndex); } msExcelUtil.DeleteRow(sheet, rowIndex); msExcelUtil.DeleteRow(sheet, rowIndex); foreach (Juesuantongjibiao1_DataDto dto in DataDtoList) { dStartColIndex = 4; var dataRowIndex = 3; int dcolIndex = HeaderDtoList.FindIndex(x => x.ExecuteMode == dto.ExecuteMode); int drowIndex = LeftDtoList.FindIndex(x => x.SupplierId == dto.SupplierId); dStartColIndex += dcolIndex; dataRowIndex += drowIndex; msExcelUtil.SetCellValue(sheet, dStartColIndex, dataRowIndex, dto.ExecuteModeSumAmt); } workbook.Save(); msExcelUtil.dispose(); }
public void ExportFuhe(string path, List <QuotationDto> lst, ProjectDto projectDto, string supplierName) { MSExcelUtil msExcelUtil = new MSExcelUtil(); Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path); Worksheet sheet = workbook.ActiveSheet; msExcelUtil.SetCellValue(sheet, 2, 1, projectDto.ProjectShortName); int startIndex = 0; startIndex += 3; int index = 1; foreach (Quotation_FuHeDto dto in lst) { msExcelUtil.SetCellValue(sheet, 1, startIndex, index); msExcelUtil.SetCellValue(sheet, 2, startIndex, supplierName); msExcelUtil.SetCellValue(sheet, 3, startIndex, dto.Province); msExcelUtil.SetCellValue(sheet, 4, startIndex, dto.City); msExcelUtil.SetCellValue(sheet, 5, startIndex, dto.fuheyaoqiu); msExcelUtil.SetCellValue(sheet, 6, startIndex, dto.dianhuazixun); msExcelUtil.SetCellValue(sheet, 7, startIndex, dto.dianhuahuifang); msExcelUtil.SetCellValue(sheet, 8, startIndex, dto.yanbenbianji); msExcelUtil.SetCellValue(sheet, 9, startIndex, dto.fuzhuyongpin); msExcelUtil.SetCellValue(sheet, 10, startIndex, dto.bianmaleixing); msExcelUtil.SetCellValue(sheet, 11, startIndex, dto.wenjuanbiancheng); msExcelUtil.SetCellValue(sheet, 14, startIndex, dto.shuliang); msExcelUtil.SetCellValue(sheet, 16, startIndex, dto.beizhu); if (index < lst.Count) { msExcelUtil.CopyRow(sheet, startIndex); startIndex++; index++; msExcelUtil.AddRow(sheet, startIndex); } else { msExcelUtil.DeleteRow(sheet, startIndex + 1); } } workbook.Save(); msExcelUtil.dispose(); }
public void ExportQita2(string path, List <QuotationDto> lst, ProjectDto projectDto, string supplierName) { MSExcelUtil msExcelUtil = new MSExcelUtil(); Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path); Worksheet sheet = workbook.ActiveSheet; msExcelUtil.SetCellValue(sheet, 2, 1, projectDto.ProjectShortName); int startIndex = 0; startIndex += 3; int index = 1; foreach (Quotation_QiTa2Dto dto in lst) { msExcelUtil.SetCellValue(sheet, 1, startIndex, index); msExcelUtil.SetCellValue(sheet, 2, startIndex, supplierName); msExcelUtil.SetCellValue(sheet, 3, startIndex, dto.Province); msExcelUtil.SetCellValue(sheet, 4, startIndex, dto.City); msExcelUtil.SetCellValue(sheet, 5, startIndex, dto.caigoufenlei); msExcelUtil.SetCellValue(sheet, 6, startIndex, dto.caigoufangshi); msExcelUtil.SetCellValue(sheet, 7, startIndex, dto.tigongfuwu); msExcelUtil.SetCellValue(sheet, 8, startIndex, dto.feiyonggoucheng); msExcelUtil.SetCellValue(sheet, 9, startIndex, dto.pinming); msExcelUtil.SetCellValue(sheet, 10, startIndex, dto.guige); msExcelUtil.SetCellValue(sheet, 11, startIndex, dto.xinghao); msExcelUtil.SetCellValue(sheet, 13, startIndex, dto.shuliang); msExcelUtil.SetCellValue(sheet, 15, startIndex, dto.beizhu); if (index < lst.Count) { msExcelUtil.CopyRow(sheet, startIndex); startIndex++; index++; msExcelUtil.AddRow(sheet, startIndex); } else { msExcelUtil.DeleteRow(sheet, startIndex + 1); } } workbook.Save(); msExcelUtil.dispose(); }
public void ExportYanjiu(string path, List <QuotationDto> lst, ProjectDto projectDto, string supplierName) { MSExcelUtil msExcelUtil = new MSExcelUtil(); Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path); Worksheet sheet = workbook.ActiveSheet; msExcelUtil.SetCellValue(sheet, 2, 1, projectDto.ProjectShortName); int startIndex = 0; startIndex += 3; int index = 1; foreach (Quotation_YanJiuDto dto in lst) { msExcelUtil.SetCellValue(sheet, 1, startIndex, index); msExcelUtil.SetCellValue(sheet, 2, startIndex, supplierName); msExcelUtil.SetCellValue(sheet, 3, startIndex, dto.Province); msExcelUtil.SetCellValue(sheet, 4, startIndex, dto.City); msExcelUtil.SetCellValue(sheet, 5, startIndex, dto.zhixingfangshi); msExcelUtil.SetCellValue(sheet, 6, startIndex, dto.gongzuofenlei); msExcelUtil.SetCellValue(sheet, 7, startIndex, dto.gongzuoneirong); msExcelUtil.SetCellValue(sheet, 8, startIndex, dto.leixingpinpai); msExcelUtil.SetCellValue(sheet, 9, startIndex, dto.guigeyaoqiu); msExcelUtil.SetCellValue(sheet, 10, startIndex, dto.zhiliangbiaozhun); msExcelUtil.SetCellValue(sheet, 13, startIndex, dto.shuliang); msExcelUtil.SetCellValue(sheet, 15, startIndex, dto.beizhu); if (index < lst.Count) { msExcelUtil.CopyRow(sheet, startIndex); startIndex++; index++; msExcelUtil.AddRow(sheet, startIndex); } else { msExcelUtil.DeleteRow(sheet, startIndex + 1); } } workbook.Save(); msExcelUtil.dispose(); }
public void ExportZhixing(string path, List <QuotationDto> lst, ProjectDto projectDto, string supplierName) { MSExcelUtil msExcelUtil = new MSExcelUtil(); Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path); Worksheet sheet = workbook.ActiveSheet; msExcelUtil.SetCellValue(sheet, 2, 1, projectDto.ProjectShortName); int startIndex = 0; startIndex += 3; int startRow = startIndex; int index = 1; foreach (Quotation_ZhiXingDto dto in lst) { msExcelUtil.SetCellValue(sheet, 1, startIndex, index); msExcelUtil.SetCellValue(sheet, 2, startIndex, supplierName); msExcelUtil.SetCellValue(sheet, 3, startIndex, dto.Province); msExcelUtil.SetCellValue(sheet, 4, startIndex, dto.City); msExcelUtil.SetCellValue(sheet, 5, startIndex, dto.IntoShopType); msExcelUtil.SetCellValue(sheet, 6, startIndex, dto.ExcuteType); msExcelUtil.SetCellValue(sheet, 7, startIndex, dto.UserType); msExcelUtil.SetCellValue(sheet, 8, startIndex, dto.ExistingOrPotential); msExcelUtil.SetCellValue(sheet, 9, startIndex, dto.CustomerType); msExcelUtil.SetCellValue(sheet, 12, startIndex, dto.Count); msExcelUtil.SetCellValue(sheet, 14, startIndex, dto.Remark); if (index < lst.Count) { msExcelUtil.CopyRow(sheet, startIndex); startIndex++; index++; msExcelUtil.AddRow(sheet, startIndex); } else { msExcelUtil.DeleteRow(sheet, startIndex + 1); } } workbook.Save(); msExcelUtil.dispose(); }
public void ExportZhuijia(string path, List <Jiagetongjibiao_HeaderDto> lst_Head, List <Jiagetongjibiao_Left_Shoudong_Dto> lst_Left, List <Jiagetongjibiao_Data_Shoudong_Dto> lst_Data, DateTime startDate, DateTime endDate) { MSExcelUtil msExcelUtil = new MSExcelUtil(); Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path); Worksheet sheet = workbook.ActiveSheet; //统计周期 msExcelUtil.SetCellValue(sheet, 2, 1, string.Format("{0} 至 {1}", startDate.ToString("yyyy年MM月dd日"), endDate.ToString("yyyy年MM月dd日"))); //动态列头 var dStartColIndex = 3; foreach (Jiagetongjibiao_HeaderDto dto in lst_Head) { msExcelUtil.CopyColumn(sheet, dStartColIndex); msExcelUtil.AddColumn(sheet, dStartColIndex + 1); msExcelUtil.SetCellValue(sheet, dStartColIndex++, 2, dto.zhixingchengshi); } var rowIndex = 3; foreach (Jiagetongjibiao_Left_Shoudong_Dto dto in lst_Left) { msExcelUtil.SetCellValue(sheet, 1, rowIndex, rowIndex - 1); msExcelUtil.SetCellValue(sheet, 2, rowIndex, dto.FeeContent); rowIndex++; msExcelUtil.AddRow(sheet, rowIndex); } msExcelUtil.DeleteRow(sheet, rowIndex); foreach (Jiagetongjibiao_Data_Shoudong_Dto dto in lst_Data) { dStartColIndex = 3; var dataRowIndex = 3; int dcolIndex = lst_Head.FindIndex(x => x.zhixingchengshi == dto.zhixingchengshi); int drowIndex = lst_Left.FindIndex(x => x.FeeContent == dto.FeeContent); dStartColIndex += dcolIndex; dataRowIndex += drowIndex; msExcelUtil.SetCellValue(sheet, dStartColIndex, dataRowIndex, dto.danjia_AVG); } workbook.Save(); msExcelUtil.dispose();; }
public void ExportZhuijia1(string path, List <Juesuantongjibiao1_Shoudong_HeaderDto> HeaderDtoList, List <Juesuantongjibiao1_Shoudong_LeftDto> LeftDtoList, List <Juesuantongjibiao1_Shoudong_DataDto> DataDtoList) { MSExcelUtil msExcelUtil = new MSExcelUtil(); Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path); Worksheet sheet = workbook.ActiveSheet; //动态列头 var dStartColIndex = 4; foreach (Juesuantongjibiao1_Shoudong_HeaderDto dto in HeaderDtoList) { msExcelUtil.CopyColumn(sheet, dStartColIndex); msExcelUtil.AddColumn(sheet, dStartColIndex + 1); msExcelUtil.SetCellValue(sheet, dStartColIndex++, 1, dto.FeeContent); } var rowIndex = 2; foreach (Juesuantongjibiao1_Shoudong_LeftDto dto in LeftDtoList) { msExcelUtil.SetCellValue(sheet, 1, rowIndex, dto.SupplierName); msExcelUtil.SetCellValue(sheet, 2, rowIndex, dto.City); msExcelUtil.SetCellValue(sheet, 3, rowIndex, dto.SupplierSumAmt); rowIndex++; msExcelUtil.AddRow(sheet, rowIndex); } msExcelUtil.DeleteRow(sheet, rowIndex); foreach (Juesuantongjibiao1_Shoudong_DataDto dto in DataDtoList) { dStartColIndex = 4; var dataRowIndex = 2; int dcolIndex = HeaderDtoList.FindIndex(x => x.FeeContent == dto.FeeContent); int drowIndex = LeftDtoList.FindIndex(x => x.SupplierId == dto.SupplierId); dStartColIndex += dcolIndex; dataRowIndex += drowIndex; msExcelUtil.SetCellValue(sheet, dStartColIndex, dataRowIndex, dto.FeeContentSumAmt); } workbook.Save(); msExcelUtil.dispose(); }
public void ExportZhuijia(string path, List <Huizongbiao_ShoudongDto> lst) { MSExcelUtil msExcelUtil = new MSExcelUtil(); Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path); Worksheet sheet = workbook.ActiveSheet; int index = 1; int startIndex = 2; foreach (Huizongbiao_ShoudongDto dto in lst) { int colIndex = 1; msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, index); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Year); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ProjectName); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ProjectShortName); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ProjectCode); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.SupplierName); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.zhixingshengfen); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.zhixingchengshi); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.FeeContent); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.danjia); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.shuliang); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.heji); msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Remark); if (index < lst.Count) { msExcelUtil.CopyRow(sheet, startIndex); startIndex++; index++; msExcelUtil.AddRow(sheet, startIndex); } } workbook.Save(); msExcelUtil.dispose(); }
private int InsertSettlements(MSExcelUtil msExcelUtil, Worksheet sheet, List <SettlementDtlDto> lst, int startIndex) { int index = 0; foreach (SettlementDtlDto dto in lst) { if (!string.IsNullOrEmpty(dto.SettleAmt)) { if (index < lst.Count - 1) { msExcelUtil.CopyRow(sheet, startIndex); msExcelUtil.AddRow(sheet, startIndex + 1); } msExcelUtil.SetCellValueAndMultLine(sheet, 1, startIndex, dto.FeeContent, 11); msExcelUtil.SetCellValue(sheet, 2, startIndex, dto.danjia); msExcelUtil.SetCellValue(sheet, 3, startIndex, dto.SettleCount); msExcelUtil.SetCellValue(sheet, 4, startIndex, dto.SettleAmt); if (dto.Remark != null && dto.FeeContent.Length < dto.Remark.Length) { msExcelUtil.SetCellValueAndMultLine(sheet, 5, startIndex, dto.Remark, 10); } else { msExcelUtil.SetCellValue(sheet, 5, startIndex, dto.Remark); } if (index < lst.Count - 1) { startIndex++; } } index++; } msExcelUtil.DeleteRow(sheet, startIndex + 1); return(startIndex); }
private int InsertChezhans(MSExcelUtil msExcelUtil, Worksheet sheet, List <QuotationDto> lst, int startIndex) { int index = 1; foreach (Quotation_CheZhanDto dto in lst) { msExcelUtil.SetCellValue(sheet, 1, startIndex, index); msExcelUtil.SetCellValue(sheet, 2, startIndex, dto.SupplierName); msExcelUtil.SetCellValue(sheet, 3, startIndex, dto.Province); msExcelUtil.SetCellValue(sheet, 4, startIndex, dto.City); msExcelUtil.SetCellValue(sheet, 5, startIndex, dto.ExcuteType); msExcelUtil.SetCellValue(sheet, 6, startIndex, dto.Responsibilites); msExcelUtil.SetCellValue(sheet, 7, startIndex, dto.UserType); msExcelUtil.SetCellValue(sheet, 8, startIndex, dto.ExistingOrPotential); msExcelUtil.SetCellValue(sheet, 9, startIndex, dto.CustomerType); msExcelUtil.SetCellValue(sheet, 10, startIndex, dto.UnitPrice); msExcelUtil.SetCellValue(sheet, 12, startIndex, dto.Count); double count = string.IsNullOrEmpty(dto.Count) ? 0 : double.Parse(dto.Count); string xiaoji = dto.UnitPrice.HasValue ? (dto.UnitPrice.Value * new decimal(count)).ToString("##.00") : ""; msExcelUtil.SetCellValue(sheet, 13, startIndex, xiaoji); if (index < lst.Count) { msExcelUtil.CopyRow(sheet, startIndex); startIndex++; index++; msExcelUtil.AddRow(sheet, startIndex); } else { msExcelUtil.DeleteRow(sheet, startIndex + 1); } } return(startIndex); }
public ActionResult PaymentExport(string serviceTrade, string projectName, string projectShortName, string supplierName, DateTime?FactPaymentTimeStart, DateTime?FactPaymentTimeEnd, string FactPaymentAmtStart, string FactPaymentAmtEnd, string PayChk = "否") { string absPath = Server.MapPath(basePath); if (!Directory.Exists(absPath)) { Directory.CreateDirectory(absPath); } string createFileName = "对公付款汇总_" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx"; string path = absPath + createFileName; string templateFile = Server.MapPath(tempPath + "对公付款汇总.xlsx");// 模板的路径 System.IO.File.Copy(templateFile, path); MSExcelUtil msExcelUtil = new MSExcelUtil(); Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path); Worksheet sheet = workbook.ActiveSheet; decimal? amtStart = string.IsNullOrEmpty(FactPaymentAmtStart) ? new Nullable <decimal>() : decimal.Parse(FactPaymentAmtStart); decimal? amtEnd = string.IsNullOrEmpty(FactPaymentAmtEnd) ? new Nullable <decimal>() : decimal.Parse(FactPaymentAmtEnd); List <FlowOrderDto> list = service.PaymentListSearch(serviceTrade, projectName, projectShortName, supplierName, FactPaymentTimeStart, FactPaymentTimeEnd, amtStart, amtEnd, PayChk); int startIndex = 2; if (list.Count > 0) { foreach (FlowOrderDto dto in list) { msExcelUtil.SetCellValue(sheet, 1, startIndex, (startIndex - 1).ToString()); msExcelUtil.SetCellValue(sheet, 2, startIndex, dto.Year); msExcelUtil.SetCellValue(sheet, 3, startIndex, dto.FactPaymentTime); msExcelUtil.SetCellValue(sheet, 4, startIndex, dto.ModelType); msExcelUtil.SetCellValue(sheet, 5, startIndex, dto.ServiceTrade); msExcelUtil.SetCellValue(sheet, 6, startIndex, dto.ProjectName); msExcelUtil.SetCellValue(sheet, 7, startIndex, dto.ProjectShortName); msExcelUtil.SetCellValue(sheet, 8, startIndex, dto.ProjectCode); msExcelUtil.SetCellValue(sheet, 9, startIndex, dto.SupplierName); msExcelUtil.SetCellValue(sheet, 10, startIndex, dto.Payee); msExcelUtil.SetCellValue(sheet, 11, startIndex, dto.PreTaxAmt); msExcelUtil.SetCellValue(sheet, 12, startIndex, dto.TaxAmt); msExcelUtil.SetCellValue(sheet, 13, startIndex, dto.FactPaymentAmt); msExcelUtil.SetCellValue(sheet, 14, startIndex, dto.InvoiceAmt); msExcelUtil.SetCellValue(sheet, 15, startIndex, dto.InvoceAmtThis); msExcelUtil.SetCellValue(sheet, 16, startIndex, dto.ProjectType); msExcelUtil.SetCellValue(sheet, 17, startIndex, dto.ExecuteCycleStartDate); msExcelUtil.SetCellValue(sheet, 18, startIndex, dto.ExecuteCycleEndDate); msExcelUtil.SetCellValue(sheet, 19, startIndex, dto.ConstractChk); msExcelUtil.SetCellValue(sheet, 20, startIndex, dto.SettlementChk); msExcelUtil.SetCellValue(sheet, 21, startIndex, dto.InvoceChk); msExcelUtil.SetCellValue(sheet, 22, startIndex, dto.InvoiceNO); msExcelUtil.SetCellValue(sheet, 23, startIndex, dto.PaperChk); msExcelUtil.SetCellValue(sheet, 24, startIndex, dto.PaymentRemark); msExcelUtil.SetCellValue(sheet, 25, startIndex, dto.Finance_PaymentStatus); msExcelUtil.SetCellValue(sheet, 26, startIndex, dto.Finance_PaymentAmt); msExcelUtil.SetCellValue(sheet, 27, startIndex, dto.Finance_NotPayReason); msExcelUtil.SetCellValue(sheet, 28, startIndex, dto.Finance_Constract); msExcelUtil.SetCellValue(sheet, 29, startIndex, dto.Finance_SettlementChk); msExcelUtil.SetCellValue(sheet, 30, startIndex, dto.Finance_InvoceAmt); msExcelUtil.SetCellValue(sheet, 31, startIndex, dto.Finance_InvoceAmtThis); //msExcelUtil.CopyRow(sheet, startIndex); startIndex++; msExcelUtil.AddRow(sheet, startIndex); } } workbook.Save(); msExcelUtil.dispose(); return(Json(new { ExportPath = path })); }
public ActionResult ApplyPayExport(string projectName, string projectShortName, string supplierName, string serviceTrade) { List <FlowOrderDto> list = service.FlowOrderSearch1(projectName, projectShortName, supplierName, serviceTrade, UserInfo.UserId); string absPath = Server.MapPath(basePath); if (!Directory.Exists(absPath)) { Directory.CreateDirectory(absPath); } string createFileName = "应付流转单付款申请_" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx"; string path = absPath + createFileName; string templateFile = Server.MapPath(tempPath + "应付流转单付款申请.xlsx");// 模板的路径 System.IO.File.Copy(templateFile, path); MSExcelUtil msExcelUtil = new MSExcelUtil(); Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path); Worksheet sheet = workbook.ActiveSheet; int startIndex = 2; if (list.Count > 0) { foreach (FlowOrderDto dto in list) { msExcelUtil.SetCellValue(sheet, 1, startIndex, dto.ServiceTrade); // msExcelUtil.SetCellValue(sheet, 2, startIndex, dto.DepartmentCode); msExcelUtil.SetCellValue(sheet, 2, startIndex, dto.CostDepartment); msExcelUtil.SetCellValue(sheet, 3, startIndex, dto.ProjectName); msExcelUtil.SetCellValue(sheet, 4, startIndex, dto.ProjectShortName); msExcelUtil.SetCellValue(sheet, 5, startIndex, dto.ProjectCode); msExcelUtil.SetCellValue(sheet, 6, startIndex, dto.ExecuteCycleStartDate.HasValue ? dto.ExecuteCycleStartDate.Value.ToString("yyyy-MM-dd") : ""); msExcelUtil.SetCellValue(sheet, 7, startIndex, dto.ExecuteCycleEndDate.HasValue ? dto.ExecuteCycleEndDate.Value.ToString("yyyy-MM-dd") : ""); msExcelUtil.SetCellValue(sheet, 8, startIndex, dto.SupplierName); msExcelUtil.SetCellValue(sheet, 9, startIndex, dto.BudgetAmt); msExcelUtil.SetCellValue(sheet, 10, startIndex, dto.BudgetLeftAmt); msExcelUtil.SetCellValue(sheet, 11, startIndex, dto.ExpendType); msExcelUtil.SetCellValue(sheet, 12, startIndex, dto.ExpendPattern); msExcelUtil.SetCellValue(sheet, 13, startIndex, dto.PaymentType); msExcelUtil.SetCellValue(sheet, 14, startIndex, dto.PaymentCompany); //msExcelUtil.SetCellValue(sheet, 15, startIndex, dto.EstimatedPaymentTime.HasValue ? dto.EstimatedPaymentTime.Value.ToString("yyyy-MM-dd") : ""); //msExcelUtil.SetCellValue(sheet, 16, startIndex, dto.EstimatePaymentAmt); msExcelUtil.SetCellValue(sheet, 15, startIndex, dto.FactPaymentTime.HasValue ? dto.FactPaymentTime.Value.ToString("yyyy-MM-dd") : ""); msExcelUtil.SetCellValue(sheet, 16, startIndex, dto.FactPaymentAmt); msExcelUtil.SetCellValue(sheet, 17, startIndex, dto.InvoiceAmt); msExcelUtil.SetCellValue(sheet, 18, startIndex, dto.Payee); msExcelUtil.SetCellValue(sheet, 19, startIndex, dto.Remark); msExcelUtil.SetCellValue(sheet, 20, startIndex, dto.Finance_PaymentStatus); if (list.IndexOf(dto) < list.Count - 1) { msExcelUtil.CopyRow(sheet, startIndex); startIndex++; msExcelUtil.AddRow(sheet, startIndex); } } } workbook.Save(); msExcelUtil.dispose(); return(Json(new { ExportPath = path })); }