Monday, 24 September 2012

Clearing excel formula cache when using jruby-poi - making Formulas work right

Last week I've been working with jruby-poi to make an script that would automatically update an MS Excel file.
The part that cost me the most time, believe it or not, was formula updating, you see, I would add rows that should change the value of pre-set  formulae, but the results would not update on Excel until you clicked on the formula.
After diving in the nearly nonexistent documentation of the gem (they give you base-usage on git, some test case you can check out also on git, everything else is default generated rdocs without a single improvement) I've came up with the following work around:


       %w[jobs defects].each do |sheet_name|
            sheet    = self.workbook.worksheets[ sheet_name ]
            rows     = sheet.rows
            from, to = 2, 70
            rows.each_with_index do |row, i|
                next if i <= from
                #order is important!
                %w[L K D E F N O P Q U V X Y Z W AA AB AC AD AE AF AG AH AI AJ].each do |letter|
                    column = letter.to_number - 1
                    puts sprintf( 'updating formula on C: %s row %d', letter, i )
                    self.workbook.on_formula_update rows[ i ][ column ]
                end
                break if i >= to
            end
end


I hope it helps anyone, also I've opened an issue on their git, let's see how they respond.