搭配自定义注解实现excel文件解析成bean对象

1.新建一个注解，用来匹配excel的cell名称

package com.lance.utils.annotation;

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

import static java.lang.annotation.ElementType.FIELD;
import static java.lang.annotation.RetentionPolicy.RUNTIME;

@Target({FIELD})
@Retention(RUNTIME)
public @interface CellMapping
{
    /**
     * 映射的字段名
     *
     * @return
     */
    String name();
}

2.新建一个需要承载excel数据的对象，将需要与excel匹配的字段加上注解

package com.lance.model;

import com.lance.utils.annotation.CellMapping;
import lombok.Data;

@Data
public class SampleModel
{
    /**
     * 序号
     */
    private int index;
    /**
     * 字段1
     */
    @CellMapping(name = "账号")
    private String hey;
    /**
     * 字段2
     */
    @CellMapping(name = "昵称")
    private String man;
}

excel格式如下：

3.读取excel的工具类，使用方式见注释

package com.lance.utils;

import com.lance.utils.annotation.CellMapping;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * excel util
 *
 * @author lance
 */
@Slf4j
public class ExcelUtil
{
    /**
     * 解析文件
     *
     * @param filePath 文件路径
     * @param cls      泛型
     * @return
     * @throws IllegalAccessException
     * @throws InstantiationException
     */
    public static <T> List<T> analysisExcelFile(String filePath, Class<T> cls) throws IllegalAccessException, InstantiationException
    {
        return analysisExcelFile(filePath, 0, 0, cls);
    }

    /**
     * 解析文件
     *
     * @param filePath  文件路径
     * @param bgnIgnore 开头忽略多少行
     * @param endIgnore 结尾忽略多少行
     * @param cls       泛型
     * @return
     * @throws IllegalAccessException
     * @throws InstantiationException
     */
    public static <T> List<T> analysisExcelFile(String filePath, int bgnIgnore, int endIgnore, Class<T> cls) throws IllegalAccessException, InstantiationException
    {
        // 表格数据对象
        List<T> modelList = new ArrayList<>();

        // 用来存放表格中数据
        List<Map<String, Object>> list = new ArrayList<>();

        // 判断文件是否存在
        if (!new File(filePath).exists())
        {
            return null;
        }
        // 读取excel文件信息
        Workbook wb = ExcelUtil.readExcel(filePath);

        if (null == wb)
        {
            return modelList;
        }

        // 获取sheet总数
        int sheetNum = wb.getNumberOfSheets();

        for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++)
        {
            // 获取sheet
            Sheet sheet = wb.getSheetAt(sheetIndex);

            // 获取最大行数
            int rowNum = sheet.getPhysicalNumberOfRows();
            // 除去结尾忽略的行数
            rowNum -= endIgnore;

            // 获取开始行(0 + bgnIgnore)
            Row titleRow = sheet.getRow(bgnIgnore);
            if (null == titleRow)
            {
                continue;
            }
            // 获取明细列数
            int colNum = titleRow.getPhysicalNumberOfCells();

            // 有效的数据开始行，即标题行+1
            int rowBgn = bgnIgnore + 1;
            // 遍历明细
            for (int rowIndex = rowBgn; rowIndex < rowNum; rowIndex++)
            {
                // 行map
                Map<String, Object> map = new HashMap<>();
                // 行信息
                Row row = sheet.getRow(rowIndex);

                if (row != null)
                {
                    // 遍历行并获取到返回值
                    for (int cellIndex = 0; cellIndex < colNum; cellIndex++)
                    {
                        Cell titleCell = titleRow.getCell(cellIndex);
                        Cell cell = row.getCell(cellIndex);
                        // 标题
                        String titleCellData = (String) ExcelUtil.getCellFormatValue(titleCell);
                        // 数据
                        Object cellData = ExcelUtil.getCellFormatValue(cell);
                        map.put(titleCellData, cellData);
                    }
                }
                else
                {
                    break;
                }
                list.add(map);
            }
        }
        log.info("解析sheet完毕,数据共有 ：[{}]条合规记录", list.size());


        // 将对账文件信息存储于类中
        for (Map<String, Object> map : list)
        {
            T model = cls.newInstance();

            // 获取类字段
            Field[] fields = model.getClass().getDeclaredFields();
            for (Field field : fields)
            {
                // 获取字段上的注解
                Annotation[] annotations = field.getAnnotations();
                if (annotations.length == 0)
                {
                    continue;
                }

                for (Annotation an : annotations)
                {
                    field.setAccessible(true);
                    // 若扫描到CellMapping注解
                    if (an.annotationType().getName().equals(CellMapping.class.getName()))
                    {
                        // 获取指定类型注解
                        CellMapping column = field.getAnnotation(CellMapping.class);
                        String mappedName = column.name();

                        // 获取model属性的类型
                        Class<?> modelType = field.getType();
                        // 获取map中的数据
                        Object value = map.get(mappedName);

                        // 匹配字段类型
                        if (null != value)
                        {
                            // 获取map中存主的字段的类型
                            Class<?> cellType = value.getClass();

                            // 处理int类型不匹配问题
                            if ((cellType == double.class || cellType == String.class) && (modelType == int.class || modelType == Integer.class))
                            {
                                value = Integer.valueOf(value.toString());
                            }
                            // 处理double/bigDecimal类型不匹配问题
                            if ((cellType == double.class || cellType == String.class) && modelType == java.math.BigDecimal.class)
                            {
                                // 不使用bigDecimal(double),否则bigDecimal(0.1)有惊喜
                                value = new BigDecimal(value.toString());
                            }
                            if (cellType == String.class && modelType == double.class)
                            {
                                value = Double.valueOf(value.toString());
                            }
                            // 处理String类型不匹配问题
                            if (cellType != String.class && modelType == String.class)
                            {
                                value = value.toString();
                            }
                        }
                        field.set(model, value);
                    }
                }
                field.setAccessible(false);
            }
            modelList.add(model);
        }

        return modelList;
    }

    /**
     * 读取excel
     *
     * @param filePath
     * @return
     */
    public static Workbook readExcel(String filePath)
    {
        if (null == filePath)
        {
            return null;
        }
        String extString = filePath.substring(filePath.lastIndexOf("."));

        InputStream is = null;
        try
        {
            is = new FileInputStream(filePath);
            if (".xls".equals(extString))
            {
                return new HSSFWorkbook(is);
            }
            else if (".xlsx".equals(extString))
            {
                return new XSSFWorkbook(is);
            }
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
        finally
        {
            if (null != is)
            {
                try
                {
                    is.close();
                }
                catch (IOException e)
                {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }

    /**
     * 读取Cell
     *
     * @param cell
     * @return
     */
    public static Object getCellFormatValue(Cell cell)
    {
        Object cellValue;

        //判断cell类型
        switch (cell.getCellType())
        {
            case Cell.CELL_TYPE_NUMERIC:
            {
                cellValue = cell.getNumericCellValue();
                break;
            }
            case Cell.CELL_TYPE_FORMULA:
            {
                //判断cell是否为日期格式
                if (DateUtil.isCellDateFormatted(cell))
                {
                    //转换为日期格式YYYY-mm-dd
                    cellValue = cell.getDateCellValue();
                }
                else
                {
                    //数字
                    cellValue = cell.getNumericCellValue();
                }
                break;
            }
            case Cell.CELL_TYPE_STRING:
            {
                cellValue = cell.getRichStringCellValue().getString();
                break;
            }
            default:
                cellValue = "";
        }
        return cellValue;
    }
}

最后测试一下，vans,使用方便快捷优雅

package com.lance;

import com.lance.model.SampleModel;
import com.lance.utils.ExcelUtil;
import lombok.extern.slf4j.Slf4j;

import java.util.List;

@Slf4j
public class ExcelTest
{
    public static void main(String[] args) throws InstantiationException, IllegalAccessException
    {
        String filePath = "C:\\Users\\Administrator\\Desktop\\wdnmd.xlsx";
        List<SampleModel> list = ExcelUtil.analysisExcelFile(filePath, SampleModel.class);
        System.out.println(list);
    }
}

附上引用的jar包

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.13</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.13</version>
        </dependency>