Bug 47747

Summary: java.lang.RuntimeException: Shared Formula Conversion: Coding Error
Product: POI Reporter: kevinrmckee
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal CC: kevinrmckee
Priority: P2    
Version: unspecified   
Target Milestone: ---   
Hardware: PC   
OS: Windows Vista   
Bug Depends on:    
Bug Blocks: 47855    
Attachments: This zip contains the original and processed spreadsheets.
Patch resolves the problem.

Description kevinrmckee 2009-08-27 06:21:18 UTC
Created attachment 24176 [details]
This zip contains the original and processed spreadsheets.

I have tried version 3.5, 3.6 and 3.7.

When trying to run the following code to go through a spreadsheet and update
the formula values, I get several errors (java.lang.RuntimeException: Shared
Formula Conversion: Coding Error).  When I open the newly created spreadsheet,
several cells have #REF issues.  I will attach the original and processed
spreadsheets.

public static void RecalculateFormulas(String filename) throws
FileNotFoundException, IOException
    {   try{
        FileInputStream fis = new FileInputStream(filename);
        FileOutputStream out = new
FileOutputStream(filename.replaceAll(".xls","temp.xls"));
        HSSFWorkbook wb = new HSSFWorkbook(fis); //or new
XSSFWorkbook("/somepath/test.xls")
        FormulaEvaluator evaluator =
wb.getCreationHelper().createFormulaEvaluator();
        for(int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
            org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(sheetNum);
            for(org.apache.poi.ss.usermodel.Row r : sheet) {
                for(org.apache.poi.ss.usermodel.Cell c : r) {
                    if(c.getCellType() ==
org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA) {
                        try{evaluator.evaluateFormulaCell(c);}
                        catch(Exception e)
                        {System.out.println(e.toString());}
                    }
                }
            }
        }
        wb.write(out);
        out.close();
        }
        catch(Exception e)
        {System.out.println(e.toString());
        }
    }
Comment 1 Petr.Udalau 2009-08-31 03:59:21 UTC
Created attachment 24191 [details]
Patch resolves the problem.
Comment 2 kevinrmckee 2009-09-02 10:40:21 UTC
(In reply to comment #1)
> Created an attachment (id=24191) [details]
> Patch resolves the problem.

Thanks, but is there a new build of the JAR file which will contain this patch, or do I have to download all of the source and rebuild it?
Comment 3 David Fisher 2009-09-02 11:04:26 UTC
Until a POI developer evaluates this patch and applies it to the trunk the patch is only available here or from the contributing developer.
Comment 4 kevinrmckee 2009-09-02 14:07:54 UTC
(In reply to comment #3)
> Until a POI developer evaluates this patch and applies it to the trunk the
> patch is only available here or from the contributing developer.

Any idea when that might happen so I can utilize this new fix? How often do patches get added to the trunk?

Thanks
Comment 5 kevinrmckee 2009-09-09 08:54:26 UTC
(In reply to comment #3)
> Until a POI developer evaluates this patch and applies it to the trunk the
> patch is only available here or from the contributing developer.

I downloaded http://encore.torchbox.com/poi-svn-build/poi-3.5-beta7-20090909.jar but I still have the same error.  Has this patch been included in the version I downloaded?
Comment 6 David Fisher 2009-09-16 20:11:01 UTC
*** Bug 47855 has been marked as a duplicate of this bug. ***
Comment 7 Josh Micich 2009-09-17 17:44:53 UTC
Fixed in svn r816417

There was a problem with the way POI associated formula records with the corresponding shared formula records.  The actual fix needed to be made much earlier than where the patch suggested (By that stage the formula record had already been attached to the wrong shared formula).