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, "B", i).Trim();
string province = msExcelUtil.GetCellValue(worksheet_FengMian, "C", i);
string city = msExcelUtil.GetCellValue(worksheet_FengMian, "D", i);
string areaCode_sale = msExcelUtil.GetCellValue(worksheet_FengMian, "E", i);
string areaCode_after = msExcelUtil.GetCellValue(worksheet_FengMian, "F", 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, "上传完毕");
}
}
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 < 100; i++)
{
string charterCode = msExcelUtil.GetCellValue(worksheet_FengMian, i, 1).ToString();
if (string.IsNullOrEmpty(charterCode))
{
break;
}
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(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);
}
public void ExportYewucaigoubiao2(string path, DateTime startDate, DateTime endDate, List <Juesuantongjibiao2Dto> lst)
{
MSExcelUtil msExcelUtil = new MSExcelUtil();
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path);
Worksheet sheet = workbook.ActiveSheet;
//统计周期
msExcelUtil.SetCellValue(sheet, 2, 1, string.Format("{0} 至 {1}", startDate.ToString("yyyy年MM月dd日"), endDate.ToString("yyyy年MM月dd日")));
//动态列头
var rowIndex = 3;
int index = 1;
foreach (Juesuantongjibiao2Dto dto in lst)
{
msExcelUtil.SetCellValue(sheet, 1, rowIndex, dto.ExecuteMode);
msExcelUtil.SetCellValue(sheet, 2, rowIndex, dto.ExecuteModeSumAmt);
msExcelUtil.SetCellValue(sheet, 3, rowIndex, dto.SettleCount);
if (index < lst.Count)
{
msExcelUtil.CopyRow(sheet, rowIndex);
//msExcelUtil.SetCellValue(sheet, 4, rowIndex, dto.Execute_Avg);
rowIndex++;
index++;
msExcelUtil.AddRow(sheet, rowIndex);
}
else
{
msExcelUtil.DeleteRow(sheet, rowIndex + 1);
}
}
workbook.Save();
msExcelUtil.dispose();
}
public void ExportZhuijia2(string path, List <Juesuantongjibiao2_ShoudongDto> lst)
{
MSExcelUtil msExcelUtil = new MSExcelUtil();
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path);
Worksheet sheet = workbook.ActiveSheet;
var rowIndex = 2;
int index = 1;
foreach (Juesuantongjibiao2_ShoudongDto dto in lst)
{
msExcelUtil.SetCellValue(sheet, 1, rowIndex, dto.FeeContent);
msExcelUtil.SetCellValue(sheet, 2, rowIndex, dto.FeeContentSumAmt);
msExcelUtil.SetCellValue(sheet, 3, rowIndex, dto.SettleCount);
msExcelUtil.SetCellValue(sheet, 4, rowIndex, dto.FeeContent_Avg);
if (index < lst.Count)
{
msExcelUtil.CopyRow(sheet, rowIndex);
//msExcelUtil.SetCellValue(sheet, 4, rowIndex, dto.Execute_Avg);
rowIndex++;
index++;
msExcelUtil.AddRow(sheet, rowIndex);
}
else
{
msExcelUtil.DeleteRow(sheet, rowIndex + 1);
}
}
workbook.Save();
msExcelUtil.dispose();
}
public void ExportBankInfo(string path, List <SupplierDto> list)
{
MSExcelUtil msExcelUtil = new MSExcelUtil();
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path);
Worksheet sheet = workbook.ActiveSheet;
var startIndex = 2;
foreach (SupplierDto dto in list)
{
int colIndex = 1;
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, startIndex - 1);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ModifyDateTime);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.SupplierCode);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.SupplierType);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.SupplierName);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.CooperationState);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.AccountBankFullName);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.AccountName);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.AccountBankNo);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.VATRate);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ServiceTrade);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.RecommendDepartment);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.CooperationState);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Remark);
startIndex++;
msExcelUtil.AddRow(sheet, startIndex);
}
workbook.Save();
msExcelUtil.dispose();
}
public void ExportAccruedCharges(string path, List <AccruedChargesDto> list)
{
MSExcelUtil msExcelUtil = new MSExcelUtil();
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path);
Worksheet sheet = workbook.ActiveSheet;
var rowIndex = 2;
foreach (AccruedChargesDto dto in list)
{
int colIndex = 1;
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, rowIndex - 1);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.CostType);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.ProjectName);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.ProjectShortName);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.ProjectCode);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.SupplierName);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.ExecuteCycle);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.AccruedChargesAmt);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.PayMonth);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.FlowInvoceAmt);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.SourceChk);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.DepartmentCode);
rowIndex++;
msExcelUtil.AddRow(sheet, rowIndex);
}
workbook.Save();
msExcelUtil.dispose();
}
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();
}
public void ExportZhichi(string path, List <QuotationDto> lst, ProjectDto projectDto)
{
MSExcelUtil msExcelUtil = new MSExcelUtil();
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path);
Worksheet sheet = workbook.ActiveSheet;
msExcelUtil.SetCellValue(sheet, 2, 1, projectDto.ProjectShortName);
int startIndex = 5;
lst.ForEach(x =>
{
ZhiChi01Dto item = (ZhiChi01Dto)x;
item.DtoType = item.RequirementType;
});
startIndex = InsertZhiChis(msExcelUtil, sheet, lst.Where(x => x.DtoType == "changdi").ToList(), startIndex);
startIndex += 4;
startIndex = InsertZhiChis(msExcelUtil, sheet, lst.Where(x => x.DtoType == "buzhan").ToList(), startIndex);
startIndex += 5;
startIndex = InsertZhiChis(msExcelUtil, sheet, lst.Where(x => x.DtoType == "zhanchezulin").ToList(), startIndex);
startIndex += 4;
startIndex = InsertZhiChis(msExcelUtil, sheet, lst.Where(x => x.DtoType == "zhancheyunshu").ToList(), startIndex);
startIndex += 5;
startIndex = InsertZhiChis(msExcelUtil, sheet, lst.Where(x => x.DtoType == "gongyingshanchailv").ToList(), startIndex);
workbook.Save();
msExcelUtil.dispose();
}
public void ExportNeibu(string path, List <SettlementDtlDto> lst, SettlementMstDto settlementMstDto)
{
MSExcelUtil msExcelUtil = new MSExcelUtil();
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path);
Worksheet sheet = workbook.ActiveSheet;
msExcelUtil.SetCellValueAndMultLine(sheet, 2, 7, settlementMstDto.ProjectName, 11);
msExcelUtil.SetCellValueAndMultLine(sheet, 5, 7, settlementMstDto.ProjectCode, 11);
//msExcelUtil.SetCellValueAndMultLine(sheet, 2, 8, settlementMstDto.ExecuteCycle, 40);
msExcelUtil.SetCellValueAndMultLine(sheet, 5, 8, settlementMstDto.SupplyService, 10);
msExcelUtil.SetCellValueAndMultLine(sheet, 2, 9, settlementMstDto.SupplierName, 11);
msExcelUtil.SetCellValueAndMultLine(sheet, 5, 9, settlementMstDto.ServiceRegion, 10);
int startIndex = 11;
startIndex = InsertSettlements(msExcelUtil, sheet, lst.Where(x => x.SettlementType == "1").ToList(), startIndex);
startIndex += 3;
startIndex = InsertSettlements(msExcelUtil, sheet, lst.Where(x => x.SettlementType == "2").ToList(), startIndex);
startIndex += 3;
startIndex = InsertSettlements(msExcelUtil, sheet, lst.Where(x => x.SettlementType == "3").ToList(), startIndex);
startIndex += 4;
msExcelUtil.SetCellValue(sheet, 4, startIndex, settlementMstDto.FlowOrderSum);
workbook.Save();
msExcelUtil.dispose();
}
public void ExportQita2(string path, List <Jiagetongjibiao_Qita2_Dto> lst, DateTime startDate, DateTime endDate)
{
MSExcelUtil msExcelUtil = new MSExcelUtil();
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path);
Worksheet sheet = workbook.ActiveSheet;
//统计周期
msExcelUtil.SetCellValue(sheet, 2, 1, string.Format("{0} 至 {1}", startDate.ToString("yyyy年MM月dd日"), endDate.ToString("yyyy年MM月dd日")));
int startIndex = 3;
int index = 1;
foreach (Jiagetongjibiao_Qita2_Dto dto in lst)
{
int colIndex = 1;
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ModelType);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.PurchaseType);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.PurchaseMode);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.SupplyService);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.CostStruct);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ItemName);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.danjia_AVG);
if (index < lst.Count)
{
msExcelUtil.CopyRow(sheet, startIndex);
startIndex++;
index++;
msExcelUtil.AddRow(sheet, startIndex);
}
}
workbook.Save();
msExcelUtil.dispose();
}
public void ExportFuhe(string path, List <Object> lst)
{
MSExcelUtil msExcelUtil = new MSExcelUtil();
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path);
Worksheet sheet = workbook.ActiveSheet;
int startIndex = 2;
int index = 1;
foreach (Huizongbiao_FuheDto dto in lst)
{
int colIndex = 1;
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, index);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.SupplierName);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ProjectName);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ProjectShortName);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.xiangmujingli);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.zhixingshengfen);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.zhixingchengshi);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ExecuteCycle);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ProjectType);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.fuheyaoqiu);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.abjuan);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.jishuwenjuanfuhe);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.wenjuanzuodaluyinfuhe);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.fuheyaoqiu1);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.fuheneirong);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.fuheshijian);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.dianhuazixun);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.dianhuhuifang);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.yanbenbianji);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.bianmaleixing);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.timushuliang);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.zishushuliang);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.dianhuyuyueyaoqiu);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.wenjuanbiancheng);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.yangbenluru);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.luruguanli);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.fuzhuyongpin);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.hesuandanwei);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.danjia);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.shuliang);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.heji);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Remark);
if (index < lst.Count)
{
msExcelUtil.CopyRow(sheet, startIndex);
startIndex++;
index++;
msExcelUtil.AddRow(sheet, startIndex);
}
}
workbook.Save();
msExcelUtil.dispose();
}
public void ExportBiancheng(string path, List <Jiagetongjibiao_HeaderDto> lst_Head, List <Jiagetongjibiao_Left_Biancheng_Dto> lst_Left, List <Jiagetongjibiao_Data_Biancheng_Dto> lst_Data, DateTime startDate, DateTime endDate)
{
MSExcelUtil msExcelUtil = new MSExcelUtil();
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path);
Worksheet sheet = workbook.ActiveSheet;
//统计周期
msExcelUtil.SetCellValue(sheet, 2, 1, string.Format("{0} 至 {1}", startDate.ToString("yyyy年MM月dd日"), endDate.ToString("yyyy年MM月dd日")));
//动态列头
var dStartColIndex = 9;
foreach (Jiagetongjibiao_HeaderDto dto in lst_Head)
{
msExcelUtil.CopyColumn(sheet, dStartColIndex);
msExcelUtil.AddColumn(sheet, dStartColIndex + 1);
msExcelUtil.SetCellValue(sheet, dStartColIndex++, 2, dto.zhixingchengshi);
}
var rowIndex = 3;
foreach (Jiagetongjibiao_Left_Biancheng_Dto dto in lst_Left)
{
msExcelUtil.SetCellValue(sheet, 1, rowIndex, rowIndex - 1);
msExcelUtil.SetCellValue(sheet, 2, rowIndex, dto.ExcuteMode);
msExcelUtil.SetCellValue(sheet, 3, rowIndex, dto.gongzuofenlei);
msExcelUtil.SetCellValue(sheet, 4, rowIndex, dto.gongzuoneirong);
msExcelUtil.SetCellValue(sheet, 5, rowIndex, dto.leixingpinpai);
msExcelUtil.SetCellValue(sheet, 6, rowIndex, dto.guigexinghao);
msExcelUtil.SetCellValue(sheet, 7, rowIndex, dto.zhiliangbiaozhun);
msExcelUtil.SetCellValue(sheet, 8, rowIndex, dto.hesuandanwei);
rowIndex++;
msExcelUtil.AddRow(sheet, rowIndex);
}
msExcelUtil.DeleteRow(sheet, rowIndex);
foreach (Jiagetongjibiao_Data_Biancheng_Dto dto in lst_Data)
{
dStartColIndex = 9;
var dataRowIndex = 3;
int dcolIndex = lst_Head.FindIndex(x => x.zhixingchengshi == dto.zhixingchengshi);
int drowIndex = lst_Left.FindIndex(x => x.ExcuteMode == dto.ExcuteMode &&
x.gongzuofenlei == dto.gongzuofenlei &&
x.gongzuoneirong == dto.gongzuoneirong &&
x.leixingpinpai == dto.leixingpinpai &&
x.guigexinghao == dto.guigexinghao &&
x.zhiliangbiaozhun == dto.zhiliangbiaozhun &&
x.hesuandanwei == dto.hesuandanwei);
dStartColIndex += dcolIndex;
dataRowIndex += drowIndex;
msExcelUtil.SetCellValue(sheet, dStartColIndex, dataRowIndex, dto.danjia_AVG);
}
workbook.Save();
msExcelUtil.dispose();
}
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, "上传完毕");
}
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();
}
public void ExportSupplier(string path, List <SupplierDto> list)
{
MSExcelUtil msExcelUtil = new MSExcelUtil();
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path);
Worksheet sheet = workbook.ActiveSheet;
var rowIndex = 3;
foreach (SupplierDto dto in list)
{
int colIndex = 1;
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.SupplierCode);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.Province);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.City);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.ServiceTrade);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.SupplierType);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.PurchaseType);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.CooperationState);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.SupplierType1);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.NotServiceType);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.PayCycle);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.RecommendDepartment);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.SupplierName);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.SupplierShortName);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.ProvideService);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.BranchCompanyAddress);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.CorporateName);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.CorporateTel);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.CorporateFixTel);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.CorporateEmail);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.BussinessMainName);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.BussinessMainTel);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.BussinessMainFixTel);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.BussinessMainEmail);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.BussinessSecondName);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.BussinessSecondTel);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.BussinessSecondFixTel);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.BussinessSecondEmail);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.CompanyFaxCode);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.Address);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.PostCode);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.Remark);
rowIndex++;
msExcelUtil.AddRow(sheet, rowIndex);
}
workbook.Save();
msExcelUtil.dispose();
}
public void ExportQita1(string path, List <Object> lst)
{
MSExcelUtil msExcelUtil = new MSExcelUtil();
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path);
Worksheet sheet = workbook.ActiveSheet;
int startIndex = 2;
int index = 1;
foreach (Huizongbiao_Qita1Dto dto in lst)
{
int colIndex = 1;
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Year);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ModelType);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ServiceRegion);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.DepartmentCode);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.CostDepartment);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.SupplierName);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ProjectName);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ProjectCode);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.PurchaseType);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.PurchaseMode);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.SupplyService);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ItemName);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Brand);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Specification);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Model);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Configuration);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Color);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Material);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.LogisticsRequirements);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.PackagingRequirements);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.InspectionCycle);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.AfterSalePeriod);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Warranty);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.danjia);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.shuliang);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.heji);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Remark);
if (index < lst.Count)
{
msExcelUtil.CopyRow(sheet, startIndex);
startIndex++;
index++;
msExcelUtil.AddRow(sheet, startIndex);
}
}
workbook.Save();
msExcelUtil.dispose();
}
public void ExportQita2(string path, List <Object> lst)
{
MSExcelUtil msExcelUtil = new MSExcelUtil();
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path);
Worksheet sheet = workbook.ActiveSheet;
int startIndex = 2;
int index = 1;
foreach (Huizongbiao_Qita2Dto dto in lst)
{
int colIndex = 1;
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Year);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ModelType);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ServiceRegion);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.DepartmentCode);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.CostDepartment);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.SupplierName);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ProjectName);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ProjectCode);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.PurchaseType);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.PurchaseMode);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.SupplyService);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.CostStruct);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ItemName);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Brand);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Specification);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Model);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Configuration);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.AfterSaleContent);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.AfterSalePeriod);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Warranty);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.OrderDate);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.DeliveryDate.HasValue ? dto.DeliveryDate.Value.ToString("yyyy-MM-dd"): "");
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.AcceptanceDate.HasValue ? dto.AcceptanceDate.Value.ToString("yyyy-MM-dd") : "");
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.danjia);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.shuliang);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.heji);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Remark);
if (index < lst.Count)
{
msExcelUtil.CopyRow(sheet, startIndex);
startIndex++;
index++;
msExcelUtil.AddRow(sheet, startIndex);
}
}
workbook.Save();
msExcelUtil.dispose();
}
public void ExportCapitalRequirement(string path, DateTime startDate, DateTime endDate, List <HeaderDto> HeaderDtoList,
List <LeftDto> LeftDtoList, List <DataDto> DataDtoList)
{
MSExcelUtil msExcelUtil = new MSExcelUtil();
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path);
Worksheet sheet = workbook.ActiveSheet;
//统计周期
msExcelUtil.SetCellValue(sheet, 2, 1, string.Format("{0} 至 {1}", startDate.ToString("yyyy年MM月dd日"), endDate.ToString("yyyy年MM月dd日")));
//动态列头
var dStartColIndex = 6;
foreach (HeaderDto dto in HeaderDtoList)
{
msExcelUtil.CopyColumn(sheet, dStartColIndex);
msExcelUtil.AddColumn(sheet, dStartColIndex + 1);
msExcelUtil.SetCellValue(sheet, dStartColIndex, 2, dto.Display);
dStartColIndex++;
}
int index = 1;
var rowIndex = 3;
foreach (LeftDto dto in LeftDtoList)
{
msExcelUtil.SetCellValue(sheet, 1, rowIndex, dto.DepartmentCode);
msExcelUtil.SetCellValue(sheet, 2, rowIndex, dto.ExpendType);
msExcelUtil.SetCellValue(sheet, 3, rowIndex, dto.ProjectShortName);
msExcelUtil.SetCellValue(sheet, 4, rowIndex, dto.ExpendPattern);
msExcelUtil.SetCellValue(sheet, 5, rowIndex, dto.PaySumAmt);
var dataColumnIndex = 6;
foreach (DataDto dataDto in DataDtoList)
{
if (dataDto.ProjectId == dto.ProjectId)
{
msExcelUtil.SetCellValue(sheet, dataColumnIndex++, rowIndex, dataDto.SumAmt);
}
}
if (index < LeftDtoList.Count)
{
msExcelUtil.CopyRow(sheet, rowIndex);
rowIndex++;
index++;
msExcelUtil.AddRow(sheet, rowIndex);
}
}
msExcelUtil.DeleteRow(sheet, rowIndex + 1);
workbook.Save();
msExcelUtil.dispose();
}
public void ExportZhixing(string path, List <QuotationDto> lst, ProjectDto projectDto)
{
MSExcelUtil msExcelUtil = new MSExcelUtil();
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path);
Worksheet sheet = workbook.ActiveSheet;
msExcelUtil.SetCellValue(sheet, 2, 1, projectDto.ProjectShortName);
int startIndex = 0;
startIndex += 3;
int startRow = startIndex;
int index = 1;
foreach (Quotation_ZhiXingDto dto in lst)
{
msExcelUtil.SetCellValue(sheet, 1, startIndex, index);
msExcelUtil.SetCellValue(sheet, 2, startIndex, dto.SupplierName);
msExcelUtil.SetCellValue(sheet, 3, startIndex, dto.Province);
msExcelUtil.SetCellValue(sheet, 4, startIndex, dto.City);
msExcelUtil.SetCellValue(sheet, 5, startIndex, dto.IntoShopType);
msExcelUtil.SetCellValue(sheet, 6, startIndex, dto.ExcuteType);
msExcelUtil.SetCellValue(sheet, 7, startIndex, dto.UserType);
msExcelUtil.SetCellValue(sheet, 8, startIndex, dto.ExistingOrPotential);
msExcelUtil.SetCellValue(sheet, 9, startIndex, dto.CustomerType);
msExcelUtil.SetCellValue(sheet, 11, startIndex, dto.UnitPrice);
msExcelUtil.SetCellValue(sheet, 12, startIndex, dto.Count);
double count = string.IsNullOrEmpty(dto.Count) ? 0 : double.Parse(dto.Count);
string xiaoji = dto.UnitPrice.HasValue ? (dto.UnitPrice.Value * new decimal(count)).ToString("##.00") : "";
// string xiaoji = dto.UnitPrice.HasValue ? (dto.UnitPrice.Value * dto.Count.Value).ToString("##.00") : "";
msExcelUtil.SetCellValue(sheet, 13, startIndex, xiaoji);
msExcelUtil.SetCellValue(sheet, 14, startIndex, dto.Remark);
if (index < lst.Count)
{
msExcelUtil.CopyRow(sheet, startIndex);
startIndex++;
index++;
msExcelUtil.AddRow(sheet, startIndex);
}
else
{
msExcelUtil.DeleteRow(sheet, startIndex + 1);
}
}
workbook.Save();
msExcelUtil.dispose();
}
public void ExportFuhe(string path, List <QuotationDto> lst, ProjectDto projectDto)
{
MSExcelUtil msExcelUtil = new MSExcelUtil();
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path);
Worksheet sheet = workbook.ActiveSheet;
msExcelUtil.SetCellValue(sheet, 2, 1, projectDto.ProjectShortName);
int startIndex = 0;
startIndex += 3;
int index = 1;
foreach (Quotation_FuHeDto dto in lst)
{
msExcelUtil.SetCellValue(sheet, 1, startIndex, index);
msExcelUtil.SetCellValue(sheet, 2, startIndex, dto.SupplierName);
msExcelUtil.SetCellValue(sheet, 3, startIndex, dto.Province);
msExcelUtil.SetCellValue(sheet, 4, startIndex, dto.City);
msExcelUtil.SetCellValue(sheet, 5, startIndex, dto.fuheyaoqiu);
msExcelUtil.SetCellValue(sheet, 6, startIndex, dto.dianhuazixun);
msExcelUtil.SetCellValue(sheet, 7, startIndex, dto.dianhuahuifang);
msExcelUtil.SetCellValue(sheet, 8, startIndex, dto.yanbenbianji);
msExcelUtil.SetCellValue(sheet, 9, startIndex, dto.fuzhuyongpin);
msExcelUtil.SetCellValue(sheet, 10, startIndex, dto.bianmaleixing);
msExcelUtil.SetCellValue(sheet, 11, startIndex, dto.wenjuanbiancheng);
msExcelUtil.SetCellValue(sheet, 13, startIndex, dto.danjia);
msExcelUtil.SetCellValue(sheet, 14, startIndex, dto.shuliang);
double count = string.IsNullOrEmpty(dto.shuliang) ? 0 : double.Parse(dto.shuliang);
string xiaoji = dto.danjia.HasValue ? (dto.danjia.Value * new decimal(count)).ToString("##.00") : "";
msExcelUtil.SetCellValue(sheet, 15, startIndex, xiaoji);
msExcelUtil.SetCellValue(sheet, 16, startIndex, dto.beizhu);
if (index < lst.Count)
{
msExcelUtil.CopyRow(sheet, startIndex);
startIndex++;
index++;
msExcelUtil.AddRow(sheet, startIndex);
}
else
{
msExcelUtil.DeleteRow(sheet, startIndex + 1);
}
}
workbook.Save();
msExcelUtil.dispose();
}
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, "上传完毕");
}
public void ExportWuxingshangpincaigou(string path, List <QuotationDto> lst, ProjectDto projectDto)
{
MSExcelUtil msExcelUtil = new MSExcelUtil();
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path);
Worksheet sheet = workbook.ActiveSheet;
msExcelUtil.SetCellValue(sheet, 2, 1, projectDto.ProjectShortName);
int startIndex = 0;
startIndex += 3;
int index = 1;
foreach (Quotation_QiTa2Dto dto in lst)
{
msExcelUtil.SetCellValue(sheet, 1, startIndex, index);
msExcelUtil.SetCellValue(sheet, 2, startIndex, dto.SupplierName);
msExcelUtil.SetCellValue(sheet, 3, startIndex, dto.Province);
msExcelUtil.SetCellValue(sheet, 4, startIndex, dto.City);
msExcelUtil.SetCellValue(sheet, 5, startIndex, dto.caigoufenlei);
msExcelUtil.SetCellValue(sheet, 6, startIndex, dto.caigoufangshi);
msExcelUtil.SetCellValue(sheet, 7, startIndex, dto.tigongfuwu);
msExcelUtil.SetCellValue(sheet, 8, startIndex, dto.feiyonggoucheng);
msExcelUtil.SetCellValue(sheet, 9, startIndex, dto.pinming);
msExcelUtil.SetCellValue(sheet, 10, startIndex, dto.guige);
msExcelUtil.SetCellValue(sheet, 11, startIndex, dto.xinghao);
msExcelUtil.SetCellValue(sheet, 12, startIndex, dto.danjia);
msExcelUtil.SetCellValue(sheet, 13, startIndex, dto.shuliang);
double count = string.IsNullOrEmpty(dto.shuliang) ? 0 : double.Parse(dto.shuliang);
string xiaoji = dto.danjia.HasValue ? (dto.danjia.Value * new decimal(count)).ToString("##.00") : "";
msExcelUtil.SetCellValue(sheet, 14, startIndex, xiaoji);
msExcelUtil.SetCellValue(sheet, 15, startIndex, dto.beizhu);
if (index < lst.Count)
{
msExcelUtil.CopyRow(sheet, startIndex);
startIndex++;
index++;
msExcelUtil.AddRow(sheet, startIndex);
}
else
{
msExcelUtil.DeleteRow(sheet, startIndex + 1);
}
}
workbook.Save();
msExcelUtil.dispose();
}
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, "上传完毕");
}
public void ExportYanjiu(string path, List <Object> lst)
{
MSExcelUtil msExcelUtil = new MSExcelUtil();
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path);
Worksheet sheet = workbook.ActiveSheet;
int startIndex = 2;
int index = 1;
foreach (Huizongbiao_YanjiuDto dto in lst)
{
int colIndex = 1;
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, index);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.SupplierName);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ProjectName);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ProjectShortName);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.xiangmujingli);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.zhixingshengfen);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.zhixingchengshi);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ExecuteCycle);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ProjectType);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.ExcuteMode);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.gongzuofenlei);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.gongzuoneirong);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.renyuangangwei);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.leixingpinpai);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.guigeyaoqiu);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.zhiliangbiaozhun);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.qitashuoming);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.hesuandanwei);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.danjia);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.shuliang);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.heji);
msExcelUtil.SetCellValue(sheet, colIndex++, startIndex, dto.Remark);
if (index < lst.Count)
{
msExcelUtil.CopyRow(sheet, startIndex);
startIndex++;
index++;
msExcelUtil.AddRow(sheet, startIndex);
}
}
workbook.Save();
msExcelUtil.dispose();
}
public void ExportYewucaigoubiao1(string path, DateTime startDate, DateTime endDate, List <Juesuantongjibiao1_HeaderDto> HeaderDtoList,
List <Juesuantongjibiao1_LeftDto> LeftDtoList, List <Juesuantongjibiao1_DataDto> DataDtoList)
{
MSExcelUtil msExcelUtil = new MSExcelUtil();
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path);
Worksheet sheet = workbook.ActiveSheet;
//统计周期
msExcelUtil.SetCellValue(sheet, 2, 1, string.Format("{0} 至 {1}", startDate.ToString("yyyy年MM月dd日"), endDate.ToString("yyyy年MM月dd日")));
//动态列头
var dStartColIndex = 4;
foreach (Juesuantongjibiao1_HeaderDto dto in HeaderDtoList)
{
msExcelUtil.CopyColumn(sheet, dStartColIndex);
msExcelUtil.AddColumn(sheet, dStartColIndex + 1);
msExcelUtil.SetCellValue(sheet, dStartColIndex++, 2, dto.ExecuteMode);
}
var rowIndex = 3;
foreach (Juesuantongjibiao1_LeftDto dto in LeftDtoList)
{
msExcelUtil.SetCellValue(sheet, 1, rowIndex, dto.City);
msExcelUtil.SetCellValue(sheet, 2, rowIndex, dto.SupplierName);
msExcelUtil.SetCellValue(sheet, 3, rowIndex, dto.SupplierSumAmt);
rowIndex++;
msExcelUtil.AddRow(sheet, rowIndex);
}
msExcelUtil.DeleteRow(sheet, rowIndex);
msExcelUtil.DeleteRow(sheet, rowIndex);
foreach (Juesuantongjibiao1_DataDto dto in DataDtoList)
{
dStartColIndex = 4;
var dataRowIndex = 3;
int dcolIndex = HeaderDtoList.FindIndex(x => x.ExecuteMode == dto.ExecuteMode);
int drowIndex = LeftDtoList.FindIndex(x => x.SupplierId == dto.SupplierId);
dStartColIndex += dcolIndex;
dataRowIndex += drowIndex;
msExcelUtil.SetCellValue(sheet, dStartColIndex, dataRowIndex, dto.ExecuteModeSumAmt);
}
workbook.Save();
msExcelUtil.dispose();
}
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, "上传完毕");
}
public void ExportFuhe(string path, List <QuotationDto> lst, ProjectDto projectDto, string supplierName)
{
MSExcelUtil msExcelUtil = new MSExcelUtil();
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path);
Worksheet sheet = workbook.ActiveSheet;
msExcelUtil.SetCellValue(sheet, 2, 1, projectDto.ProjectShortName);
int startIndex = 0;
startIndex += 3;
int index = 1;
foreach (Quotation_FuHeDto dto in lst)
{
msExcelUtil.SetCellValue(sheet, 1, startIndex, index);
msExcelUtil.SetCellValue(sheet, 2, startIndex, supplierName);
msExcelUtil.SetCellValue(sheet, 3, startIndex, dto.Province);
msExcelUtil.SetCellValue(sheet, 4, startIndex, dto.City);
msExcelUtil.SetCellValue(sheet, 5, startIndex, dto.fuheyaoqiu);
msExcelUtil.SetCellValue(sheet, 6, startIndex, dto.dianhuazixun);
msExcelUtil.SetCellValue(sheet, 7, startIndex, dto.dianhuahuifang);
msExcelUtil.SetCellValue(sheet, 8, startIndex, dto.yanbenbianji);
msExcelUtil.SetCellValue(sheet, 9, startIndex, dto.fuzhuyongpin);
msExcelUtil.SetCellValue(sheet, 10, startIndex, dto.bianmaleixing);
msExcelUtil.SetCellValue(sheet, 11, startIndex, dto.wenjuanbiancheng);
msExcelUtil.SetCellValue(sheet, 14, startIndex, dto.shuliang);
msExcelUtil.SetCellValue(sheet, 16, startIndex, dto.beizhu);
if (index < lst.Count)
{
msExcelUtil.CopyRow(sheet, startIndex);
startIndex++;
index++;
msExcelUtil.AddRow(sheet, startIndex);
}
else
{
msExcelUtil.DeleteRow(sheet, startIndex + 1);
}
}
workbook.Save();
msExcelUtil.dispose();
}
public void ExportQita2(string path, List <QuotationDto> lst, ProjectDto projectDto, string supplierName)
{
MSExcelUtil msExcelUtil = new MSExcelUtil();
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path);
Worksheet sheet = workbook.ActiveSheet;
msExcelUtil.SetCellValue(sheet, 2, 1, projectDto.ProjectShortName);
int startIndex = 0;
startIndex += 3;
int index = 1;
foreach (Quotation_QiTa2Dto dto in lst)
{
msExcelUtil.SetCellValue(sheet, 1, startIndex, index);
msExcelUtil.SetCellValue(sheet, 2, startIndex, supplierName);
msExcelUtil.SetCellValue(sheet, 3, startIndex, dto.Province);
msExcelUtil.SetCellValue(sheet, 4, startIndex, dto.City);
msExcelUtil.SetCellValue(sheet, 5, startIndex, dto.caigoufenlei);
msExcelUtil.SetCellValue(sheet, 6, startIndex, dto.caigoufangshi);
msExcelUtil.SetCellValue(sheet, 7, startIndex, dto.tigongfuwu);
msExcelUtil.SetCellValue(sheet, 8, startIndex, dto.feiyonggoucheng);
msExcelUtil.SetCellValue(sheet, 9, startIndex, dto.pinming);
msExcelUtil.SetCellValue(sheet, 10, startIndex, dto.guige);
msExcelUtil.SetCellValue(sheet, 11, startIndex, dto.xinghao);
msExcelUtil.SetCellValue(sheet, 13, startIndex, dto.shuliang);
msExcelUtil.SetCellValue(sheet, 15, startIndex, dto.beizhu);
if (index < lst.Count)
{
msExcelUtil.CopyRow(sheet, startIndex);
startIndex++;
index++;
msExcelUtil.AddRow(sheet, startIndex);
}
else
{
msExcelUtil.DeleteRow(sheet, startIndex + 1);
}
}
workbook.Save();
msExcelUtil.dispose();
}
