JAVA读写EXCEL(免注解式解析，兼容2003和2007)

·本文提供了两种excel的解析方式，您可以根据自身需求进行使用。
·以下是一些使用建议:
	本文包含了（同一种核心），一种为注解式解析，一种为非注解式解析。
	如果您的excel处理起来并不复杂，可以使用注解式进行处理。
	如果您的excel中的数据解析起来较为复杂或有一些其他处理，建议使用非注解式手动配置解析。

·本人更倾向于使用者在使用过程中，根据自己的需求修改此代码，使其更加实用，而非生搬硬套

• 本文中使用到的架包
  poi-3.11-20141221.jar(提供.xls格式的excel文件所需要HSSF支持)
  poi-ooxml-3.11-20141221.jar(提供.xlsx格式的excel文件所需要XSSF支持)
  poi-ooxml-schemas-3.11-20141221.jar(ooxml依赖包)
  xmlbeans-2.6.0.jar(用于解析xml,poi-ooxml-schemas依赖包)
• maven

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.11</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.11</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.11</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.xmlbeans/xmlbeans -->
<dependency>
    <groupId>org.apache.xmlbeans</groupId>
    <artifactId>xmlbeans</artifactId>
    <version>2.6.0</version>
</dependency>

• 使用示例(使用注解)

import com.test.utils.excel.Excel;
import com.test.utils.excel.ExcelCol;
import com.test.utils.excel.ExcelColType;
import lombok.Data;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Font;

import java.util.Date;
import java.util.List;

@Data
@Excel(titleRow = 0)
public class TestModel {

    @ExcelCol(titleIndex = 0, titleName = "id", type = ExcelColType.UUID32)
    private String id;

    @ExcelCol(titleName = "superId", type = ExcelColType.TIME_MILLIS)
    private String superId;

    @ExcelCol(titleIndex = 0, format = {"M月d号","MM月dd号"})
    private Date date;

    @ExcelCol(titleIndex = 1, append = "星期：$value$")
    private String week;

    @ExcelCol(titleName = "姓名", fontColor = Font.COLOR_RED, width = 30)
    private String name;

    @ExcelCol(titleIndex = 3, format = {"HH:mm:ss", "H:mm:ss"})
    private Date startTime;

    @ExcelCol(titleIndex = 4)
    private String content;

    @ExcelCol(titleName = "结束时间", format = {"HH:mm:ss", "H:mm:ss"}, backGroundColor = HSSFColor.LIME.index)
    private Date endTime;

	public static void main(String[] args) {
		List<TestModel> testModels = ExcelUtils.read("D:\\test\\六月份考勤.xlsx", TestModel.class);
		System.out.println(testModels);
		boolean writer = ExcelUtils.write("D:\\test\\六月份考勤-3.xlsx", testModels);
		System.out.println("是否创建：" + writer);
	}
}

• 使用示例(不使用注解)

public static void main(String[] args) throws IOException {
        List<TestModel> list = ExcelUtils.open("D:\\test\\Test.xlsx").sheet(0, TestModel::new)
                .title(0)
                .col(-1).value(() -> UUID.randomUUID().toString()).to(TestModel::setId)
                .col(0).to((bean, value) -> {
                    if (value != null && !value.isEmpty()) {
                        bean.setDate(toDate(value, value.length() == 4 ? "M月d号" : "MM月dd号"));
                    }
                })
                .col(1).to(TestModel::setWeek)
                .col("姓名").to(TestModel::setName)
                .col(3).format("HH:mm:ss").to((bean, value)->bean.setStartTime(toDate(value, "HH:mm:ss")))
                .col(4).to(TestModel::setContent)
                .col("下班时间").to((bean, value) -> {
                    if (value != null && !value.isEmpty()) {
                        bean.setEndTime(toDate(value, value.length() == 7 ? "H:mm:ss" : "HH:mm:ss"));
                    }
                }).read();
        ExcelUtils.open("D:\\test\\Test-2.xlsx").sheet(0, TestModel::new)
                .title(0)
                .col(0, "id").data(TestModel::getId)
                .col(1).data(bean->dateToString(bean.getDate(), "MM月dd日"))
                .col("星期").data(TestModel::getWeek).fontColor(Font.COLOR_RED)
                .col(3, "姓名").data(TestModel::getName)
                .col(4, "上班时间").data(bean->dateToString(bean.getStartTime(), "HH:mm:ss"))
                .col("工作内容").data(TestModel::getContent).wrapText()
                .col(6).data(bean->dateToString(bean.getEndTime(), "HH:mm:ss"))
                .write(list);

    }

• 代码实现

工具类：

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.xmlbeans.impl.piccolo.io.FileFormatException;

import java.io.*;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.function.BiConsumer;
import java.util.function.Function;
import java.util.function.Supplier;
import java.util.stream.Collectors;

public class ExcelUtils {

    private String excelPath;

    private boolean isExcel2003;

    private ExcelUtils(){}

    public static <T> boolean write(String path, List<T> data) {
        Class<?> clazz = data.get(0).getClass();
        try {
            Excel excelAnnotation = clazz.getAnnotation(Excel.class);
            Field[] fields = clazz.getDeclaredFields();
            ExcelUtils excel = ExcelUtils.open(path);
            SheetModel<T> sheet;
            if (!"".equals(excelAnnotation.sheetName())) {
                sheet = excel.sheet(excelAnnotation.sheetName());
            } else {
                sheet = excel.sheet(excelAnnotation.sheetIndex());
            }
            if (excelAnnotation.titleRow() > -1) {
                sheet.title(excelAnnotation.titleRow());
            }
            for (Field field : fields) {
                ExcelCol excelCol = field.getAnnotation(ExcelCol.class);
                if (excelCol == null) {
                    continue;
                }
                ColModel<T> col;
                if (!"".equals(excelCol.titleName())) {
                    if (excelCol.titleIndex() != -1) {
                        col = sheet.col(excelCol.titleIndex(), excelCol.titleName());
                    } else {
                        col = sheet.col(excelCol.titleName());
                    }
                } else if(excelCol.titleIndex() != -1) {
                    col = sheet.col(excelCol.titleIndex());
                } else {
                    throw new NullPointerException("未定义的列：" + field.getName());
                }


                if (excelCol.width() != -1) {
                    col.width(excelCol.width());
                }
                if (excelCol.height() != -1) {
                    col.height(excelCol.height());
                }
                if (!"".equals(excelCol.fontName())) {
                    col.fontName(excelCol.fontName());
                }
                if (excelCol.fontBold()) {
                    col.fontBold();
                }
                if (excelCol.fontColor() != -1) {
                    col.fontColor((short) excelCol.fontColor());
                }
                if (excelCol.fontSize() != -1) {
                    col.fontSize(excelCol.fontSize());
                }
                if (excelCol.wrapText()) {
                    col.wrapText();
                }
                if (excelCol.italic()) {
                    col.italic();
                }
                if (excelCol.backGroundColor() != -1) {
                    col.backGroundColor((short) excelCol.backGroundColor());
                }
                if (excelCol.center()) {
                    col.center();
                }
                if (excelCol.border().length > 0 && excelCol.border().length == 4) {
                    Map<Integer, HSSFColor> indexHash = HSSFColor.getIndexHash();
                    HSSFColor u = indexHash.get(excelCol.border()[0]);
                    HSSFColor r = indexHash.get(excelCol.border()[1]);
                    HSSFColor b = indexHash.get(excelCol.border()[2]);
                    HSSFColor l = indexHash.get(excelCol.border()[3]);
                    col.border(u, r, b, l);
                }
                if (excelCol.type() == ExcelColType.UUID) {
                    col.value(()-> UUID.randomUUID().toString());
                    continue;
                }
                else if (excelCol.type() == ExcelColType.UUID32) {
                    col.value(()-> UUID.randomUUID().toString().replace("-", ""));
                    continue;
                }
                else if (excelCol.type() == ExcelColType.TIME_MILLIS) {
                    col.value(()-> String.valueOf(System.currentTimeMillis()));
                    continue;
                }
                Class<?> type = field.getType();
                if (excelCol.type() == ExcelColType.BASE && type == Date.class) {
                    col.format(excelCol.format()[0]).data((bean)-> getValue(field, bean));
                }
                else if (excelCol.type() == ExcelColType.BASE && type == String.class && !"".equals(excelCol.append())) {
                    col.data((bean) -> {
                        Object value = getValue(field, bean);
                        return excelCol.append().replace("$value$", String.valueOf(value));
                    });
                }
                else if (excelCol.type() == ExcelColType.BASE) {
                    col.data((bean) -> getValue(field, bean));
                }
                else if (excelCol.type() == ExcelColType.LIST && !"".equals(excelCol.split())) {
                    col.data((bean) -> {
                        List<?> list = (List<?>)getValue(field, bean);
                        if (list == null) {
                            return null;
                        }
                        StringBuilder s = new StringBuilder();
                        for (Object o : list) {
                            s.append(o).append(excelCol.split());
                        }
                        s.delete(s.length() - excelCol.split().length(), s.length());
                        return s.toString();
                    });
                }
                else {
                    col.data((bean) -> getValue(field, bean));
                }
            }
            sheet.write(data);
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    public static <T> List<T> read(String path, Class<T> clazz) {
        SheetModel<T> sheet = null;
        try {
            Excel excelAnnotation = clazz.getAnnotation(Excel.class);
            Field[] fields = clazz.getDeclaredFields();
            ExcelUtils excel = ExcelUtils.open(path);
            if (!"".equals(excelAnnotation.sheetName())) {
                sheet = excel.sheet(excelAnnotation.sheetName(), () -> {
                    try {
                        return clazz.newInstance();
                    } catch (Exception ignored) {
                        return null;
                    }
                });
            } else {
                sheet = excel.sheet(excelAnnotation.sheetIndex(), ()-> {
                    try {
                        return clazz.newInstance();
                    } catch (Exception ignored) {
                        return null;
                    }
                });
            }
            if (excelAnnotation.titleRow() > -1) {
                sheet.title(excelAnnotation.titleRow());
            }
            for (Field field : fields) {
                ExcelCol excelCol = field.getAnnotation(ExcelCol.class);
                if (excelCol == null) {
                    continue;
                }
                ColModel<T> col;
                if (excelCol.type() == ExcelColType.UUID) {
                    col = sheet.col(-1).value(()-> UUID.randomUUID().toString());
                }
                else if (excelCol.type() == ExcelColType.UUID32) {
                    col = sheet.col(-1).value(()-> UUID.randomUUID().toString().replace("-", ""));
                }
                else if (excelCol.type() == ExcelColType.TIME_MILLIS) {
                    col = sheet.col(-1).value(()-> String.valueOf(System.currentTimeMillis()));
                }
                else if (!"".equals(excelCol.titleName())) {
                    col = sheet.col(excelCol.titleName());
                } else if(excelCol.titleIndex() != -1) {
                    col = sheet.col(excelCol.titleIndex());
                } else {
                    throw new NullPointerException("未定义的列：" + field.getName());
                }
                Class<?> type = field.getType();
                if (excelCol.type() == ExcelColType.BASE && type == Date.class) {
                    col.format(excelCol.format()[0]).to((bean, value)-> {
                        Date date = toDate(excelCol.format(), value);
                        setValue(field, bean, date);
                    });
                }
                else if (excelCol.type() == ExcelColType.BASE && type == String.class && !"".equals(excelCol.append())) {
                    col.to((bean, value) -> {
                        String value$ = excelCol.append().replace("$value$", String.valueOf(value));
                        setValue(field, bean, value$);
                    });
                }
                else if (excelCol.type() == ExcelColType.BASE) {
                    col.to((bean, value) -> setValue(field, bean, tranType(value, type)));
                }
                else if (excelCol.type() == ExcelColType.LIST && !"".equals(excelCol.split())) {
                    col.to((bean, value) -> {
                        List<?> list = toList(value, excelCol.split(), excelCol.valueType());
                        setValue(field, bean, list);
                    });
                }
                else if (excelCol.type() == ExcelColType.UUID || excelCol.type() == ExcelColType.UUID32 || excelCol.type() == ExcelColType.TIME_MILLIS) {
                    col.to((bean, value) -> setValue(field, bean, value));
                }
                else {
                    col.to((bean, value) -> setValue(field, bean, tranType(value, type)));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (sheet == null) {
            return new ArrayList<>();
        }
        return sheet.read();
    }

    /**
     * 打开一个文件（文件不一定必须存在）
     * @param excelPath excel路口
     * @return  excel操作对象
     */
    public static ExcelUtils open(String excelPath) throws IOException {
        if (!excelPath.endsWith("xlsx") && !excelPath.endsWith("xls")) {
            throw new FileFormatException("不是excel文件");
        }
        ExcelUtils excelUtils = new ExcelUtils();
        excelUtils.excelPath = excelPath;
        excelUtils.isExcel2003 = excelPath.endsWith("xls");
        return excelUtils;
    }

    /**
     * 指定一个sheet页
     * @param sheetName sheet页名称
     * @return  sheet页操作对象
     */
    public <T> SheetModel<T> sheet(String sheetName) {
        SheetModel<T> sheetModel = new SheetModel<>();
        sheetModel.sheetName = sheetName;
        return sheetModel;
    }

    /**
     * 指定一个sheet页
     * @param sheetName sheet页名称
     * @param supplier  sheet页对应的对象创建方法
     * @return  sheet页操作对象
     */
    public <T> SheetModel<T> sheet(String sheetName, Supplier<T> supplier) {
        SheetModel<T> sheetModel = new SheetModel<>(supplier);
        sheetModel.sheetName = sheetName;
        return sheetModel;
    }

    /**
     * 指定一个sheet页
     * @param sheetIndex sheet页索引
     * @return  sheet页操作对象
     */
    public <T> SheetModel<T> sheet(int sheetIndex) {
        SheetModel<T> sheetModel = new SheetModel<>();
        sheetModel.sheetIndex = sheetIndex;
        if (sheetModel.sheetName == null) {
            sheetModel.sheetName = "Sheet" + sheetIndex;
        }
        return sheetModel;
    }

    /**
     * 指定一个sheet页
     * @param sheetIndex sheet页索引
     * @param supplier  sheet页对应的对象创建方法
     * @return  sheet页操作对象
     */
    public <T> SheetModel<T> sheet(int sheetIndex, Supplier<T> supplier) {
        SheetModel<T> sheetModel = new SheetModel<>(supplier);
        sheetModel.sheetIndex = sheetIndex;
        if (sheetModel.sheetName == null) {
            sheetModel.sheetName = "Sheet" + sheetIndex;
        }
        return sheetModel;
    }

    private static Date toDate(String[] formats, String date) {
        if (date == null || "".equals(date)) {
            return null;
        }
        String format;
        char[] chars = date.toCharArray();
        for (int i = 0; i < formats.length; i++) {
            String fm = formats[i];
            if (fm.length() != date.length()) {
                formats[i] = null;
                continue;
            }
            char[] array = fm.toCharArray();
            for (int index = 0; index < array.length; index++) {
                if (String.valueOf(chars[index]).matches("[0-9]")) {
                    if (!String.valueOf(array[index]).matches("[yMdHmsS]")) {
                        formats[i] = null;
                        break;
                    }
                } else if (chars[index] != array[index]) {
                    formats[i] = null;
                    break;
                }
            }
        }
        format = Arrays.stream(formats).filter(Objects::nonNull).findFirst().orElse(null);
        if (format == null) {
            return null;
        }
        try {
            return new SimpleDateFormat(format).parse(date);
        } catch (Exception e) {
            return null;
        }
    }

    private static Object tranType(String value, Class<?> clazz) {
        if (clazz == String.class) {
            return value;
        }
        if (clazz == byte.class) {
            return tryCatch(Byte::valueOf, value, (byte) 0);
        }
        if (clazz == Byte.class) {
            return tryCatch(Byte::valueOf, value, null);
        }
        if (clazz == short.class) {
            return tryCatch(Short::valueOf, value, (short) 0);
        }
        if (clazz == Short.class) {
            return tryCatch(Short::valueOf, value, null);
        }
        if (clazz == int.class) {
            return tryCatch(Integer::valueOf, value, 0);
        }
        if (clazz == Integer.class) {
            return tryCatch(Integer::valueOf, value, null);
        }
        if (clazz == long.class) {
            return tryCatch(Long::valueOf, value, 0L);
        }
        if (clazz == Long.class) {
            return tryCatch(Long::valueOf, value, null);
        }
        if (clazz == float.class) {
            return tryCatch(Float::valueOf, value, 0.0f);
        }
        if (clazz == Float.class) {
            return tryCatch(Float::valueOf, value, null);
        }
        if (clazz == double.class) {
            return tryCatch(Double::valueOf, value, 0.0);
        }
        if (clazz == Double.class) {
            return tryCatch(Double::valueOf, value, null);
        }
        if (clazz == boolean.class) {
            return tryCatch(Boolean::valueOf, value, false);
        }
        if (clazz == Boolean.class) {
            return tryCatch(Boolean::valueOf, value, null);
        }
        if (clazz == char.class) {
            return tryCatch(v->v.charAt(0), value, '\u0000');
        }
        if (clazz == Character.class) {
            return tryCatch(v->v.charAt(0), value, null);
        }
        return null;
    }

    private static <T>T tryCatch(Function<String, T> function, String value, T defaultValue) {
        try {
            return function.apply(value);
        } catch (Exception e) {
            return defaultValue;
        }
    }

    private static <T>List<T> toList(String value, String split, Class<T> valueType) {
        List<T> list = new ArrayList<>();
        String[] strings = value.split(split);
        for (String string : strings) {
            Object o = tranType(string, valueType);
            list.add(valueType.cast(o));
        }
        return list;
    }

    private static void setValue(Field field, Object bean, Object value) {
        try {
            boolean accessible = field.isAccessible();
            field.setAccessible(true);
            field.set(bean, value);
            field.setAccessible(accessible);
        } catch (Exception ignored) {
        }
    }

    private static Object getValue(Field field, Object bean) {
        try {
            boolean accessible = field.isAccessible();
            field.setAccessible(true);
            Object value = field.get(bean);
            field.setAccessible(accessible);
            return value;
        } catch (Exception ignored) {
        }
        return null;
    }

    public class SheetModel<T> {

        private Supplier<T> supplier;

        private String sheetName;

        private int sheetIndex = -1;

        private final List<ColModel<T>> cols = new ArrayList<>();

        private int titleRow = -1;

        private SheetModel() {
        }

        private SheetModel(Supplier<T> supplier) {
            this.supplier = supplier;
        }

        /**
         * 设置表头
         * @param index 表头下标（从零开始）
         * @return  sheet页操作对象
         */
        public SheetModel<T> title(int index) {
            this.titleRow = index;
            return this;
        }

        /**
         * 指定一个单元格
         * @param colIndex 单元格下标
         * @return  单元格操作对象
         */
        public ColModel<T> col(int colIndex) {
            ColModel<T> colModel = new ColModel<>(this);
            colModel.colIndex = colIndex;
            colModel.colName = "Column" + (this.cols.size() + 1);
            this.cols.add(colModel);
            return colModel;
        }

        /**
         * 指定一个单元格
         * @param colName 单元格表头名称
         * @return  单元格操作对象
         */
        public ColModel<T> col(String colName) {
            ColModel<T> colModel = new ColModel<>(this);
            colModel.colName = colName;
            this.cols.add(colModel);
            return colModel;
        }

        /**
         * 指定一个单元格
         * @param colIndex 单元格下标
         * @param colName 单元格表头名称
         * @return  单元格操作对象
         */
        public ColModel<T> col(int colIndex, String colName) {
            ColModel<T> colModel = new ColModel<>(this);
            colModel.colName = colName;
            colModel.colIndex = colIndex;
            this.cols.add(colModel);
            return colModel;
        }

        /**
         * 开始读取
         * @return  读取对象的集合
         */
        public List<T> read() {
            List<T> list = new ArrayList<>();
            FileInputStream fis = null;
            try {
                File file = new File(excelPath);
                if (!file.exists()) {
                    throw new FileNotFoundException("文件未找到：" + excelPath);
                }
                fis = new FileInputStream(file);
                Workbook workbook;
                if (isExcel2003) {
                    workbook = new HSSFWorkbook(fis);
                } else {
                    workbook = new XSSFWorkbook(fis);
                }
                Sheet sheet;
                if (this.sheetIndex != -1) {
                    if (this.sheetIndex < 0 || workbook.getNumberOfSheets() <= this.sheetIndex) {
                        throw new ArrayIndexOutOfBoundsException("Sheet页不存在：" + sheetIndex);
                    }
                    sheet = workbook.getSheetAt(this.sheetIndex);
                } else {
                    if (this.sheetName == null || this.sheetName.isEmpty()) {
                        throw new NullPointerException("无法定位Sheet页");
                    }
                    sheet = workbook.getSheet(this.sheetName);
                }
                int rowNum = sheet.getPhysicalNumberOfRows();
                Map<String, Integer> colIndexMap;
                if (titleRow != -1) {
                    Row title = sheet.getRow(titleRow);
                    colIndexMap = cols.stream().collect(Collectors.toMap(t->t.uuid, col -> {
                        if (col.customValue) {
                            return -1;
                        }
                        if (col.colIndex != -1) {
                            return col.colIndex;
                        }
                        int cellNum = title.getPhysicalNumberOfCells();
                        for (int index = 0; index < cellNum; index++) {
                            Cell cell = title.getCell(index);
                            if (Objects.deepEquals(getValue(cell, col), col.colName)) {
                                return cell.getColumnIndex();
                            }
                        }
                        return -1;
                    }));
                } else {
                    colIndexMap = new HashMap<>();
                    for (ColModel<T> col : cols) {
                        if (col.customValue) {
                            continue;
                        }
                        if (col.colIndex == -1) {
                            throw new NullPointerException("无法确定的列：" + col.colName);
                        }
                        colIndexMap.put(col.uuid, col.colIndex);
                    }
                }
                for (int index = 0; index < rowNum; index++) {
                    if (index == titleRow) {
                        continue;
                    }
                    Row row = sheet.getRow(index);
                    T bean = supplier.get();
                    for (ColModel<T> col : cols) {
                        if (col.customValue) {
                            col.setter.accept(bean, col.value.get());
                            continue;
                        }
                        Integer colIndex = colIndexMap.get(col.uuid);
                        if (colIndex == -1) {
                            continue;
                        }
                        Cell cell = row.getCell(colIndex);
                        col.setter.accept(bean, getValue(cell, col));
                    }
                    list.add(bean);
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (fis != null) {
                    try {
                        fis.close();
                    } catch (Exception ignored){
                    }
                }
            }
            return list;
        }

        /**
         * 数据写出为excel
         * @param data  数据
         * @return  是否写出成功
         */
        public boolean write(List<T> data) {
            FileOutputStream fos = null;
            try {
                Workbook workbook;
                if (isExcel2003) {
                    workbook = new HSSFWorkbook();
                } else {
                    workbook = new XSSFWorkbook();
                }
                Sheet sheet;
                if (sheetName != null) {
                    sheet = workbook.createSheet(sheetName);
                } else if (sheetIndex != -1) {
                    sheet = workbook.createSheet("Sheet" + sheetIndex);
                } else {
                    sheet = workbook.createSheet();
                }
                int maxIndex = cols.stream().mapToInt(col -> col.colIndex).max().orElse(-1);
                maxIndex = Math.max(maxIndex, cols.size());
                List<ColModel<T>> collect = cols.stream().filter(col -> col.colIndex == -1).collect(Collectors.toList());
                if (collect.size() > 0) {
                    for (int colIndex = 0; colIndex < maxIndex; colIndex++) {
                        int finalColIndex = colIndex;
                        List<ColModel<T>> models = cols.stream().filter(col -> col.colIndex == finalColIndex).collect(Collectors.toList());
                        if (models.size() == 0) {
                            collect.remove(0).colIndex = colIndex;
                        }
                        else if (models.size() > 1) {
                            models.remove(0);
                            collect.addAll(models);
                        }
                    }
                }
                if (titleRow != -1) {
                    Row row = sheet.createRow(titleRow);
                    for (ColModel<T> col : cols) {
                        Cell cell;
                        if (col.colIndex != -1) {
                            cell = row.createCell(col.colIndex);
                        } else {
                            continue;
                        }
                        if (col.fontStyle != null) {
                            col.fontStyle.setStyle(cell);
                        }
                        cell.setCellValue(col.colName);
                    }
                }
                int index = 0;
                for (T datum : data) {
                    if (titleRow == index) {
                        index ++;
                        continue;
                    }
                    Row row = sheet.createRow(index ++);
                    for (ColModel<T> col : cols) {
                        Cell cell;
                        if (col.colIndex != -1) {
                            cell = row.createCell(col.colIndex);
                        } else {
                            continue;
                        }
                        if (col.fontStyle != null) {
                            col.fontStyle.setStyle(cell);
                        }
                        if (col.customValue) {
                            cell.setCellValue(col.value.get());
                        } else {
                            Object value = col.getter.apply(datum);
                            if (col.dateFormat != null && !col.dateFormat.isEmpty() && value instanceof Date) {
                                SimpleDateFormat sdf = new SimpleDateFormat(col.dateFormat);
                                value = sdf.format(value);
                            }
                            cell.setCellValue(String.valueOf(value));
                        }
                    }
                }
                fos = new FileOutputStream(excelPath);
                workbook.write(fos);
                return true;
            } catch (Exception e) {
                e.printStackTrace();
                return false;
            } finally {
                if(fos != null) {
                    try {
                        fos.close();
                    } catch (Exception ignored){
                    }
                }
            }
        }

        /**
         * 从excel中将数据转换为Java类型
         * @param cell      单元格
         * @param cellModel 单元格操作对象
         * @return  数据
         */
        private String getValue(Cell cell, ColModel<T> cellModel) {
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        String format;
                        if (cellModel.dateFormat == null) {
                            format = cell.getCellStyle().getDataFormatString();
                        } else {
                            format = cellModel.dateFormat;
                        }
                        SimpleDateFormat sdf = new SimpleDateFormat(format);
                        return sdf.format(cell.getDateCellValue());
                    }
                    DecimalFormat df = new DecimalFormat("0");
                    return df.format(cell.getNumericCellValue());
                case HSSFCell.CELL_TYPE_STRING: // 字符串
                    return cell.getStringCellValue();
                case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                    return cell.getBooleanCellValue() + "";
                case HSSFCell.CELL_TYPE_FORMULA: // 公式
                    return cell.getCellFormula() + "";
                case HSSFCell.CELL_TYPE_BLANK: // 空值
                case HSSFCell.CELL_TYPE_ERROR: // 故障
                default:
                    return null;
            }
        }
    }


    public class ColModel<T> {

        private final String uuid;

        private final SheetModel<T> sheetModel;

        private int colIndex = -1;

        private String colName;

        private BiConsumer<T, String> setter;

        private Function<T, Object> getter;

        private boolean customValue;

        private Supplier<String> value;

        private String dateFormat;

        private FontStyle fontStyle;

        private ColModel(SheetModel<T> sheetModel) {
            this.sheetModel = sheetModel;
            this.uuid = UUID.randomUUID().toString();
        }

        /**
         * 指定一个单元格
         * @param colIndex  单元格下标
         * @param colName   单元格表头名称
         * @return  单元格操作对象
         */
        public ColModel<T> col(int colIndex, String colName) {
            return sheetModel.col(colIndex, colName);
        }

        /**
         * 指定一个单元格
         * @param colName   单元格表头名称
         * @return  单元格操作对象
         */
        public ColModel<T> col(String colName) {
            return sheetModel.col(colName);
        }

        /**
         * 指定一个单元格
         * @param colIndex  单元格下标
         * @return  单元格操作对象
         */
        public ColModel<T> col(int colIndex) {
            return sheetModel.col(colIndex);
        }

        /**
         * 读取使用！！！
         * 指定数据读取后，存储的地方
         * @param setter    excel列对应的字段set方法
         * @return  单元格处理对象
         */
        public ColModel<T> to(BiConsumer<T, String> setter) {
            this.setter = setter;
            return this;
        }

        /**
         * 写出使用！！！
         * 指定excel列存储的数据获取方式
         * @param getter    excel列对应的字段get方法
         * @return  单元格操作对象
         */
        public ColModel<T> data(Function<T, Object> getter) {
            this.getter = getter;
            return this;
        }

        /**
         * 调用此方法，
         * 对于excel读取：将放弃从单元格获取数据，转为从此方法中设定值取
         * 对于excel写出：将放弃从传入的集合中获取数据，转为使用此方法获取设定值
         * @param value 默认值表达式
         * @return  单元格操作对象
         */
        public ColModel<T> value(Supplier<String> value) {
            this.customValue = true;
            this.value = value;
            return this;
        }

        /**
         * 读取时，若数据是Date类型，会按照此处设置的格式进行读取。
         * 写出时，若数据是Date类型，会按照此处设置的格式进行写出。
         * @param dateFormat    时间格式
         * @return  单元格操作对象
         */
        public ColModel<T> format(String dateFormat) {
            this.dateFormat = dateFormat;
            return this;
        }

        /**
         * 开始读取
         * @return  读取对象的集合
         */
        public List<T> read() {
            return sheetModel.read();
        }

        /**
         * 数据写出为excel
         * @param data  数据
         * @return  是否写出成功
         */
        public boolean write(List<T> data) {
            return sheetModel.write(data);
        }

        /**
         * 设置单元格宽度（默认为自适应列宽）
         * @param width 宽度（与前端的px接近）
         * @return  单元格操作对象
         */
        public ColModel<T> width(int width) {
            if (fontStyle == null) {
                fontStyle = new FontStyle();
            }
            this.fontStyle.width = (int) (width * 28.57);
            return this;
        }

        /**
         * 设置单元格高度
         * @param height    高度（与前端的px接近）
         * @return 单元格操作对象
         */
        public ColModel<T> height(int height) {
            if (fontStyle == null) {
                fontStyle = new FontStyle();
            }
            this.fontStyle.height = (int) (height * 28.57);
            return this;
        }

        /**
         * 设置字体
         * @param name  字体名
         * @return  单元格操作对象
         */
        public ColModel<T> fontName(String name) {
            if (fontStyle == null) {
                fontStyle = new FontStyle();
            }
            this.fontStyle.fontName = name;
            return this;
        }

        /**
         * 设置字体加粗
         * @return  单元格操作对象
         */
        public ColModel<T> fontBold() {
            if (fontStyle == null) {
                fontStyle = new FontStyle();
            }
            this.fontStyle.fontBold = true;
            return this;
        }

        /**
         * 设置字体颜色
         * @param color 字体颜色（应传入Font对象中定义的颜色，例：Font.COLOR_RED）
         * @return  单元格操作对象
         */
        public ColModel<T> fontColor(short color) {
            if (fontStyle == null) {
                fontStyle = new FontStyle();
            }
            this.fontStyle.fontColor = color;
            return this;
        }

        /**
         * 设置字体大小
         * @param size  字体大小
         * @return  单元格操作对象
         */
        public ColModel<T> fontSize(int size) {
            if (fontStyle == null) {
                fontStyle = new FontStyle();
            }
            this.fontStyle.fontSize = size;
            return this;
        }

        /**
         * 设置字体为斜体
         * @return  单元格操作对象
         */
        public ColModel<T> italic() {
            if (fontStyle == null) {
                fontStyle = new FontStyle();
            }
            this.fontStyle.italic = true;
            return this;
        }

        /**
         * 设置背景色
         * @param color  背景颜色（应传入Font对象中定义的颜色，例：Font.COLOR_RED）
         * @return  单元格操作对象
         */
        public ColModel<T> backGroundColor(short color) {
            if (fontStyle == null) {
                fontStyle = new FontStyle();
            }
            this.fontStyle.backGroundColor = color;
            return this;
        }

        /**
         * 设置居中（上下居中、左右居中）
         * @return  单元格操作对象
         */
        public ColModel<T> center() {
            if (fontStyle == null) {
                fontStyle = new FontStyle();
            }
            this.fontStyle.center = true;
            return this;
        }

        /**
         * 设置自动换行
         * @return  单元格操作对象
         */
        public ColModel<T> wrapText() {
            if (fontStyle == null) {
                fontStyle = new FontStyle();
            }
            this.fontStyle.wrapText = true;
            return this;
        }

        /**
         * 设置边框
         * @param top       上边框颜色
         * @param right     右边框颜色
         * @param bottom    下边框颜色
         * @param left      左边框颜色
         * @return  单元格操作对象
         */
        public ColModel<T> border(HSSFColor top, HSSFColor right, HSSFColor bottom, HSSFColor left) {
            if (fontStyle == null) {
                fontStyle = new FontStyle();
            }
            this.fontStyle.border = new HSSFColor[]{top, right, bottom, left};
            return this;
        }

    }

    private static class FontStyle {
        /**
         * 宽
         */
        private int width;

        /**
         * 高
         */
        private int height;

        /**
         * 字体
         */
        private String fontName;

        /**
         * 加粗
         */
        private boolean fontBold;

        /**
         * 字体颜色
         */
        private Short fontColor;

        /**
         * 字体大小
         */
        private int fontSize;

        private boolean wrapText;
        /**
         * 斜体
         */
        private boolean italic;

        /**
         * 背景颜色
         */
        private Short backGroundColor;

        /**
         * 是否居中
         */
        private boolean center;

        /**
         * 边框（上、右、下、左）
         */
        private HSSFColor[] border;

        private void setStyle(Cell cell) {
            int index = cell.getColumnIndex();
            Row row = cell.getRow();
            Sheet sheet = row.getSheet();
            Workbook workbook = sheet.getWorkbook();
            CellStyle cellStyle = workbook.createCellStyle();
            Font font = workbook.createFont();
            if (width > 0) {
                sheet.setColumnWidth(cell.getColumnIndex(), width);
            } else {
                sheet.autoSizeColumn(index, true);
            }
            if(height > 0) {
                row.setHeightInPoints(height);
            }
            if(fontName != null) {
                font.setFontName(fontName);
            }
            if(fontBold) {
                font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            }
            if(fontColor != null) {
                font.setColor(fontColor);
            }
            if(fontSize > 0) {
                font.setFontHeightInPoints((short) fontSize);
            }
            if(wrapText) {
                cellStyle.setWrapText(true);
            }
            if(italic) {
                font.setItalic(true);
            }
            if(backGroundColor != null) {
                cellStyle.setFillForegroundColor(backGroundColor);// 设置背景色
                cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            }
            if(center) {
                cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
                cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            }
            if(border != null) {
                cellStyle.setTopBorderColor(border[0].getIndex());
                cellStyle.setRightBorderColor(border[1].getIndex());
                cellStyle.setBottomBorderColor(border[2].getIndex());
                cellStyle.setLeftBorderColor(border[3].getIndex());
            }
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
        }

    }

}

• Excel.java（excel注解）

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface Excel {

    /**
     * sheet页名称
     */
    String sheetName() default "";

    /**
     * sheet页码（从1开始）
     */
    int sheetIndex() default 0;

    /**
     * 表头行号（从1开始）
     */
    int titleRow() default -1;

}

• ExcelCol.java（excel列注解）

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.FIELD)
public @interface ExcelCol {

    /**
     * 列名
     */
    String titleName() default "";

    /**
     * 列所在位置（从1开始）
     */
    int titleIndex() default -1;

    /**
     * 列中数据对应的类型
     */
    ExcelColType type() default ExcelColType.BASE;


    /**
     * 当type设置为ExcelColType.LIST时，表示集合中数据的类型；
     * 其他type类型，不需要设置此字段
     */
    Class<?> valueType() default Object.class;

    /**
     * 当type设置为ExcelColType.BASE，且数据类型为Date时，将使用此处设置的格式进行转换
     * 其他type类型/其他类型的数据，不需要设置此字段
     * 如果是多种格式，可自动根据数据的真实格式进行匹配
     */
    String[] format() default "yyyy-MM-dd HH:mm:ss.SSS";

    /**
     * 若设置了此处，则会将数据进行拼接
     * 数据使用$value$进行代替，例：Hello $value$-$value$  ==解析后==> Hello World-World
     * 注意：若设置了此处，当前注解的type只能为ExcelColType.BASE，且字段类型只能为String
     * 此注解会同时影响读取和写出
     */
    String append() default "";

    /**
     * 若设置了此处，则会将数据进行拆分
     * 当type设置为ExcelColType.LIST，且数据类型为List时，将使用此处设置进行分割
     */
    String split() default "";


    /**
     * 宽（与前端的px接近）
     */
    int width() default -1;

    /**
     * 高（与前端的px接近）
     */
    int height() default -1;

    /**
     * 字体
     */
    String fontName() default "";

    /**
     * 加粗
     */
    boolean fontBold() default false;

    /**
     * 字体颜色
     */
    int fontColor() default -1;

    /**
     * 字体大小
     */
    int fontSize() default -1;

    /**
     * 自动换行
     */
    boolean wrapText() default false;
    /**
     * 斜体
     */
    boolean italic() default false;

    /**
     * 背景颜色
     */
    int backGroundColor() default -1;

    /**
     * 是否居中
     */
    boolean center() default false;

    /**
     * 边框（上、右、下、左）
     */
    int[] border() default {};
}

• ExcelColType.java（字段类型枚举类）

public enum ExcelColType {
    /**
     * 基础类型（八大基础类型及其包装类，String，Date）
     */
    BASE,

    /**
     * List（使用集合，必须定义注解的split属性，否则无效）
     */
    LIST,

    /**
     * UUID，带横杠
     */
    UUID,

    /**
     * UUID，去横杠
     */
    UUID32,

    /**
     * 时间戳
     */
    TIME_MILLIS,
}