Beispiel #1
0
 public ReadStatus readSheet(InputStream is, int sheetNo, Map<String, Object> beans) {
   ReadStatus readStatus = new DefaultReadStatus();
   readStatus.setStatus(ReadStatus.STATUS_SUCCESS);
   OgnlStack stack = new OgnlStack(beans);
   try {
     Workbook wb = WorkbookFactory.create(is);
     readSheet(wb, sheetNo, definition.getExcelSheets().iterator().next(), stack, readStatus);
   } catch (IOException e) {
     readStatus.setStatus(ReadStatus.STATUS_READ_FILE_ERROR);
   } catch (InvalidFormatException e) {
     readStatus.setStatus(ReadStatus.STATUS_READ_FILE_ERROR);
   }
   return readStatus;
 }
Beispiel #2
0
  public ReadStatus readAllPerSheet(InputStream is, Map<String, Object> beans) {
    ReadStatus readStatus = new DefaultReadStatus();
    readStatus.setStatus(ReadStatus.STATUS_SUCCESS);
    try {
      Workbook wb = WorkbookFactory.create(is);
      if (definition.getExcelSheets().size() == 0) {
        readStatus.setStatus(ReadStatus.STATUS_SETTING_ERROR);
        readStatus.setMessage("No sheet definition found");
      } else {
        // Only first ExcelSheet Definition will be used
        ExcelSheet sheetDefinition = definition.getExcelSheets().iterator().next();

        Map<String, List<Object>> cacheMap = new HashMap<String, List<Object>>();
        for (String key : beans.keySet()) {
          if (beans.get(key) != null) cacheMap.put(key, new ArrayList<Object>());
        }
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
          Map<String, Object> clonedBeans = cloneMap(beans);
          readSheet(wb, i, sheetDefinition, new OgnlStack(clonedBeans), readStatus);
          for (String key : clonedBeans.keySet()) cacheMap.get(key).add(clonedBeans.get(key));
        }
        for (String key : beans.keySet()) {
          if (cacheMap.containsKey(key)) {
            beans.put(key, cacheMap.get(key));
          } else {
            beans.put(key, null);
          }
        }
      }
    } catch (IOException e) {
      readStatus.setStatus(ReadStatus.STATUS_READ_FILE_ERROR);
    } catch (InvalidFormatException e) {
      readStatus.setStatus(ReadStatus.STATUS_READ_FILE_ERROR);
    } catch (InstantiationException e) {
      e.printStackTrace();
      readStatus.setStatus(ReadStatus.STATUS_SYSTEM_ERROR);
      readStatus.setMessage("New Instance Error");
    } catch (IllegalAccessException e) {
      e.printStackTrace();
      readStatus.setStatus(ReadStatus.STATUS_SYSTEM_ERROR);
      readStatus.setMessage("New Instance Error");
    }
    return readStatus;
  }
Beispiel #3
0
  private void readSheet(
      Workbook wb,
      int sheetNo,
      ExcelSheet sheetDefinition,
      OgnlStack stack,
      ReadStatus readStatus) {
    // In Read Operation only the first loopBlock will be read
    int loopBlock = 0;

    for (ExcelBlock blockDefinition : sheetDefinition.getExcelBlocks()) {
      if (((skipErrors && readStatus.getStatus() == ReadStatus.STATUS_DATA_COLLECTION_ERROR)
              || readStatus.getStatus() == ReadStatus.STATUS_SUCCESS)
          && (loopBlock < 1 || !blockDefinition.isLoop())) {
        if (blockDefinition.isLoop()) {
          loopBlock++;
          readLoopBlock(wb, sheetNo, blockDefinition, stack, readStatus);
        } else readSimpleBlock(wb, sheetNo, blockDefinition, stack, readStatus);
      }
    }
  }
Beispiel #4
0
  private void readSimpleBlock(
      Workbook wb,
      int sheetNo,
      ExcelBlock blockDefinition,
      OgnlStack stack,
      ReadStatus readStatus) {
    // Simple Block will only care about cells in these Block
    Sheet sheet = wb.getSheetAt(sheetNo);
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

    for (ExcelCell cellDefinition : blockDefinition.getCells()) {
      Row row = sheet.getRow(cellDefinition.getRow());
      Cell cell = row == null ? null : row.getCell(cellDefinition.getCol());
      try {
        Object value = getCellValue(cell, evaluator);
        value =
            checkValue(
                sheetNo,
                ExcelUtil.getCellIndex(cellDefinition.getRow(), cellDefinition.getCol()),
                value,
                cellDefinition,
                getPropertyType(stack.peek(), cellDefinition));
        logger.debug(
            "{}[Checked]:{}",
            ExcelUtil.getCellIndex(cellDefinition.getRow(), cellDefinition.getCol()),
            value);
        stack.setValue(cellDefinition.getDataName(), value);
      } catch (ExcelManipulateException e) {
        if (readStatus.getStatus() == ReadStatus.STATUS_SUCCESS)
          readStatus.setStatus(ReadStatus.STATUS_DATA_COLLECTION_ERROR);
        readStatus.addException(e);
      } catch (Exception e) {
        e.printStackTrace();
        readStatus.setStatus(ReadStatus.STATUS_SYSTEM_ERROR);
        readStatus.setMessage(e.getMessage());
      }
    }
  }
Beispiel #5
0
  @SuppressWarnings({"unchecked", "rawtypes"})
  private void readLoopBlock(
      Workbook wb,
      int sheetNo,
      ExcelBlock blockDefinition,
      OgnlStack stack,
      ReadStatus readStatus) {
    // Loop Block will only care about row loop
    String dataName = blockDefinition.getDataName();
    if (dataName == null || dataName.length() == 0) {
      readStatus.setStatus(ReadStatus.STATUS_SETTING_ERROR);
      readStatus.setMessage("dataName for block[" + blockDefinition.toString() + "] is not set");
      return;
    }

    try {
      Object obj = stack.getValue(dataName);
      Collection dataList;
      if (obj == null) {
        dataList = new ArrayList();
        stack.setValue(dataName, dataList);
      } else if (!(obj instanceof Collection)) {
        readStatus.setStatus(ReadStatus.STATUS_SETTING_ERROR);
        readStatus.setMessage("Property " + dataName + " is not a Collection");
        return;
      } else {
        dataList = (Collection) obj;
      }

      int startRow = blockDefinition.getStartRow();
      int step = blockDefinition.getEndRow() - blockDefinition.getStartRow() + 1;
      while (!checkBreak(
          wb.getSheetAt(sheetNo),
          startRow,
          blockDefinition.getStartCol(),
          blockDefinition.getBreakCondition())) {
        Object value = readBlock(wb, sheetNo, blockDefinition, startRow, readStatus);
        dataList.add(value);
        startRow += step;
      }
    } catch (Exception e) {
      e.printStackTrace();
      readStatus.setStatus(ReadStatus.STATUS_SYSTEM_ERROR);
      readStatus.setMessage(e.getMessage());
    }
  }
Beispiel #6
0
 public ReadStatus readAll(InputStream is, Map<String, Object> beans) {
   ReadStatus readStatus = new DefaultReadStatus();
   readStatus.setStatus(ReadStatus.STATUS_SUCCESS);
   try {
     Workbook wb = WorkbookFactory.create(is);
     if (definition.getExcelSheets().size() == 0
         || wb.getNumberOfSheets() < definition.getExcelSheets().size()) {
       readStatus.setStatus(ReadStatus.STATUS_SETTING_ERROR);
       readStatus.setMessage(
           "No sheet definition found or Sheet Number in definition is more than number in file.");
     } else {
       OgnlStack stack = new OgnlStack(beans);
       for (int i = 0; i < definition.getExcelSheets().size(); i++) {
         readSheet(wb, i, definition.getExcelSheets().get(i), stack, readStatus);
       }
     }
   } catch (IOException e) {
     readStatus.setStatus(ReadStatus.STATUS_READ_FILE_ERROR);
   } catch (InvalidFormatException e) {
     readStatus.setStatus(ReadStatus.STATUS_READ_FILE_ERROR);
   }
   return readStatus;
 }
Beispiel #7
0
  /**
   * Read Block in loop condition
   *
   * @param <T>
   * @param wb
   * @param sheetNo
   * @param blockDefinition
   * @param startRow
   * @param needCreate
   * @param readStatus
   * @return
   * @throws Exception
   */
  private Object readBlock(
      Workbook wb, int sheetNo, ExcelBlock blockDefinition, int startRow, ReadStatus readStatus)
      throws Exception {
    Sheet sheet = wb.getSheetAt(sheetNo);
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

    if (blockDefinition.getLoopClass() == null) {
      Map<String, Object> result = new HashMap<String, Object>();

      for (ExcelCell cellDefinition : blockDefinition.getCells()) {
        int rowOffSet = cellDefinition.getRow() - blockDefinition.getStartRow();
        Row row = sheet.getRow(startRow + rowOffSet);
        Cell cell = row == null ? null : row.getCell(cellDefinition.getCol());
        try {
          Object value = getCellValue(cell, evaluator);
          value =
              checkValue(
                  sheetNo,
                  ExcelUtil.getCellIndex(startRow + rowOffSet, cellDefinition.getCol()),
                  value,
                  cellDefinition,
                  getPropertyType(result, cellDefinition));
          logger.debug(
              "{}[Checked]:{}",
              ExcelUtil.getCellIndex(startRow + rowOffSet, cellDefinition.getCol()),
              value);
          result.put(cellDefinition.getDataName(), value);
        } catch (ExcelManipulateException e) {
          if (readStatus.getStatus() == ReadStatus.STATUS_SUCCESS)
            readStatus.setStatus(ReadStatus.STATUS_DATA_COLLECTION_ERROR);
          readStatus.addException(e);
        }
      }
      return result;
    } else {
      Object result = blockDefinition.getLoopClass().newInstance();
      OgnlStack ognlStack = new OgnlStack(result);
      for (ExcelCell cellDefinition : blockDefinition.getCells()) {
        int rowOffSet = cellDefinition.getRow() - blockDefinition.getStartRow();
        Row row = sheet.getRow(startRow + rowOffSet);
        Cell cell = row == null ? null : row.getCell(cellDefinition.getCol());
        try {
          Object value = getCellValue(cell, evaluator);
          value =
              checkValue(
                  sheetNo,
                  ExcelUtil.getCellIndex(startRow + rowOffSet, cellDefinition.getCol()),
                  value,
                  cellDefinition,
                  getPropertyType(result, cellDefinition));
          logger.debug(
              "{}[Checked]:{}",
              ExcelUtil.getCellIndex(startRow + rowOffSet, cellDefinition.getCol()),
              value);
          ognlStack.setValue(cellDefinition.getDataName(), value);
        } catch (ExcelManipulateException e) {
          if (readStatus.getStatus() == ReadStatus.STATUS_SUCCESS)
            readStatus.setStatus(ReadStatus.STATUS_DATA_COLLECTION_ERROR);
          readStatus.addException(e);
        }
      }
      return result;
    }
  }