Java Excel模板校验

Excel模板校验

1.引用

1.批量导入实现的逻辑就是下载模板然后去根据模板的表头去进行填写数据，奈何现在有一种情况就是用户瞎改表头导致在业务层获取这个字段的时候实现业务错误处理。可以在业务层中设置某某某字段为必填，但是感觉这样还是不太严谨。 > 2.所以还是要对excel表格进行模板校验 > 3.最后如果更加完善的话,可以增加文件大小校验，文件格式校验 >

2.模板校验代码

import com.alibaba.excel.annotation.ExcelProperty;
import com.boc.common.excel.vo.StudentVO;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import io.swagger.annotations.ApiOperation;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.BufferedInputStream;
import java.io.InputStream;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author: xxx
 * @createTime: 2021/12/07 17:07
 * @company: https://www.xxx.cn
 * @description:
 */
public class ExcelCheck {

    /**
     * 检验导入Excel的格式
     * @param excel
     * @param size
     * @param unit  限制单位（B,K,M,G)
     * @return
     * @throws Exception
     */
    public static String checkExcelPattern(MultipartFile excel,Object object,int size,String unit) throws Exception {
        if(excel == null || excel.isEmpty()){
            throw new Exception("导入文件不能为空！");
        }
        //用于拼接校验结果
        StringBuilder builder = new StringBuilder();
        //大小校验
        if (!FileUtils.checkFileSize(excel, size, unit)){
            builder.append("上传文件仅支持"+size+unit+"以内的Excel文件;");
        }
        /*if((excel.getSize()/1048576)>2){
            builder.append("上传文件仅支持2M以内的Excel文件；");
        }*/
        //校验文件格式，必须为excel文件
        /*String fileName = excel.getOriginalFilename();
        String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
        if (!"xlsx".equals(suffix)) {
            builder.append("导入文件只支持xlsx类型后缀；");
        }*/
        //校验文件表头
        BufferedInputStream fin = new BufferedInputStream(excel.getInputStream());
        Workbook wb = null;
        try{
            wb = WorkbookFactory.create(fin);;
        }catch(Exception e){
            wb = WorkbookFactory.create(fin);;
        }
        //XSSFWorkbook wb = new XSSFWorkbook(fin);导致出现异常
        //columnName = {"姓名", "性别", "年级", "班级","身份证号","学籍号","学工号","性格","家庭情况","操行等等","特长","标签"};
        //获取注解当中的值
        Map<String, List<String>> annotationValue = getAnnotationValue(object);
        List<String> annotationName = annotationValue.get("annotationName");
        //获取到的实体注解名称顺序要与excel表头顺序保持一样
        String[] columnName = annotationName.toArray(new String[]{});
        Sheet sheet = wb.getSheetAt(0);
        Row row = sheet.getRow(0);
        if (row != null && row.getLastCellNum() >= columnName.length) {
            int lastCellNum = row.getLastCellNum();
            for (int idx = 0; idx < lastCellNum; idx++) {
                String value = getCellValue(row.getCell(idx)).trim();
                if (idx <  columnName.length) {
                    if (StringUtils.isBlank(value) || !columnName[idx].equals(value)) {
                        builder.append("第" + (idx + 1) + "列表头应为" + columnName[idx]+"!");
                    }
                } else {
                    if (idx ==  columnName.length) {
                        builder.append("导入文件只应该有:"+ columnName.length+"!");
                    }
                }
            }
        } else {
            builder.append("上传文件首行不能为空，且应与模板文件表头保持一致；");
        }
        if(builder.length()>0){
            builder.append("请下载模板按照模板表头顺序进行上传!");
            //builder.setCharAt(builder.length()-1, '！');
        }
        return builder.toString();
    }


    /**
     * 获取cell值
     *
     * @param cell
     * @return
     */
    private static String getCellValue(Cell cell) {
        String cellValue = "";
        // 以下是判断数据的类型
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC: // 数字
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    cellValue = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue())).toString();
                } else {
                    DataFormatter dataFormatter = new DataFormatter();
                    cellValue = dataFormatter.formatCellValue(cell);
                }
                break;
            case Cell.CELL_TYPE_STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN: // Boolean
                cellValue = cell.getBooleanCellValue() + "";
                break;
            case Cell.CELL_TYPE_FORMULA: // 公式
                cellValue = cell.getCellFormula() + "";
                break;
            case Cell.CELL_TYPE_BLANK: // 空值
                cellValue = "";
                break;
            case Cell.CELL_TYPE_ERROR: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }


    public static void main(String[] args) throws Exception {
        Map<String, List<String>> annotationValue = getAnnotationValue(new StudentVO());
        System.out.println(annotationValue);
        List<String> annotationName = annotationValue.get("annotationName");
        String[] strings = annotationName.toArray(new String[]{});
        System.out.println(Arrays.toString(strings));
    }


    /**
     * 获取该注解对象的属性值(字段名称和注解的value值)
     * @param object
     * @return
     */
    public static Map<String,List<String>> getAnnotationValue(Object object) {
        Map<String,List<String>>map=new HashMap<>();
        List<String> fieldList=new ArrayList<>();
        List<String> annotationList=new ArrayList<>();
        //StudentVO studentVO = new StudentVO();
        Field[] fields = object.getClass().getDeclaredFields();
        for(int i = 0 ; i < fields.length ; i++) {
            //设置是否允许访问，不是修改原来的访问权限修饰词。
            fields[i].setAccessible(true);
            ExcelProperty annotation = fields[i].getAnnotation(ExcelProperty.class);
            String[] value = annotation.value();
            for (String s : value) {
                annotationList.add(s);
            }
            fieldList.add(fields[i].getName());
        }
        map.put("fieldName",fieldList);
        map.put("annotationName",annotationList);
        return map;
    }

}   

3.解释

image 在这里插入图片描述 image 在这里插入图片描述

1.传入Excel导入的实体目的就是为了通过反射然后去获取@ExcelProperty中的表头值，做到自动化 > 2.其中fileUtils是校验文件大小的工具类。需要的在我以前文章找找。 > 3.相关代码注释里面已经写的很完整，如果自用可以在这个基础上面进行修改

4.异常情况

1.The supplied data appears to be in the OLE2 Format. You are calling the part of POI that deals with OOXML (Office Open XML) Documents. You need to call a different part of POI to process this data (eg HSSF instead of XSSF) > 场景：easyExcel导入时报错，百度之后发现是.xls文件手动修改后缀为.xlsx，poi识别报错 > 解决方法：.xls另存为.xlsx而不是手动修改后缀 > 彻底解决方法：这段异常表示excel版本和workbook的类型不匹配导致的, HSSFWorkbook不支持Excel2007之后的文件版本, poi提供了XSSFWorkbook类型支持后面的版本,但是这段代码对老版本的excel不兼容, 查看文档和源码发现HSSFWorkbook和XSSFWorkbook都实现了Workbook接口, 按照网上推荐的方法, 把代码修改成了

Workbook workbook = null; try{ workbook = new HSSFWorkbook(in); }catch(Exception e){ workbook = new XSSFWorkbook(in); }

然后. 成功的抛出了另一个异常 java.io.IOException: Stream closed，可以通过下面方式解决

Workbook workbook = WorkbookFactory.create(in);

然后如果还有出现这个错误的：java.io.IOException: getFileMagic() only operates on streams which support mark(int)：因为FileInputStream不支持标记，可以使用BufferedInputStream代替

BufferedInputStream fin = new BufferedInputStream(excel.getInputStream());