From f2a4b5a4342226650ca0d8618b43ccbe7b267925 Mon Sep 17 00:00:00 2001
From: Knut Stenmark <knut.stenmark@gmail.com>
Date: Thu, 21 Jul 2022 16:17:46 +0200
Subject: Prevent Excel from crashing when multiple data columns are added to
 PivotTable

Excel will crash unless both `colFields` and `colItems` are declared in the PivotTable xml.

First `field` of `colFields` needs to be set to -2 to tell Excel that the value columns comes from individual columns and not groups.
I do not know why `colItems` are needed, but when set, the Excel document opens without crashing.
---
 lib/axlsx/workbook/worksheet/pivot_table.rb | 14 ++++++++++++--
 1 file changed, 12 insertions(+), 2 deletions(-)

(limited to 'lib')

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|
-- 
cgit v1.2.3