SpringBoot 数据库批量导入导出 Xlsx文件的导入与导出 全量导出 数据库导出表格 数据处理 外部数据

介绍

poi-ooxml 是 Apache POI 项目中的一个库，专门用于处理 Microsoft Office 2007 及以后版本的文件，特别是 Excel 文件（.xlsx 格式）和 Word 文件（.docx 格式）。

在管理系统中需要对数据库的数据进行导入或导出在系统中经常使用的到。

Java针对MS Office的操作的库屈指可数，比较有名的就是Apache的POI库。这个库异常强大，但是使用起来也并不容易。Hutool针对POI封装一些常用工具，使Java操作Excel等文件变得异常简单。

胡图官网：https://doc.hutool.cn/pages/poi/#%E7%94%B1%E6%9D%A5

Hutool-poi是针对Apache POI的封装，因此需要用户自行引入POI库,Hutool默认不引入。

• XLS：是 Excel 97-2003 版本的文件格式，基于二进制文件格式。存储数据和工作表信息采用了二进制格式，不易直接读取和处理。

• XLSX：是 Excel 2007 及更高版本使用的文件格式，基于 XML。XLSX 实际上是一个压缩的文件包，里面包含了多个 XML 文件，更加标准化和易于扩展。

依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.7.18</version>
</dependency>

实体类

支持别名注解的。可以在字段上加@Alias注解。

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("site")
public class Site implements Serializable {

    private static final long serialVersionUID = 1L;

    @TableId(value = "id", type = IdType.AUTO)
    @Alias(value = "编号")
    private Integer id;

    @TableField("ip_address")
    @Alias(value = "IP地址")
    private String ipAddress;

    @TableField("source")
    @Alias(value = "来源")
    private String source;

    @TableField("operation")
    private String operation;

    @TableField("access_time")
    private LocalDateTime accessTime;
    
}

导出Xls

 @GetMapping()
    public void export(HttpServletResponse response) throws Exception{

        String fileName = "测试.xls";
        String encodedFileName = URLEncoder.encode(fileName, "UTF-8");
        encodedFileName = encodedFileName.replace("+", "%20");  // 处理空格字符

        List<Site> list = siteService.list();
// 通过工具类创建writer，默认创建xls格式
        ExcelWriter writer = ExcelUtil.getWriter();

// 一次性写出内容，使用默认样式，强制输出标题
        writer.write(list, true);

        //response为HttpServletResponse对象
        response.setContentType("application/vnd.ms-excel;charset=utf-8");

        //test.xls是弹出下载对话框的文件名
        response.setHeader("Content-Disposition","attachment;filename="+encodedFileName);

        ServletOutputStream out=response.getOutputStream();

        writer.flush(out, true);
    // 关闭writer，释放内存
        writer.close();
//此处记得关闭输出Servlet流
        IoUtil.close(out);

    }

导出Xlsx

@GetMapping()
public void export(HttpServletResponse response) throws Exception{

    String  fileName = URLEncoder.encode("测试.xlsx", "UTF-8").replace("+", "%20");

    List<Site> list = siteService.list();

    ExcelWriter writer = ExcelUtil.getWriter(true);
    writer.write(list, true);

    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
    response.setHeader("Content-Disposition","attachment;filename="+fileName);
    ServletOutputStream out=response.getOutputStream();

    writer.flush(out, true);
    writer.close();
    IoUtil.close(out);
    
}

以上都是基于全量数据的导出方法，下面是按需导出。

创建自定义注解

@Retention(RetentionPolicy.RUNTIME) // 保证注解在运行时可访问
public  @interface ExcelTitle {
    String value(); // 用来保存 Excel 列标题
}

自定义实体类

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("site")
public class Site implements Serializable {

    private static final long serialVersionUID = 1L;

    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    @TableField("ip_address")
    @ExcelTitle("IP地址")
    private String ipAddress;

    @TableField("source")
    @ExcelTitle("来源")
    private String source;

    @TableField("operation")
    @ExcelTitle("测试字段")
    private String operation;

    @TableField("access_time")
    @ExcelTitle("访问时间")
    private LocalDateTime accessTime;

}

按需不排序

@GetMapping() 
public void export(HttpServletResponse response) throws Exception{

    // 需要导出的字段名
    String[] key = {"访问时间", "IP地址", "来源"};

    // 查找性能较高
    Set<String> keySet = new HashSet<>(Arrays.asList(key));

    // 对文件名进行 URL 编码，防止中文字符出现乱码，并将 "+" 替换成 "%20"
    String fileName = URLEncoder.encode("测试.xlsx", "UTF-8").replace("+", "%20");

    // 从服务层获取数据列表，通常是从数据库或其他数据源获取
    List<Site> list = siteService.list();

    // 创建一个 ExcelWriter 实例，用于写入 Excel 文件，true 表示创建时有表头
    ExcelWriter writer = ExcelUtil.getWriter(true);

    // 获取 Site 类的所有字段（反射机制）
    Field[] fields = Site.class.getDeclaredFields();

    // 遍历每个字段
    for (Field field : fields) {

        // 获取字段上是否有 @ExcelTitle 注解
        ExcelTitle excelAnnotation = field.getAnnotation(ExcelTitle.class);

        // 如果字段上存在 @ExcelTitle 注解
        if (excelAnnotation != null) {

            // 获取注解中的 value 值（即字段对应的 Excel 标题）
            String excelValue = excelAnnotation.value();

            // 判断当前字段的 Excel 标题是否是我们关心的字段
            if (Arrays.asList(key).contains(excelValue)) {

                // 如果是我们关心的字段，则添加标题别名（即字段名与 Excel 表头的映射关系）
                writer.addHeaderAlias(field.getName(), excelValue);
            }
        }
    }

    // 默认情况下，未添加 alias 的属性也会被写出。如果我们只想输出加了别名的字段，可以调用该方法
    writer.setOnlyAlias(true);

    // 将数据写入 Excel 文件，true 表示需要写入表头
    writer.write(list, true);

    // 设置响应内容类型为 Excel 文件格式
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");

    // 设置响应头，指定下载的文件名
    response.setHeader("Content-Disposition", "attachment;filename=" + fileName);

    // 获取响应输出流，用于将文件写入客户端
    ServletOutputStream out = response.getOutputStream();

    // 将 Excel 内容刷新到输出流
    writer.flush(out, true);

    // 关闭 writer 释放资源
    writer.close();

    // 关闭输出流
    IoUtil.close(out);
}

按需排序导出

    @GetMapping()
    public void export(HttpServletResponse response) throws Exception {

        String[] key = {"访问时间", "IP地址", "来源"};
        Set<String> keySet = new HashSet<>(Arrays.asList(key));


        String fileName = URLEncoder.encode("测试.xlsx", "UTF-8").replace("+", "%20");

        List<Site> list = siteService.list();

        ExcelWriter writer = ExcelUtil.getWriter(true);

        for (String k : key) {
          String title =  getCharacter(Site.class, k);
          if(title==null)continue;
            writer.addHeaderAlias(title, k);
        }


//        //自定义标题别名
//        writer.addHeaderAlias("ipAddress", "IP地址");

        // 默认的，未添加alias的属性也会写出，如果想只写出加了别名的字段，可以调用此方法排除之
        writer.setOnlyAlias(true);

        writer.write(list, true);


        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        ServletOutputStream out = response.getOutputStream();

        writer.flush(out, true);
        writer.close();
        IoUtil.close(out);

    }

/**
 * 根据传入的类和关键字，查找类中具有 @ExcelTitle 注解的字段，
 * 如果该字段的 @ExcelTitle 注解的 value 属性与传入的关键字匹配，则返回该字段的名称。
 *
 * @param clazz 目标类的 Class 对象，用于获取类的字段信息。
 * @param key   需要匹配的关键字，用于查找与其对应的 @ExcelTitle 注解的字段。
 * @return 如果找到匹配的字段，则返回字段名称；否则返回 null。
 */
public String getCharacter(Class<Site> clazz, String key) {
    // 获取目标类的所有字段
    Field[] fields = clazz.getDeclaredFields();
    
    // 遍历所有字段
    for (Field field : fields) {
        // 获取当前字段的 @ExcelTitle 注解
        ExcelTitle excelAnnotation = field.getAnnotation(ExcelTitle.class);

        // 如果字段具有 @ExcelTitle 注解
        if (excelAnnotation != null) {
            
            // 获取注解的 value 值
            String excelValue = excelAnnotation.value();

            // 检查传入的 key 是否与 @ExcelTitle 注解中的值匹配
            if (Arrays.asList(key).contains(excelValue)) {
                
                // 如果匹配，返回字段名称
                return field.getName();
            }
        }
    }
    
    // 如果没有找到匹配的字段，返回 null
    return null;
}

也可以将该功能给前端去做。

批量导入

@PostMapping("/import")
public String importData(MultipartFile file) throws IOException {

    //读取文件转成输入流
    ExcelReader reader = ExcelUtil.getReader(file.getInputStream());

    List<Object> header = reader.readRow(0); // 读取第0行作为列标题
    for (Object column : header) {
        reader.addHeaderAlias(column+"",getCharacter(Site.class,column+""));
        }

    List<Site> list = reader.readAll(Site.class);
    siteService.saveBatch(list);
    
    return null;
}

模板下载

@GetMapping("/template")
public void template(HttpServletResponse response) throws Exception {
    // 定义模板标题
    String[] key = {"访问时间", "IP地址", "来源"};

    // 文件名编码
    String fileName = URLEncoder.encode("测试.xlsx", "UTF-8").replace("+", "%20");

    // 创建一个空的 List，作为数据源，模板将只包含标题行
    List<Map<String, Object>> list = new ArrayList<>();

    // 生成空数据的行
    Map<String, Object> emptyRow = new HashMap<>();
    for (String column : key) {
        emptyRow.put(column, "");  // 为空字段赋空值
    }
    list.add(emptyRow); // 将空数据行添加到 list

    // 创建 ExcelWriter
    ExcelWriter writer = ExcelUtil.getWriter(true);

    // 为每一列设置别名
    for (String k : key) {
        writer.addHeaderAlias(k, k);  // 直接使用标题作为别名
    }

    // 写入空数据（标题和一行空数据）
    writer.write(list, true);

    // 设置响应的内容类型和头部，触发浏览器下载
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
    response.setHeader("Content-Disposition", "attachment;filename=" + fileName);

    // 获取输出流，写入到客户端
    ServletOutputStream out = response.getOutputStream();

    // 将数据写入输出流
    writer.flush(out, true);
    writer.close();
    IoUtil.close(out);
}

测试demo，实际业务自行优化