Can configure a pivot table when creating it

see examples/pivot_table.rb

    wb.add_worksheet(:name => "Data Sheet") do |sheet|
      sheet.add_row ['Month', 'Year', 'Type', 'Sales', 'Region']
      30.times { sheet.add_row [month, year, type, sales, region] }
      sheet.add_pivot_table 'G4:L17', "A1:E31" do |pivot_table|
        pivot_table.rows = ['Month', 'Year']
        pivot_table.columns = ['Type']
        pivot_table.data = ['Sales']
        pivot_table.pages = ['Region']
      end
    end

7 files changed, 204 insertions, 14 deletions

diff --git a/examples/pivot_table.rb b/examples/pivot_table.rb
index 229ed8c9..07d03d55 100644
--- a/examples/pivot_table.rb
+++ b/examples/pivot_table.rb
@@ -27,7 +27,12 @@ end
 wb.add_worksheet(:name => "Data Sheet") do |sheet|
   sheet.add_row ['Month', 'Year', 'Type', 'Sales', 'Region']
   30.times { sheet.add_row [month, year, type, sales, region] }
-  sheet.add_pivot_table 'G4:L17', "A1:E31"
+  sheet.add_pivot_table 'G4:L17', "A1:E31" do |pivot_table|
+    pivot_table.rows = ['Month', 'Year']
+    pivot_table.columns = ['Type']
+    pivot_table.data = ['Sales']
+    pivot_table.pages = ['Region']
+  end
 end
 
 # Write the excel file
diff --git a/lib/axlsx.rb b/lib/axlsx.rb
index 20129b95..91391db5 100644
--- a/lib/axlsx.rb
+++ b/lib/axlsx.rb
@@ -104,6 +104,18 @@ module Axlsx
     Axlsx::col_ref(c_index).to_s << (r_index+1).to_s
   end
 
+  def self.range_to_a(range)
+    range.match(/^(\w+?\d+)\:(\w+?\d+)$/)
+    start_col, start_row = name_to_indices($1)
+    end_col,   end_row   = name_to_indices($2)
+    (start_row..end_row).to_a.map do |row_num|
+      (start_col..end_col).to_a.map do |col_num|
+        "#{col_ref(col_num)}#{row_num+1}"
+      end
+    end
+  end
+
+
   # performs the increadible feat of changing snake_case to CamelCase
   # @param [String] s The snake case string to camelize
   # @return [String]
diff --git a/lib/axlsx/workbook/worksheet/pivot_table.rb b/lib/axlsx/workbook/worksheet/pivot_table.rb
index c4eff8be..d876f777 100644
--- a/lib/axlsx/workbook/worksheet/pivot_table.rb
+++ b/lib/axlsx/workbook/worksheet/pivot_table.rb
@@ -19,6 +19,10 @@ module Axlsx
       @sheet = sheet
       @sheet.workbook.pivot_tables << self
       @name = "PivotTable#{index+1}"
+      @rows = []
+      @columns = []
+      @data = []
+      @pages = []
       parse_options options
       yield self if block_given?
     end
@@ -46,6 +50,54 @@ module Axlsx
       end
     end
 
+    # The rows
+    # @return [Array]
+    attr_reader :rows
+
+    def rows=(v)
+      DataTypeValidator.validate "#{self.class}.rows", [Array], v
+      v.each do |ref|
+        DataTypeValidator.validate "#{self.class}.rows[]", [String], ref
+      end
+      @rows = v
+    end
+
+    # The columns
+    # @return [Array]
+    attr_reader :columns
+
+    def columns=(v)
+      DataTypeValidator.validate "#{self.class}.columns", [Array], v
+      v.each do |ref|
+        DataTypeValidator.validate "#{self.class}.columns[]", [String], ref
+      end
+      @columns = v
+    end
+
+    # The data
+    # @return [Array]
+    attr_reader :data
+
+    def data=(v)
+      DataTypeValidator.validate "#{self.class}.data", [Array], v
+      v.each do |ref|
+        DataTypeValidator.validate "#{self.class}.data[]", [String], ref
+      end
+      @data = v
+    end
+
+    # The pages
+    # @return [String]
+    attr_reader :pages
+
+    def pages=(v)
+      DataTypeValidator.validate "#{self.class}.pages", [Array], v
+      v.each do |ref|
+        DataTypeValidator.validate "#{self.class}.pages[]", [String], ref
+      end
+      @pages = v
+    end
+
     # The index of this chart in the workbooks charts collection
     # @return [Integer]
     def index
@@ -102,19 +154,95 @@ module Axlsx
       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 <<   '<location firstDataCol="1" firstDataRow="1" firstHeaderRow="1" ref="' << ref << '"/>'
       str <<   '<pivotFields count="' << header_cells_count.to_s << '">'
-      header_cells_count.times do
-        str <<   '<pivotField compact="0" outline="0" subtotalTop="0" showAll="0" includeNewItemsInFilter="1"/>'
+      header_cell_values.each do |cell_value|
+        str <<   pivot_field_for(cell_value)
       end
       str <<   '</pivotFields>'
+      if rows.empty?
+        str << '<rowFields count="1"><field x="-2"/></rowFields>'
+        str << '<rowItems count="2"><i><x/></i> <i i="1"><x v="1"/></i></rowItems>'
+      else
+        str << '<rowFields count="' << rows.size.to_s << '">'
+        rows.each do |row_value|
+          str << '<field x="' << header_index_of(row_value).to_s << '"/>'
+        end
+        str << '</rowFields>'
+        str << '<rowItems count="' << rows.size.to_s << '">'
+        rows.size.times do |i|
+          str << '<i/>'
+        end
+        str << '</rowItems>'
+      end
+      if columns.empty?
+        str << '<colItems count="1"><i/></colItems>'
+      else
+        str << '<colFields count="' << columns.size.to_s << '">'
+        columns.each do |column_value|
+          str << '<field x="' << header_index_of(column_value).to_s << '"/>'
+        end
+        str << '</colFields>'
+      end
+      unless pages.empty?
+        str << '<pageFields count="' << pages.size.to_s << '">'
+        pages.each do |page_value|
+          str << '<pageField fld="' << header_index_of(page_value).to_s << '"/>'
+        end
+        str << '</pageFields>'
+      end
+      unless data.empty?
+        str << '<dataFields count="' << data.size.to_s << '">'
+        data.each do |datum_value|
+          str << '<dataField name="Sum of ' << datum_value << '" ' <<
+                            'fld="' << header_index_of(datum_value).to_s << '" ' <<
+                            'baseField="0" baseItem="0"/>'
+        end
+        str << '</dataFields>'
+      end
       str << '</pivotTableDefinition>'
     end
 
-    private
+    def header_cell_refs
+      Axlsx::range_to_a(header_range).first
+    end
 
-    # get the header cells (hackish)
     def header_cells
-      header = range.gsub(/^(\w+?)(\d+)\:(\w+?)\d+$/, '\1\2:\3\2')
-      @sheet[header]
+      @sheet[header_range]
+    end
+
+    def header_cell_values
+      header_cells.map(&:value)
+    end
+
+    def header_index_of(value)
+      header_cell_values.index(value)
+    end
+
+    private
+
+    def pivot_field_for(cell_ref)
+      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>'
+      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
+        '<pivotField axis="axisCol" compact="0" outline="0" subtotalTop="0" showAll="0" includeNewItemsInFilter="1">' <<
+          '<items count="1"><item t="default"/></items>' <<
+        '</pivotField>'
+      elsif data.include? cell_ref
+        '<pivotField dataField="1" compact="0" outline="0" subtotalTop="0" showAll="0" includeNewItemsInFilter="1">' <<
+        '</pivotField>'
+      else
+        '<pivotField compact="0" outline="0" subtotalTop="0" showAll="0" includeNewItemsInFilter="1">' <<
+        '</pivotField>'
+      end
+    end
+
+    def header_range
+      range.gsub(/^(\w+?)(\d+)\:(\w+?)\d+$/, '\1\2:\3\2')
     end
 
   end
diff --git a/lib/axlsx/workbook/worksheet/pivot_table_cache_definition.rb b/lib/axlsx/workbook/worksheet/pivot_table_cache_definition.rb
index e70bcc6a..5a6c7442 100644
--- a/lib/axlsx/workbook/worksheet/pivot_table_cache_definition.rb
+++ b/lib/axlsx/workbook/worksheet/pivot_table_cache_definition.rb
@@ -30,7 +30,7 @@ module Axlsx
     end
 
     def cache_id
-      index
+      index + 1
     end
 
     # The relation reference id for this table
@@ -49,8 +49,8 @@ module Axlsx
       str <<     '<worksheetSource ref="' << pivot_table.range << '" sheet="Data Sheet"/>'
       str <<   '</cacheSource>'
       str <<   '<cacheFields count="' << pivot_table.header_cells_count.to_s << '">'
-      pivot_table.header_cells_count.times do |i|
-        str <<   '<cacheField name="placeholder_' << i.to_s << '" numFmtId="0">'
+      pivot_table.header_cells.each do |cell|
+        str <<   '<cacheField name="' << cell.value << '" numFmtId="0">'
         str <<     '<sharedItems count="0">'
         str <<     '</sharedItems>'
         str <<   '</cacheField>'
diff --git a/test/tc_axlsx.rb b/test/tc_axlsx.rb
index 4901ede2..0f94b3e5 100644
--- a/test/tc_axlsx.rb
+++ b/test/tc_axlsx.rb
@@ -54,4 +54,10 @@ class TestAxlsx < Test::Unit::TestCase
     # todo
   end
 
+  def test_range_to_a
+    assert_equal([['A1', 'B1', 'C1']],                         Axlsx::range_to_a('A1:C1'))
+    assert_equal([['A1', 'B1', 'C1'], ['A2', 'B2', 'C2']],     Axlsx::range_to_a('A1:C2'))
+    assert_equal([['Z5', 'AA5', 'AB5'], ['Z6', 'AA6', 'AB6']], Axlsx::range_to_a('Z5:AB6'))
+  end
+
 end
diff --git a/test/workbook/worksheet/tc_pivot_table.rb b/test/workbook/worksheet/tc_pivot_table.rb
index 6f1f3f7a..3a94cfae 100644
--- a/test/workbook/worksheet/tc_pivot_table.rb
+++ b/test/workbook/worksheet/tc_pivot_table.rb
@@ -4,9 +4,9 @@ class TestPivotTable < Test::Unit::TestCase
   def setup
     p = Axlsx::Package.new
     @ws = p.workbook.add_worksheet
-    40.times do
-      @ws << ["aa","aa","aa","aa","aa","aa"]
-    end
+
+    @ws << ["Year","Month","Region", "Type", "Sales"]
+    @ws << [2012,  "Nov",  "East",   "Soda", "12345"]
   end
 
   def test_initialization
@@ -24,6 +24,29 @@ class TestPivotTable < Test::Unit::TestCase
     assert_equal(@ws.pivot_tables.last, pivot_table, "must be added to worksheet pivot tables collection")
   end
 
+  def test_add_pivot_table_with_config
+    pivot_table = @ws.add_pivot_table('G5:G6', 'A1:D5') do |pt|
+      pt.rows = ['Year', 'Month']
+      pt.columns = ['Type']
+      pt.data = ['Sales']
+      pt.pages = ['Region']
+    end
+    assert_equal(['Year', 'Month'], pivot_table.rows)
+    assert_equal(['Type'], pivot_table.columns)
+    assert_equal(['Sales'], pivot_table.data)
+    assert_equal(['Region'], pivot_table.pages)
+  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'  ))
+    assert_equal(2,   pivot_table.header_index_of('Region' ))
+    assert_equal(3,   pivot_table.header_index_of('Type'   ))
+    assert_equal(4,   pivot_table.header_index_of('Sales'  ))
+    assert_equal(nil, pivot_table.header_index_of('Missing'))
+  end
+
   def test_pn
     @ws.add_pivot_table('G5:G6', 'A1:D5')
     assert_equal(@ws.pivot_tables.first.pn, "pivotTables/pivotTable1.xml")
@@ -59,4 +82,20 @@ class TestPivotTable < Test::Unit::TestCase
     assert(errors.empty?, "error free validation")
   end
 
+  def test_to_xml_string_with_configuration
+    pivot_table = @ws.add_pivot_table('G5:G6', 'A1:E5') do |pt|
+      pt.rows = ['Year', 'Month']
+      pt.columns = ['Type']
+      pt.data = ['Sales']
+      pt.pages = ['Region']
+    end
+    schema = Nokogiri::XML::Schema(File.open(Axlsx::SML_XSD))
+    doc = Nokogiri::XML(pivot_table.to_xml_string)
+    errors = []
+    schema.validate(doc).each do |error|
+      errors.push error
+      puts error.message
+    end
+    assert(errors.empty?, "error free validation")
+  end
 end
diff --git a/test/workbook/worksheet/tc_pivot_table_cache_definition.rb b/test/workbook/worksheet/tc_pivot_table_cache_definition.rb
index 78563f7d..2b4389b7 100644
--- a/test/workbook/worksheet/tc_pivot_table_cache_definition.rb
+++ b/test/workbook/worksheet/tc_pivot_table_cache_definition.rb
@@ -29,7 +29,7 @@ class TestPivotTableCacheDefinition < Test::Unit::TestCase
   end
 
   def test_cache_id
-    assert_equal(0, @cache_definition.cache_id)
+    assert_equal(1, @cache_definition.cache_id)
   end
 
   def test_to_xml_string