优雅的导入导出Excel

首先，配置pom依赖，注意poi-ooxml的版本要大于等于3.17,3.8小于3.17不可用

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

然后再创建Excel对应的Bean对象：

/**
 * @ClassName User
 * @Description TODO
 * @Author RickSun && iFillDream
 * @Date 2020/1/9 10:59
 * @Version 1.0
 */
public class User {

    private String name;

    private Integer age;

    private String address;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    //我们重写了toString()方法
    @Override
    public String toString() {
        return "User{" +
                "name='" + name + '\'' +
                ", age=" + age +
                ", address='" + address + '\'' +
                '}';
    }

    //创建构造方法时一定要创建一个空参构造器
    public User(String name, Integer age, String address) {
        this.name = name;
        this.age = age;
        this.address = address;
    }

    //空参构造器
    public User() {
        super();
    }
}

导入导出代码如下所示：

package com.hmkj.ygyr.web.controller.pc;

import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;

import io.swagger.annotations.ApiOperation;
import org.springframework.web.bind.annotation.*;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;

import java.util.*;



@RestController
@RequestMapping("/filldream/excel")
public class PcAccountController{

    public static void main(String[] args)  {
        //导入Excel
        ExcelReader excelReader = ExcelUtil.getReader(new File("D:\\Pictures\\test\\filldream.xls"));
//        ExcelUtil.getReader(InputStream); 也可通过流导入
        //读取为Map列表
        readToMap(excelReader);
        //读取为Bwan列表
        List<User> userList = readToBean(excelReader);
        //关闭
        excelReader.close();
        //导出到本地
        exportLocal(userList);
    }

    /**
     * 读取为Bean列表
     * @param excelReader
     * @return
     */
    public static List<User> readToBean(ExcelReader excelReader){
        System.out.println("----readToBean----");
        List<User> users = excelReader.readAll(User.class);
        users.forEach( user ->{
            System.out.println(user);
        });
        return users;
    }


    /**
     * 导出到本地
     * @param userList
     */
    public static void exportLocal(List<User> userList){
        // 通过工具类创建writer
        ExcelWriter writer = ExcelUtil.getWriter("D:\\Pictures\\test\\exportList.xlsx");
        // 一次性写出内容
        writer.write(userList);
        writer.setSheet("用户列表");
        // 关闭writer，释放内存
        writer.close();
    }



    @GetMapping("/export")
    @ApiOperation(value = "导出Excel")
    public void export(HttpServletResponse response)throws Exception {
        //初始化用户
        List<User> userList = new ArrayList<>(2);
        userList.add(new User("轻梦致新",2,"浙江"));
        userList.add(new User("轻梦致新",2,"杭州"));

        ServletOutputStream out = response.getOutputStream();
        ExcelWriter writer = new ExcelWriter(true,"用户表");
        writer.write(userList, true);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition","attachment;filename=ifilldream.xlsx");
        writer.flush(out, true);
        writer.close();
        IoUtil.close(out);
    }


    /**
     * 读取为Map列表
     * @param excelReader
     */
    public static void readToMap(ExcelReader excelReader){
        System.out.println("----readToMap----");
        List<Map<String, Object>> readAll = excelReader.readAll();
        for (Map<String, Object> map : readAll) {
            if(checkMap(map,"name")){
                System.out.println(map.get("name").toString());
            }
            if(checkMap(map,"age")){
                System.out.println(map.get("age").toString());
            }
            if(checkMap(map,"address")){
                System.out.println(map.get("address").toString());
            }
        }
    }

    /**
     * 检验map是否含有指定字段
     * @param map
     * @param fieldName
     * @return
     */
    public static Boolean checkMap(Map<String, Object> map,String fieldName){
        return map.containsKey(fieldName) && map.get(fieldName) != null;
    }

}

下载提示文件损坏问题解决
有用户反馈按照代码生成的Excel下载后提示文件损坏，无法打开，经过排查，可能是几个问题：

（1）writer和out流没有正确关闭，请在代码末尾的finally块增加关闭。
（2）扩展名不匹配。getWriter默认生成xls，Content-Disposition中也应该是xls，只有getWriter(true)时才可以使用xlsx
（3）Maven项目中Excel保存于ClassPath中（src/main/resources下）宏替换导致被破坏，解决办法是添加filtering（参考：https://blog.csdn.net/qq_42270377/article/details/92771349）
（4）Excel打开提示文件损坏，WPS可以打开。这是Excel的安全性控制导致的，解决办法见：https://blog.csdn.net/zm9898/article/details/99677626
除此之外，你还可以这么导入Excel:

@ApiOperation(value = "写入数据")
@PostMapping(value = "/readExcel")
public BaseResponse<Void> readExcel(@RequestParam(value = "file", required = true) MultipartFile file) throws IOException {
    /**
     * TODO do something
     * 可以使用以下方式导入
     */
    ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
    /**
     * TODO do something
     */
    return new BaseResponse();
}

统一首发平台为微信公众号"轻梦致新"，搜索关注公众号，第一时间阅读最新内容。