easyexcel处理复杂表头

最新推荐文章于 2024-07-30 17:15:08 发布

小柒v

最新推荐文章于 2024-07-30 17:15:08 发布

阅读量509

点赞数 14

文章标签： java

本文链接：https://blog.csdn.net/qq_44982110/article/details/137516184

版权

本文介绍了如何在Java项目中使用EasyExcel库处理Excel文件，包括下载模板、创建Excel实体类、导出Excel数据并支持单元格合并，以及处理上传的Excel文件进行导入操作。

摘要由CSDN通过智能技术生成

需求，模板如下

在这里插入图片描述

功能如下

在这里插入图片描述

开始整活，依赖包。

<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>easyexcel</artifactId>
	<version>3.2.1</version>
</dependency>

下载导入模板

1.方法

@GetMapping("/download")
    public void download(HttpServletResponse response) {
        ExcelUtil.exportExcel(new ArrayList<>(), "xxxx导入模板", BasicIntegralDownloadVo.class, response);
    }

2.实体

@ExcelIgnoreUnannotated
@Data
public class BasicIntegralDownloadVo {

    private static final long serialVersionUID = 1L;

    @ColumnWidth(value = 8)
    @ExcelProperty(value = {"积分制积分明细表","户名"},index = 0)
    private String name;

    @ColumnWidth(value = 8)
    @ExcelProperty(value = {"积分制积分明细表","人口类型"},index = 1)
    private String populationType;

    @ColumnWidth(value = 8)
    @ExcelProperty(value = {"积分制积分明细表","门牌号"},index = 2)
    private String houseNumber;

    @ColumnWidth(value = 8)
    @ExcelProperty(value = {"积分制积分明细表","联系方式"},index = 3)
    private String mobile;

    @ColumnWidth(value = 8)
    @ExcelProperty(value = {"积分制积分明细表","身份证"},index = 4)
    private String idCard;

    @ColumnWidth(value = 8)
    @ExcelProperty(value = {"积分制积分明细表","积分年度"},index = 5)
    private String integralYear;

    @ColumnWidth(value = 10)
    @ExcelProperty(value = {"积分制积分明细表","农户零星养殖鸡鸭棚规范管理", "第1季度"},index = 6)
    private Integer type1;

    @ColumnWidth(value = 10)
    @ExcelProperty(value = {"积分制积分明细表","农户零星养殖鸡鸭棚规范管理", "第2季度"},index = 7)
    private Integer type2;

    @ColumnWidth(value = 10)
    @ExcelProperty(value = {"积分制积分明细表","农户零星养殖鸡鸭棚规范管理", "第3季度"},index = 8)
    private Integer type3;

    @ColumnWidth(value = 10)
    @ExcelProperty(value = {"积分制积分明细表","农户零星养殖鸡鸭棚规范管理", "第4季度"},index = 9)
    private Integer type4;

    @ColumnWidth(value = 10)
    @ExcelProperty(value = {"积分制积分明细表","小菜园规范种植、禁止毁绿占绿进行种植", "第1季度"},index = 10)
    private Integer type5;

    @ColumnWidth(value = 10)
    @ExcelProperty(value = {"积分制积分明细表","小菜园规范种植、禁止毁绿占绿进行种植", "第2季度"},index = 11)
    private Integer type6;

    @ColumnWidth(value = 10)
    @ExcelProperty(value = {"积分制积分明细表","小菜园规范种植、禁止毁绿占绿进行种植", "第3季度"},index = 12)
    private Integer type7;

    @ColumnWidth(value = 10)
    @ExcelProperty(value = {"积分制积分明细表","小菜园规范种植、禁止毁绿占绿进行种植", "第4季度"},index =13)
    private Integer type8;
}

导出工具类

	public static <T> void exportExcel(List<T> list, String sheetName, Class<T> clazz, HttpServletResponse response) {
        try {
            resetResponse(sheetName, response);
            ServletOutputStream os = response.getOutputStream();
            exportExcel(list, sheetName, clazz, false, os);
        } catch (IOException e) {
            throw new RuntimeException("导出Excel异常");
        }
    }
    
	/**
     * 重置响应体
     */
    public static void resetResponse(String sheetName, HttpServletResponse response) throws UnsupportedEncodingException {
        String filename = encodingFilename(sheetName);
        FileUtils.setAttachmentResponseHeader(response, filename);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
    }

	/**
     * 导出excel
     *
     * @param list      导出数据集合
     * @param sheetName 工作表的名称
     * @param clazz     实体类
     * @param merge     是否合并单元格
     * @param os        输出流
     */
    public static <T> void exportExcel(List<T> list, String sheetName, Class<T> clazz, boolean merge, OutputStream os) {
        ExcelWriterSheetBuilder builder = EasyExcel.write(os, clazz)
            .autoCloseStream(false)
            // 自动适配
            .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
            // 大数值自动转换 防止失真
            .registerConverter(new ExcelBigNumberConvert())
            .sheet(sheetName);
        if (merge) {
            // 合并处理器
            builder.registerWriteHandler(new CellMergeStrategy(list, true));
        }
        builder.doWrite(list);
    }

导入

	/**
     * 导入
     */
    @PostMapping("/import")
    public R<Void> importResident(MultipartFile file) throws IOException {
        ExcelResult<BasicIntegralDownloadVo> result = ExcelUtil.importExcel2(file.getInputStream(), BasicIntegralDownloadVo.class, new BasicIntegralListener());
        return R.ok(result.getAnalysis());
    }

	导入监听器
	//todo 无奈,东西有点多，不想写了噗嗤。