Example #1
0
 private Object getCellValue(Cell cell, FormulaEvaluator evaluator)
     throws ExcelManipulateException {
   if (cell == null) return null;
   // log.debug("Read Value for: " + ExcelUtil.getCellIndex(cell.getRowIndex(),
   // cell.getColumnIndex()));
   Object value = null;
   CellValue cellValue = evaluator.evaluate(cell);
   if (cellValue == null) {
     logger.debug("{}: null", ExcelUtil.getCellIndex(cell.getRowIndex(), cell.getColumnIndex()));
     return null;
   }
   switch (cellValue.getCellType()) {
     case Cell.CELL_TYPE_BLANK:
     case Cell.CELL_TYPE_ERROR:
       break;
     case Cell.CELL_TYPE_BOOLEAN:
       value = cellValue.getBooleanValue();
       break;
     case Cell.CELL_TYPE_NUMERIC:
       if (DateUtil.isCellDateFormatted(cell)) {
         value = DateUtil.getJavaDate(cellValue.getNumberValue());
       } else value = cellValue.getNumberValue();
       break;
     case Cell.CELL_TYPE_STRING:
       value = cellValue.getStringValue();
   }
   logger.debug(
       "{}: {}", ExcelUtil.getCellIndex(cell.getRowIndex(), cell.getColumnIndex()), value);
   return value;
 }
Example #2
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());
      }
    }
  }
Example #3
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;
    }
  }