使用hutool-excel导入或导出excel

基础依赖

<dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-poi</artifactId>
            <version>5.6.3</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.0.0</version>
        </dependency>
        <!-- 推荐引入poi-ooxml，这个包会自动关联引入poi包，且可以很好的支持Office2007+的文档格式 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.0.0</version>
        </dependency>
        <!-- 如果需要使用Sax方式读取Excel，需要引入以下依赖（POI-4.x以上这个非必须）-->
        <dependency>
            <groupId>xerces</groupId>
            <artifactId>xercesImpl</artifactId>
            <version>2.12.0</version>
        </dependency>
           

1. 输出自定义excel模板

@RequestMapping("getTemplate")
public void getTemplate(HttpServletResponse response) throws UnsupportedEncodingException {
    // 设置模板第一行信息
    List<String> row = CollUtil.newArrayList("姓名", "年龄", "性别", "地址");
    List<List<String>> rows = CollUtil.newArrayList(Collections.singleton(row));
    ExcelWriter writer = ExcelUtil.getWriter();
    // 设置excel文件的文件名, 文件名需要设置code为utf-8 否则乱码
    response.setContentType("application/vnd.ms-excel;charset=utf-8");
    String fileName = URLEncoder.encode("上传模板", "UTF-8");
    response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
    // 写出
    writer.write(rows, true);
    ServletOutputStream out = null;
    try {
        out = response.getOutputStream();
        writer.flush(out, true);
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        writer.close();
    }
    IoUtil.close(out);
}
           

实现效果：

2. 输出数据库中的数据到excel中

实体bean

@Data
@AllArgsConstructor
@NoArgsConstructor
public class ExcelBean implements Serializable {
    private String name;
    private Integer age;
    private String sex;
    private String address;
}
           

逻辑代码

List<Person> personList = new ArrayList<>();
		// 模拟数据库查询操作
        for (int i = 0; i < 10; i++) {
            personList.add(new Person("小"+i,i,"男","地址"+i));
        }
        ExcelWriter writer = ExcelUtil.getWriter();
        // 输出别名
        writer.addHeaderAlias("name", "姓名");
        writer.addHeaderAlias("age", "年龄");
        writer.addHeaderAlias("sex", "性别");
        writer.addHeaderAlias("address", "地址");
        writer.write(personList, true);
        // 设置excel文件的文件名, 文件名需要设置code为utf-8 否则乱码
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        String fileName = URLEncoder.encode("下载文件", "UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
        ServletOutputStream out = null;
        try {
            out = response.getOutputStream();
            writer.flush(out, true);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            writer.close();
        }
        IoUtil.close(out);
           

生成文件效果

3. 读取excel中的数据

逻辑代码

public static void main(String[] args) {
        // 获取文件
        ExcelReader reader = ExcelUtil.getReader("C:\\Users\\hj\\Downloads\\下载文件.xls");
        // 校验表头是否符合输入要求
        List<List<Object>> first = reader.read(0, 0);
        AtomicReference<String> checkString = new AtomicReference<>("");
        first.get(0).forEach(data ->{
            checkString.updateAndGet(v -> v + data.toString());
        });
        System.out.println(checkString);
        // 从第2行开始读取到最后一行
        List<List<Object>> list = reader.read(1, reader.getRowCount());
        List<Person> personList = new ArrayList<>();
        list.forEach(data -> {
            // 逐行读取excel的内容
            Person person = new Person();
            person.setName( data.get(0).toString());
            person.setAge(Integer.parseInt(data.get(1).toString()));
            person.setSex(data.get(2).toString());
            person.setAddress(data.get(3).toString());
            personList.add(person);
        });
        personList.forEach(System.out::println);
    }
           

实现效果