Java 反射实现excel单元格自动读取写入

在单元格实体属性较少的时候，可以逐个单元格读取然后使用setXxx的方法设置到实体bean中，写的时候对应以getXxx的方式获取实体bean里的属性值逐个写入到一行的各个单元格中。
但是实体属性多的时候觉得很鸡肋了，我很不喜欢这种setXxx，getXxx一大堆。

可以使用注解的方式加在excel读取后需要转换的实体类中，申明各个属性对应在excel单元格的顺序，然后使用反射的方式获取这个类的所有所有变量然后进行遍历，根据变量上指定的序号去获取对应单元格的值。
写入的时候也是一样获取到所有变量进行遍历，然后使用getXxx的方式获取属性值，再获取到excel指定的序号，写入到指定的单元格中。

Excel注解类：

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

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelAnnotation {
    int order();
}

在实体类属性上使用这个注解申明在excel上对应的单元格的顺序，order从0开始

package invoketest;

import org.apache.commons.lang3.builder.ToStringBuilder;

/**
 * @Description:
 * @Author qiuran
 * @Date 2022/3/22
 **/
public class VideoExcelEntity implements Comparable<VideoExcelEntity>
{

    /**
     * 专区Id
     */
    @ExcelAnnotation(order = 0)
    private String catalogId;

    /**
     * 专区名称
     */
    @ExcelAnnotation(order = 1)
    private String catalogName;

    /**
     * 视频Id
     */
    @ExcelAnnotation(order = 2)
    private String videoId;

    /**
     * 视频名称
     */
    @ExcelAnnotation(order = 3)
    private String videoName;

    /**
     * 视频子标题
     */
    @ExcelAnnotation(order = 4)
    private String shortName;

    /**
     * 简介
     */
    @ExcelAnnotation(order = 5)
    private String profile;

    /**
     * IMDB评分
     */
    @ExcelAnnotation(order = 6)
    private String imdbScore;

    /**
     * 豆瓣评分
     */
    @ExcelAnnotation(order = 7)
    private String doubanScore;

    @ExcelAnnotation(order = 8)
    private String topLevelCode;

    /**
     * 一级分类
     */
    @ExcelAnnotation(order = 9)
    private String topLevelLabel;

    /**
     * 二级分类
     */
    @ExcelAnnotation(order = 10)
    private String secondLevelLabel;

    /**
     * 视频时长(分钟)
     */
    @ExcelAnnotation(order = 11)
    private String duration;

    /**
     * 视频类型
     */
    @ExcelAnnotation(order = 12)
    private String formType;

    /**
     * 剧集总数
     */
    @ExcelAnnotation(order = 13)
    private String serialCount;

    /**
     * 视频封面信息,json数组
     */
    @ExcelAnnotation(order = 14)
    private String mediaFileContent;

    /**
     * 视频封面信息,json数组
     */
    @ExcelAnnotation(order = 15)
    private String cover;

    /**
     * 导入失败原因
     */
    @ExcelAnnotation(order = 16)
    private String failedReason;

    public String getFailedReason()
    {
        return failedReason;
    }

    public void setFailedReason(String failedReason)
    {
        this.failedReason = failedReason;
    }

    public String getVideoId()
    {
        return videoId;
    }

    public String getCatalogId()
    {
        return catalogId;
    }

    public void setCatalogId(String catalogId)
    {
        this.catalogId = catalogId;
    }

    public String getCatalogName()
    {
        return catalogName;
    }

    public void setCatalogName(String catalogName)
    {
        this.catalogName = catalogName;
    }

    public void setVideoId(String videoId)
    {
        this.videoId = videoId;
    }

    public String getVideoName()
    {
        return videoName;
    }

    public void setVideoName(String videoName)
    {
        this.videoName = videoName;
    }


    public String getShortName()
    {
        return shortName;
    }

    public void setShortName(String shortName)
    {
        this.shortName = shortName;
    }

    public String getProfile()
    {
        return profile;
    }

    public void setProfile(String profile)
    {
        this.profile = profile;
    }

    public String getImdbScore()
    {
        return imdbScore;
    }

    public void setImdbScore(String imdbScore)
    {
        this.imdbScore = imdbScore;
    }

    public String getDoubanScore()
    {
        return doubanScore;
    }

    public void setDoubanScore(String doubanScore)
    {
        this.doubanScore = doubanScore;
    }

    public String getTopLevelLabel()
    {
        return topLevelLabel;
    }

    public void setTopLevelLabel(String topLevelLabel)
    {
        this.topLevelLabel = topLevelLabel;
    }

    public String getSecondLevelLabel()
    {
        return secondLevelLabel;
    }

    public void setSecondLevelLabel(String secondLevelLabel)
    {
        this.secondLevelLabel = secondLevelLabel;
    }

    public String getDuration()
    {
        return duration;
    }

    public void setDuration(String duration)
    {
        this.duration = duration;
    }

    public String getFormType()
    {
        return formType;
    }

    public void setFormType(String formType)
    {
        this.formType = formType;
    }

    public String getSerialCount()
    {
        return serialCount;
    }

    public void setSerialCount(String serialCount)
    {
        this.serialCount = serialCount;
    }

    public String getCover()
    {
        return cover;
    }

    public void setCover(String cover)
    {
        this.cover = cover;
    }

    public String getTopLevelCode()
    {
        return topLevelCode;
    }

    public void setTopLevelCode(String topLevelCode)
    {
        this.topLevelCode = topLevelCode;
    }

    public String getMediaFileContent()
    {
        return mediaFileContent;
    }

    public void setMediaFileContent(String mediaFileContent)
    {
        this.mediaFileContent = mediaFileContent;
    }

    @Override
    public String toString()
    {
        return new ToStringBuilder(this).append("catalogId", catalogId)
                .append("catalogId", catalogName)
                .append("videoId", videoId)
                .append("videoName", videoName)
                .append("shortName", shortName)
                .append("profile", profile)
                .append("imdbScore", imdbScore)
                .append("doubanScore", doubanScore)
                .append("topLevelLabel", topLevelLabel)
                .append("secondLevelLabel", secondLevelLabel)
                .append("duration", duration)
                .append("formType", formType)
                .append("serialCount", serialCount)
                .append("mediaFileContent", mediaFileContent)
                .append("cover", cover)
                .append("failedReason", failedReason)
                .append("topLevelCode", topLevelCode)
                .toString();
    }
// 后面放入set集合，根据视频id去重
    @Override
    public int compareTo(VideoExcelEntity o)
    {
        return this.videoId.compareTo(o.getVideoId());
    }
}

几个工具方法：

 // 获取单元格各类型值，返回字符串类型
    public String getCellValueByCell(Cell cell)
    {
        // 判断是否为null或空串
        if (cell == null || cell.toString().trim().equals(""))
        {
            return "";
        }
        String cellValue = "";
        CellType cellType = cell.getCellType();
        if (cellType == CellType.NUMERIC)
        { // 数值类型
            cellValue = String.valueOf((int)cell.getNumericCellValue());
        }
        if (cellType == CellType.STRING)
        { // 表达式类型
            cellValue = cell.getStringCellValue();
        }
        
        return cellValue;
    }
	
	/**
	*读取一行excel数据设置到一个实体类中
	*
	/
	Object readCellValue(Class<?> classT, Row row)
        throws NoSuchMethodException, InvocationTargetException, IllegalAccessException, InstantiationException
    {
        Field[] fields = classT.getDeclaredFields();
        
        Constructor<?> constructor = classT.getConstructor();
        Object instance = constructor.newInstance();
        
        for (Field field : fields)
        {
            if (field.isAnnotationPresent(ExcelAnnotation.class))
            {
                ExcelAnnotation excelAnnotation = field.getAnnotation(ExcelAnnotation.class);
                int order = excelAnnotation.order();
                if (order > row.getLastCellNum() || order < row.getFirstCellNum())
                {
                    continue;
                }
                Cell cell = row.getCell(order);
                String fieldName = field.getName();
                Method method =
                    classT.getMethod("set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1),
                        field.getType());
                
                String val = getCellValueByCell(cell);
                Object value = val;
                
                if (value.getClass() != field.getType())
                {
                    if (field.getType() == Integer.class)
                    {
                        value = Integer.parseInt(val);
                    }
                    if (field.getType() == Long.class)
                    {
                        value = Long.parseLong(val);
                    }
                }
                method.invoke(instance, value);
            }
        }
        return instance;
    }
	
	    /**
     * 写入一行数据
     *
     * @param object
     * @param row
     * @throws NoSuchMethodException
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     */
    void writeCelllValue(Object object, Row row)
        throws NoSuchMethodException, IllegalAccessException, InvocationTargetException
    {
        Field[] fields = object.getClass().getDeclaredFields();
        
        for (Field field : fields)
        {
            if (field.isAnnotationPresent(ExcelAnnotation.class))
            {
                ExcelAnnotation excelAnnotation = field.getAnnotation(ExcelAnnotation.class);
                int order = excelAnnotation.order();
                String fieldName = field.getName();
                Method method = object.getClass()
                    .getMethod("get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1));
                Object valueObj = method.invoke(object);
                if (null == valueObj)
                {
                    row.createCell(order).setCellValue("");
                }
                else
                {
                    row.createCell(order).setCellValue(valueObj.toString());
                }
            }
        }
        
    }
    

读取数据示例

    private void readDrama(Workbook wb)
        throws NoSuchMethodException, InstantiationException, IllegalAccessException, InvocationTargetException
    {
        Sheet sheet = wb.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        // 根据剧集id去除重复数据
        Set<DramaExcelEntity> dramaExcelList = new TreeSet<>();
        
        List<DramaExcelEntity> failedList = new ArrayList<>();
        
        // 从第二行开始读
        for (int i = 1; i <= lastRowNum; i++)
        {
            Row row = sheet.getRow(i);
            
            Object obj = readCellValue(DramaExcelEntity.class, row);
            
            if (null == obj)
            {
                continue;
            }
            DramaExcelEntity dramaExcel = (DramaExcelEntity)obj;
            if (StringUtils.isNotEmpty(dramaExcel.getDramaId()))
            {
                dramaExcelList.add((DramaExcelEntity)obj);
            }
            else
            {
                dramaExcel.setFailedReason("required fileds is empty");
                failedList.add(dramaExcel);
            }
            
            int rowNum = i;
            
            if (rowNum <= BATCH_SIZE && rowNum >= lastRowNum)
            {
                List<DramaExcelEntity> entityList = insertDramaData(dramaExcelList);
                if (!entityList.isEmpty())
                {
                    failedList.addAll(entityList);
                }
                // 插完之后置空
                dramaExcelList = new TreeSet<>();
            }
            else
            {
                // 分批插入
                if (rowNum % BATCH_SIZE == 0 || rowNum == lastRowNum)
                {
                    List<DramaExcelEntity> entityList = insertDramaData(dramaExcelList);
                    
                    if (!entityList.isEmpty())
                    {
                        failedList.addAll(entityList);
                    }
                    // 插完之后置空
                    dramaExcelList = new TreeSet<>();
                }
            }
        }
        if (!failedList.isEmpty())
        {
            try
            {
                writeFailedDrama(failedList);
            }
            catch (Exception e)
            {
                LOG.error("RequestId:{}|write failedVideo files error:{}",requestId,e);
            }
        }
        
    }

写入示例：

 private void writeFailedDrama(List<DramaExcelEntity> invalidDramaList)
    {
        FileOutputStream output = null;
        HSSFWorkbook wb = null;
        try
        {
            String currentDate = DateUtil.getCurrentDate() +"_" +System.currentTimeMillis();
            String filePath = FAILED_DATA_PATH + DRAMA_FILE_PREFIX + "_failed_" + currentDate + ".xls";
            File fileDir = new File(FAILED_DATA_PATH);
            if (!fileDir.exists())
            {
                fileDir.mkdirs();
            }
            File file = new File(filePath);
            if (!file.exists())
            {
                file.createNewFile();
            }
            output = new FileOutputStream(file);
            
            wb = new HSSFWorkbook();
            // 是否需要追加写入 追加需要先判断sheet是否存在,和最后行数进行追加
            int numberOfSheets = wb.getNumberOfSheets();
            Sheet sheet = null;
            if (numberOfSheets <= 0)
            {
                sheet = wb.createSheet();
            }
            else
            {
                sheet = wb.getSheetAt(numberOfSheets - 1);
            }
            // rowNum起始是0
            int lastRowNum = sheet.getLastRowNum();
            
            if (lastRowNum <= 0 || null == sheet.getRow(lastRowNum))
            {
                Row firstRow = sheet.createRow(0);
                // 创建文件头
                for (int j = 0; j < DRAMA_HEADERS.length; j++)
                {
                    firstRow.createCell(j).setCellValue(DRAMA_HEADERS[j]);
                }
                ++lastRowNum;
            }
            
            // 写入文件内容
            for (int i = 0; i < invalidDramaList.size(); i++)
            {
                Row row = sheet.createRow(++lastRowNum);
                writeCelllValue(invalidDramaList.get(i), row);
            }
            // 写入磁盘
            wb.write(output);
        }
        catch (Exception e)
        {
            LOG.error("RequestId:{}|write failedVideo files error:{}" , requestId,e);
        }
        finally
        {
            if (null != wb)
            {
                try
                {
                    wb.close();
                }
                catch (IOException e)
                {
                    LOG.error("RequestId:{}|write failedVideo files close io error:{}" ,requestId, e);
                }
            }
            if (null != output)
            {
                try
                {
                    output.close();
                }
                catch (IOException e)
                {
                    LOG.error("RequestId:{}|write failedVideo files close io error:{}" ,requestId,e);
                }
            }
        }
        
    }