当前位置: 首页 > article >正文

easy-poi导出and导入一对多数据excel

easy-poi导出and导入一对多数据excel

一、导入jar包

        <!-- easy-poi -->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.4.0</version>
        </dependency>

二、创建excel对象

father-obj

package com.example.excel.easypoi.entity.my;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import lombok.Data;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

@Data
public class Father implements Serializable {

    @Excel(name = "编号", needMerge = true)
    private String id;

    @Excel(name = "姓名", needMerge = true)
    private String name;

    @Excel(name = "头像",type = 2,imageType = 2,width = 20,height = 15, needMerge = true)
    private byte[] logo;

    @Excel(name="年龄", orderNum="3", suffix="岁",needMerge = true)
    private Integer age;

    @Excel(name="生日", width=20.0, format="yyyy-MM-dd HH:mm:ss", orderNum="2",needMerge = true)
    private Date bir;

    @Excel(name = "状态", width = 25, replace = {"待审_0", "通过_1"}, addressList = true, needMerge = true)
    private String status;

    @ExcelCollection(name = "子列表")
    private List<Son> sonList;
}

son-obj

package com.example.excel.easypoi.entity.my;

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;

import java.io.Serializable;
@Data
public class Son  implements Serializable {

    @Excel(name="子编号")
    private String id;

    @Excel(name="子姓名")
    private String name;

}

三、工具类

package com.example.excel.easypoi.util;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import com.alibaba.fastjson2.JSON;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author: reshui
 * description:
 * DateTime:2025/2/20-11:17
 */
@Slf4j
public class EasyPoiExcelUtil {
    /**
     * 通用的 Excel 导入方法
     * @param file 上传的 Excel 文件
     * @param clazz 要导入的数据类型的 Class 对象
     * @param titleRows 标题所占的行数
     * @param headRows 表头所占的行数
     * @param <T> 泛型类型,表示要导入的数据类型
     * @return 包含导入数据的列表
     * @throws Exception 当读取文件输入流出现异常时抛出
     */
    public static <T> List<T> importExcel(MultipartFile file, Class<T> clazz, int titleRows, int headRows) throws Exception {
        // 导入配置参数
        ImportParams params = new ImportParams();
        // 标题占几行
        params.setTitleRows(titleRows);
        // 表头占几行
        params.setHeadRows(headRows);
        // 参数1:输入流  参数2:导入的数据类型  参数3:导入配置参数
        return ExcelImportUtil.importExcel(file.getInputStream(), clazz, params);
    }
    public static void download(HttpServletResponse response, ExportParams exportParams,Class<?> clazz, List<?> data,String fileName) throws IOException {
        try {
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams, clazz, data);
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            workbook.write(response.getOutputStream());
            workbook.close();
        } catch (Exception e) {
            log.error("导出Excel异常:", e);
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap<String, String>();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
        }
    }

}

四、controller-api接口层

package com.example.excel.easypoi.controller;

import cn.afterturn.easypoi.excel.entity.ExportParams;
import com.example.excel.easypoi.entity.my.Father;
import com.example.excel.easypoi.entity.my.Son;
import com.example.excel.easypoi.util.EasyPoiExcelUtil;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Random;

/**
 * @author: ly
 * description:
 * DateTime:2025/2/20-10:25
 */
@RestController
@RequestMapping("/easyCommon")
public class EasyPoiCommonController {


    @PostMapping("import")
    public List<Father> importData(MultipartFile file) throws Exception {
        List<Father> dataList = EasyPoiExcelUtil.importExcel(file, Father.class, 0, 2);
        dataList.forEach(System.out::println);
        return dataList;
    }

    @GetMapping("export")
    public void exportData(HttpServletResponse response, Integer x) throws Exception {
        List<Father> fatherList = getFatherList(x);
        EasyPoiExcelUtil.download(response, new ExportParams(), Father.class, fatherList, "用户信息列表");
    }

    public List<Father> getFatherList(Integer x) {
        List<Father> userList = new ArrayList<>();
        for (int i = 1; i <= x; i++) {
            Father user = new Father();
            user.setId("编号" + i);
            user.setName("姓名-" + i);
            user.setStatus(i % 2 == 0 ? "1" : "0");
            user.setBir(new Date());
            user.setAge(i);
            user.setLogo(null);

            Random rand = new Random();
            int num = rand.nextInt(5) + 1;
            List<Son> orderList = new ArrayList<>(num);
            for (int j = 1; j <= num; j++) {
                Son order = new Son();
                order.setId("订单号" + j);
                order.setName("商品" + j);
                orderList.add(order);
            }
            user.setSonList(orderList);
            userList.add(user);
        }
        return userList;
    }
}

原文地址:https://blog.csdn.net/weixin_42477252/article/details/146208149
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.kler.cn/a/586453.html

相关文章:

  • 动手学深度学习11.8. RMSProp算法-笔记练习(PyTorch)
  • 【YOLOv8】YOLOv8改进系列(5)----替换主干网络之EfficientFormerV2
  • 麒麟系统利用pycharm生成deb文件
  • 机器视觉条形光源应用解析
  • UDP__网络编程——Linux
  • SQLark 实战 | 如何从Excel、csv、txt等外部文件进行数据导入
  • 《Python实战进阶》No23: 使用 Selenium 自动化浏览器操作
  • Go string 字符串底层逻辑
  • 用Python玩转Hyperledger:构建企业级区块链解决方案
  • HTML5-基础知识
  • Android 底部 Tab 导航终极指南:封装 BottomTabHelper 实现高效、灵活的 Tab 布局
  • kafka rocketmq rabbitmq 都是怎么实现顺序消费的
  • C#控制台应用程序学习——3.11
  • 如何选择适合您智能家居解决方案的通信协议?
  • 《GitHub网路访问不稳定:解决办法》:此文为AI自动生成
  • SQL语言的编译原理
  • leetcode top100矩阵题73.54.48.240
  • 【react】react中的<></>和React Fragment的用法及区别详解
  • 数据结构与算法——数据结构4
  • apollo3录音到wav播放解决方法