diff options
Diffstat (limited to 'lib')
| -rw-r--r-- | lib/axlsx.rb | 12 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/pivot_table.rb | 140 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/pivot_table_cache_definition.rb | 6 |
3 files changed, 149 insertions, 9 deletions
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 << '<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_cells_count.times do - str << '<pivotField compact="0" outline="0" subtotalTop="0" showAll="0" includeNewItemsInFilter="1"/>' + 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 - 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 + '<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 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 << '<worksheetSource ref="' << pivot_table.range << '" sheet="Data Sheet"/>' str << '</cacheSource>' str << '<cacheFields count="' << pivot_table.header_cells_count.to_s << '">' - pivot_table.header_cells_count.times do |i| - str << '<cacheField name="placeholder_' << i.to_s << '" numFmtId="0">' + pivot_table.header_cells.each do |cell| + str << '<cacheField name="' << cell.value << '" numFmtId="0">' str << '<sharedItems count="0">' str << '</sharedItems>' str << '</cacheField>' |