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, [email protected] < [email protected]> 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: [email protected] > For additional commands, e-mail: [email protected] > > -- 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: [email protected] M: +31 (0) 646347972 W: http://uGlyDuckling.nl <http://www.uglyduckling.nl/>
