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