You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Kumart <kt...@emmes.com> on 2019/04/03 16:30:42 UTC
Cell Calculations by reference to other cells in hidden sheets
within the same workbook
Hi,I have a Excel workbook/spreadsheet with 1 visible sheet and 6 hidden
sheets.Formula cells on visible sheet (Sheet 0) are pointing to cells on
hidden sheets within the same workbook.One of the formulas is to calculate
age based on two dates. When I try to retrieve calculated value of cell
containing age, I am getting a string, "'Unformatted Values'!AD13" where
'Unformatted Values' is one of the hidden sheets.I have seen methods to link
external workbooks and to evaluate all formulas usingFormulaEvaluator
mainWorkbookEvaluator =
wb.getCreationHelper().createFormulaEvaluator();Map<String,
FormulaEvaluator> workbooks = new HashMap<String,
FormulaEvaluator>();workbooks.put(wb.getSheetName(0),
mainWorkbookEvaluator);workbooks.put(wb.getSheetName(1),
mainWorkbookEvaluator);workbooks.put(wb.getSheetName(2),
mainWorkbookEvaluator); etc.,When trying to setup referenced workbooks
using,mainWorkbookEvaluator.setupReferencedWorkbooks(workbooks);I am getting
the following exception about attempting to register same workbook under
different names.java.lang.IllegalArgumentException: Attempted to register
same workbook under names 'Unformatted Values' and 'NP T-Scores' at
org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.(CollaboratingWorkbooksEnvironment.java:114)
at
org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.setup(CollaboratingWorkbooksEnvironment.java:75)
at
org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.setupFormulaEvaluator(CollaboratingWorkbooksEnvironment.java:89)
at
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.setupReferencedWorkbooks(HSSFFormulaEvaluator.java:103)
at com.emmes.hbb.POICalculator.main(POICalculator.java:61)How can I create a
link/reference between visible sheet and all the other hidden sheets for
evaluating formulas?Thanks for your help.
--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html
Re: Cell Calculations by reference to other cells in hidden sheets
within the same workbook
Posted by Greg Woolsey <gr...@gmail.com>.
From the Javadoc for evaluate(Cell):
"This method should be preferred over evaluateInCell() when the call should
not modify the contents of the original cell."
You need to use the result of this call, a CellValue instance, like the POI
example page shows, as this call doesn't save the formula evaluation result
in the cell.
Or, you can use
evaluateFormulaCell(Cell)
instead. As the Javadoc says, this method updates the cached value but
leaves the cell as a formula cell. Use the returned CellType to know which
cell getter to use when reading the calculated value.
Or you can call evaluateAll() which performs evaluateFormulaCell() for all
formula cells in the workbook at once, and then read the updated results.
It looks like you are just confusing the various methods and their
behaviors.
On Wed, Apr 10, 2019 at 1:52 PM Kumar Thotapally <kt...@emmes.com>
wrote:
> Greg,
>
> Here is my code snippet and its output:
>
>
> sheet.getRow(2).getCell(5).setCellValue(examDate); // 04/10/2019
>
> sheet.getRow(2).getCell(7).setCellValue(birthDate); // 01/01/1980
> // output computed AGE value from cell B5 - Expected result: 39
> FormulaEvaluator evaluator =
> wb.getCreationHelper().createFormulaEvaluator();
> Cell c = sheet.getRow(4).getCell(1);
> CellValue cellValue = evaluator.evaluate(c);
> evaluator.evaluate(c);
> System.out.println("Cell Value (String) : "+
> cellValue.toString());
> System.out.println("Cell Value (Number) : "+
> cellValue.getNumberValue());
> System.out.println("Formula : " +
> c.getCellFormula());
> System.out.println("CachedFormulaResultType : " +
> c.getCachedFormulaResultType());
> System.out.println("Numeric value : " +
> c.getNumericCellValue());
>
> And the output is :
>
> Cell Value (String) : org.apache.poi.ss.usermodel.CellValue [#VALUE!]
> Cell Value (Number) : 0.0
> Formula : 'Unformatted Values'!AD13
> CachedFormulaResultType : NUMERIC
> Numeric value : 0.0
>
> 'Unformatted Values' is the name of a hidden sheet in the workbook
>
>
>
> --
> Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html
Re: Cell Calculations by reference to other cells in hidden sheets
within the same workbook
Posted by Kumar Thotapally <kt...@emmes.com>.
Greg,
Here is my code snippet and its output:
sheet.getRow(2).getCell(5).setCellValue(examDate); // 04/10/2019
sheet.getRow(2).getCell(7).setCellValue(birthDate); // 01/01/1980
// output computed AGE value from cell B5 - Expected result: 39
FormulaEvaluator evaluator =
wb.getCreationHelper().createFormulaEvaluator();
Cell c = sheet.getRow(4).getCell(1);
CellValue cellValue = evaluator.evaluate(c);
evaluator.evaluate(c);
System.out.println("Cell Value (String) : "+ cellValue.toString());
System.out.println("Cell Value (Number) : "+
cellValue.getNumberValue());
System.out.println("Formula : " + c.getCellFormula());
System.out.println("CachedFormulaResultType : " +
c.getCachedFormulaResultType());
System.out.println("Numeric value : " + c.getNumericCellValue());
And the output is :
Cell Value (String) : org.apache.poi.ss.usermodel.CellValue [#VALUE!]
Cell Value (Number) : 0.0
Formula : 'Unformatted Values'!AD13
CachedFormulaResultType : NUMERIC
Numeric value : 0.0
'Unformatted Values' is the name of a hidden sheet in the workbook
--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html
Re: Cell Calculations by reference to other cells in hidden sheets
within the same workbook
Posted by Greg Woolsey <gr...@gmail.com>.
Did you explicitly evaluate the cell? The example page on the website
shows how to do that. The cached numeric value would only be useful
_after_ you explicitly call evaluate(cell). At that point you also would
be able to just use the evaluation result directly.
On Wed, Apr 10, 2019 at 10:30 AM Kumar Thotapally <kt...@emmes.com>
wrote:
> I used getCachedFormulaResultType() and it returned the type to be Numeric
> and numeric cell value returned a value 0.0 which is incorrect.
>
> When I get string value of the cell, it is returning reference to
> calculation formula on a separate sheet within the workbook.
>
> In this particular spreadsheet, I know all the cells that are of type
> formula. I am having difficulty getting the formula evaluated.
>
>
>
> --
> Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html
Re: Cell Calculations by reference to other cells in hidden sheets
within the same workbook
Posted by Kumar Thotapally <kt...@emmes.com>.
I used getCachedFormulaResultType() and it returned the type to be Numeric
and numeric cell value returned a value 0.0 which is incorrect.
When I get string value of the cell, it is returning reference to
calculation formula on a separate sheet within the workbook.
In this particular spreadsheet, I know all the cells that are of type
formula. I am having difficulty getting the formula evaluated.
--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html
Re: Cell Calculations by reference to other cells in hidden sheets
within the same workbook
Posted by Greg Woolsey <gr...@gmail.com>.
I see your problem. XSSFCell.toString() returns the formula String for a
formula cell type, not the cached cell value.
With POI, you need your own method that does a bit more work based on the
reported CellType for each cell. Even more if you want the value formatted
as it would appear in Excel.
For CellType = FORMULA, you need to then look at
Cell.getCachedFormulaResultType() and then return the desired
representation of the appropriate get*CellValue() method.
On Mon, Apr 8, 2019 at 8:18 AM Kumar Thotapally <kt...@emmes.com>
wrote:
> Greg, thank you.
>
> I have tried evaluate(cell) as well as evaluateAll() and I am still getting
> the reference to another cell on a hidden sheet instead of calculated
> value.
>
> Following is my program:
>
> public class POICalculator {
> private DataFormatter formatter;
>
> public static void main(String[] args) {
> // To fill cell values in calculator spreadsheet and
> // obtain computed cell value
> InputStream ExcelFileToRead;
> try {
> ExcelFileToRead = new
> FileInputStream("C:\\temp\\XLS\\NPCalculatorBlack.xls");
> HSSFWorkbook wb = new HSSFWorkbook(ExcelFileToRead);
> HSSFWorkbook test = new HSSFWorkbook();
> HSSFSheet sheet = wb.getSheetAt(0);
> HSSFRow row;
> HSSFCell cell;
> String examDate = "04/02/2019";
> String birthDate = "01/01/1980";
> FormulaEvaluator mainWorkbookEvaluator =
> wb.getCreationHelper().createFormulaEvaluator();
> Map<String, FormulaEvaluator> workbooks = new HashMap<String,
> FormulaEvaluator>();
> // Add this workbook
> /* workbooks.put(wb.getSheetName(0), mainWorkbookEvaluator);
> workbooks.put(wb.getSheetName(1), mainWorkbookEvaluator);
> workbooks.put(wb.getSheetName(2), mainWorkbookEvaluator);
> workbooks.put(wb.getSheetName(3), mainWorkbookEvaluator);
> workbooks.put(wb.getSheetName(4), mainWorkbookEvaluator);
> workbooks.put(wb.getSheetName(5), mainWorkbookEvaluator);
> workbooks.put(wb.getSheetName(6), mainWorkbookEvaluator);
> mainWorkbookEvaluator.setupReferencedWorkbooks(workbooks);
> */
> mainWorkbookEvaluator.evaluateAll();
> // input cell value for F3
> sheet.getRow(2).getCell(5).setCellValue(examDate);
> // input cell value for H3
> sheet.getRow(2).getCell(7).setCellValue(birthDate);
> HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
> *wb.getCreationHelper().createFormulaEvaluator().evaluateAll();
> cell = sheet.getRow(4).getCell(1);
> wb.getCreationHelper().createFormulaEvaluator().evaluate(cell);*
>
> // output computed AGE value from cell B5 - Expected result: 39
> String age = sheet.getRow(4).getCell(1).toString();
> System.out.println(age);
> } catch (Exception e) {
> e.printStackTrace();
> }
> }
> }
> Output is shown as:
>
> *'Unformatted Values'!AD13*
> Where 'Unformatted Values' is a hidden sheet within the same workbook.
>
>
>
> --
> Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html
Re: Cell Calculations by reference to other cells in hidden sheets
within the same workbook
Posted by Kumar Thotapally <kt...@emmes.com>.
Greg, thank you.
I have tried evaluate(cell) as well as evaluateAll() and I am still getting
the reference to another cell on a hidden sheet instead of calculated value.
Following is my program:
public class POICalculator {
private DataFormatter formatter;
public static void main(String[] args) {
// To fill cell values in calculator spreadsheet and
// obtain computed cell value
InputStream ExcelFileToRead;
try {
ExcelFileToRead = new
FileInputStream("C:\\temp\\XLS\\NPCalculatorBlack.xls");
HSSFWorkbook wb = new HSSFWorkbook(ExcelFileToRead);
HSSFWorkbook test = new HSSFWorkbook();
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row;
HSSFCell cell;
String examDate = "04/02/2019";
String birthDate = "01/01/1980";
FormulaEvaluator mainWorkbookEvaluator =
wb.getCreationHelper().createFormulaEvaluator();
Map<String, FormulaEvaluator> workbooks = new HashMap<String,
FormulaEvaluator>();
// Add this workbook
/* workbooks.put(wb.getSheetName(0), mainWorkbookEvaluator);
workbooks.put(wb.getSheetName(1), mainWorkbookEvaluator);
workbooks.put(wb.getSheetName(2), mainWorkbookEvaluator);
workbooks.put(wb.getSheetName(3), mainWorkbookEvaluator);
workbooks.put(wb.getSheetName(4), mainWorkbookEvaluator);
workbooks.put(wb.getSheetName(5), mainWorkbookEvaluator);
workbooks.put(wb.getSheetName(6), mainWorkbookEvaluator);
mainWorkbookEvaluator.setupReferencedWorkbooks(workbooks);
*/
mainWorkbookEvaluator.evaluateAll();
// input cell value for F3
sheet.getRow(2).getCell(5).setCellValue(examDate);
// input cell value for H3
sheet.getRow(2).getCell(7).setCellValue(birthDate);
HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
*wb.getCreationHelper().createFormulaEvaluator().evaluateAll();
cell = sheet.getRow(4).getCell(1);
wb.getCreationHelper().createFormulaEvaluator().evaluate(cell);*
// output computed AGE value from cell B5 - Expected result: 39
String age = sheet.getRow(4).getCell(1).toString();
System.out.println(age);
} catch (Exception e) {
e.printStackTrace();
}
}
}
Output is shown as:
*'Unformatted Values'!AD13*
Where 'Unformatted Values' is a hidden sheet within the same workbook.
--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html
Re: Cell Calculations by reference to other cells in hidden sheets
within the same workbook
Posted by Greg Woolsey <gr...@gmail.com>.
Hidden sheets aren't external, they are just marked to not be displayed in
a UI. POI doesn't care if sheets are hidden or not when calculating
formulas, I do this all the time.
How are you looking for the calculated value of the cell? What you are
getting back is a formula value, which is just a reference in this case to
another cell, it looks like. That's a typical result from
Cell.getCellFormula() or similar.
What you need is
wb.getCreationHelper().createFormulaEvaluator().evaluate(cell);
On Fri, Apr 5, 2019 at 1:33 PM Kumar Thotapally <kt...@emmes.com>
wrote:
> I am re-posting this as my previous post was not properly formatted. I
> apologize.
>
> Hi,
>
> I have a Excel workbook/spreadsheet with 1 visible sheet and 6 hidden
> sheets. Formula cells on visible sheet (Sheet 0) are pointing to cells on
> hidden sheets within the same workbook.One of the formulas is to calculate
> age based on two dates.
>
> When I try to retrieve calculated value of cell containing age, I am
> getting
> a string,
>
> "'Unformatted Values'!AD13"
>
> where 'Unformatted Values' is one of the hidden sheets. I have seen
> methods
> to link external workbooks and to evaluate all formulas using
>
> FormulaEvaluator mainWorkbookEvaluator =
> wb.getCreationHelper().createFormulaEvaluator();
> Map<String, FormulaEvaluator> workbooks = new HashMap<String,
> FormulaEvaluator>();
> workbooks.put(wb.getSheetName(0), mainWorkbookEvaluator);
> workbooks.put(wb.getSheetName(1), mainWorkbookEvaluator);
> workbooks.put(wb.getSheetName(2), mainWorkbookEvaluator);
>
> etc., when trying to setup referenced workbooks using,
>
> mainWorkbookEvaluator.setupReferencedWorkbooks(workbooks);
> I am getting the following exception about attempting to register same
> workbook under different names.
> java.lang.IllegalArgumentException: Attempted to register same workbook
> under names 'Unformatted Values' and 'NP T-Scores' at
>
> org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.(CollaboratingWorkbooksEnvironment.java:114)
> at
>
> org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.setup(CollaboratingWorkbooksEnvironment.java:75)
> at
>
> org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.setupFormulaEvaluator(CollaboratingWorkbooksEnvironment.java:89)
> at
>
> org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.setupReferencedWorkbooks(HSSFFormulaEvaluator.java:103)
> at com.emmes.hbb.POICalculator.main(POICalculator.java:61)
>
> How can I create a link/reference between visible sheet and all the other
> hidden sheets for evaluating formulas?
>
> Thanks for your help.
>
>
>
> --
> Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html
Re: Cell Calculations by reference to other cells in hidden sheets
within the same workbook
Posted by Kumar Thotapally <kt...@emmes.com>.
I am re-posting this as my previous post was not properly formatted. I
apologize.
Hi,
I have a Excel workbook/spreadsheet with 1 visible sheet and 6 hidden
sheets. Formula cells on visible sheet (Sheet 0) are pointing to cells on
hidden sheets within the same workbook.One of the formulas is to calculate
age based on two dates.
When I try to retrieve calculated value of cell containing age, I am getting
a string,
"'Unformatted Values'!AD13"
where 'Unformatted Values' is one of the hidden sheets. I have seen methods
to link external workbooks and to evaluate all formulas using
FormulaEvaluator mainWorkbookEvaluator =
wb.getCreationHelper().createFormulaEvaluator();
Map<String, FormulaEvaluator> workbooks = new HashMap<String,
FormulaEvaluator>();
workbooks.put(wb.getSheetName(0), mainWorkbookEvaluator);
workbooks.put(wb.getSheetName(1), mainWorkbookEvaluator);
workbooks.put(wb.getSheetName(2), mainWorkbookEvaluator);
etc., when trying to setup referenced workbooks using,
mainWorkbookEvaluator.setupReferencedWorkbooks(workbooks);
I am getting the following exception about attempting to register same
workbook under different names.
java.lang.IllegalArgumentException: Attempted to register same workbook
under names 'Unformatted Values' and 'NP T-Scores' at
org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.(CollaboratingWorkbooksEnvironment.java:114)
at
org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.setup(CollaboratingWorkbooksEnvironment.java:75)
at
org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.setupFormulaEvaluator(CollaboratingWorkbooksEnvironment.java:89)
at
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.setupReferencedWorkbooks(HSSFFormulaEvaluator.java:103)
at com.emmes.hbb.POICalculator.main(POICalculator.java:61)
How can I create a link/reference between visible sheet and all the other
hidden sheets for evaluating formulas?
Thanks for your help.
--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html