SpringBoot实现Excel读取
这是本人写的一个SpringBoot对Excel读取的方法,实测能用,待提升的地方有很多,有不足之处请多多指点。
Excel2003版(后缀为.xls)最大行数是65536行,最大列数是256列。
Excel2007以上的版本(后缀为.xlsx)最大行数是1048576行,最大列数是16384列。提供2种方法读取:1.根据指定的开始和结束行数读取返回结果,结果格式为List<Map<String, Object>>2.根据指定的开始和结束行数读取返回结果,结果格式为List<POJO(传入的实体类)>
请根据实际内存堆可用大小进行读取,太多可进行分段读取(类似分页的原理)
读取Excel所需要的几个类
![3056caae4ae8e1885c2ad9ee0e7c855e.png](https://img-blog.csdnimg.cn/img_convert/3056caae4ae8e1885c2ad9ee0e7c855e.png)
1.在pom.xml加上依赖
</dependencies>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
</dependencies>
2.ExcelPOJO实体类
package com.cly.utils.Excel;
/**
* @author : CLy
* @ClassName : ExcelPOJO
* @date : 2020/7/9 17:13
* 实体类所有成员变量都需要有GET,SET方法
* 所有成员变量都要加上注解@excelRescoure(value = "?"),?为Excel真实列名,必须一一对应
* @excelRescoure(value = "?"),?可为空,需要用到才赋值
* 成员变量目前只允许String,Double,Interge,Float
**/
public class ExcelPOJO {
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPasswork() {
return passwork;
}
public void setPasswork(String passwork) {
this.passwork = passwork;
}
public String getLook() {
return look;
}
public void setLook(String look) {
this.look = look;
}
@excelRescoure(value = "XM")
private String name;
@excelRescoure(value = "SFZH")
private String passwork;
@excelRescoure()
private String look;
@Override
public String toString(){
return "name:"+this.getName()+",passwork:"+this.getPasswork()+",look:"+this.getLook();
}
public ExcelPOJO() {}
}
3.@interface自定义注解(用于实体类读取)
package com.cly.utils.Excel;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* @author : CLy
* @ClassName : myRescoure
* @date : 2020/7/10 9:31
**/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface excelRescoure {
String value() default "";//默认为空
}
4.excelRead类(读取Excel数据类)有很多冗余的代码,可抽离出来
package com.cly.utils.Excel;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.sun.org.apache.bcel.internal.generic.NEW;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.xml.transform.Source;
import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.*;
import java.text.DecimalFormat;
import java.util.*;
/**
* @author : CLy
* @ClassName : excelRead
* @date : 2020/7/9 11:08
**/
public class excelRead {
//日志输出
private static Logger logger = LoggerFactory.getLogger(excelRead.class);
//定义excel类型
private static f