Hutool Excel工具类导入导出

最新推荐文章于 2024-04-29 16:22:54 发布

·暮雨·

最新推荐文章于 2024-04-29 16:22:54 发布

阅读量3.4k

点赞数 3

分类专栏： Java Hutool工具类文章标签： java

原文链接：https://blog.csdn.net/beidaol/article/details/104767380?spm=1001.2101.3001.6650.2&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7Edefault-2.nonecase&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7E

版权

Java 同时被 2 个专栏收录

55 篇文章 2 订阅

订阅专栏

Hutool工具类

4 篇文章 0 订阅

订阅专栏

导出

这里的数据直接写死了
代码
user

@Data
public class User {
    private String name;
    private String age;
    private Date birthDay;

    public User(String name, String age, Date date) {
        this.name=name;
        this.age=age;
        this.birthDay=date;
    }
}

Controller

@RestController
@RequestMapping("/excell/")
public class ExcellController {

    @GetMapping("export")
    public void export(HttpServletResponse response) {
        List<User> list = new ArrayList<>();
        list.add(new User("zhangsan", "1231", new Date()));
        list.add(new User("zhangsan1", "1232", new Date()));
        list.add(new User("zhangsan2", "1233", new Date()));
        list.add(new User("zhangsan3", "1234", new Date()));
        list.add(new User("zhangsan4", "1235", new Date()));
        list.add(new User("zhangsan5", "1236", DateUtil.date(new Date())));
        // 通过工具类创建writer，默认创建xls格式
        ExcelWriter writer = ExcelUtil.getWriter();
        //自定义标题别名
        writer.addHeaderAlias("name", "姓名");
        writer.addHeaderAlias("age", "年龄");
        writer.addHeaderAlias("birthDay", "生日");
        // 合并单元格后的标题行，使用默认标题样式
        writer.merge(2, "申请人员信息");
        // 一次性写出内容，使用默认样式，强制输出标题
        writer.write(list, true);
        //out为OutputStream，需要写出到的目标流
        //response为HttpServletResponse对象
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
//        String name = StringUtils.toUtf8String("申请学院");
        String name = "666";
        response.setHeader("Content-Disposition", "attachment;filename=" + name + ".xls");
        ServletOutputStream out = null;
        try {
            out = response.getOutputStream();
            writer.flush(out, true);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            // 关闭writer，释放内存
            writer.close();
        }
        //此处记得关闭输出Servlet流
        IoUtil.close(out);
    }
}

url

localhost:8090/excell/export

测试
在这里插入图片描述

导入

在读取excell完成之后并将数据插入数据库中
项目截图
在这里插入图片描述

相应pom

<!--        hutool-->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.0.7</version>
        </dependency>
<!--        poi-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

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

        <!-- mybatis整合Springboot -->
       <dependency>
           <groupId>org.mybatis.spring.boot</groupId>
           <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.1.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.35</version>
        </dependency>

application.properties

server.port=8090

spring.datasource.driverClassName=com.mysql.jdbc.Driver
#  com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=root

mybatis.configuration.map-underscore-to-camel-case=true
#mybatis.type-aliases-package=com.example.springboot.model
mybatis.type-aliases-package=com.axpose.controller.bean


logging.level.com.example.springbootexcel.mapper=debug
#mappe.xml 路径
mybatis.mapper-locations=classpath*:mapper/*.xml

实体

@Data
public class Excel {
    private Long id;

    private String name;

    private Integer age;

    private Double height;

    private Double weight;

    private String edu;

    private Date createTime;

    private Date updateTime;

    private Integer status;
}

service

@Service
public class ExcelService {


    @Autowired
    private ExcelMapper excelMapper;

    public int addList(List<Excel> list){
        return excelMapper.insertList(list);
    }
}

mapper

@Mapper
@Component
public interface ExcelMapper {
    /**
     * 批量插入
     * @param recordList
     * @return
     */
    int insertList(List<Excel> recordList);
}

mapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.axpose.mapper.ExcelMapper">

    <insert id="insertList" parameterType="com.axpose.controller.bean.Excel">
        insert into excel (
        name,
        age,
        height,
        weight,
        edu,
        update_time,
        status
        )
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (
            #{item.name,jdbcType=VARCHAR},
            #{item.age,jdbcType=INTEGER},
            #{item.height,jdbcType=DOUBLE},
            #{item.weight,jdbcType=DOUBLE},
            #{item.edu,jdbcType=VARCHAR},
            NOW(),
            #{item.status,jdbcType=INTEGER}
            )
        </foreach>
    </insert>
</mapper>

controller

@RestController
@RequestMapping("/import")
public class ExcelController2 {
  @Autowired private ExcelService service;

  @GetMapping("/toHtml")
  public String test(HttpServletRequest request) {
    return "excelImport";
  }

  // 处理文件上传
  @PostMapping("/excelImport")
  public String uploadImg(@RequestParam("file") MultipartFile file, HttpServletRequest request) {

    System.out.println(file);

    if (file.isEmpty()) {
      System.out.println("文件为空！");
      return "excelImport";
    }

    // 1.获取上传文件输入流
    InputStream inputStream = null;
    try {
      inputStream = file.getInputStream();
    } catch (Exception e) {
      // return ResponseData.fail(ResponseCodeEnum.ERROR_PARAM_INVALID);
      e.printStackTrace();
    }
    //        // 2.应用HUtool ExcelUtil获取ExcelReader指定输入流和sheet
    //        ExcelReader excelReader = ExcelUtil.getReader(inputStream, "导入材料清单");
    //        // 可以加上表头验证
    //        // 3.读取第二行到最后一行数据
    //        List<List<Object>> read = excelReader.read(2, excelReader.getRowCount());
    //        for (List<Object> objects : read) {
    //            // objects.get(0),读取某行第一列数据
    //            // objects.get(1),读取某行第二列数据
    //            System.out.println(objects.get(0));
    //        }

    // 调用用 hutool 方法读取数据 默认调用第一个sheet
    ExcelReader excelReader = ExcelUtil.getReader(inputStream);
    // 从第二行开始获取数据   excelReader.read的结果是一个2纬的list，外层是行，内层是行对应的所有列
    // 读取方式1
//    List<List<Object>> read = excelReader.read(1, excelReader.getRowCount());
//    List<Excel> excels = new ArrayList<>();
//    // 循环获取的数据
//    for (int i = 0; i < read.size(); i++) {
//      List list = read.get(i);
//      Excel excel = new Excel();
//      //按照列获取
//      excel.setName(list.get(0).toString());
//      excel.setAge(Integer.parseInt(list.get(1).toString()));
//      excel.setHeight(Double.parseDouble(list.get(2).toString()));
//      excel.setWeight(Double.parseDouble(list.get(3).toString()));
//      excel.setEdu(list.get(4).toString());
//      excel.setStatus(Integer.parseInt(list.get(5).toString()));
//      excels.add(excel);
//    }
//    excels.forEach(
//        index -> {
//          System.out.println(index);
//        });
    // 读取方式2
    // 读取为Bean列表，Bean中的字段名为标题，字段值为标题对应的单元格值。
            List<Excel> excels = excelReader.readAll(Excel.class);
            excels.forEach(index ->{
                System.out.println(index.getName());
            });
            service.addList(excels);
    System.out.println("导入成功");
    return "excelImport";
  }
}

测试
url

localhost:8090/import/excelImport

在这里插入图片描述

将博主文章拿来测试一下，顺便转一下。

https://blog.csdn.net/beidaol/article/details/104767380?spm=1001.2101.3001.6650.2&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7Edefault-2.nonecase&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7Edefault-2.nonecase

另一篇文章也很 nice 采用的是readAll 这种形式的

https://blog.csdn.net/qq_36403831/article/details/108715160?utm_medium=distribute.pc_relevant.none-task-blog-2_defaultbaidujs_title~default-0.opensearchhbase&spm=1001.2101.3001.4242.1

·暮雨·

关注

3
点赞
踩
13

收藏

觉得还不错? 一键收藏
9
评论
Hutool Excel工具类导入导出

导出这里的数据直接写死了代码user@Datapublic class User { private String name; private String age; private Date birthDay; public User(String name, String age, Date date) { this.name=name; this.age=age; this.birthDay=date;
复制链接

扫一扫