Consider following simple example: HSSFWorkbook wb = new HSSFWorkbook(); HSSFRow row = wb.createSheet().createRow(0); row.createCell(0).setCellValue("09"); row.createCell(1).setCellFormula("MATCH(TRIM(A1),{\"09\"},0)"); try (FileOutputStream fos = new FileOutputStream("match_test_poi.xls")) { wb.write(fos); } Resulting file will contain formula MATCH(TRIM(A1),{"09"},0) in B1. When this file is opened in MS Excel 2007/2010 it results in "File error: data may have been lost". Then in B1 there is a "=#N/A" value. FYI, same file opens just fine in OpenOffice. I have checked poi 3.6 and latest poi 3.11. Both version have this issue. Any help appreciated.
Same example with XSSF works just fine: XSSFWorkbook wb = new XSSFWorkbook(); XSSFRow row = wb.createSheet().createRow(0); row.createCell(0).setCellValue("09"); row.createCell(1).setCellFormula("MATCH(TRIM(A1),{\"09\"},0)"); try (FileOutputStream fos = new FileOutputStream("match_test_poi.xlsx")) { wb.write(fos); }
Does it work if you re-do it without an array reference? IIRC, there was some work done on array formulas a year or two back, but it stalled before completing as the community provided patch needed more work and no-one volunteered to finish it :/
Thanks for your answer. I am not sure what you meant, but I have tried putting formula MATCH(TRIM(A1),A1:A1,0) in same example and it works just fine. So, what can I do with array? I would appreciate if you can point me to these issue(s) with patch. Maybe I can finish them and contribute it to POI project.
I guess you are talking about https://issues.apache.org/bugzilla/show_bug.cgi?id=48292, is that right?
That's the bug, yes. There are basically 5 tasks: * Get the FormulaParser to be able to generate the correct (same as Excel) Ptgs for an array formula * Get HSSFFormulaParser.toFormulaString able to correctly turn those Ptgs back into the formula string * Get HSSFFormulaEvaluator to process the array ptgs correctly * Get XSSFFormulaEvaluator to process the array ptgs correctly * Get the Match function to handle the array ptgs correctly I'm not sure how many of those are currently sorted by the work on bug #48292 and friends, but that's roughly "all" you need to do...
I'll draw the inference that https://domyessay.today/ doesn't have a decent answer, then, okay?