Merge pull request #148 from alexrothenberg/pivot_table

Create a simple Pivot Table

16 files changed, 637 insertions, 14 deletions

diff --git a/examples/pivot_table.rb b/examples/pivot_table.rb
new file mode 100644
index 00000000..07d03d55
--- /dev/null
+++ b/examples/pivot_table.rb
@@ -0,0 +1,39 @@
+#!/usr/bin/env ruby -w -s
+# -*- coding: utf-8 -*-
+
+$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib"
+require 'axlsx'
+
+p = Axlsx::Package.new
+wb = p.workbook
+
+# Create some data in a sheet
+def month
+  %w(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec).sample
+end
+def year
+  %w(2010 2011 2012).sample
+end
+def type
+  %w(Meat Dairy Beverages Produce).sample
+end
+def sales
+  rand(5000)
+end
+def region
+  %w(East West North South).sample
+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" 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
+p.serialize("pivot_table.xlsx")
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/package.rb b/lib/axlsx/package.rb
index e3981d62..df87ed12 100644
--- a/lib/axlsx/package.rb
+++ b/lib/axlsx/package.rb
@@ -192,6 +192,12 @@ module Axlsx
       workbook.tables.each do |table|
         parts << {:entry => "xl/#{table.pn}", :doc => table.to_xml_string, :schema => SML_XSD}
       end
+      workbook.pivot_tables.each do |pivot_table|
+        cache_definition = pivot_table.cache_definition
+        parts << {:entry => "xl/#{pivot_table.rels_pn}", :doc => pivot_table.relationships.to_xml_string, :schema => RELS_XSD}
+        parts << {:entry => "xl/#{pivot_table.pn}", :doc => pivot_table.to_xml_string} #, :schema => SML_XSD}
+        parts << {:entry => "xl/#{cache_definition.pn}", :doc => cache_definition.to_xml_string} #, :schema => SML_XSD}
+      end
 
       workbook.comments.each do|comment|
         if comment.size > 0
@@ -255,6 +261,13 @@ module Axlsx
                                        :ContentType => TABLE_CT)
       end
 
+      workbook.pivot_tables.each do |pivot_table|
+        c_types << Axlsx::Override.new(:PartName => "/xl/#{pivot_table.pn}",
+                                       :ContentType => PIVOT_TABLE_CT)
+        c_types << Axlsx::Override.new(:PartName => "/xl/#{pivot_table.cache_definition.pn}",
+                                       :ContentType => PIVOT_TABLE_CACHE_DEFINITION_CT)
+      end
+
       workbook.comments.each do |comment|
         if comment.size > 0
         c_types << Axlsx::Override.new(:PartName => "/xl/#{comment.pn}",
diff --git a/lib/axlsx/rels/relationship.rb b/lib/axlsx/rels/relationship.rb
index 04911904..385059f1 100644
--- a/lib/axlsx/rels/relationship.rb
+++ b/lib/axlsx/rels/relationship.rb
@@ -12,6 +12,7 @@ module Axlsx
     # @note Supported types are defined as constants in Axlsx:
     # @see XML_NS_R
     # @see TABLE_R
+    # @see PIVOT_TABLE_R
     # @see WORKBOOK_R
     # @see WORKSHEET_R
     # @see APP_R
diff --git a/lib/axlsx/util/constants.rb b/lib/axlsx/util/constants.rb
index 66434b87..437de6db 100644
--- a/lib/axlsx/util/constants.rb
+++ b/lib/axlsx/util/constants.rb
@@ -51,6 +51,10 @@ module Axlsx
   # table rels namespace
   TABLE_R = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/table"
 
+  # pivot table rels namespace
+  PIVOT_TABLE_R = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/pivotTable"
+  PIVOT_TABLE_CACHE_DEFINITION_R = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/pivotCacheDefinition"
+
   # workbook rels namespace
   WORKBOOK_R = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument"
 
@@ -99,6 +103,12 @@ module Axlsx
   # table content type
   TABLE_CT = "application/vnd.openxmlformats-officedocument.spreadsheetml.table+xml"
 
+  # pivot table content type
+  PIVOT_TABLE_CT = "application/vnd.openxmlformats-officedocument.spreadsheetml.pivotTable+xml"
+
+  # pivot table cache definition content type
+  PIVOT_TABLE_CACHE_DEFINITION_CT = "application/vnd.openxmlformats-officedocument.spreadsheetml.pivotCacheDefinition+xml"
+
   # workbook content type
   WORKBOOK_CT = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"
 
@@ -208,6 +218,13 @@ module Axlsx
   # drawing part
   TABLE_PN = "tables/table%d.xml"
 
+  # pivot table parts
+  PIVOT_TABLE_PN = "pivotTables/pivotTable%d.xml"
+  PIVOT_TABLE_CACHE_DEFINITION_PN = "pivotCache/pivotCacheDefinition%d.xml"
+
+  # pivot table rels parts
+  PIVOT_TABLE_RELS_PN = "pivotTables/_rels/pivotTable%d.xml.rels"
+
   # chart part
   CHART_PN = "charts/chart%d.xml"
 
@@ -320,7 +337,7 @@ module Axlsx
   # x1E Information Separator Two
   # x1F Information Separator One
   #
-  # The following are not dealt with. 
+  # The following are not dealt with.
   # If you have this in your data, expect excel to blow up!
   #
   # x7F	Delete
@@ -365,7 +382,7 @@ module Axlsx
   # @see http://www.codetable.net/asciikeycodes
   pattern = "[\x0-\x08\x0B\x0C\x0E-\x1F]"
   pattern= pattern.respond_to?(:encode) ? pattern.encode('UTF-8') : pattern
-  
+
   # The regular expression used to remove control characters from worksheets
   CONTROL_CHAR_REGEX = Regexp.new(pattern, 'n')
 
diff --git a/lib/axlsx/util/validators.rb b/lib/axlsx/util/validators.rb
index cc8f0a92..aa8eb1ac 100644
--- a/lib/axlsx/util/validators.rb
+++ b/lib/axlsx/util/validators.rb
@@ -41,6 +41,7 @@ module Axlsx
       raise ArgumentError, (ERR_REGEX % [v.inspect, regex.to_s]) unless (v.respond_to?(:to_s) && v.to_s.match(regex))
     end
   end
+
   # Validate that the class of the value provided is either an instance or the class of the allowed types and that any specified additional validation returns true.
   class DataTypeValidator
     # Perform validation
@@ -229,14 +230,14 @@ module Axlsx
   # TABLE_CT, WORKBOOK_CT, APP_CT, RELS_CT, STYLES_CT, XML_CT, WORKSHEET_CT, SHARED_STRINGS_CT, CORE_CT, CHART_CT, DRAWING_CT, COMMENT_CT are allowed
   # @param [Any] v The value validated
   def self.validate_content_type(v)
-    RestrictionValidator.validate :content_type, [TABLE_CT, WORKBOOK_CT, APP_CT, RELS_CT, STYLES_CT, XML_CT, WORKSHEET_CT, SHARED_STRINGS_CT, CORE_CT, CHART_CT, JPEG_CT, GIF_CT, PNG_CT, DRAWING_CT, COMMENT_CT, VML_DRAWING_CT], v
+    RestrictionValidator.validate :content_type, [TABLE_CT, WORKBOOK_CT, APP_CT, RELS_CT, STYLES_CT, XML_CT, WORKSHEET_CT, SHARED_STRINGS_CT, CORE_CT, CHART_CT, JPEG_CT, GIF_CT, PNG_CT, DRAWING_CT, COMMENT_CT, VML_DRAWING_CT, PIVOT_TABLE_CT, PIVOT_TABLE_CACHE_DEFINITION_CT], v
   end
 
   # Requires that the value is a valid relationship_type
   # XML_NS_R, TABLE_R, WORKBOOK_R, WORKSHEET_R, APP_R, RELS_R, CORE_R, STYLES_R, CHART_R, DRAWING_R, IMAGE_R, HYPERLINK_R, SHARED_STRINGS_R are allowed
   # @param [Any] v The value validated
   def self.validate_relationship_type(v)
-    RestrictionValidator.validate :relationship_type, [XML_NS_R, TABLE_R, WORKBOOK_R, WORKSHEET_R, APP_R, RELS_R, CORE_R, STYLES_R, CHART_R, DRAWING_R, IMAGE_R, HYPERLINK_R, SHARED_STRINGS_R, COMMENT_R, VML_DRAWING_R, COMMENT_R_NULL], v
+    RestrictionValidator.validate :relationship_type, [XML_NS_R, TABLE_R, WORKBOOK_R, WORKSHEET_R, APP_R, RELS_R, CORE_R, STYLES_R, CHART_R, DRAWING_R, IMAGE_R, HYPERLINK_R, SHARED_STRINGS_R, COMMENT_R, VML_DRAWING_R, COMMENT_R_NULL, PIVOT_TABLE_R, PIVOT_TABLE_CACHE_DEFINITION_R], v
   end
 
   # Requires that the value is a valid table element type
diff --git a/lib/axlsx/workbook/workbook.rb b/lib/axlsx/workbook/workbook.rb
index 6c99d6de..c1d304a4 100644
--- a/lib/axlsx/workbook/workbook.rb
+++ b/lib/axlsx/workbook/workbook.rb
@@ -40,6 +40,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'
@@ -121,10 +124,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
@@ -170,7 +180,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.
@@ -182,6 +192,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
 
 
@@ -217,7 +228,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
@@ -259,6 +270,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)
@@ -299,6 +313,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..d876f777
--- /dev/null
+++ b/lib/axlsx/workbook/worksheet/pivot_table.rb
@@ -0,0 +1,249 @@
+# 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}"
+      @rows = []
+      @columns = []
+      @data = []
+      @pages = []
+      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 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
+      @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_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
+
+    def header_cell_refs
+      Axlsx::range_to_a(header_range).first
+    end
+
+    def header_cells
+      @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
+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..5a6c7442
--- /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 + 1
+    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.each do |cell|
+        str <<   '<cacheField name="' << cell.value << '" 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 8a595b2d..4c59e340 100644
--- a/lib/axlsx/workbook/worksheet/worksheet.rb
+++ b/lib/axlsx/workbook/worksheet/worksheet.rb
@@ -79,6 +79,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
@@ -454,6 +460,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)
@@ -539,7 +551,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
 
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/tc_package.rb b/test/tc_package.rb
index d4333993..f3802105 100644
--- a/test/tc_package.rb
+++ b/test/tc_package.rb
@@ -8,7 +8,8 @@ class TestPackage < Test::Unit::TestCase
     ws.add_row ['Can', 'we', 'build it?']
     ws.add_row ['Yes!', 'We', 'can!']
     ws.add_hyperlink :ref => ws.rows.first.cells.last, :location => 'https://github.com/randym'
-    ws.workbook.add_defined_name("#{ws.name}!A1:C2", :name => '_xlnm.Print_Titles', :hidden => true)
+    # Not sure what this does but no specs break without it and `definedNames` is not in sml.xsd
+    # ws.workbook.add_defined_name("#{ws.name}!A1:C2", :name => '_xlnm.Print_Titles', :hidden => true)
     ws.protect_range('A1:C1')
     ws.protect_range(ws.rows.last.cells)
     ws.add_comment :author => 'alice', :text => 'Hi Bob', :ref => 'A12'
@@ -38,15 +39,15 @@ class TestPackage < Test::Unit::TestCase
     ws.add_chart(Axlsx::Line3DChart, :title => "axis labels") do |chart|
       chart.valAxis.title = 'bob'
       chart.d_lbls.show_val = true
-    end 
-    
+    end
+
     ws.add_chart(Axlsx::Bar3DChart, :title => 'bar chart') do |chart|
       chart.add_series :data => [1,4,5], :labels => %w(A B C)
       chart.d_lbls.show_percent = true
     end
 
     ws.add_chart(Axlsx::ScatterChart, :title => 'scat man') do |chart|
-      chart.add_series :xData => [1,2,3,4], :yData => [4,3,2,1] 
+      chart.add_series :xData => [1,2,3,4], :yData => [4,3,2,1]
       chart.d_lbls.show_val = true
     end
 
@@ -61,7 +62,7 @@ class TestPackage < Test::Unit::TestCase
     ws.add_image :image_src => File.expand_path('../../examples/image1.gif', __FILE__) do |image|
       image.start_at 0, 20
       image.width=360
-      image.height=333 
+      image.height=333
     end
     ws.add_image :image_src => File.expand_path('../../examples/image1.png', __FILE__) do |image|
       image.start_at 9, 20
@@ -69,6 +70,9 @@ class TestPackage < Test::Unit::TestCase
       image.height = 167
     end
     ws.add_table 'A1:C1'
+
+    ws.add_pivot_table 'G5:G6', 'A1:B3'
+
   end
 
   def test_use_autowidth
@@ -135,10 +139,13 @@ class TestPackage < Test::Unit::TestCase
     assert_equal(p.select{ |part| part[:entry] =~ /xl\/worksheets\/sheet\d\.xml/ }.size, @package.workbook.worksheets.size, "one or more sheet missing")
     assert_equal(p.select{ |part| part[:entry] =~ /xl\/worksheets\/_rels\/sheet\d\.xml\.rels/ }.size, @package.workbook.worksheets.size, "one or more sheet rels missing")
     assert_equal(p.select{ |part| part[:entry] =~ /xl\/comments\d\.xml/ }.size, @package.workbook.worksheets.size, "one or more sheet rels missing")
+    assert_equal(p.select{ |part| part[:entry] =~ /xl\/pivotTables\/pivotTable\d\.xml/ }.size, @package.workbook.worksheets.first.pivot_tables.size, "one or more pivot tables missing")
+    assert_equal(p.select{ |part| part[:entry] =~ /xl\/pivotTables\/_rels\/pivotTable\d\.xml.rels/ }.size, @package.workbook.worksheets.first.pivot_tables.size, "one or more pivot tables rels missing")
+    assert_equal(p.select{ |part| part[:entry] =~ /xl\/pivotCache\/pivotCacheDefinition\d\.xml/ }.size, @package.workbook.worksheets.first.pivot_tables.size, "one or more pivot tables missing")
 
 
     #no mystery parts
-    assert_equal(p.size, 21)
+    assert_equal(p.size, 24)
 
   end
 
diff --git a/test/workbook/worksheet/tc_pivot_table.rb b/test/workbook/worksheet/tc_pivot_table.rb
new file mode 100644
index 00000000..3a94cfae
--- /dev/null
+++ b/test/workbook/worksheet/tc_pivot_table.rb
@@ -0,0 +1,101 @@
+require 'tc_helper.rb'
+
+class TestPivotTable < Test::Unit::TestCase
+  def setup
+    p = Axlsx::Package.new
+    @ws = p.workbook.add_worksheet
+
+    @ws << ["Year","Month","Region", "Type", "Sales"]
+    @ws << [2012,  "Nov",  "East",   "Soda", "12345"]
+  end
+
+  def test_initialization
+    assert(@ws.workbook.pivot_tables.empty?)
+    assert(@ws.pivot_tables.empty?)
+  end
+
+  def test_add_pivot_table
+    pivot_table = @ws.add_pivot_table('G5:G6', 'A1:D5')
+    assert_equal('G5:G6', pivot_table.ref, 'ref assigned from first parameter')
+    assert_equal('A1:D5', pivot_table.range, 'range assigned from second parameter')
+    assert_equal('PivotTable1', pivot_table.name, 'name automatically generated')
+    assert(pivot_table.is_a?(Axlsx::PivotTable), "must create a pivot table")
+    assert_equal(@ws.workbook.pivot_tables.last, pivot_table, "must be added to workbook pivot tables collection")
+    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")
+  end
+
+  def test_rId
+    @ws.add_pivot_table('G5:G6', 'A1:D5')
+    assert_equal(@ws.pivot_tables.first.rId, "rId1")
+  end
+
+  def test_index
+    @ws.add_pivot_table('G5:G6', 'A1:D5')
+    assert_equal(@ws.pivot_tables.first.index, @ws.workbook.pivot_tables.index(@ws.pivot_tables.first))
+  end
+
+  def test_relationships
+    assert(@ws.relationships.empty?)
+    @ws.add_pivot_table('G5:G6', 'A1:D5')
+    assert_equal(@ws.relationships.size, 1, "adding a pivot table adds a relationship")
+    @ws.add_pivot_table('G10:G11', 'A1:D5')
+    assert_equal(@ws.relationships.size, 2, "adding a pivot table adds a relationship")
+  end
+
+  def test_to_xml_string
+    pivot_table = @ws.add_pivot_table('G5:G6', 'A1:D5')
+    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
+
+  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
new file mode 100644
index 00000000..2b4389b7
--- /dev/null
+++ b/test/workbook/worksheet/tc_pivot_table_cache_definition.rb
@@ -0,0 +1,46 @@
+require 'tc_helper.rb'
+
+class TestPivotTableCacheDefinition < Test::Unit::TestCase
+  def setup
+    p = Axlsx::Package.new
+    @ws = p.workbook.add_worksheet
+    5.times do
+      @ws << ["aa","aa","aa","aa"]
+    end
+    @pivot_table = @ws.add_pivot_table('G5:G6', 'A1:D5')
+    @cache_definition = @pivot_table.cache_definition
+  end
+
+  def test_initialization
+    assert(@cache_definition.is_a?(Axlsx::PivotTableCacheDefinition), "must create a pivot table cache definition")
+    assert_equal(@pivot_table, @cache_definition.pivot_table, 'refers back to its pivot table')
+  end
+
+  def test_pn
+    assert_equal('pivotCache/pivotCacheDefinition1.xml', @cache_definition.pn)
+  end
+
+  def test_rId
+    assert_equal('rId1', @cache_definition.rId)
+  end
+
+  def test_index
+    assert_equal(0, @cache_definition.index)
+  end
+
+  def test_cache_id
+    assert_equal(1, @cache_definition.cache_id)
+  end
+
+  def test_to_xml_string
+    schema = Nokogiri::XML::Schema(File.open(Axlsx::SML_XSD))
+    doc = Nokogiri::XML(@cache_definition.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_worksheet.rb b/test/workbook/worksheet/tc_worksheet.rb
index da90ffa0..5d5e14a4 100644
--- a/test/workbook/worksheet/tc_worksheet.rb
+++ b/test/workbook/worksheet/tc_worksheet.rb
@@ -168,6 +168,12 @@ class TestWorksheet < Test::Unit::TestCase
     assert @ws.drawing.is_a?(Axlsx::Drawing)
   end
 
+  def test_add_pivot_table
+    assert(@ws.workbook.pivot_tables.empty?, "the sheet's workbook should not have any pivot tables by default")
+    @ws.add_pivot_table 'G5:G6', 'A1:D:10'
+    assert_equal(@ws.workbook.pivot_tables.size, 1, "add_pivot_tables adds a pivot_table to the workbook")
+  end
+
   def test_col_style
     @ws.add_row [1,2,3,4]
     @ws.add_row [1,2,3,4]
@@ -366,6 +372,7 @@ class TestWorksheet < Test::Unit::TestCase
     @ws.merge_cells "A4:A5"
     @ws.add_chart Axlsx::Pie3DChart
     @ws.add_table "E1:F3"
+    @ws.add_pivot_table  'G5:G6', 'A1:D10'
     schema = Nokogiri::XML::Schema(File.open(Axlsx::SML_XSD))
     doc = Nokogiri::XML(@ws.to_xml_string)
     assert(schema.validate(doc).map { |e| puts e.message; e }.empty?, schema.validate(doc).map { |e| e.message }.join('\n'))
@@ -382,6 +389,8 @@ class TestWorksheet < Test::Unit::TestCase
     assert_equal(@ws.relationships.size, 4, "adding a comment adds 3 relationships")
     c = @ws.add_comment :text => 'not that is a comment!', :author => 'travis', :ref => "A1"
     assert_equal(@ws.relationships.size, 4, "adding multiple comments in the same worksheet should not add any additional comment relationships")
+    c = @ws.add_pivot_table 'G5:G6', 'A1:D10'
+    assert_equal(@ws.relationships.size, 5, "adding a pivot table adds 1 relationship")
   end