1

Im new to POI and I have requirement to search a given list of strings in two different excel sheets which will have the occurence of the search Strings, one or more times.

Now, im using Cell itereator to iterate through all rows to search the Strings,

for (String searchString : SearchList){
List<Row> matchedRows = getMatchedRows(searchString); 

}

public List<Row> getMatchedRows(String searchString)
List<Row> rowsMatched = new ArrayList();
(Row rowOfSheet : workSheet) {
if(rowOfSheet.getCell(index).getStringCellValue().equalsIgnoreCase(searchString)) {
    rowsMatched.add(rowOfSheet)
           }
     }
}

but the performance is will getting affected as i have huge records in ExcelSheet as well searchList. So i tried to use "Match" Formula to get index of matching row using Evaluator, but i couldnt gt the correct syntax, when i try to execute the following:

 "MATCH('C:\\Files\\[File1.xlsx]sheet1'!$A$2,'C:\\Files\\[File2.xlsx]sheet2'!$B$2:$B$741,0)"

This gives me the following exception:

 Exception in thread "main" java.lang.IllegalArgumentException: Invalid sheetIndex: -1.
at org.apache.poi.ss.formula.SheetRefEvaluator.<init>(SheetRefEvaluator.java:39)
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:656)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:527)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:288)

How to specify Complete File path in formulae String. I refrred the following bt this is not working out for me.

http://stackoverflow.com/questions/10915475/java-poi-xssf-formulaevaluator

how to solve this. Any Suggestions and Solutions are Much helpful. Thanks in Advance. Please give some solution asap i need it badly.

user1909657
  • 91
  • 1
  • 1
  • 5

0 Answers0