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 ShopReportDto GetShopReportDto(string projectCode, string shopCode)
{
DataSet[] dataSetList = service.GetShopReportDto(projectCode, shopCode);
ShopReportDto shopReportDto = new ShopReportDto();
List <ShopSubjectScoreInfoDto> shopSubjectScoreInfoDtoList = new List <ShopSubjectScoreInfoDto>();
shopReportDto.ShopSubjectScoreInfoDtoList = shopSubjectScoreInfoDtoList;
#region 封面信息
DataSet ds = dataSetList[0];
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
shopReportDto.ProjectCode = Convert.ToString(ds.Tables[0].Rows[i]["ProjectCode"]);
shopReportDto.ShopCode = Convert.ToString(ds.Tables[0].Rows[i]["ShopCode"]);
shopReportDto.ShopName = Convert.ToString(ds.Tables[0].Rows[i]["ShopName"]);
shopReportDto.AreaName = Convert.ToString(ds.Tables[0].Rows[i]["AreaName"]);
shopReportDto.Province = Convert.ToString(ds.Tables[0].Rows[i]["Province"]);
shopReportDto.City = Convert.ToString(ds.Tables[0].Rows[i]["City"]);
}
}
#endregion
#region 指标点得分
ds = dataSetList[1];
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
ShopSubjectScoreInfoDto subjectScore = new ShopSubjectScoreInfoDto();
//subjectScore.FullScore = Convert.ToDecimal(ds.Tables[0].Rows[i]["FullScore"]);
subjectScore.Score = Convert.ToString(ds.Tables[0].Rows[i]["Score"]);
subjectScore.ScoreYOrN = Convert.ToString(ds.Tables[0].Rows[i]["ScoreYOrN"]);
subjectScore.LossDesc = Convert.ToString(ds.Tables[0].Rows[i]["LossDesc"]);
// subjectScore.PicName = Convert.ToString(ds.Tables[0].Rows[i]["PicName"]);
subjectScore.SubjectCode = Convert.ToString(ds.Tables[0].Rows[i]["SubjectCode"]);
shopSubjectScoreInfoDtoList.Add(subjectScore);
}
}
#endregion
return(shopReportDto);
}
void bw_DoWork(object sender, DoWorkEventArgs e)
{
string[] shopNames;
int currentShopDtoIndex = 0;
foreach (ShopDto shopDto in _shopDtoList)
{
try
{
object[] arguments = e.Argument as object[];
ShopReportDto shopReportDto = GetShopReportDto(arguments[0] as string, shopDto.ShopCode);
WriteDataToExcel(shopReportDto);
_bw.ReportProgress(currentShopDtoIndex++);
}
catch (Exception ex)
{
shopLeft.Add(shopDto);
WriteErrorLog(shopDto.ShopCode + shopDto.ShopName + ex.Message.ToString());
continue;
}
}
}
private ShopReportDto GetShopReportDto(string projectCode, string shopCode)
{
DataSet[] dataSetList = service.GetShopReportDto(projectCode, shopCode);
ShopReportDto shopReportDto = new ShopReportDto();
//List<AllScoreDto26> allScoreDtoList26 = new List<AllScoreDto26>();
//List<AllScoreDto31> allScoreDtoList31 = new List<AllScoreDto31>();
//List<AllScoreSumDto> allScoreSumDtoList = new List<AllScoreSumDto>();
//List<AllShopScoreDto11> allShopScoreDtoList11 = new List<AllShopScoreDto11>();
//List<AllShopScoreDto16> allShopScoreDtoList16 = new List<AllShopScoreDto16>();
//List<ChaptersScoreDto27To30> chaptersScoreDtoList27To30 = new List<ChaptersScoreDto27To30>();
//List<ChaptersScoreDto32To35> chaptersScoreDtoList32To35 = new List<ChaptersScoreDto32To35>();
//List<ShopChapterScoreDto12To15> shopChapterScoreDtoList12To15 = new List<ShopChapterScoreDto12To15>();
//List<ShopChapterScoreDto17To20> shopChapterScoreDtoList17To20 = new List<ShopChapterScoreDto17To20>();
//List<SubjectsScoreDto> subjectsScoreDtoList = new List<SubjectsScoreDto>();
//shopReportDto.AllScoreDtoList26 = allScoreDtoList26;
//shopReportDto.AllScoreDtoList31 = allScoreDtoList31;
//shopReportDto.AllScoreSumDtoList = allScoreSumDtoList;
//shopReportDto.AllShopScoreDtoList11 = allShopScoreDtoList11;
//shopReportDto.AllShopScoreDtoList16 = allShopScoreDtoList16;
//shopReportDto.ChaptersScoreDtoList27To30 = chaptersScoreDtoList27To30;
//shopReportDto.ChaptersScoreDtoList32To35 = chaptersScoreDtoList32To35;
//shopReportDto.ShopChapterScoreDtoList12To15 = shopChapterScoreDtoList12To15;
//shopReportDto.ShopChapterScoreDtoList17To20 = shopChapterScoreDtoList17To20;
//shopReportDto.SubjectsScoreDtoList = subjectsScoreDtoList;
//#region 封面信息
//DataSet ds = dataSetList[0];
//if (ds.Tables[0].Rows.Count > 0)
//{
// for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
// {
// shopReportDto.ProjectCode = Convert.ToString(ds.Tables[0].Rows[i]["ProjectCode"]);
// shopReportDto.ShopCode = Convert.ToString(ds.Tables[0].Rows[i]["ShopCode"]);
// shopReportDto.ShopName = Convert.ToString(ds.Tables[0].Rows[i]["ShopName"]);
// shopReportDto.AreaName = Convert.ToString(ds.Tables[0].Rows[i]["AreaName"]);
// shopReportDto.Province = Convert.ToString(ds.Tables[0].Rows[i]["Province"]);
// shopReportDto.City = Convert.ToString(ds.Tables[0].Rows[i]["City"]);
// }
//}
//#endregion
//#region 11行
//ds = dataSetList[1];
//if (ds.Tables[0].Rows.Count > 0)
//{
// for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
// {
// AllShopScoreDto11 allshopScore = new AllShopScoreDto11();
// allshopScore.Type = "A";
// allshopScore.ScoreShop = Convert.ToDecimal(ds.Tables[0].Rows[i]["Score"]);
// allShopScoreDtoList11.Add(allshopScore);
// }
//}
//#endregion
//#region 12到15行
//ds = dataSetList[2];
//if (ds.Tables[0].Rows.Count > 0)
//{
// for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
// {
// ShopChapterScoreDto12To15 shopChapterScore = new ShopChapterScoreDto12To15();
// shopChapterScore.Type = "A";
// //shopChapterScore.CharterName = Convert.string(ds.Tables[0].Rows[i]["CharterName"]);
// shopChapterScore.ScoreShop = Convert.ToDecimal(ds.Tables[0].Rows[i]["Score"]);
// shopChapterScore.SubjectPassCount = Convert.ToDecimal(ds.Tables[0].Rows[i]["CNT"]);
// shopChapterScoreDtoList12To15.Add(shopChapterScore);
// }
//}
//#endregion
//#region 16行
//ds = dataSetList[3];
//if (ds.Tables[0].Rows.Count > 0)
//{
// for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
// {
// AllShopScoreDto16 allshopScore = new AllShopScoreDto16();
// allshopScore.Type = "B";
// allshopScore.ScoreShop = Convert.ToDecimal(ds.Tables[0].Rows[i]["Score"]);
// allShopScoreDtoList16.Add(allshopScore);
// }
//}
//#endregion
//#region 17到20行
//ds = dataSetList[4];
//if (ds.Tables[0].Rows.Count > 0)
//{
// for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
// {
// ShopChapterScoreDto17To20 shopChapterScore = new ShopChapterScoreDto17To20();
// shopChapterScore.Type = "B";
// //shopChapterScore.CharterName = Convert.ToDecimal(ds.Tables[0].Rows[i]["CharterName"]);
// shopChapterScore.ScoreShop = Convert.ToDecimal(ds.Tables[0].Rows[i]["Score"]);
// shopChapterScore.SubjectPassCount = Convert.ToDecimal(ds.Tables[0].Rows[i]["CNT"]);
// shopChapterScoreDtoList17To20.Add(shopChapterScore);
// }
//}
//#endregion
//#region 25行
//ds = dataSetList[5];
//if (ds.Tables[0].Rows.Count > 0)
//{
// for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
// {
// AllScoreSumDto allscoreSum = new AllScoreSumDto();
// allscoreSum.ScoreArea_AVG = Convert.ToDecimal(ds.Tables[0].Rows[i]["AreaAVG"]);
// allscoreSum.ScoreArea_MAX = Convert.ToDecimal(ds.Tables[0].Rows[i]["AreaMAX"]);
// allscoreSum.ScoreAll_AVG = Convert.ToDecimal(ds.Tables[0].Rows[i]["AllAVG"]);
// allscoreSum.ScoreAll_MAX = Convert.ToDecimal(ds.Tables[0].Rows[i]["AllMAX"]);
// allScoreSumDtoList.Add(allscoreSum);
// }
//}
//#endregion
//#region 26行
//ds = dataSetList[6];
//if (ds.Tables[0].Rows.Count > 0)
//{
// for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
// {
// AllScoreDto26 allscore = new AllScoreDto26();
// allscore.Type = "A";
// allscore.ScoreArea_AVG = Convert.ToDecimal(ds.Tables[0].Rows[i]["AreaAVG"]);
// allscore.ScoreArea_MAX = Convert.ToDecimal(ds.Tables[0].Rows[i]["AreaMAX"]);
// allscore.ScoreAll_AVG = Convert.ToDecimal(ds.Tables[0].Rows[i]["AllAVG"]);
// allscore.ScoreAll_MAX = Convert.ToDecimal(ds.Tables[0].Rows[i]["AllMAX"]);
// allScoreDtoList26.Add(allscore);
// }
//}
//#endregion
//#region 27到30行
//ds = dataSetList[7];
//if (ds.Tables[0].Rows.Count > 0)
//{
// for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
// {
// ChaptersScoreDto27To30 chapterScore = new ChaptersScoreDto27To30();
// chapterScore.Type = "A";
// chapterScore.ScoreArea_AVG = Convert.ToDecimal(ds.Tables[0].Rows[i]["AreaAVG"]);
// chapterScore.ScoreArea_MAX = Convert.ToDecimal(ds.Tables[0].Rows[i]["AreaMAX"]);
// chapterScore.ScoreAll_AVG = Convert.ToDecimal(ds.Tables[0].Rows[i]["AllAVG"]);
// chapterScore.ScoreAll_MAX = Convert.ToDecimal(ds.Tables[0].Rows[i]["AllMAX"]);
// chaptersScoreDtoList27To30.Add(chapterScore);
// }
//}
//#endregion
//#region 31行
//ds = dataSetList[8];
//if (ds.Tables[0].Rows.Count > 0)
//{
// for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
// {
// AllScoreDto31 allscore = new AllScoreDto31();
// allscore.Type = "B";
// allscore.ScoreArea_AVG = Convert.ToDecimal(ds.Tables[0].Rows[i]["AreaAVG"]);
// allscore.ScoreArea_MAX = Convert.ToDecimal(ds.Tables[0].Rows[i]["AreaMAX"]);
// allscore.ScoreAll_AVG = Convert.ToDecimal(ds.Tables[0].Rows[i]["AllAVG"]);
// allscore.ScoreAll_MAX = Convert.ToDecimal(ds.Tables[0].Rows[i]["AllMAX"]);
// allScoreDtoList31.Add(allscore);
// }
//}
//#endregion
//#region 32到35行
//ds = dataSetList[9];
//if (ds.Tables[0].Rows.Count > 0)
//{
// for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
// {
// ChaptersScoreDto32To35 chapterScore = new ChaptersScoreDto32To35();
// chapterScore.Type = "B";
// chapterScore.ScoreArea_AVG = Convert.ToDecimal(ds.Tables[0].Rows[i]["AreaAVG"]);
// chapterScore.ScoreArea_MAX = Convert.ToDecimal(ds.Tables[0].Rows[i]["AreaMAX"]);
// chapterScore.ScoreAll_AVG = Convert.ToDecimal(ds.Tables[0].Rows[i]["AllAVG"]);
// chapterScore.ScoreAll_MAX = Convert.ToDecimal(ds.Tables[0].Rows[i]["AllMAX"]);
// chaptersScoreDtoList32To35.Add(chapterScore);
// }
//}
//#endregion
//#region 指标点得分
//ds = dataSetList[10];
//if (ds.Tables[0].Rows.Count > 0)
//{
// for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
// {
// SubjectsScoreDto subjectScore = new SubjectsScoreDto();
// subjectScore.FullScore = Convert.ToDecimal(ds.Tables[0].Rows[i]["FullScore"]);
// subjectScore.Score = Convert.ToDecimal(ds.Tables[0].Rows[i]["Score"]);
// subjectScore.LostDesc = Convert.ToString(ds.Tables[0].Rows[i]["LossDesc"]);
// subjectScore.PicName = Convert.ToString(ds.Tables[0].Rows[i]["PicName"]);
// subjectScore.SubjectCode = Convert.ToString(ds.Tables[0].Rows[i]["SubjectCode"]);
// subjectsScoreDtoList.Add(subjectScore);
// }
//}
//#endregion
return(shopReportDto);
}
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);
}
private ShopReportDto GetShopReportDto(string projectCode, string shopCode)
{
ShopReportDto shopReportDto = new ShopReportDto();
#region 迈巴赫
if (!checkBox1.Checked)
{
DataSet[] dataSetList = service.GetShopReportDto_Week(projectCode, shopCode, false);
List <ShopSubjectScoreInfoDto> shopSubjectScoreInfoDtoList = new List <ShopSubjectScoreInfoDto>();
shopReportDto.ShopSubjectScoreInfoDtoList = shopSubjectScoreInfoDtoList;
#region 经销商基本信息
DataSet dsShop = dataSetList[0];
if (dsShop.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsShop.Tables[0].Rows.Count; i++)
{
shopReportDto.ShopCode = Convert.ToString(dsShop.Tables[0].Rows[i]["ShopCode"]);
shopReportDto.ShopName = Convert.ToString(dsShop.Tables[0].Rows[i]["ShopName"]);
shopReportDto.AreaName = Convert.ToString(dsShop.Tables[0].Rows[i]["AreaCode"]);
shopReportDto.City = Convert.ToString(dsShop.Tables[0].Rows[i]["City"]);
}
}
#endregion
#region 体系信息
DataSet dsSubject = dataSetList[1];
if (dsSubject.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsSubject.Tables[0].Rows.Count; i++)
{
ShopSubjectScoreInfoDto shopSubjectScoreInfo = new ShopSubjectScoreInfoDto();
shopSubjectScoreInfo.SubjectCode = Convert.ToString(dsSubject.Tables[0].Rows[i]["SubjectCode"]);
shopSubjectScoreInfo.CheckPoint = Convert.ToString(dsSubject.Tables[0].Rows[i]["CheckPoint"]);
shopSubjectScoreInfo.Score = Convert.ToString(dsSubject.Tables[0].Rows[i]["Score"]);
shopSubjectScoreInfo.LossDesc = Convert.ToString(dsSubject.Tables[0].Rows[i]["LossDesc"]);
shopSubjectScoreInfo.Remark = Convert.ToString(dsSubject.Tables[0].Rows[i]["Remark"]);
shopSubjectScoreInfoDtoList.Add(shopSubjectScoreInfo);
}
}
#endregion
}
#endregion
#region 奔驰
else
{
DataSet[] dataSetList = service.GetShopReportDto_Week(projectCode, shopCode, true);
List <ShopSubjectScoreInfoDto> shopSubjectScoreInfoDtoList = new List <ShopSubjectScoreInfoDto>();
List <SaleContantScoreInfoDto> saleContantScoreInfoList = new List <SaleContantScoreInfoDto>();
List <SaleContantSubjectScoreDto> saleContantSubjectScoreDtoList = new List <SaleContantSubjectScoreDto>();
shopReportDto.SaleContantScoreInfoList = saleContantScoreInfoList;
//shopReportDto.ShopSubjectScoreInfoDtoList = shopSubjectScoreInfoDtoList;
shopReportDto.SaleContantSubjectScoreDtoList = saleContantSubjectScoreDtoList;
shopReportDto.ShopSubjectScoreInfoDtoList = shopSubjectScoreInfoDtoList;
#region 经销商基本信息
DataSet dsShop = dataSetList[0];
if (dsShop.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsShop.Tables[0].Rows.Count; i++)
{
shopReportDto.ShopCode = Convert.ToString(dsShop.Tables[0].Rows[i]["ShopCode"]);
shopReportDto.ShopName = Convert.ToString(dsShop.Tables[0].Rows[i]["ShopName"]);
shopReportDto.AreaName = Convert.ToString(dsShop.Tables[0].Rows[i]["AreaCode"]);
shopReportDto.City = Convert.ToString(dsShop.Tables[0].Rows[i]["City"]);
}
}
#endregion
#region 体系信息
DataSet dsSubject = dataSetList[1];
if (dsSubject.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsSubject.Tables[0].Rows.Count; i++)
{
ShopSubjectScoreInfoDto shopSubjectScoreInfo = new ShopSubjectScoreInfoDto();
shopSubjectScoreInfo.SubjectCode = Convert.ToString(dsSubject.Tables[0].Rows[i]["SubjectCode"]);
shopSubjectScoreInfo.CheckPoint = Convert.ToString(dsSubject.Tables[0].Rows[i]["CheckPoint"]);
shopSubjectScoreInfo.Score = Convert.ToString(dsSubject.Tables[0].Rows[i]["Score"]);
shopSubjectScoreInfo.LossDesc = Convert.ToString(dsSubject.Tables[0].Rows[i]["LossDesc"]);
shopSubjectScoreInfo.Remark = Convert.ToString(dsSubject.Tables[0].Rows[i]["Remark"]);
shopSubjectScoreInfoDtoList.Add(shopSubjectScoreInfo);
}
}
#endregion
#region 销售顾问
DataSet dsSaleContantInfo = dataSetList[2];
if (dsSaleContantInfo.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsSaleContantInfo.Tables[0].Rows.Count; i++)
{
SaleContantScoreInfoDto saleContantScoreInfo = new SaleContantScoreInfoDto();
saleContantScoreInfo.SaleName = Convert.ToString(dsSaleContantInfo.Tables[0].Rows[i]["SaleName"]);
//saleContantScoreInfo.Score = Convert.ToString(dsSaleContantInfo.Tables[0].Rows[i]["Score"]);
saleContantScoreInfoList.Add(saleContantScoreInfo);
}
}
DataSet dsSaleSubjectScoreInfo = dataSetList[3];
if (dsSaleSubjectScoreInfo.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsSaleSubjectScoreInfo.Tables[0].Rows.Count; i++)
{
SaleContantSubjectScoreDto saleSubjectSore = new SaleContantSubjectScoreDto();
saleSubjectSore.SubjectCode = Convert.ToString(dsSaleSubjectScoreInfo.Tables[0].Rows[i]["SubjectCode"]);
saleSubjectSore.SaleName = Convert.ToString(dsSaleSubjectScoreInfo.Tables[0].Rows[i]["SalesConsultant"]);
saleSubjectSore.Score = Convert.ToString(dsSaleSubjectScoreInfo.Tables[0].Rows[i]["Score"]);
saleSubjectSore.Remark = Convert.ToString(dsSaleSubjectScoreInfo.Tables[0].Rows[i]["Remark"]);
saleContantSubjectScoreDtoList.Add(saleSubjectSore);
}
}
#endregion
}
#endregion
return(shopReportDto);
}
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 ShopReportDto GetShopReportDto(string projectCode, string shopCode)
{
ShopReportDto shopReportDto = new ShopReportDto();
#region 迈巴赫
if (!checkBox1.Checked)
{
DataSet[] dataSetList = service.GetShopReportDto(projectCode, shopCode, false);
List <ShopCharterScoreInfoDto> shopCharterScoreInfoDtoList = new List <ShopCharterScoreInfoDto>();
List <ShopSubjectScoreInfoDto> shopSubjectScoreInfoDtoList = new List <ShopSubjectScoreInfoDto>();
shopReportDto.ShopCharterScoreInfoDtoList = shopCharterScoreInfoDtoList;
shopReportDto.ShopSubjectScoreInfoDtoList = shopSubjectScoreInfoDtoList;
#region 经销商基本信息
DataSet dsShop = dataSetList[0];
if (dsShop.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsShop.Tables[0].Rows.Count; i++)
{
shopReportDto.ShopCode = Convert.ToString(dsShop.Tables[0].Rows[i]["ShopCode"]);
shopReportDto.ShopName = Convert.ToString(dsShop.Tables[0].Rows[i]["ShopName"]);
shopReportDto.AreaName = Convert.ToString(dsShop.Tables[0].Rows[i]["AreaCode"]);
shopReportDto.City = Convert.ToString(dsShop.Tables[0].Rows[i]["City"]);
shopReportDto.ShopScore = Convert.ToString(dsShop.Tables[0].Rows[i]["ShopScore"]);
shopReportDto.OrderNO_All = Convert.ToString(dsShop.Tables[0].Rows[i]["OrderNO_All"]);
shopReportDto.OrderNO_Area = Convert.ToString(dsShop.Tables[0].Rows[i]["OrderNO_SmallArea"]);
shopReportDto.SalesContant = Convert.ToString(dsShop.Tables[0].Rows[i]["SaleContant"]);
shopReportDto.SmallAreaScore = Convert.ToString(dsShop.Tables[0].Rows[i]["SmallScore"]);
shopReportDto.BigAreaScore = Convert.ToString(dsShop.Tables[0].Rows[i]["BigScore"]);
shopReportDto.AllScore = Convert.ToString(dsShop.Tables[0].Rows[i]["AllScore"]);
shopReportDto.MustLoss = Convert.ToString(dsShop.Tables[0].Rows[i]["MustLoss"]);
}
}
#endregion
#region 章节信息
DataSet dsCharter = dataSetList[1];
if (dsCharter.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsCharter.Tables[0].Rows.Count; i++)
{
ShopCharterScoreInfoDto shopCharterScoreInfo = new ShopCharterScoreInfoDto();
shopCharterScoreInfo.CharterCode = Convert.ToString(dsCharter.Tables[0].Rows[i]["CharterCode"]);
shopCharterScoreInfo.ShopScore = Convert.ToString(dsCharter.Tables[0].Rows[i]["ShopCharterScore"]);
shopCharterScoreInfo.SmallScore = Convert.ToString(dsCharter.Tables[0].Rows[i]["SmallCharterScore"]);
shopCharterScoreInfo.BigScore = Convert.ToString(dsCharter.Tables[0].Rows[i]["BigCharterScore"]);
shopCharterScoreInfo.AllScore = Convert.ToString(dsCharter.Tables[0].Rows[i]["AllCharterScore"]);
shopCharterScoreInfoDtoList.Add(shopCharterScoreInfo);
}
}
#endregion
#region 体系信息
DataSet dsSubject = dataSetList[2];
if (dsSubject.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsSubject.Tables[0].Rows.Count; i++)
{
ShopSubjectScoreInfoDto shopSubjectScoreInfo = new ShopSubjectScoreInfoDto();
shopSubjectScoreInfo.SubjectCode = Convert.ToString(dsSubject.Tables[0].Rows[i]["SubjectCode"]);
shopSubjectScoreInfo.CheckPoint = Convert.ToString(dsSubject.Tables[0].Rows[i]["CheckPoint"]);
shopSubjectScoreInfo.Score = Convert.ToString(dsSubject.Tables[0].Rows[i]["Score"]);
shopSubjectScoreInfo.LossDesc = Convert.ToString(dsSubject.Tables[0].Rows[i]["LossDesc"]);
shopSubjectScoreInfo.Remark = Convert.ToString(dsSubject.Tables[0].Rows[i]["Remark"]);
shopSubjectScoreInfoDtoList.Add(shopSubjectScoreInfo);
}
}
#endregion
}
#endregion
#region 奔驰
else
{
DataSet[] dataSetList = service.GetShopReportDto(projectCode, shopCode, true);
List <ShopCharterScoreInfoDto> shopCharterScoreInfoDtoList = new List <ShopCharterScoreInfoDto>();
List <ShopSubjectScoreInfoDto> shopSubjectScoreInfoDtoList = new List <ShopSubjectScoreInfoDto>();
List <BDCORRepScoreInfoDto> bdcOrrepScoreInfoList = new List <BDCORRepScoreInfoDto>();
List <ShopSubjectScoreInfo_BDCOrRepDto> bdcShopSubjectScoreInfoList = new List <ShopSubjectScoreInfo_BDCOrRepDto>();
List <SaleContantScoreInfoDto> saleContantScoreInfoList = new List <SaleContantScoreInfoDto>();
List <SaleContantScoreInfo_AreaDto> saleContantScoreInfo_AreaList = new List <SaleContantScoreInfo_AreaDto>();
List <SaleContantCharterScoreInfoDto> saleContantCharterScoreInfoDtoList = new List <SaleContantCharterScoreInfoDto>();
List <SaleAreaCharterScoreDto> saleAreaCharterScoreDtoList = new List <SaleAreaCharterScoreDto>();
List <SaleContantSubjectScoreDto> saleContantSubjectScoreDtoList = new List <SaleContantSubjectScoreDto>();
shopReportDto.ShopCharterScoreInfoDtoList = shopCharterScoreInfoDtoList;
shopReportDto.ShopSubjectScoreInfoDtoList = shopSubjectScoreInfoDtoList;
shopReportDto.BDCORRepScoreInfoDtoList = bdcOrrepScoreInfoList;
shopReportDto.BDCShopSubjectScoreInfoList = bdcShopSubjectScoreInfoList;
shopReportDto.SaleContantScoreInfoList = saleContantScoreInfoList;
shopReportDto.SaleContantScoreInfo_AreaList = saleContantScoreInfo_AreaList;
shopReportDto.SaleContantCharterScoreInfoDtoList = saleContantCharterScoreInfoDtoList;
shopReportDto.SaleAreaCharterScoreDtoList = saleAreaCharterScoreDtoList;
shopReportDto.SaleContantSubjectScoreDtoList = saleContantSubjectScoreDtoList;
#region 经销商基本信息
DataSet dsShop = dataSetList[0];
if (dsShop.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsShop.Tables[0].Rows.Count; i++)
{
shopReportDto.ShopCode = Convert.ToString(dsShop.Tables[0].Rows[i]["ShopCode"]);
shopReportDto.ShopName = Convert.ToString(dsShop.Tables[0].Rows[i]["ShopName"]);
shopReportDto.AreaName = Convert.ToString(dsShop.Tables[0].Rows[i]["AreaCode"]);
shopReportDto.City = Convert.ToString(dsShop.Tables[0].Rows[i]["City"]);
shopReportDto.ShopScore = Convert.ToString(dsShop.Tables[0].Rows[i]["ShopScore"]);
shopReportDto.OrderNO_All = Convert.ToString(dsShop.Tables[0].Rows[i]["OrderNO_All"]);
shopReportDto.OrderNO_Area = Convert.ToString(dsShop.Tables[0].Rows[i]["OrderNO_SmallArea"]);
shopReportDto.SalesContant = Convert.ToString(dsShop.Tables[0].Rows[i]["SaleContant"]);
shopReportDto.SmallAreaScore = Convert.ToString(dsShop.Tables[0].Rows[i]["SmallScore"]);
shopReportDto.BigAreaScore = Convert.ToString(dsShop.Tables[0].Rows[i]["BigScore"]);
shopReportDto.AllScore = Convert.ToString(dsShop.Tables[0].Rows[i]["AllScore"]);
shopReportDto.MustLoss = Convert.ToString(dsShop.Tables[0].Rows[i]["MustLoss"]);
}
}
#endregion
#region 章节信息
DataSet dsCharter = dataSetList[1];
if (dsCharter.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsCharter.Tables[0].Rows.Count; i++)
{
ShopCharterScoreInfoDto shopCharterScoreInfo = new ShopCharterScoreInfoDto();
shopCharterScoreInfo.CharterCode = Convert.ToString(dsCharter.Tables[0].Rows[i]["CharterCode"]);
shopCharterScoreInfo.ShopScore = Convert.ToString(dsCharter.Tables[0].Rows[i]["ShopCharterScore"]);
shopCharterScoreInfo.SmallScore = Convert.ToString(dsCharter.Tables[0].Rows[i]["SmallCharterScore"]);
shopCharterScoreInfo.BigScore = Convert.ToString(dsCharter.Tables[0].Rows[i]["BigCharterScore"]);
shopCharterScoreInfo.AllScore = Convert.ToString(dsCharter.Tables[0].Rows[i]["AllCharterScore"]);
shopCharterScoreInfoDtoList.Add(shopCharterScoreInfo);
}
}
#endregion
#region 体系信息
DataSet dsSubject = dataSetList[2];
if (dsSubject.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsSubject.Tables[0].Rows.Count; i++)
{
ShopSubjectScoreInfoDto shopSubjectScoreInfo = new ShopSubjectScoreInfoDto();
shopSubjectScoreInfo.SubjectCode = Convert.ToString(dsSubject.Tables[0].Rows[i]["SubjectCode"]);
shopSubjectScoreInfo.CheckPoint = Convert.ToString(dsSubject.Tables[0].Rows[i]["CheckPoint"]);
shopSubjectScoreInfo.Score = Convert.ToString(dsSubject.Tables[0].Rows[i]["Score"]);
shopSubjectScoreInfo.LossDesc = Convert.ToString(dsSubject.Tables[0].Rows[i]["LossDesc"]);
shopSubjectScoreInfo.Remark = Convert.ToString(dsSubject.Tables[0].Rows[i]["Remark"]);
shopSubjectScoreInfoDtoList.Add(shopSubjectScoreInfo);
}
}
#endregion
//#region BDC
//DataSet dsBDCScoreInfo = dataSetList[3];
//if (dsBDCScoreInfo.Tables[0].Rows.Count > 0)
//{
// for (int i = 0; i < dsBDCScoreInfo.Tables[0].Rows.Count; i++)
// {
// BDCORRepScoreInfoDto bdcScoreInfo = new BDCORRepScoreInfoDto();
// bdcScoreInfo.SaleName = Convert.ToString(dsBDCScoreInfo.Tables[0].Rows[i]["SaleName"]);
// bdcScoreInfo.SalesType = Convert.ToString(dsBDCScoreInfo.Tables[0].Rows[i]["SalesType"]);
// bdcScoreInfo.Score = Convert.ToString(dsBDCScoreInfo.Tables[0].Rows[i]["Score"]);
// bdcScoreInfo.SmallAreaScore = Convert.ToString(dsBDCScoreInfo.Tables[0].Rows[i]["SmallScore"]);
// bdcScoreInfo.BigAreaScore = Convert.ToString(dsBDCScoreInfo.Tables[0].Rows[i]["BigScore"]);
// bdcScoreInfo.AllScore = Convert.ToString(dsBDCScoreInfo.Tables[0].Rows[i]["AllScore"]);
// bdcOrrepScoreInfoList.Add(bdcScoreInfo);
// }
//}
//DataSet dsBDCSubjectScoreInfo = dataSetList[4];
//if (dsBDCSubjectScoreInfo.Tables[0].Rows.Count > 0)
//{
// for (int i = 0; i < dsBDCSubjectScoreInfo.Tables[0].Rows.Count; i++)
// {
// ShopSubjectScoreInfo_BDCOrRepDto bdcSubjectScoreInfo = new ShopSubjectScoreInfo_BDCOrRepDto();
// bdcSubjectScoreInfo.SubjectCode = Convert.ToString(dsBDCSubjectScoreInfo.Tables[0].Rows[i]["SubjectCode"]);
// bdcSubjectScoreInfo.Score = Convert.ToString(dsBDCSubjectScoreInfo.Tables[0].Rows[i]["Score"]);
// bdcSubjectScoreInfo.LossDesc = Convert.ToString(dsBDCSubjectScoreInfo.Tables[0].Rows[i]["LossDesc"]);
// bdcSubjectScoreInfo.Remark = Convert.ToString(dsBDCSubjectScoreInfo.Tables[0].Rows[i]["Remark"]);
// bdcShopSubjectScoreInfoList.Add(bdcSubjectScoreInfo);
// }
//}
//#endregion
#region 销售顾问
DataSet dsSaleContantInfo = dataSetList[5];
if (dsSaleContantInfo.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsSaleContantInfo.Tables[0].Rows.Count; i++)
{
SaleContantScoreInfoDto saleContantScoreInfo = new SaleContantScoreInfoDto();
saleContantScoreInfo.SaleName = Convert.ToString(dsSaleContantInfo.Tables[0].Rows[i]["SaleName"]);
saleContantScoreInfo.Score = Convert.ToString(dsSaleContantInfo.Tables[0].Rows[i]["Score"]);
saleContantScoreInfoList.Add(saleContantScoreInfo);
}
}
DataSet dsSaleContantInfo_Area = dataSetList[6];
if (dsSaleContantInfo_Area.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsSaleContantInfo_Area.Tables[0].Rows.Count; i++)
{
SaleContantScoreInfo_AreaDto saleContantScoreInfo_Area = new SaleContantScoreInfo_AreaDto();
saleContantScoreInfo_Area.SmallAreaScore = Convert.ToString(dsSaleContantInfo_Area.Tables[0].Rows[i]["SmallScore"]);
saleContantScoreInfo_Area.BigAreaScore = Convert.ToString(dsSaleContantInfo_Area.Tables[0].Rows[i]["BigScore"]);
saleContantScoreInfo_Area.AllScore = Convert.ToString(dsSaleContantInfo_Area.Tables[0].Rows[i]["AllScore"]);
saleContantScoreInfo_AreaList.Add(saleContantScoreInfo_Area);
}
}
DataSet dsSaleContantCharterScore = dataSetList[7];
if (dsSaleContantCharterScore.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsSaleContantCharterScore.Tables[0].Rows.Count; i++)
{
SaleContantCharterScoreInfoDto saleContantCharterScoreInfo = new SaleContantCharterScoreInfoDto();
saleContantCharterScoreInfo.CharterCode = Convert.ToString(dsSaleContantCharterScore.Tables[0].Rows[i]["CharterCode"]);
saleContantCharterScoreInfo.SaleName = Convert.ToString(dsSaleContantCharterScore.Tables[0].Rows[i]["SaleName"]);
saleContantCharterScoreInfo.Score = Convert.ToString(dsSaleContantCharterScore.Tables[0].Rows[i]["Score"]);
saleContantCharterScoreInfoDtoList.Add(saleContantCharterScoreInfo);
}
}
DataSet dsSaleAreaCharterScore = dataSetList[8];
if (dsSaleAreaCharterScore.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsSaleAreaCharterScore.Tables[0].Rows.Count; i++)
{
SaleAreaCharterScoreDto saleAreaCharterScore = new SaleAreaCharterScoreDto();
saleAreaCharterScore.CharterCode = Convert.ToString(dsSaleAreaCharterScore.Tables[0].Rows[i]["CharterCode"]);
saleAreaCharterScore.SmallCharterScore = Convert.ToString(dsSaleAreaCharterScore.Tables[0].Rows[i]["SmallCharterScore"]);
saleAreaCharterScore.BigCharterScore = Convert.ToString(dsSaleAreaCharterScore.Tables[0].Rows[i]["BigCharterScore"]);
saleAreaCharterScore.AllCharterScore = Convert.ToString(dsSaleAreaCharterScore.Tables[0].Rows[i]["AllCharterScore"]);
saleAreaCharterScoreDtoList.Add(saleAreaCharterScore);
}
}
DataSet dsSaleSubjectScoreInfo = dataSetList[9];
if (dsSaleSubjectScoreInfo.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsSaleSubjectScoreInfo.Tables[0].Rows.Count; i++)
{
SaleContantSubjectScoreDto saleSubjectSore = new SaleContantSubjectScoreDto();
saleSubjectSore.SubjectCode = Convert.ToString(dsSaleSubjectScoreInfo.Tables[0].Rows[i]["SubjectCode"]);
saleSubjectSore.SaleName = Convert.ToString(dsSaleSubjectScoreInfo.Tables[0].Rows[i]["SalesConsultant"]);
saleSubjectSore.Score = Convert.ToString(dsSaleSubjectScoreInfo.Tables[0].Rows[i]["Score"]);
saleSubjectSore.Remark = Convert.ToString(dsSaleSubjectScoreInfo.Tables[0].Rows[i]["Remark"]);
saleContantSubjectScoreDtoList.Add(saleSubjectSore);
}
}
#endregion
}
#endregion
return(shopReportDto);
}
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 ShopReportDto GetShopReportDto(string projectCode, string shopCode)
{
DataSet[] dataSetList = service.GetShopReportDto(projectCode, shopCode);
ShopReportDto shopReportDto = new ShopReportDto();
List <ShopCharterScoreInfoDto> shopCharterScoreInfoDtoList = new List <ShopCharterScoreInfoDto>();
List <ShopSubjectScoreInfoDto> shopSubjectScoreInfoDtoList = new List <ShopSubjectScoreInfoDto>();
List <SaleContantScoreInfoDto> saleContantScoreInfoList = new List <SaleContantScoreInfoDto>();
List <SaleContantSubjectScoreDto> saleContantSubjectScoreDtoList = new List <SaleContantSubjectScoreDto>();
shopReportDto.SaleContantScoreInfoList = saleContantScoreInfoList;
shopReportDto.SaleContantSubjectScoreDtoList = saleContantSubjectScoreDtoList;
shopReportDto.ShopCharterScoreInfoDtoList = shopCharterScoreInfoDtoList;
shopReportDto.ShopSubjectScoreInfoDtoList = shopSubjectScoreInfoDtoList;
#region 经销商基本信息
DataSet dsShop = dataSetList[0];
if (dsShop.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsShop.Tables[0].Rows.Count; i++)
{
shopReportDto.ShopCode = Convert.ToString(dsShop.Tables[0].Rows[i]["ShopCode"]);
shopReportDto.ShopName = Convert.ToString(dsShop.Tables[0].Rows[i]["ShopName"]);
shopReportDto.AreaName = Convert.ToString(dsShop.Tables[0].Rows[i]["AreaCode"]);
shopReportDto.City = Convert.ToString(dsShop.Tables[0].Rows[i]["City"]);
shopReportDto.ShopScore = Convert.ToString(dsShop.Tables[0].Rows[i]["ShopScore"]);
shopReportDto.OrderNO_All = Convert.ToString(dsShop.Tables[0].Rows[i]["OrderNO_All"]);
shopReportDto.OrderNO_Area = Convert.ToString(dsShop.Tables[0].Rows[i]["OrderNO_SmallArea"]);
shopReportDto.SalesContant = Convert.ToString(dsShop.Tables[0].Rows[i]["SaleContant"]);
shopReportDto.SmallAreaScore = Convert.ToString(dsShop.Tables[0].Rows[i]["SmallScore"]);
shopReportDto.BigAreaScore = Convert.ToString(dsShop.Tables[0].Rows[i]["BigScore"]);
shopReportDto.AllScore = Convert.ToString(dsShop.Tables[0].Rows[i]["AllScore"]);
shopReportDto.MustLoss = Convert.ToString(dsShop.Tables[0].Rows[i]["MustLoss"]);
}
}
#endregion
#region 章节信息
DataSet dsCharter = dataSetList[1];
if (dsCharter.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsCharter.Tables[0].Rows.Count; i++)
{
ShopCharterScoreInfoDto shopCharterScoreInfo = new ShopCharterScoreInfoDto();
shopCharterScoreInfo.CharterCode = Convert.ToString(dsCharter.Tables[0].Rows[i]["CharterCode"]);
shopCharterScoreInfo.ShopScore = Convert.ToString(dsCharter.Tables[0].Rows[i]["ShopCharterScore"]);
shopCharterScoreInfo.SmallScore = Convert.ToString(dsCharter.Tables[0].Rows[i]["SmallCharterScore"]);
shopCharterScoreInfo.BigScore = Convert.ToString(dsCharter.Tables[0].Rows[i]["BigCharterScore"]);
shopCharterScoreInfo.AllScore = Convert.ToString(dsCharter.Tables[0].Rows[i]["AllCharterScore"]);
shopCharterScoreInfoDtoList.Add(shopCharterScoreInfo);
}
}
#endregion
#region 体系信息
DataSet dsSubject = dataSetList[2];
if (dsSubject.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsSubject.Tables[0].Rows.Count; i++)
{
ShopSubjectScoreInfoDto shopSubjectScoreInfo = new ShopSubjectScoreInfoDto();
shopSubjectScoreInfo.SubjectCode = Convert.ToString(dsSubject.Tables[0].Rows[i]["SubjectCode"]);
shopSubjectScoreInfo.CheckPoint = Convert.ToString(dsSubject.Tables[0].Rows[i]["CheckPoint"]);
shopSubjectScoreInfo.Score = Convert.ToString(dsSubject.Tables[0].Rows[i]["Score"]);
shopSubjectScoreInfo.LossDesc = Convert.ToString(dsSubject.Tables[0].Rows[i]["LossDesc"]);
shopSubjectScoreInfo.Remark = Convert.ToString(dsSubject.Tables[0].Rows[i]["Remark"]);
shopSubjectScoreInfoDtoList.Add(shopSubjectScoreInfo);
}
}
#endregion
#region 销售顾问
DataSet dsSaleContantInfo = dataSetList[3];
if (dsSaleContantInfo.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsSaleContantInfo.Tables[0].Rows.Count; i++)
{
SaleContantScoreInfoDto saleContantScoreInfo = new SaleContantScoreInfoDto();
saleContantScoreInfo.SaleName = Convert.ToString(dsSaleContantInfo.Tables[0].Rows[i]["SaleName"]);
//saleContantScoreInfo.Score = Convert.ToString(dsSaleContantInfo.Tables[0].Rows[i]["Score"]);
saleContantScoreInfoList.Add(saleContantScoreInfo);
}
}
DataSet dsSaleSubjectScoreInfo = dataSetList[4];
if (dsSaleSubjectScoreInfo.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsSaleSubjectScoreInfo.Tables[0].Rows.Count; i++)
{
SaleContantSubjectScoreDto saleSubjectSore = new SaleContantSubjectScoreDto();
saleSubjectSore.SubjectCode = Convert.ToString(dsSaleSubjectScoreInfo.Tables[0].Rows[i]["SubjectCode"]);
saleSubjectSore.SaleName = Convert.ToString(dsSaleSubjectScoreInfo.Tables[0].Rows[i]["SalesConsultant"]);
saleSubjectSore.Score = Convert.ToString(dsSaleSubjectScoreInfo.Tables[0].Rows[i]["Score"]);
saleSubjectSore.Remark = Convert.ToString(dsSaleSubjectScoreInfo.Tables[0].Rows[i]["Remark"]);
saleContantSubjectScoreDtoList.Add(saleSubjectSore);
}
}
#endregion
return(shopReportDto);
}
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);
}
