引入easyexcel依赖
<!-- easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.11</version>
</dependency>
代码如下
public interface ExcelAlphabet {
String A = "A";
String B = "B";
String C = "C";
String D = "D";
String E = "E";
String F = "F";
String G = "G";
String H = "H";
String I = "I";
String J = "J";
String K = "K";
String L = "L";
String M = "M";
String N = "N";
String O = "O";
String P = "P";
String Q = "Q";
String R = "R";
String S = "S";
String T = "T";
String U = "U";
String V = "V";
String W = "W";
String X = "X";
String Y = "Y";
String Z = "Z";
}
import lombok.Data;
import java.lang.reflect.Field;
import java.util.LinkedHashMap;
import java.util.Map;
@Data
public class ExcelEnums {
//字母转数字
private final static Map<Integer, String> number_to_letter = new LinkedHashMap<>();
//数字转字母
private final static Map<String, Integer> letter_to_number = new LinkedHashMap<>();
private volatile static ExcelEnums single;
public static String getForNumber(Integer number) {
if (number == null || number <= 0) { // 检测列数是否正确
throw new IllegalArgumentException("number is wrongful");
}
String letter = number_to_letter.get(number);
if (letter == null) {
letter = numberToLetter(number);
number_to_letter.put(number, letter);
}
return letter;
}
public static Integer getForLetter(String letter) {
if (letter == null) {
throw new IllegalArgumentException("letter is null");
}
Integer number = letter_to_number.get(letter);
if (letter == null) {
number = letterToNumber(letter);
letter_to_number.put(letter, number);
}
return number;
}
private ExcelEnums() {
}
public static ExcelEnums getInstance() {
if (single == null) {
synchronized (ExcelEnums.class) {
if (single == null) {
single = new ExcelEnums();
}
}
}
return single;
}
static {
try {
Class<ExcelAlphabet> excelAlphabetClass = ExcelAlphabet.class;
Field[] declaredFields = excelAlphabetClass.getDeclaredFields();
for (int i = 0; i < declaredFields.length; i++) {
Field declaredField = declaredFields[i];
String letter = (String) declaredField.get(excelAlphabetClass);
int number = letterToNumber(letter);
number_to_letter.put(number, letter);
letter_to_number.put(letter, number);
}
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
/**
* 将以字母表示的Excel列数转换成数字表示
*
* @param letter 以字母表示的列数,不能为空且只允许包含字母字符
* @return 返回转换的数字,转换失败返回-1
* @author WuQianLing
*/
private static int letterToNumber(String letter) {
// 检查字符串是否为空
if (letter == null || letter.isEmpty()) {
return -1;
}
String upperLetter = letter.toUpperCase(); // 转为大写字符串
if (!upperLetter.matches("[A-Z]+")) { // 检查是否符合,不能包含非字母字符
return -1;
}
long num = 0; // 存放结果数值
long base = 1;
// 从字符串尾部开始向头部转换
for (int i = upperLetter.length() - 1; i >= 0; i--) {
char ch = upperLetter.charAt(i);
num += (ch - 'A' + 1) * base;
base *= 26;
if (num > Integer.MAX_VALUE) { // 防止内存溢出
return -1;
}
}
return (int) num;
}
/**
* 将数字转换成以字母表示的Excel列数
*
* @param num 表示列数的数字
* @return 返回转换的字母字符串,转换失败返回null
* @author WuQianLing
*/
private static String numberToLetter(int num) {
if (num <= 0) { // 检测列数是否正确
return null;
}
StringBuffer letter = new StringBuffer();
do {
--num;
int mod = num % 26; // 取余
letter.append((char) (mod + 'A')); // 组装字符串
num = (num - mod) / 26; // 计算剩下值
} while (num > 0);
return letter.reverse().toString(); // 返回反转后的字符串
}
}
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE, ElementType.FIELD})
public @interface ExcelSheet {
String abscissa() default "";
int ordinate() default -1;
}
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Retention(RetentionPolicy.RUNTIME)
/**
*
*
**/
@Target({ElementType.TYPE, ElementType.FIELD})
public @interface ExcelSheetRow {
String abscissa() default "";
}
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.CellData;
import com.yq.supervision.utils.DateDifferenceUtil;
import lombok.Data;
import org.springblade.common.constant.ExcelEnums;
import org.springblade.core.tool.utils.Func;
import javax.sql.rowset.serial.SerialBlob;
import javax.sql.rowset.serial.SerialClob;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.ObjectOutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.SQLException;
import java.sql.Time;
import java.util.*;
@Data
public abstract class ReadListenerFather<Bean> extends AnalysisEventListener<LinkedHashMap> {
public ReadListenerFather(String letter) {
count = 0;
true_count = 0;
max_row = ExcelEnums.getForLetter(letter);
dateUtil = DateDifferenceUtil.getInstance();
}
/**
*
* @param letter 第一列空数据位置
* @param beanClass
*/
public ReadListenerFather(String letter, Class<Bean> beanClass) {
this(letter);
this.beanClass = beanClass;
}
List<Map> list = new ArrayList<Map>();
protected int max_row;
protected DateDifferenceUtil dateUtil;
protected int count;
protected int true_count;
protected int head_row_number = 1;
protected Class<Bean> beanClass;
protected final static ExcelEnums exception = ExcelEnums.getInstance();
public void invokeHead(Map<Integer, CellData> headMap, AnalysisContext context) {
checkUp(headMap, context);
super.invokeHead(headMap, context);
this.head_row_number++;
}
public void checkUp(Map headMap, AnalysisContext context) {
if (headMap.size() >= max_row) {
int key = 0;
String value = null;
try {
Field tail = headMap.getClass().getDeclaredField("tail");
tail.setAccessible(true);
Map.Entry<Integer, Object> entry = (Map.Entry<Integer, Object>) tail.get(headMap);
key = entry.getKey();
Object values = entry.getValue();
if (values != null) {
value = values.toString();
}
} catch (NoSuchFieldException | IllegalAccessException e) {
e.printStackTrace();
}
throw new IllegalStateException(
new StringBuilder(context.readSheetHolder().getSheetName())
.append(" Greater than length [")
.append(ExcelEnums.getForNumber(key + 1))
.append((true_count + 1))
.append("] value [")
.append(value)
.append("]")
.toString());
}
true_count++;
count++;
}
@Override
public void invoke(LinkedHashMap data, AnalysisContext context) {
if (count == 0) {
count = context.getCurrentRowNum();
true_count = count;
}
checkUp(data, context);//count++ ; true_count++;
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
}
public abstract List<Bean> listToBean() throws InstantiationException, IllegalAccessException, SQLException;
protected byte[] toByteArray(Object obj) {
byte[] bytes = null;
ByteArrayOutputStream bos = null;
ObjectOutputStream oos = null;
try {
bos = new ByteArrayOutputStream();
oos = new ObjectOutputStream(bos);
oos.writeObject(obj);
bytes = bos.toByteArray();
} catch (IOException ex) {
return new byte[0];
} finally {
try {
if (oos != null) {
oos.close();
oos = null;
}
} catch (IOException EOss) {
EOss.printStackTrace();
}
try {
if (bos != null) {
bos.flush();
bos.close();
bos = null;
}
} catch (IOException BOss) {
BOss.printStackTrace();
}
}
return bytes;
}
protected <Bean> void judgmentCategory(Field TField, Bean addT, Object getKey) throws IllegalAccessException, SQLException {
if (getKey == null || getKey.equals("null") || "".equals(getKey)) {
return;
}
if (TField.getType().isAssignableFrom(void.class) || TField.getType().isAssignableFrom(Void.class)) {
return;
}
if (TField.getType().isAssignableFrom(String.class) || TField.getType().isAssignableFrom(Object.class)) {
TField.set(addT, getKey);
return;
}
if (TField.getType().isArray()) {
if (TField.getType().isAssignableFrom(Byte[].class) || TField.getType().isAssignableFrom(byte[].class)) {
TField.set(addT, toByteArray(getKey));
} else {
TField.set(addT, getKey);
}
}
judgmentCategory(TField, addT, Func.toStr(getKey), false);
}
protected boolean isWrapClass(Class clz) {
try {
return ((Class) clz.getField("TYPE").get(null)).isPrimitive();
} catch (Exception e) {
return false;
}
}
protected <Bean> void judgmentCategory(Field TField, Bean addT, String getKey, boolean meaningless) throws IllegalAccessException, SQLException {
if (isWrapClass((Class) TField.getType()) || TField.getType().isPrimitive()) {
if (TField.getType().isAssignableFrom(Byte.class) || TField.getType().isAssignableFrom(byte.class)) {
TField.set(addT, Byte.parseByte(getKey));
} else if (TField.getType().isAssignableFrom(Short.class) || TField.getType().isAssignableFrom(short.class)) {
TField.set(addT, Short.parseShort(getKey));
} else if (TField.getType().isAssignableFrom(Integer.class) || (TField.getType().isAssignableFrom(int.class))) {
TField.set(addT, Integer.parseInt(getKey));
} else if (TField.getType().isAssignableFrom(Long.class) || TField.getType().isAssignableFrom(long.class)) {
TField.set(addT, Long.parseLong(getKey));
} else if (TField.getType().isAssignableFrom(Float.class) || TField.getType().isAssignableFrom(float.class)) {
TField.set(addT, Float.parseFloat(getKey));
} else if (TField.getType().isAssignableFrom(Character.class) || TField.getType().isAssignableFrom(char.class)) {
TField.set(addT, getKey.charAt(0));
} else if (TField.getType().isAssignableFrom(Boolean.class) || TField.getType().isAssignableFrom(boolean.class)) {
TField.set(addT, Boolean.parseBoolean(getKey));
} else if (TField.getType().isAssignableFrom(Double.class) || TField.getType().isAssignableFrom(double.class)) {
TField.set(addT, Double.parseDouble(getKey));
} else {
return;
}
} else if (TField.getType().isAssignableFrom(BigDecimal.class)) {
TField.set(addT, new BigDecimal(getKey));
} else {
//date_start
if (TField.getType().isAssignableFrom(Date.class)) {
TField.set(addT, dateUtil.forMat(getKey, true));
} else if (TField.getType().isAssignableFrom(java.sql.Timestamp.class)) {
TField.set(addT, new java.sql.Timestamp(dateUtil.forMat(getKey, true).getTime()));
}
// else if (TField.getType().isAssignableFrom(com.sun.jmx.snmp.Timestamp.class)) {
// TField.set(addT, new com.sun.jmx.snmp.Timestamp(dateUtil.forMat(getKey).getTime()));
// }
else if (TField.getType().isAssignableFrom(Time.class)) {
TField.set(addT, new Time(dateUtil.forMat(getKey, true).getTime()));
//date_end
} else if (Blob.class.isAssignableFrom(TField.getType())) {
TField.set(addT, new SerialBlob(getKey.getBytes()));
} else if (Clob.class.isAssignableFrom(TField.getType())) {
TField.set(addT, new SerialClob(getKey.toCharArray()));
} else {
return;
}
}
}
}
import com.alibaba.excel.context.AnalysisContext;
import org.springblade.common.constant.ExcelSheet;
import java.lang.reflect.Field;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
//根据坐标读取
public class SheetReadListener<Bean> extends ReadListenerFather<Bean> {
public String[] key;
public SheetReadListener(String letter, Class<Bean> beanClass, String... key) {
super(letter, beanClass);
this.key = key;
}
public SheetReadListener(String letter, Class<Bean> beanClass) {
super(letter, beanClass);
}
public SheetReadListener(String letter) {
super(letter);
}
@Override
public void invoke(LinkedHashMap data, AnalysisContext context) {
if (key != null) {
for (int i = 0; i < key.length; i++) {
if (key[i].equals(data.get(0))) {
return;
}
}
}
super.invoke(data, context);
list.add(data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
}
@Override
public List<Bean> listToBean() throws InstantiationException, IllegalAccessException, SQLException {
List<Bean> arrays = new ArrayList<>();
Class<Bean> tCalls = this.beanClass;
if (tCalls == null) {
throw new IllegalStateException("Class is null");
}
if (list.size() > 0) {
Bean object = tCalls.newInstance();
Field[] declaredFields = tCalls.getDeclaredFields();
for (int i = 0; i < declaredFields.length; i++) {
Field declaredField = declaredFields[i];
declaredField.setAccessible(true);
ExcelSheet annotation = declaredField.getAnnotation(ExcelSheet.class);
if (annotation != null) {
Map linkedHashMap = list.get(annotation.ordinate() - head_row_number);
try {
super.judgmentCategory(declaredField, object, linkedHashMap.get(exception.getForLetter(annotation.abscissa()) - 1));
} catch (IllegalAccessException e) {
throw new IllegalAccessException(tCalls + "." + declaredField.getName() + ":\t\t\t" + e.getMessage());
}
}
}
arrays.add(object);
}
return arrays;
}
}
import com.alibaba.excel.context.AnalysisContext;
import org.springblade.common.constant.ExcelSheetRow;
import java.lang.reflect.Field;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
//数组列
public class SheetReadRowListener<Bean> extends ReadListenerFather<Bean> {
public SheetReadRowListener(String letter, Class<Bean> beanClass, String... key) {
super(letter, beanClass);
this.key = key;
}
public SheetReadRowListener(String letter, Class<Bean> beanClass) {
super(letter, beanClass);
}
public SheetReadRowListener(String letter) {
super(letter);
}
public String[] key;
@Override
public void invoke(LinkedHashMap data, AnalysisContext context) {
if (test(data)) {
return;
}
if (key != null) {
for (int i = 0; i < key.length; i++) {
if (key[i].equals(data.get(0))) {
return;
}
}
}
super.invoke(data, context);
list.add(data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
}
@Override
public List<Bean> listToBean() throws InstantiationException, IllegalAccessException, SQLException {
List<Bean> arrays = new ArrayList<>();
Class<Bean> tCalls = this.beanClass;
if (tCalls == null) {
throw new IllegalStateException("Class is null");
}
if (list.size() > 0) {
for (Map map : list) {
if (test(map)) {
continue;
}
Bean object = tCalls.newInstance();
Field[] declaredFields = tCalls.getDeclaredFields();
for (int i = 0; i < declaredFields.length; i++) {
Field declaredField = declaredFields[i];
declaredField.setAccessible(true);
ExcelSheetRow annotation = declaredField.getAnnotation(ExcelSheetRow.class);
if (annotation != null) {
try {
super.judgmentCategory(declaredField, object, map.get(exception.getForLetter(annotation.abscissa()) - 1));
} catch (Exception e) {
throw new InstantiationException(declaredField.getName() + e.getMessage());
}
}
}
arrays.add(object);
}
}
return arrays;
}
public boolean test(Map linkedHashMap) {
for (Object obj : linkedHashMap.values()) {
if (obj != null) {
return false;
}
}
return true;
}
}