EasyExcel导出动态列
这几天一直在写导出excel表然后我用的是EasyExcelEasyExcel官网
一定要看下去
先看截图动态导出的列
看这两次导出的
直接上代码先导入maven
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.4</version>
</dependency>
首先先看官方例子
仔细看红线哪一行,我们用的就是List<List>方式
为什么不直接查到数据直接写不就好了,
我也试过了数据对应的列会有问题等后面说
@PostMapping("/excel/export")
public void importExcel(HttpServletResponse response, @RequestBody WorkOrderQueryVo workOrderQueryVo) throws Exception {
String fileName = "C:\\Users\\win\\Desktop\\xxx.xlsx"; //先创建文件不然会报错
EasyExcel.write(fileName)
// 这里放入动态头
.head(workOrderService.head(workOrderQueryVo.getExcelType())).registerConverter(new LocalDateTimeConverter()).sheet("工单信息")
// 当然这里数据也可以用 List<List<String>> 去传入
.doWrite(workOrderService.findAllExcelSearch(workOrderQueryVo));
}
.registerConverter(new LocalDateTimeConverter())这是我的日期是LocalDateTime, EasyExcel默认支持Date
head方法
public List<List<String>> head(Integer excelType) {
List<List<String>> list = new ArrayList<List<String>>();
List<String> head0 = new ArrayList<String>(); //创建每一列
head0.add("故障编号");
list.add(head0);
List<String> head1 = new ArrayList<String>();
head1.add("设备名称");
list.add(head1);
List<String> head2 = new ArrayList<String>();
list.add(head2);
head2.add("所属单位");
List<String> head3 = new ArrayList<String>();
list.add(head3);
head3.add("故障时间");
List<String> head4 = new ArrayList<String>();
list.add(head4);
head4.add("派单时间");
List<String> head5 = new ArrayList<String>();
list.add(head5);
head5.add("故障类型");
List<String> head6 = new ArrayList<String>();
list.add(head6);
head6.add("派单方式");
List<String> head7 = new ArrayList<String>();
list.add(head7);
head7.add("工单状态");
if (!excelType.equals(TWO)) {
List<String> head8 = new ArrayList<String>();
head8.add("接单人员");
list.add(head8);
}
if (excelType.equals(TWO)) {
List<String> head8 = new ArrayList<String>();
head8.add("派给人员");
list.add(head8);
}
if (excelType.equals(SEVEN) || excelType.equals(EIGHT) || excelType.equals(NINE)) {
List<String> head9 = new ArrayList<String>();
head9.add("接单单位");
list.add(head9);
}
if (excelType.equals(EIGHT)) {
List<String> head10 = new ArrayList<String>();
head10.add("维修结束时间");
list.add(head10);
}
if (excelType.equals(TEN)) {
List<String> head10 = new ArrayList<String>();
head10.add("工单结束时间");
List<String> head11 = new ArrayList<String>();
head11.add("修复方式");
List<String> head12 = new ArrayList<String>();
head12.add("处理时长");
list.add(head10);
list.add(head11);
list.add(head12);
}
return list;
}
这里就不多介绍了
还记得上面说的吗?
为啥不用,
List<String> head0 = new ArrayList<String>(); //创建每一列
head0.add("故障编号");
list.add(head0);
List<String> head1 = new ArrayList<String>();
head1.add("设备名称");
生成的时候 这样的但是你查询到的数据比如是个list,设备名称的值所索引是0数据会直接写入故障编号那一列,所有我们用List<List> 去传入
@Override
public List<List<String>> findAllExcelSearch(WorkOrderQueryVo workOrderQueryVo) {
List<List<String>> list = new ArrayList<List<String>>(); //储存数据
for (WorkOrderDto workOrderDto : records) { //查询的数据进行遍历
List<String> head0 = new ArrayList<String>();//创建一个集合来对查询出的数据进行排序;
head0.add(0,workOrderDto.getFaultId());//0代表这第一列
head0.add(1,workOrderDto.getDeviceName());
head0.add(2,workOrderDto.getOrgName());
head0.add(3,workOrderDto.getFaultTime());
head0.add(4,workOrderDto.getSendTime().toString());
head0.add(5,workOrderDto.getFaultType());
if (workOrderDto.getDispatchType()!=null){
if (workOrderDto.getDispatchType().equals(ZERO)){
head0.add(6,"自动");
}else {
head0.add(6,"手动");
}
}
if (workOrderDto.getIsDispatch()!=null){
if (workOrderDto.getIsDispatch().equals(ZERO)){
head0.add(7,"已派单");
}else {
List<String> head7 = new ArrayList<String>();
head0.add(7,"未派单");
}
}
if (!workOrderQueryVo.getExcelType().equals(TWO)) {
head0.add(8,workOrderDto.getNickname());
}
if (workOrderQueryVo.getExcelType().equals(SEVEN) || workOrderQueryVo.getExcelType().equals(EIGHT) || workOrderQueryVo.getExcelType().equals(NINE)) {
head0.add(9,workOrderDto.getOrderOrgan());
}
if (workOrderQueryVo.getExcelType().equals(EIGHT)) {
head0.add(10,workOrderDto.getFaultEndTime().toString());
}
if (workOrderQueryVo.getExcelType().equals(TEN)) {
head0.add(10,workOrderDto.getFaultEndTime().toString());
if (workOrderDto.getRepairType()!=null){
if (workOrderDto.getRepairType().equals(ZERO)){
head0.add(11,"自动");
}else {
head0.add(11,"手动");
}
}else {
head0.add(11,workOrderDto.getFaultTime());
}
head0.add(12,workOrderDto.getFaultTime());
}
list.add(head0);//把排序好的数据添加的集合中
}
return list;
}
本来不是把得到的数据在进行处理的我当时想了一个方法
看到实体类的@ExcelProperty的index来解决的但是不行不知道新的版本有没有解决可能是我太菜了,最后把LocalDateTime自定义的转换工具献上
public class LocalDateTimeConverter implements Converter<LocalDateTime> {
@Override
public Class<LocalDateTime> supportJavaTypeKey() {
return LocalDateTime.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public LocalDateTime convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
return LocalDateTime.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
}
@Override
public CellData<String> convertToExcelData(LocalDateTime value, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
return new CellData<>(value.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
}
}
这是直接写入可以改成web中的写也就是导出
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("测试, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream())
直接拿官方的例子就行了
qq群:909886569有问题联系我