简单封装 java poi 导入excel

简单封装java poi excel导入数据

/**
 * 导入excel
 *  事例
 *      Map<String , String> params = new HashMap<>();
 *      params.put("菜单名称", "name");
 *      params.put("路径", "url");
 *      List<AppMenu> appMenus = ImportExcel.importExcel(file, params, AppMenu.class);
 */
public class ImportExcel {

    private static final String BEAN_EMPTY    = "对象不能为空";
    private static final String FILE_EMPTY    = "文件不能为空";
    private static final String FILE_FORMAT   = "文件格式不正确";
    private static final String ERROR_CONTENT = "导入失败";
    private static final String ERROR_MSG     = "表格第一行必须为表头";


    /**
     *  读取出filePath中的所有数据信息
     *  名字与excel表头名字相同即可，顺序可不一致，
     *  @param file      表格文件
     *  @param params    对应表格表头文字和实体类属性
     *  @param clazz     实体类class
     *  @param <T>       实体类泛型
     *  @return          返回集合实体类
     *  @throws Exception
     */
    public static <T> List<T> importExcel(MultipartFile file , Map<String , String> params , Class<?> clazz){
        Assert.notNull(params , BEAN_EMPTY);
        Assert.notNull(file , FILE_EMPTY);
        Workbook workbook = getWorkBook(file);
        Sheet sheet = workbook.getSheetAt(0);
        List<Map<String, Object>> mapList = getData(getFields(sheet, params), sheet);
        if(workbook != null) try{workbook.close();}catch (Exception e){}
        List<T> list = new ArrayList<>();
        for(Map<String , Object> map : mapList) list.add((T)JSONObject.parseObject(JSONObject.toJSONString(map), clazz));
        return list ;
    }


    /***
     * 获取  Workbook   对象
     * @param file
     * @return
     */
    public static Workbook getWorkBook(MultipartFile file){
        try{
            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());
        }catch (Exception e){
            throw BaseException.throwEx(FILE_FORMAT);
        }
    }


    /**
     * 或标题对应字段
     * @param sheet
     * @param params
     * @return
     */
    private static List<String> getFields(Sheet sheet , Map<String , String> params){
        try{
            List<String> list = new ArrayList<>();
            Row rowHeader = sheet.getRow(0);
            int rowNum = rowHeader.getLastCellNum();
            for(int i = 0 ; i < rowNum ; i++) list.add(params.get(rowHeader.getCell(i).getStringCellValue()));
            return list ;
        }catch (Exception e){
            throw BaseException.throwEx(ERROR_MSG);
        }
    }


    /***
     * 获取结果集
     * @param fieldSs
     * @param sheet
     * @return
     */
    public static List<Map<String , Object>> getData(List<String>  fieldSs, Sheet sheet){
        try{
            int lineNum = sheet.getLastRowNum();
            List<Map<String , Object>> mapList = new ArrayList<>();
            for (int i = 1 ; i <= lineNum; i++) {
                Row row = sheet.getRow(i);
                if (null == row) continue;
                Map<String , Object> map = new HashMap<>();
                int rowNum = row.getLastCellNum();
                for(int j = 0 ; j < rowNum ; j++){
                    Cell cell = row.getCell(j);
                    Object value = null ;
                    switch (cell.getCellType()) {
                        case STRING:
                            if(ObjectUtils.isEmpty(cell.getStringCellValue())) value = "";
                            else{
                                if(cell.getStringCellValue().equals("男"))        value = 1;
                                else if(cell.getStringCellValue().equals("女"))   value = 2;
                                else if(cell.getStringCellValue().equals("是"))   value = 1;
                                else if(cell.getStringCellValue().equals("否"))   value = 0;
                                else if(cell.getStringCellValue().equals("停用")) value = 0;
                                else if(cell.getStringCellValue().equals("启用")) value = 1;
                                else value = cell.getStringCellValue();
                            }
                            break;
                        case NUMERIC:
                            DecimalFormat df = new DecimalFormat("0");
                            if ("General".equals(cell.getCellStyle().getDataFormatString()))    value = ObjectUtils.isEmpty(cell.getNumericCellValue()) ? null : df.format((cell.getNumericCellValue()));
                            else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) value = ObjectUtils.isEmpty(cell.getDateCellValue())    ? null : cell.getDateCellValue();
                            else value = ObjectUtils.isEmpty(cell.getNumericCellValue()) ? null : df.format(cell.getNumericCellValue());
                            break;
                        case BOOLEAN:
                            value = ObjectUtils.isEmpty(cell.getBooleanCellValue()) ? true : cell.getBooleanCellValue();
                            break;
                        default:
                            value = "";
                            break;
                    }
                    map.put(fieldSs.get(j) , value);
                }
                mapList.add(map);
            }
            return mapList ;
        }catch (Exception e){
            throw BaseException.throwEx(ERROR_CONTENT);
        }
    }
}