Rails axlsx gem - Formula not escaping

Question

Is there a way to ignore executing a formula while rendering spreadsheet?

Currently, sheet.add_row("=10+10") will evaluate 20, even if I give :formula => :false or :type=> :string

The only hacky way is to provide a single quote, but it's not a pretty approach.

Can you give a complete minimal working example. I get an `Invalid Data "=10+10" for array_to_cells. must be Array. (ArgumentError)` with your code snipplet. I have to use `sheet.add_row(["=10+10"])` — knut, Nov 25 '16 at 12:34
It cannot be anything but a string type. Else if fails. And if it's string, with an '=' then it's considered a formula and executed. — Sid, Nov 25 '16 at 13:47

score 2 · Answer 1 · edited May 23 '17 at 11:47

2

I found an answer for this in Stop Excel from automatically converting certain text values to dates

require 'axlsx'
Axlsx::Package.new do |p|
  p.workbook.add_worksheet(:name => 'DATA') do |sheet|
    sheet.add_row(['="10+10"', 'Maybe this is the best solution'])
    sheet.add_row(["'10+10", 'Hack with single quote'])
  end    
  p.serialize('test.xlsx')
end

This results in:

edited May 23 '17 at 11:47

Community

1
1

answered Nov 25 '16 at 12:39

knut

27,320
6
84
112

That was the hacky way I used, but then it does not look good. I am checking for a better solution. – Sid Nov 25 '16 at 13:46
Can you try `sheet.add_row([" =10+10"])` (see the space before the =). I can't test it actual (no installed excel), but if I open it with Open Office it works. – knut Nov 25 '16 at 23:07
That didn't help, at least with MS Office 2016. – Sid Nov 26 '16 at 04:45

score 2 · Accepted Answer · answered Nov 26 '16 at 04:51

I looked at the source code of the gem in question, the following code is there:

def is_formula?
   @type == :string && @value.to_s.start_with?('=')
end

It means that anything of type string with '=' will be treated like a formula. And the only accepted types are date, string, integer, float etc. Anything else in place of :type => :string and it does not accept it.

As an alternative, I had to open the class cell_serializer.rb in the gem and reimplement the method in a custom way to get rid of cell.is_formula? check.

def string_type_serialization(cell, str='')
      if cell.is_formula?
        formula_serialization cell, str
      elsif !cell.ssti.nil?
        value_serialization 's', cell.ssti.to_s, str
      else
        inline_string_serialization cell, str
      end
 end

Reimplemented method:

def string_type_serialization(cell, str='')
        if !cell.ssti.nil?
          value_serialization 's', cell.ssti.to_s, str
        else
          inline_string_serialization cell, str
        end
end

I realize it's a hacky way, but it affects system wide code. If I need anything complex in future, I can always make changes to one central place.

score 1 · Answer 3 · edited May 23 '17 at 12:34

I found another approach. In the approach mentioned in this answer, although the formula is not executed when the spreadsheet opens, it is evaluated if the user clicks on it and then blurs away. This might not be the best solution.

Better solution is to wrap any excel functions in TEXT function. This ensures the formulae is not executed.

e.g.

= 9 + 9 can be substituted with =TEXT("9+9","#"), and it will be printed as it is, without evaluation.

Rails axlsx gem - Formula not escaping

3 Answers3