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

Parameters:

  • ref (String) —

    The reference to where the pivot table lives like 'G4:L17'.

  • range (String) —

    The reference to the pivot table data like 'A1:D31'.

  • sheet (Worksheet) —

    The sheet containing the table data.

  • options (Hash) (defaults to: {}) —

    a customizable set of options

Options Hash (options):

Yields:

  • (_self)

Yield Parameters:



16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
 
# 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
  @no_subtotals_on_headers = []
  @style_info = {}
  parse_options options
  yield self if block_given?
end

Instance Attribute Details

#columns ⇒ Array

The columns

Returns:

  • (Array) 


91
92
93
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 91

def columns
  @columns
end

#data ⇒ Array

The data

Returns:

  • (Array) 


104
105
106
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 104

def data
  @data
end

#data_sheet ⇒ Object

See Also:



59
60
61
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 59

def data_sheet
  @data_sheet || @sheet
end

#name ⇒ String (readonly)

The name of the table.

Returns:

  • (String) 


48
49
50
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 48

def name
  @name
end

#no_subtotals_on_headers ⇒ Object

Defines the headers in which subtotals are not to be included @return[Array]



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

def no_subtotals_on_headers
  @no_subtotals_on_headers
end

#pages ⇒ String

The pages

Returns:

  • (String) 


124
125
126
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 124

def pages
  @pages
end

#range ⇒ String

The range where the data for this pivot table lives.

Returns:

  • (String) 


65
66
67
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 65

def range
  @range
end

#ref ⇒ String (readonly)

The reference to the table data

Returns:

  • (String) 


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

def ref
  @ref
end

#rows ⇒ Array

The rows

Returns:

  • (Array) 


77
78
79
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 77

def rows
  @rows
end

#sheet ⇒ String (readonly)

The name of the sheet.

Returns:

  • (String) 


52
53
54
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 52

def sheet
  @sheet
end

#style_info ⇒ Object

Style info for the pivot table @return[Hash]



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

def style_info
  @style_info
end

Instance Method Details

#cache_definition ⇒ PivotTableCacheDefinition

The cache_definition for this pivot table

Returns:



155
156
157
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 155

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

#header_cell_refs ⇒ Array

References for header cells

Returns:

  • (Array) 


233
234
235
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 233

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

#header_cell_values ⇒ Array

The values in the header cells collection

Returns:

  • (Array) 


245
246
247
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 245

def header_cell_values
  header_cells.map(&:value)
end

#header_cells ⇒ Array

The header cells for the pivot table

Returns:

  • (Array) 


239
240
241
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 239

def header_cells
  data_sheet[header_range]
end

#header_cells_count ⇒ Integer

The number of cells in the header_cells collection

Returns:

  • (Integer) 


251
252
253
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 251

def header_cells_count
  header_cells.count
end

#header_index_of(value) ⇒ Integer

The index of a given value in the header cells

Returns:

  • (Integer) 


257
258
259
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 257

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

#index ⇒ Integer

The index of this chart in the workbooks charts collection

Returns:

  • (Integer) 


137
138
139
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 137

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

#pn ⇒ String

The part name for this table

Returns:

  • (String) 


143
144
145
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 143

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

#relationships ⇒ Relationships

The relationships for this pivot table.

Returns:



161
162
163
164
165
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 161

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

#rels_pn ⇒ String

The relationship part name of this pivot table

Returns:

  • (String) 


149
150
151
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 149

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

#to_xml_string(str = '') ⇒ String

Serializes the object

Parameters:

  • str (String) (defaults to: '')

Returns:

  • (String) 


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
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 170

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, !no_subtotals_on_headers.include?(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|
      # The correct name prefix in ["Sum","Average", etc...]
      str << "<dataField name='#{(datum_value[: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
  # custom pivot table style
  unless style_info.empty?
    str << '<pivotTableStyleInfo'
    style_info.each do |k,v|
      str << ' ' << k.to_s << '="' << v.to_s << '"'
    end
    str << ' />'
  end
  str << '</pivotTableDefinition>'
end