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 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 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 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 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();; }
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(); }