您的位置 首页 java

java 使用 easypoi 导出多表头文件

智云科技发布于 2023年8月20日评论关闭阅读(108)

效果：

java 使用 easypoi 导出多表头文件

导入依赖： 

              <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-base</artifactId>
        <version>3.2.0</version>
</dependency>
<dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-web</artifactId>
        <version>3.2.0</version>
</dependency>
<dependency>
       <groupId>cn.afterturn</groupId>
       <artifactId>easypoi- annotation </artifactId>
       <version>3.2.0</version>
</dependency>

创建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,need Merge  = 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<JanMonthlyVo> january;
/**
 * 二月
 * */@ExcelCollection(name = "二月",orderNum = "10")
private List< Feb MonthlyVo> february;
/**
 * 三月
 * */@ExcelCollection(name = "三月",orderNum = "11")
private List<MarMonthlyVo> march;
/**
 * 四月
 * */@ExcelCollection(name = "四月",orderNum = "12")
private List<AprMonthlyVo> april;
/**
 * 五月
 * */@ExcelCollection(name = "五月",orderNum = "13")
private List<MayMonthlyVo> may;
/**
 * 六月
 * */@ExcelCollection(name = "六月",orderNum = "14")
private List<JunMonthlyVo> june;
/**
 * 七月
 * */@ExcelCollection(name = "七月",orderNum = "15")
private List<JulyMonthlyVo> july;
/**
 * 八月
 * */@ExcelCollection(name = "八月",orderNum = "16")
private List<AugMonthlyVo> august;
/**
 * 九月
 * */@ExcelCollection(name = "九月",orderNum = "17")
private List<SepMonthlyVo> september;
/**
 * 十月
 * */@ExcelCollection(name = "十月",orderNum = "18")
private List<OctMonthlyVo> october;
/**
 * 十一月
 * */@ExcelCollection(name = "十一月",orderNum = "19")
private List<NovMonthlyVo> november;
/**
 * 十二月
 * */@ExcelCollection(name = "十二月",orderNum = "20")
private List<DecMonthlyVo> 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, @ Request Body PageParamVO  params )throws  Exception {
buCostReductionStatiYWService.exportExcel(response,params);
}

ServiceImpl 层： 

 /**
 * 导出
 *
 * @param response
 * @throws Exception
 */@Override
public void exportExcel(HttpServletResponse response, PageParamVO paramVO) throws Exception {
List<CostReductionStatiImprotVo> 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 <String, Object> 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<CostReductionStatiImprotVo> getAllCostReductionImportInfo(PageParamVO paramVO) {
ArrayList<CostReductionStatiImprotVo> ImprotVoList = new ArrayList<>();
List<DataFilter> filter = paramVO.getFilter();
List<BuCostReductionStatiEntity> 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<JanMonthlyVo> janMonthlyVos = new ArrayList<>();
ArrayList<FebMonthlyVo> febMonthlyVos = new ArrayList<>();
ArrayList<MarMonthlyVo> marMonthlyVos = new ArrayList<>();
ArrayList<AprMonthlyVo> aprMonthlyVos = new ArrayList<>();
ArrayList<MayMonthlyVo> mayMonthlyVos = new ArrayList<>();
ArrayList<JunMonthlyVo> junMonthlyVos = new ArrayList<>();
ArrayList<JulyMonthlyVo> julyMonthlyVos = new ArrayList<>();
ArrayList<AugMonthlyVo> augMonthlyVos = new ArrayList<>();
ArrayList<SepMonthlyVo> sepMonthlyVos = new ArrayList<>();
ArrayList<OctMonthlyVo> octMonthlyVos = new ArrayList<>();
ArrayList<NovMonthlyVo> novMonthlyVos = new ArrayList<>();
ArrayList<DecMonthlyVo> 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;
}

第一次写多表头导出，特意记录一下。

标签:
打赏 点赞(1)

文章来源：智云一二三科技

文章标题：java 使用 easypoi 导出多表头文件

文章地址：https://www.zhihuclub.com/174430.shtml

关于作者: 智云科技

相关文章

热门文章

1分享新浪图床上传接口源码

点赞(915)阅读(60,645)

2PHP简单实现路由Route功能

点赞(1.13K)阅读(35,954)

3Tideways、xhprof 和 xhgui 打造 PHP 非侵入式监控平台

点赞(345)阅读(4,282)

4centos系统如何查看是否安装了mysql

点赞(515)阅读(3,773)

5curl 工具简述

点赞(487)阅读(2,969)
网站地图