From 35d3cc8b21bce0c4ce7b9ec4e29d52df4b5f9cc4 Mon Sep 17 00:00:00 2001
From: Jean Jacques Warmerdam <sjaq@koelka.st>
Date: Mon, 25 Feb 2013 14:57:00 +0100
Subject: Allow other sheet as data source for pivot tables and fix hard coded
 sheet name in Pivot Table Cache Definition

---
 lib/axlsx/workbook/worksheet/pivot_table.rb                  | 12 +++++++++++-
 lib/axlsx/workbook/worksheet/pivot_table_cache_definition.rb |  2 +-
 2 files changed, 12 insertions(+), 2 deletions(-)

diff --git a/lib/axlsx/workbook/worksheet/pivot_table.rb b/lib/axlsx/workbook/worksheet/pivot_table.rb
index 4ebcdee8..94edf80e 100644
--- a/lib/axlsx/workbook/worksheet/pivot_table.rb
+++ b/lib/axlsx/workbook/worksheet/pivot_table.rb
@@ -19,6 +19,7 @@ module Axlsx
       @sheet = sheet
       @sheet.workbook.pivot_tables << self
       @name = "PivotTable#{index+1}"
+      @data_sheet = nil
       @rows = []
       @columns = []
       @data = []
@@ -39,6 +40,15 @@ module Axlsx
     # @return [String]
     attr_reader :sheet
 
+    # The sheet used as data source for the pivot table
+    # @return [Worksheet]
+    attr_writer :data_sheet
+
+    # (see #data_sheet)
+    def data_sheet
+      @data_sheet || @sheet
+    end
+
     # The range where the data for this pivot table lives.
     # @return [String]
     attr_reader :range
@@ -206,7 +216,7 @@ module Axlsx
     # The header cells for the pivot table
     # @return [Array]
     def header_cells
-      @sheet[header_range]
+      data_sheet[header_range]
     end
 
     # The values in the header cells collection
diff --git a/lib/axlsx/workbook/worksheet/pivot_table_cache_definition.rb b/lib/axlsx/workbook/worksheet/pivot_table_cache_definition.rb
index c1683520..37f46c51 100644
--- a/lib/axlsx/workbook/worksheet/pivot_table_cache_definition.rb
+++ b/lib/axlsx/workbook/worksheet/pivot_table_cache_definition.rb
@@ -48,7 +48,7 @@ module Axlsx
       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 <<     '<worksheetSource ref="' << pivot_table.range << '" sheet="' << pivot_table.data_sheet.name << '"/>'
       str <<   '</cacheSource>'
       str <<   '<cacheFields count="' << pivot_table.header_cells_count.to_s << '">'
       pivot_table.header_cells.each do |cell|
-- 
cgit v1.2.3


From b980d98fe610c988e50394c81ddba1c0c3ed73f5 Mon Sep 17 00:00:00 2001
From: Jean Jacques Warmerdam <sjaq@koelka.st>
Date: Wed, 24 Jul 2013 13:02:58 +0200
Subject: Add tests for data source option for pivot tables

---
 test/workbook/worksheet/tc_pivot_table.rb                  | 10 ++++++++++
 test/workbook/worksheet/tc_pivot_table_cache_definition.rb |  8 ++++++++
 2 files changed, 18 insertions(+)

diff --git a/test/workbook/worksheet/tc_pivot_table.rb b/test/workbook/worksheet/tc_pivot_table.rb
index ee90bec2..56ac4bd4 100644
--- a/test/workbook/worksheet/tc_pivot_table.rb
+++ b/test/workbook/worksheet/tc_pivot_table.rb
@@ -36,6 +36,16 @@ class TestPivotTable < Test::Unit::TestCase
     assert_equal(@ws.pivot_tables.last, pivot_table, "must be added to worksheet pivot tables collection")
   end
 
+  def test_set_pivot_table_data_sheet
+    pivot_table = @ws.add_pivot_table('G5:G6', 'A1:D5')
+    data_sheet = @ws.clone
+    data_sheet.name = "Pivot Table Data Source"
+
+    assert_equal(pivot_table.data_sheet.name, @ws.name, "must default to the same sheet the pivot table is added to")
+    pivot_table.data_sheet = data_sheet
+    assert_equal(pivot_table.data_sheet.name, data_sheet.name, "data sheet assigned to pivot table")
+  end
+
   def test_add_pivot_table_with_config
     pivot_table = @ws.add_pivot_table('G5:G6', 'A1:D5') do |pt|
       pt.rows = ['Year', 'Month']
diff --git a/test/workbook/worksheet/tc_pivot_table_cache_definition.rb b/test/workbook/worksheet/tc_pivot_table_cache_definition.rb
index a38e808a..282078b0 100644
--- a/test/workbook/worksheet/tc_pivot_table_cache_definition.rb
+++ b/test/workbook/worksheet/tc_pivot_table_cache_definition.rb
@@ -32,6 +32,14 @@ class TestPivotTableCacheDefinition < Test::Unit::TestCase
     assert_equal(1, @cache_definition.cache_id)
   end
 
+  def test_data_sheet
+    data_sheet = @ws.clone
+    data_sheet.name = "Pivot Table Data Source"
+    @pivot_table.data_sheet = data_sheet
+
+    assert(@cache_definition.to_xml_string.include?(data_sheet.name), "must set the data source correctly")
+  end
+
   def test_to_xml_string
     schema = Nokogiri::XML::Schema(File.open(Axlsx::SML_XSD))
     doc = Nokogiri::XML(@cache_definition.to_xml_string)
-- 
cgit v1.2.3