There is no way to escape executing formula, :string does not work

[sidd-kulk]

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 => :falseor :type=> :string

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

[straydogstudio]

This is more of an Axlsx question, and I see you already got an answer on stack overflow, which didn't give much of an answer. I'm curious if there is a non breaking space or the like which you can use to lead the string instead of the ' character. It might fool the pattern matching and not be visually ugly.

If you do find an answer, please leave it here and on stack overflow for others to find.

[sidd-kulk]

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.

[sidd-kulk]

I found another approach. In the approach mentioned in above answer by e, 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.

[straydogstudio]

Thanks @sidcool1234. Text looks like a pretty good solution.