使用POI 读和写excel的工具类

package util.excel;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.*;
import java.util.stream.Collectors;

/**
 * Excel 读和写的工具类  暂时不支持多层表头
 *
 */
public class ExcelUtil {

    private static final String DATE = "date";
    private static final String STRING = "string";
    private static final String LONG = "long";
    private static final String INT = "int";
    private static final String TYPE_LIST = "typeList";
    private static final int PAGE_SIZE = 60000;

    private static void checkExcel(Row row, List<ExcelClass> classList) throws BusiException {

        int j = 0;
        for (Cell cell : row) {
            String cellValue = cell.getStringCellValue();

            int finalJ = j;
            Optional op = classList.stream().filter(p -> p.getColNo() == finalJ && p.getExcelExport().title().equals(cellValue)).findFirst();
            if (!op.isPresent()) {
                throw new BusiException();
            }
            j++;
        }
    }

    public static <T> List<T> readExcel2007(InputStream inputStream, Class<T> cls, boolean check) throws Exception {
        return readExcel(new XSSFWorkbook(inputStream), cls, check);
    }

    public static <T> List<T> readExcel2003(InputStream inputStream, Class<T> cls, boolean check) throws Exception {
        return readExcel(new HSSFWorkbook(inputStream), cls, check);
    }

    /**
     * 读Excel数据
     *
     * @param workbook Excel
     * @param cls      Excel配置对象
     * @param check    true校验是否跟导出的Excel的列完全一样 false不校验
     * @return java.util.List<T>
     * @throws Exception
     * @author lijiwang6407001878
     * @date 16:16 2019/12/17
     **/
    private static <T> List<T> readExcel(Workbook workbook, Class<T> cls, boolean check) throws Exception {
        List<T> tList = new ArrayList<>();
        List<ExcelClass> classList = getMethods(cls);

        int beginRow = 3;
        int i = 0;
        for (Sheet sheet : workbook) {

            if (workbook.isSheetHidden(i++)) {
                continue;
            }

            for (Row row : sheet) {
                if (check) {
                    checkExcel(sheet.getRow(0), classList);
                } else {
                    beginRow--;
                }
                if (row.getRowNum() < beginRow - 1) {
                    continue;
                }

                T t = cls.newInstance();
                int j = 0;
                for (ExcelClass ec : classList) {

                    if (j != ec.getColNo()) {
                        throw new RuntimeException();
                    }

                    Cell cell = row.getCell(j++);

                    setInstanceValue(t, ec, cell);
                }

                tList.add(t);
            }
        }
        return tList;
    }

    private static <T> void setInstanceValue(T t, ExcelClass ec, Cell cell)
            throws IllegalAccessException, InvocationTargetException {

        if (cell == null || StringUtils.isEmpty(cell.toString())) {
            return;
        }
        ExcelExport ee = ec.getExcelExport();
        String type = ee.type();
        String format = ee.format();

        Object value;
        int cellType = cell.getCellType();
        if (Cell.CELL_TYPE_NUMERIC == cellType) {
            switch (type) {
                case DATE:
                    value = DateFormatUtils.format(cell.getDateCellValue(), format);
                    break;
                case STRING:
                    double valueD = cell.getNumericCellValue();
                    value = double2String(valueD);
                    break;
                case LONG:
                    value = (long) cell.getNumericCellValue();
                    break;
                case INT:
                    value = (int) cell.getNumericCellValue();
                    break;
                default:
                    value = cell.getNumericCellValue();
            }
        } else if (Cell.CELL_TYPE_BOOLEAN == cellType) {
            value = cell.getBooleanCellValue();
        } else {
            value = cell.getStringCellValue();
        }
        Method method = ec.getMethodSet();
        method.invoke(t, value);
    }

    private static String double2String(double valueD) {
        String value;
        String s = String.valueOf(valueD);
        int index = s.lastIndexOf(".0");
        if (index > 0) {
            value = s.substring(0, index);
        } else {
            value = s;
        }
        return value;
    }

    public static <T> Workbook createExcel2007(List<T> data, String lang, boolean isHiddenRow) throws Exception {
        return createExcel2007(data, null, lang, isHiddenRow);
    }

    public static <T> Workbook createExcel2007(List<T> data, Map<String, List<String>> map,
                                               String lang, boolean isHiddenRow) throws Exception {
        Workbook workbook = new XSSFWorkbook();
        if (CollectionUtils.isEmpty(data)) {
            return workbook;
        }
        return createExcel(workbook, data, map, lang, isHiddenRow);
    }

    public static <T> Workbook createExcel2003(List<T> data, String lang, boolean isHiddenRow) throws Exception {
        return createExcel2007(data, null, lang, isHiddenRow);
    }

    public static <T> Workbook createExcel2003(List<T> data, Map<String, List<String>> map,
                                               String lang, boolean isHiddenRow) throws Exception {
        Workbook workbook = new HSSFWorkbook();
        if (CollectionUtils.isEmpty(data)) {
            return workbook;
        }
        return createExcel(workbook, data, map, lang, isHiddenRow);
    }

    /**
     * 生成Excel表格
     *
     * @param data        数据源  T是数据实体  请通过注解ExcelExport配置Excel单元格
     * @param map         如果有下拉列表，放入map中
     * @param isHiddenRow 隐藏一行表头编码在第一行，用于导入验证Excel格式
     * @return org.apache.poi.ss.usermodel.Workbook
     * @throws Exception
     * @author lijiwang6407001878
     * @date 11:22 2019/12/10
     **/
    private static <T> Workbook createExcel(Workbook workbook, List<T> data, Map<String, List<String>> map,
                                            String lang, boolean isHiddenRow) throws Exception {

        int dataSize = data.size();
        T t = data.get(0);
        Class cls = t.getClass();
        List<ExcelClass> classList = getMethods(cls);

        int pageTotal = getPageTotal(dataSize);
        if (map != null && !map.isEmpty()) {
            createListSheet(workbook, map);
        }

        for (int i = 0; i < pageTotal; i++) {
            List<T> pageList = data.stream().skip(i * PAGE_SIZE).limit(PAGE_SIZE).collect(Collectors.toList());
            Sheet sheet = workbook.createSheet();
            sheet.setDefaultColumnWidth(13);
            int pageDataSize = pageList.size();

            int rowNo = 0;
            if (isHiddenRow) {
                createHideRow(sheet, classList, rowNo++);
            }
            createHeader(workbook, sheet, classList, lang, rowNo++);
            createBody(workbook, sheet, classList, pageList, rowNo);
            if (map != null && !map.isEmpty()) {
                setListSheet(workbook, sheet, pageDataSize, classList, map, rowNo);
            }
        }

        return workbook;
    }

    private static void createHideRow(Sheet sheet, List<ExcelClass> classList, int rowNo) {
        Row row = sheet.createRow(rowNo);
        row.setZeroHeight(true);

        int i = 0;
        for (ExcelClass ec : classList) {

            ExcelExport excelExport = ec.getExcelExport();

            Cell cell = row.createCell(i);
            cell.setCellValue(excelExport.title());
            i++;
        }

    }

    private static int getPageTotal(int dataSize) {
        int pageTotal = 0;
        if (dataSize == 0) {
            return pageTotal;
        }

        if (dataSize % PAGE_SIZE == 0) {
            pageTotal = dataSize / PAGE_SIZE;
        } else {
            pageTotal = dataSize / PAGE_SIZE + 1;
        }
        return pageTotal;
    }

    private static void createListSheet(Workbook workbook, Map<String, List<String>> map) {
        Sheet sheet = workbook.createSheet(TYPE_LIST);

        List<Row> rowList = new ArrayList<>();
        //x 是列 y是行
        int x = 0;

        for (Map.Entry<String, List<String>> entry : map.entrySet()) {

            List<String> list = entry.getValue();

            for (int z = 0; z < list.size(); z++) {
                if (z < rowList.size()) {
                    continue;
                }
                Row row = sheet.createRow(z);
                rowList.add(row);
            }

            int y = 0;
            for (String cellVal : list) {
                Row row = rowList.get(y);
                Cell cell = row.createCell(x);
                cell.setCellValue(cellVal);
                y++;
            }

            x++;
        }

        workbook.setSheetHidden(workbook.getSheetIndex(TYPE_LIST), Workbook.SHEET_STATE_HIDDEN);
    }

    private static void setListSheet(Workbook workbook, Sheet sheet, int dataSize,
                                     List<ExcelClass> classList, Map<String, List<String>> map, int rowNo) {
        int x = 0;
        //65表示A
        int a = 65;
        for (Map.Entry<String, List<String>> entry : map.entrySet()) {
            String key = entry.getKey();
            List<String> list = entry.getValue();

            Optional<ExcelClass> op = classList.stream().filter(p -> key.equals(p.getExcelExport().selectKey())).findFirst();
            if (op.isPresent()) {
                ExcelClass ec = op.get();
                char c = (char) (a + x);
                sheet.addValidationData(setDataValidation(workbook,
                        TYPE_LIST + "!$" + c + "$1:$" + c + "$" + list.size(),
                        rowNo, ec.getColNo(), dataSize + rowNo - 1, ec.getColNo()));
            }
            x++;
        }
    }

    private static DataValidation setDataValidation(Workbook wb, String listFormula,
                                                    int firstRow, int firstCol, int endRow, int endCol) {
        Sheet sheet = wb.getSheet(TYPE_LIST);
        if (sheet == null) {
            return null;
        }
        DataValidationHelper helper = sheet.getDataValidationHelper();
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);

        DataValidationConstraint formulaListConstraint = helper.createFormulaListConstraint(listFormula);
        return helper.createValidation(formulaListConstraint, regions);
    }

    private static <T> void createBody(Workbook workbook, Sheet sheet,
                                       List<ExcelClass> classList, List<T> data, int rowNo)
            throws IllegalAccessException, InvocationTargetException {

        Map<String, CellStyle> styleMap = new HashMap<>(5);

        for (int x = 0; x < data.size(); x++) {
            Row row = sheet.createRow(x + rowNo);
            T d = data.get(x);
            int y = 0;
            for (ExcelClass ec : classList) {

                ExcelExport ee = ec.getExcelExport();
                Method method = ec.getMethodGet();
                Object value = method.invoke(d);

                String format = ee.format();
                short align = ee.bodyAlign();

                CellStyle cellStyle = styleMap.get(format + align);
                if (cellStyle == null) {
                    CellStyle bodyStyle = workbook.createCellStyle();
                    DataFormat dataFormat = workbook.createDataFormat();
                    if (StringUtils.isNotEmpty(format)) {
                        bodyStyle.setDataFormat(dataFormat.getFormat(format));
                    }
                    bodyStyle.setAlignment(align);
                    styleMap.put(format + align, bodyStyle);
                    cellStyle = bodyStyle;
                }

                Cell cell = row.createCell(y++);
                cell.setCellStyle(cellStyle);

                setCellValue(value, cell);
            }
        }
    }

    private static void setCellValue(Object value, Cell cell) {
        if (value == null) {
            return;
        }
        if (value instanceof Date) {
            Date date = (Date) value;
            cell.setCellValue(date);
        } else if (value instanceof Double) {
            cell.setCellValue((Double) value);
        } else if (value instanceof Integer) {
            cell.setCellValue((Integer) value);
        } else if (value instanceof Long) {
            cell.setCellValue((Long) value);
        } else if (value instanceof Byte) {
            cell.setCellValue((Byte) value);
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        } else {
            cell.setCellValue(value.toString());
        }
    }

    private static void createHeader(Workbook workbook, Sheet sheet, List<ExcelClass> classList, String lang, int rowNo) {
        lang = EpmsMessageUtils.convert2ClientLanguage(lang);
        Locale currentLocale = Locale.getDefault();
        ResourceBundle myResources = ResourceBundle.getBundle("messages_" + lang, currentLocale);

        Row row = sheet.createRow(rowNo);
        CellStyle titleStyle = workbook.createCellStyle();
        Font titleFont = workbook.createFont();
        titleFont.setBold(true);
        titleStyle.setFont(titleFont);

        int i = 0;
        for (ExcelClass ec : classList) {

            ExcelExport excelExport = ec.getExcelExport();
            int width = excelExport.width();
            if (width != 13) {
                sheet.setColumnWidth(i, width * 256);
            }
            titleStyle.setAlignment(excelExport.titleAlign());

            Cell cell = row.createCell(i);
            cell.setCellStyle(titleStyle);

            String title;
            if (excelExport.columnHide()) {
                sheet.setColumnHidden(i, true);
                title = excelExport.title();
            } else {
                title = myResources.getString(excelExport.title());
            }
            cell.setCellValue(title);
            i++;
        }
    }

    public static void writeFile(String path, Workbook workbook) {
        try (FileOutputStream outputStream = new FileOutputStream(path)) {
            workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取注解的方法
     *
     * @param cls
     * @return List<ExcelClass>
     * @throws Exception
     * @author lijiwang6407001878
     * @date 16:24 2019/12/10
     **/
    private static <T> List<ExcelClass> getMethods(Class<T> cls) throws NoSuchMethodException {
        List<ExcelClass> list = new ArrayList<>();
        Field[] fields = cls.getDeclaredFields();
        int colNo = 0;
        for (Field field : fields) {
            ExcelExport excelExport = field.getDeclaredAnnotation(ExcelExport.class);
            if (excelExport == null) {
                continue;
            }

            Class param = field.getType();
            String name = field.getName();
            String tail = name.substring(0, 1).toUpperCase() + name.substring(1);
            String getMethodName = "get" + tail;
            String setMethodName = "set" + tail;

            Method methodGet = cls.getMethod(getMethodName);
            Method methodSet = cls.getMethod(setMethodName, param);

            ExcelClass ec = new ExcelClass();
            ec.setColNo(colNo++);
            ec.setExcelExport(excelExport);
            ec.setMethodGet(methodGet);
            ec.setMethodSet(methodSet);
            list.add(ec);
        }
        return list;
    }
}

下面是使用注解类对Excel的配置

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import java.lang.annotation.*;

@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface ExcelExport {

    /*** 标题名称 可以配置 多语言的KEY */
    String title() default "";

    /*** 表头单元格位置 HSSFCellStyle.ALIGN_LEFT 居左  默认HSSFCellStyle.ALIGN_CENTER 居中 HSSFCellStyle.ALIGN_RIGHT*/
    short titleAlign() default HSSFCellStyle.ALIGN_CENTER;

    /*** 数据单元格位置 默认HSSFCellStyle.ALIGN_LEFT 居左  HSSFCellStyle.ALIGN_CENTER 居中 HSSFCellStyle.ALIGN_RIGHT*/
    short bodyAlign() default HSSFCellStyle.ALIGN_LEFT;

    /*** 数据格式 */
    String format() default "";

    /*** excel的数据类型 date表示时间 string double 目前只支持这三种，如果是date格式必须设置 */
    String type() default "string";

    /*** Excel 下拉框 的key，数据通过 createExcel方法的map参数设置 */
    String selectKey() default "";

    /*** 隐藏列 */
    boolean columnHide() default false;

    /*** 宽度 */
    int width() default 13;
}

import java.lang.reflect.Method;

/**
 * Excel注解的信息和对应方法
 *
 * @author lijiwang6407001878
 * @date 2019/12/14 10:20
 */
public class ExcelClass {

    /*** 列号 */
    private int colNo;

    private ExcelExport excelExport;

    private Method methodGet;

    private Method methodSet;

    ...
}

测试类

public class Person {
	@ExcelExport(title = "id", columnHide = true, titleAlign=HSSFCellStyle.ALIGN_LEFT)
	private String id;
	//支持中英文的时候，title写中英文的key
	@ExcelExport(title = "Excel.column.name")
	private String name;
	@ExcelExport(title = "Excel.column.type", width=20)
	private String type;
	@ExcelExport(title = "Excel.column.age", width=15, type='int')
	private int age;
	@ExcelExport(title = "Excel.column.name.date", format = "yyyy-MM-dd", type = "date")
    private Date date;
	...setting getting	
}

public static void main(String[] args) throws Exception {
	String path="D:/ccc.xls";
	write();
	read(String path)
}
public static void write() throws Exception {
   String path = "D:/workspace/ccc.xls";

   List<Person> data = new ArrayList<>();
   int z = 1000000;
   for (int i = 0; i < z; i++) {
       Person p= new Person();
       p.setName("张三 " + i);
       p.setRegionName("深圳"+i);
       p.setType("短发");
       p.setAge(i);
       p.setDate(new Date());
       data.add(p);
   }
	//发型下拉框
   Map<String, List<String>> map = new HashMap<>(20);
   List<String> userList = Arrays.asList("短发", "长发");
   map.put("typeKey", userList);

   long start = System.currentTimeMillis();
	//支持2003版和2007版
   Workbook workbook = ExcelUtil.createExcel2003(data, map, "zh", false);
   ExcelUtil.writeFile(path, workbook);

   long end = System.currentTimeMillis();
   System.out.println((end - start) / 1000);

   workbook.close();
}

private static void read(String path) throws Exception {
   FileInputStream fis = new FileInputStream(new File(path));

   long start = System.currentTimeMillis();
	//支持2003版和2007版
   List<ScheduleActivityExcel> list = ExcelUtil.readExcel2003(fis, ScheduleActivityExcel.class, true);
   System.out.println(list.size());

   long end = System.currentTimeMillis();
   System.out.println((end - start) / 1000);
   fis.close();
}