POI报表工具

首先先定义一个注解

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.METHOD, ElementType.FIELD, ElementType.PARAMETER})
public @interface ExcelProperty {
    /**
     * 列索引
     * @return
     */
    String index() default "";
    /**
     * 列名
     * @return
     */
    String title() default "";
    /**
     * 格式
     * @return
     */
    String align() default "center";
    /**
     * 日期格式化
     * @return
     */
    String dateFormat() default "yyyy/MM/dd";
    /**
     * 数字格式化
     * @return
     */
    String decimalFormat() default "#.#";
    
}

然后创建一个用户表格类,lombok.Data用于get和set封装的注解。

import com.bjlytr.pe.portal.utils.ExcelProperty;
import lombok.Data;

@Data
public class UserExcel {
    @ExcelProperty(index = "A", title = "用户名")
    private String name;
   @ExcelProperty(index = "B", dateFormat = "dd/MM/yyyy", title = "生日 日/月/年")
   private String birthday;
     @ExcelProperty(index = "C",  title = "描述")
    private String remark;
}

提供一个表格的工具类

import jodd.util.StringUtil;
import net.logstash.logback.encoder.org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.validation.ConstraintViolation;
import javax.validation.Validation;
import javax.validation.Validator;
import javax.validation.groups.Default;
import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.util.*;

public class ExcelUtils<T> {

    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtils.class);
    private static final Validator validator = Validation.buildDefaultValidatorFactory().getValidator();
 /**
     * 记录当前行不符javax.validation.Validation合校验规则的信息
     */
    private static final String VALIDATION_ERROR_MSG = "validateErrMg";

    /**
     * 指定路径输出   Excel2003
     * @param data
     * @param outFile
     * @param <T>
     */
    public static <T> void outExcelFile(List<T> data, File outFile) {
        // 创建workbook
        try (HSSFWorkbook workbook = new HSSFWorkbook();
             FileOutputStream fileOutputStream = new FileOutputStream(outFile)) {
            // 创建sheet
            Sheet sheet = workbook.createSheet("sheet");

            Class clazz = data.get(0).getClass();
            final Map<Field, MetaData> metaDataMap = getFieldMetaDataMap(clazz, workbook);
            // 创建表头行
            Row row = sheet.createRow(0);
            MetaData metaData;
            for (Map.Entry<Field, MetaData> entry : metaDataMap.entrySet()) {
                metaData = entry.getValue();
                final String fieldValue = metaData.getTitle();
                creCell(row, metaData.getColumnIndex(), fieldValue, metaData.getStyle());
            }

            // 行索引  因为表头已经设置，索引行索引从1开始
            int rowIndex = 1;

            for (Object obj : data) {
                // 创建新行，索引加1,为创建下一行做准备
                row = sheet.createRow(rowIndex++);
                for (Map.Entry<Field, MetaData> entry : metaDataMap.entrySet()) {
                    metaData = entry.getValue();
                    final String fieldValue = metaData.getFieldValue(obj, entry.getKey());
                    creCell(row, metaData.getColumnIndex(), fieldValue, metaData.getStyle());
                }
            }
            workbook.write(fileOutputStream);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    /**
     * 指定路径输出
     *
     * @param data
     * @param <T>
     * @templateFile 模板文件
     */
    public static <T> byte[] outExcelToBytes(List<T> data, File templateFile, int sheetIndex) {
        // 创建workbook
        try (FileInputStream in = new FileInputStream(templateFile);
             XSSFWorkbook workbook = new XSSFWorkbook(in);
             ByteArrayOutputStream os = new ByteArrayOutputStream();
        ) {
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            Class clazz = data.get(0).getClass();
            Map<Field, MetaData> metaDataMap = getFieldMetaDataMap(clazz, workbook);

            Row row;
            MetaData metaData;
            // 行索引
            int rowIndex = 1;
            for (Object obj : data) {
                // 创建新行，索引加1,为创建下一行做准备
                row = sheet.createRow(rowIndex++);
                for (Map.Entry<Field, MetaData> entry : metaDataMap.entrySet()) {
                    metaData = entry.getValue();
                    final String fieldValue = metaData.getFieldValue(obj, entry.getKey());
                    creCell(row, metaData.getColumnIndex(), fieldValue, metaData.getStyle());
                }
            }
            workbook.write(os);
            return os.toByteArray();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }


    /**
     * 指定路径读取
     *
     * @param in
     * @param startRow
     * @param clazz
     * @param <T>
     * @return
     */
    public static <T extends Object> List<T> readExcelFile(InputStream in, int startRow, Class<T> clazz) {
        if (in == null) {
            return null;
        }
        List<T> list = new ArrayList<>();
        Workbook workbook = null;
        try {
            // 得到一个工作表
            workbook = xlsx(in);
            Sheet sheet = workbook.getSheetAt(0);
            // 获取行总数
            int rows = sheet.getLastRowNum() + 1;
            Row row;
            // 获取类所有属性
            Field[] fields = clazz.getDeclaredFields();

            T obj = null;
            int coumnIndex = 0;
            Cell cell = null;
            ExcelProperty excelProperty = null;
            for (int i = startRow; i < rows; i++) {
                // 获取excel行
                row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                try {
             	     // 创建实体
		    obj = clazz.newInstance();
                     // 处理空行
                    boolean isEmptyCell = true;
                    for (Field f : fields) {
                        // 设置属性可访问
                        f.setAccessible(true);
                        // 判断是否是注解
                        if (f.isAnnotationPresent(ExcelProperty.class)) {
                            // 获取注解
                            excelProperty = f.getAnnotation(ExcelProperty.class);
                            // 日期格式
                            String dateFormat = excelProperty.dateFormat();
                            // 数字格式
                            String decimalFormat = excelProperty.decimalFormat();
                            // 获取列索引
                            coumnIndex = excelColStrToNum(excelProperty.index());
                            // 获取单元格
                            cell = row.getCell(coumnIndex);
                            // 设置属性
                            String cellValue = getCellValue(workbook, cell, dateFormat, decimalFormat);
                            if (StringUtil.isNotBlank(cellValue)) {
                                isEmptyCell = false;
                                setFieldValue(obj, f, cellValue, dateFormat, decimalFormat);
                            }
                        }
                    }
                    final Set<ConstraintViolation<T>> validate = validator.validate(obj, Default.class);
                    if (!validate.isEmpty()) {
                        StringBuilder msgBuf = new StringBuilder(1000);
                        validate.forEach(item -> msgBuf.append(item.getMessage()).append("|"));
                        final Field field = clazz.getDeclaredField(VALIDATION_ERROR_MSG);
                        field.setAccessible(true);
                        field.set(obj, msgBuf.toString());
                    }
                    // 添加到集合中
                    if (!isEmptyCell) {
                        list.add(obj);
                    }
                } catch (InstantiationException e1) {
                    e1.printStackTrace();
                } catch (IllegalAccessException e1) {
                    e1.printStackTrace();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }


    public static class WrongDataObj {
        private int lastCellIndex;
        private Workbook workbook;
        private Sheet sheet;
        private CellStyle cellStyle;
        //缓存数字格式的cellStyle 因数字格式可能有多个所以使用map
        private Map<Short, CellStyle> numberCellStyleMap = new HashMap();

        public WrongDataObj(InputStream fileInputStream) {
            init(fileInputStream, 0, 1, null);
        }

        public WrongDataObj(InputStream fileInputStream, int sheetIndex, int titleRowIndex) {
            init(fileInputStream, sheetIndex, titleRowIndex, null);
        }

        public WrongDataObj(InputStream fileInputStream, int sheetIndex, int titleRowIndex, CellStyle cellStyle) {
            init(fileInputStream, sheetIndex, titleRowIndex, cellStyle);
        }

        /**
         * 设置错误信息到末尾的单元格，整行没有数据则不写入
         *
         * @param rowIndex
         * @param msg
         */
        public void setWrongMsg(int rowIndex, String msg) {
            Row row = sheet.getRow(rowIndex);
            if (row == null) {
                return;
            }
            Cell cell = row.createCell(lastCellIndex, CellType.STRING);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(msg);
            for (int j = 0; j < lastCellIndex + 1; j++) {
                Cell c = row.getCell(j);
                if (c == null) {
                    row.createCell(j, CellType.STRING);
                } else {
                    if (CellType.NUMERIC.equals(c.getCellTypeEnum())) {
                        short dataFormat = c.getCellStyle().getDataFormat();
                        CellStyle cellStyle1 = numberCellStyleMap.get(dataFormat);
                        if (cellStyle1 == null) {
                            //除开数据格式外其他属性使用指定的
                            cellStyle1 = workbook.createCellStyle();
                            cellStyle1.setDataFormat(dataFormat);
                            cellStyle1.setFillForegroundColor(cellStyle.getFillForegroundColor());
                            cellStyle1.setFillPattern(cellStyle.getFillPatternEnum());
                            cellStyle1.setAlignment(cellStyle.getAlignmentEnum());
                            numberCellStyleMap.put(dataFormat, cellStyle1);
                        }
                        c.setCellStyle(cellStyle1);
                    } else {
                        c.setCellStyle(cellStyle);
                    }
                }
            }
        }

        public void writeToFile(File outFile) {
            try {
                if (!outFile.getParentFile().exists()) {
                    outFile.getParentFile().mkdirs();
                }
                if (!outFile.exists()) {
                    outFile.createNewFile();
                }
                workbook.write(new FileOutputStream(outFile));
            } catch (IOException e) {
                LOGGER.error(e.getMessage());
            } finally {
                try {
                    workbook.close();
                } catch (IOException e) {
                    LOGGER.error(e.getMessage());
                }
            }
        }

        private void init(InputStream fileInputStream, int sheetIndex, int titleRowIndex, CellStyle cellStyle) {
            try {
                this.workbook = new XSSFWorkbook(fileInputStream);
                this.sheet = workbook.getSheetAt(sheetIndex);
                this.lastCellIndex = sheet.getRow(titleRowIndex).getLastCellNum();
                if (cellStyle == null) {
                    this.cellStyle = createDefaultCellStyle();
                } else {
                    this.cellStyle = cellStyle;
                }
            } catch (IOException e) {
                LOGGER.error(e.getMessage());
            }
        }

        private CellStyle createDefaultCellStyle() {
            CellStyle defaultCellStyle = workbook.createCellStyle();
            DataFormat format = workbook.createDataFormat();
            defaultCellStyle.setDataFormat(format.getFormat("@"));
            //设置背景颜色
            defaultCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
            defaultCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            return defaultCellStyle;
        }
    }

    /**
     * 写入到模板，模板中有多个sheet
     *
     * @param dataList     数据
     * @param templateFile 模板文件
     * @param startRow     数据起始行
     * @param outFile      输出文件
     * @param <T>
     */
    public static <T> void outExcelsheetListFile(List<List<T>> dataList, File templateFile, int startRow, File outFile) {
        try (FileInputStream in = new FileInputStream(templateFile);
             FileOutputStream os = new FileOutputStream(outFile);
             XSSFWorkbook workbook = new XSSFWorkbook(in)) {
            ZipSecureFile.setMinInflateRatio(-1.0d);
            XSSFSheet sheet = null;
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                sheet = workbook.getSheetAt(i);
                List<T> data = dataList.get(i);
                final Class<?> clazz = data.get(0).getClass();
                Map<Field, MetaData> map = getFieldMetaDataMap(clazz, workbook);
                MetaData metaData;
                Integer rowIndex = startRow;
                // 行索引  因为表头已经设置，索引行索引从1开始
                for (Object obj : data) {
                    // 创建新行，索引加1,为创建下一行做准备
                    Row row = sheet.createRow(rowIndex++);
                    for (Map.Entry<Field, MetaData> entry : map.entrySet()) {
                        metaData = entry.getValue();
                        final String fieldValue = metaData.getFieldValue(obj, entry.getKey());
                        creCell(row, metaData.getColumnIndex(), fieldValue, metaData.getStyle());
                    }
                }
            }
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }



    /**
     * 获取数据
     * @param clazz
     * @param workbook
     * @return
     */
    private static Map<Field, MetaData> getFieldMetaDataMap(Class<?> clazz, Workbook workbook) {
        // 获取实体所有属性
        Field[] fields = clazz.getDeclaredFields();

        ExcelProperty excelProperty;

        Map<Field, MetaData> map = new HashMap<>(fields.length);

        for (Field f : fields) {
            // 是否是注解
            if (f.isAnnotationPresent(ExcelProperty.class)) {
                // 获取注解
                excelProperty = f.getAnnotation(ExcelProperty.class);
                // 获取列索引
                int index = excelColStrToNum(excelProperty.index());
                // 列名称
                String name = excelProperty.title();
                // 日期格式
                String dateFormat = excelProperty.dateFormat();
                // 数字格式
                String decimalFormat = excelProperty.decimalFormat();

                // 对齐样式
                String align = excelProperty.align();
                CellStyle style;
                style = workbook.createCellStyle();

                if ("right".equalsIgnoreCase(align)) {
                    DataFormat format = workbook.createDataFormat();
                    style.setDataFormat(format.getFormat("@"));
                    style.setAlignment(HorizontalAlignment.RIGHT);
                } else if ("left".equalsIgnoreCase(align)) {
                    DataFormat format = workbook.createDataFormat();
                    style.setDataFormat(format.getFormat("@"));
                    style.setAlignment(HorizontalAlignment.LEFT);
                } else {
                    DataFormat format = workbook.createDataFormat();
                    style.setDataFormat(format.getFormat("@"));
                    style.setAlignment(HorizontalAlignment.CENTER);
                }

                f.setAccessible(true);

                MetaData metaData = new MetaData();
                metaData.setColumnIndex(index);
                metaData.setTitle(name);
                metaData.setDateFormat(dateFormat);
                metaData.setDecimalFormat(decimalFormat);
                metaData.setStyle(style);
                metaData.setNeedFormatter(f.getType() == BigDecimal.class || f.getType() == Date.class);
                map.put(f, metaData);
            }
        }
        return map;
    }


    /**
     * 通过对应的 get 方法获取值
     *
     * @param fieldName
     * @param obj
     * @return
     */
    private static Object getValue(Object fieldName, Object obj) {
        try {
            Class<?> aClass = obj.getClass();
            Field declaredField = aClass.getDeclaredField(fieldName.toString());
            declaredField.setAccessible(true);
            PropertyDescriptor pd = new PropertyDescriptor(declaredField.getName(), aClass);
            Method readMethod = pd.getReadMethod();
            return readMethod.invoke(obj);
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } catch (IntrospectionException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 2003
     */
    private static Workbook xls(InputStream is) {
        try {
            // 得到工作簿
            return new HSSFWorkbook(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }


    /**
     * 2007
     */
    private static Workbook xlsx(InputStream is) {
        try {
            // 得到工作簿
            return new XSSFWorkbook(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }


    /**
     * 创建单元格
     *
     * @param row
     * @param c
     * @param cellValue
     * @param style
     */
    private static void creCell(Row row, int c, String cellValue, CellStyle style) {
        Cell cell = row.createCell(c);
        cell.setCellValue(cellValue);
        cell.setCellStyle(style);
    }

    /**
     * 获取单元格值并转换为String
     *
     * @param workbook
     * @param cell
     * @param dateFormat    日期格式化
     * @param decimalFormat 数字格式化
     * @return
     */
    private static String getCellValue(Workbook workbook, Cell cell, String dateFormat, String decimalFormat) {

        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

        if (cell == null) {
            return "";
        }

        String result = "";

        switch (cell.getCellTypeEnum()) { //cell.getCellTypeEnum()
            case NUMERIC:
                // 情景一：2/12/2019 12:30 情景二：12:30 情景三：12月12日 都会识别为true 但自定义格式除外
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    result = DateFormatUtils.format(cell.getDateCellValue(), dateFormat);
                } else {
                    result = new DecimalFormat(decimalFormat).format(cell.getNumericCellValue());
                }
                break;
            case STRING:
                result = cell.getRichStringCellValue().toString();
                break;
            case BOOLEAN:
                result = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA:
                // result = cell.getCellFormula();
                result = evaluator.evaluate(cell).formatAsString();
                break;
            case ERROR:
            case _NONE:
            case BLANK:
            default:
                result = "";
                break;
        }
        return result;
    }

    /**
     * 设置字段值
     *
     * @param obj
     * @param f
     * @param cellValue
     */
    private static void setFieldValue(Object obj, Field f, String cellValue, String dateFormat, String decimalFormat) {
        try {
            if (f.getType() == int.class || f.getType() == Integer.class) {
                // f.setInt(obj, Integer.valueOf(cellValue)); // 不会自动装箱
                f.set(obj, Integer.valueOf(cellValue));
            } else if (f.getType() == Double.class || f.getType() == double.class) {
                f.set(obj, Double.valueOf(cellValue));
            } else if (f.getType() == BigDecimal.class) {
                f.set(obj, new BigDecimal(cellValue));
            } else if (f.getType() == Date.class) {
                f.set(obj, DateUtils.parseDate(cellValue, dateFormat));
            } else if (f.getType() == long.class || f.getType() == Long.class) {
                f.set(obj, Long.valueOf(cellValue));
            } else if (f.getType() == boolean.class || f.getType() == Boolean.class) {
                String[] falseArr = new String[]{"FALSE", "false", "False", "否", "No", "NO", "假", "0", "F", "f"};
                if (Arrays.asList(falseArr).contains(cellValue)) {
                    f.set(obj, false);
                } else {
                    f.set(obj, true);
                }
            } else {
                f.set(obj, cellValue);
            }
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (ParseException e) {
            e.printStackTrace();
        }
    }

    /**
     * A -> 0
     *
     * @param colStr
     * @return
     */
    public static int excelColStrToNum(String colStr) {
        int num = 0;
        int result = 0;
        int length = colStr.length();
        for (int i = 0; i < length; i++) {
            char ch = colStr.charAt(length - i - 1);
            num = (ch - 'A' + 1);
            num *= Math.pow(26, i);
            result += num;
        }
        return result - 1;
    }

    /**
     * 0 -> A
     *
     * @param columnIndex
     * @return
     */
    public static String excelColIndexToStr(int columnIndex) {
        if (columnIndex < 0) {
            return null;
        }
        columnIndex = columnIndex + 1;
        String columnStr = "";
        columnIndex--;
        do {
            if (columnStr.length() > 0) {
                columnIndex--;
            }
            columnStr = ((char) (columnIndex % 26 + (int) 'A')) + columnStr;
            columnIndex = (columnIndex - columnIndex % 26) / 26;
        } while (columnIndex > 0);
        return columnStr;
    }

    private static class MetaData {
        int columnIndex;
        boolean needFormatter;
        CellStyle style;
        String dateFormat = "";
        String decimalFormat = "##.#";
        String title;

        public String getFieldValue(Object t, Field field) {
            final Object value = getValue(field.getName(), t);
            if (value == null) {
                return "";
            }
            String result = "";
            if (needFormatter) {
                if (field.getType() == BigDecimal.class) {
                    DecimalFormat df = new DecimalFormat(decimalFormat);
                    result = df.format(value);
                } else if (field.getType() == Date.class) {
                    result = DateFormatUtils.format((Date) value, dateFormat);
                }
            } else {
                result = value.toString();
            }
            return result;
        }

        public String getTitle() {
            return title;
        }

        public void setTitle(String title) {
            this.title = title;
        }

        public int getColumnIndex() {
            return columnIndex;
        }

        public void setColumnIndex(int columnIndex) {
            this.columnIndex = columnIndex;
        }

        public boolean isNeedFormatter() {
            return needFormatter;
        }

        public void setNeedFormatter(boolean needFormatter) {
            this.needFormatter = needFormatter;
        }

        public CellStyle getStyle() {
            return style;
        }

        public void setStyle(CellStyle style) {
            this.style = style;
        }

        public String getDateFormat() {
            return dateFormat;
        }

        public void setDateFormat(String dateFormat) {
            this.dateFormat = dateFormat;
        }

        public String getDecimalFormat() {
            return decimalFormat;
        }

        public void setDecimalFormat(String decimalFormat) {
            this.decimalFormat = decimalFormat;
        }
    }
}

测试

public static void main(String[] args) throws FileNotFoundException {
    
    List<UserExcel > excelDates = new ArrayList<>();
       
    List<UserExcel > results = new ArrayList<>();//用户信息列表
    for (UserExcel dto : results) {
        UserExcel excelDate = new UserExcel();
        excelDate.setName(dto.getName());
        excelDate.setCode(dto.getBirthday());
        excelDate.setRemark(dto.getRemark());
        excelDates.add(excelDate);
    }

    //读取模板
    File outExcel = new File("C:\\111\\3.xlsx");
    File tempFile = new File("C:\\111\\4.xlsx");
    File outFile = new File("C:\\111\\5.xlsx");
    InputStream in = new FileInputStream(outFile);
    List<UserExcel > userExcels = ExcelUtils.readExcelFile(in, 1, UserExcel .class);//导入

    ExcelUtils.outExcelFile(excelDates, outFile); // 导出
    List list = new ArrayList<>();
    
    list.add(excelDates);
    ExcelUtils.outExcelsheetListFile(list, tempFile, 1,outFile);// 根据模板导出
}

结果自行验证，可以的话记得点个赞哦