Формула Java POI XSSF VLookup

Я пытаюсь поместить простую формулу VLookup в свой файл «.xlsx», используя Java и Apache POI.
Эта формула имеет внешнюю ссылку, и она НЕ работает для меня.

Итак, чтобы дать вам более подробную информацию, я использую poi и poi-ooxml версии 3.13 и excel 2007.
Я помещаю формулу в ячейку следующим образом (где ячейка - это ячейка):

cell.setCellType(Cell.CELL_TYPE_FORMULA);
cell.setCellFormula("StringContainingFormula");

А затем оцените формулу. Я пробовал три разных способа, но безуспешно. (wb - это XSSFWorkbook).

1

FormulaEvaluator mainWorkbookEvaluator = wb.getCreationHelper().createFormulaEvaluator();
Map<String,FormulaEvaluator> workbooks = new HashMap<String, FormulaEvaluator>();
workbooks.put("SpreadsheetName.xlsx", mainWorkbookEvaluator);
mainWorkbookEvaluator.setupReferencedWorkbooks(workbooks);
mainWorkbookEvaluator.evaluateAll();

2 

XSSFEvaluationWorkbook.create(wb);
Workbook nwb = wb;
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
for (Sheet sheet : nwb) {
    for (Row r : sheet) {
        for (Cell c : r) {
            if (c.getCellType() == Cell.CELL_TYPE_FORMULA) {
                try {
                    //evaluator.evaluateFormulaCell(c);
                    evaluator.evaluate(c);
                } catch (Exception e) {
                    System.out.println("Error occured in 'EvaluateFormulas' : " + e);
                }
            }
        }
    }
}

3

XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);

Проблема в том, что он записывает в файл Excel, а затем при его оценке выдает ошибку:

java.lang.IllegalArgumentException: недопустимый sheetIndex: -1

Теперь, если я открываю файл Excel, появляется предупреждение:

Автоматическое обновление ссылок отключено

Если я включаю содержимое, формула показывает результат правильно, а если я не делаю ничего, кроме формулы, в результате получается #N/A.
Теперь, если я выберу ячейку с формулой в ней, щелкните панель формул и нажмите введите, чем формула показывает результат.

Обновление:

Итак, я отключил предупреждающее сообщение, перейдя в параметры Excel, и он начал выдавать мне формулу внутри ячейки.
НО, когда я попытался получить из него значение результата, используя

if (cell.getCachedFormulaResultType == Cell.CELL_TYPE_STRING) {
    System.out.println("Last evaluated as \"" + cell.getRichStringCellValue() + "\"");
}

Он никогда не давал мне getCachedFormulaResultType как CELL_TYPE_STRING, он всегда возвращал CELL_TYPE_NUMERIC. Он должен возвращать строковое значение. Я указываю URL и другое значение (слова, разделенные "|" - "кошка | собака | птица").

Я ценю любую помощь / предложение.


java apache-poi xssf
person ManishChristian    schedule 29.01.2016    source источник
comment
Вероятно, лучше всего внимательно проверить документацию Оценка формул. Может, вам поможет что-то вроде wb.setForceFormulaRecalculation(true);?   -  person K.Nicholas    schedule 30.01.2016
comment
Кроме того, в документе указано createFormulaEvaluator для листов, на которые вы ссылаетесь. Например, workbooks.put("input.xls", WorkbookFactory.create("c:\temp\input22.xls").getCreationHelper().createFormulaEvaluator());   -  person K.Nicholas    schedule 30.01.2016
comment
@Nicholas, большое спасибо за советы, но я не смог найти ничего похожего на wb.setForceFormulaRecalculation(true);, и я уже пробовал createFormulaEvaluator ссылку, но не повезло.   -  person ManishChristian    schedule 01.02.2016
comment
Что ж, я имел в виду, что в документе говорится, что у вас должен быть createFormulatEvaluator для каждой электронной таблицы, как основной электронной таблицы, так и электронной таблицы, на которую ссылается VLookup.   -  person K.Nicholas    schedule 01.02.2016
comment
Я попробовал использовать обе таблицы (добавил их обе), но получил сообщение об ошибке Attempted to register same workbook under names "Spreadsheet one (reference from)" and "Spreadsheet two ((reference to))".   -  person ManishChristian    schedule 01.02.2016
comment
Что ж, я могу попробовать здесь простой пример, если хочешь. Вы сопоставили внешнюю ссылку с setupReferencedWorkbooks(java.util.Map<java.lang.String,FormulaEvaluator> workbooks)?   -  person K.Nicholas    schedule 01.02.2016
comment
@ Николас, это было бы круто. И, к вашему сведению, если я использую XSSFWorkbook, он не дает мне возможности setupReferencedWorkbooks. Возможно, мне придется преобразовать его в рабочую книгу, а затем попробовать.   -  person ManishChristian    schedule 01.02.2016

Ответы (3)


arrow_upward
1
arrow_downward

Ну вроде работает. Вы должны относиться к excel осторожно; Намного удобнее, если вы сначала откроете внешнюю книгу, а затем основную. Если вы этого не сделаете, появится сообщение о небезопасных ссылках, но в остальном все в порядке. Оценщик работает только с отдельными ячейками, поэтому вам придется выполнить цикл, если вы хотите пересчитать всю электронную таблицу. Кроме того, внешняя ссылка должна быть уже связана с помощью excel, POI еще не реализовал эту функцию. Вот что я положил в гитхаб:

    // Open workbooks
    Path pathBook1 = Paths.get("c:/users/karl/scsb/Vlookup/Book1.xlsx");
    InputStream is = Files.newInputStream(pathBook1);
    XSSFWorkbook book1 = new XSSFWorkbook(is);
    // Add Linked Cell
    // The workbook must already have been linked to Book2.xlsx by Excel
    // The linkExternalWorkbook has not yet been implemented: SEE BUG #57184
    Cell cell = book1.getSheetAt(0).createRow(2).createCell(0);
    cell.setCellFormula("A2+[Book2.xlsx]Sheet1!A1");
    // Create evaluator after the new cell has been added.
    FormulaEvaluator mainEvaluator = book1.getCreationHelper().createFormulaEvaluator();
    XSSFWorkbook book2 = new XSSFWorkbook("c:/users/karl/scsb/Vlookup/Book2.xlsx");
    Map<String, FormulaEvaluator> workbooks = new HashMap<String, FormulaEvaluator>();
    workbooks.put("Book1.xlsx", mainEvaluator);
    workbooks.put("Book2.xlsx", book2.getCreationHelper().createFormulaEvaluator());
    mainEvaluator.setupReferencedWorkbooks(workbooks);
//  mainEvaluator.evaluateAll();                            // doesn't work.
//  XSSFFormulaEvaluator.evaluateAllFormulaCells(book1);    // doesn't work.
    mainEvaluator.evaluateFormulaCell(cell);
    System.out.println(cell.getNumericCellValue());
    book2.close();
    // Close and write workbook 1
    is.close();
    OutputStream os = Files.newOutputStream(pathBook1);
    book1.write(os);
    os.close();
    book1.close();
person K.Nicholas    schedule 02.02.2016
comment
У меня не работает. Если я использую setCellFormula (без полного пути (как и вы)) перед открытием книги, на которую ссылаются, это дает мне ошибку java.lang.RuntimeException: Book not linked for filename "ReferencedFileName.xlsx". И если я использую полный путь, это снова дает мне Invalid sheetIndex: -1 ошибку. - person ManishChristian; 02.02.2016
comment
Как я уже упоминал, внешняя электронная таблица должна быть связана с помощью excel перед запуском этого кода. Существует вызов метода для связывания электронных таблиц в POI (linkExternalWorkbook), но он не реализован. Я ничего не могу с этим поделать. - person K.Nicholas; 02.02.2016
comment
Прежде всего, большое спасибо @Nicholas за вашу помощь. Итак, можно вкратце сказать, что в настоящий момент это невозможно? Потому что, если мы не можем связать книги, нет смысла ссылаться на внешнюю книгу. - person ManishChristian; 02.02.2016
comment
Да, мы можем сказать, что в настоящее время это невозможно сделать полностью на Java. - person K.Nicholas; 02.02.2016

arrow_upward
0
arrow_downward

У меня недавно возникла такая же проблема. Проблема заключалась в том, что с текущим POI нельзя было написать документ, добавить формулу в конкретное поле и выполнить вычисление формулы для документа, который на данный момент еще не существует. Чтобы решить эту проблему, я использовал следующий трюк:

  1. Запишите все данные и формулы в книгу
  2. добавьте workbook.setForceFormulaRecalculation(true); перед закрытием этого документа
  3. Напишите и закройте документ
  4. Откройте тот же документ снова и сделайте только FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); evaluator.evaluateAll();
  5. Напишите и закройте снова
person Vanja Lee    schedule 08.02.2016

arrow_upward
0
arrow_downward

Итак, поскольку у меня нет другого варианта, мне пришлось скопировать данные, на которые есть ссылки, в основную книгу (я знаю, что это не лучшая идея, если данные огромны), чтобы все заработало.
Итак, что я сделал является:

  1. Скопируйте данные из внешней книги.
  2. Создайте новый лист в основной книге (в котором вам нужно написать формулу) и вставьте данные, на которые есть ссылки.
  3. Теперь напишите формулу, и на этот раз вместо внешней книги используйте вновь созданный рабочий лист (со вставленными данными).
    Примечание *: вам нужно сначала записать все данные (включая формулы), прежде чем заголовок для оценки формулы.
  4. Затем откройте книгу для чтения, если вы уже закрылись, и оцените все формулы, подобные этой (wb - это XSSFWorkbook):

XSSFFormulaEvaluator.evaluateAllFormulaCells (wb);

person ManishChristian    schedule 08.02.2016