private void simpleButton1_Click(object sender, EventArgs e)
{
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(btnModule.Text);
Worksheet worksheet_FengMian = workbook.Worksheets["AB"] as Worksheet;
for (int i = 2; i < 500; i++)
{
try
{
string shopCode = msExcelUtil.GetCellValue(worksheet_FengMian, "B", i);
string projectCode = msExcelUtil.GetCellValue(worksheet_FengMian, "A", i);
if (!string.IsNullOrEmpty(shopCode) && !string.IsNullOrEmpty(projectCode))
{
string type = msExcelUtil.GetCellValue(worksheet_FengMian, "C", i);
string checkUser = msExcelUtil.GetCellValue(worksheet_FengMian, "D", i);
//DateTime? checkDate = Convert.ToDateTime(msExcelUtil.GetCellValue(worksheet_FengMian, "E", i));
webService.SaveShopExamType(projectCode, shopCode, type, checkUser, DateTime.Now);
}
}
catch (Exception ex)
{
CommonHandler.ShowMessage(MessageType.Information, "第" + i.ToString() + "行数据格式错误");
continue;
}
}
CommonHandler.ShowMessage(MessageType.Information, "上传完毕");
SearchResult();
}
private void simpleButton1_Click(object sender, EventArgs e)
{
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(btnModule.Text);
Worksheet worksheet_FengMian = workbook.Worksheets["Shop"] as Worksheet;
for (int i = 2; i < 500; i++)
{
string shopCode = msExcelUtil.GetCellValue(worksheet_FengMian, "A", i);
if (!string.IsNullOrEmpty(shopCode))
{
string shopName = msExcelUtil.GetCellValue(worksheet_FengMian, "B", i);
string areaCode = msExcelUtil.GetCellValue(worksheet_FengMian, "E", i);
string province = msExcelUtil.GetCellValue(worksheet_FengMian, "F", i);
string city = msExcelUtil.GetCellValue(worksheet_FengMian, "G", i);
string group = msExcelUtil.GetCellValue(worksheet_FengMian, "H", i);
string salesOrAftersales = msExcelUtil.GetCellValue(worksheet_FengMian, "I", i);
string carType = msExcelUtil.GetCellValue(worksheet_FengMian, "J", i);
try
{
webService.SaveShop(areaCode, "", shopCode, shopName, true, this.UserInfoDto.UserID, province, city, salesOrAftersales, group, carType);
}
catch (Exception)
{
continue;
}
}
}
CommonHandler.ShowMessage(MessageType.Information, "上传完毕");
SearchShop();
}
private void simpleButton1_Click(object sender, EventArgs e)
{
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(btnModule.Text);
Worksheet worksheet_FengMian = workbook.Worksheets["Shop"] as Worksheet;
for (int i = 2; i < 1000; i++)
{
string shopCode = msExcelUtil.GetCellValue(worksheet_FengMian, "A", i).Trim();
string shopCode1 = msExcelUtil.GetCellValue(worksheet_FengMian, "B", i).Trim();
if (!string.IsNullOrEmpty(shopCode))
{
string shopName = msExcelUtil.GetCellValue(worksheet_FengMian, "G", i).Trim();
string province = msExcelUtil.GetCellValue(worksheet_FengMian, "E", i);
string city = msExcelUtil.GetCellValue(worksheet_FengMian, "F", i);
string areaCode_sale = msExcelUtil.GetCellValue(worksheet_FengMian, "C", i);
string areaCode_after = msExcelUtil.GetCellValue(worksheet_FengMian, "D", i);
webService.SaveShop(areaCode_sale, areaCode_after, shopCode, shopName, true, this.UserInfoDto.UserID, province, city);
}
//else
//{
// if (!string.IsNullOrEmpty(shopCode1))
// {
// string shopName = msExcelUtil.GetCellValue(worksheet_FengMian, "G", i).Trim();
// string province = msExcelUtil.GetCellValue(worksheet_FengMian, "E", i);
// string city = msExcelUtil.GetCellValue(worksheet_FengMian, "F", i);
// string areaCode_sale = msExcelUtil.GetCellValue(worksheet_FengMian, "C", i).Trim();
// string areaCode_after = msExcelUtil.GetCellValue(worksheet_FengMian, "D", i).Trim();
// webService.SaveShop(areaCode_sale, areaCode_after, shopCode1, shopName, true, this.UserInfoDto.UserID, province, city);
// }
//}
CommonHandler.ShowMessage(MessageType.Information, "上传完毕");
}
}
public static void Export(string id, string path, string userid)
{
MSExcelUtil msExcelUtil = new MSExcelUtil();
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path);
Worksheet sheet = workbook.ActiveSheet;
try
{
service.ConstractTemplateDtlDelete(new ConstractTemplateDtl()
{
ConstractTemplateId = int.Parse(id)
});
int maxRow = 100000;
int startRowIndex = 2;
while (true)
{
string type = msExcelUtil.GetCellValue(sheet, 1, startRowIndex);
string content = msExcelUtil.GetCellValue(sheet, 2, startRowIndex);
if (string.IsNullOrWhiteSpace(type))
{
break;
}
type = type.Trim();
ConstractTemplateDtl constractTemplateDtl = new ConstractTemplateDtl();
constractTemplateDtl.ConstractTemplateId = int.Parse(id);
constractTemplateDtl.InDateTime = DateTime.Now;
constractTemplateDtl.InUserId = userid;
constractTemplateDtl.OrderNO = 0;
if (fixContentArray.Contains(type))
{
constractTemplateDtl.ContentType = "固定内容";
constractTemplateDtl.ContentType2 = type;
}
else
{
constractTemplateDtl.ContentType = type;
constractTemplateDtl.TemplateContent = content;
}
service.ConstractTemplateDtlSave(constractTemplateDtl);
startRowIndex++;
if (startRowIndex > maxRow)
{
break;
}
}
}
catch (Exception ex)
{
Utils.log(ex.ToString());
}
msExcelUtil.dispose();
}
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 btnCharterSale_Click(object sender, EventArgs e)
{
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(btnModule.Text);
Worksheet worksheet_FengMian = workbook.Worksheets["经销商"] as Worksheet;
string projectCode = CommonHandler.GetComboBoxSelectedValue(cboProject).ToString();
for (int i = 2; i < 10000; i++)
{
string userId = msExcelUtil.GetCellValue(worksheet_FengMian, 1, i).ToString();
if (string.IsNullOrEmpty(userId))
{
break;
}
if (!string.IsNullOrEmpty(userId))
{
string shopCode = msExcelUtil.GetCellValue(worksheet_FengMian, 2, i).ToString();
webService.SaveUserInfoShop(projectCode, userId, shopCode, UserInfoDto.UserID, StatusTypes.INSERT);
}
}
SearchResult();
CommonHandler.ShowMessage(MessageType.Information, "上传完毕");
}
private void simpleButton2_Click(object sender, EventArgs e)
{
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(buttonEdit1.Text);
Worksheet worksheet_FengMian = workbook.Worksheets["Sheet1"] as Worksheet;
//string projectCode = CommonHandler.GetComboBoxSelectedValue(cboProjects).ToString();
for (int i = 2; i < 1000; i++)
{
string projectCode = msExcelUtil.GetCellValue(worksheet_FengMian, "A", i).ToString();
string shopCode = msExcelUtil.GetCellValue(worksheet_FengMian, "B", i).ToString();
if (string.IsNullOrEmpty(shopCode))
{
break;
}
if (!string.IsNullOrEmpty(shopCode))
{
string salesName = msExcelUtil.GetCellValue(worksheet_FengMian, "C", i).ToString();
string salesType = msExcelUtil.GetCellValue(worksheet_FengMian, "D", i).ToString();
string inUserName = msExcelUtil.GetCellValue(worksheet_FengMian, "E", i).ToString();
// string score = msExcelUtil.GetCellValue(worksheet_FengMian, "F", i).ToString();
//service.UploadSalesContantInfo(projectCode, shopCode, salesType, salesName, score, "", orderNO_All, orderNO_SmallArea, this.UserInfoDto.UserID);
for (int j = 6; j < 100; j++)
{
string subjectCode = msExcelUtil.GetCellValue(worksheet_FengMian, j, 1).ToString();
string subjectScore = msExcelUtil.GetCellValue(worksheet_FengMian, j, i).ToString();
string score = "";
string lossDesc = "";
if (subjectScore.Trim() == "/")
{
score = "9999";
lossDesc = "";
}
else if (isNumberic(subjectScore))
{
score = subjectScore;
lossDesc = "";
}
else
{
score = "";
lossDesc = subjectScore;
}
if (!string.IsNullOrEmpty(subjectCode) && !string.IsNullOrEmpty(subjectScore))
{
service.SaveSalesConsultant_Upload(projectCode, shopCode, subjectCode, salesName, score, lossDesc, inUserName, salesType);
}
}
}
}
CommonHandler.ShowMessage(MessageType.Information, "上传完毕");
}
private void simpleButton1_Click(object sender, EventArgs e)
{
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(btnModule.Text);
Worksheet worksheet_FengMian = workbook.Worksheets["Area"] as Worksheet;
for (int i = 2; i < 90; i++)
{
string areaCode = msExcelUtil.GetCellValue(worksheet_FengMian, "A", i);
if (!string.IsNullOrEmpty(areaCode))
{
string areaName = msExcelUtil.GetCellValue(worksheet_FengMian, "B", i);
string areaType = msExcelUtil.GetCellValue(worksheet_FengMian, "C", i);
string upperAreaCode = msExcelUtil.GetCellValue(worksheet_FengMian, "D", i);
webService.SaveArea(areaCode, areaName, upperAreaCode, areaType, this.UserInfoDto.UserID);
}
}
CommonHandler.ShowMessage(MessageType.Information, "上传完毕");
}
private void simpleButton1_Click(object sender, EventArgs e)
{
//Workbook workbook = msExcelUtil.OpenExcelByMSExcel(btnModule.Text);
//Worksheet worksheet_FengMian = workbook.Worksheets["Link"] as Worksheet;
//Worksheet worksheet_Subjects = workbook.Worksheets["Subjects"] as Worksheet;
//for (int i = 2; i < 146; i++)
//{
// string subjectCode = msExcelUtil.GetCellValue(worksheet_Subjects, "A", i);
// if (!string.IsNullOrEmpty(subjectCode))
// {
// string linkCode = msExcelUtil.GetCellValue(worksheet_Subjects, "B", i);
// string checkItem = msExcelUtil.GetCellValue(worksheet_Subjects, "C", i);
// string standard = msExcelUtil.GetCellValue(worksheet_Subjects, "D", i);
// decimal weight = Convert.ToDecimal(msExcelUtil.GetCellValue(worksheet_Subjects, "E", i));
// int orderNo = Convert.ToInt32(msExcelUtil.GetCellValue(worksheet_Subjects, "F", i));
// webService.Area_SubjectSave("201601", "0", checkItem, standard, orderNo, weight, linkCode, "sysadmin", subjectCode);
// }
//}
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(btnModule.Text);
Worksheet worksheet_FengMian = workbook.Worksheets["Subjects"] as Worksheet;
for (int i = 2; i < 300; i++)
{
string subjectCode = msExcelUtil.GetCellValue(worksheet_FengMian, "B", i);
if (!string.IsNullOrEmpty(subjectCode))
{
string projectCode = msExcelUtil.GetCellValue(worksheet_FengMian, "A", i);
int orderNo = 0;
decimal weight = 0;
if (!string.IsNullOrEmpty(msExcelUtil.GetCellValue(worksheet_FengMian, "C", i)))
{
orderNo = Convert.ToInt32(msExcelUtil.GetCellValue(worksheet_FengMian, "C", i));
}
if (!string.IsNullOrEmpty(msExcelUtil.GetCellValue(worksheet_FengMian, "D", i)))
{
weight = Convert.ToDecimal(msExcelUtil.GetCellValue(worksheet_FengMian, "D", i));
}
webService.UpDateSubject(projectCode, subjectCode, orderNo, weight);
}
}
CommonHandler.ShowMessage(MessageType.Information, "上传完毕");
}
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 btnAllCharter_Click(object sender, EventArgs e)
{
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(btnModule.Text);
Worksheet worksheet_FengMian = workbook.Worksheets["全国环节得分"] as Worksheet;
string projectCode = CommonHandler.GetComboBoxSelectedValue(cboProjects).ToString();
string allScore = msExcelUtil.GetCellValue(worksheet_FengMian, "A", 2).ToString();
string bdcScore = msExcelUtil.GetCellValue(worksheet_FengMian, "B", 2).ToString();
string receptionistScore = msExcelUtil.GetCellValue(worksheet_FengMian, "C", 2).ToString();
string saleScore = msExcelUtil.GetCellValue(worksheet_FengMian, "D", 2).ToString();
service.UploadAllScore(projectCode, allScore, bdcScore, receptionistScore, saleScore, "", this.UserInfoDto.UserID);
for (int i = 5; i < 20; i++)
{
string charterCode = msExcelUtil.GetCellValue(worksheet_FengMian, i, 1).ToString();
if (!string.IsNullOrEmpty(charterCode))
{
string CharterScore = msExcelUtil.GetCellValue(worksheet_FengMian, i, 2).ToString();
service.UploadAllCharterScore(projectCode, charterCode, CharterScore, "", this.UserInfoDto.UserID);
}
}
CommonHandler.ShowMessage(MessageType.Information, "上传完毕");
}
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 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 button2_Click(object sender, EventArgs e)
{
openFileDialog1.InitialDirectory = @"C:\Users\huohaitao\Desktop\导入需求书";
openFileDialog1.Multiselect = true;
if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
using (var db = new PurchaseEntities())
{
foreach (string filename in openFileDialog1.FileNames)
{
textBox1.Text = filename;
MSExcelUtil msExcelUtil = new MSExcelUtil();
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(filename);
Worksheet sheet = workbook.ActiveSheet;
int rowIndex = 2;
string type = null;
do
{
type = msExcelUtil.GetCellValue(sheet, 1, rowIndex);
string content = msExcelUtil.GetCellValue(sheet, 2, rowIndex);
string childChk = msExcelUtil.GetCellValue(sheet, 3, rowIndex);
if (string.IsNullOrEmpty(content))
{
continue;
}
string[] items = content.Split('/');
foreach (string item in items)
{
HiddenCode code = new HiddenCode();
code.Type = type.Trim();
code.Code = item.Trim();
code.Name = item.Trim();
if (item == "不限制" || item == "不涉及")
{
code.InDateTime = DateTime.MaxValue;
}
else
{
code.InDateTime = DateTime.Now;
}
code.InUserId = "sysadmin";
code.UseChk = true;
if (db.HiddenCode.Find(type, item) == null)
{
db.HiddenCode.Add(code);
}
if (db.HiddenCodeType.Where(x => x.TypName == code.Type).Count() == 0)
{
db.HiddenCodeType.Add(new HiddenCodeType()
{
InDateTime = DateTime.Now,
InUserId = "sysadmin",
TypName = code.Type,
ChildChk = string.IsNullOrEmpty(childChk) ? false : Convert.ToBoolean(childChk),
});
db.SaveChanges();
}
}
rowIndex++;
} while (!string.IsNullOrEmpty(type));
try
{
db.SaveChanges();
}
catch (DbEntityValidationException dex)
{
MessageBox.Show(dex.Message);
}
MessageBox.Show("导入完成");
}
}
}
}
private void btnAllCharter_Click(object sender, EventArgs e)
{
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(btnModule.Text);
Worksheet worksheet_FengMian = workbook.Worksheets["全国环节得分"] as Worksheet;
string projectCode = CommonHandler.GetComboBoxSelectedValue(cboProjects).ToString();
string allScore = msExcelUtil.GetCellValue(worksheet_FengMian, "A", 2).ToString();
string CharterScore_A = msExcelUtil.GetCellValue(worksheet_FengMian, "B", 2).ToString();
string CharterScore_B = msExcelUtil.GetCellValue(worksheet_FengMian, "C", 2).ToString();
string CharterScore_C = msExcelUtil.GetCellValue(worksheet_FengMian, "D", 2).ToString();
string CharterScore_D = msExcelUtil.GetCellValue(worksheet_FengMian, "E", 2).ToString();
string CharterScore_E = msExcelUtil.GetCellValue(worksheet_FengMian, "F", 2).ToString();
string CharterScore_F = msExcelUtil.GetCellValue(worksheet_FengMian, "G", 2).ToString();
string CharterScore_G = msExcelUtil.GetCellValue(worksheet_FengMian, "H", 2).ToString();
service.UploadAllScore(projectCode, allScore, "", this.UserInfoDto.UserID);
service.UploadAllCharterScore(projectCode, "A", CharterScore_A, "", this.UserInfoDto.UserID);
service.UploadAllCharterScore(projectCode, "B", CharterScore_B, "", this.UserInfoDto.UserID);
service.UploadAllCharterScore(projectCode, "C", CharterScore_C, "", this.UserInfoDto.UserID);
service.UploadAllCharterScore(projectCode, "D", CharterScore_D, "", this.UserInfoDto.UserID);
service.UploadAllCharterScore(projectCode, "E", CharterScore_E, "", this.UserInfoDto.UserID);
service.UploadAllCharterScore(projectCode, "F", CharterScore_F, "", this.UserInfoDto.UserID);
service.UploadAllCharterScore(projectCode, "G", CharterScore_G, "", this.UserInfoDto.UserID);
CommonHandler.ShowMessage(MessageType.Information, "上传完毕");
}
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);
}
