org.apache.poi.ss.usermodel.FormulaEvaluator Java Examples

private List<String> getExcelResourceLines(String fileName) {
    List<String> expectedLines = new ArrayList<>();
    try {
        Workbook wb = WorkbookFactory.create(getClass().getResourceAsStream("/" + fileName));
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        Sheet sheet = wb.getSheetAt(0);
        DataFormatter df = new DataFormatter();
        int index = 0;
        for (Row row : sheet) {
            String rowStr = "";
            for (Cell cell : row) {
                rowStr += df.formatCellValue(cell, evaluator);
            }
            expectedLines.add(index, rowStr);
            index++;
        }
    } catch (IOException | InvalidFormatException e) {
        e.printStackTrace();
    }
    return expectedLines;
}
 

@Test
void testToValueFormulaNumericLong() throws Exception {
  CellValue cellValue = new CellValue(1.2342151234E10);

  Cell cell = mock(Cell.class);

  FormulaEvaluator formulaEvaluator = mock(FormulaEvaluator.class);
  when(formulaEvaluator.evaluate(cell)).thenReturn(cellValue);

  CreationHelper creationHelper = mock(CreationHelper.class);
  when(creationHelper.createFormulaEvaluator()).thenReturn(formulaEvaluator);

  Workbook workbook = mock(Workbook.class);
  when(workbook.getCreationHelper()).thenReturn(creationHelper);

  Sheet sheet = mock(Sheet.class);
  when(sheet.getWorkbook()).thenReturn(workbook);

  when(cell.getCellTypeEnum()).thenReturn(FORMULA);
  when(cell.getSheet()).thenReturn(sheet);
  when(cell.getNumericCellValue()).thenReturn(1.2342151234E10);
  assertEquals("12342151234", toValue(cell));
}
 

@Override
public T toObject(final Cell cell) throws XlsMapperException {

    final ProcessCase processCase = ProcessCase.Load;
    final String formattedValue = Utils.trim(configuration.getCellFormatter().format(cell), trimmed);

    // デフォルト値の設定
    if(isEmptyCell(formattedValue, cell) && defaultValue.isPresent(processCase)) {
        return defaultValue.get(processCase);
    }

    // 数式のセルの場合、予め評価しておく
    final Cell evaluatedCell;
    if(cell.getCellTypeEnum().equals(CellType.FORMULA)) {
        final Workbook workbook = cell.getSheet().getWorkbook();
        final CreationHelper helper = workbook.getCreationHelper();
        final FormulaEvaluator evaluator = helper.createFormulaEvaluator();

        evaluatedCell = evaluator.evaluateInCell(cell);
    } else {
        evaluatedCell = cell;
    }

    return parseCell(evaluatedCell, formattedValue);
}
 

@Override
public String formatCellValue(Cell cell, FormulaEvaluator evaluator) {

    if (cell == null) {
        return "";
    }

    int cellType = cell.getCellType();
    if (cellType == Cell.CELL_TYPE_FORMULA) {
        if (evaluator == null) {
            return cell.getCellFormula();
        }
        cellType = evaluator.evaluateFormulaCell(cell);
    }
    switch (cellType) {
        case Cell.CELL_TYPE_NUMERIC :
            return getFormattedNumberString(cell);
        case Cell.CELL_TYPE_STRING :
            return cell.getRichStringCellValue().getString();
        case Cell.CELL_TYPE_BOOLEAN :
            return String.valueOf(cell.getBooleanCellValue());
        case Cell.CELL_TYPE_BLANK :
            return "";
    }
    throw new RuntimeException("Unexpected celltype (" + cellType + ")");
}
 

/**
 * Converts the specified number of rows of a Excel file into JSON and returns
 * them as a String.
 *
 * @param input the Excel file to convert into JSON
 * @param numRows the number of rows from the Excel file to convert
 * @return a string with the JSON of the Excel rows
 * @throws IOException excel file could not be read
 * @throws InvalidFormatException file is not in a valid excel format
 */
private String convertExcelRows(File input, int numRows) throws IOException, InvalidFormatException {
    try (Workbook wb = WorkbookFactory.create(input)) {
        // Only support single sheet files for now
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        Sheet sheet = wb.getSheetAt(0);
        DataFormatter df = new DataFormatter();
        JSONArray rowList = new JSONArray();
        String[] columns;
        for (Row row : sheet) {
            if (row.getRowNum() <= numRows) {
                //getLastCellNumber instead of getPhysicalNumberOfCells so that blank values don't shift cells
                columns = new String[row.getLastCellNum()];
                for (int i = 0; i < row.getLastCellNum(); i++ ) {
                    columns[i] = df.formatCellValue(row.getCell(i), evaluator);
                }
                rowList.add(columns);
            }
        }
        return rowList.toString();
    }
}
 

/**
     * <p>
     * Returns the formatted value of a cell as a <tt>String</tt> regardless of the cell type. If the Excel number
     * format pattern cannot be parsed then the cell value will be formatted using a default format.
     * </p>
     * <p>
     * When passed a null or blank cell, this method will return an empty String (""). Formula cells will be evaluated
     * using the given {@link FormulaEvaluator} if the evaluator is non-null. If the evaluator is null, then the formula
     * String will be returned. The caller is responsible for setting the currentRow on the evaluator
     * </p>
     * <p>
     * When a ConditionalFormattingEvaluator is present, it is checked first to see if there is a number format to
     * apply. If multiple rules apply, the last one is used. If no ConditionalFormattingEvaluator is present, no rules
     * apply, or the applied rules do not define a format, the cell's style format is used.
     * </p>
     * <p>
     * The two evaluators should be from the same context, to avoid inconsistencies in cached values.
     * </p>
     *
     * @param cell
     *            The cell (can be null)
     * @param evaluator
     *            The FormulaEvaluator (can be null)
     * @param cfEvaluator
     *            ConditionalFormattingEvaluator (can be null)
     * @return a string value of the cell
     */
    public String formatCellValue(Cell cell, FormulaEvaluator evaluator, ConditionalFormattingEvaluator cfEvaluator) {
        localeChangedObservable.checkForLocaleChange();

        if (cell == null) {
            return "";
        }

        CellType cellType = cell.getCellTypeEnum();
        if (cellType == CellType.FORMULA) {
            if (evaluator == null) {
                return cell.getCellFormula();
            }
            cellType = evaluator.evaluateFormulaCellEnum(cell);
        }
        switch (cellType) {
            case NUMERIC:

//                if (DateUtil.isCellDateFormatted(cell, cfEvaluator)) {
                    return getFormattedDateString(cell, cfEvaluator);
//                }
//                return getFormattedNumberString(cell, cfEvaluator);

            case STRING:
                return cell.getRichStringCellValue().getString();

            case BOOLEAN:
                return cell.getBooleanCellValue() ? "TRUE" : "FALSE";
            case BLANK:
                return "";
            case ERROR:
                return FormulaError.forInt(cell.getErrorCellValue()).getString();
            default:
                throw new RuntimeException("Unexpected celltype (" + cellType + ")");
        }
    }
 

public Object getCellValueByEvaluatingFormula(String fileLocation, String cellLocation) throws IOException {
    Object cellValue = new Object();

    FileInputStream inputStream = new FileInputStream(new File(fileLocation));
    Workbook workbook = new XSSFWorkbook(inputStream);

    Sheet sheet = workbook.getSheetAt(0);
    FormulaEvaluator evaluator = workbook.getCreationHelper()
        .createFormulaEvaluator();

    CellAddress cellAddress = new CellAddress(cellLocation);
    Row row = sheet.getRow(cellAddress.getRow());
    Cell cell = row.getCell(cellAddress.getColumn());

    if (cell.getCellType() == CellType.FORMULA) {
        switch (evaluator.evaluateFormulaCell(cell)) {
            case BOOLEAN:
                cellValue = cell.getBooleanCellValue();
                break;
            case NUMERIC:
                cellValue = cell.getNumericCellValue();
                break;
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            default:
                cellValue = null;
        }
    }

    workbook.close();
    return cellValue;
}
 

public static Map<String, String> deleteCell(@NotNull final DeleteCellInputs deleteCellInputs) {
    try {
        final String excelFileName = deleteCellInputs.getCommonInputs().getExcelFileName();
        final Workbook excelDoc = getExcelDoc(excelFileName);
        final Sheet worksheet = getWorksheet(excelDoc, deleteCellInputs.getCommonInputs().getWorksheetName());

        final int firstRowIndex = worksheet.getFirstRowNum();
        final int firstColumnIndex = 0;
        final int lastRowIndex = worksheet.getLastRowNum();
        final int lastColumnIndex = getLastColumnIndex(worksheet, firstRowIndex, lastRowIndex);

        final String rowIndexDefault = firstRowIndex + ":" + lastRowIndex;
        final String columnIndexDefault = firstColumnIndex + ":" + lastColumnIndex;
        final String rowIndex = defaultIfEmpty(deleteCellInputs.getRowIndex(), rowIndexDefault);
        final String columnIndex = defaultIfEmpty(deleteCellInputs.getColumnIndex(), columnIndexDefault);

        final List<Integer> rowIndexList = validateIndex(processIndex(rowIndex), firstRowIndex, lastRowIndex, true);
        final List<Integer> columnIndexList = validateIndex(processIndex(columnIndex), firstColumnIndex, lastColumnIndex, false);

        if (rowIndexList.size() != 0 && columnIndexList.size() != 0) {
            final int deleteCellResult = deleteCell(worksheet, rowIndexList, columnIndexList);
            //update formula cells
            final FormulaEvaluator evaluator = excelDoc.getCreationHelper().createFormulaEvaluator();
            for (Row r : worksheet) {
                for (Cell c : r) {
                    if (c.getCellType() == CellType.FORMULA) {
                        evaluator.evaluateFormulaCell(c);
                    }
                }
            }
            updateWorkbook(excelDoc, excelFileName);
            return getSuccessResultsMap(String.valueOf(deleteCellResult));

        } else {
            return getSuccessResultsMap("0");
        }
    } catch (Exception e) {
        return getFailureResultsMap(e.getMessage());
    }
}
 

/**
 *
 * @param cell the cell
 * @param formulaEvaluator the formula to evaluate, if needed
 * @return return the cell value as String
 */
public static String getCellValueAsString(Cell cell, FormulaEvaluator formulaEvaluator) {
    if (cell == null) {
        return StringUtils.EMPTY;
    }
    switch (cell.getCellType()) {
    case BLANK:
        return "";
    case BOOLEAN:
        return cell.getBooleanCellValue() ? Boolean.TRUE.toString() : Boolean.FALSE.toString();
    case ERROR:
        return "Cell Error type";
    case FORMULA:
        try {
            return getCellValueAsString(cell, formulaEvaluator.evaluate(cell));
        } catch (Exception e) {
            // log error message and the formula
            LOGGER.warn("Unable to evaluate cell (line: {}, col: {}) with formula '{}': {}", cell.getRowIndex(),
                    cell.getColumnIndex(), cell.getCellFormula(), e.getMessage(), e);
            return StringUtils.EMPTY;
        }
    case NUMERIC:
        return getNumericValue(cell, null, false);
    case STRING:
        return StringUtils.trim(cell.getStringCellValue());
    default:
        return "Unknown Cell Type: " + cell.getCellType();
    }
}
 

public static void readSheetWithFormula() {
	try {
		FileInputStream file = new FileInputStream(new File(
				TestUtil.DOC_PATH + File.separator + EXCEL_NAME
						+ Globals.SUFFIX_XLSX));
		// Create Workbook instance holding reference to .xlsx file
		XSSFWorkbook workbook = new XSSFWorkbook(file);
		FormulaEvaluator evaluator = workbook.getCreationHelper()
				.createFormulaEvaluator();
		// Get first/desired sheet from the workbook
		XSSFSheet sheet = workbook.getSheetAt(0);
		// Iterate through each rows one by one
		Iterator<Row> rowIterator = sheet.iterator();
		while (rowIterator.hasNext()) {
			Row row = rowIterator.next();
			// For each row, iterate through all the columns
			Iterator<Cell> cellIterator = row.cellIterator();
			while (cellIterator.hasNext()) {
				Cell cell = cellIterator.next();
				// If it is formula cell, it will be evaluated otherwise no
				// change will happen
				switch (evaluator.evaluateInCell(cell).getCellType()) {
				case Cell.CELL_TYPE_NUMERIC:
					System.out.print(cell.getNumericCellValue() + "\t\t");
					break;
				case Cell.CELL_TYPE_STRING:
					System.out.print(cell.getStringCellValue() + "\t\t");
					break;
				case Cell.CELL_TYPE_FORMULA:
					// Not again
					break;
				}
			}
			System.out.println("");
		}
		file.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
}
 

/**
 *
 * @param cell
 * @param formulaEvaluator
 * @return return the cell value as String (if needed evaluate the existing formula)
 */
public static String getCellValueAsString(Cell cell, FormulaEvaluator formulaEvaluator) {
    if (cell == null) {
        return StringUtils.EMPTY;
    }
    switch (cell.getCellTypeEnum()) {
    case BLANK:
        return "";
    case BOOLEAN:
        return cell.getBooleanCellValue() ? Boolean.TRUE.toString() : Boolean.FALSE.toString();
    case ERROR:
        return "Cell Error type";
    case FORMULA:
        try {
            return getCellValueAsString(cell, formulaEvaluator.evaluate(cell));
        } catch (Exception e) {
            // log error message and the formula
            LOGGER.warn("Unable to evaluate cell (line: {}, col: {}) with formula '{}': {}", cell.getRowIndex(),
                    cell.getColumnIndex(), cell.getCellFormula(), e.getMessage(), e);
            return StringUtils.EMPTY;
        }
    case NUMERIC:
        return getNumericValue(cell, null, false);
    case STRING:
        //TODO which is better? StringUtils.trim(cell.getStringCellValue())
        return cell.getRichStringCellValue().getString();
    default:
        return "Unknown Cell Type: " + cell.getCellTypeEnum();
    }
}
 

static Field parseCell(Cell cell, FormulaEvaluator evaluator) throws ExcelUnsupportedCellTypeException {
  CellType cellType = cell.getCellTypeEnum();
  // set the cellType of a formula cell to its cached formula result type in order to process it as its result type
  boolean isFormula = cell.getCellTypeEnum().equals(CellType.FORMULA);
  if (isFormula) {
    cellType = cell.getCachedFormulaResultTypeEnum();
  }

  switch (cellType) {
    case STRING:
      return Field.create(cell.getStringCellValue());
    case NUMERIC:
      Double rawValue = cell.getNumericCellValue();  // resolves formulas automatically and gets value without cell formatting
      String displayValue = isFormula ? evaluator.evaluate(cell).formatAsString() : dataFormatter.formatCellValue(cell);
      boolean numericallyEquivalent = false;
      try {
        numericallyEquivalent = Double.parseDouble(displayValue) == rawValue;
      } catch (NumberFormatException e) { }

      if (DateUtil.isCellDateFormatted(cell)) {
        // It's a date, not a number
        java.util.Date dt = cell.getDateCellValue();
        // if raw number is < 1 then it's a time component only, otherwise date.
        return rawValue < 1 ? Field.createTime(dt) : Field.createDate(dt);
      }

      // some machinations to handle integer values going in without decimal vs. with .0 for rawValue
      return Field.create(numericallyEquivalent ? new BigDecimal(displayValue) : BigDecimal.valueOf(rawValue));

    case BOOLEAN:
      return Field.create(cell.getBooleanCellValue());
    case BLANK:
      return Field.create("");
    default:
      throw new ExcelUnsupportedCellTypeException(cell, cellType);
  }
}
 

private String retrieveCellContent(Cell cell, Workbook wb, boolean evaluateFormulas, String formatting) {
	FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
	DataFormatter formatter = new DataFormatter(true);
	String cellContent = null;
	int cellType = cell.getCellType();
	switch(cellType) {
	case Cell.CELL_TYPE_BLANK:
		break;
	case Cell.CELL_TYPE_FORMULA:
		if (evaluateFormulas) {
			cellContent = formatter.formatCellValue(cell, evaluator);
		} else {
			// Display the formula instead
			cellContent = cell.getCellFormula();
		}
		break;
	default:
		if(formatting.equalsIgnoreCase("excel")) {
			cellContent = formatter.formatCellValue(cell);
		} else if(formatting.equalsIgnoreCase("raw")) {
			// Display the raw cell contents
			switch (cellType) {
			case Cell.CELL_TYPE_NUMERIC:
				cellContent = Double.toString(cell.getNumericCellValue());
				break;
			case Cell.CELL_TYPE_STRING:
				cellContent = cell.getStringCellValue();
				break;
			case Cell.CELL_TYPE_BOOLEAN:
				cellContent = Boolean.toString(cell.getBooleanCellValue());
				break;	
			}
		}
		break;
	}
	return cellContent;
}
 

protected static void evaluateAllFormulaCells(Workbook wb, FormulaEvaluator evaluator) {
    for(int i=0; i<wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);

        for(Row r : sheet) {
            for (Cell c : r) {
                if (c.getCellTypeEnum() == CellType.FORMULA) {
                    evaluator.evaluateFormulaCellEnum(c);
                }
            }
        }
    }
}
 

public static void setupFormulaEvaluator(Map<String,FormulaEvaluator> evaluators) {
    Map<String, WorkbookEvaluator> evaluatorsByName = new HashMap<String, WorkbookEvaluator>(evaluators.size());
    for (Map.Entry<String,FormulaEvaluator> swb : evaluators.entrySet()) {
        String wbName = swb.getKey();
        FormulaEvaluator eval = swb.getValue();
        if (eval instanceof WorkbookEvaluatorProvider) {
            evaluatorsByName.put(wbName, ((WorkbookEvaluatorProvider)eval)._getWorkbookEvaluator());
        } else {
            throw new IllegalArgumentException("Formula Evaluator " + eval + 
                                               " provides no WorkbookEvaluator access");
        }
    }
    setup(evaluatorsByName);
}
 

/**
 * We store (cell types per row) per column.
 *
 * @param sheet key is the column number, value is a Map with key row number and value Type
 * @return A Map<colId, Map<rowId, type>>
 */
private SortedMap<Integer, SortedMap<Integer, String>> collectSheetTypeMatrix(Sheet sheet,
        FormulaEvaluator formulaEvaluator) {

    int firstRowNum = sheet.getFirstRowNum();
    int lastRowNum = sheet.getLastRowNum();

    LOGGER.debug("firstRowNum: {}, lastRowNum: {}", firstRowNum, lastRowNum);

    SortedMap<Integer, SortedMap<Integer, String>> cellsTypeMatrix = new TreeMap<>();

    // we start analysing rows
    for (int rowCounter = firstRowNum; rowCounter <= lastRowNum; rowCounter++) {

        int cellCounter = 0;

        Row row = sheet.getRow(rowCounter);
        if (row == null) {
            continue;
        }

        Iterator<Cell> cellIterator = row.cellIterator();

        String currentType;

        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();

            CellType xlsType = CellType.STRING;

            try {
                xlsType = cell.getCellType() == CellType.FORMULA ? //
                        formulaEvaluator.evaluate(cell).getCellType() : cell.getCellType();
            } catch (Exception e) {
                // ignore formula error evaluation get as a String with the formula
            }
            switch (xlsType) {
            case BOOLEAN:
                currentType = BOOLEAN.getName();
                break;
            case NUMERIC:
                currentType = getTypeFromNumericCell(cell);
                break;
            case BLANK:
                currentType = BLANK;
                break;
            case FORMULA:
            case STRING:
                currentType = STRING.getName();
                break;
            case ERROR:
                // we cannot really do anything with an error
            default:
                currentType = ANY.getName();
            }

            SortedMap<Integer, String> cellInfo = cellsTypeMatrix.get(cellCounter);

            if (cellInfo == null) {
                cellInfo = new TreeMap<>();
            }
            cellInfo.put(rowCounter, currentType);

            cellsTypeMatrix.put(cellCounter, cellInfo);
            cellCounter++;
        }
    }

    LOGGER.trace("cellsTypeMatrix: {}", cellsTypeMatrix);
    return cellsTypeMatrix;
}
 

@Override
public void setupReferencedWorkbooks(Map<String, FormulaEvaluator> evaluators) {
    CollaboratingWorkbooksEnvironment.setupFormulaEvaluator(evaluators);
}
 

public String getCellStringValueWithFormula(Cell cell, Workbook workbook) {
    DataFormatter formatter = new DataFormatter();
    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    return formatter.formatCellValue(cell, evaluator);
}
 

@NotNull
public static Map<String, String> modifyCell(@NotNull final ModifyCellInputs modifyCellInputs) {
    try {
        final String excelFileName = modifyCellInputs.getCommonInputs().getExcelFileName();
        final Workbook excelDoc = getExcelDoc(excelFileName);
        final Sheet worksheet = getWorksheet(excelDoc, modifyCellInputs.getCommonInputs().getWorksheetName());

        final int firstRowIndex = worksheet.getFirstRowNum();
        final int lastRowIndex = worksheet.getLastRowNum();
        final int firstColumnIndex = 0;
        final int lastColumnIndex = getLastColumnIndex(worksheet, firstRowIndex, lastRowIndex);
        final String columnDelimiter = modifyCellInputs.getColumnDelimiter();
        final String newValue = modifyCellInputs.getNewValue();

        final String rowIndexDefault = firstRowIndex + ":" + lastRowIndex;
        final String columnIndexDefault = firstColumnIndex + ":" + lastColumnIndex;
        final String rowIndex = defaultIfEmpty(modifyCellInputs.getRowIndex(), rowIndexDefault);
        final String columnIndex = defaultIfEmpty(modifyCellInputs.getColumnIndex(), columnIndexDefault);

        final List<Integer> rowIndexList = validateIndex(processIndex(rowIndex), firstRowIndex, lastRowIndex, true);
        final List<Integer> columnIndexList = validateIndex(processIndex(columnIndex), firstColumnIndex, lastColumnIndex, false);

        final List<String> dataList = getDataList(newValue, columnIndexList, columnDelimiter);

        incompleted = false;
        final int modifyCellDataResult = modifyCellData(worksheet, rowIndexList, columnIndexList, dataList);

        if (modifyCellDataResult != 0) {
            //update formula cells
            final FormulaEvaluator evaluator = excelDoc.getCreationHelper().createFormulaEvaluator();
            for (Row row : worksheet) {
                for (Cell cell : row) {
                    if (cell.getCellType() == CellType.FORMULA) {
                        evaluator.evaluateFormulaCell(cell);
                    }
                }
            }
            updateWorkbook(excelDoc, excelFileName);
        }

        if (modifyCellDataResult == rowIndexList.size() && !incompleted) {
            return getSuccessResultsMap(String.valueOf(modifyCellDataResult));
        } else {
            return getFailureResultsMap(String.valueOf(modifyCellDataResult));
        }
    } catch (Exception e) {
        return getFailureResultsMap(e.getMessage());
    }
}
 

private static void processFormulaColumn(final Workbook excelDoc,
                                         final Sheet worksheet,
                                         final int firstRow,
                                         final int columnIndex) {

    final FormulaEvaluator evaluator = excelDoc.getCreationHelper().createFormulaEvaluator();
    for (int i = firstRow; i <= worksheet.getLastRowNum(); i++) {
        final Row row = worksheet.getRow(i);
        if (row != null) {
            final Cell cell = row.getCell(columnIndex);
            if (cell != null && (cell.getCellType() != CellType.BLANK)) {
                //formula type
                if (cell.getCellType() == CellType.FORMULA) {
                    CellValue cellValue = evaluator.evaluate(cell);

                    switch (cellValue.getCellType()) {
                        case BOOLEAN:
                            cell.setCellType(CellType.STRING);
                            break;
                        case NUMERIC:
                            cell.setCellType(CellType.NUMERIC);
                            break;
                        case STRING:
                            if (StringUtils.isBlank(cell.getStringCellValue())) {
                                cell.setCellType(CellType.BLANK);
                            } else {
                                cell.setCellType(CellType.STRING);
                            }
                            break;
                        case BLANK:

                            break;
                        case ERROR:
                            break;

                        // CELL_TYPE_FORMULA will never happen
                        case FORMULA:
                            break;
                    }
                }
            }
        }
    }
}
 

private int init_sheet() {
    tables.clear();
    recordNumber = 0;

    SchemeConf scfg = SchemeConf.getInstance();
    String file_path = "";

    IdentifyDescriptor column_ident = new IdentifyDescriptor();

    // 枚举所有数据表信息
    for (SchemeConf.DataInfo src : scfg.getDataSource()) {
        if (false == src.file_path.isEmpty()) {
            file_path = src.file_path;
        }

        if (file_path.isEmpty() || src.table_name.isEmpty() || src.data_col <= 0 || src.data_row <= 0) {
            ProgramOptions.getLoger().error("data source file \"%s\" (%s:%d，%d) ignored.", src.file_path,
                    src.table_name, src.data_row, src.data_col);
            continue;
        }

        Sheet tb = ExcelEngine.openSheet(file_path, src.table_name);
        if (null == tb) {
            ProgramOptions.getLoger().error("open data source file \"%s\" or sheet \"%s\".", src.file_path,
                    src.table_name);
            continue;
        }

        // 公式支持
        FormulaEvaluator formula = null;
        if (ProgramOptions.getInstance().enableFormular) {
            formula = tb.getWorkbook().getCreationHelper().createFormulaEvaluator();
        }

        DataSheetInfo res = new DataSheetInfo();
        // 根据第一个表建立名称关系表
        {
            int key_row = scfg.getKey().getRow() - 1;
            Row row = tb.getRow(key_row);
            if (null == row) {
                ProgramOptions.getLoger().error("try to get description name of %s in \"%s\" row %d failed",
                        src.table_name, src.file_path, key_row);
                return -53;
            }

            for (int i = src.data_col - 1; i < row.getLastCellNum() + 1; ++i) {
                column_ident.index = i;
                DataContainer<String> k = getStringCache("");
                ExcelEngine.cell2s(k, row, column_ident, formula);
                IdentifyDescriptor ident = IdentifyEngine.n2i(k.get(), i);
                res.name_mapping.put(ident.name, ident);
                res.index_mapping.add(ident);
            }
        }

        res.file_name = file_path;
        res.table = tb;
        res.formula = formula;
        res.next_index = src.data_row - 1;
        res.last_row_number = tb.getLastRowNum();
        res.current_row = null;

        tables.add(res);

        // 记录数量计数
        recordNumber += res.last_row_number - src.data_row + 2;
    }

    return 0;
}
 

/***
 * 构建macro表cache，由于macro表大多数情况下都一样，所以加缓存优化
 */
HashMap<String, String> init_macro_with_cache(List<SchemeConf.DataInfo> src_list) {
    LinkedList<HashMap<String, String>> data_filled = new LinkedList<HashMap<String, String>>();

    IdentifyDescriptor column_ident = new IdentifyDescriptor();

    // 枚举所有macro表信息
    for (SchemeConf.DataInfo src : src_list) {
        String file_path = "";
        if (false == src.file_path.isEmpty()) {
            file_path = src.file_path;
        }
        String fp_name = file_path + "/" + src.table_name;

        // 优先读缓存
        MacroFileCache res = macro_cache.cache.getOrDefault(fp_name, null);
        if (null != res) {
            if (res.file.file_path.equals(file_path) && res.file.table_name.equals(src.table_name)
                    && res.file.data_row == src.data_row && res.file.data_col == src.data_col) {
                data_filled.add(res.macros);
                continue;
            } else {
                ProgramOptions.getLoger().warn(
                        "try to open macro source \"%s:%s\" (row=%d,col=%d) but already has cache \"%s:%s\" (row=%d,col=%d). the old macros will be covered",
                        file_path, src.table_name, src.data_row, src.data_col, res.file.file_path,
                        res.file.table_name, res.file.data_row, res.file.data_col);
            }
        }
        res = new MacroFileCache(src, file_path);

        if (file_path.isEmpty() || src.table_name.isEmpty() || src.data_col <= 0 || src.data_row <= 0) {
            ProgramOptions.getLoger().warn("macro source \"%s\" (%s:%d，%d) ignored.", file_path, src.table_name,
                    src.data_row, src.data_col);
            continue;
        }

        Sheet tb = ExcelEngine.openSheet(file_path, src.table_name);
        if (null == tb) {
            ProgramOptions.getLoger().warn("open macro source \"%s\" or sheet %s failed.", file_path,
                    src.table_name);
            continue;
        }

        FormulaEvaluator evalor = tb.getWorkbook().getCreationHelper().createFormulaEvaluator();

        int row_num = tb.getLastRowNum() + 1;
        for (int i = src.data_row - 1; i < row_num; ++i) {
            Row row = tb.getRow(i);
            column_ident.index = src.data_col - 1;
            DataContainer<String> data_cache = getStringCache("");
            ExcelEngine.cell2s(data_cache, row, column_ident);
            String key = data_cache.get();

            column_ident.index = src.data_col;
            data_cache = getStringCache("");
            ExcelEngine.cell2s(data_cache, row, column_ident, evalor);

            String val = data_cache.get();
            if (null != key && null != val && !key.isEmpty() && !val.isEmpty()) {
                if (res.macros.containsKey(key)) {
                    ProgramOptions.getLoger().warn("macro key \"%s\" is used more than once.", key);
                }
                res.macros.put(key, val);
            }
        }

        macro_cache.cache.put(fp_name, res);
        data_filled.add(res.macros);
    }

    // 空对象特殊处理
    if (data_filled.isEmpty()) {
        return macro_cache.empty;
    }

    // 只有一个macro项，则直接返回
    if (1 == data_filled.size()) {
        return data_filled.getFirst();
    }

    HashMap<String, String> ret = new HashMap<String, String>();
    for (HashMap<String, String> copy_from : data_filled) {
        ret.putAll(copy_from);
    }

    return ret;
}
 

/**
 * Return the columns metadata for the given sheet.
 *
 * @param sheet the sheet to look at.
 * @param datasetId the dataset id.
 * @return the columns metadata for the given sheet.
 */
private List<ColumnMetadata> parsePerSheet(Sheet sheet, String datasetId, FormulaEvaluator formulaEvaluator) {

    LOGGER.debug(Markers.dataset(datasetId), "parsing sheet '{}'", sheet.getSheetName());

    // Map<ColId, Map<RowId, type>>
    SortedMap<Integer, SortedMap<Integer, String>> cellsTypeMatrix =
            collectSheetTypeMatrix(sheet, formulaEvaluator);
    int averageHeaderSize = guessHeaderSize(cellsTypeMatrix);

    // here we have information regarding types for each rows/col (yup a Matrix!! :-) )
    // so we can analyse and guess metadata (column type, header value)
    final List<ColumnMetadata> columnsMetadata = new ArrayList<>(cellsTypeMatrix.size());

    cellsTypeMatrix.forEach((colId, typePerRowMap) -> {

        Type type = guessColumnType(colId, typePerRowMap, averageHeaderSize);

        String headerText = null;
        if (averageHeaderSize == 1 && sheet.getRow(0) != null) {
            // so header value is the first row of the column
            Cell headerCell = sheet.getRow(0).getCell(colId);
            headerText = XlsUtils.getCellValueAsString(headerCell, formulaEvaluator);
        }

        // header text cannot be null so use a default one
        if (StringUtils.isEmpty(headerText)) {
            // +1 because it starts from 0
            headerText = message("import.local.generated_column_name", colId + 1);
        }

        // FIXME what do we do if header size is > 1 concat all lines?
        columnsMetadata.add(ColumnMetadata.Builder //
                .column() //
                .headerSize(averageHeaderSize) //
                .name(headerText) //
                .type(type) //
                .build());

    });

    return columnsMetadata;
}
 

@Override
public void setupReferencedWorkbooks(Map<String, FormulaEvaluator> workbooks) {}
 

public String formatCellValue(Cell cell, FormulaEvaluator formulaEvaluator, String locale) {
	DataFormatter formatter = getLocalizedDataFormater(locale);
	return formatter.formatCellValue(cell, formulaEvaluator);
}
 

@Override
public void setupReferencedWorkbooks(Map<String, FormulaEvaluator> arg0) {
}
 

@Override
public void setupReferencedWorkbooks(Map<String, FormulaEvaluator> evaluators) {
    CollaboratingWorkbooksEnvironment.setupFormulaEvaluator(evaluators);
}
 

public cfData execute( cfSession _session, List<cfData> parameters ) throws cfmRunTimeException {
	cfSpreadSheetData	spreadsheet = null;
	int rowNo, columnNo;
	
	/*
	 * Collect up the parameters
	 */
spreadsheet	= (cfSpreadSheetData)parameters.get(2);
rowNo				= parameters.get(1).getInt() - 1;
columnNo		= parameters.get(0).getInt() - 1;
		
if ( rowNo < 0 )
	throwException(_session, "row must be 1 or greater (" + rowNo + ")");
if ( columnNo < 0 )
	throwException(_session, "column must be 1 or greater (" + columnNo + ")");


/*
 * Find the cell in question 
 */
Sheet	sheet = spreadsheet.getActiveSheet();
Row row	= sheet.getRow( rowNo );
if ( row == null )
	row	= sheet.createRow( rowNo );

Cell cell	= row.getCell( columnNo );
if ( cell == null )
	cell = row.createCell( columnNo );

FormulaEvaluator evaluator = spreadsheet.getWorkBook().getCreationHelper().createFormulaEvaluator();

if ( cell.getCellType() == Cell.CELL_TYPE_BOOLEAN )
	return cfBooleanData.getcfBooleanData( cell.getBooleanCellValue() );
else if ( cell.getCellType() == Cell.CELL_TYPE_NUMERIC )
	return new cfNumberData( cell.getNumericCellValue() );
else if ( cell.getCellType() == Cell.CELL_TYPE_BLANK )
	return cfStringData.EMPTY_STRING;
else if ( cell.getCellType() == Cell.CELL_TYPE_STRING )
	return new cfStringData( cell.getStringCellValue() );
else if ( cell.getCellType() == Cell.CELL_TYPE_FORMULA ){
	CellValue cellValue = evaluator.evaluate(cell);
	
	switch (cellValue.getCellType()) {
		case Cell.CELL_TYPE_BOOLEAN:
			return cfBooleanData.getcfBooleanData(cellValue.getBooleanValue());
		case Cell.CELL_TYPE_NUMERIC:
			return new cfNumberData(cellValue.getNumberValue());
		case Cell.CELL_TYPE_STRING:
			return new cfStringData(cellValue.getStringValue());
		default:
			return cfStringData.EMPTY_STRING;
	}

}else
	return cfStringData.EMPTY_STRING;
}
 

@Documentation(
    value = "Insert a table from an Excel .xlsx file.",
    params = {
        @Param(name = "uri", value = "The Excel .xlsx file uri, it can be relative to the template"),
        @Param(name = "sheetName", value = "The sheet name"),
        @Param(name = "topLeftCellAdress", value = "The top left cell address"),
        @Param(name = "bottomRightCellAdress", value = "The bottom right cell address"),
        @Param(name = "languageTag", value = "The language tag for the locale"),
    },
    result = "insert the table",
    examples = {
        @Example(expression = "'excel.xlsx'.asTable('Feuil1', 'C3', 'F7', 'fr-FR')", result = "insert the table from 'excel.xlsx'"),
    }
)
// @formatter:on
public MTable asTable(String uriStr, String sheetName, String topLeftCellAdress, String bottomRightCellAdress,
        String languageTag) throws IOException {
    final MTable res = new MTableImpl();

    final URI xlsxURI = URI.createURI(uriStr, false);
    final URI uri = xlsxURI.resolve(templateURI);

    try (XSSFWorkbook workbook = new XSSFWorkbook(uriConverter.createInputStream(uri));) {
        final FormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook);
        final XSSFSheet sheet = workbook.getSheet(sheetName);
        if (sheet == null) {
            throw new IllegalArgumentException(String.format("The sheet %s doesn't exist in %s.", sheetName, uri));
        } else {
            final Locale locale;
            if (languageTag != null) {
                locale = Locale.forLanguageTag(languageTag);
            } else {
                locale = Locale.getDefault();
            }
            final DataFormatter dataFormatter = new DataFormatter(locale);
            final CellAddress start = new CellAddress(topLeftCellAdress);
            final CellAddress end = new CellAddress(bottomRightCellAdress);
            int rowIndex = start.getRow();
            while (rowIndex <= end.getRow()) {
                final XSSFRow row = sheet.getRow(rowIndex++);
                if (row != null) {
                    final MRow mRow = new MRowImpl();
                    int cellIndex = start.getColumn();
                    while (cellIndex <= end.getColumn()) {
                        final XSSFCell cell = row.getCell(cellIndex++);
                        if (cell != null) {
                            final MStyle style = getStyle(cell);
                            final MElement text = new MTextImpl(dataFormatter.formatCellValue(cell, evaluator),
                                    style);
                            final Color background = getColor(cell.getCellStyle().getFillForegroundColorColor());
                            final MCell mCell = new MCellImpl(text, background);
                            mRow.getCells().add(mCell);
                        } else {
                            mRow.getCells().add(createEmptyCell());
                        }
                    }
                    res.getRows().add(mRow);
                } else {
                    final int length = end.getColumn() - start.getColumn() + 1;
                    res.getRows().add(createEmptyRow(length));
                }
            }

        }
    }

    return res;
}
 

@Override
public Model convert(ExcelConfig config) throws IOException, MobiException {
    Mapping mapping = mappingFactory.getAllExisting(config.getMapping()).stream().findFirst().orElseThrow(() ->
            new IllegalArgumentException("Missing mapping object"));
    Set<Ontology> sourceOntologies = config.getOntologies().isEmpty() ? getSourceOntologies(mapping) :
            config.getOntologies();
    String[] nextRow;
    Model convertedRDF = modelFactory.createModel();
    ArrayList<ClassMapping> classMappings = parseClassMappings(config.getMapping());

    try {
        Workbook wb = WorkbookFactory.create(config.getData());
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        Sheet sheet = wb.getSheetAt(0);
        DataFormatter df = new DataFormatter();
        boolean containsHeaders = config.getContainsHeaders();
        long offset = config.getOffset();
        Optional<Long> limit = config.getLimit();
        long lastRowNumber = -1;

        //Traverse each row and convert column into RDF
        for (Row row : sheet) {
            // If headers exist or the row is before the offset point, skip the row
            if ((containsHeaders && row.getRowNum() == 0) || row.getRowNum() - (containsHeaders ? 1 : 0) < offset
                    || (limit.isPresent() && row.getRowNum() >= limit.get() + offset) || row.getLastCellNum() < 0) {
                lastRowNumber++;
                continue;
            }
            // Logging the automatic skip of empty rows with no formatting
            while (row.getRowNum() > lastRowNumber + 1) {
                LOGGER.debug(String.format("Skipping empty row number: %d", lastRowNumber + 1));
                lastRowNumber++;
            }
            //getLastCellNumber instead of getPhysicalNumberOfCells so that blank values don't cause cells to shift
            nextRow = new String[row.getLastCellNum()];
            boolean rowContainsValues = false;
            for (int i = 0; i < row.getLastCellNum(); i++) {
                nextRow[i] = df.formatCellValue(row.getCell(i), evaluator);
                if (!rowContainsValues && !nextRow[i].isEmpty()) {
                    rowContainsValues = true;
                }
            }
            //Skipping empty rows
            if (rowContainsValues) {
                writeClassMappingsToModel(convertedRDF, nextRow, classMappings, sourceOntologies);
            } else {
                LOGGER.debug(String.format("Skipping empty row number: %d", row.getRowNum()));
            }
            lastRowNumber++;
        }
    } catch (InvalidFormatException | NotImplementedException e) {
        throw new MobiException(e);
    }

    return convertedRDF;
}