Fix invalid xml when pivot table created with more than one column in data field

3 files changed, 44 insertions, 2 deletions

diff --git a/CHANGELOG.md b/CHANGELOG.md
index b9a0d429..9983d90d 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -1,6 +1,9 @@
 CHANGELOG
 ---------
 
+- **Unreleased**
+  - [PR #123](https://github.com/caxlsx/caxlsx/pull/123) - Fix invalid xml when pivot table created with more than one column in data field. Solves [Issue #110](https://github.com/caxlsx/caxlsx/issues/110)
+
 - **September.22.21**: 3.1.1
   - [PR #107](https://github.com/caxlsx/caxlsx/pull/107) - Add overlap to bar charts
   - [PR #108](https://github.com/caxlsx/caxlsx/pull/108) - Fix gap depth and gap depth validators for bar charts and 3D bar charts
diff --git a/lib/axlsx/workbook/worksheet/pivot_table.rb b/lib/axlsx/workbook/worksheet/pivot_table.rb
index d8c630ec..bc500a8a 100644
--- a/lib/axlsx/workbook/worksheet/pivot_table.rb
+++ b/lib/axlsx/workbook/worksheet/pivot_table.rb
@@ -173,7 +173,9 @@ module Axlsx
     # @return [String]
     def to_xml_string(str = '')
       str << '<?xml version="1.0" encoding="UTF-8"?>'
-      str << ('<pivotTableDefinition xmlns="' << XML_NS << '" name="' << name << '" cacheId="' << cache_definition.cache_id.to_s << '"  dataOnRows="1" applyNumberFormats="0" applyBorderFormats="0" applyFontFormats="0" applyPatternFormats="0" applyAlignmentFormats="0" applyWidthHeightFormats="1" dataCaption="Data" showMultipleLabel="0" showMemberPropertyTips="0" useAutoFormatting="1" indent="0" compact="0" compactData="0" gridDropZones="1" multipleFieldFilters="0">')
+
+      str << ('<pivotTableDefinition xmlns="' << XML_NS << '" name="' << name << '" cacheId="' << cache_definition.cache_id.to_s << '"' << (data.size <= 1 ? ' dataOnRows="1"' : '') << ' applyNumberFormats="0" applyBorderFormats="0" applyFontFormats="0" applyPatternFormats="0" applyAlignmentFormats="0" applyWidthHeightFormats="1" dataCaption="Data" showMultipleLabel="0" showMemberPropertyTips="0" useAutoFormatting="1" indent="0" compact="0" compactData="0" gridDropZones="1" multipleFieldFilters="0">')
+
       str << ('<location firstDataCol="1" firstDataRow="1" firstHeaderRow="1" ref="' << ref << '"/>')
       str << ('<pivotFields count="' << header_cells_count.to_s << '">')
       header_cell_values.each do |cell_value|
@@ -195,7 +197,7 @@ module Axlsx
         end
         str << '</rowItems>'
       end
-      if columns.empty?
+      if columns.empty? && data.size <= 1
         str << '<colItems count="1"><i/></colItems>'
       else
         str << ('<colFields count="' << columns.size.to_s << '">')
diff --git a/test/workbook/worksheet/tc_pivot_table.rb b/test/workbook/worksheet/tc_pivot_table.rb
index a494dcfd..591f0ba9 100644
--- a/test/workbook/worksheet/tc_pivot_table.rb
+++ b/test/workbook/worksheet/tc_pivot_table.rb
@@ -140,4 +140,41 @@ class TestPivotTable < Test::Unit::TestCase
     doc = Nokogiri::XML(pivot_table.to_xml_string)
     assert_equal('4', doc.at_css('dataFields dataField')['numFmtId'], 'adding format options to pivot_table')
   end
+
+  def test_pivot_table_with_more_than_one_data_row
+    ### https://github.com/caxlsx/caxlsx/issues/110
+    
+    pivot_table = @ws.add_pivot_table('G5:G6', 'A1:E5') do |pt|
+      pt.rows = ["Date", "Name"]
+      pt.data = [
+        {ref: "Gross amount", num_fmt: 2},
+        {ref: "Net amount", num_fmt: 2},
+      ]
+    end
+
+    xml = pivot_table.to_xml_string
+
+    assert(xml.include?('colFields'))
+
+    assert(!xml.include?('dataOnRows'))
+    assert(!xml.include?('colItems'))
+  end
+
+  def test_pivot_table_with_only_one_data_row
+    ### https://github.com/caxlsx/caxlsx/issues/110
+    
+    pivot_table = @ws.add_pivot_table('G5:G6', 'A1:E5') do |pt|
+      pt.rows = ["Date", "Name"]
+      pt.data = [
+        {ref: "Gross amount", num_fmt: 2},
+      ]
+    end
+
+    xml = pivot_table.to_xml_string
+
+    assert(xml.include?('dataOnRows'))
+    assert(xml.include?('colItems'))
+
+    assert(!xml.include?('colFields'))
+  end
 end