From 4560bd0a1b8b46bf4d8c0783f9fa12e8ceee714f Mon Sep 17 00:00:00 2001 From: Alex Rothenberg Date: Tue, 27 Nov 2012 09:53:30 -0500 Subject: 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...) --- examples/pivot_table.rb | 34 ++++++++++++++++++++++++++++++++++ 1 file changed, 34 insertions(+) create mode 100644 examples/pivot_table.rb (limited to 'examples') diff --git a/examples/pivot_table.rb b/examples/pivot_table.rb new file mode 100644 index 00000000..229ed8c9 --- /dev/null +++ b/examples/pivot_table.rb @@ -0,0 +1,34 @@ +#!/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" +end + +# Write the excel file +p.serialize("pivot_table.xlsx") -- cgit v1.2.3 From 036f5883939a91fbc3eb377d968d85500dc3098a Mon Sep 17 00:00:00 2001 From: Alex Rothenberg Date: Tue, 27 Nov 2012 13:45:31 -0500 Subject: 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 --- examples/pivot_table.rb | 7 +- lib/axlsx.rb | 12 ++ lib/axlsx/workbook/worksheet/pivot_table.rb | 140 ++++++++++++++++++++- .../worksheet/pivot_table_cache_definition.rb | 6 +- test/tc_axlsx.rb | 6 + test/workbook/worksheet/tc_pivot_table.rb | 45 ++++++- .../worksheet/tc_pivot_table_cache_definition.rb | 2 +- 7 files changed, 204 insertions(+), 14 deletions(-) (limited to 'examples') 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 << '' str << '' str << '' - header_cells_count.times do - str << '' + header_cell_values.each do |cell_value| + str << pivot_field_for(cell_value) end str << '' + if rows.empty? + str << '' + str << ' ' + else + str << '' + rows.each do |row_value| + str << '' + end + str << '' + str << '' + rows.size.times do |i| + str << '' + end + str << '' + end + if columns.empty? + str << '' + else + str << '' + columns.each do |column_value| + str << '' + end + str << '' + end + unless pages.empty? + str << '' + pages.each do |page_value| + str << '' + end + str << '' + end + unless data.empty? + str << '' + data.each do |datum_value| + str << '' + end + str << '' + end str << '' 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 + '' << + '' << + '' + elsif columns.include? cell_ref + '' << + '' << + '' + elsif pages.include? cell_ref + '' << + '' << + '' + elsif data.include? cell_ref + '' << + '' + else + '' << + '' + 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 << '' str << '' str << '' - pivot_table.header_cells_count.times do |i| - str << '' + pivot_table.header_cells.each do |cell| + str << '' str << '' str << '' str << '' 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 -- cgit v1.2.3