效果：
导入依赖：
cn.afterturn
easypoi-base
3.2.0
cn.afterturn
easypoi-web
3.2.0
cn.afterturn
easypoi-annotation
3.2.0
创建Excel 对应文件 CostReductionStatiImprotVo
@Data
@EqualsAndHashCode(callSuper=false)
public class CostReductionStatiImprotVo implements Serializable {
private static final long serialVersionUID = -1L;
/**
* 年度
*/
@Excel(isWrap = false,name="年度",orderNum = "1",width = 20,needMerge = true)
private String year;
/**
* 供应商编码
*/
@Excel(isWrap = false,name="供应商编码",orderNum = "2",width = 23,needMerge = true)
private String suppCode;
/**
* 供应商名称
*/
@Excel(isWrap = false,name="供应商名称",orderNum = "3",width = 30,needMerge = true)
private String suppName;
/**
* 物料编码
*/
@Excel(isWrap = false,name="物料编码",orderNum = "4",width = 25,needMerge = true)
private String masterCode;
/**
* 物料名称
*/
@Excel(isWrap = false,name="物料名称",orderNum = "5",width = 30,needMerge = true)
private String masterName;
/**
* 调价原因
*/
@Excel(isWrap = false,name="调价原因",orderNum = "6",width = 30,needMerge = true)
private String modifyPriceReson;
/**
* 开始日期
*/
@Excel(isWrap = false,name="开始日期",orderNum = "7",width = 30,needMerge = true)
private String startTime;
/**
* 单价差额
*/
@Excel(isWrap = false,name="单价差额",orderNum = "8",width = 30,needMerge = true)
private String unitPriceDifference;
/**
* 一月
* */
@ExcelCollection(name = "一月",orderNum = "9")
private List january;
/**
* 二月
* */
@ExcelCollection(name = "二月",orderNum = "10")
private List february;
/**
* 三月
* */
@ExcelCollection(name = "三月",orderNum = "11")
private List march;
/**
* 四月
* */
@ExcelCollection(name = "四月",orderNum = "12")
private List april;
/**
* 五月
* */
@ExcelCollection(name = "五月",orderNum = "13")
private List may;
/**
* 六月
* */
@ExcelCollection(name = "六月",orderNum = "14")
private List june;
/**
* 七月
* */
@ExcelCollection(name = "七月",orderNum = "15")
private List july;
/**
* 八月
* */
@ExcelCollection(name = "八月",orderNum = "16")
private List august;
/**
* 九月
* */
@ExcelCollection(name = "九月",orderNum = "17")
private List september;
/**
* 十月
* */
@ExcelCollection(name = "十月",orderNum = "18")
private List october;
/**
* 十一月
* */
@ExcelCollection(name = "十一月",orderNum = "19")
private List november;
/**
* 十二月
* */
@ExcelCollection(name = "十二月",orderNum = "20")
private List december;
/**
* 累计用量合计
*/
@Excel(isWrap = false,name="累计用量合计",orderNum = "21",width = 30,needMerge = true)
private Long cumulativeUsSum;
/**
* 降本金额合计
*/
@Excel(isWrap = false,name="降本金额合计",orderNum = "22",width = 30,needMerge = true)
private String costReductAmountSum;
/**
* 统计时间
*/
@Excel(isWrap = false,name="统计时间",orderNum = "23",width = 30,needMerge = true)
private String statiTime;
} 说明 ： orderNum ：排序，如果属性顺序和表格字段顺序不一致，可已设置顺序
width ：表格列宽
needMerge ：是否需要合并单元格
@ExcelCollection(name = "十二月",orderNum = "20")
对应双层表头字段
/** 特别注意！！！！ 踩坑**/
属性字段必须遵守驼峰命名发，首字母小写！！！！！！
注意属性，Excel支持属性类型不多，我使用的都是基本常用属性，在serviceImpl类中再做在转换CostReductionStatiImprotVo类中，多层表头字段对应一个lis list里面的也需单独建VO
例如：JanMonthlyVo
@Data
@EqualsAndHashCode(callSuper=false)
public class JanMonthlyVo implements Serializable {
private static final long serialVersionUID = -2L;
/**
* 一月累计用量
*/
@Excel(isWrap = false,name="一月累计用量",orderNum = "1",width = 15)
private Integer cumulativeUsJan;
/**
* 一月降本金额
*/
@Excel(isWrap = false,name="一月降本金额",orderNum = "2",width = 15)
private String costReductAmountJan;
}Contorller 层：
/**
* 导出多重表头
* @param response
* @throws Exception
*/
@PostMapping("/exportExcel")
public void exportExcel(HttpServletResponse response, @RequestBody PageParamVO params)throws Exception{
buCostReductionStatiYWService.exportExcel(response,params);
}ServiceImpl 层：
/**
* 导出
*
* @param response
* @throws Exception
*/
@Override
public void exportExcel(HttpServletResponse response, PageParamVO paramVO) throws Exception {
List importInfoList = getAllCostReductionImportInfo(paramVO);
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
//test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
response.setHeader("Content-Disposition", "attachment;filename=file.xlsx");
HashMap map = new HashMap<>();
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(),CostReductionStatiImprotVo.class,importInfoList);
workbook.setSheetName(0,"降本统计");
ServletOutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
workbook.close();
}
//获取数据
public List getAllCostReductionImportInfo(PageParamVO paramVO) {
ArrayList ImprotVoList = new ArrayList<>();
List filter = paramVO.getFilter();
List entityList = buCostReductionStatiMapper.selectList(WrapperFilter.buildQueryWrapper(new QueryWrapper<>(),filter));
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:ss:mm");
if (CollectionUtils.isNotEmpty(entityList)) {
for (BuCostReductionStatiEntity entity : entityList) {
JanMonthlyVo janMonthlyVo = new JanMonthlyVo();
FebMonthlyVo febMonthlyVo = new FebMonthlyVo();
MarMonthlyVo marMonthlyVo = new MarMonthlyVo();
AprMonthlyVo aprMonthlyVo = new AprMonthlyVo();
MayMonthlyVo mayMonthlyVo = new MayMonthlyVo();
JunMonthlyVo junMonthlyVo = new JunMonthlyVo();
JulyMonthlyVo julyMonthlyVo = new JulyMonthlyVo();
AugMonthlyVo augMonthlyVo = new AugMonthlyVo();
SepMonthlyVo sepMonthlyVo = new SepMonthlyVo();
OctMonthlyVo octMonthlyVo = new OctMonthlyVo();
NovMonthlyVo novMonthlyVo = new NovMonthlyVo();
DecMonthlyVo decMonthlyVo = new DecMonthlyVo();
ArrayList janMonthlyVos = new ArrayList<>();
ArrayList febMonthlyVos = new ArrayList<>();
ArrayList marMonthlyVos = new ArrayList<>();
ArrayList aprMonthlyVos = new ArrayList<>();
ArrayList mayMonthlyVos = new ArrayList<>();
ArrayList junMonthlyVos = new ArrayList<>();
ArrayList julyMonthlyVos = new ArrayList<>();
ArrayList augMonthlyVos = new ArrayList<>();
ArrayList sepMonthlyVos = new ArrayList<>();
ArrayList octMonthlyVos = new ArrayList<>();
ArrayList novMonthlyVos = new ArrayList<>();
ArrayList decMonthlyVos = new ArrayList<>();
CostReductionStatiImprotVo improtVo = new CostReductionStatiImprotVo();
improtVo.setYear(entity.getYear());
improtVo.setMasterCode(entity.getMasterCode());
improtVo.setMasterName(entity.getMasterName());
improtVo.setSuppCode(entity.getSuppCode());
improtVo.setSuppName(entity.getSuppName());
switch (entity.getModifyPriceReson()) {
case "T02":
improtVo.setModifyPriceReson(BuCostReductionStatiConstans.T02CH);
break;
case "T03":
improtVo.setModifyPriceReson(BuCostReductionStatiConstans.T03CH);
break;
case "T04":
improtVo.setModifyPriceReson(BuCostReductionStatiConstans.T04CH);
break;
case "T05":
improtVo.setModifyPriceReson(BuCostReductionStatiConstans.T05CH);
break;
case "T06":
improtVo.setModifyPriceReson(BuCostReductionStatiConstans.T06CH);
break;
}
improtVo.setStartTime(format.format(entity.getStartTime()));
improtVo.setUnitPriceDifference(entity.getUnitPriceDifference()==null?null:entity.getUnitPriceDifference().toString());
improtVo.setCumulativeUsSum(entity.getCumulativeUsSum());
improtVo.setCostReductAmountSum(entity.getCostReductAmountSum()==null?null:entity.getCostReductAmountSum().toString());
improtVo.setStatiTime(format.format(entity.getStatiTime()));
janMonthlyVo.setCumulativeUsJan(entity.getCumulativeUsJan());
janMonthlyVo.setCostReductAmountJan(entity.getCostReductAmountJan()==null?null:entity.getCostReductAmountJan().toString());
janMonthlyVos.add(janMonthlyVo);
improtVo.setJanuary(janMonthlyVos);
febMonthlyVo.setCumulativeUsFeb(entity.getCumulativeUsFeb());
febMonthlyVo.setCostReductAmountFeb(entity.getCostReductAmountFeb()==null?null:entity.getCostReductAmountFeb().toString());
febMonthlyVos.add(febMonthlyVo);
improtVo.setFebruary(febMonthlyVos);
marMonthlyVo.setCumulativeUsMar(entity.getCumulativeUsMar());
marMonthlyVo.setCostReductAmountMar(entity.getCostReductAmountMar()==null?null:entity.getCostReductAmountMar().toString());
marMonthlyVos.add(marMonthlyVo);
improtVo.setMarch(marMonthlyVos);
aprMonthlyVo.setCumulativeUsApr(entity.getCumulativeUsApr());
aprMonthlyVo.setCostReductAmountApr(entity.getCostReductAmountApr()==null?null:entity.getCostReductAmountApr().toString());
aprMonthlyVos.add(aprMonthlyVo);
improtVo.setApril(aprMonthlyVos);
mayMonthlyVo.setCumulativeUsMay(entity.getCumulativeUsMay());
mayMonthlyVo.setCostReductAmountMay(entity.getCostReductAmountMay()==null?null:entity.getCostReductAmountMay().toString());
mayMonthlyVos.add(mayMonthlyVo);
improtVo.setMay(mayMonthlyVos);
junMonthlyVo.setCumulativeUsJun(entity.getCumulativeUsJun());
junMonthlyVo.setCostReductAmountJun(entity.getCostReductAmountJun()==null?null:entity.getCostReductAmountJun().toString());
junMonthlyVos.add(junMonthlyVo);
improtVo.setJune(junMonthlyVos);
julyMonthlyVo.setCumulativeUsJul(entity.getCumulativeUsJul());
julyMonthlyVo.setCostReductAmountJul(entity.getCostReductAmountJul()==null?null:entity.getCostReductAmountJul().toString());
julyMonthlyVos.add(julyMonthlyVo);
improtVo.setJuly(julyMonthlyVos);
augMonthlyVo.setCumulativeUsAug(entity.getCumulativeUsAug());
augMonthlyVo.setCostReductAmountAug(entity.getCostReductAmountAug()==null?null:entity.getCostReductAmountAug().toString());
augMonthlyVos.add(augMonthlyVo);
improtVo.setAugust(augMonthlyVos);
sepMonthlyVo.setCumulativeUsSep(entity.getCumulativeUsSep());
sepMonthlyVo.setCostReductAmountSep(entity.getCostReductAmountSep()==null?null:entity.getCostReductAmountSep().toString());
sepMonthlyVos.add(sepMonthlyVo);
improtVo.setSeptember(sepMonthlyVos);
improtVo.getSeptember();
octMonthlyVo.setCumulativeUsOct(entity.getCumulativeUsOct());
octMonthlyVo.setCostReductAmountOct(entity.getCostReductAmountOct()==null?null:entity.getCostReductAmountOct().toString());
octMonthlyVos.add(octMonthlyVo);
improtVo.setOctober(octMonthlyVos);
novMonthlyVo.setCumulativeUsNov(entity.getCumulativeUsNov());
novMonthlyVo.setCostReductAmountNov(entity.getCostReductAmountNov()==null?null:entity.getCostReductAmountNov().toString());
novMonthlyVos.add(novMonthlyVo);
improtVo.setNovember(novMonthlyVos);
decMonthlyVo.setCumulativeUsDec(entity.getCumulativeUsDec());
decMonthlyVo.setCostReductAmountDec(entity.getCostReductAmountDec()==null?null:entity.getCostReductAmountDec().toString());
decMonthlyVos.add(decMonthlyVo);
improtVo.setDecember(decMonthlyVos);
ImprotVoList.add(improtVo);
}
return ImprotVoList;
}
return null;
} 第一次写多表头导出，特意记录一下。
页面更新：2024-03-08
本站资料均由网友自行发布提供，仅用于学习交流。如有版权问题，请与我联系，QQ：4156828
© CopyRight 2008-2024 All Rights Reserved. Powered By bs178.com 闽ICP备11008920号-3
闽公网安备35020302034844号
