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 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 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 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();
}
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();
}
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();
}
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();
}
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 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 ExportYanjiu(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_YanJiuDto 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.zhixingfangshi);
msExcelUtil.SetCellValue(sheet, 6, startIndex, dto.gongzuofenlei);
msExcelUtil.SetCellValue(sheet, 7, startIndex, dto.gongzuoneirong);
msExcelUtil.SetCellValue(sheet, 8, startIndex, dto.leixingpinpai);
msExcelUtil.SetCellValue(sheet, 9, startIndex, dto.guigeyaoqiu);
msExcelUtil.SetCellValue(sheet, 10, startIndex, dto.zhiliangbiaozhun);
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();
}
public void ExportZhixing(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 startRow = startIndex;
int index = 1;
foreach (Quotation_ZhiXingDto 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.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, 12, startIndex, dto.Count);
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 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 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 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();;
}
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 <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 ExportQuotationHor(string path, string projectName, string projectShortName, string projectCode,
List <QuotationExport_Head_Dto> HeaderDtoList, List <QuotationExport_Left_Dto> LeftDtoList, List <QuotationExport_Data_Dto> DataDtoList)
{
MSExcelUtil msExcelUtil = new MSExcelUtil();
Workbook workbook = msExcelUtil.OpenExcelByMSExcel(path);
Worksheet sheet = workbook.ActiveSheet;
//项目名称、简称、编号
msExcelUtil.SetCellValue(sheet, 2, 1, projectName);
msExcelUtil.SetCellValue(sheet, 4, 1, projectShortName);
msExcelUtil.SetCellValue(sheet, 6, 1, projectCode);
//动态列头
int dStartColIndex = 5;
int beizhuCount = 0;
for (int i = 0; i < HeaderDtoList.Count; i++)
{
QuotationExport_Head_Dto dto = HeaderDtoList[i];
msExcelUtil.SetCellValue(sheet, dStartColIndex, 2, dto.QuotationMode);
dStartColIndex = dStartColIndex + 3;
if (i < (HeaderDtoList.Count - 1))
{
msExcelUtil.CopyAndInsertColumns(sheet, dStartColIndex - 3, 2, dStartColIndex - 1, 8, dStartColIndex, 2);
if (beizhuCount == 0)
{
msExcelUtil.SetColumnWidth(sheet, dStartColIndex + 1, 10);
}
//复制备注
beizhuCount++;
msExcelUtil.CopyColumn(sheet, dStartColIndex + 3 + beizhuCount);
msExcelUtil.AddColumn(sheet, dStartColIndex + 4 + beizhuCount);
msExcelUtil.SetCellValue(sheet, dStartColIndex + 4 + beizhuCount, 2, "备注" + (i + 2));
}
}
beizhuCount = 0;
for (int i = 0; i < HeaderDtoList.Count; i++)
{
//复制备注
beizhuCount++;
msExcelUtil.SetColumnWidth(sheet, dStartColIndex + beizhuCount, 20);
}
var rowIndex = 4;
foreach (QuotationExport_Left_Dto dto in LeftDtoList)
{
int colIndex = 1;
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, rowIndex - 3);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.SupplierName);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.zhixingshengfen);
msExcelUtil.SetCellValue(sheet, colIndex++, rowIndex, dto.zhixingchengshi);
//msExcelUtil.CopyRow(sheet, rowIndex);
rowIndex++;
msExcelUtil.AddRow(sheet, rowIndex);
}
msExcelUtil.DeleteRow(sheet, rowIndex);
msExcelUtil.DeleteRow(sheet, rowIndex);
int beizhuStart = dStartColIndex + 1;
decimal[] zongjiArr = new decimal[LeftDtoList.Count];
foreach (QuotationExport_Data_Dto dto in DataDtoList)
{
dStartColIndex = 5;
var dataRowIndex = 4;
int dcolIndex = HeaderDtoList.FindIndex(x => x.QuotationMode == dto.QuotationMode);
int drowIndex = LeftDtoList.FindIndex(x => (x.SupplierName == dto.SupplierName) && (x.zhixingshengfen == dto.zhixingshengfen) && (x.zhixingchengshi == dto.zhixingchengshi));
dStartColIndex += dcolIndex * 3;
dataRowIndex += drowIndex;
decimal heji = 0;
if (dto.danjia.HasValue && dto.shuliang.HasValue)
{
heji = dto.danjia.Value * dto.shuliang.Value;
}
zongjiArr[drowIndex] += heji;
msExcelUtil.SetCellValue(sheet, dStartColIndex++, dataRowIndex, dto.danjia);
msExcelUtil.SetCellValue(sheet, dStartColIndex++, dataRowIndex, dto.shuliang);
msExcelUtil.SetCellValue(sheet, dStartColIndex++, dataRowIndex, heji);
msExcelUtil.SetCellValue(sheet, beizhuStart + dcolIndex, dataRowIndex, dto.beizhu);
msExcelUtil.SetCellValue(sheet, beizhuStart - 1, dataRowIndex, zongjiArr[drowIndex]);
}
workbook.Save();
msExcelUtil.dispose();
}
