Java 类org.apache.poi.ss.usermodel.FormulaEvaluator 实例源码

private List<T> parseCurrentSheet() throws IllegalAccessException, InstantiationException, InvocationTargetException, NoSuchMethodException {
    List<T> list = new ArrayList<>();
    workbook = getWorkbook();
    Sheet sheet = workbook.getSheetAt(this.sheetNumber);
    final FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    Map<Method, TableCellMapping> cellMappingByMethod = ImporterUtils.getMappedMethods(clazz, group);
    final Constructor<T> constructor = clazz.getDeclaredConstructor();
    constructor.setAccessible(true);

    int i = 0;
    Iterator<Row> rowIterator = sheet.iterator();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        i++;
        if (i <= headersRows) {
            continue;
        }
        T instance = constructor.newInstance();
        for (Entry<Method, TableCellMapping> methodTcm : cellMappingByMethod.entrySet()) {
            TableCellMapping tcm = methodTcm.getValue();
            ImporterUtils.parseSpreadsheetCell(tcm.converter(), evaluator, row.getCell(tcm.columnIndex()), methodTcm.getKey(), instance, exporterFormatter, useFormatterToParseValueAsString);
        }
        list.add(instance);
    }
    return list;
}

private List<List<String>> parseCurrentSheetAsStringList() {
    workbook = getWorkbook();
    Sheet sheet = workbook.getSheetAt(getSheetNumber());
    final FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    Iterator<Row> rowIterator = sheet.iterator();
    List<List<String>> list = new ArrayList<>();
    int i = 0;
    while (rowIterator.hasNext()) {
        List<String> rowList = new ArrayList<>();
        Row row = rowIterator.next();
        i++;
        if (i <= headersRows) {
            continue;
        }
        Iterator<Cell> cells = row.cellIterator();
        while (cells.hasNext()) {
            Cell cell = cells.next();
            rowList.add(ImporterUtils.getValueOrEmpty(evaluator, cell, exporterFormatter));
        }
        list.add(rowList);
    }
    return list;
}

public static Object getValueOrEmptyAsObject(FormulaEvaluator evaluator, Cell cell) {
    final CellValue cellValue = evaluator.evaluate(cell);
    if (cellValue == null) {
        return "";
    }
    switch (cellValue.getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN:
            return Boolean.valueOf(cellValue.getBooleanValue());
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                return cell.getDateCellValue();
            }
            BigDecimal bd = BigDecimal.valueOf(cell.getNumericCellValue()).setScale(DECIMAL_PRECISION, BigDecimal.ROUND_HALF_UP);
            return bd.stripTrailingZeros();
        case Cell.CELL_TYPE_STRING:
            return cellValue.getStringValue();
        case Cell.CELL_TYPE_ERROR:
            return "ERRO";
        default:
            return "";
    }
}

private void openReferencedWorkbooks(Integer maxWorkbookSize, Workbook workbook, FormulaEvaluator evaluator) {
    Map<String,FormulaEvaluator> workbooks = new HashMap<String, FormulaEvaluator>();

    if(workbook instanceof XSSFWorkbook) {
        if(((XSSFWorkbook)workbook).getExternalLinksTable()!=null) {
            for (ExternalLinksTable sheet : ((XSSFWorkbook)workbook).getExternalLinksTable()) {
                String file = sheet.getLinkedFileName();
                File f = resolver.resolve(file);
                if(f!=null) {
                    try {
                        WorkbookFile book = openWorkbook(f, maxWorkbookSize, false, false);
                        workbooks.put(file, book.getWorkbook().getCreationHelper().createFormulaEvaluator());
                    } catch(Exception e) {
                        logger.error("An error occured while opening referenced workbook '"+file+"'. Main workbook: '"+mainWorkbookFile+"'");
                    }
                } else {
                    logger.warn("Unable to resolve external workbook '"+file+"'. Main workbook: '"+mainWorkbookFile+"'");
                }
            }
        }
    }

    workbooks.put("this", evaluator);           
    evaluator.setupReferencedWorkbooks(workbooks);
}

/**
 * Format the cell content as a String, with number and date format applied.
 * @return cell content with format applied
 */
private String getFormattedText(Locale locale) {
    // is there a special date format?
    if (getResultType()==CellType.DATE) {
        DateTimeFormatter df = getCellStyle().getLocaleAwareDateFormat(locale);
        if (df != null) {
            return df.format(getDateTime());
        }
    }

    // if not, let POI do the formatting
    FormulaEvaluator evaluator = getWorkbook().evaluator;
    DataFormatter dataFormatter = getWorkbook().getDataFormatter(locale);
    try {
        return dataFormatter.formatCellValue(poiCell, evaluator);
    } catch (Exception ex) {
        return Cell.ERROR_TEXT;
    }
}

private Object getValue( Cell cell, FormulaEvaluator evaluator ) {

        switch( cell.getCellType() ) {
            case Cell.CELL_TYPE_FORMULA :
                if( StringUtil.isNotEmpty( cell ) ) {
                    switch( evaluator.evaluateFormulaCell(cell) ) {
                        case Cell.CELL_TYPE_NUMERIC :
                            return getNumericCellValue( cell );
                        case Cell.CELL_TYPE_BOOLEAN :
                            return cell.getBooleanCellValue();
                        case Cell.CELL_TYPE_STRING  :
                            return cell.getStringCellValue();
                    }
                }
                return cell.getStringCellValue();
            case Cell.CELL_TYPE_NUMERIC :
                return getNumericCellValue( cell );
            case Cell.CELL_TYPE_BOOLEAN :
                return cell.getBooleanCellValue();
            default :
                return cell.getStringCellValue();

        }

    }

@Test
public void testRead_FORMULA_NUMERIC() throws IOException {
    try (Workbook workbook = new XSSFWorkbook()) {
        // 準備
        Sheet sheet = workbook.createSheet();
        Row row0 = sheet.createRow(0);
        row0.createCell(0).setCellFormula("1200+34");
        row0.createCell(1).setCellFormula("1200+34.56");
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        evaluator.evaluateAll();

        // 実行＆検証
        try (ExcelReader reader = new ExcelReader(workbook)) {

            String[] r0 = reader.read();
            assertNotNull(r0);
            assertEquals(2, r0.length);
            assertEquals("1234", r0[0]);
            assertEquals("1234.56", r0[1]);

            assertNull(reader.read());
        }
    }
}

@Test
public void testRead_FORMULA_STRING() throws IOException {
    try (Workbook workbook = new XSSFWorkbook()) {
        // 準備
        Sheet sheet = workbook.createSheet();
        Row row0 = sheet.createRow(0);
        row0.createCell(0).setCellFormula("\"CELL\"&\"00\"");
        row0.createCell(1).setCellFormula("\"CELL\"&\"01\"");
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        evaluator.evaluateAll();

        // 実行＆検証
        try (ExcelReader reader = new ExcelReader(workbook)) {

            String[] r0 = reader.read();
            assertNotNull(r0);
            assertEquals(2, r0.length);
            assertEquals("CELL00", r0[0]);
            assertEquals("CELL01", r0[1]);

            assertNull(reader.read());
        }
    }
}

@Test
public void testRead_FORMULA_BOOLEAN() throws IOException {
    try (Workbook workbook = new XSSFWorkbook()) {
        // 準備
        Sheet sheet = workbook.createSheet();
        Row row0 = sheet.createRow(0);
        row0.createCell(0).setCellFormula("1=1");
        row0.createCell(1).setCellFormula("1=0");
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        evaluator.evaluateAll();

        // 実行＆検証
        try (ExcelReader reader = new ExcelReader(workbook)) {

            String[] r0 = reader.read();
            assertNotNull(r0);
            assertEquals(2, r0.length);
            assertEquals("true", r0[0]);
            assertEquals("false", r0[1]);

            assertNull(reader.read());
        }
    }
}

@Test
public void testRead_FORMULA_ERROR() throws IOException {
    try (Workbook workbook = new XSSFWorkbook()) {
        // 準備
        Sheet sheet = workbook.createSheet();
        Row row0 = sheet.createRow(0);
        row0.createCell(0).setCellErrorValue((byte) 0);
        row0.createCell(1).setCellFormula("A1");
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        evaluator.evaluateAll();

        // 実行＆検証
        try (ExcelReader reader = new ExcelReader(workbook)) {

            String[] r0 = reader.read();
            assertNotNull(r0);
            assertEquals(2, r0.length);
            assertNull(r0[0]);
            assertNull(r0[1]);

            assertNull(reader.read());
        }
    }
}

@Test
public void testRead_FORMULA_NUMERIC() throws IOException {
    try (Workbook workbook = new XSSFWorkbook()) {
        // 準備
        Sheet sheet = workbook.createSheet();
        Row row0 = sheet.createRow(0);
        row0.createCell(0).setCellFormula("1200+34");
        row0.createCell(1).setCellFormula("1200+34.56");
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        evaluator.evaluateAll();

        // 実行＆検証
        try (ExcelReader reader = new ExcelReader(workbook)) {

            String[] r0 = reader.read();
            assertNotNull(r0);
            assertEquals(2, r0.length);
            assertEquals("1234", r0[0]);
            assertEquals("1234.56", r0[1]);

            assertNull(reader.read());
        }
    }
}

@Test
public void testRead_FORMULA_STRING() throws IOException {
    try (Workbook workbook = new XSSFWorkbook()) {
        // 準備
        Sheet sheet = workbook.createSheet();
        Row row0 = sheet.createRow(0);
        row0.createCell(0).setCellFormula("\"CELL\"&\"00\"");
        row0.createCell(1).setCellFormula("\"CELL\"&\"01\"");
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        evaluator.evaluateAll();

        // 実行＆検証
        try (ExcelReader reader = new ExcelReader(workbook)) {

            String[] r0 = reader.read();
            assertNotNull(r0);
            assertEquals(2, r0.length);
            assertEquals("CELL00", r0[0]);
            assertEquals("CELL01", r0[1]);

            assertNull(reader.read());
        }
    }
}

@Test
public void testRead_FORMULA_BOOLEAN() throws IOException {
    try (Workbook workbook = new XSSFWorkbook()) {
        // 準備
        Sheet sheet = workbook.createSheet();
        Row row0 = sheet.createRow(0);
        row0.createCell(0).setCellFormula("1=1");
        row0.createCell(1).setCellFormula("1=0");
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        evaluator.evaluateAll();

        // 実行＆検証
        try (ExcelReader reader = new ExcelReader(workbook)) {

            String[] r0 = reader.read();
            assertNotNull(r0);
            assertEquals(2, r0.length);
            assertEquals("true", r0[0]);
            assertEquals("false", r0[1]);

            assertNull(reader.read());
        }
    }
}

@Test
public void testRead_FORMULA_ERROR() throws IOException {
    try (Workbook workbook = new XSSFWorkbook()) {
        // 準備
        Sheet sheet = workbook.createSheet();
        Row row0 = sheet.createRow(0);
        row0.createCell(0).setCellErrorValue((byte) 0);
        row0.createCell(1).setCellFormula("A1");
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        evaluator.evaluateAll();

        // 実行＆検証
        try (ExcelReader reader = new ExcelReader(workbook)) {

            String[] r0 = reader.read();
            assertNotNull(r0);
            assertEquals(2, r0.length);
            assertNull(r0[0]);
            assertNull(r0[1]);

            assertNull(reader.read());
        }
    }
}

private void doFormats(XSSFDataValidationHelper dvHelper, XSSFSheet sheetTracing, int rowIndex, FormulaEvaluator evaluator) {
    insertCondition(dvHelper, sheetTracing, rowIndex, 3, "1", "31");
    insertCondition(dvHelper, sheetTracing, rowIndex, 4, "1", "12");
    insertCondition(dvHelper, sheetTracing, rowIndex, 5, "1900", "3000");
    insertCondition(dvHelper, sheetTracing, rowIndex, 6, "1", "31");
    insertCondition(dvHelper, sheetTracing, rowIndex, 7, "1", "12");
    insertCondition(dvHelper, sheetTracing, rowIndex, 8, "1900", "3000");
    insertDecCondition(dvHelper, sheetTracing, rowIndex, 9);
    insertDropBox(dvHelper, sheetTracing, rowIndex, 10, "=Units");
    insertDropBox(dvHelper, sheetTracing, rowIndex, 11, "=StationIDs");
    //XSSFRow row = sheetTracing.getRow(rowIndex);
    //XSSFCell cell = row.getCell(12);
    //cell.setCellFormula("INDEX(Companies,MATCH(L" + (row.getRowNum() + 1) + ",StationIDs,0),1)");
    //evaluator.evaluateFormulaCell(cell);
    insertDropBox(dvHelper, sheetTracing, rowIndex, 0, "=LotNumbers");      
}

private void evaluateFormulas(Sheet sheet) {
    FormulaEvaluator evaluator = sheet.getWorkbook().getCreationHelper()
            .createFormulaEvaluator();

    Row row = null;
    Cell cell = null;

    for (int i = 0; i < 100; i++) {
        row = sheet.getRow(i);
        if (row == null)
            row = sheet.createRow(i);

        for (int j = 0; j < 50; j++) {
            cell = row.getCell(j);
            if (cell == null)
                cell = row.createCell(j);

            evaluator.evaluateFormulaCell(cell);
        }
    }
}

/**
 * Evaluate the formula of the given cell.
 * 
 * @param workbook
 *            workbook (excel) for evaluating the cell formula
 * @param cell
 *            cell (excel)
 * 
 * @return the value of the excel-call as string (the formula will be executed)
 */
static String evaluateCellFormula(XSSFWorkbook workbook, XSSFCell cell) {
    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    CellValue cellValue = evaluator.evaluate(cell);

    switch (cellValue.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        return String.valueOf(cellValue.getBooleanValue());
    case Cell.CELL_TYPE_NUMERIC:
        return String.valueOf(cellValue.getNumberValue());
    case Cell.CELL_TYPE_STRING:
        return cellValue.getStringValue();
    default:
        return null;
    }
}

public static void evaluateAllCell(FormulaEvaluator evaluator, Sheet sheet) {
    if ((evaluator == null) || (sheet == null)) {
        return;
    }
    for (int r = 0; r <= sheet.getLastRowNum(); r++) {
        Row row = sheet.getRow(r);
        if (row == null) {
            continue;
        }
        for (int c = 0; c <= row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell == null) {
                continue;
            }
            switch (cell.getCellType()) {
                case CELL_TYPE_FORMULA: {
                    evaluator.evaluateInCell(cell);
                }
            }
        }
    }
}

public FormattedValue formatCellValue(Cell cell, FormulaEvaluator evaluator) {
    FormattedValue ret = doFormatCellValue(cell, evaluator);
    if (cell != null && cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        ret.setFormula(cell.getCellFormula());
    }
    if (DEBUG && cell != null && cell.getCellStyle() != null) {
        int cellType = cell.getCellType();
        if (cellType == Cell.CELL_TYPE_FORMULA) {
            cellType = cell.getCachedFormulaResultType();
        }
        if (cellType == Cell.CELL_TYPE_NUMERIC) {
            System.out.println("Format: " + 
                cell.getCellStyle().getDataFormat() + ": " + 
                cell.getCellStyle().getDataFormatString() + " --- " + 
                ExcelUtils.isCellDateFormatted(cell) + ":" + 
                DateUtil.isCellDateFormatted(cell) + ":" + 
                DateUtil.isInternalDateFormat(cell.getCellStyle().getDataFormat()) + " --- " + 
                ret); 
        }
    }
    return ret;
}

@Override
protected List<Object> parse(From fixture) {
    InputStream inputStream = fixture.getInputStream();
    Workbook workbook = createWorkbook(inputStream);

    DataFormatter dataFormatter = new DataFormatter();
    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

    List<Object> mappings = new LinkedList<Object>();
    Helpers helpers = new Helpers();
    helpers.dataFormatter = dataFormatter;
    helpers.evaluator = evaluator;

    Parameters parameters = new Parameters();
    parameters.mode = fixture.getMode();

    for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
        Sheet sheet = workbook.getSheetAt(sheetIndex);

        mappings.addAll(parseSheet(sheet, helpers, parameters));
    }

    return mappings;
}

private Object evaluateCellFormula(final HSSFWorkbook workbook, final Cell cell) {
    FormulaEvaluator evaluator = workbook.getCreationHelper()
            .createFormulaEvaluator();
    CellValue cellValue = evaluator.evaluate(cell);
    Object result = null;
    if (cellValue.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
        result = cellValue.getBooleanValue();
    } else if (cellValue.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        result = cellValue.getNumberValue();
    } else if (cellValue.getCellType() == Cell.CELL_TYPE_STRING) {
        result = cellValue.getStringValue();
    }
    return result;
}

public static void evaluateFormulas(String fileName) throws IOException {
  xLogger.fine("Entering evaluateFormulas. fileName: {0}", fileName);
  // Create a InoutStream from the bytes in the cloud storage.
  // Create a template workbook
  // Evaluate the formulas
  // Save the workbook.
  if (fileName == null || fileName.isEmpty()) {
    xLogger.severe("Cannot evaluate formulas in a null or empty file");
    return;
  }

  InputStream is = null;
  OutputStream os = null;
  // Create a workbook from the bytes
  try {
    // Get the template bytes from GCS ( Note: By now the data has been added to the appropriate sheet/s)
    is = _storageUtil.getInputStream(CustomReportsExportMgr.CUSTOMREPORTS_BUCKETNAME, fileName);
    if (is == null) {
      xLogger.severe("Failed to create Input stream for {0}", fileName);
      return;
    }
    Workbook
        templateWb =
        WorkbookFactory.create(
            is); // From the bytes downloaded from the google cloud storage, form the Workbook
    if (templateWb != null) {
      CreationHelper createHelper = templateWb.getCreationHelper();
      xLogger.fine("Created createHelper. {0}", createHelper);
      if (createHelper != null) {
        FormulaEvaluator evaluator = createHelper.createFormulaEvaluator();
        xLogger.fine("Created evaluator. {0}", evaluator);
        if (evaluator != null) {
          evaluator.evaluateAll();
          xLogger.fine("After evaluator.evaluateAll");
          templateWb.setForceFormulaRecalculation(
              true); // Added this line because some formula cells were not getting updated even after calling evaluateAll
          xLogger.fine("After templateWb.setForceFormulaRecalculation");
          // Write to file
          xLogger.fine("Now creating baos");
          os =
              _storageUtil
                  .getOutputStream(CustomReportsExportMgr.CUSTOMREPORTS_BUCKETNAME, fileName,
                      false);
          xLogger.fine("os: {0}", os);
          templateWb.write(os); // Write the workbook to OutputStream
          xLogger.fine("Wrote templateWb to baos");
        } // end if evaluator != null
      } // end if createHelper != null
    } // end if templateWb != null
  } catch (Exception e) {
    xLogger.severe("{0} while evaluating formulas in the file {1}. Message: {2}",
        e.getClass().getName(), fileName, e.getMessage(), e);
  } finally {
    if (is != null) {
      is.close();
    }
    if (os != null) {
      os.close();
    }
  }
  xLogger.fine("Exiting evaluateFormulas");
}

/**
 * Final formatting of the sheet upon completion of writing the data. For
 * example, we can only size the column widths once the data is in the
 * report and the sheet knows how wide the data is.
 */
protected void finalSheetFormat() {
    final FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    if (isHierarchical()) {
        /*
         * evaluateInCell() is equivalent to paste special -> value. The formula refers to cells
         * in the other sheet we are going to delete. We sum in the other sheet because if we
         * summed in the main sheet, we would double count. Subtotal with hidden rows is not yet
         * implemented in POI.
         */
        for (final Row r : sheet) {
            for (final Cell c : r) {
                if (c.getCellTypeEnum() == CellType.FORMULA) {
                    evaluator.evaluateInCell(c);
                }
            }
        }
        workbook.setActiveSheet(workbook.getSheetIndex(sheet));
        if (hierarchicalTotalsSheet != null) {
            workbook.removeSheetAt(workbook.getSheetIndex(hierarchicalTotalsSheet));
        }
    } else {
        evaluator.evaluateAll();
    }
    for (int col = 0; col < getPropIds().size(); col++) {
        sheet.autoSizeColumn(col);
    }
}

public static <T> List<T> parse(InputStream inputStream, Class<T> type, @Nullable Locale locale) throws IOException {
    Workbook workbook;
    try {
        workbook = WorkbookFactory.create(inputStream);
    } catch (InvalidFormatException ex) {
        throw new BusinessException(BusinessCode.INVALID_EXCEL);
    }
    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    return parse(workbook, evaluator, type, locale);
}

private static <T> List<T> parse(Workbook workbook, FormulaEvaluator evaluator, Class<T> type, @Nullable Locale locale) {
    Metainfo<T> metainfo = Metainfo.forType(type, locale);
    Sheet sheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
    Iterator<Row> rows = sheet.rowIterator();
    if (!rows.hasNext()) {
        return Collections.emptyList();
    }
    Row firstRow = rows.next();
    Map<Integer, String> columnIndexToFieldName = Maps.newHashMapWithExpectedSize(metainfo.size());
    for (Iterator<Cell> it = firstRow.cellIterator(); it.hasNext();) {
        Cell cell = it.next();
        JsonElement jsonElement = parseAsJsonElement(cell, evaluator);
        if (jsonElement != null) {
            Field field = metainfo.getField(jsonElement.getAsString());
            if (field != null) {
                String name = field.getName();
                int index = cell.getColumnIndex();
                columnIndexToFieldName.put(index, name);
            }
        }
    }
    if (columnIndexToFieldName.isEmpty()) {
        return Collections.emptyList();
    }
    List<T> result = new ArrayList<>(sheet.getLastRowNum() - sheet.getFirstRowNum());
    while (rows.hasNext()) {
        result.add(parseRow(evaluator, rows.next(), columnIndexToFieldName, type));
    }
    return result;
}

private static JsonElement parseAsJsonElement(Cell cell, FormulaEvaluator evaluator) {
    switch (cell.getCellTypeEnum()) {
        case NUMERIC:
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                return new JsonPrimitive(DateFormatterHolder.formatter.format(cell.getDateCellValue().toInstant()));
            } else {
                return new JsonPrimitive(cell.getNumericCellValue());
            }
        case STRING:
            return new JsonPrimitive(cell.getStringCellValue());
        case FORMULA:
            CellValue cellValue = evaluator.evaluate(cell);
            switch (cellValue.getCellTypeEnum()) {
                case NUMERIC:
                    return new JsonPrimitive(cellValue.getNumberValue());
                case STRING:
                    return new JsonPrimitive(cellValue.getStringValue());
                case BLANK:
                    return new JsonPrimitive("");
                case BOOLEAN:
                    return new JsonPrimitive(cellValue.getBooleanValue());
                case ERROR:
                default:
                    return null;
            }
        case BLANK:
            return new JsonPrimitive("");
        case BOOLEAN:
            return new JsonPrimitive(cell.getBooleanCellValue());
        case ERROR:
        default:
            return null;
    }
}

private static <T> T parseRow(FormulaEvaluator evaluator, Row row, Map<Integer, String> fields, Class<T> type) {
    JsonObject jsonObject = new JsonObject();
    for (Iterator<Cell> it = row.cellIterator(); it.hasNext();) {
        Cell cell = it.next();
        String name = fields.get(cell.getColumnIndex());
        if (name != null) {
            JsonElement cellValue = parseAsJsonElement(cell, evaluator);
            if (cellValue != null) {
                jsonObject.add(name, cellValue);
            }
        }
    }
    return GsonHolder.GSON.fromJson(jsonObject, type);
}

/**
 * 
 * son las celdas que ya se han modificado
 * se necesitan almacenar para poder notificar los
 * cambios en modificaciones posteriores
 */

protected ExcelBase(ArrayList<WorkBookInfo> arrWorkBookInfo) throws IOException, EncryptedDocumentException, InvalidFormatException
{

    // System.setProperty ("org.apache.poi.util.POILogger",
    // "org.apache.poi.util.SystemOutLogger");
    // System.setProperty ("poi.log.level", POILogger.INFO + "");
    // evaluator.setDebugEvaluationOutputForNextEval(true);
    //Collection<String> unsupportedFuncs = WorkbookEvaluator.getNotSupportedFunctionNames ();
    //agrego funciones personalizadas
    //WorkbookEvaluator.registerFunction("DAVERAGE", new DAVERAGE());

    FileInputStream fis;
    WorkBookObject workBookObject;
    Map<String,FormulaEvaluator> workbooksEvaluator = new HashMap<String, FormulaEvaluator>();

    for (WorkBookInfo workBookInfoItem : arrWorkBookInfo)
    {

         workBookObject = new WorkBookObject();
         workBookObject.workBookInfo = workBookInfoItem;

         fis = new FileInputStream(workBookInfoItem.path);
         workBookObject.workBook = WorkbookFactory.create(fis);

         //recorro las hojas del libro
        for (String sheetNameItem : workBookInfoItem.sheetsNames)
        {
            workBookObject.sheets.put(sheetNameItem,workBookObject.workBook.getSheet(sheetNameItem)); 
        }

        workBookObject.evaluator = workBookObject.workBook.getCreationHelper().createFormulaEvaluator();

        //mapa de referencias de evaluator
        workbooksEvaluator.put(workBookInfoItem.fileName, workBookObject.evaluator);

        arrWorkBookObject.add(workBookObject);

    }

    //asigno referencias todos a todos
    for (WorkBookObject workBookObjectItem : arrWorkBookObject)
    {
        workBookObjectItem.evaluator.setupReferencedWorkbooks(workbooksEvaluator);
    }

}

public static String getValueOrEmpty(FormulaEvaluator evaluator, Cell cell, ExporterFormatter exporterFormatter) {
    Object value = getValueOrEmptyAsObject(evaluator, cell);
    if (value instanceof Date) {
        exporterFormatter.formatByDateType((Date) value);
    }
    if (value instanceof BigDecimal) {
        return formatNumericAsString((BigDecimal) value, exporterFormatter);
    }
    return value.toString();
}

private static int evaluateFormulaCell(Cell cell, FormulaEvaluator evaluator) {
    int typ = -1;
    try {
        typ = evaluator.evaluateFormulaCell(cell);
    } catch (RuntimeException e) {
        String cellRef = CellReference.convertNumToColString(cell.getColumnIndex())+(cell.getRowIndex()+1);
        String errMsg = "Error while evaluating cell " + cellRef + " from sheet " + cell.getSheet().getSheetName() + ": " + e.getMessage();
        throw new RuntimeException(errMsg, e);
    }
    return typ;
}

/**
 * <p>
 * Returns the formatted value of a cell as a <tt>String</tt> regardless
 * of the cell type. If the Excel 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>
 *
 * @param cell The cell (can be null)
 * @param evaluator The FormulaEvaluator (can be null)
 * @return a string value of the cell
 */
public String formatCellValue(Cell cell, FormulaEvaluator evaluator) {
    localeChangedObervable.checkForLocaleChange();

    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 :

            if (ExcelDateUtil.isCellDateFormatted(cell)) {
                return getFormattedDateString(cell);
            }
            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 "";
        case Cell.CELL_TYPE_ERROR:
            return FormulaError.forInt(cell.getErrorCellValue()).getString();
    }
    throw new RuntimeException("Unexpected celltype (" + cellType + ")");
}

private static Object getCellValue(FormulaEvaluator formulaEvaluator, Cell cell) {
    Object cellPrimitiveValue = getCellPrimitiveValue(cell, cell.getCellType());
    if (cellPrimitiveValue != null) {
        return cellPrimitiveValue;
    } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        return getCellValueFromFormula(formulaEvaluator, cell);
    }
    return null;
}

private static Object getCellValueFromFormula(FormulaEvaluator formulaEvaluator, Cell cell) {
    try {
        CellValue cellValue = formulaEvaluator.evaluate(cell);

        if (cellValue.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            if (DateUtil.isCellDateFormatted(cell)) {
                Calendar calendar = GregorianCalendar.getInstance();
                calendar.setTime(DateUtil.getJavaDate(cellValue.getNumberValue()));

                return calendar.getTime();
            } else {
                return DECIMAL_FORMAT.format(cellValue.getNumberValue());
            }
        } else if (cellValue.getCellType() == Cell.CELL_TYPE_STRING) {
            if (StringUtils.hasText(cellValue.getStringValue())) {
                return cellValue.getStringValue();
            }
        }
    } catch (NotImplementedException e) {
        // If formula use Excel features not implemented in POI (like proper),
        // we can retrieve the cached value (which may no longer be correct, depending of what you do on your file).
        FormulaFeature feature = EnumUtils.getEnum(FormulaFeature.class, e.getCause().getMessage());
        if (ALLOWED_NOT_IMPLEMENTED_FORMULA_FEATURES.contains(feature)) {
            return getCellPrimitiveValue(cell, cell.getCachedFormulaResultType());
        } else {
            throw e;
        }
    }

    return null;
}

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;
}

/**
 *
 * @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.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        return "";
    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue() ? Boolean.TRUE.toString() : Boolean.FALSE.toString();
    case Cell.CELL_TYPE_ERROR:
        return "Cell Error type";
    case Cell.CELL_TYPE_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 Cell.CELL_TYPE_NUMERIC:
        return getNumericValue(cell, null, false);
    case Cell.CELL_TYPE_STRING:
        return StringUtils.trim(cell.getStringCellValue());
    default:
        return "Unknown Cell Type: " + cell.getCellType();
    }
}

private boolean updatePrecalculatedValue(Cell destination,
    Cell source, FormulaEvaluator sEvaluator) {
  if(source != null) {
    switch(source.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
      return updatePrecalculatedBlank(destination);
    case Cell.CELL_TYPE_BOOLEAN:
      return updatePrecalculatedBoolean(destination, source.getBooleanCellValue());
    case Cell.CELL_TYPE_ERROR:
      return updatePrecalculatedError(destination,
          FormulaError.forInt(source.getErrorCellValue()));
    case Cell.CELL_TYPE_FORMULA:
      try {
        return updatePrecalculatedCellValue(destination, sEvaluator.evaluate(source));
      } catch(Exception e) {
        switch(source.getCachedFormulaResultType()) {
        case Cell.CELL_TYPE_NUMERIC:
          return updatePrecalculatedNumeric(destination, source.getNumericCellValue());
        case Cell.CELL_TYPE_STRING:
          return updatePrecalculatedString(destination, source.getStringCellValue());
        case Cell.CELL_TYPE_BOOLEAN:
          return updatePrecalculatedBoolean(destination, source.getBooleanCellValue());
        case Cell.CELL_TYPE_ERROR:
          return updatePrecalculatedError(destination,
              FormulaError.forInt(source.getErrorCellValue()));
        }
      }
    case Cell.CELL_TYPE_NUMERIC:
      return updatePrecalculatedNumeric(destination, source.getNumericCellValue());
    case Cell.CELL_TYPE_STRING:
      return updatePrecalculatedString(destination, source.getStringCellValue());
    default:
      return false;
    }
  } else {
    return updatePrecalculatedError(destination, FormulaError.REF);
  }
}

@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 exists 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;
}

public FormulaEvaluator getMainFormulaEvaluator() {
    return mainFormulaEvaluator;
}

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;
}

private String generateCSVFromCell(Cell cell, FormulaEvaluator evaluator)
{
    try
    {
        if ((cell != null) && (cell.getCellType() != Cell.CELL_TYPE_BLANK))
        {
            CellValue cellValue = evaluator.evaluate(cell);

            if (cellValue.getCellType() == Cell.CELL_TYPE_STRING)
                return cellValue.getStringValue();
            else if (cellValue.getCellType() == Cell.CELL_TYPE_NUMERIC)
            {
                if (DateUtil.isCellDateFormatted(cell))
                {
                    CellStyle cellStyle = cell.getCellStyle();

                    String     excelDateFormat = cellStyle.getDataFormatString();
                    String     javaDateFormat  = excelToJavaDataFormat(excelDateFormat);
                    DateFormat dateFormat      = new SimpleDateFormat(javaDateFormat);
                    return dateFormat.format(cell.getDateCellValue());
                }
                else
                    return Double.toString(cellValue.getNumberValue());
            }
            else if (cellValue.getCellType() == Cell.CELL_TYPE_BOOLEAN)
                return Boolean.toString(cellValue.getBooleanValue());
            else if (cellValue.getCellType() == Cell.CELL_TYPE_BLANK)
                return "";
            else
            {
                logger.log(Level.WARNING, "Problem process cell: Unknown CellValue Type = " + cellValue.getCellType());
                return "";
            }
        }
        else
            return "";
    }
    catch (Throwable throwable)
    {
        logger.log(Level.WARNING, "Problem process cell: Unknown Cell Type", throwable);
        return "";
    }
}