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()); } }
Created attachment 24191 [details] Patch resolves the problem.
(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?
Until a POI developer evaluates this patch and applies it to the trunk the patch is only available here or from the contributing developer.
(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
(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?
*** Bug 47855 has been marked as a duplicate of this bug. ***
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).