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 ++++++ lib/axlsx/package.rb | 13 +++ lib/axlsx/rels/relationship.rb | 1 + lib/axlsx/util/constants.rb | 21 +++- lib/axlsx/util/validators.rb | 5 +- lib/axlsx/workbook/workbook.rb | 28 ++++- lib/axlsx/workbook/worksheet/pivot_table.rb | 121 +++++++++++++++++++++ .../worksheet/pivot_table_cache_definition.rb | 63 +++++++++++ lib/axlsx/workbook/worksheet/pivot_tables.rb | 24 ++++ lib/axlsx/workbook/worksheet/worksheet.rb | 33 ++++-- test/tc_package.rb | 19 +++- test/workbook/worksheet/tc_pivot_table.rb | 62 +++++++++++ .../worksheet/tc_pivot_table_cache_definition.rb | 46 ++++++++ test/workbook/worksheet/tc_worksheet.rb | 19 +++- 14 files changed, 461 insertions(+), 28 deletions(-) create mode 100644 examples/pivot_table.rb create mode 100644 lib/axlsx/workbook/worksheet/pivot_table.rb create mode 100644 lib/axlsx/workbook/worksheet/pivot_table_cache_definition.rb create mode 100644 lib/axlsx/workbook/worksheet/pivot_tables.rb create mode 100644 test/workbook/worksheet/tc_pivot_table.rb create mode 100644 test/workbook/worksheet/tc_pivot_table_cache_definition.rb 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") 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 99c8e112..b19a18f7 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 9c1ec741..03f0c89a 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 @@ -221,14 +222,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 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 << '' + unless pivot_tables.empty? + str << '' + pivot_tables.each_with_index do |pivot_table, index| + rId = "rId#{@worksheets.size + index + 1 }" + str << '' + end + str << '' + end defined_names.to_xml_string(str) str << '' 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 << '' + str << '' + str << '' + str << '' + header_cells_count.times do + str << '' + end + str << '' + str << '' + 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 << '' + str << '' + str << '' + str << '' + str << '' + str << '' + pivot_table.header_cells_count.times do |i| + str << '' + str << '' + str << '' + str << '' + end + str << '' + str << '' + 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 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..6f1f3f7a --- /dev/null +++ b/test/workbook/worksheet/tc_pivot_table.rb @@ -0,0 +1,62 @@ +require 'tc_helper.rb' + +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 + 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_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 + +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..78563f7d --- /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(0, @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 4c8f2822..c488e83d 100644 --- a/test/workbook/worksheet/tc_worksheet.rb +++ b/test/workbook/worksheet/tc_worksheet.rb @@ -153,6 +153,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] @@ -317,7 +323,7 @@ class TestWorksheet < Test::Unit::TestCase def test_styles assert(@ws.styles.is_a?(Axlsx::Styles), 'worksheet provides access to styles') end - + def test_to_xml_string_with_illegal_chars nasties = "\v\u2028\u0001\u0002\u0003\u0004\u0005\u0006\u0007\u0008\u001f" @ws.add_row [nasties] @@ -341,6 +347,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')) @@ -357,6 +364,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 @@ -400,7 +409,7 @@ class TestWorksheet < Test::Unit::TestCase @ws.add_row [1, 2, 3] assert_nothing_raised {@ws.protect_range(@ws.rows.first.cells) } assert_equal('A1:C1', @ws.send(:protected_ranges).last.sqref) - + end def test_merge_cells @ws.add_row [1,2,3] @@ -412,7 +421,7 @@ class TestWorksheet < Test::Unit::TestCase assert_equal(@ws.send(:merged_cells).size, 3) assert_equal(@ws.send(:merged_cells).last, "A3:B3") end - + def test_merge_cells_sorts_correctly_by_row_when_given_array 10.times do |i| @ws.add_row [i] @@ -420,7 +429,7 @@ class TestWorksheet < Test::Unit::TestCase @ws.merge_cells [@ws.rows[8].cells.first, @ws.rows[9].cells.first] assert_equal "A9:A10", @ws.send(:merged_cells).first end - + def test_auto_filter assert(@ws.auto_filter.range.nil?) assert_raise(ArgumentError) { @ws.auto_filter = 123 } @@ -432,6 +441,6 @@ class TestWorksheet < Test::Unit::TestCase @ws.auto_filter.range = 'A1:D9' @ws.auto_filter.add_column 0, :filters, :filter_items => [1] doc = Nokogiri::XML(@ws.to_xml_string) - assert(doc.xpath('//sheetPr[@filterMode="true"]')) + assert(doc.xpath('//sheetPr[@filterMode="true"]')) end end -- cgit v1.2.3