SpringBoot整合easyexcel实现Excl导入导出

迈不过友情╰ 2024-04-07 13:39 108阅读 0赞

> EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel

[easyexcel官网][easyexcel]

*  **导入依赖**

<dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>3.0.5</version>
            </dependency>

*  **编写Excl数据实体类**

import com.alibaba.excel.annotation.ExcelProperty;
    import com.alibaba.excel.annotation.format.DateTimeFormat;
    import com.fasterxml.jackson.annotation.JsonFormat;
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    import lombok.experimental.Accessors;
    
    import java.util.Date;
    
    /**
     * @Description: 登记信息
     */
    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    public class RegistrationInformation {
        
    
        private String ID;
        @ExcelProperty(value ="客户") // @ExcelProperty 注解的作用在于 映射excl的表头和实体类的字段关系
        private String ownerName;
        @ExcelProperty(value ="电话")
        private String contactNumber;
        @ExcelProperty(value ="身份证")
        private String idNumber;
        @ExcelProperty(value ="地址")
        private String address;
        @ExcelProperty(value ="品牌")
        private String brandAndModel;
        @ExcelProperty(value ="车牌")
        private String licensePlateNumber;
        @ExcelProperty(value ="车架号")
        private String frameNo;
        @ExcelProperty(value ="发动机")
        private String engineNumber;
        @JsonFormat(pattern = "yyyy-MM-dd HH:MM:ss", timezone = "GMT+8")
        @ExcelProperty(value ="登记时间")
        @DateTimeFormat("yyyy-MM-dd HH:mm:ss") //格式化Excl 的时间格式
        private Date createTime;
        @JsonFormat(pattern = "yyyy-MM-dd HH:MM:ss", timezone = "GMT+8")
        @ExcelProperty(value ="上次修改时间")
        @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
        private Date updateTime;
    
    }

注：  
`@ExcelProperty`: 用于匹配excel和实体类的匹配  
参数如下：

<table> 
 <thead> 
  <tr> 
   <th>名称</th> 
   <th>默认值</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>value</td> 
   <td>空</td> 
   <td>用于匹配excel中的头，必须全匹配,如果有多行头，会匹配最后一行头</td> 
  </tr> 
  <tr> 
   <td>order</td> 
   <td>Integer.MAX_VALUE</td> 
   <td>优先级高于value，会根据order的顺序来匹配实体和excel中数据的顺序</td> 
  </tr> 
  <tr> 
   <td>index</td> 
   <td>-1</td> 
   <td>优先级高于value和order，会根据index直接指定到excel中具体的哪一列</td> 
  </tr> 
  <tr> 
   <td>converter</td> 
   <td>自动选择</td> 
   <td>指定当前字段用什么转换器，默认会自动选择。读的情况下只要实现com.alibaba.excel.converters.Converter#convertToJavaData(com.alibaba.excel.converters.ReadConverterContext&lt;?&gt;) 方法即可</td> 
  </tr> 
 </tbody> 
</table>

`@DateTimeFormat`: 日期转换，用String去接收excel日期格式的数据会调用这个注解  
参数如下：

<table> 
 <thead> 
  <tr> 
   <th>名称</th> 
   <th>默认值</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>value</td> 
   <td>空</td> 
   <td>参照java.text.SimpleDateFormat书写即可</td> 
  </tr> 
  <tr> 
   <td>use1904windowing</td> 
   <td>自动选择</td> 
   <td>excel中时间是存储1900年起的一个双精度浮点数，但是有时候默认开始日期是1904，所以设置这个值改成默认1904年开始</td> 
  </tr> 
 </tbody> 
</table>

[其他注解请参见官网][easyexcel]

*  **导入，导出**

import com.alibaba.excel.EasyExcel;
    import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
    import com.example.vehicleinformationsystem.bean.RegistrationInformation;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.*;
    import org.springframework.web.multipart.MultipartFile;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.io.InputStream;
    import java.net.URLEncoder;
    import java.util.List;
    
    /**
     * @Description:
     */
    @RestController
    @RequestMapping("/test")
    public class test {
        
        /**
         * 操作数据库，mapper
         */
        @Autowired
        TestMapper mapper;
        /**
         * 导出excel
         * @param response
         * @return
         */
        @GetMapping("/download")
        public String  download(HttpServletResponse response){
        
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            try {
        
    
                //数据库将结果查询出来
                List<RegistrationInformation> infoList = mapper.selectList(null);
    
                response.setContentType("application/vnd.ms-excel");
                response.setCharacterEncoding("utf-8");
    			//设置导出的文件名称及编码格式
                String fileName = URLEncoder.encode("车辆信息", "UTF-8");
                response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
    
                EasyExcel.write(response.getOutputStream(), RegistrationInformation.class)
                        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())//自适应表格格式
                        .sheet("模板")
                        .doWrite(infoList);
            } catch (IOException e) {
        
                e.printStackTrace();
                return  "程序异常"+e.getMessage();
            }
            return null;
        }
    
        /**
         * 导入excel
         * @param file
         * @return
         */
        @PostMapping("/upload")
        public String upload(@RequestParam("file") MultipartFile file)  {
        
            InputStream inputStream = null;
            try {
        
                inputStream = file.getInputStream();
                List<RegistrationInformation> dataList = EasyExcel.read(inputStream).head(RegistrationInformation.class).sheet().doReadSync();
                inputStream.close();
    
                for (RegistrationInformation info:dataList ) {
        
                    //将结果插入到数据库
                   mapper.insert(info);
                }
            } catch (IOException e) {
        
                e.printStackTrace();
    
            }
            return"上传成功";
        }
    
    }

*  **EXCL格式**  
    ![在这里插入图片描述][0c56ee1657254429ba56fe0932afc0fa.png]
 *  **前端接收返回的二进制流excel表格文件**  
    [参考博客][Link 1]

[easyexcel]: https://easyexcel.opensource.alibaba.com/docs/current/api/
[0c56ee1657254429ba56fe0932afc0fa.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/f42b39abff0b4f5181082617b0b917bd.png
[Link 1]: https://blog.csdn.net/baidu_41651062/article/details/119190552?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522166244940916782388051389%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=166244940916782388051389&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~first_rank_ecpm_v1~pc_rank_v39-2-119190552-null-null.142%5Ev46%5Epc_rank_34_2&utm_term=%E5%89%8D%E7%AB%AF%E6%8E%A5%E6%94%B6%E5%90%8E%E7%AB%AF%E8%BF%94%E5%9B%9E%E7%9A%84excl%E4%B8%8B%E8%BD%BD&spm=1018.2226.3001.4187