Create a simple Pivot Table

* an example can be run with `ruby examples/pivot_table.rb`
* right now you cannot set options on the pivot table to make it useful (coming soon...)

5 files changed, 256 insertions, 13 deletions

diff --git a/lib/axlsx/workbook/workbook.rb b/lib/axlsx/workbook/workbook.rb
index c9556c4d..a3c42743 100644
--- a/lib/axlsx/workbook/workbook.rb
+++ b/lib/axlsx/workbook/workbook.rb
@@ -39,6 +39,9 @@ require 'axlsx/workbook/defined_names.rb'
 require 'axlsx/workbook/worksheet/table_style_info.rb'
 require 'axlsx/workbook/worksheet/table.rb'
 require 'axlsx/workbook/worksheet/tables.rb'
+require 'axlsx/workbook/worksheet/pivot_table_cache_definition.rb'
+require 'axlsx/workbook/worksheet/pivot_table.rb'
+require 'axlsx/workbook/worksheet/pivot_tables.rb'
 require 'axlsx/workbook/worksheet/data_validation.rb'
 require 'axlsx/workbook/worksheet/data_validations.rb'
 require 'axlsx/workbook/worksheet/sheet_view.rb'
@@ -120,10 +123,17 @@ require 'axlsx/workbook/worksheet/selection.rb'
     # @return [SimpleTypedList]
     attr_reader :tables
 
+    # A colllection of pivot tables associated with this workbook
+    # @note The recommended way to manage drawings is Worksheet#add_table
+    # @see Worksheet#add_table
+    # @see Table
+    # @return [SimpleTypedList]
+    attr_reader :pivot_tables
+
 
     # A collection of defined names for this workbook
     # @note The recommended way to manage defined names is Workbook#add_defined_name
-    # @see DefinedName 
+    # @see DefinedName
     # @return [DefinedNames]
     def defined_names
       @defined_names ||= DefinedNames.new
@@ -169,7 +179,7 @@ require 'axlsx/workbook/worksheet/selection.rb'
     #  w.parse_string :date1904, "//xmlns:workbookPr/@date1904"
     #  w
     #end
-    
+
     # Creates a new Workbook
     # The recomended way to work with workbooks is via Package#workbook
     # @option options [Boolean] date1904. If this is not specified, date1904 is set to false. Office 2011 for Mac defaults to false.
@@ -181,6 +191,7 @@ require 'axlsx/workbook/worksheet/selection.rb'
       @images = SimpleTypedList.new Pic
       # Are these even used????? Check package serialization parts
       @tables = SimpleTypedList.new Table
+      @pivot_tables = SimpleTypedList.new PivotTable
       @comments = SimpleTypedList.new Comments
 
 
@@ -216,7 +227,7 @@ require 'axlsx/workbook/worksheet/selection.rb'
     def use_autowidth=(v=true) Axlsx::validate_boolean v; @use_autowidth = v; end
 
     # inserts a worksheet into this workbook at the position specified.
-    # It the index specified is out of range, the worksheet will be added to the end of the 
+    # It the index specified is out of range, the worksheet will be added to the end of the
     # worksheets collection
     # @return [Worksheet]
     # @param index The zero based position to insert the newly created worksheet
@@ -258,6 +269,9 @@ require 'axlsx/workbook/worksheet/selection.rb'
       @worksheets.each do |sheet|
         r << Relationship.new(WORKSHEET_R, WORKSHEET_PN % (r.size+1))
       end
+      pivot_tables.each_with_index do |pivot_table, index|
+        r << Relationship.new(PIVOT_TABLE_CACHE_DEFINITION_R, PIVOT_TABLE_CACHE_DEFINITION_PN % (index+1))
+      end
       r << Relationship.new(STYLES_R,  STYLES_PN)
       if use_shared_strings
           r << Relationship.new(SHARED_STRINGS_R,  SHARED_STRINGS_PN)
@@ -298,6 +312,14 @@ require 'axlsx/workbook/worksheet/selection.rb'
         end
       end
       str << '</sheets>'
+      unless pivot_tables.empty?
+        str << '<pivotCaches>'
+        pivot_tables.each_with_index do |pivot_table, index|
+          rId = "rId#{@worksheets.size + index + 1  }"
+          str << '<pivotCache cacheId="' << pivot_table.cache_definition.cache_id.to_s << '" r:id="' << rId << '"/>'
+        end
+        str << '</pivotCaches>'
+      end
       defined_names.to_xml_string(str)
       str << '</workbook>'
     end
diff --git a/lib/axlsx/workbook/worksheet/pivot_table.rb b/lib/axlsx/workbook/worksheet/pivot_table.rb
new file mode 100644
index 00000000..c4eff8be
--- /dev/null
+++ b/lib/axlsx/workbook/worksheet/pivot_table.rb
@@ -0,0 +1,121 @@
+# encoding: UTF-8
+module Axlsx
+  # Table
+  # @note Worksheet#add_pivot_table is the recommended way to create tables for your worksheets.
+  # @see README for examples
+  class PivotTable
+
+    include Axlsx::OptionsParser
+
+    # Creates a new PivotTable object
+    # @param [String] ref The reference to where the pivot table lives like 'G4:L17'.
+    # @param [String] range The reference to the pivot table data like 'A1:D31'.
+    # @param [Worksheet] sheet The sheet containing the table data.
+    # @option options [Cell, String] name
+    # @option options [TableStyle] style
+    def initialize(ref, range, sheet, options={})
+      @ref = ref
+      self.range = range
+      @sheet = sheet
+      @sheet.workbook.pivot_tables << self
+      @name = "PivotTable#{index+1}"
+      parse_options options
+      yield self if block_given?
+    end
+
+    # The reference to the table data
+    # @return [String]
+    attr_reader :ref
+
+    # The name of the table.
+    # @return [String]
+    attr_reader :name
+
+    # The name of the sheet.
+    # @return [String]
+    attr_reader :sheet
+
+    # The range where the data for this pivot table lives.
+    # @return [String]
+    attr_reader :range
+
+    def range=(v)
+      DataTypeValidator.validate "#{self.class}.range", [String], v
+      if v.is_a?(String)
+        @range = v
+      end
+    end
+
+    # The index of this chart in the workbooks charts collection
+    # @return [Integer]
+    def index
+      @sheet.workbook.pivot_tables.index(self)
+    end
+
+    # The part name for this table
+    # @return [String]
+    def pn
+      "#{PIVOT_TABLE_PN % (index+1)}"
+    end
+
+    # The relationship part name of this pivot table
+    # @return [String]
+    def rels_pn
+      "#{PIVOT_TABLE_RELS_PN % (index+1)}"
+    end
+
+    def header_cells_count
+      header_cells.count
+    end
+
+    def cache_definition
+      @cache_definition ||= PivotTableCacheDefinition.new(self)
+    end
+
+    # The worksheet relationships. This is managed automatically by the worksheet
+    # @return [Relationships]
+    def relationships
+      r = Relationships.new
+      r << Relationship.new(PIVOT_TABLE_CACHE_DEFINITION_R, "../#{cache_definition.pn}")
+      r
+    end
+
+    # identifies the index of an object withing the collections used in generating relationships for the worksheet
+    # @param [Any] object the object to search for
+    # @return [Integer] The index of the object
+    def relationships_index_of(object)
+      objects = [cache_definition]
+      objects.index(object)
+    end
+
+    # The relation reference id for this table
+    # @return [String]
+    def rId
+      "rId#{index+1}"
+    end
+
+    # Serializes the object
+    # @param [String] str
+    # @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 <<   '<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"/>'
+      end
+      str <<   '</pivotFields>'
+      str << '</pivotTableDefinition>'
+    end
+
+    private
+
+    # get the header cells (hackish)
+    def header_cells
+      header = range.gsub(/^(\w+?)(\d+)\:(\w+?)\d+$/, '\1\2:\3\2')
+      @sheet[header]
+    end
+
+  end
+end
diff --git a/lib/axlsx/workbook/worksheet/pivot_table_cache_definition.rb b/lib/axlsx/workbook/worksheet/pivot_table_cache_definition.rb
new file mode 100644
index 00000000..e70bcc6a
--- /dev/null
+++ b/lib/axlsx/workbook/worksheet/pivot_table_cache_definition.rb
@@ -0,0 +1,63 @@
+# encoding: UTF-8
+module Axlsx
+  # Table
+  # @note Worksheet#add_pivot_table is the recommended way to create tables for your worksheets.
+  # @see README for examples
+  class PivotTableCacheDefinition
+
+    include Axlsx::OptionsParser
+
+    # Creates a new PivotTable object
+    # @param [String] pivot_table The pivot table this cache definition is in
+    def initialize(pivot_table)
+      @pivot_table = pivot_table
+    end
+
+    # # The reference to the pivot table data
+    # # @return [PivotTable]
+    attr_reader :pivot_table
+
+    # The index of this chart in the workbooks charts collection
+    # @return [Integer]
+    def index
+      pivot_table.sheet.workbook.pivot_tables.index(pivot_table)
+    end
+
+    # The part name for this table
+    # @return [String]
+    def pn
+      "#{PIVOT_TABLE_CACHE_DEFINITION_PN % (index+1)}"
+    end
+
+    def cache_id
+      index
+    end
+
+    # The relation reference id for this table
+    # @return [String]
+    def rId
+      "rId#{index + 1}"
+    end
+
+    # Serializes the object
+    # @param [String] str
+    # @return [String]
+    def to_xml_string(str = '')
+      str << '<?xml version="1.0" encoding="UTF-8"?>'
+      str << '<pivotCacheDefinition xmlns="' << XML_NS << '" xmlns:r="' << XML_NS_R << '" invalid="1" refreshOnLoad="1" recordCount="0">'
+      str <<   '<cacheSource type="worksheet">'
+      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">'
+        str <<     '<sharedItems count="0">'
+        str <<     '</sharedItems>'
+        str <<   '</cacheField>'
+      end
+      str <<   '</cacheFields>'
+      str << '</pivotCacheDefinition>'
+    end
+
+  end
+end
diff --git a/lib/axlsx/workbook/worksheet/pivot_tables.rb b/lib/axlsx/workbook/worksheet/pivot_tables.rb
new file mode 100644
index 00000000..f5625fc0
--- /dev/null
+++ b/lib/axlsx/workbook/worksheet/pivot_tables.rb
@@ -0,0 +1,24 @@
+module Axlsx
+
+  # A simple, self serializing class for storing pivot tables
+  class PivotTables < SimpleTypedList
+
+    # creates a new Tables object
+    def initialize(worksheet)
+      raise ArgumentError, "you must provide a worksheet" unless worksheet.is_a?(Worksheet)
+      super PivotTable
+      @worksheet = worksheet
+    end
+
+    # The worksheet that owns this collection of pivot tables
+    # @return [Worksheet]
+    attr_reader :worksheet
+
+    # returns the relationships required by this collection
+    def relationships
+      return [] if empty?
+      map{ |pivot_table| Relationship.new(PIVOT_TABLE_R, "../#{pivot_table.pn}") }
+    end
+  end
+
+end
diff --git a/lib/axlsx/workbook/worksheet/worksheet.rb b/lib/axlsx/workbook/worksheet/worksheet.rb
index 6a32e881..ff3e022e 100644
--- a/lib/axlsx/workbook/worksheet/worksheet.rb
+++ b/lib/axlsx/workbook/worksheet/worksheet.rb
@@ -41,7 +41,7 @@ module Axlsx
     def name
       @name ||=  "Sheet" + (index+1).to_s
     end
- 
+
     # The sheet calculation properties
     # @return [SheetCalcPr]
     def sheet_calc_pr
@@ -76,6 +76,12 @@ module Axlsx
       @tables ||=  Tables.new self
     end
 
+    # The pivot tables in this worksheet
+    # @return [Array] of Table
+    def pivot_tables
+      @pivot_tables ||=  PivotTables.new self
+    end
+
     # A typed collection of hyperlinks associated with this worksheet
     # @return [WorksheetHyperlinks]
     def hyperlinks
@@ -104,7 +110,7 @@ module Axlsx
     # An range that excel will apply an autfilter to "A1:B3"
     # This will turn filtering on for the cells in the range.
     # The first row is considered the header, while subsequent rows are considerd to be data.
-    # @return String 
+    # @return String
     def auto_filter
       @auto_filter ||= AutoFilter.new self
     end
@@ -280,7 +286,7 @@ module Axlsx
 
     # The name of the worksheet
     # The name of a worksheet must be unique in the workbook, and must not exceed 31 characters
-    # @param [String] name 
+    # @param [String] name
     def name=(name)
       validate_sheet_name name
       @name=Axlsx::coder.encode(name)
@@ -388,7 +394,7 @@ module Axlsx
       cf = ConditionalFormatting.new( :sqref => cells )
       cf.add_rules rules
       conditional_formattings << cf
-      conditional_formattings      
+      conditional_formattings
     end
 
     # Add data validation to this worksheet.
@@ -436,6 +442,12 @@ module Axlsx
       tables.last
     end
 
+    def add_pivot_table(ref, range, options={})
+      pivot_tables << PivotTable.new(ref, range, self, options)
+      yield pivot_tables.last if block_given?
+      pivot_tables.last
+    end
+
     # Shortcut to worsksheet_comments#add_comment
     def add_comment(options={})
       worksheet_comments.add_comment(options)
@@ -513,7 +525,7 @@ module Axlsx
     def sanitize(str)
       str.gsub(CONTROL_CHAR_REGEX, '')
     end
-    
+
     # The worksheet relationships. This is managed automatically by the worksheet
     # @return [Relationships]
     def relationships
@@ -521,7 +533,8 @@ module Axlsx
       r + [tables.relationships,
            worksheet_comments.relationships,
            hyperlinks.relationships,
-           worksheet_drawing.relationship].flatten.compact || []
+           worksheet_drawing.relationship,
+           pivot_tables.relationships].flatten.compact || []
       r
     end
 
@@ -570,12 +583,12 @@ module Axlsx
 
 
     private
-   
+
     def validate_sheet_name(name)
       DataTypeValidator.validate "Worksheet.name", String, name
       raise ArgumentError, (ERR_SHEET_NAME_TOO_LONG % name) if name.size > 31
       raise ArgumentError, (ERR_SHEET_NAME_COLON_FORBIDDEN % name) if name.include? ':'
-      name = Axlsx::coder.encode(name) 
+      name = Axlsx::coder.encode(name)
       sheet_names = @workbook.worksheets.map { |s| s.name }
       raise ArgumentError, (ERR_DUPLICATE_SHEET_NAME % name) if sheet_names.include?(name)
     end
@@ -606,7 +619,7 @@ module Axlsx
     # @see Worksheet#protect_range
     # @return [SimpleTypedList] The protected ranges for this worksheet
     def protected_ranges
-      @protected_ranges ||= ProtectedRanges.new self 
+      @protected_ranges ||= ProtectedRanges.new self
       # SimpleTypedList.new ProtectedRange
     end
 
@@ -619,7 +632,7 @@ module Axlsx
     # data validations array
     # @return [Array]
     def data_validations
-      @data_validations ||= DataValidations.new self 
+      @data_validations ||= DataValidations.new self
     end
 
     # merged cells array