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...) --- 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 ++++-- 9 files changed, 292 insertions(+), 17 deletions(-) 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 (limited to 'lib') 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 -- 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 'lib') 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