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