evaluate(cell) vs evaluateInCell(cell) difference in poi library?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
I am working on poi library. I want to evaluate some formula cell which is more than 255 columns.
XSSFRow row219 = sheet.createRow(219);
XSSFCell vv0 = (XSSFCell) row219.createCell(255);
vv0.setCellValue(40);
XSSFCell vv = (XSSFCell) row219.createCell(256);
vv.setCellValue(70);
XSSFCell cell = sheet.createRow(1).createCell(1);
cell.setCellFormula("OFFSET(IV220,0,1)");
XSSFFormulaEvaluator evaluator =
(XSSFFormulaEvaluator) workbook.getCreationHelper().createFormulaEvaluator();
evaluator.evaluateInCell(cell);
but it gives result #REF! in cell. but if I use below code I am getting my expected result
XSSFRow row219 = sheet.createRow(219);
XSSFCell vv0 = (XSSFCell) row219.createCell(255);
vv0.setCellValue(40);
XSSFCell vv = (XSSFCell) row219.createCell(256);
vv.setCellValue(70);
XSSFCell cell = sheet.createRow(1).createCell(1);
cell.setCellFormula("OFFSET(IV220,0,1)");
XSSFFormulaEvaluator evaluator =
(XSSFFormulaEvaluator) workbook.getCreationHelper().createFormulaEvaluator();
evaluator.evaluate(cell);
Then I am getting my expected value 70.
I want to know the difference between evaluator.evaluateInCell(cell); and evaluator.evaluate(cell); though it is using same abstract class inside poi library
protected abstract CellValue evaluateFormulaCellValue(Cell var1);
and same implementation of this abstract class BaseXSSFFormulaEvaluator but why result is differ ?
NB: I am using poi library 3.17
java excel apache-poi
asked Nov 29 '18 at 6:38
flopcoderflopcoder
787513
add a comment |
0
I am working on poi library. I want to evaluate some formula cell which is more than 255 columns.
XSSFRow row219 = sheet.createRow(219);
XSSFCell vv0 = (XSSFCell) row219.createCell(255);
vv0.setCellValue(40);
XSSFCell vv = (XSSFCell) row219.createCell(256);
vv.setCellValue(70);
XSSFCell cell = sheet.createRow(1).createCell(1);
cell.setCellFormula("OFFSET(IV220,0,1)");
XSSFFormulaEvaluator evaluator =
(XSSFFormulaEvaluator) workbook.getCreationHelper().createFormulaEvaluator();
evaluator.evaluateInCell(cell);
but it gives result #REF! in cell. but if I use below code I am getting my expected result
XSSFRow row219 = sheet.createRow(219);
XSSFCell vv0 = (XSSFCell) row219.createCell(255);
vv0.setCellValue(40);
XSSFCell vv = (XSSFCell) row219.createCell(256);
vv.setCellValue(70);
XSSFCell cell = sheet.createRow(1).createCell(1);
cell.setCellFormula("OFFSET(IV220,0,1)");
XSSFFormulaEvaluator evaluator =
(XSSFFormulaEvaluator) workbook.getCreationHelper().createFormulaEvaluator();
evaluator.evaluate(cell);
Then I am getting my expected value 70.
I want to know the difference between evaluator.evaluateInCell(cell); and evaluator.evaluate(cell); though it is using same abstract class inside poi library
protected abstract CellValue evaluateFormulaCellValue(Cell var1);
and same implementation of this abstract class BaseXSSFFormulaEvaluator but why result is differ ?
NB: I am using poi library 3.17
java excel apache-poi
asked Nov 29 '18 at 6:38
flopcoderflopcoder
787513
1
Where are you getting the expected value? The difference betweenevaluateInCellandevaluateis thatevaluateInCellreplaces the formula with the evaluated value. As it is documented, it: "puts the formula result back into the cell, in place of the old formula." TheFormlaEvaluatordefinitelly is wrong when evaluatingOFFSETforXSSF. But if you are opening the sheet in Excel then, afterevaluateInCellthe wrong evaluated value is in the cell but afterevaluatethe formula remains in the cell and will be reevaluated from Excel's GUI. So Excel evaluates correct then.
– Axel Richter
Nov 29 '18 at 8:19
I know that "evaluateInCell and evaluate is that evaluateInCell replaces the formula with the evaluated value. As it is documented, it: "puts the formula result back into the cell, in place of the old formula." But I am wondered when evaluateInCell call and coulumn number more than 255 Its not return value. Its Returns #REF! but if column number <255 it gives correct result
– flopcoder
Nov 29 '18 at 8:50
Theapache poiFormlaEvaluatordefinitely is wrong when evaluating OFFSET for XSSF since it uses the old limits of Excel 2003 for column count and row count. But bothevaluateInCellas well asevaluateevaluate wrong.
– Axel Richter
Nov 29 '18 at 10:01
add a comment |
0
0
0
I am working on poi library. I want to evaluate some formula cell which is more than 255 columns.
XSSFRow row219 = sheet.createRow(219);
XSSFCell vv0 = (XSSFCell) row219.createCell(255);
vv0.setCellValue(40);
XSSFCell vv = (XSSFCell) row219.createCell(256);
vv.setCellValue(70);
XSSFCell cell = sheet.createRow(1).createCell(1);
cell.setCellFormula("OFFSET(IV220,0,1)");
XSSFFormulaEvaluator evaluator =
(XSSFFormulaEvaluator) workbook.getCreationHelper().createFormulaEvaluator();
evaluator.evaluateInCell(cell);
but it gives result #REF! in cell. but if I use below code I am getting my expected result
XSSFRow row219 = sheet.createRow(219);
XSSFCell vv0 = (XSSFCell) row219.createCell(255);
vv0.setCellValue(40);
XSSFCell vv = (XSSFCell) row219.createCell(256);
vv.setCellValue(70);
XSSFCell cell = sheet.createRow(1).createCell(1);
cell.setCellFormula("OFFSET(IV220,0,1)");
XSSFFormulaEvaluator evaluator =
(XSSFFormulaEvaluator) workbook.getCreationHelper().createFormulaEvaluator();
evaluator.evaluate(cell);
Then I am getting my expected value 70.
I want to know the difference between evaluator.evaluateInCell(cell); and evaluator.evaluate(cell); though it is using same abstract class inside poi library
protected abstract CellValue evaluateFormulaCellValue(Cell var1);
and same implementation of this abstract class BaseXSSFFormulaEvaluator but why result is differ ?
NB: I am using poi library 3.17
java excel apache-poi
asked Nov 29 '18 at 6:38
flopcoderflopcoder
787513
I am working on poi library. I want to evaluate some formula cell which is more than 255 columns.
XSSFRow row219 = sheet.createRow(219);
XSSFCell vv0 = (XSSFCell) row219.createCell(255);
vv0.setCellValue(40);
XSSFCell vv = (XSSFCell) row219.createCell(256);
vv.setCellValue(70);
XSSFCell cell = sheet.createRow(1).createCell(1);
cell.setCellFormula("OFFSET(IV220,0,1)");
XSSFFormulaEvaluator evaluator =
(XSSFFormulaEvaluator) workbook.getCreationHelper().createFormulaEvaluator();
evaluator.evaluateInCell(cell);
but it gives result #REF! in cell. but if I use below code I am getting my expected result
XSSFRow row219 = sheet.createRow(219);
XSSFCell vv0 = (XSSFCell) row219.createCell(255);
vv0.setCellValue(40);
XSSFCell vv = (XSSFCell) row219.createCell(256);
vv.setCellValue(70);
XSSFCell cell = sheet.createRow(1).createCell(1);
cell.setCellFormula("OFFSET(IV220,0,1)");
XSSFFormulaEvaluator evaluator =
(XSSFFormulaEvaluator) workbook.getCreationHelper().createFormulaEvaluator();
evaluator.evaluate(cell);
Then I am getting my expected value 70.
I want to know the difference between evaluator.evaluateInCell(cell); and evaluator.evaluate(cell); though it is using same abstract class inside poi library
protected abstract CellValue evaluateFormulaCellValue(Cell var1);
and same implementation of this abstract class BaseXSSFFormulaEvaluator but why result is differ ?
NB: I am using poi library 3.17
java excel apache-poi
java excel apache-poi
asked Nov 29 '18 at 6:38
flopcoderflopcoder
787513
asked Nov 29 '18 at 6:38
flopcoderflopcoder
787513
edited Nov 29 '18 at 6:45
flopcoder
asked Nov 29 '18 at 6:38
flopcoderflopcoder
787513
asked Nov 29 '18 at 6:38
flopcoderflopcoder
787513
asked Nov 29 '18 at 6:38
flopcoderflopcoder
787513
787513
1
Where are you getting the expected value? The difference betweenevaluateInCellandevaluateis thatevaluateInCellreplaces the formula with the evaluated value. As it is documented, it: "puts the formula result back into the cell, in place of the old formula." TheFormlaEvaluatordefinitelly is wrong when evaluatingOFFSETforXSSF. But if you are opening the sheet in Excel then, afterevaluateInCellthe wrong evaluated value is in the cell but afterevaluatethe formula remains in the cell and will be reevaluated from Excel's GUI. So Excel evaluates correct then.
– Axel Richter
Nov 29 '18 at 8:19
I know that "evaluateInCell and evaluate is that evaluateInCell replaces the formula with the evaluated value. As it is documented, it: "puts the formula result back into the cell, in place of the old formula." But I am wondered when evaluateInCell call and coulumn number more than 255 Its not return value. Its Returns #REF! but if column number <255 it gives correct result
– flopcoder
Nov 29 '18 at 8:50
Theapache poiFormlaEvaluatordefinitely is wrong when evaluating OFFSET for XSSF since it uses the old limits of Excel 2003 for column count and row count. But bothevaluateInCellas well asevaluateevaluate wrong.
– Axel Richter
Nov 29 '18 at 10:01
add a comment |
1
Where are you getting the expected value? The difference betweenevaluateInCellandevaluateis thatevaluateInCellreplaces the formula with the evaluated value. As it is documented, it: "puts the formula result back into the cell, in place of the old formula." TheFormlaEvaluatordefinitelly is wrong when evaluatingOFFSETforXSSF. But if you are opening the sheet in Excel then, afterevaluateInCellthe wrong evaluated value is in the cell but afterevaluatethe formula remains in the cell and will be reevaluated from Excel's GUI. So Excel evaluates correct then.
– Axel Richter
Nov 29 '18 at 8:19
I know that "evaluateInCell and evaluate is that evaluateInCell replaces the formula with the evaluated value. As it is documented, it: "puts the formula result back into the cell, in place of the old formula." But I am wondered when evaluateInCell call and coulumn number more than 255 Its not return value. Its Returns #REF! but if column number <255 it gives correct result
– flopcoder
Nov 29 '18 at 8:50
Theapache poiFormlaEvaluatordefinitely is wrong when evaluating OFFSET for XSSF since it uses the old limits of Excel 2003 for column count and row count. But bothevaluateInCellas well asevaluateevaluate wrong.
– Axel Richter
Nov 29 '18 at 10:01
1
1
Where are you getting the expected value? The difference between
evaluateInCell and evaluate is that evaluateInCell replaces the formula with the evaluated value. As it is documented, it: "puts the formula result back into the cell, in place of the old formula." The FormlaEvaluator definitelly is wrong when evaluating OFFSET for XSSF. But if you are opening the sheet in Excel then, after evaluateInCell the wrong evaluated value is in the cell but after evaluate the formula remains in the cell and will be reevaluated from Excel's GUI. So Excel evaluates correct then.– Axel Richter
Nov 29 '18 at 8:19
Where are you getting the expected value? The difference between
evaluateInCell and evaluate is that evaluateInCell replaces the formula with the evaluated value. As it is documented, it: "puts the formula result back into the cell, in place of the old formula." The FormlaEvaluator definitelly is wrong when evaluating OFFSET for XSSF. But if you are opening the sheet in Excel then, after evaluateInCell the wrong evaluated value is in the cell but after evaluate the formula remains in the cell and will be reevaluated from Excel's GUI. So Excel evaluates correct then.– Axel Richter
Nov 29 '18 at 8:19
I know that "evaluateInCell and evaluate is that evaluateInCell replaces the formula with the evaluated value. As it is documented, it: "puts the formula result back into the cell, in place of the old formula." But I am wondered when evaluateInCell call and coulumn number more than 255 Its not return value. Its Returns #REF! but if column number <255 it gives correct result
– flopcoder
Nov 29 '18 at 8:50
I know that "evaluateInCell and evaluate is that evaluateInCell replaces the formula with the evaluated value. As it is documented, it: "puts the formula result back into the cell, in place of the old formula." But I am wondered when evaluateInCell call and coulumn number more than 255 Its not return value. Its Returns #REF! but if column number <255 it gives correct result
– flopcoder
Nov 29 '18 at 8:50
The
apache poi FormlaEvaluator definitely is wrong when evaluating OFFSET for XSSF since it uses the old limits of Excel 2003 for column count and row count. But both evaluateInCell as well as evaluate evaluate wrong.– Axel Richter
Nov 29 '18 at 10:01
The
apache poi FormlaEvaluator definitely is wrong when evaluating OFFSET for XSSF since it uses the old limits of Excel 2003 for column count and row count. But both evaluateInCell as well as evaluate evaluate wrong.– Axel Richter
Nov 29 '18 at 10:01
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53533193%2fevaluatecell-vs-evaluateincellcell-difference-in-poi-library%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53533193%2fevaluatecell-vs-evaluateincellcell-difference-in-poi-library%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Where are you getting the expected value? The difference between
evaluateInCellandevaluateis thatevaluateInCellreplaces the formula with the evaluated value. As it is documented, it: "puts the formula result back into the cell, in place of the old formula." TheFormlaEvaluatordefinitelly is wrong when evaluatingOFFSETforXSSF. But if you are opening the sheet in Excel then, afterevaluateInCellthe wrong evaluated value is in the cell but afterevaluatethe formula remains in the cell and will be reevaluated from Excel's GUI. So Excel evaluates correct then.– Axel Richter
Nov 29 '18 at 8:19
I know that "evaluateInCell and evaluate is that evaluateInCell replaces the formula with the evaluated value. As it is documented, it: "puts the formula result back into the cell, in place of the old formula." But I am wondered when evaluateInCell call and coulumn number more than 255 Its not return value. Its Returns #REF! but if column number <255 it gives correct result
– flopcoder
Nov 29 '18 at 8:50
The
apache poiFormlaEvaluatordefinitely is wrong when evaluating OFFSET for XSSF since it uses the old limits of Excel 2003 for column count and row count. But bothevaluateInCellas well asevaluateevaluate wrong.– Axel Richter
Nov 29 '18 at 10:01