devbox
小蓝xlanll
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

Java使用EasyExcel实现批量导入_excel批量导入java easyexcel

作者：小蓝xlanll | 2024-04-27 09:37:04

赞

踩

excel批量导入java easyexcel

EasyExcel是阿里下的开源产品，基于poi进行的开发，相比于其他的解析方式不要太6。接下来就是使用的方式

第一步：引入jar包

  1. <!-- excel工具框架 -->
  2. <dependency>
  3.     <groupId>org.apache.poi</groupId>
  4.     <artifactId>poi-ooxml</artifactId>
  5.     <version>3.15</version>
  6. </dependency>
  7.  
  8. <!--easyexcel解析excell-->
  9. <dependency>
  10.     <groupId>com.alibaba</groupId>
  11.     <artifactId>easyexcel</artifactId>
  12.     <version>1.0.3</version>
  13. </dependency>

第二步：读取工具类封装

  1. package com.edu.common.utils;
  2.  
  3. import java.io.IOException;
  4. import java.io.InputStream;
  5. import java.io.PushbackInputStream;
  6. import java.util.ArrayList;
  7. import java.util.List;
  8.  
  9. import com.alibaba.excel.metadata.BaseRowModel;
  10. import com.alibaba.excel.metadata.Sheet;
  11. import org.apache.poi.EmptyFileException;
  12. import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
  13. import org.apache.poi.poifs.filesystem.DocumentFactoryHelper;
  14. import org.apache.poi.poifs.filesystem.NPOIFSFileSystem;
  15. import org.apache.poi.util.IOUtils;
  16.  
  17. import com.alibaba.excel.ExcelReader;
  18. import com.alibaba.excel.read.context.AnalysisContext;
  19. import com.alibaba.excel.read.event.AnalysisEventListener;
  20. import com.alibaba.excel.support.ExcelTypeEnum;
  21.  
  22. /**
  23.  *@ClassName EasyExcelUtil
  24.  *@Description easyExcel操作excel工具类
  25.  *@Auther William
  26.  *@Date 2019/6/5 11:35
  27.  *@Version 1.0
  28.  */
  29. public class EasyExcelUtil {
  30.     /**
  31.      * @param in 文件输入流
  32.      * @param customContent
  33.      *           自定义模型可以在
  34.      *           AnalysisContext中获取用于监听者回调使用
  35.      * @param eventListener 用户监听
  36.      * @throws IOException
  37.      * @throws EmptyFileException
  38.      * @throws InvalidFormatException
  39.      */
  40.     public static ExcelReader getExcelReader(InputStream in, Object customContent,AnalysisEventListener<?> eventListener) throws EmptyFileException, IOException, InvalidFormatException {
  41.         // 如果输入流不支持mark/reset，需要对其进行包裹
  42.         if (!in.markSupported()) {
  43.             in = new PushbackInputStream(in, 8);
  44.         }
  45.         // 确保至少有一些数据
  46.         byte[] header8 = IOUtils.peekFirst8Bytes(in);
  47.         ExcelTypeEnum excelTypeEnum = null;
  48.         if (NPOIFSFileSystem.hasPOIFSHeader(header8)) {
  49.             excelTypeEnum = ExcelTypeEnum.XLS;
  50.         }
  51.         if (DocumentFactoryHelper.hasOOXMLHeader(in)) {
  52.             excelTypeEnum = ExcelTypeEnum.XLSX;
  53.         }
  54.         if (excelTypeEnum != null) {
  55.             return new ExcelReader(in, excelTypeEnum, customContent, eventListener);
  56.         }
  57.         throw new InvalidFormatException("Your InputStream was neither an OLE2 stream, nor an OOXML stream");
  58.  
  59.     }
  60.  
  61.     /**
  62.      * @param in 文件输入流
  63.      * @param customContent
  64.      *           自定义模型可以在
  65.      *           AnalysisContext中获取用于监听者回调使用
  66.      * @param eventListener 用户监听
  67.      * @param trim  是否对解析的String做trim()默认true,用于防止 excel中空格引起的装换报错。
  68.      * @throws IOException
  69.      * @throws EmptyFileException
  70.      * @throws InvalidFormatException
  71.      */
  72.     public static ExcelReader getExcelReader(InputStream in, Object customContent,
  73.                                              AnalysisEventListener<?> eventListener, boolean trim)
  74.             throws EmptyFileException, IOException, InvalidFormatException {
  75.         // 如果输入流不支持mark/reset，需要对其进行包裹
  76.         if (!in.markSupported()) {
  77.             in = new PushbackInputStream(in, 8);
  78.         }
  79.  
  80.         // 确保至少有一些数据
  81.         byte[] header8 = IOUtils.peekFirst8Bytes(in);
  82.         ExcelTypeEnum excelTypeEnum = null;
  83.         if (NPOIFSFileSystem.hasPOIFSHeader(header8)) {
  84.             excelTypeEnum = ExcelTypeEnum.XLS;
  85.         }
  86.         if (DocumentFactoryHelper.hasOOXMLHeader(in)) {
  87.             excelTypeEnum = ExcelTypeEnum.XLSX;
  88.         }
  89.         if (excelTypeEnum != null) {
  90.             return new ExcelReader(in, excelTypeEnum, customContent, eventListener, trim);
  91.         }
  92.         throw new InvalidFormatException("Your InputStream was neither an OLE2 stream, nor an OOXML stream");
  93.     }
  94.  
  95.  
  96.     /*
  97.      *@Description: 读取Excel文件内容
  98.      *@param in excel文件流
  99.      *@param tClass 对应excel实体bean
  100.      *@return: 对应excel实体bean的list
  101.      *@Author:  William
  102.      *@Date:  2019/6/5 13:24
  103.      */
  104.     public static<T> List<T> getExcelContent(InputStream in, Class<T> tClass){
  105.         List<T> excelPropertyIndexModelList = new ArrayList<>();
  106.         try {
  107.             AnalysisEventListener<T> listener = new AnalysisEventListener<T>() {
  108.                 @Override
  109.                 public void invoke(T excelPropertyIndexModel, AnalysisContext analysisContext) {
  110.                     excelPropertyIndexModelList.add(excelPropertyIndexModel);
  111.                 }
  112.                 @Override
  113.                 public void doAfterAllAnalysed(AnalysisContext analysisContext) {
  114.                     //读取之后的操作
  115.                 }
  116.             };
  117.             ExcelReader excelReader = EasyExcelUtil.getExcelReader(in, null, listener);
  118.             // 第二个参数为表头行数，按照实际设置
  119.             excelReader.read(new Sheet(1, 1, (Class<? extends BaseRowModel>) tClass));
  120.         } catch (Exception e) {
  121.             e.printStackTrace();
  122.         }
  123.         return excelPropertyIndexModelList;
  124.     }
  125. }

第三步：封装excel对应的实体bean（这一步要注意，每一个字段跟表格要对应起来）

  1. package com.edu.common.pojo;
  2.  
  3. import java.io.FileInputStream;
  4. import java.io.InputStream;
  5. import java.util.List;
  6. import com.alibaba.excel.annotation.ExcelProperty;
  7. import com.alibaba.excel.metadata.BaseRowModel;
  8. import com.edu.common.utils.EasyExcelUtil;
  9.  
  10. /**
  11.  *@ClassName ExcelImportUserInfo
  12.  *@Description 批量导入用户信息模板bean
  13.  *@Auther William
  14.  *@Date 2019/6/5 13:32
  15.  *@Version 1.0
  16.  */
  17. public class ExcelImportUserInfo extends BaseRowModel{
  18.     @ExcelProperty(value = "用户名", index = 0)
  19.     private String userName;
  20.  
  21.     @ExcelProperty(value = "用户角色", index = 1)
  22.     private String userRole;
  23.  
  24.     @ExcelProperty(value = "用户姓名", index = 2)
  25.     private String userId;
  26.  
  27.     @ExcelProperty(value = "用户密码", index = 3)
  28.     private String password;
  29.  
  30.     @ExcelProperty(value = "性别", index = 4)
  31.     private String sex;
  32.  
  33.     @ExcelProperty(value = "用户年级", index = 5)
  34.     private String gradeId;
  35.  
  36.     @ExcelProperty(value = "用户学校", index = 6)
  37.     private String schoolId;
  38.  
  39.     @ExcelProperty(value = "用户状态", index = 7)
  40.     private String userStatus;
  41.  
  42.     public String getUserName() {
  43.         return userName;
  44.     }
  45.  
  46.     public void setUserName(String userName) {
  47.         this.userName = userName;
  48.     }
  49.  
  50.     public String getUserRole() {
  51.         return userRole;
  52.     }
  53.  
  54.     public void setUserRole(String userRole) {
  55.         this.userRole = userRole;
  56.     }
  57.  
  58.     public String getUserId() {
  59.         return userId;
  60.     }
  61.  
  62.     public void setUserId(String userId) {
  63.         this.userId = userId;
  64.     }
  65.  
  66.     public String getPassword() {
  67.         return password;
  68.     }
  69.  
  70.     public void setPassword(String password) {
  71.         this.password = password;
  72.     }
  73.  
  74.     public String getSex() {
  75.         return sex;
  76.     }
  77.  
  78.     public void setSex(String sex) {
  79.         this.sex = sex;
  80.     }
  81.  
  82.     public String getGradeId() {
  83.         return gradeId;
  84.     }
  85.  
  86.     public void setGradeId(String gradeId) {
  87.         this.gradeId = gradeId;
  88.     }
  89.  
  90.     public String getSchoolId() {
  91.         return schoolId;
  92.     }
  93.  
  94.     public void setSchoolId(String schoolId) {
  95.         this.schoolId = schoolId;
  96.     }
  97.  
  98.     public String getUserStatus() {
  99.         return userStatus;
  100.     }
  101.  
  102.     public void setUserStatus(String userStatus) {
  103.         this.userStatus = userStatus;
  104.     }
  105.     @Override
  106.     public String toString() {
  107.         return "ExcelImportUserInfo{" +
  108.                 "userName='" + userName + '\'' +
  109.                 ", userRole='" + userRole + '\'' +
  110.                 ", userId='" + userId + '\'' +
  111.                 ", password='" + password + '\'' +
  112.                 ", sex='" + sex + '\'' +
  113.                 ", gradeId='" + gradeId + '\'' +
  114.                 ", schoolId='" + schoolId + '\'' +
  115.                 ", userStatus='" + userStatus + '\'' +
  116.                 '}';
  117.     }
  118.  
  119.     
  120. }

对应的excel表格

测试方法：（测试方法基于jdk1.8,如果1.7自己换成for循环吧）

public static void main(String[] args) throws Exception {

InputStream in = new FileInputStream("E://aaa.xlsx");
  //读取的工具类可以根据自己需要封装
  //我这边是需要List集合所以封装了集合

List<ExcelImportUserInfo> excelContentList = EasyExcelUtil.getExcelContent(in, ExcelImportUserInfo.class); excelContentList.forEach(excelPropertyIndexModel -> {

           System.out.println(excelPropertyIndexModel.toString());

});

}

好了，以上就是使用方法了。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/小蓝xlanll/article/detail/496073
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号