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 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 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(); }
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 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 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 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 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 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 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 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 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(); }
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 void ExportFuhe(string path, List <Jiagetongjibiao_HeaderDto> lst_Head, List <Jiagetongjibiao_Left_Fuhe_Dto> lst_Left, List <Jiagetongjibiao_Data_Fuhe_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 = 14; 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_Fuhe_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.fuheyaoqiu); msExcelUtil.SetCellValue(sheet, 4, rowIndex, dto.fuheshijian); msExcelUtil.SetCellValue(sheet, 5, rowIndex, dto.dianhuazixun); msExcelUtil.SetCellValue(sheet, 6, rowIndex, dto.dianhuhuifang); msExcelUtil.SetCellValue(sheet, 7, rowIndex, dto.yanbenbianji); msExcelUtil.SetCellValue(sheet, 8, rowIndex, dto.bianmaleixing); msExcelUtil.SetCellValue(sheet, 9, rowIndex, dto.wenjuanbiancheng); msExcelUtil.SetCellValue(sheet, 10, rowIndex, dto.timushuliang); msExcelUtil.SetCellValue(sheet, 11, rowIndex, dto.zishushuliang); msExcelUtil.SetCellValue(sheet, 12, rowIndex, dto.yangbenluru); msExcelUtil.SetCellValue(sheet, 13, rowIndex, dto.hesuandanwei); rowIndex++; msExcelUtil.AddRow(sheet, rowIndex); } msExcelUtil.DeleteRow(sheet, rowIndex); foreach (Jiagetongjibiao_Data_Fuhe_Dto dto in lst_Data) { dStartColIndex = 14; var dataRowIndex = 3; int dcolIndex = lst_Head.FindIndex(x => x.zhixingchengshi == dto.zhixingchengshi); int drowIndex = lst_Left.FindIndex(x => x.ExcuteMode == dto.ExcuteMode && x.fuheyaoqiu == dto.fuheyaoqiu && x.fuheshijian == dto.fuheshijian && x.dianhuazixun == dto.dianhuazixun && x.dianhuhuifang == dto.dianhuhuifang && x.yanbenbianji == dto.yanbenbianji && x.bianmaleixing == dto.bianmaleixing && x.timushuliang == dto.timushuliang && x.zishushuliang == dto.zishushuliang && x.wenjuanbiancheng == dto.wenjuanbiancheng && x.yangbenluru == dto.yangbenluru && x.hesuandanwei == dto.hesuandanwei); dStartColIndex += dcolIndex; dataRowIndex += drowIndex; msExcelUtil.SetCellValue(sheet, dStartColIndex, dataRowIndex, dto.danjia_AVG); } workbook.Save(); msExcelUtil.dispose();; }
private void WriteDataToExcel(ShopReportDto shopReportDto) { // Workbook workbook = msExcelUtil.OpenExcelByMSExcel(AppDomain.CurrentDomain.BaseDirectory + @"\Resources\Template\SingleShopReportTemplate_20130812.xlsx"); Workbook workbook = msExcelUtil.OpenExcelByMSExcel(btnModule.Text); #region 封面 { Worksheet worksheet_FengMian = workbook.Worksheets["封面"] as Worksheet; msExcelUtil.SetCellValue(worksheet_FengMian, "C9", shopReportDto.ShopCode); msExcelUtil.SetCellValue(worksheet_FengMian, "F9", shopReportDto.ShopName); msExcelUtil.SetCellValue(worksheet_FengMian, "C11", shopReportDto.AreaName); msExcelUtil.SetCellValue(worksheet_FengMian, "F11", shopReportDto.Province + shopReportDto.City); //msExcelUtil.SetCellValue(worksheet_FengMian, "D17", shopReportDto.ProjectCode); //msExcelUtil.SetCellValue(worksheet_FengMian, "H17", shopReportDto.City); } #endregion #region 经销商得分概况 { Worksheet worksheet_ShopScore = workbook.Worksheets["经销商得分概况"] as Worksheet; //#region 11行 16行 //List<AllShopScoreDto11> allShopScoreDtoList11= shopReportDto.AllShopScoreDtoList11; //foreach (AllShopScoreDto11 allShopScore in allShopScoreDtoList11) //{ // msExcelUtil.SetCellValue(worksheet_ShopScore, "H", 11, allShopScore.ScoreShop); //} //List<AllShopScoreDto16> allShopScoreDtoList16 = shopReportDto.AllShopScoreDtoList16; //foreach (AllShopScoreDto16 allShopScore in allShopScoreDtoList16) //{ // msExcelUtil.SetCellValue(worksheet_ShopScore, "H", 16, allShopScore.ScoreShop); //} //#endregion #region 12到15行 17到20行 List <ShopChapterScoreDto12To15> shopChpterScoreDtoList12To15 = shopReportDto.ShopChapterScoreDtoList12To15; int rowIndex = 12; foreach (ShopChapterScoreDto12To15 shopChpterScore in shopChpterScoreDtoList12To15) { msExcelUtil.SetCellValue(worksheet_ShopScore, "G", rowIndex, shopChpterScore.SubjectPassCount); msExcelUtil.SetCellValue(worksheet_ShopScore, "H", rowIndex, shopChpterScore.ScoreShop); rowIndex++; } List <ShopChapterScoreDto17To20> shopChpterScoreDtoList17To20 = shopReportDto.ShopChapterScoreDtoList17To20; rowIndex = 17; foreach (ShopChapterScoreDto17To20 shopChpterScore in shopChpterScoreDtoList17To20) { msExcelUtil.SetCellValue(worksheet_ShopScore, "G", rowIndex, shopChpterScore.SubjectPassCount); msExcelUtil.SetCellValue(worksheet_ShopScore, "H", rowIndex, shopChpterScore.ScoreShop); rowIndex++; } #endregion #region 25行 List <AllScoreSumDto> allScoreSumList = shopReportDto.AllScoreSumDtoList; foreach (AllScoreSumDto allScoreSum in allScoreSumList) { msExcelUtil.SetCellValue(worksheet_ShopScore, "D", 25, allScoreSum.ScoreArea_AVG); msExcelUtil.SetCellValue(worksheet_ShopScore, "E", 25, allScoreSum.ScoreArea_MAX); msExcelUtil.SetCellValue(worksheet_ShopScore, "F", 25, allScoreSum.ScoreAll_AVG); msExcelUtil.SetCellValue(worksheet_ShopScore, "G", 25, allScoreSum.ScoreAll_MAX); } #endregion #region 26行 31行 List <AllScoreDto26> allScoreDto26List = shopReportDto.AllScoreDtoList26; foreach (AllScoreDto26 allScoreDto in allScoreDto26List) { msExcelUtil.SetCellValue(worksheet_ShopScore, "D", 26, allScoreDto.ScoreArea_AVG); msExcelUtil.SetCellValue(worksheet_ShopScore, "E", 26, allScoreDto.ScoreArea_MAX); msExcelUtil.SetCellValue(worksheet_ShopScore, "F", 26, allScoreDto.ScoreAll_AVG); msExcelUtil.SetCellValue(worksheet_ShopScore, "G", 26, allScoreDto.ScoreAll_MAX); } List <AllScoreDto31> allScoreDto31List = shopReportDto.AllScoreDtoList31; foreach (AllScoreDto31 allScoreDto in allScoreDto31List) { msExcelUtil.SetCellValue(worksheet_ShopScore, "D", 31, allScoreDto.ScoreArea_AVG); msExcelUtil.SetCellValue(worksheet_ShopScore, "E", 31, allScoreDto.ScoreArea_MAX); msExcelUtil.SetCellValue(worksheet_ShopScore, "F", 31, allScoreDto.ScoreAll_AVG); msExcelUtil.SetCellValue(worksheet_ShopScore, "G", 31, allScoreDto.ScoreAll_MAX); } #endregion #region 27到30行 32到35行 List <ChaptersScoreDto27To30> chaptersScoreDto27To30List = shopReportDto.ChaptersScoreDtoList27To30; rowIndex = 27; foreach (ChaptersScoreDto27To30 chpterScore in chaptersScoreDto27To30List) { msExcelUtil.SetCellValue(worksheet_ShopScore, "D", rowIndex, chpterScore.ScoreArea_AVG); msExcelUtil.SetCellValue(worksheet_ShopScore, "E", rowIndex, chpterScore.ScoreArea_MAX); msExcelUtil.SetCellValue(worksheet_ShopScore, "F", rowIndex, chpterScore.ScoreAll_AVG); msExcelUtil.SetCellValue(worksheet_ShopScore, "G", rowIndex, chpterScore.ScoreAll_MAX); rowIndex++; } List <ChaptersScoreDto32To35> chaptersScoreDto32To35List = shopReportDto.ChaptersScoreDtoList32To35; rowIndex = 32; foreach (ChaptersScoreDto32To35 chpterScore in chaptersScoreDto32To35List) { msExcelUtil.SetCellValue(worksheet_ShopScore, "D", rowIndex, chpterScore.ScoreArea_AVG); msExcelUtil.SetCellValue(worksheet_ShopScore, "E", rowIndex, chpterScore.ScoreArea_MAX); msExcelUtil.SetCellValue(worksheet_ShopScore, "F", rowIndex, chpterScore.ScoreAll_AVG); msExcelUtil.SetCellValue(worksheet_ShopScore, "G", rowIndex, chpterScore.ScoreAll_MAX); rowIndex++; } #endregion } #endregion #region 指标点得分详情 List <SubjectsScoreDto> subjectsScoreDtoListDetail = shopReportDto.SubjectsScoreDtoList; { Worksheet worksheet_ShopScoreDetail = workbook.Worksheets["指标点得分详情"] as Worksheet; int rowIndex1 = 4; foreach (SubjectsScoreDto subjectsScoreDto in subjectsScoreDtoListDetail) { for (int i = 4; i < 150; i++) { if (subjectsScoreDto.SubjectCode == msExcelUtil.GetCellValue(worksheet_ShopScoreDetail, "C", i).ToString()) { //msExcelUtil.SetCellValue(worksheet_ShopScoreDetail, "F", i, subjectsScoreDto.FullScore); if (subjectsScoreDto.Score == Convert.ToDecimal(9999.00)) { msExcelUtil.SetCellValue(worksheet_ShopScoreDetail, "G", i, "."); } else { msExcelUtil.SetCellValue(worksheet_ShopScoreDetail, "G", i, subjectsScoreDto.Score); } msExcelUtil.SetCellValue(worksheet_ShopScoreDetail, "H", i, subjectsScoreDto.LostDesc); } } } } #endregion #region 失分照片 { List <SubjectsScoreDto> subjectsScoreDtoList = shopReportDto.SubjectsScoreDtoList; Worksheet worksheet_ShopScoreDetail2 = workbook.Worksheets["失分照片"] as Worksheet; int rowIndex = 3; foreach (SubjectsScoreDto subjectsScoreDto in subjectsScoreDtoList) { if (String.IsNullOrEmpty(subjectsScoreDto.LostDesc) || String.IsNullOrEmpty(subjectsScoreDto.PicName) || subjectsScoreDto.LostDesc == null || subjectsScoreDto.PicName == null) { msExcelUtil.DeleteRow(worksheet_ShopScoreDetail2, rowIndex); continue; } else { // msExcelUtil.SetCellValue(worksheet_ShopScoreDetail2, "F", rowIndex, subjectsScoreDto.LostDesc); string[] picNameArray = subjectsScoreDto.PicName.Split(';'); int picIndex = 0; foreach (string picName in picNameArray) { if (picIndex != 0 && picIndex % 3 == 0) { msExcelUtil.AddRow(worksheet_ShopScoreDetail2, ++rowIndex); } if (string.IsNullOrEmpty(picName)) { continue; } byte[] bytes = service.SearchAnswerDtl2Pic(picName.Replace(".jpg", ""), shopReportDto.ProjectCode + shopReportDto.ShopName, subjectsScoreDto.SubjectCode, "", ""); if (bytes == null || bytes.Length == 0) { continue; } Image.FromStream(new MemoryStream(bytes)).Save(Path.Combine(Path.GetTempPath(), picName + ".jpg")); int colIndex = 3 + picIndex % 3; msExcelUtil.InsertPicture(worksheet_ShopScoreDetail2, worksheet_ShopScoreDetail2.Cells[rowIndex, colIndex] as Microsoft.Office.Interop.Excel.Range, Path.Combine(Path.GetTempPath(), picName + ".jpg"), rowIndex); picIndex++; } } rowIndex++; } } #endregion //workbook.Save(Path.Combine(tbnFilePath.Text,shopReportDto.ProjectCode+"_"+shopReportDto.ShopName+".xls")); workbook.Close(true, Path.Combine(tbnFilePath.Text, shopReportDto.ProjectCode + "_" + shopReportDto.ShopName + ".xlsx"), Type.Missing); }
public void ExportZhixing(string path, List <Jiagetongjibiao_HeaderDto> lst_Head, List <Jiagetongjibiao_Left_Zhixing_Dto> lst_Left, List <Jiagetongjibiao_Data_Zhixing_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 = 21; 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_Zhixing_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.zhixingfenlei); msExcelUtil.SetCellValue(sheet, 4, rowIndex, dto.gongzuozhize); msExcelUtil.SetCellValue(sheet, 5, rowIndex, dto.jindianfangshi); msExcelUtil.SetCellValue(sheet, 6, rowIndex, dto.yonghufenlei); msExcelUtil.SetCellValue(sheet, 7, rowIndex, dto.kehufenlei); msExcelUtil.SetCellValue(sheet, 8, rowIndex, dto.xianyouhuoqianzai); msExcelUtil.SetCellValue(sheet, 9, rowIndex, dto.fangwenshichang); msExcelUtil.SetCellValue(sheet, 10, rowIndex, dto.chenggonglv); msExcelUtil.SetCellValue(sheet, 11, rowIndex, dto.tuisongfangshi); msExcelUtil.SetCellValue(sheet, 12, rowIndex, dto.canhuirenshu); msExcelUtil.SetCellValue(sheet, 13, rowIndex, dto.genfangxuqiu); msExcelUtil.SetCellValue(sheet, 14, rowIndex, dto.cheliangleibei); msExcelUtil.SetCellValue(sheet, 15, rowIndex, dto.pinpaifenlei); msExcelUtil.SetCellValue(sheet, 16, rowIndex, dto.chejiafanwei); msExcelUtil.SetCellValue(sheet, 17, rowIndex, dto.goucheyugoushijianduan); msExcelUtil.SetCellValue(sheet, 18, rowIndex, dto.gongzuoshijian); msExcelUtil.SetCellValue(sheet, 19, rowIndex, dto.jingyingshijian); msExcelUtil.SetCellValue(sheet, 20, rowIndex, dto.hesuandanwei); rowIndex++; msExcelUtil.AddRow(sheet, rowIndex); } msExcelUtil.DeleteRow(sheet, rowIndex); foreach (Jiagetongjibiao_Data_Zhixing_Dto dto in lst_Data) { dStartColIndex = 21; var dataRowIndex = 3; int dcolIndex = lst_Head.FindIndex(x => x.zhixingchengshi == dto.zhixingchengshi); int drowIndex = lst_Left.FindIndex(x => x.ExcuteMode == dto.ExcuteMode && x.zhixingfenlei == dto.zhixingfenlei && x.gongzuozhize == dto.gongzuozhize && x.jindianfangshi == dto.jindianfangshi && x.yonghufenlei == dto.yonghufenlei && x.kehufenlei == dto.kehufenlei && x.xianyouhuoqianzai == dto.xianyouhuoqianzai && x.fangwenshichang == dto.fangwenshichang && x.chenggonglv == dto.chenggonglv && x.tuisongfangshi == dto.tuisongfangshi && x.canhuirenshu == dto.canhuirenshu && x.genfangxuqiu == dto.genfangxuqiu && x.cheliangleibei == dto.cheliangleibei && x.pinpaifenlei == dto.pinpaifenlei && x.chejiafanwei == dto.chejiafanwei && x.goucheyugoushijianduan == dto.goucheyugoushijianduan && x.gongzuoshijian == dto.gongzuoshijian && x.jingyingshijian == dto.jingyingshijian && x.hesuandanwei == dto.hesuandanwei); dStartColIndex += dcolIndex; dataRowIndex += drowIndex; msExcelUtil.SetCellValue(sheet, dStartColIndex, dataRowIndex, dto.danjia_AVG); } workbook.Save(); msExcelUtil.dispose();; }
public void ExportQuotationHor(string path, string projectName, string projectShortName, string projectCode, List <QuotationExport_Head_Dto> HeaderDtoList, List <QuotationExport_Left_Dto> LeftDtoList, List <QuotationExport_Data_Dto> DataDtoList) { MSExcelUtil msExcelUtil = new MSExcelUtil(); Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path); Worksheet sheet = workbook.ActiveSheet; //项目名称、简称、编号 msExcelUtil.SetCellValue(sheet, 2, 1, projectName); msExcelUtil.SetCellValue(sheet, 4, 1, projectShortName); msExcelUtil.SetCellValue(sheet, 6, 1, projectCode); //动态列头 int dStartColIndex = 5; int beizhuCount = 0; for (int i = 0; i < HeaderDtoList.Count; i++) { QuotationExport_Head_Dto dto = HeaderDtoList[i]; msExcelUtil.SetCellValue(sheet, dStartColIndex, 2, dto.QuotationMode); dStartColIndex = dStartColIndex + 3; if (i < (HeaderDtoList.Count - 1)) { msExcelUtil.CopyAndInsertColumns(sheet, dStartColIndex - 3, 2, dStartColIndex - 1, 8, dStartColIndex, 2); if (beizhuCount == 0) { msExcelUtil.SetColumnWidth(sheet, dStartColIndex + 1, 10); } //复制备注 beizhuCount++; msExcelUtil.CopyColumn(sheet, dStartColIndex + 3 + beizhuCount); msExcelUtil.AddColumn(sheet, dStartColIndex + 4 + beizhuCount); msExcelUtil.SetCellValue(sheet, dStartColIndex + 4 + beizhuCount, 2, "备注" + (i + 2)); } } beizhuCount = 0; for (int i = 0; i < HeaderDtoList.Count; i++) { //复制备注 beizhuCount++; msExcelUtil.SetColumnWidth(sheet, dStartColIndex + beizhuCount, 20); } var rowIndex = 4; foreach (QuotationExport_Left_Dto dto in LeftDtoList) { int colIndex = 1; msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, rowIndex - 3); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.SupplierName); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.zhixingshengfen); msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.zhixingchengshi); //msExcelUtil.CopyRow(sheet, rowIndex); rowIndex++; msExcelUtil.AddRow(sheet, rowIndex); } msExcelUtil.DeleteRow(sheet, rowIndex); msExcelUtil.DeleteRow(sheet, rowIndex); int beizhuStart = dStartColIndex + 1; decimal[] zongjiArr = new decimal[LeftDtoList.Count]; foreach (QuotationExport_Data_Dto dto in DataDtoList) { dStartColIndex = 5; var dataRowIndex = 4; int dcolIndex = HeaderDtoList.FindIndex(x => x.QuotationMode == dto.QuotationMode); int drowIndex = LeftDtoList.FindIndex(x => (x.SupplierName == dto.SupplierName) && (x.zhixingshengfen == dto.zhixingshengfen) && (x.zhixingchengshi == dto.zhixingchengshi)); dStartColIndex += dcolIndex * 3; dataRowIndex += drowIndex; decimal heji = 0; if (dto.danjia.HasValue && dto.shuliang.HasValue) { heji = dto.danjia.Value * dto.shuliang.Value; } zongjiArr[drowIndex] += heji; msExcelUtil.SetCellValue(sheet, dStartColIndex++, dataRowIndex, dto.danjia); msExcelUtil.SetCellValue(sheet, dStartColIndex++, dataRowIndex, dto.shuliang); msExcelUtil.SetCellValue(sheet, dStartColIndex++, dataRowIndex, heji); msExcelUtil.SetCellValue(sheet, beizhuStart + dcolIndex, dataRowIndex, dto.beizhu); msExcelUtil.SetCellValue(sheet, beizhuStart - 1, dataRowIndex, zongjiArr[drowIndex]); } workbook.Save(); msExcelUtil.dispose(); }