大概就这样(有需要我补充)
1.工具类(好几个)
import java.util.LinkedHashMap;
public class ExcelClassField {
/** 字段名称 */
private String fieldName;
/** 表头名称 */
private String name;
/** 映射关系 */
private LinkedHashMap<String, String> kvMap;
/** 示例值 */
private Object example;
/** 排序 */
private int sort;
/** 是否为注解字段:0-否,1-是 */
private int hasAnnotation;
public String getFieldName() {
return fieldName;
}
public void setFieldName(String fieldName) {
this.fieldName = fieldName;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public LinkedHashMap<String, String> getKvMap() {
return kvMap;
}
public void setKvMap(LinkedHashMap<String, String> kvMap) {
this.kvMap = kvMap;
}
public Object getExample() {
return example;
}
public void setExample(Object example) {
this.example = example;
}
public int getSort() {
return sort;
}
public void setSort(int sort) {
this.sort = sort;
}
public int getHasAnnotation() {
return hasAnnotation;
}
public void setHasAnnotation(int hasAnnotation) {
this.hasAnnotation = hasAnnotation;
}
}
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelExport {
/** 字段名称 */
String value();
/** 导出排序先后: 数字越小越靠前(默认按Java类字段顺序导出) */
int sort() default 0;
/** 导出映射,格式如:0-未知;1-男;2-女 */
String kv() default "";
/** 导出模板示例值(有值的话,直接取该值,不做映射) */
String example() default "";
}
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelImport {
/** 字段名称 */
String value();
/** 导出映射,格式如:0-未知;1-男;2-女 */
String kv() default "";
/** 是否为必填字段(默认为非必填) */
boolean required() default false;
/** 最大长度(默认255) */
int maxLength() default 255;
/** 导入唯一性验证(多个字段则取联合验证) */
boolean unique() default false;
}
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URL;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Map.Entry;
/**
* Excel导入导出工具类
*
*/
public class ExcelUtils {
private static final String XLSX = ".xlsx";
private static final String XLS = ".xls";
public static final String ROW_MERGE = "row_merge";
public static final String COLUMN_MERGE = "column_merge";
private static final String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
private static final String ROW_NUM = "rowNum";
private static final String ROW_DATA = "rowData";
private static final String ROW_TIPS = "rowTips";
private static final int CELL_OTHER = 0;
private static final int CELL_ROW_MERGE = 1;
private static final int CELL_COLUMN_MERGE = 2;
private static final int IMG_HEIGHT = 30;
private static final int IMG_WIDTH = 30;
private static final char LEAN_LINE = '/';
private static final int BYTES_DEFAULT_LENGTH = 10240;
private static final NumberFormat NUMBER_FORMAT = NumberFormat.getNumberInstance();
public static <T> List<T> readFile(File file, Class<T> clazz) throws Exception {
JSONArray array = readFile(file);
return getBeanList(array, clazz);
}
public static <T> List<T> readMultipartFile(MultipartFile mFile, Class<T> clazz) throws Exception {
JSONArray array = readMultipartFile(mFile);
return getBeanList(array, clazz);
}
public static JSONArray readFile(File file) throws Exception {
return readExcel(null, file);
}
public static JSONArray readMultipartFile(MultipartFile mFile) throws Exception {
return readExcel(mFile, null);
}
public static Map<String, JSONArray> readFileManySheet(File file) throws Exception {
return readExcelManySheet(null, file);
}
public static Map<String, JSONArray> readFileManySheet(MultipartFile file) throws Exception {
return readExcelManySheet(file, null);
}
private static <T> List<T> getBeanList(JSONArray array, Class<T> clazz) throws Exception {
List<T> list = new ArrayList<>();
Map<Integer, String> uniqueMap = new HashMap<>(16);
for (int i = 0; i < array.size(); i++) {
list.add(getBean(clazz, array.getJSONObject(i), uniqueMap));
}
return list;
}
/**
* 获取每个对象的数据
*/
private static <T> T getBean(Class<T> c, JSONObject obj, Map<Integer, String> uniqueMap) throws Exception {
T t = c.newInstance();
Field[] fields = c.getDeclaredFields();
List<String> errMsgList = new ArrayList<>();
boolean hasRowTipsField = false;
StringBuilder uniqueBuilder = new StringBuilder();
int rowNum = 0;
for (Field field : fields) {
// 行号
if (field.getName().equals(ROW_NUM)) {
rowNum = obj.getInteger(ROW_NUM);
field.setAccessible(true);
field.set(t, rowNum);
continue;
}
// 是否需要设置异常信息
if (field.getName().equals(ROW_TIPS)) {
hasRowTipsField = true;
continue;
}
// 原始数据
if (field.getName().equals(ROW_DATA)) {
field.setAccessible(true);
field.set(t, obj.toString());
continue;
}
// 设置对应属性值
setFieldValue(t, field, obj, uniqueBuilder, errMsgList);
}
// 数据唯一性校验
if (uniqueBuilder.length() > 0) {
if (uniqueMap.containsValue(uniqueBuilder.toString())) {
Set<Integer> rowNumKeys = uniqueMap.keySet();
for (Integer num : rowNumKeys) {
if (uniqueMap.get(num).equals(uniqueBuilder.toString())) {
errMsgList.add(String.format("数据唯一性校验失败,(%s)与第%s行重复)", uniqueBuilder, num));
}
}
} else {
uniqueMap.put(rowNum, uniqueBuilder.toString());
}
}
// 失败处理
if (errMsgList.isEmpty() && !hasRowTipsField) {
return t;
}
StringBuilder sb = new StringBuilder();
int size = errMsgList.size();
for (int i = 0; i < size; i++) {
if (i == size - 1) {
sb.append(errMsgList.get(i));
} else {
sb.append(errMsgList.get(i)).append(";");
}
}
// 设置错误信息
for (Field field : fields) {
if (field.getName().equals(ROW_TIPS)) {
field.setAccessible(true);
field.set(t, sb.toString());
}
}
return t;
}
private static <T> void setFieldValue(T t, Field field, JSONObject obj, StringBuilder uniqueBuilder, List<String> errMsgList) {
// 获取 ExcelImport 注解属性
ExcelImport annotation = field.getAn