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