Merge pull request #186 from tablecheck/escape-formulas-improvement

escape_formulas - add settings for global, workbook, worksheet, row and cell levels

5 files changed, 157 insertions, 1 deletions

diff --git a/test/tc_axlsx.rb b/test/tc_axlsx.rb
index c214f8e2..73fdd9ab 100644
--- a/test/tc_axlsx.rb
+++ b/test/tc_axlsx.rb
@@ -139,4 +139,17 @@ class TestAxlsx < Test::Unit::TestCase
     assert_equal({ foo: { baz: true } }, h1.merge(h2))
     assert_equal({ foo: { bar: true, baz: true } }, Axlsx.hash_deep_merge(h1, h2))
   end
+
+  def test_escape_formulas
+    Axlsx.instance_variable_set(:@escape_formulas, nil)
+    assert_equal false, Axlsx::escape_formulas
+
+    Axlsx::escape_formulas = true
+    assert_equal true, Axlsx::escape_formulas
+
+    Axlsx::escape_formulas = false
+    assert_equal false, Axlsx::escape_formulas
+  ensure
+    Axlsx.instance_variable_set(:@escape_formulas, nil)
+  end
 end
diff --git a/test/workbook/tc_workbook.rb b/test/workbook/tc_workbook.rb
index c085ed91..443dd392 100644
--- a/test/workbook/tc_workbook.rb
+++ b/test/workbook/tc_workbook.rb
@@ -161,4 +161,38 @@ class TestWorkbook < Test::Unit::TestCase
     wb_xml = Nokogiri::XML(@wb.to_xml_string)
     assert_equal sheet.name, wb_xml.xpath('//xmlns:workbook/xmlns:sheets/*[1]/@name').to_s
   end
+
+  def test_escape_formulas
+    Axlsx::escape_formulas = false
+    p = Axlsx::Package.new
+    @wb = p.workbook
+    assert_false @wb.escape_formulas
+    assert_false @wb.add_worksheet.escape_formulas
+    assert_false @wb.add_worksheet(escape_formulas: false).escape_formulas
+    assert @wb.add_worksheet(escape_formulas: true).escape_formulas
+
+    Axlsx::escape_formulas = true
+    p = Axlsx::Package.new
+    @wb = p.workbook
+    assert @wb.escape_formulas
+    assert @wb.add_worksheet.escape_formulas
+    assert_false @wb.add_worksheet(escape_formulas: false).escape_formulas
+    assert @wb.add_worksheet(escape_formulas: true).escape_formulas
+
+    @wb.escape_formulas = false
+    assert_false @wb.escape_formulas
+    assert_false @wb.add_worksheet.escape_formulas
+    assert_false @wb.add_worksheet(escape_formulas: false).escape_formulas
+    assert @wb.add_worksheet(escape_formulas: true).escape_formulas
+
+    @wb.escape_formulas = true
+    p = Axlsx::Package.new
+    @wb = p.workbook
+    assert @wb.escape_formulas
+    assert @wb.add_worksheet.escape_formulas
+    assert_false @wb.add_worksheet(escape_formulas: false).escape_formulas
+    assert @wb.add_worksheet(escape_formulas: true).escape_formulas
+  ensure
+    Axlsx.instance_variable_set(:@escape_formulas, nil)
+  end
 end
diff --git a/test/workbook/worksheet/tc_cell.rb b/test/workbook/worksheet/tc_cell.rb
index f5264115..cb878c12 100644
--- a/test/workbook/worksheet/tc_cell.rb
+++ b/test/workbook/worksheet/tc_cell.rb
@@ -317,6 +317,8 @@ class TestCell < Test::Unit::TestCase
   end
 
   def test_plain_string
+    @c.escape_formulas = false
+
     @c.type = :integer
     assert_equal(@c.plain_string?, false)
 
@@ -333,6 +335,17 @@ class TestCell < Test::Unit::TestCase
     @c.value = '=sum'
     assert_equal(@c.plain_string?, false)
 
+    @c.value = '{=sum}'
+    assert_equal(@c.plain_string?, false)
+
+    @c.escape_formulas = true
+
+    @c.value = '=sum'
+    assert_equal(@c.plain_string?, true)
+
+    @c.value = '{=sum}'
+    assert_equal(@c.plain_string?, true)
+
     @c.value = 'plain string'
     @c.font_name = 'Arial'
     assert_equal(@c.plain_string?, false)
@@ -381,6 +394,37 @@ class TestCell < Test::Unit::TestCase
     assert(doc.xpath("//t[text()='=IF(2+2=4,4,5)']").any?)
   end
 
+  def test_to_xml_string_numeric_escaped
+    p = Axlsx::Package.new
+    ws = p.workbook.add_worksheet do |sheet|
+      sheet.add_row ["-1", "+2"], escape_formulas: true, types: :text
+    end
+    doc = Nokogiri::XML(ws.to_xml_string)
+    doc.remove_namespaces!
+    assert(doc.xpath("//t[text()='-1']").any?)
+    assert(doc.xpath("//t[text()='+2']").any?)
+  end
+
+  def test_to_xml_string_other_owasp_escaped
+    p = Axlsx::Package.new
+    ws = p.workbook.add_worksheet do |sheet|
+      sheet.add_row [
+        "@1",
+        "%2",
+        "|3",
+        "\rfoo",
+        "\tbar"
+      ], escape_formulas: true
+    end
+    doc = Nokogiri::XML(ws.to_xml_string)
+    doc.remove_namespaces!
+    assert(doc.xpath("//t[text()='@1']").any?)
+    assert(doc.xpath("//t[text()='%2']").any?)
+    assert(doc.xpath("//t[text()='|3']").any?)
+    assert(doc.xpath("//t[text()='\nfoo']").any?)
+    assert(doc.xpath("//t[text()='\tbar']").any?)
+  end
+
   def test_to_xml_string_formula_escape_array_parameter
     p = Axlsx::Package.new
     ws = p.workbook.add_worksheet do |sheet|
@@ -413,7 +457,7 @@ class TestCell < Test::Unit::TestCase
   def test_to_xml_string_text_formula
     p = Axlsx::Package.new
     ws = p.workbook.add_worksheet do |sheet|
-      sheet.add_row ["=1+1", "-1+1"], type: :text
+      sheet.add_row ["=1+1", "-1+1"], types: :text
     end
     doc = Nokogiri::XML(ws.to_xml_string)
     doc.remove_namespaces!
diff --git a/test/workbook/worksheet/tc_row.rb b/test/workbook/worksheet/tc_row.rb
index 29c4415b..cf8fa7c3 100644
--- a/test/workbook/worksheet/tc_row.rb
+++ b/test/workbook/worksheet/tc_row.rb
@@ -153,4 +153,31 @@ class TestRow < Test::Unit::TestCase
       assert_equal(c.value, index < offset ? nil : values[index - offset])
     end
   end
+
+  def test_escape_formulas
+    @ws.escape_formulas = false
+    @row = @ws.add_row
+    assert_false @row.add_cell('').escape_formulas
+    assert_false @row.add_cell('', escape_formulas: false).escape_formulas
+    assert @row.add_cell('', escape_formulas: true).escape_formulas
+
+    @row = Axlsx::Row.new(@ws)
+    assert_false @row.add_cell('').escape_formulas
+
+    @ws.escape_formulas = true
+    @row = @ws.add_row
+
+    assert @row.add_cell('').escape_formulas
+    assert_false @row.add_cell('', escape_formulas: false).escape_formulas
+    assert @row.add_cell('', escape_formulas: true).escape_formulas
+
+    @row.escape_formulas = false
+    assert_equal [false, false, false], @row.cells.map(&:escape_formulas)
+
+    @row.escape_formulas = true
+    assert_equal [true, true, true], @row.cells.map(&:escape_formulas)
+
+    @row.escape_formulas = [false, true, false]
+    assert_equal [false, true, false], @row.cells.map(&:escape_formulas)
+  end
 end
diff --git a/test/workbook/worksheet/tc_worksheet.rb b/test/workbook/worksheet/tc_worksheet.rb
index f6c65fe4..d83cdf91 100644
--- a/test/workbook/worksheet/tc_worksheet.rb
+++ b/test/workbook/worksheet/tc_worksheet.rb
@@ -891,4 +891,42 @@ class TestWorksheet < Test::Unit::TestCase
       wb.styles.style_index.values.first
     )
   end
+
+  def test_escape_formulas
+    @wb.escape_formulas = false
+    @ws = @wb.add_worksheet
+    assert_false @ws.escape_formulas
+    assert_false @ws.add_row(['']).cells.first.escape_formulas
+    assert_false @ws.add_row([''], escape_formulas: false).cells.first.escape_formulas
+    assert @ws.add_row([''], escape_formulas: true).cells.first.escape_formulas
+    assert_equal [true, false], @ws.add_row(['', ''], escape_formulas: [true, false]).cells.map(&:escape_formulas)
+
+    @ws = Axlsx::Worksheet.new(@wb)
+    assert_false @ws.escape_formulas
+
+    @wb.escape_formulas = true
+    @ws = @wb.add_worksheet
+    assert @ws.escape_formulas
+    assert @ws.add_row(['']).cells.first.escape_formulas
+    assert_false @ws.add_row([''], escape_formulas: false).cells.first.escape_formulas
+    assert @ws.add_row([''], escape_formulas: true).cells.first.escape_formulas
+    assert_equal [true, false], @ws.add_row(['', ''], escape_formulas: [true, false]).cells.map(&:escape_formulas)
+
+    @ws = Axlsx::Worksheet.new(@wb)
+    assert @ws.escape_formulas
+
+    @ws.escape_formulas = false
+    assert_false @ws.escape_formulas
+    assert_false @ws.add_row(['']).cells.first.escape_formulas
+    assert_false @ws.add_row([''], escape_formulas: false).cells.first.escape_formulas
+    assert @ws.add_row([''], escape_formulas: true).cells.first.escape_formulas
+    assert_equal [true, false], @ws.add_row(['', ''], escape_formulas: [true, false]).cells.map(&:escape_formulas)
+
+    @ws.escape_formulas = true
+    assert @ws.escape_formulas
+    assert @ws.add_row(['']).cells.first.escape_formulas
+    assert_false @ws.add_row([''], escape_formulas: false).cells.first.escape_formulas
+    assert @ws.add_row([''], escape_formulas: true).cells.first.escape_formulas
+    assert_equal [true, false], @ws.add_row(['', ''], escape_formulas: [true, false]).cells.map(&:escape_formulas)
+  end
 end