From f2a4b5a4342226650ca0d8618b43ccbe7b267925 Mon Sep 17 00:00:00 2001 From: Knut Stenmark 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. --- test/workbook/worksheet/tc_pivot_table.rb | 16 ++++++++++++++-- 1 file changed, 14 insertions(+), 2 deletions(-) (limited to 'test') 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 -- cgit v1.2.3