easyExcel实现excel导出

最新推荐文章于 2024-07-20 15:39:26 发布

昵称真的好难取

最新推荐文章于 2024-07-20 15:39:26 发布

阅读量548

点赞数 1

文章标签： excel java 前端

本文链接：https://blog.csdn.net/weixin_42168592/article/details/127864959

版权

控制层:

@GetMapping("/attendanceStatistics/export")
public void attendanceStatistics (AttendanceStatisticsScreeningDto dto,HttpServletResponse response){
        attendanceRecordService.attendanceStatistics(dto,response);
    }

实现:

PageVo<AttendanceStatisticsListVo> listS = this.getAttendanceStatistics(dto,PageDto.of(1,Integer.MAX_VALUE));
        List<AttendanceStatisticsListVo> resultList = listS.getList();
        LinkedList<AttendanceStatisticsDto> exportList = new LinkedList<>();
        for (int i = 0; i < resultList.size(); i++) {
            AttendanceStatisticsDto exportVo = new AttendanceStatisticsDto();
            exportVo.setSalerUserName(resultList.get(i).getSalerUserName());
            exportVo.setExpectedQuantity(resultList.get(i).getExpectedQuantity());
            exportVo.setActualQuantity(resultList.get(i).getActualQuantity());
            exportVo.setNoClocking(resultList.get(i).getNoClocking());
            if (ObjectUtil.isNotEmpty(resultList.get(i).getStatisticsListBos())) {
                StringBuffer listBo = new StringBuffer();
                resultList.get(i).getStatisticsListBos().forEach(attendanceStatisticsListBo -> {
                    String time = attendanceStatisticsListBo.getTime().format(DateTimeFormatter.ISO_DATE);
                    String projectName = attendanceStatisticsListBo.getProjectName();
                    StringBuffer stringBuffer = new StringBuffer();
                    stringBuffer.append(time).append(" ").append(projectName);
                    listBo.append(stringBuffer).append(",");
                });
                exportVo.setStatisticsListBos(listBo.toString());
            }
            exportList.add(exportVo);
        }
        //定义导出文件参数
        CommonExportExcelDto exportExcelDto = new CommonExportExcelDto();
        exportExcelDto.setResponse(response);
        exportExcelDto.setFileName("考勤统计");
        exportExcelDto.setSheetName("工作表1");
        exportExcelDto.setList(exportList);
        exportExcelDto.setClazz(AttendanceStatisticsDto.class);
        //4:输出文件
        easyExcelUtil.exportExcel(exportExcelDto);

导出AttendanceStatisticsDto内容

@Data
public class AttendanceStatisticsDto {

    @ExcelProperty("销售人员名称")
    private String salerUserName;

    @ExcelProperty("应该次数")
    private Integer expectedQuantity;

    @ExcelProperty("实际次数")
    private Integer actualQuantity;

    @ExcelProperty("未打卡次数")
    private Integer noClocking;

    @ExcelProperty("未打卡明细")
    private String statisticsListBos;
}

CommonExportExcelDto内容

@Data
public class CommonExportExcelDto implements Serializable {
    /**
     * response
     */
    private HttpServletResponse response;
    /**
     * 文件名称
     */
    private String fileName;
    /**
     * sheet名称
     */
    private String sheetName;
    /**
     * 数据集
     */
    private List<?> list;
    /**
     * class
     */
    private Class<?> clazz;
}

输出文件EasyExcelUtil方法实现

@Component
@Slf4j
public class EasyExcelUtil {
    /**
     * 读取excel
     *
     * @param file
     * @param clazz
     * @return
     */
    public List readExcel(MultipartFile file, Class clazz) {
        List list = new ArrayList<>();
        try {
            EasyExcel.read(file.getInputStream(), clazz, new PageReadListener<T>(dataList -> {
                for (int i = 0; i < dataList.size(); i++) {
                    list.add(dataList.get(i));
                }
            })).sheet().headRowNumber(1).doRead();
        } catch (Exception e) {
            log.error("解析excel失败：" + e);
        }
        return list;
    }

    /**
     * 导出
     *
     * @param exportExcelDto
     */
    public void exportExcel(CommonExportExcelDto exportExcelDto) {
        HttpServletResponse response = exportExcelDto.getResponse();
        try {
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(exportExcelDto.getFileName() + DateUtil.today() + ".xlsx", "UTF-8"));
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            //设置背景颜色
            headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
            //设置头字体
            WriteFont headWriteFont = new WriteFont();
            headWriteFont.setFontHeightInPoints((short) 13);
            headWriteFont.setBold(true);
            headWriteCellStyle.setWriteFont(headWriteFont);
            //设置头居中
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            //内容策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            //设置 水平居中
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
            EasyExcel.write(response.getOutputStream(), exportExcelDto.getClazz())
                    // 设置头和内容居中
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    // 设置内容自适应宽度
                    .registerWriteHandler(new LongestCellWidthHandler())
                    // 导出文件名
                    .autoCloseStream(Boolean.TRUE).sheet(exportExcelDto.getSheetName())
                    .doWrite(exportExcelDto.getList());
        } catch (Exception e) {
            e.printStackTrace();
            log.error("文件导出失败：" + e);
        }
    }

}