java poi excel 注解方式导入

java poi excel 注解方式导入

简单封装基于注解方式导入

/**
 * excel导入
 * 基于实体类中的@ImportField 注解读取excel表格中的数据
 */
public class ImportExcelUtil {

    private static final String BEAN_EMPTY  = "对象不能为空";
    private static final String FILE_EMPTY  = "文件不能为空";
    private static final String FILE_FORMAT = "文件格式不正确";
    private static final String ERROR       = "导入失败";
    private static final String TABLE_INDEX = "id";


    /**
     * * 读取出filePath中的所有数据信息
     *   名字与excel表头名字相同即可，顺序可不一致，
     * @param obj  为对象
     * @return
     */
    public static List<Map<String, Object>> importExcel(MultipartFile file , Object obj){
        Assert.notNull(obj , BEAN_EMPTY);
        Assert.notNull(file , FILE_EMPTY);
        try{
            Workbook workbook = getWorkBook(file);
            Sheet sheet = workbook.getSheetAt(0);
            return getData(getField(sheet , obj) , sheet);
        }catch (Exception e){
            throw BaseException.throwEx(ERROR);
        }
    }


    /***
     * 获取   Workbook   对象
     * @param file
     * @return
     */
    public static Workbook getWorkBook(MultipartFile file) throws Exception{
        String fileName = file.getOriginalFilename();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) Assert.notNull(fileName , FILE_FORMAT);
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) return new XSSFWorkbook(file.getInputStream());
        return new HSSFWorkbook(file.getInputStream());
    }


    /****
     * 获取字段排序
     * @param sheet
     * @param object
     * @return
     */
    private static String[] getField(Sheet sheet , Object object){
        Class<?> clazz = object.getClass();
        Field[] fields = clazz.getDeclaredFields();
        Row rowHeader = sheet.getRow(0);
        String[] names = new String[fields.length];
        for(int i = 0 ; i < fields.length ; i++){
            for(int j = 0 ; j < fields.length ; j++){
                fields[i].setAccessible(true);
                ImportField annotation = fields[j].getAnnotation(ImportField.class);
                if(null == annotation) continue;
                if(annotation.value().equals(rowHeader.getCell(i).getStringCellValue().trim())){
                    if(fields[j].getName().equals(TABLE_INDEX)) continue ;
                    names[i] = fields[j].getName();
                }
            }
        }
        return names ;
    }


    /***
     * 获取结果集
     * @param fieldSs
     * @param sheet
     * @return
     */
    public static List<Map<String , Object>> getData(String[] fieldSs , Sheet sheet){
        int lineNum = sheet.getLastRowNum();
        List<Map<String , Object>> list = new ArrayList<>();
        DecimalFormat df = new DecimalFormat("0");
        Map<String , Object> params = null ;
        for (int i = 1 ; i <= lineNum; i++) {
            Row row = sheet.getRow(i);
            if (null == row) continue;
            params = new HashMap<>();
            for(int j = 0 ; j < fieldSs.length ; j++) {
                Cell cell = row.getCell(j);
                if(null == cell) params.put(fieldSs[j] ,  ObjectUtils.isEmpty(cell.getStringCellValue()) ? "" : cell.getStringCellValue());
                /*****************************  字段名根据实际情况修改   *****************************************/
                switch (cell.getCellType()) {
                    case STRING:
                        if(ObjectUtils.isEmpty(cell.getStringCellValue())) params.put(fieldSs[j] ,  "");
                        else{
                            if(cell.getStringCellValue().equals("男"))        params.put(fieldSs[j] ,  1);
                            else if(cell.getStringCellValue().equals("女"))   params.put(fieldSs[j] ,  2);
                            else if(cell.getStringCellValue().equals("是"))   params.put(fieldSs[j] ,  1);
                            else if(cell.getStringCellValue().equals("否"))   params.put(fieldSs[j] ,  0);
                            else if(cell.getStringCellValue().equals("停用")) params.put(fieldSs[j] ,  0);
                            else if(cell.getStringCellValue().equals("启用")) params.put(fieldSs[j] ,  1);
                            else params.put(fieldSs[j] , cell.getStringCellValue());
                        }
                        break;
                    case NUMERIC:
                        if ("General".equals(cell.getCellStyle().getDataFormatString()))    params.put(fieldSs[j] ,  ObjectUtils.isEmpty(cell.getNumericCellValue()) ? null : df.format((cell.getNumericCellValue())));
                        else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) params.put(fieldSs[j] ,  ObjectUtils.isEmpty(cell.getDateCellValue())    ? null : cell.getDateCellValue());
                        else params.put(fieldSs[j] ,  ObjectUtils.isEmpty(cell.getNumericCellValue()) ? null : df.format(cell.getNumericCellValue()));
                        break;
                    case BOOLEAN:
                        params.put(fieldSs[j] ,  ObjectUtils.isEmpty(cell.getBooleanCellValue()) ? true : cell.getBooleanCellValue());
                        break;
                    default:
                        params.put(fieldSs[j] ,  "");
                        break;
                }
            }
            list.add(params);
        }
        return list ;
    }

}