Bug 57390

Summary: Match function with array argument in formula results in "File error: data may have been lost"
Product: POI Reporter: Vitaly Litvak <vitavaque>
Component: HSSFAssignee: POI Developers List <dev>
Status: NEW ---    
Severity: normal    
Priority: P2    
Version: 3.11-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   

Description Vitaly Litvak 2014-12-22 18:28:37 UTC
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.
Comment 1 Vitaly Litvak 2014-12-22 18:34:03 UTC
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);
        }
Comment 2 Nick Burch 2014-12-23 01:34:10 UTC
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 :/
Comment 3 Vitaly Litvak 2014-12-23 10:17:07 UTC
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.
Comment 4 Vitaly Litvak 2014-12-23 13:26:47 UTC
I guess you are talking about https://issues.apache.org/bugzilla/show_bug.cgi?id=48292, is that right?
Comment 5 Nick Burch 2014-12-24 03:43:27 UTC
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...
Comment 6 karinkreider 2018-12-26 13:32:01 UTC
I'll draw the inference that https://domyessay.today/ doesn't have a decent answer, then, okay?