优雅的导入导出Excel

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

	
    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 userList = readToBean(excelReader);
     //关闭
     excelReader.close();
     //导出到本地
     exportLocal(userList);
 }
    
 
 public static List readToBean(ExcelReader excelReader){
     System.out.println("----readToBean----");
     List users = excelReader.readAll(User.class);
     users.forEach( user ->{
  System.out.println(user);
     });
     return users;
 }
    
    
 
 public static void exportLocal(List 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 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);
 }
    
    
 
 public static void readToMap(ExcelReader excelReader){
     System.out.println("----readToMap----");
     List> readAll = excelReader.readAll();
     for (Map 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());
  }
     }
 }
    
 
 public static Boolean checkMap(Map 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 readExcel(@RequestParam(value = “file”, required = true) MultipartFile file) throws IOException {

return new baseResponse();
}

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