Class: Axlsx::PivotTable

Inherits:
Object
  • Object
show all
Includes:
OptionsParser
Defined in:
lib/axlsx/workbook/worksheet/pivot_table.rb

Overview

Note:

Worksheet#add_pivot_table is the recommended way to create tables for your worksheets.

Table

See Also:

  • for examples

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from OptionsParser

#parse_options

Constructor Details

#initialize(ref, range, sheet, options = {}) {|_self| ... } ⇒ PivotTable

Creates a new PivotTable object

Options Hash (options):

Yields:

  • (_self)

Yield Parameters:



16
17
18
19
20
21
22
23
24
25
26
27
28
 
# 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}"
  @rows = []
  @columns = []
  @data = []
  @pages = []
  parse_options options
  yield self if block_given?
end

Instance Attribute Details

#columns ⇒ Array

The columns



70
71
72
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 70

def columns
  @columns
end

#data ⇒ Array

The data



83
84
85
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 83

def data
  @data
end

#name ⇒ String (readonly)

The name of the table.



36
37
38
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 36

def name
  @name
end

#pages ⇒ String

The pages



96
97
98
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 96

def pages
  @pages
end

#range ⇒ String

The range where the data for this pivot table lives.



44
45
46
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 44

def range
  @range
end

#ref ⇒ String (readonly)

The reference to the table data



32
33
34
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 32

def ref
  @ref
end

#rows ⇒ Array

The rows



56
57
58
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 56

def rows
  @rows
end

#sheet ⇒ String (readonly)

The name of the sheet.



40
41
42
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 40

def sheet
  @sheet
end

Instance Method Details

#cache_definition ⇒ PivotTableCacheDefinition

The cache_definition for this pivot table



127
128
129
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 127

def cache_definition
  @cache_definition ||= PivotTableCacheDefinition.new(self)
end

#header_cell_refs ⇒ Array

References for header cells



202
203
204
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 202

def header_cell_refs
  Axlsx::range_to_a(header_range).first
end

#header_cell_values ⇒ Array

The values in the header cells collection



214
215
216
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 214

def header_cell_values
  header_cells.map(&:value)
end

#header_cells ⇒ Array

The header cells for the pivot table



208
209
210
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 208

def header_cells
  @sheet[header_range]
end

#header_cells_count ⇒ Integer

The number of cells in the header_cells collection



220
221
222
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 220

def header_cells_count
  header_cells.count
end

#header_index_of(value) ⇒ Integer

The index of a given value in the header cells



226
227
228
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 226

def header_index_of(value)
  header_cell_values.index(value)
end

#index ⇒ Integer

The index of this chart in the workbooks charts collection



109
110
111
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 109

def index
  @sheet.workbook.pivot_tables.index(self)
end

#pn ⇒ String

The part name for this table



115
116
117
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 115

def pn
  "#{PIVOT_TABLE_PN % (index+1)}"
end

#relationships ⇒ Relationships

The worksheet relationships. This is managed automatically by the worksheet



133
134
135
136
137
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 133

def relationships
  r = Relationships.new
  r << Relationship.new(PIVOT_TABLE_CACHE_DEFINITION_R, "../#{cache_definition.pn}")
  r
end

#rels_pn ⇒ String

The relationship part name of this pivot table



121
122
123
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 121

def rels_pn
  "#{PIVOT_TABLE_RELS_PN % (index+1)}"
end

#rId ⇒ String

The relation reference id for this table



141
142
143
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 141

def rId
  "rId#{index+1}"
end

#to_xml_string(str = '') ⇒ String

Serializes the object



148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 148

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.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