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