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 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(); }
public void ExportNeibu(string path, List <SettlementDtlDto> lst, SettlementMstDto settlementMstDto) { MSExcelUtil msExcelUtil = new MSExcelUtil(); Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path); Worksheet sheet = workbook.ActiveSheet; msExcelUtil.SetCellValueAndMultLine(sheet, 2, 7, settlementMstDto.ProjectName, 11); msExcelUtil.SetCellValueAndMultLine(sheet, 5, 7, settlementMstDto.ProjectCode, 11); //msExcelUtil.SetCellValueAndMultLine(sheet, 2, 8, settlementMstDto.ExecuteCycle, 40); msExcelUtil.SetCellValueAndMultLine(sheet, 5, 8, settlementMstDto.SupplyService, 10); msExcelUtil.SetCellValueAndMultLine(sheet, 2, 9, settlementMstDto.SupplierName, 11); msExcelUtil.SetCellValueAndMultLine(sheet, 5, 9, settlementMstDto.ServiceRegion, 10); int startIndex = 11; startIndex = InsertSettlements(msExcelUtil, sheet, lst.Where(x => x.SettlementType == "1").ToList(), startIndex); startIndex += 3; startIndex = InsertSettlements(msExcelUtil, sheet, lst.Where(x => x.SettlementType == "2").ToList(), startIndex); startIndex += 3; startIndex = InsertSettlements(msExcelUtil, sheet, lst.Where(x => x.SettlementType == "3").ToList(), startIndex); startIndex += 4; msExcelUtil.SetCellValue(sheet, 4, startIndex, settlementMstDto.FlowOrderSum); workbook.Save(); msExcelUtil.dispose(); }
public void ExportZhichi(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 = 5; lst.ForEach(x => { ZhiChi01Dto item = (ZhiChi01Dto)x; item.DtoType = item.RequirementType; }); startIndex = InsertZhiChis(msExcelUtil, sheet, lst.Where(x => x.DtoType == "changdi").ToList(), startIndex); startIndex += 4; startIndex = InsertZhiChis(msExcelUtil, sheet, lst.Where(x => x.DtoType == "buzhan").ToList(), startIndex); startIndex += 5; startIndex = InsertZhiChis(msExcelUtil, sheet, lst.Where(x => x.DtoType == "zhanchezulin").ToList(), startIndex); startIndex += 4; startIndex = InsertZhiChis(msExcelUtil, sheet, lst.Where(x => x.DtoType == "zhancheyunshu").ToList(), startIndex); startIndex += 5; startIndex = InsertZhiChis(msExcelUtil, sheet, lst.Where(x => x.DtoType == "gongyingshanchailv").ToList(), startIndex); workbook.Save(); msExcelUtil.dispose(); }
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 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 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 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 void WriteDataToExcel(ShopReportDto shopReportDto) { Workbook workbook = msExcelUtil.OpenExcelByMSExcel(tbnFilePath.Text + @"\" + "单店报告模板.xlsx"); #region 经销商基本信息 { Worksheet worksheet_FengMian = workbook.Worksheets["广汽本田客服领域特约店得分"] as Worksheet; #region 经销商基本信息 // msExcelUtil.SetCellValue(worksheet_FengMian, "D1", shopReportDto.ShopName); msExcelUtil.SetCellValue(worksheet_FengMian, "D6", shopReportDto.Province); msExcelUtil.SetCellValue(worksheet_FengMian, "D7", shopReportDto.ShopCode); msExcelUtil.SetCellValue(worksheet_FengMian, "H6", shopReportDto.AreaName); msExcelUtil.SetCellValue(worksheet_FengMian, "H7", shopReportDto.ShopName); #endregion #region 体系信息 Worksheet worksheet_Subject = workbook.Worksheets["考核项目达成明细"] as Worksheet; for (int i = 5; i < 300; i++) { for (int j = 0; j < shopReportDto.ShopSubjectScoreInfoDtoList.Count; j++) { if (msExcelUtil.GetCellValue(worksheet_Subject, "M", i).ToString() == shopReportDto.ShopSubjectScoreInfoDtoList[j].SubjectCode || msExcelUtil.GetCellValue(worksheet_Subject, "M", i).ToString() == "*" + shopReportDto.ShopSubjectScoreInfoDtoList[j].SubjectCode) { msExcelUtil.SetCellValue(worksheet_Subject, "O", i, shopReportDto.ShopSubjectScoreInfoDtoList[j].ScoreYOrN); msExcelUtil.SetCellValue(worksheet_Subject, "P", i, shopReportDto.ShopSubjectScoreInfoDtoList[j].LossDesc); if (shopReportDto.ShopSubjectScoreInfoDtoList[j].LossDesc.Length >= 42) { msExcelUtil.SetCellHeight(worksheet_Subject, "P", i, 36); } if (shopReportDto.ShopSubjectScoreInfoDtoList[j].LossDesc.Length >= 63) { msExcelUtil.SetCellHeight(worksheet_Subject, "P", i, 54); } if (shopReportDto.ShopSubjectScoreInfoDtoList[j].LossDesc.Length >= 84) { msExcelUtil.SetCellHeight(worksheet_Subject, "P", i, 72); } if (shopReportDto.ShopSubjectScoreInfoDtoList[j].LossDesc.Length >= 105) { msExcelUtil.SetCellHeight(worksheet_Subject, "P", i, 90); } } } } #endregion } #endregion workbook.Close(true, Path.Combine(tbnFilePath.Text, shopReportDto.AreaName + "_" + shopReportDto.ShopCode + "_" + shopReportDto.ShopName + "_2018年第1期售后明检项目_单店报告.xlsx"), Type.Missing); }
public void ExportChezhan(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 = 4; startIndex = InsertChezhans(msExcelUtil, sheet, lst.Where(x => x.DtoType == "Chenzhan01").ToList(), startIndex, supplierName); startIndex += 4; startIndex = InsertChezhans(msExcelUtil, sheet, lst.Where(x => x.DtoType == "Chenzhan02").ToList(), startIndex, supplierName); startIndex += 4; startIndex = InsertChezhans(msExcelUtil, sheet, lst.Where(x => x.DtoType == "Chenzhan03").ToList(), startIndex, supplierName); 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 ExportChezhan(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); lst.ForEach(x => { Quotation_CheZhanDto item = (Quotation_CheZhanDto)x; item.DtoType = item.RequirementType; }); int startIndex = 4; startIndex = InsertChezhans(msExcelUtil, sheet, lst.Where(x => x.DtoType == "dingliang").ToList(), startIndex); startIndex += 4; startIndex = InsertChezhans(msExcelUtil, sheet, lst.Where(x => x.DtoType == "zuotanhui").ToList(), startIndex); startIndex += 4; startIndex = InsertChezhans(msExcelUtil, sheet, lst.Where(x => x.DtoType == "shenfangjirijiliuzhi").ToList(), startIndex); workbook.Save(); msExcelUtil.dispose(); }
public void ExportZhichi(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 = 5; startIndex = InsertZhiChis(msExcelUtil, sheet, lst.Where(x => x.DtoType == "Changdibuzhan_Zhichi01").ToList(), startIndex, supplierName); startIndex += 4; startIndex = InsertZhiChis(msExcelUtil, sheet, lst.Where(x => x.DtoType == "Changdibuzhan_Zhichi02").ToList(), startIndex, supplierName); startIndex += 5; startIndex = InsertZhiChis(msExcelUtil, sheet, lst.Where(x => x.DtoType == "Yunshuzuche_Zhichi01").ToList(), startIndex, supplierName); startIndex += 4; startIndex = InsertZhiChis(msExcelUtil, sheet, lst.Where(x => x.DtoType == "Yunshuzuche_Zhichi02").ToList(), startIndex, supplierName); startIndex += 5; startIndex = InsertZhiChis(msExcelUtil, sheet, lst.Where(x => x.DtoType == "Gongyingshangchailv_Zhichi01").ToList(), startIndex, supplierName); startIndex += 4; startIndex = InsertZhiChis(msExcelUtil, sheet, lst.Where(x => x.DtoType == "Gongyingshangchailv_Zhichi02").ToList(), startIndex, supplierName); workbook.Save(); msExcelUtil.dispose(); }
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 })); }
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 ExportYanjiu(string path, List <Jiagetongjibiao_HeaderDto> lst_Head, List <Jiagetongjibiao_Left_Yanjiu_Dto> lst_Left, List <Jiagetongjibiao_Data_Yanjiu_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_Yanjiu_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.guigeyaoqiu); 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_Yanjiu_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.guigeyaoqiu == dto.guigeyaoqiu && 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 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 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 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(); }
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 })); }
private void WriteDataToExcel(ShopReportDto shopReportDto) { if (!checkBox1.Checked) { Workbook workbook = msExcelUtil.OpenExcelByMSExcel(tbnFilePath.Text + @"\" + "销售质量现场考核_单店报告.xlsx"); #region 经销商基本信息 { Worksheet worksheet_FengMian = workbook.Worksheets["本店总分"] as Worksheet; #region 经销商基本信息 msExcelUtil.SetCellValue(worksheet_FengMian, "D10", shopReportDto.ShopName); msExcelUtil.SetCellValue(worksheet_FengMian, "D12", shopReportDto.AreaName); msExcelUtil.SetCellValue(worksheet_FengMian, "J12", shopReportDto.City); //msExcelUtil.SetCellValue(worksheet_FengMian, "J14", shopReportDto.SalesContant); //msExcelUtil.SetCellValue(worksheet_FengMian, "G20", shopReportDto.ShopScore); //msExcelUtil.SetCellValue(worksheet_FengMian, "H20", shopReportDto.SmallAreaScore); //msExcelUtil.SetCellValue(worksheet_FengMian, "I20", shopReportDto.BigAreaScore); //msExcelUtil.SetCellValue(worksheet_FengMian, "J20", shopReportDto.AllScore); //msExcelUtil.SetCellValue(worksheet_FengMian, "G21", shopReportDto.OrderNO_All); //msExcelUtil.SetCellValue(worksheet_FengMian, "G22", shopReportDto.OrderNO_Area); // msExcelUtil.SetCellValue(worksheet_FengMian, "G30", shopReportDto.MustLoss); #endregion #region 体系信息 for (int i = 30; i < 100; i++) { for (int j = 0; j < shopReportDto.ShopSubjectScoreInfoDtoList.Count; j++) { if (msExcelUtil.GetCellValue(worksheet_FengMian, "B", i).ToString() == shopReportDto.ShopSubjectScoreInfoDtoList[j].SubjectCode || msExcelUtil.GetCellValue(worksheet_FengMian, "B", i).ToString() == "*" + shopReportDto.ShopSubjectScoreInfoDtoList[j].SubjectCode) { msExcelUtil.SetCellValue(worksheet_FengMian, "G", i, shopReportDto.ShopSubjectScoreInfoDtoList[j].Score); msExcelUtil.SetCellValue(worksheet_FengMian, "H", i, shopReportDto.ShopSubjectScoreInfoDtoList[j].LossDesc); if (shopReportDto.ShopSubjectScoreInfoDtoList[j].LossDesc.Length >= 38) { msExcelUtil.SetCellHeight(worksheet_FengMian, "H", i, 45); } if (shopReportDto.ShopSubjectScoreInfoDtoList[j].LossDesc.Length >= 57) { msExcelUtil.SetCellHeight(worksheet_FengMian, "H", i, 60); } if (shopReportDto.ShopSubjectScoreInfoDtoList[j].LossDesc.Length >= 76) { msExcelUtil.SetCellHeight(worksheet_FengMian, "H", i, 75); } if (shopReportDto.ShopSubjectScoreInfoDtoList[j].LossDesc.Length >= 95) { msExcelUtil.SetCellHeight(worksheet_FengMian, "H", i, 90); } if (shopReportDto.ShopSubjectScoreInfoDtoList[j].LossDesc.Length >= 110) { msExcelUtil.SetCellHeight(worksheet_FengMian, "H", i, 105); } //msExcelUtil.SetCellValue(worksheet_FengMian, "I", i, shopReportDto.ShopSubjectScoreInfoDtoList[j].Remark); } } } #endregion } #endregion workbook.Close(true, Path.Combine(tbnFilePath.Text, "2016Q1梅赛德斯-迈巴赫销售质量现场考核" + "_" + shopReportDto.ShopCode + "_" + shopReportDto.ShopName + "_单店报告" + ".xlsx"), Type.Missing); } else { Workbook workbook = msExcelUtil.OpenExcelByMSExcel(tbnFilePath.Text + @"\" + "Smart销售质量现场考核_单店报告.xlsx"); Worksheet worksheet_FengMian = workbook.Worksheets["本店总分"] as Worksheet; #region 经销商基本信息 { #region 经销商基本信息 msExcelUtil.SetCellValue(worksheet_FengMian, "D11", shopReportDto.ShopName); msExcelUtil.SetCellValue(worksheet_FengMian, "D13", shopReportDto.ShopCode); msExcelUtil.SetCellValue(worksheet_FengMian, "J13", shopReportDto.AreaName); //msExcelUtil.SetCellValue(worksheet_FengMian, "I12", shopReportDto.City); //msExcelUtil.SetCellValue(worksheet_FengMian, "J14", shopReportDto.SalesContant); //msExcelUtil.SetCellValue(worksheet_FengMian, "G20", shopReportDto.ShopScore); //msExcelUtil.SetCellValue(worksheet_FengMian, "H20", shopReportDto.SmallAreaScore); //msExcelUtil.SetCellValue(worksheet_FengMian, "I20", shopReportDto.BigAreaScore); //msExcelUtil.SetCellValue(worksheet_FengMian, "J20", shopReportDto.AllScore); //msExcelUtil.SetCellValue(worksheet_FengMian, "G21", shopReportDto.OrderNO_All); //msExcelUtil.SetCellValue(worksheet_FengMian, "G22", shopReportDto.OrderNO_Area); // msExcelUtil.SetCellValue(worksheet_FengMian, "G30", shopReportDto.MustLoss); #endregion #region 体系信息 for (int i = 31; i < 155; i++) { for (int j = 0; j < shopReportDto.ShopSubjectScoreInfoDtoList.Count; j++) { if (msExcelUtil.GetCellValue(worksheet_FengMian, "B", i).ToString() == shopReportDto.ShopSubjectScoreInfoDtoList[j].SubjectCode || msExcelUtil.GetCellValue(worksheet_FengMian, "B", i).ToString() == "*" + shopReportDto.ShopSubjectScoreInfoDtoList[j].SubjectCode) { msExcelUtil.SetCellValue(worksheet_FengMian, "G", i, shopReportDto.ShopSubjectScoreInfoDtoList[j].Score); msExcelUtil.SetCellValue(worksheet_FengMian, "H", i, GetString(shopReportDto.ShopSubjectScoreInfoDtoList[j].LossDesc.Split(';'))); if (shopReportDto.ShopSubjectScoreInfoDtoList[j].LossDesc.Length > 66) { msExcelUtil.SetCellHeight(worksheet_FengMian, "H", i, 36); } if (shopReportDto.ShopSubjectScoreInfoDtoList[j].LossDesc.Length >= 99) { msExcelUtil.SetCellHeight(worksheet_FengMian, "H", i, 48); } if (shopReportDto.ShopSubjectScoreInfoDtoList[j].LossDesc.Length >= 132) { msExcelUtil.SetCellHeight(worksheet_FengMian, "H", i, 60); } //msExcelUtil.SetCellValue(worksheet_FengMian, "J", i, shopReportDto.ShopSubjectScoreInfoDtoList[j].Remark); } } } #endregion } #endregion #region 销售顾问 Worksheet worksheet_SaleContant = workbook.Worksheets["销售顾问得分"] as Worksheet; if (shopReportDto.SaleContantScoreInfoList.Count > 0) { for (int i = 0; i < shopReportDto.SaleContantScoreInfoList.Count; i++) { msExcelUtil.SetCellValue(worksheet_SaleContant, i + 7, 5, "销售顾问" + "\r\n" + shopReportDto.SaleContantScoreInfoList[i].SaleName); //msExcelUtil.SetCellValue(worksheet_SaleContant, i + 7, 6, shopReportDto.SaleContantScoreInfoList[i].Score); } } for (int i = 15; i < 130; i++) { for (int j = 0; j < shopReportDto.SaleContantSubjectScoreDtoList.Count; j++) { if (msExcelUtil.GetCellValue(worksheet_SaleContant, "B", i).ToString() == shopReportDto.SaleContantSubjectScoreDtoList[j].SubjectCode || msExcelUtil.GetCellValue(worksheet_SaleContant, "B", i).ToString() == "*" + shopReportDto.SaleContantSubjectScoreDtoList[j].SubjectCode) { msExcelUtil.SetCellValue(worksheet_SaleContant, "O", i, shopReportDto.SaleContantSubjectScoreDtoList[j].Remark); for (int z = 7; z < 15; z++) { if (msExcelUtil.GetCellValue(worksheet_SaleContant, z, 5).ToString() == "销售顾问" + "\r\n" + shopReportDto.SaleContantSubjectScoreDtoList[j].SaleName) { msExcelUtil.SetCellValue(worksheet_SaleContant, z, i, shopReportDto.SaleContantSubjectScoreDtoList[j].Score); if (shopReportDto.SaleContantSubjectScoreDtoList[j].Score.Length > 20) { msExcelUtil.SetCellHeight(worksheet_SaleContant, z, i, 36); } if (shopReportDto.SaleContantSubjectScoreDtoList[j].Score.Length >= 30) { msExcelUtil.SetCellHeight(worksheet_SaleContant, z, i, 48); } if (shopReportDto.SaleContantSubjectScoreDtoList[j].Score.Length >= 40) { msExcelUtil.SetCellHeight(worksheet_SaleContant, z, i, 60); } if (shopReportDto.SaleContantSubjectScoreDtoList[j].Score.Length >= 50) { msExcelUtil.SetCellHeight(worksheet_SaleContant, z, i, 72); } if (shopReportDto.SaleContantSubjectScoreDtoList[j].Score.Length >= 60) { msExcelUtil.SetCellHeight(worksheet_SaleContant, z, i, 84); } if (shopReportDto.SaleContantSubjectScoreDtoList[j].Score.Length >= 70) { msExcelUtil.SetCellHeight(worksheet_SaleContant, z, i, 96); } } } } } } #endregion //string projectCode = CommonHandler.GetComboBoxSelectedValue(cboProjects).ToString(); //projectCode = projectCode.Substring(0, 4) + "Q" + projectCode.Substring(5, 1); workbook.Close(true, Path.Combine(tbnFilePath.Text, "2017Q3" + "Smart销售质量现场考核" + "_" + shopReportDto.ShopName + "_" + shopReportDto.AreaName + ".xlsx"), Type.Missing); } }
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 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(); }
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 <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(); }