Added pivot table option to pass in list of row fields to omit subtotals from

2 files changed, 24 insertions, 6 deletions

diff --git a/lib/axlsx/workbook/worksheet/pivot_table.rb b/lib/axlsx/workbook/worksheet/pivot_table.rb
index 0d5f34e8..71bcde90 100644
--- a/lib/axlsx/workbook/worksheet/pivot_table.rb
+++ b/lib/axlsx/workbook/worksheet/pivot_table.rb
@@ -25,10 +25,15 @@ module Axlsx
       @data = []
       @pages = []
       @subtotal = nil
+      @no_subtotals_on_headers = []
       parse_options options
       yield self if block_given?
     end
 
+    # Defines the headers in which subtotals are not to be included
+    # @return[Array]
+    attr_accessor :no_subtotals_on_headers
+
     # The reference to the table data
     # @return [String]
     attr_reader :ref
@@ -163,7 +168,7 @@ module Axlsx
       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|
-        str <<   pivot_field_for(cell_value)
+        str <<   pivot_field_for(cell_value,!no_subtotals_on_headers.include?(cell_value))
       end
       str <<   '</pivotFields>'
       if rows.empty?
@@ -200,8 +205,9 @@ module Axlsx
       unless data.empty?
         str << "<dataFields count=\"#{data.size}\">"
         data.each do |datum_value|
-          str << "<dataField name='#{@subtotal} of #{datum_value[:ref]}' fld='#{header_index_of(datum_value[:ref])}' baseField='0' baseItem='0'"
-          str << " subtotal='#{datum_value[:subtotal]}' " if datum_value[:subtotal]
+          # The correct name prefix in ["Sum","Average", etc...]
+          str << "<dataField name=\"#{(datum_value[:subtotal]||'')} of #{datum_value[:ref]}\" fld=\"#{header_index_of(datum_value[:ref])}\" baseField=\"0\" baseItem=\"0\""
+          str << " subtotal=\"#{datum_value[:subtotal]}\" " if datum_value[:subtotal]
           str << "/>"
         end
         str << '</dataFields>'
@@ -241,9 +247,13 @@ module Axlsx
 
     private
 
-    def pivot_field_for(cell_ref)
+    def pivot_field_for(cell_ref, subtotal=true)
       if rows.include? cell_ref
-        '<pivotField axis="axisRow" compact="0" outline="0" subtotalTop="0" showAll="0" includeNewItemsInFilter="1">' + '<items count="1"><item t="default"/></items>' + '</pivotField>'
+        if subtotal
+          '<pivotField axis="axisRow" compact="0" outline="0" subtotalTop="0" showAll="0" includeNewItemsInFilter="1">' + '<items count="1"><item t="default"/></items>' + '</pivotField>'
+        else
+          '<pivotField axis="axisRow" compact="0" outline="0" subtotalTop="0" showAll="0" includeNewItemsInFilter="1" defaultSubtotal="0">' + '</pivotField>'
+        end
       elsif columns.include? cell_ref
         '<pivotField axis="axisCol" compact="0" outline="0" subtotalTop="0" showAll="0" includeNewItemsInFilter="1">' + '<items count="1"><item t="default"/></items>' + '</pivotField>'
       elsif pages.include? cell_ref
diff --git a/test/workbook/worksheet/tc_pivot_table.rb b/test/workbook/worksheet/tc_pivot_table.rb
index 3c42d605..a62e6f40 100644
--- a/test/workbook/worksheet/tc_pivot_table.rb
+++ b/test/workbook/worksheet/tc_pivot_table.rb
@@ -67,7 +67,15 @@ class TestPivotTable < Test::Unit::TestCase
     assert_equal([{:ref=>"Sales", :subtotal => 'average'}], pivot_table.data)
   end
 
-  def test_header_indices
+  def test_add_pivot_table_with_row_without_subtotals
+    pivot_table = @ws.add_pivot_table('G5:G6', 'A1:D5', {:no_subtotals_on_headers=>['Year']}) do |pt|
+      pt.data = ['Sales']
+      pt.rows = ['Year','Month']
+    end
+    assert_equal(['Year'], pivot_table.no_subtotals_on_headers)
+  end
+
+ def test_header_indices
     pivot_table = @ws.add_pivot_table('G5:G6', 'A1:E5')
     assert_equal(0,   pivot_table.header_index_of('Year'   ))
     assert_equal(1,   pivot_table.header_index_of('Month'  ))