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();
}
private int InsertChezhans(MSExcelUtil msExcelUtil, Worksheet sheet, List <QuotationDto> lst, int startIndex, string supplierName)
{
int index = 1;
foreach (Quotation_CheZhanDto 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.ExcuteType);
msExcelUtil.SetCellValue(sheet, 6, startIndex, dto.Responsibilites);
msExcelUtil.SetCellValue(sheet, 7, startIndex, dto.UserType);
msExcelUtil.SetCellValue(sheet, 8, startIndex, dto.ExistingOrPotential);
msExcelUtil.SetCellValue(sheet, 9, startIndex, dto.CustomerType);
msExcelUtil.SetCellValue(sheet, 12, startIndex, dto.Count);
if (index < lst.Count)
{
msExcelUtil.CopyRow(sheet, startIndex);
startIndex++;
index++;
msExcelUtil.AddRow(sheet, startIndex);
}
else
{
msExcelUtil.DeleteRow(sheet, startIndex + 1);
}
}
return(startIndex);
}
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();
}
private int InsertZhiChis(MSExcelUtil msExcelUtil, Worksheet sheet, List <QuotationDto> lst, int startIndex)
{
int index = 1;
foreach (ZhiChi01Dto 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.fenlei);
msExcelUtil.SetCellValue(sheet, 6, startIndex, dto.leixingpinpai);
msExcelUtil.SetCellValue(sheet, 7, startIndex, dto.danjia);
msExcelUtil.SetCellValue(sheet, 9, 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, 10, startIndex, xiaoji);
if (index < lst.Count)
{
msExcelUtil.CopyRow(sheet, startIndex);
startIndex++;
index++;
msExcelUtil.AddRow(sheet, startIndex);
}
else
{
msExcelUtil.DeleteRow(sheet, startIndex + 1);
}
}
return(startIndex);
}
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 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 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();
}
private int InsertZhiChis(MSExcelUtil msExcelUtil, Worksheet sheet, List <QuotationDto> lst, int startIndex, string supplierName)
{
int index = 1;
foreach (ZhiChi01Dto 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.fenlei);
msExcelUtil.SetCellValue(sheet, 6, startIndex, dto.leixingpinpai);
msExcelUtil.SetCellValue(sheet, 9, startIndex, dto.shuliang);
if (index < lst.Count)
{
msExcelUtil.CopyRow(sheet, startIndex);
startIndex++;
index++;
msExcelUtil.AddRow(sheet, startIndex);
}
else
{
msExcelUtil.DeleteRow(sheet, startIndex + 1);
}
}
return(startIndex);
}
public void ExportZhuijia(string path, List <Jiagetongjibiao_HeaderDto> lst_Head, List <Jiagetongjibiao_Left_Shoudong_Dto> lst_Left, List <Jiagetongjibiao_Data_Shoudong_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 = 3;
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_Shoudong_Dto dto in lst_Left)
{
msExcelUtil.SetCellValue(sheet, 1, rowIndex, rowIndex - 1);
msExcelUtil.SetCellValue(sheet, 2, rowIndex, dto.FeeContent);
rowIndex++;
msExcelUtil.AddRow(sheet, rowIndex);
}
msExcelUtil.DeleteRow(sheet, rowIndex);
foreach (Jiagetongjibiao_Data_Shoudong_Dto dto in lst_Data)
{
dStartColIndex = 3;
var dataRowIndex = 3;
int dcolIndex = lst_Head.FindIndex(x => x.zhixingchengshi == dto.zhixingchengshi);
int drowIndex = lst_Left.FindIndex(x => x.FeeContent == dto.FeeContent);
dStartColIndex += dcolIndex;
dataRowIndex += drowIndex;
msExcelUtil.SetCellValue(sheet, dStartColIndex, dataRowIndex, dto.danjia_AVG);
}
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 ExportZhuijia1(string path, List <Juesuantongjibiao1_Shoudong_HeaderDto> HeaderDtoList,
List <Juesuantongjibiao1_Shoudong_LeftDto> LeftDtoList, List <Juesuantongjibiao1_Shoudong_DataDto> DataDtoList)
{
MSExcelUtil msExcelUtil = new MSExcelUtil();
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path);
Worksheet sheet = workbook.ActiveSheet;
//动态列头
var dStartColIndex = 4;
foreach (Juesuantongjibiao1_Shoudong_HeaderDto dto in HeaderDtoList)
{
msExcelUtil.CopyColumn(sheet, dStartColIndex);
msExcelUtil.AddColumn(sheet, dStartColIndex + 1);
msExcelUtil.SetCellValue(sheet, dStartColIndex++, 1, dto.FeeContent);
}
var rowIndex = 2;
foreach (Juesuantongjibiao1_Shoudong_LeftDto dto in LeftDtoList)
{
msExcelUtil.SetCellValue(sheet, 1, rowIndex, dto.SupplierName);
msExcelUtil.SetCellValue(sheet, 2, rowIndex, dto.City);
msExcelUtil.SetCellValue(sheet, 3, rowIndex, dto.SupplierSumAmt);
rowIndex++;
msExcelUtil.AddRow(sheet, rowIndex);
}
msExcelUtil.DeleteRow(sheet, rowIndex);
foreach (Juesuantongjibiao1_Shoudong_DataDto dto in DataDtoList)
{
dStartColIndex = 4;
var dataRowIndex = 2;
int dcolIndex = HeaderDtoList.FindIndex(x => x.FeeContent == dto.FeeContent);
int drowIndex = LeftDtoList.FindIndex(x => x.SupplierId == dto.SupplierId);
dStartColIndex += dcolIndex;
dataRowIndex += drowIndex;
msExcelUtil.SetCellValue(sheet, dStartColIndex, dataRowIndex, dto.FeeContentSumAmt);
}
workbook.Save();
msExcelUtil.dispose();
}
private int InsertSettlements(MSExcelUtil msExcelUtil, Worksheet sheet, List <SettlementDtlDto> lst, int startIndex)
{
int index = 0;
foreach (SettlementDtlDto dto in lst)
{
if (!string.IsNullOrEmpty(dto.SettleAmt))
{
if (index < lst.Count - 1)
{
msExcelUtil.CopyRow(sheet, startIndex);
msExcelUtil.AddRow(sheet, startIndex + 1);
}
msExcelUtil.SetCellValueAndMultLine(sheet, 1, startIndex, dto.FeeContent, 11);
msExcelUtil.SetCellValue(sheet, 2, startIndex, dto.danjia);
msExcelUtil.SetCellValue(sheet, 3, startIndex, dto.SettleCount);
msExcelUtil.SetCellValue(sheet, 4, startIndex, dto.SettleAmt);
if (dto.Remark != null && dto.FeeContent.Length < dto.Remark.Length)
{
msExcelUtil.SetCellValueAndMultLine(sheet, 5, startIndex, dto.Remark, 10);
}
else
{
msExcelUtil.SetCellValue(sheet, 5, startIndex, dto.Remark);
}
if (index < lst.Count - 1)
{
startIndex++;
}
}
index++;
}
msExcelUtil.DeleteRow(sheet, startIndex + 1);
return(startIndex);
}
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 ExportChezhan(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 = 4;
startIndex = InsertChezhans(msExcelUtil, sheet, lst.Where(x => x.DtoType == "Chenzhan01").ToList(), startIndex, supplierName);
startIndex += 4;
startIndex = InsertChezhans(msExcelUtil, sheet, lst.Where(x => x.DtoType == "Chenzhan02").ToList(), startIndex, supplierName);
startIndex += 4;
startIndex = InsertChezhans(msExcelUtil, sheet, lst.Where(x => x.DtoType == "Chenzhan03").ToList(), startIndex, supplierName);
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();
}
public void ExportChezhan(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);
lst.ForEach(x => {
Quotation_CheZhanDto item = (Quotation_CheZhanDto)x;
item.DtoType = item.RequirementType;
});
int startIndex = 4;
startIndex = InsertChezhans(msExcelUtil, sheet, lst.Where(x => x.DtoType == "dingliang").ToList(), startIndex);
startIndex += 4;
startIndex = InsertChezhans(msExcelUtil, sheet, lst.Where(x => x.DtoType == "zuotanhui").ToList(), startIndex);
startIndex += 4;
startIndex = InsertChezhans(msExcelUtil, sheet, lst.Where(x => x.DtoType == "shenfangjirijiliuzhi").ToList(), startIndex);
workbook.Save();
msExcelUtil.dispose();
}
public void ExportZhichi(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 = 5;
startIndex = InsertZhiChis(msExcelUtil, sheet, lst.Where(x => x.DtoType == "Changdibuzhan_Zhichi01").ToList(), startIndex, supplierName);
startIndex += 4;
startIndex = InsertZhiChis(msExcelUtil, sheet, lst.Where(x => x.DtoType == "Changdibuzhan_Zhichi02").ToList(), startIndex, supplierName);
startIndex += 5;
startIndex = InsertZhiChis(msExcelUtil, sheet, lst.Where(x => x.DtoType == "Yunshuzuche_Zhichi01").ToList(), startIndex, supplierName);
startIndex += 4;
startIndex = InsertZhiChis(msExcelUtil, sheet, lst.Where(x => x.DtoType == "Yunshuzuche_Zhichi02").ToList(), startIndex, supplierName);
startIndex += 5;
startIndex = InsertZhiChis(msExcelUtil, sheet, lst.Where(x => x.DtoType == "Gongyingshangchailv_Zhichi01").ToList(), startIndex, supplierName);
startIndex += 4;
startIndex = InsertZhiChis(msExcelUtil, sheet, lst.Where(x => x.DtoType == "Gongyingshangchailv_Zhichi02").ToList(), startIndex, supplierName);
workbook.Save();
msExcelUtil.dispose();
}
public ActionResult ApplyPayExport(string projectName, string projectShortName, string supplierName, string serviceTrade)
{
List <FlowOrderDto> list = service.FlowOrderSearch1(projectName, projectShortName, supplierName, serviceTrade, UserInfo.UserId);
string absPath = Server.MapPath(basePath);
if (!Directory.Exists(absPath))
{
Directory.CreateDirectory(absPath);
}
string createFileName = "应付流转单付款申请_" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx";
string path = absPath + createFileName;
string templateFile = Server.MapPath(tempPath + "应付流转单付款申请.xlsx");// 模板的路径
System.IO.File.Copy(templateFile, path);
MSExcelUtil msExcelUtil = new MSExcelUtil();
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path);
Worksheet sheet = workbook.ActiveSheet;
int startIndex = 2;
if (list.Count > 0)
{
foreach (FlowOrderDto dto in list)
{
msExcelUtil.SetCellValue(sheet, 1, startIndex, dto.ServiceTrade);
// msExcelUtil.SetCellValue(sheet, 2, startIndex, dto.DepartmentCode);
msExcelUtil.SetCellValue(sheet, 2, startIndex, dto.CostDepartment);
msExcelUtil.SetCellValue(sheet, 3, startIndex, dto.ProjectName);
msExcelUtil.SetCellValue(sheet, 4, startIndex, dto.ProjectShortName);
msExcelUtil.SetCellValue(sheet, 5, startIndex, dto.ProjectCode);
msExcelUtil.SetCellValue(sheet, 6, startIndex, dto.ExecuteCycleStartDate.HasValue ? dto.ExecuteCycleStartDate.Value.ToString("yyyy-MM-dd") : "");
msExcelUtil.SetCellValue(sheet, 7, startIndex, dto.ExecuteCycleEndDate.HasValue ? dto.ExecuteCycleEndDate.Value.ToString("yyyy-MM-dd") : "");
msExcelUtil.SetCellValue(sheet, 8, startIndex, dto.SupplierName);
msExcelUtil.SetCellValue(sheet, 9, startIndex, dto.BudgetAmt);
msExcelUtil.SetCellValue(sheet, 10, startIndex, dto.BudgetLeftAmt);
msExcelUtil.SetCellValue(sheet, 11, startIndex, dto.ExpendType);
msExcelUtil.SetCellValue(sheet, 12, startIndex, dto.ExpendPattern);
msExcelUtil.SetCellValue(sheet, 13, startIndex, dto.PaymentType);
msExcelUtil.SetCellValue(sheet, 14, startIndex, dto.PaymentCompany);
//msExcelUtil.SetCellValue(sheet, 15, startIndex, dto.EstimatedPaymentTime.HasValue ? dto.EstimatedPaymentTime.Value.ToString("yyyy-MM-dd") : "");
//msExcelUtil.SetCellValue(sheet, 16, startIndex, dto.EstimatePaymentAmt);
msExcelUtil.SetCellValue(sheet, 15, startIndex, dto.FactPaymentTime.HasValue ? dto.FactPaymentTime.Value.ToString("yyyy-MM-dd") : "");
msExcelUtil.SetCellValue(sheet, 16, startIndex, dto.FactPaymentAmt);
msExcelUtil.SetCellValue(sheet, 17, startIndex, dto.InvoiceAmt);
msExcelUtil.SetCellValue(sheet, 18, startIndex, dto.Payee);
msExcelUtil.SetCellValue(sheet, 19, startIndex, dto.Remark);
msExcelUtil.SetCellValue(sheet, 20, startIndex, dto.Finance_PaymentStatus);
if (list.IndexOf(dto) < list.Count - 1)
{
msExcelUtil.CopyRow(sheet, startIndex);
startIndex++;
msExcelUtil.AddRow(sheet, startIndex);
}
}
}
workbook.Save();
msExcelUtil.dispose();
return(Json(new { ExportPath = path }));
}
public void ExportZhixing(string path, List <Jiagetongjibiao_HeaderDto> lst_Head, List <Jiagetongjibiao_Left_Zhixing_Dto> lst_Left, List <Jiagetongjibiao_Data_Zhixing_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 = 21;
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_Zhixing_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.zhixingfenlei);
msExcelUtil.SetCellValue(sheet, 4, rowIndex, dto.gongzuozhize);
msExcelUtil.SetCellValue(sheet, 5, rowIndex, dto.jindianfangshi);
msExcelUtil.SetCellValue(sheet, 6, rowIndex, dto.yonghufenlei);
msExcelUtil.SetCellValue(sheet, 7, rowIndex, dto.kehufenlei);
msExcelUtil.SetCellValue(sheet, 8, rowIndex, dto.xianyouhuoqianzai);
msExcelUtil.SetCellValue(sheet, 9, rowIndex, dto.fangwenshichang);
msExcelUtil.SetCellValue(sheet, 10, rowIndex, dto.chenggonglv);
msExcelUtil.SetCellValue(sheet, 11, rowIndex, dto.tuisongfangshi);
msExcelUtil.SetCellValue(sheet, 12, rowIndex, dto.canhuirenshu);
msExcelUtil.SetCellValue(sheet, 13, rowIndex, dto.genfangxuqiu);
msExcelUtil.SetCellValue(sheet, 14, rowIndex, dto.cheliangleibei);
msExcelUtil.SetCellValue(sheet, 15, rowIndex, dto.pinpaifenlei);
msExcelUtil.SetCellValue(sheet, 16, rowIndex, dto.chejiafanwei);
msExcelUtil.SetCellValue(sheet, 17, rowIndex, dto.goucheyugoushijianduan);
msExcelUtil.SetCellValue(sheet, 18, rowIndex, dto.gongzuoshijian);
msExcelUtil.SetCellValue(sheet, 19, rowIndex, dto.jingyingshijian);
msExcelUtil.SetCellValue(sheet, 20, rowIndex, dto.hesuandanwei);
rowIndex++;
msExcelUtil.AddRow(sheet, rowIndex);
}
msExcelUtil.DeleteRow(sheet, rowIndex);
foreach (Jiagetongjibiao_Data_Zhixing_Dto dto in lst_Data)
{
dStartColIndex = 21;
var dataRowIndex = 3;
int dcolIndex = lst_Head.FindIndex(x => x.zhixingchengshi == dto.zhixingchengshi);
int drowIndex = lst_Left.FindIndex(x => x.ExcuteMode == dto.ExcuteMode &&
x.zhixingfenlei == dto.zhixingfenlei &&
x.gongzuozhize == dto.gongzuozhize &&
x.jindianfangshi == dto.jindianfangshi &&
x.yonghufenlei == dto.yonghufenlei &&
x.kehufenlei == dto.kehufenlei &&
x.xianyouhuoqianzai == dto.xianyouhuoqianzai &&
x.fangwenshichang == dto.fangwenshichang &&
x.chenggonglv == dto.chenggonglv &&
x.tuisongfangshi == dto.tuisongfangshi &&
x.canhuirenshu == dto.canhuirenshu &&
x.genfangxuqiu == dto.genfangxuqiu &&
x.cheliangleibei == dto.cheliangleibei &&
x.pinpaifenlei == dto.pinpaifenlei &&
x.chejiafanwei == dto.chejiafanwei &&
x.goucheyugoushijianduan == dto.goucheyugoushijianduan &&
x.gongzuoshijian == dto.gongzuoshijian &&
x.jingyingshijian == dto.jingyingshijian &&
x.hesuandanwei == dto.hesuandanwei);
dStartColIndex += dcolIndex;
dataRowIndex += drowIndex;
msExcelUtil.SetCellValue(sheet, dStartColIndex, dataRowIndex, dto.danjia_AVG);
}
workbook.Save();
msExcelUtil.dispose();;
}
public void ExportYanjiu(string path, List <Jiagetongjibiao_HeaderDto> lst_Head, List <Jiagetongjibiao_Left_Yanjiu_Dto> lst_Left, List <Jiagetongjibiao_Data_Yanjiu_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_Yanjiu_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.guigeyaoqiu);
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_Yanjiu_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.guigeyaoqiu == dto.guigeyaoqiu &&
x.zhiliangbiaozhun == dto.zhiliangbiaozhun &&
x.hesuandanwei == dto.hesuandanwei);
dStartColIndex += dcolIndex;
dataRowIndex += drowIndex;
msExcelUtil.SetCellValue(sheet, dStartColIndex, dataRowIndex, dto.danjia_AVG);
}
workbook.Save();
msExcelUtil.dispose();
}
public void ExportFuhe(string path, List <Jiagetongjibiao_HeaderDto> lst_Head, List <Jiagetongjibiao_Left_Fuhe_Dto> lst_Left, List <Jiagetongjibiao_Data_Fuhe_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 = 14;
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_Fuhe_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.fuheyaoqiu);
msExcelUtil.SetCellValue(sheet, 4, rowIndex, dto.fuheshijian);
msExcelUtil.SetCellValue(sheet, 5, rowIndex, dto.dianhuazixun);
msExcelUtil.SetCellValue(sheet, 6, rowIndex, dto.dianhuhuifang);
msExcelUtil.SetCellValue(sheet, 7, rowIndex, dto.yanbenbianji);
msExcelUtil.SetCellValue(sheet, 8, rowIndex, dto.bianmaleixing);
msExcelUtil.SetCellValue(sheet, 9, rowIndex, dto.wenjuanbiancheng);
msExcelUtil.SetCellValue(sheet, 10, rowIndex, dto.timushuliang);
msExcelUtil.SetCellValue(sheet, 11, rowIndex, dto.zishushuliang);
msExcelUtil.SetCellValue(sheet, 12, rowIndex, dto.yangbenluru);
msExcelUtil.SetCellValue(sheet, 13, rowIndex, dto.hesuandanwei);
rowIndex++;
msExcelUtil.AddRow(sheet, rowIndex);
}
msExcelUtil.DeleteRow(sheet, rowIndex);
foreach (Jiagetongjibiao_Data_Fuhe_Dto dto in lst_Data)
{
dStartColIndex = 14;
var dataRowIndex = 3;
int dcolIndex = lst_Head.FindIndex(x => x.zhixingchengshi == dto.zhixingchengshi);
int drowIndex = lst_Left.FindIndex(x => x.ExcuteMode == dto.ExcuteMode &&
x.fuheyaoqiu == dto.fuheyaoqiu &&
x.fuheshijian == dto.fuheshijian &&
x.dianhuazixun == dto.dianhuazixun &&
x.dianhuhuifang == dto.dianhuhuifang &&
x.yanbenbianji == dto.yanbenbianji &&
x.bianmaleixing == dto.bianmaleixing &&
x.timushuliang == dto.timushuliang &&
x.zishushuliang == dto.zishushuliang &&
x.wenjuanbiancheng == dto.wenjuanbiancheng &&
x.yangbenluru == dto.yangbenluru &&
x.hesuandanwei == dto.hesuandanwei);
dStartColIndex += dcolIndex;
dataRowIndex += drowIndex;
msExcelUtil.SetCellValue(sheet, dStartColIndex, dataRowIndex, dto.danjia_AVG);
}
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 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 int InsertChezhans(MSExcelUtil msExcelUtil, Worksheet sheet, List <QuotationDto> lst, int startIndex)
{
int index = 1;
foreach (Quotation_CheZhanDto 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.ExcuteType);
msExcelUtil.SetCellValue(sheet, 6, startIndex, dto.Responsibilites);
msExcelUtil.SetCellValue(sheet, 7, startIndex, dto.UserType);
msExcelUtil.SetCellValue(sheet, 8, startIndex, dto.ExistingOrPotential);
msExcelUtil.SetCellValue(sheet, 9, startIndex, dto.CustomerType);
msExcelUtil.SetCellValue(sheet, 10, 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") : "";
msExcelUtil.SetCellValue(sheet, 13, startIndex, xiaoji);
if (index < lst.Count)
{
msExcelUtil.CopyRow(sheet, startIndex);
startIndex++;
index++;
msExcelUtil.AddRow(sheet, startIndex);
}
else
{
msExcelUtil.DeleteRow(sheet, startIndex + 1);
}
}
return(startIndex);
}
public ActionResult PaymentExport(string serviceTrade, string projectName, string projectShortName, string supplierName, DateTime?FactPaymentTimeStart, DateTime?FactPaymentTimeEnd,
string FactPaymentAmtStart, string FactPaymentAmtEnd, string PayChk = "否")
{
string absPath = Server.MapPath(basePath);
if (!Directory.Exists(absPath))
{
Directory.CreateDirectory(absPath);
}
string createFileName = "对公付款汇总_" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx";
string path = absPath + createFileName;
string templateFile = Server.MapPath(tempPath + "对公付款汇总.xlsx");// 模板的路径
System.IO.File.Copy(templateFile, path);
MSExcelUtil msExcelUtil = new MSExcelUtil();
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path);
Worksheet sheet = workbook.ActiveSheet;
decimal? amtStart = string.IsNullOrEmpty(FactPaymentAmtStart) ? new Nullable <decimal>() : decimal.Parse(FactPaymentAmtStart);
decimal? amtEnd = string.IsNullOrEmpty(FactPaymentAmtEnd) ? new Nullable <decimal>() : decimal.Parse(FactPaymentAmtEnd);
List <FlowOrderDto> list = service.PaymentListSearch(serviceTrade, projectName, projectShortName, supplierName, FactPaymentTimeStart,
FactPaymentTimeEnd, amtStart, amtEnd, PayChk);
int startIndex = 2;
if (list.Count > 0)
{
foreach (FlowOrderDto dto in list)
{
msExcelUtil.SetCellValue(sheet, 1, startIndex, (startIndex - 1).ToString());
msExcelUtil.SetCellValue(sheet, 2, startIndex, dto.Year);
msExcelUtil.SetCellValue(sheet, 3, startIndex, dto.FactPaymentTime);
msExcelUtil.SetCellValue(sheet, 4, startIndex, dto.ModelType);
msExcelUtil.SetCellValue(sheet, 5, startIndex, dto.ServiceTrade);
msExcelUtil.SetCellValue(sheet, 6, startIndex, dto.ProjectName);
msExcelUtil.SetCellValue(sheet, 7, startIndex, dto.ProjectShortName);
msExcelUtil.SetCellValue(sheet, 8, startIndex, dto.ProjectCode);
msExcelUtil.SetCellValue(sheet, 9, startIndex, dto.SupplierName);
msExcelUtil.SetCellValue(sheet, 10, startIndex, dto.Payee);
msExcelUtil.SetCellValue(sheet, 11, startIndex, dto.PreTaxAmt);
msExcelUtil.SetCellValue(sheet, 12, startIndex, dto.TaxAmt);
msExcelUtil.SetCellValue(sheet, 13, startIndex, dto.FactPaymentAmt);
msExcelUtil.SetCellValue(sheet, 14, startIndex, dto.InvoiceAmt);
msExcelUtil.SetCellValue(sheet, 15, startIndex, dto.InvoceAmtThis);
msExcelUtil.SetCellValue(sheet, 16, startIndex, dto.ProjectType);
msExcelUtil.SetCellValue(sheet, 17, startIndex, dto.ExecuteCycleStartDate);
msExcelUtil.SetCellValue(sheet, 18, startIndex, dto.ExecuteCycleEndDate);
msExcelUtil.SetCellValue(sheet, 19, startIndex, dto.ConstractChk);
msExcelUtil.SetCellValue(sheet, 20, startIndex, dto.SettlementChk);
msExcelUtil.SetCellValue(sheet, 21, startIndex, dto.InvoceChk);
msExcelUtil.SetCellValue(sheet, 22, startIndex, dto.InvoiceNO);
msExcelUtil.SetCellValue(sheet, 23, startIndex, dto.PaperChk);
msExcelUtil.SetCellValue(sheet, 24, startIndex, dto.PaymentRemark);
msExcelUtil.SetCellValue(sheet, 25, startIndex, dto.Finance_PaymentStatus);
msExcelUtil.SetCellValue(sheet, 26, startIndex, dto.Finance_PaymentAmt);
msExcelUtil.SetCellValue(sheet, 27, startIndex, dto.Finance_NotPayReason);
msExcelUtil.SetCellValue(sheet, 28, startIndex, dto.Finance_Constract);
msExcelUtil.SetCellValue(sheet, 29, startIndex, dto.Finance_SettlementChk);
msExcelUtil.SetCellValue(sheet, 30, startIndex, dto.Finance_InvoceAmt);
msExcelUtil.SetCellValue(sheet, 31, startIndex, dto.Finance_InvoceAmtThis);
//msExcelUtil.CopyRow(sheet, startIndex);
startIndex++;
msExcelUtil.AddRow(sheet, startIndex);
}
}
workbook.Save();
msExcelUtil.dispose();
return(Json(new { ExportPath = path }));
}
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);
}
}
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 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 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);
}
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();
}
