Merge pull request #156 from basefarm/pivot_multiple_data_columns

Prevent Excel from crashing when multiple data columns added to PivotTable

3 files changed, 27 insertions, 4 deletions

diff --git a/CHANGELOG.md b/CHANGELOG.md
index 95bb0781..13d1f43c 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -15,6 +15,7 @@ CHANGELOG
     - Allow specifying `:all` in `border: {edges: :all}` which is a shortcut for `border: {edges: [:left, :right, :top, :bottom]}`
 
 - **Unreleased**
+  - [PR #156](https://github.com/caxlsx/caxlsx/pull/156) - Prevent Excel from crashing when multiple data columns added to PivotTable
   - [PR #155](https://github.com/caxlsx/caxlsx/pull/155) - Add `hideDropDown` alias for `showDropDown` setting, as the latter is confusing to use (because its logic seems inverted).
   - [PR #143](https://github.com/caxlsx/caxlsx/pull/143) - Add setting `sort_on_headers` for pivot tables
   - [PR #132](https://github.com/caxlsx/caxlsx/pull/132) - Remove monkey patch from Object#instance_values
diff --git a/lib/axlsx/workbook/worksheet/pivot_table.rb b/lib/axlsx/workbook/worksheet/pivot_table.rb
index 60960392..455665a5 100644
--- a/lib/axlsx/workbook/worksheet/pivot_table.rb
+++ b/lib/axlsx/workbook/worksheet/pivot_table.rb
@@ -217,8 +217,18 @@ module Axlsx
         end
         str << '</rowItems>'
       end
-      if columns.empty? && data.size <= 1
-        str << '<colItems count="1"><i/></colItems>'
+      if columns.empty?
+        if data.size > 1
+          str << '<colFields count="1"><field x="-2"/></colFields>'
+          str << "<colItems count=\"#{data.size}\">"
+          str << '<i><x/></i>'
+          data[1..-1].each_with_index do |datum_value,i|
+            str << "<i i=\"#{i + 1}\"><x v=\"#{i + 1}\"/></i>"
+          end
+          str << '</colItems>'
+        else
+          str << '<colItems count="1"><i/></colItems>'
+        end
       else
         str << ('<colFields count="' << columns.size.to_s << '">')
         columns.each do |column_value|
diff --git a/test/workbook/worksheet/tc_pivot_table.rb b/test/workbook/worksheet/tc_pivot_table.rb
index c7aa53cc..f676a72d 100644
--- a/test/workbook/worksheet/tc_pivot_table.rb
+++ b/test/workbook/worksheet/tc_pivot_table.rb
@@ -162,15 +162,27 @@ class TestPivotTable < Test::Unit::TestCase
       pt.data = [
         {ref: "Gross amount", num_fmt: 2},
         {ref: "Net amount", num_fmt: 2},
+        {ref: "Margin", num_fmt: 2},
       ]
     end
 
     xml = pivot_table.to_xml_string
 
+    assert(!xml.include?('dataOnRows'))
     assert(xml.include?('colFields'))
+    assert(xml.include?('colItems'))
+
+    doc = Nokogiri::XML(pivot_table.to_xml_string)
+
+    assert_equal('1', doc.at_css('colFields')['count'])
+    assert_equal('-2', doc.at_css('colFields field')['x'])
+
+    assert_equal('3', doc.at_css('colItems')['count'])
+    assert_equal( 3, doc.at_css('colItems').children.size)
+    assert_nil( doc.at_css('colItems i')['x'])
+    assert_equal('1', doc.at_css('colItems i[i=1] x')['v'])
+    assert_equal('2', doc.at_css('colItems i[i=2] x')['v'])
 
-    assert(!xml.include?('dataOnRows'))
-    assert(!xml.include?('colItems'))
   end
 
   def test_pivot_table_with_only_one_data_row