You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "chaitanya.gorugontula@kkr.com" <ch...@kkr.com> on 2012/04/19 17:06:11 UTC
Detect External Reference in Formula
Hi,
We have an application which processes Excel Workbooks sent by clients and
saves the data from the sheets into DB. As part of that process, we try to
perform multiple validations on the worksheets and one of the validations is
detecting if any of the formulae in the sheet are actually referring to
cells outside of the workbook available to the application (External
Reference).
The way we are accomplishing this is by iterating all the cells in all the
sheets and see if any cell is formula cell and then get the formula text
from the cell... using "cell.getCellFormula()" ... and check if there is any
text like ".xls" / ".csv" in the text.
This works fine for .xls files (HSSF Sheets) but recently we upgraded POI to
SS user model (version 3.7) to support both .xls and .xlsx files and since
we have noticed that...
for a formula with an external reference like the below in .xlsx files ...
=OFFSET('C:\Test\[1.xls]Monthly Consolidated'!$B458,0,'C:\[1.xls]Monthly
Consolidated'!$C$1)
cell.getCellFormula() call returns...
OFFSET('[1]Monthly Consolidated'!$B458,0,'[1]Monthly Consolidated'!$C$1).
/After reading some posts related to formula evaluation in xlsx formatted
files, I understand that Excel 2007/2010 format excel files have a different
way of saving the reference to excel sheets, which can verified by changing
extension of .xlsx to .zip file and checking the folders.
*What I am looking for is if there is anyway/method/steps to figure out if
the formula that I am looking at is actually having an external formula
reference or not, specifically in xlsx files.*
I tried to approach the problem by trying to evaluate the cell and look at
the exception to see if it gives me a clue in solving the problem but what I
have observed is that the following exception gets thrown
java.lang.IllegalArgumentException: Invalid sheetIndex: -1.
at
org.apache.poi.ss.formula.SheetRefEvaluator.<init>(SheetRefEvaluator.java:35)
at
org.apache.poi.ss.formula.OperationEvaluationContext.createExternSheetRefEvaluator(OperationEvaluationContext.java:97)
at
org.apache.poi.ss.formula.OperationEvaluationContext.getRef3DEval(OperationEvaluationContext.java:249)
at
org.apache.poi.ss.formula.WorkbookEvaluator.getEvalForPtg(WorkbookEvaluator.java:572)
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:458)
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:279)
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:618)
at
org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:47)
at
org.apache.poi.ss.formula.LazyRefEval.getInnerValueEval(LazyRefEval.java:44)
at
org.apache.poi.hssf.record.formula.eval.OperandResolver.getSingleValue(OperandResolver.java:62)
at
org.apache.poi.hssf.record.formula.eval.TwoOperandNumericOperation.singleOperandEvaluate(TwoOperandNumericOperation.java:29)
at
org.apache.poi.hssf.record.formula.eval.TwoOperandNumericOperation.evaluate(TwoOperandNumericOperation.java:35)
at
org.apache.poi.hssf.record.formula.functions.Fixed2ArgFunction.evaluate(Fixed2ArgFunction.java:33)
at
org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:119)
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:456)
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:279)
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:221)
at
org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCellValue(XSSFFormulaEvaluator.java:257)/
not only for the cell that has external reference in the formula but also
any other formulae in the sheet that have a reference to the cell that
contains external formula. Hence, I cannot isolate the cells that have
external reference.
Any guidance will be very helpful ...
Appreicate your time.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Detect-External-Reference-in-Formula-tp5652011p5652011.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Detect External Reference in Formula
Posted by "chaitanya.gorugontula@kkr.com" <ch...@kkr.com>.
May be conventionally not. But that does not guarantee that nobody will use
[] anywhere else in other formulae to mean something else... as I had
demonstrated with concatenate() example.
It would have been very helpful if POI was to throw some exception/set some
flag to indicate that it is trying to process an external reference. Not
sure if regular expression route is the only thing left out... which to me
does not look like will solve the problem without loopholes.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Detect-External-Reference-in-Formula-tp5652011p5652264.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Detect External Reference in Formula
Posted by Mark Coleman <m....@uglyduckling.nl>.
Are the square brackets ([]) actually used for anything other than
signifying an external link?
Mark
On 19 April 2012 17:54, chaitanya.gorugontula@kkr.com <
chaitanya.gorugontula@kkr.com> wrote:
> Thanks Mark for the quick response.
>
> I did consider going the java regular expression route but then there might
> be corner case of the regex matching a string within a normal formula...
> which is actually not external... something like the below formula...
>
> =CONCATENATE("abc","def","'[1]Monthly Consolidated'!$C$1")
>
> As you mentioned, I will try to see if I can fine tune regex matching to
> see
> if I can come up with a pattern but if you look at the example above... I
> took exactly the example of formula text returned for an external formula
> and used it in a simple formula like concatenate(). Though it can be
> debated
> that the above formula is not very useful, I think you follow my broader
> point about not being able to identify anything in the formula that seems
> to
> be very unique to have a proper regular expression.
>
> Really appreciate your time... and let me know if you think you have a
> regular expression pattern which might resolve only external formulae
> uniquely.
>
> Thanks.
>
>
> --
> View this message in context:
> http://apache-poi.1045710.n5.nabble.com/Detect-External-Reference-in-Formula-tp5652011p5652149.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
--
Mark Coleman
uGly Duckling B.V.
Burgemeester le Fevre de Montignylaan 30
3055LG Rotterdam, the Netherlands
KvK nummer: 52272125
BTW nummer: NL850371570B.01
Rabobank: 14.68.33.473
E: m.coleman@uglyduckling.nl
M: +31 (0) 646347972
W: http://uGlyDuckling.nl <http://www.uglyduckling.nl/>
Re: Detect External Reference in Formula
Posted by "chaitanya.gorugontula@kkr.com" <ch...@kkr.com>.
Thanks Mark for the quick response.
I did consider going the java regular expression route but then there might
be corner case of the regex matching a string within a normal formula...
which is actually not external... something like the below formula...
=CONCATENATE("abc","def","'[1]Monthly Consolidated'!$C$1")
As you mentioned, I will try to see if I can fine tune regex matching to see
if I can come up with a pattern but if you look at the example above... I
took exactly the example of formula text returned for an external formula
and used it in a simple formula like concatenate(). Though it can be debated
that the above formula is not very useful, I think you follow my broader
point about not being able to identify anything in the formula that seems to
be very unique to have a proper regular expression.
Really appreciate your time... and let me know if you think you have a
regular expression pattern which might resolve only external formulae
uniquely.
Thanks.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Detect-External-Reference-in-Formula-tp5652011p5652149.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Detect External Reference in Formula
Posted by Mark Coleman <m....@uglyduckling.nl>.
Hey Chaitanya,
I'm dealing with a similar thing right now. I also wanted to check for
external links and used the same method as you, but I only need to support
xls files. I did however also want to pull out the paths, file names and
cell references.
To do this I used java regex's. In your example the presence of \'\[.+\]
(or something like that) would indicate an external file. I haven't check
this because I'm mobile but your could use this helpful
website<http://www.regexplanet.com/advanced/java/index.html>to confirm
the correct regex before taking the plunge.
Hope that helps!
Mark
On 19 April 2012 17:06, chaitanya.gorugontula@kkr.com <
chaitanya.gorugontula@kkr.com> wrote:
> Hi,
>
> We have an application which processes Excel Workbooks sent by clients and
> saves the data from the sheets into DB. As part of that process, we try to
> perform multiple validations on the worksheets and one of the validations
> is
> detecting if any of the formulae in the sheet are actually referring to
> cells outside of the workbook available to the application (External
> Reference).
>
> The way we are accomplishing this is by iterating all the cells in all the
> sheets and see if any cell is formula cell and then get the formula text
> from the cell... using "cell.getCellFormula()" ... and check if there is
> any
> text like ".xls" / ".csv" in the text.
>
> This works fine for .xls files (HSSF Sheets) but recently we upgraded POI
> to
> SS user model (version 3.7) to support both .xls and .xlsx files and since
> we have noticed that...
> for a formula with an external reference like the below in .xlsx files ...
>
> =OFFSET('C:\Test\[1.xls]Monthly Consolidated'!$B458,0,'C:\[1.xls]Monthly
> Consolidated'!$C$1)
>
> cell.getCellFormula() call returns...
>
> OFFSET('[1]Monthly Consolidated'!$B458,0,'[1]Monthly Consolidated'!$C$1).
>
> /After reading some posts related to formula evaluation in xlsx formatted
> files, I understand that Excel 2007/2010 format excel files have a
> different
> way of saving the reference to excel sheets, which can verified by changing
> extension of .xlsx to .zip file and checking the folders.
>
> *What I am looking for is if there is anyway/method/steps to figure out if
> the formula that I am looking at is actually having an external formula
> reference or not, specifically in xlsx files.*
>
> I tried to approach the problem by trying to evaluate the cell and look at
> the exception to see if it gives me a clue in solving the problem but what
> I
> have observed is that the following exception gets thrown
>
> java.lang.IllegalArgumentException: Invalid sheetIndex: -1.
> at
>
> org.apache.poi.ss.formula.SheetRefEvaluator.<init>(SheetRefEvaluator.java:35)
> at
>
> org.apache.poi.ss.formula.OperationEvaluationContext.createExternSheetRefEvaluator(OperationEvaluationContext.java:97)
> at
>
> org.apache.poi.ss.formula.OperationEvaluationContext.getRef3DEval(OperationEvaluationContext.java:249)
> at
>
> org.apache.poi.ss.formula.WorkbookEvaluator.getEvalForPtg(WorkbookEvaluator.java:572)
> at
>
> org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:458)
> at
>
> org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:279)
> at
>
> org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:618)
> at
>
> org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:47)
> at
>
> org.apache.poi.ss.formula.LazyRefEval.getInnerValueEval(LazyRefEval.java:44)
> at
>
> org.apache.poi.hssf.record.formula.eval.OperandResolver.getSingleValue(OperandResolver.java:62)
> at
>
> org.apache.poi.hssf.record.formula.eval.TwoOperandNumericOperation.singleOperandEvaluate(TwoOperandNumericOperation.java:29)
> at
>
> org.apache.poi.hssf.record.formula.eval.TwoOperandNumericOperation.evaluate(TwoOperandNumericOperation.java:35)
> at
>
> org.apache.poi.hssf.record.formula.functions.Fixed2ArgFunction.evaluate(Fixed2ArgFunction.java:33)
> at
>
> org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:119)
> at
>
> org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:456)
> at
>
> org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:279)
> at
>
> org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:221)
> at
>
> org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCellValue(XSSFFormulaEvaluator.java:257)/
>
>
> not only for the cell that has external reference in the formula but also
> any other formulae in the sheet that have a reference to the cell that
> contains external formula. Hence, I cannot isolate the cells that have
> external reference.
>
> Any guidance will be very helpful ...
>
> Appreicate your time.
>
> --
> View this message in context:
> http://apache-poi.1045710.n5.nabble.com/Detect-External-Reference-in-Formula-tp5652011p5652011.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
--
Mark Coleman
uGly Duckling B.V.
Burgemeester le Fevre de Montignylaan 30
3055LG Rotterdam, the Netherlands
KvK nummer: 52272125
BTW nummer: NL850371570B.01
Rabobank: 14.68.33.473
E: m.coleman@uglyduckling.nl
M: +31 (0) 646347972
W: http://uGlyDuckling.nl <http://www.uglyduckling.nl/>