springboot2.6.1导出xlsx表格

需求：
将列表数据导出为xlsx表格
1.pom.xm

 
        
            com.alibaba
            easyexcel
            3.0.5
        
1
2
3
4
5
6

2.controller

    @GetMapping("/export")
   @ResponseStatus(HttpStatus.OK)
   @ApiImplicitParams({
           @ApiImplicitParam(name = "time", value = "查询日期", required = true, dataTypeClass = String.class)
   })
   public void export(@ApiIgnore @RequestAttribute(value = Constants.SESSION_USER) User loginUser,
                      @RequestParam(value = "time", required = true) String time,
                      HttpServletResponse response) {
       statisticAnalysisService.export( time, response);
   }
1
2
3
4
5
6
7
8
9
10

3.service

  public void export(String time, HttpServletResponse response) {
        try {
            List list = getExportList(time);
            if (CollectionUtils.isNotEmpty(list)) {
                ExcelUtil.outputExcel(response, list, ExportStatisticResponse.class, "statistic-analysis.xlsx");
            }
        } catch (
                IOException e) {
            logger.error("导出失败，原因是e:{}", e.getMessage());
        }

    }

 private List getExportList(String time) {
        List list = statisticAnalysisMapper.getStatisticListByTime(time);
        List responseList = new ArrayList<>(12);
        if (CollectionUtils.isNotEmpty(list)) {
            for (StatisticDetailResponse dto: list) {
                ExportStatisticResponse statisticResponse = new ExportStatisticResponse();
                BeanUtils.copyProperties(dto, statisticResponse);
                statisticResponse.setEndTime(dto.getEndTimeStr());
                statisticResponse.setStartTime(dto.getStartTimeStr());
                responseList.add(statisticResponse);
            }
        }
        return responseList;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

4.工具类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
public class ExcelUtil {

    public static void outputExcel(HttpServletResponse response, List list, Class cla, String sheetName) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        String fileName = URLEncoder.encode(sheetName, "UTF-8");
        response.setHeader("Content-Disposition","attachment;filename="+fileName);
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
        WriteSheet sheet = EasyExcel.writerSheet(0, sheetName).head(cla).build();
        excelWriter.write(list, sheet);
        excelWriter.finish();
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

5.实体类

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;
import lombok.EqualsAndHashCode;

@Data
@ColumnWidth(26)
@EqualsAndHashCode
public class ExportStatisticResponse {

    @ExcelProperty("开始时间")
    private String startTime;
    /**
     * session结束时间
     */
    @ExcelProperty("结束时间")
    private String endTime;

    /**
     * 耗时
     */
    @ExcelProperty("耗时 (min)")
    private Integer duration;
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

6.导出结果