java通用Excel读取器——升级版

excel读取

看了下之前上传的资源，发现excel通用读取的代码下载量还可以，所以趁着有些时间，对excel通用读取的功能进行了一次升级优化，升级后的代码结构上耦合度更低，扩展性更好，同时还增加了些功能，进一步简化了使用。具体设计及代码如下：

1. 单元格的读取

   // 1.针对各个类型的单元格进行读取
   private Object getCellOriginalValue(Cell cell) {
       switch (cell.getCellType()) {
           case STRING:
               // 字符串
               return getStringCellValue(cell);
           case NUMERIC:
               // 数字
               if (DateUtil.isCellDateFormatted(cell)) {
                   return getNumericCellDateValue(cell);
               } else {
                   return getNumericCellValue(cell);
               }
           case BOOLEAN:
               // 布尔值
               return Boolean.toString(cell.getBooleanCellValue());
           case FORMULA:
               // 公式
               try {
                   return getNumericCellValue(cell);
               } catch (Exception e1) {
                   try {
                       return getFormuaCellStringValue(cell);
                   } catch (Exception e2) {
                       throw new RuntimeException("获取公式类型的单元格失败", e2);
                   }
               }
           default:
               return null;
       }
   }
   
   // 2.对于各单元格的值进行转换
   public Object getCellValue(Cell cell) {
       Object cellValue = getCellOriginalValue(cell);
       if (getConfig() == null) {
           return cellValue;
       }
       List<DataConvertor> dataConvertorList = getConfig().getDataConvertorList();
       for (DataConvertor dataConvertor : dataConvertorList) {
           cellValue = dataConvertor.convertData(cellValue);
       }
       return cellValue;
   }
   

   提供的值转换器

   [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kNeuI2Tw-1622466643735)(pic/通用组件/image-20210531204828584.png)]

2. 行的读取

   // 配置与读取器分离，方便后续扩展。
   private List<Object> getRowValueToList(Row row){
       List<ExcelCellReader> excelCellReaderList = getConfig().getExcelCellReaderList();
       int cellCount = getConfig().getEndCellIndex() - getConfig().getStartCellIndex();
       if (cellCount > excelCellReaderList.size()) {
           for (int j = excelCellReaderList.size(); j <= cellCount; j++) {
               excelCellReaderList.add(getConfig().getDefaultCellReader());
           }
       }
       List<Object> rowDataList = new ArrayList<>();
       for (int i = getConfig().getStartCellIndex(); i <= getConfig().getEndCellIndex(); i++) {
           Cell cell = row.getCell(i);
           rowDataList.add(excelCellReaderList.get(i).getCellValue(cell));
       }
       return rowDataList;
   }
   

3. sheet的读取

   // 同样做了配置与读取器的分离
   public List<T> getSheetValue(Sheet sheet) {
       int startRowIndex = getConfig().getStartRowIndex();
       int endRowIndex = getConfig().getEndRowIndex();
       Iterator<Row> rowsIt = sheet.rowIterator();
   
       List<T> sheetDataList = new ArrayList<>();
       int currentIndex = -1;
       while (rowsIt.hasNext()) {
           Row row = rowsIt.next();
           currentIndex++;
           if (currentIndex < startRowIndex) {
               continue;
           }
           if (endRowIndex > -1 && endRowIndex > startRowIndex && currentIndex > endRowIndex) {
               break;
           }
           Object rowValue = config.getExcelRowReader().getRowValue(row);
           if (isInvalidRowValue(rowValue)) {
               continue;
           }
           if (isEndRowValue(rowValue)) {
               break;
           }
           sheetDataList.add(convertRowValueToObject(rowValue));
       }
       return sheetDataList;
   }
   

4. excel的读取

   public List getExcelData() {
       List excelDataList = new ArrayList();
       try {
           for (Map<String, Object> sheetConfigMap : getExcelReaderConfig().getSheetConfigList()) {
               Sheet sheet = getSheet(sheetConfigMap);
               if (sheet == null) {
                   continue;
               }
               excelDataList.add(((ExcelSheetReader) sheetConfigMap.get(ExcelReaderConfig.ECXCEL_READER_CONFIG_SHEET_READER_KEY)).getSheetValue(sheet));
           }
       } finally {
           close();
       }
       return excelDataList;
   }
   

5. 构造器,为了简化使用而生

   // 只展示出冰山一角
   private DataConvertor buildDataConvertor(String dataConvertorStr) {
       if(StringUtils.isBlank(dataConvertorStr)){
           return null;
       }
       String[] dataConvertorInfoArray = dataConvertorStr.split(KEY_SPLIT);
       String dataConvertorName = dataConvertorInfoArray[0];
       DataConvertor dataConvertor = DataConvertFact.getInstance().getDataConvertor(dataConvertorName);
       if(dataConvertor == null){
           return null;
       }
       if(dataConvertorInfoArray.length < 2){
           return dataConvertor;
       }
       if((dataConvertor instanceof ConvertToString)||
          (dataConvertor instanceof ConvertToSqlTimestamp) ||
          (dataConvertor instanceof ConvertToSqlDate) ||
          (dataConvertor instanceof ConvertToSqlTime) ||
          (dataConvertor instanceof ConvertToDate)
         ){
           dataConvertor.setConvertConfig(new FormmaterConfig(dataConvertorInfoArray[1]));
       }else if(dataConvertor instanceof StringReplaceConvert && dataConvertorInfoArray.length>2){
           // 参数：原字符串，新字符串，替换标识（1：替换开头，0：替换结尾，不设置或设置其他的：全部替换）
           StringReplaceConfig convertConfig = new StringReplaceConfig();
           convertConfig.setSrcStr(dataConvertorInfoArray[1]);
           convertConfig.setNewStr(dataConvertorInfoArray[2]);
           if(dataConvertorInfoArray.length>3){
               String replaceOpStr = dataConvertorInfoArray[2];
               if(replaceOpStr.equals("1")){
                   convertConfig.setReplacePrefix(true);
                   convertConfig.setReplaceSuffix(false);
                   convertConfig.setReplaceAll(false);
               }else if(replaceOpStr.equals("0")){
                   convertConfig.setReplacePrefix(true);
                   convertConfig.setReplaceSuffix(true);
                   convertConfig.setReplaceAll(false);
               }
           }
           dataConvertor.setConvertConfig(convertConfig);
       }
       return dataConvertor;
   }
   

6. 使用，最重要的使用来了，针对于普通的类型的excel，返回json集合的，简直不要太简单了

   String fielName = "D:\\test.xlsx"; 
   try {
       DefaultExcelReaderBuilder builder = new DefaultExcelReaderBuilder();
       //1.指定读取文件的文件名称
       builder.setExcelFileName(fielName); 
       //2.指定需要读取的sheet位置（0表示第一个sheet）、各列对应的返回数据中的key名称，
       builder.addSheetReader(0,"seq,name,no,age,birthday,workaddr,tel")
           // 3.特殊需求，日期格式化、字符替换（没有则不需要此步骤）
           .setCellConvert(0,"birthday", String.class.getName()+ DefaultExcelReaderBuilder.KEY_SPLIT+DateUtils.DEFAULT_DATE_FORMATTER
                           ,"StringReplaceConvert"+DefaultExcelReaderBuilder.KEY_SPLIT+"200"+DefaultExcelReaderBuilder.KEY_SPLIT+"199"+DefaultExcelReaderBuilder.KEY_SPLIT+"1")
           .setSheetDataStartRowIndexAndEndCellIndex(0,1,6); // 4.指定读取的起始行和结束列.
       List excelData = builder.build().getExcelData(); // 5.返回 excel中的数据。
       System.out.println(new JSONArray(excelData).toString());
   } catch (Exception e) {
       e.printStackTrace();
   }
   

   全部源码下载地址，请点击