class Axlsx::PivotTable

Table @note Worksheet#add_pivot_table is the recommended way to create tables for your worksheets. @see README for examples

Attributes

columns[R]

The columns @return [Array]

data[R]

The data @return [Array]

data_sheet[W]

The sheet used as data source for the pivot table @return [Worksheet]

name[R]

The name of the table. @return [String]

pages[R]

The pages @return [String]

range[R]

The range where the data for this pivot table lives. @return [String]

ref[R]

The reference to the table data @return [String]

rows[R]

The rows @return [Array]

sheet[R]

The name of the sheet. @return [String]

Public Class Methods

new(ref, range, sheet, options={}) { |self| ... } click to toggle source

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

# File lib/axlsx/workbook/worksheet/pivot_table.rb, line 16
def initialize(ref, range, sheet, options={})
  @ref = ref
  self.range = range
  @sheet = sheet
  @sheet.workbook.pivot_tables << self
  @name = "PivotTable#{index+1}"
  @data_sheet = nil
  @rows = []
  @columns = []
  @data = []
  @pages = []
  @subtotal = nil
  parse_options options
  yield self if block_given?
end

Public Instance Methods

cache_definition() click to toggle source

The cache_definition for this pivot table @return [PivotTableCacheDefinition]

# File lib/axlsx/workbook/worksheet/pivot_table.rb, line 145
def cache_definition
  @cache_definition ||= PivotTableCacheDefinition.new(self)
end
columns=(v) click to toggle source

(see columns)

# File lib/axlsx/workbook/worksheet/pivot_table.rb, line 84
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
data=(v) click to toggle source

(see data)

# File lib/axlsx/workbook/worksheet/pivot_table.rb, line 97
def data=(v)
  DataTypeValidator.validate "#{self.class}.data", [Array], v
  @data = []
  v.each do |data_field|
    if data_field.is_a? String
      data_field = {:ref => data_field}
    end
    data_field.values.each do |value|
      DataTypeValidator.validate "#{self.class}.data[]", [String], value
    end
    @data << data_field
  end
  @data
end
data_sheet() click to toggle source

@see data_sheet

# File lib/axlsx/workbook/worksheet/pivot_table.rb, line 49
def data_sheet
  @data_sheet || @sheet
end
header_cell_refs() click to toggle source

References for header cells @return [Array]

# File lib/axlsx/workbook/worksheet/pivot_table.rb, line 214
def header_cell_refs
  Axlsx::range_to_a(header_range).first
end
header_cell_values() click to toggle source

The values in the header cells collection @return [Array]

# File lib/axlsx/workbook/worksheet/pivot_table.rb, line 226
def header_cell_values
  header_cells.map(&:value)
end
header_cells() click to toggle source

The header cells for the pivot table @return [Array]

# File lib/axlsx/workbook/worksheet/pivot_table.rb, line 220
def header_cells
  data_sheet[header_range]
end
header_cells_count() click to toggle source

The number of cells in the header_cells collection @return [Integer]

# File lib/axlsx/workbook/worksheet/pivot_table.rb, line 232
def header_cells_count
  header_cells.count
end
header_index_of(value) click to toggle source

The index of a given value in the header cells @return [Integer]

# File lib/axlsx/workbook/worksheet/pivot_table.rb, line 238
def header_index_of(value)
  header_cell_values.index(value)
end
index() click to toggle source

The index of this chart in the workbooks charts collection @return [Integer]

# File lib/axlsx/workbook/worksheet/pivot_table.rb, line 127
def index
  @sheet.workbook.pivot_tables.index(self)
end
pages=(v) click to toggle source

(see pages)

# File lib/axlsx/workbook/worksheet/pivot_table.rb, line 117
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
pn() click to toggle source

The part name for this table @return [String]

# File lib/axlsx/workbook/worksheet/pivot_table.rb, line 133
def pn
  "#{PIVOT_TABLE_PN % (index+1)}"
end
range=(v) click to toggle source

(see range)

# File lib/axlsx/workbook/worksheet/pivot_table.rb, line 58
def range=(v)
  DataTypeValidator.validate "#{self.class}.range", [String], v
  if v.is_a?(String)
    @range = v
  end
end
relationships() click to toggle source

The relationships for this pivot table. @return [Relationships]

# File lib/axlsx/workbook/worksheet/pivot_table.rb, line 151
def relationships
  r = Relationships.new
  r << Relationship.new(cache_definition, PIVOT_TABLE_CACHE_DEFINITION_R, "../#{cache_definition.pn}")
  r
end
rels_pn() click to toggle source

The relationship part name of this pivot table @return [String]

# File lib/axlsx/workbook/worksheet/pivot_table.rb, line 139
def rels_pn
  "#{PIVOT_TABLE_RELS_PN % (index+1)}"
end
rows=(v) click to toggle source

(see rows)

# File lib/axlsx/workbook/worksheet/pivot_table.rb, line 71
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
to_xml_string(str = '') click to toggle source

Serializes the object @param [String] str @return [String]

# File lib/axlsx/workbook/worksheet/pivot_table.rb, line 160
def to_xml_string(str = '')
  str << '<?xml version="1.0" encoding="UTF-8"?>'
  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_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}\">"
    data.each do |datum_value|
      str << "<dataField name='#{@subtotal} of #{datum_value[:ref]}' fld='#{header_index_of(datum_value[:ref])}' baseField='0' baseItem='0'"
      str << " subtotal='#{datum_value[:subtotal]}' " if datum_value[:subtotal]
      str << "/>"
    end
    str << '</dataFields>'
  end
  str << '</pivotTableDefinition>'
end

Private Instance Methods

data_refs() click to toggle source
# File lib/axlsx/workbook/worksheet/pivot_table.rb, line 258
def data_refs
  data.map { |hash| hash[:ref] }
end
header_range() click to toggle source
# File lib/axlsx/workbook/worksheet/pivot_table.rb, line 262
def header_range
  range.gsub(/^(\w+?)(\d+)\:(\w+?)\d+$/, '\1\2:\3\2')
end
pivot_field_for(cell_ref) click to toggle source
# File lib/axlsx/workbook/worksheet/pivot_table.rb, line 244
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_refs.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