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); }
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); } }
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 = 23; i < 30; i++) { for (int j = 0; j < shopReportDto.ShopCharterScoreInfoDtoList.Count; j++) { if (msExcelUtil.GetCellValue(worksheet_FengMian, "B", i).ToString() == shopReportDto.ShopCharterScoreInfoDtoList[j].CharterCode) { msExcelUtil.SetCellValue(worksheet_FengMian, "G", i, shopReportDto.ShopCharterScoreInfoDtoList[j].ShopScore); msExcelUtil.SetCellValue(worksheet_FengMian, "H", i, shopReportDto.ShopCharterScoreInfoDtoList[j].SmallScore); msExcelUtil.SetCellValue(worksheet_FengMian, "I", i, shopReportDto.ShopCharterScoreInfoDtoList[j].BigScore); msExcelUtil.SetCellValue(worksheet_FengMian, "J", i, shopReportDto.ShopCharterScoreInfoDtoList[j].AllScore); } } } #endregion #region 体系信息 for (int i = 50; i < 152; 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 string projectCode = CommonHandler.GetComboBoxSelectedValue(cboProjects).ToString(); projectCode = "20" + projectCode.Substring(3, 2) + "Q" + projectCode.Substring(8, 1); workbook.Close(true, Path.Combine(tbnFilePath.Text, "梅赛德斯-迈巴赫销售质量现场考核" + "_" + shopReportDto.ShopCode + "_" + shopReportDto.ShopName + "综合报告" + ".xlsx"), Type.Missing); } else { Workbook workbook = msExcelUtil.OpenExcelByMSExcel(tbnFilePath.Text + @"\" + "2016Q4梅赛德斯-奔驰销售质量现场考核综合报告.xlsx"); Worksheet worksheet_FengMian = workbook.Worksheets["本店总分"] as Worksheet; #region 经销商基本信息 { #region 经销商基本信息 msExcelUtil.SetCellValue(worksheet_FengMian, "D10", shopReportDto.ShopName); msExcelUtil.SetCellValue(worksheet_FengMian, "D12", 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 = 23; i < 33; i++) { for (int j = 0; j < shopReportDto.ShopCharterScoreInfoDtoList.Count; j++) { if (msExcelUtil.GetCellValue(worksheet_FengMian, "B", i).ToString() == shopReportDto.ShopCharterScoreInfoDtoList[j].CharterCode) { msExcelUtil.SetCellValue(worksheet_FengMian, "G", i, shopReportDto.ShopCharterScoreInfoDtoList[j].ShopScore); msExcelUtil.SetCellValue(worksheet_FengMian, "H", i, shopReportDto.ShopCharterScoreInfoDtoList[j].SmallScore); msExcelUtil.SetCellValue(worksheet_FengMian, "I", i, shopReportDto.ShopCharterScoreInfoDtoList[j].BigScore); msExcelUtil.SetCellValue(worksheet_FengMian, "J", i, shopReportDto.ShopCharterScoreInfoDtoList[j].AllScore); } } } #endregion #region 体系信息 for (int i = 54; i < 210; 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 > 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 BDC //if (shopReportDto.BDCORRepScoreInfoDtoList != null && shopReportDto.BDCORRepScoreInfoDtoList.Count > 0) //{ // if (shopReportDto.BDCORRepScoreInfoDtoList[0].SalesType == "2") // { // Worksheet worksheet_BDC = workbook.Worksheets["电话咨询环节得分(BDC)"] as Worksheet; // msExcelUtil.SetCellValue(worksheet_BDC, "G4", "BDC" + "\r\n" + shopReportDto.BDCORRepScoreInfoDtoList[0].SaleName); // msExcelUtil.SetCellValue(worksheet_BDC, "G5", shopReportDto.BDCORRepScoreInfoDtoList[0].Score); // msExcelUtil.SetCellValue(worksheet_BDC, "H5", shopReportDto.BDCORRepScoreInfoDtoList[0].SmallAreaScore); // msExcelUtil.SetCellValue(worksheet_BDC, "I5", shopReportDto.BDCORRepScoreInfoDtoList[0].BigAreaScore); // msExcelUtil.SetCellValue(worksheet_BDC, "J5", shopReportDto.BDCORRepScoreInfoDtoList[0].AllScore); // msExcelUtil.SetCellValue(worksheet_BDC, "G7", "BDC" + "\r\n" + shopReportDto.BDCORRepScoreInfoDtoList[0].SaleName); // for (int i = 8; i < 26; i++) // { // for (int j = 0; j < shopReportDto.BDCShopSubjectScoreInfoList.Count; j++) // { // if (msExcelUtil.GetCellValue(worksheet_BDC, "B", i).ToString() == shopReportDto.BDCShopSubjectScoreInfoList[j].SubjectCode) // { // msExcelUtil.SetCellValue(worksheet_BDC, "G", i, shopReportDto.BDCShopSubjectScoreInfoList[j].Score); // msExcelUtil.SetCellValue(worksheet_BDC, "H", i, shopReportDto.BDCShopSubjectScoreInfoList[j].LossDesc); // if (shopReportDto.BDCShopSubjectScoreInfoList[j].LossDesc.Length > 32) // msExcelUtil.SetCellHeight(worksheet_FengMian, "H", i, 36); // if (shopReportDto.BDCShopSubjectScoreInfoList[j].LossDesc.Length >= 48) // msExcelUtil.SetCellHeight(worksheet_FengMian, "H", i, 48); // if (shopReportDto.BDCShopSubjectScoreInfoList[j].LossDesc.Length >= 64) // msExcelUtil.SetCellHeight(worksheet_FengMian, "H", i, 60); // msExcelUtil.SetCellValue(worksheet_BDC, "J", i, shopReportDto.BDCShopSubjectScoreInfoList[j].Remark); // } // } // } // } // else // { // Worksheet worksheet_Rep = workbook.Worksheets["接待员 销售顾问环节得分"] as Worksheet; // msExcelUtil.SetCellValue(worksheet_Rep, "G4", "接待员" + "\r\n" + shopReportDto.BDCORRepScoreInfoDtoList[0].SaleName); // msExcelUtil.SetCellValue(worksheet_Rep, "G5", shopReportDto.BDCORRepScoreInfoDtoList[0].Score); // msExcelUtil.SetCellValue(worksheet_Rep, "H5", shopReportDto.BDCORRepScoreInfoDtoList[0].SmallAreaScore); // msExcelUtil.SetCellValue(worksheet_Rep, "I5", shopReportDto.BDCORRepScoreInfoDtoList[0].BigAreaScore); // msExcelUtil.SetCellValue(worksheet_Rep, "J5", shopReportDto.BDCORRepScoreInfoDtoList[0].AllScore); // msExcelUtil.SetCellValue(worksheet_Rep, "G7", "接待员" + "\r\n" + shopReportDto.BDCORRepScoreInfoDtoList[0].SaleName); // for (int i = 8; i < 26; i++) // { // for (int j = 0; j < shopReportDto.BDCShopSubjectScoreInfoList.Count; j++) // { // if (msExcelUtil.GetCellValue(worksheet_Rep, "B", i).ToString() == shopReportDto.BDCShopSubjectScoreInfoList[j].SubjectCode) // { // msExcelUtil.SetCellValue(worksheet_Rep, "G", i, shopReportDto.BDCShopSubjectScoreInfoList[j].Score); // msExcelUtil.SetCellValue(worksheet_Rep, "H", i, shopReportDto.BDCShopSubjectScoreInfoList[j].LossDesc); // if (shopReportDto.BDCShopSubjectScoreInfoList[j].LossDesc.Length > 32) // msExcelUtil.SetCellHeight(worksheet_FengMian, "H", i, 36); // if (shopReportDto.BDCShopSubjectScoreInfoList[j].LossDesc.Length >= 48) // msExcelUtil.SetCellHeight(worksheet_FengMian, "H", i, 48); // if (shopReportDto.BDCShopSubjectScoreInfoList[j].LossDesc.Length >= 64) // msExcelUtil.SetCellHeight(worksheet_FengMian, "H", i, 60); // msExcelUtil.SetCellValue(worksheet_Rep, "J", i, shopReportDto.BDCShopSubjectScoreInfoList[j].Remark); // } // } // } // } //} //#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); } } msExcelUtil.SetCellValue(worksheet_SaleContant, "O", 6, shopReportDto.SaleContantScoreInfo_AreaList[0].SmallAreaScore); msExcelUtil.SetCellValue(worksheet_SaleContant, "P", 6, shopReportDto.SaleContantScoreInfo_AreaList[0].BigAreaScore); msExcelUtil.SetCellValue(worksheet_SaleContant, "Q", 6, shopReportDto.SaleContantScoreInfo_AreaList[0].AllScore); for (int i = 7; i < 16; i++) { for (int j = 0; j < shopReportDto.SaleContantCharterScoreInfoDtoList.Count; j++) { if (msExcelUtil.GetCellValue(worksheet_SaleContant, "B", i).ToString() == shopReportDto.SaleContantCharterScoreInfoDtoList[j].CharterCode) { for (int z = 7; z < 15; z++) { if (msExcelUtil.GetCellValue(worksheet_SaleContant, z, 5).ToString() == "销售顾问" + "\r\n" + shopReportDto.SaleContantCharterScoreInfoDtoList[j].SaleName) { msExcelUtil.SetCellValue(worksheet_SaleContant, z, i, shopReportDto.SaleContantCharterScoreInfoDtoList[j].Score); if (shopReportDto.SaleContantCharterScoreInfoDtoList[j].Score.Length > 20) { msExcelUtil.SetCellHeight(worksheet_FengMian, z, i, 36); } if (shopReportDto.SaleContantCharterScoreInfoDtoList[j].Score.Length >= 30) { msExcelUtil.SetCellHeight(worksheet_FengMian, z, i, 48); } if (shopReportDto.SaleContantCharterScoreInfoDtoList[j].Score.Length >= 40) { msExcelUtil.SetCellHeight(worksheet_FengMian, z, i, 60); } if (shopReportDto.SaleContantCharterScoreInfoDtoList[j].Score.Length >= 50) { msExcelUtil.SetCellHeight(worksheet_FengMian, z, i, 72); } } } } } } for (int i = 7; i < 16; i++) { for (int j = 0; j < shopReportDto.SaleAreaCharterScoreDtoList.Count; j++) { if (msExcelUtil.GetCellValue(worksheet_SaleContant, "B", i).ToString() == shopReportDto.SaleAreaCharterScoreDtoList[j].CharterCode) { msExcelUtil.SetCellValue(worksheet_SaleContant, "O", i, shopReportDto.SaleAreaCharterScoreDtoList[j].SmallCharterScore); msExcelUtil.SetCellValue(worksheet_SaleContant, "P", i, shopReportDto.SaleAreaCharterScoreDtoList[j].BigCharterScore); msExcelUtil.SetCellValue(worksheet_SaleContant, "Q", i, shopReportDto.SaleAreaCharterScoreDtoList[j].AllCharterScore); } } } for (int i = 20; 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 = "20" + projectCode.Substring(2, 2) + "Q" + projectCode.Substring(7, 1); workbook.Close(true, Path.Combine(tbnFilePath.Text, projectCode + "梅赛德斯-奔驰销售质量现场考核" + "_" + shopReportDto.ShopCode + "_" + shopReportDto.ShopName + "_综合报告" + ".xlsx"), Type.Missing); } }
private void WriteDataToExcel(ShopReportDto shopReportDto) { Workbook workbook = msExcelUtil.OpenExcelByMSExcel(tbnFilePath.Text + @"\" + "Smart销售质量现场考核报告模板.xlsx"); #region 经销商基本信息 { Worksheet worksheet_FengMian = workbook.Worksheets["本店总分"] as Worksheet; #region 经销商基本信息 msExcelUtil.SetCellValue(worksheet_FengMian, "D11", shopReportDto.ShopName); msExcelUtil.SetCellValue(worksheet_FengMian, "J13", shopReportDto.AreaName); msExcelUtil.SetCellValue(worksheet_FengMian, "D13", shopReportDto.ShopCode); //msExcelUtil.SetCellValue(worksheet_FengMian, "J14", shopReportDto.SalesContant); msExcelUtil.SetCellValue(worksheet_FengMian, "G22", shopReportDto.ShopScore); msExcelUtil.SetCellValue(worksheet_FengMian, "H22", shopReportDto.SmallAreaScore); msExcelUtil.SetCellValue(worksheet_FengMian, "I22", shopReportDto.BigAreaScore); msExcelUtil.SetCellValue(worksheet_FengMian, "J22", shopReportDto.AllScore); msExcelUtil.SetCellValue(worksheet_FengMian, "G23", shopReportDto.OrderNO_All); msExcelUtil.SetCellValue(worksheet_FengMian, "G24", shopReportDto.OrderNO_Area); // msExcelUtil.SetCellValue(worksheet_FengMian, "G30", shopReportDto.MustLoss); #endregion #region 章节信息 for (int i = 23; i < 32; i++) { for (int j = 0; j < shopReportDto.ShopCharterScoreInfoDtoList.Count; j++) { if (msExcelUtil.GetCellValue(worksheet_FengMian, "B", i).ToString() == shopReportDto.ShopCharterScoreInfoDtoList[j].CharterCode) { msExcelUtil.SetCellValue(worksheet_FengMian, "G", i, shopReportDto.ShopCharterScoreInfoDtoList[j].ShopScore); msExcelUtil.SetCellValue(worksheet_FengMian, "H", i, shopReportDto.ShopCharterScoreInfoDtoList[j].SmallScore); msExcelUtil.SetCellValue(worksheet_FengMian, "I", i, shopReportDto.ShopCharterScoreInfoDtoList[j].BigScore); msExcelUtil.SetCellValue(worksheet_FengMian, "J", i, shopReportDto.ShopCharterScoreInfoDtoList[j].AllScore); } } } #endregion #region 体系信息 for (int i = 56; i < 172; i++) { for (int j = 0; j < shopReportDto.ShopSubjectScoreInfoDtoList.Count; j++) { if (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 >= 34) { msExcelUtil.SetCellHeight(worksheet_FengMian, "H", i, 45); } if (shopReportDto.ShopSubjectScoreInfoDtoList[j].LossDesc.Length >= 51) { msExcelUtil.SetCellHeight(worksheet_FengMian, "H", i, 60); } if (shopReportDto.ShopSubjectScoreInfoDtoList[j].LossDesc.Length >= 68) { msExcelUtil.SetCellHeight(worksheet_FengMian, "H", i, 75); } if (shopReportDto.ShopSubjectScoreInfoDtoList[j].LossDesc.Length >= 85) { msExcelUtil.SetCellHeight(worksheet_FengMian, "H", i, 90); } msExcelUtil.SetCellValue(worksheet_FengMian, "K", 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 = 17; 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); }