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
33
34
35
36
 
# 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 = []
  @grand_totals = :both
  @sort_on_headers = {}
  @style_info = {}
  @use_auto_formatting = true
  @apply_width_height_formats = true
  parse_options options
  yield self if block_given?
end

Instance Attribute Details

#apply_width_height_formats ⇒ Boolean

Whether to apply width/height formats to the pivot table.

Returns:

  • (Boolean) 


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

def apply_width_height_formats
  @apply_width_height_formats
end

#columns ⇒ Array

The columns

Returns:

  • (Array) 


130
131
132
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 130

def columns
  @columns
end

#data ⇒ Array

The data as an array of either headers (String) or hashes or mix of the two. Hash in format of { ref: header, num_fmt: numFmts, subtotal: subtotal }, where header is String, numFmts is Integer, and subtotal one of %w[sum count average max min product countNums stdDev stdDevp var varp]; leave subtotal blank to sum values

Returns:

  • (Array) 


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

def data
  @data
end

#data_sheet ⇒ Object

See Also:



99
100
101
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 99

def data_sheet
  @data_sheet || @sheet
end

#grand_totals ⇒ Symbol

Defines which Grand Totals are to be shown. Defaults to :both to show both row & column grand totals. Set to :row_only, :col_only, or :none to hide one or both Grand Totals.

Returns:

  • (Symbol) —

    The row and/or column Grand Totals that are to be shown.



61
62
63
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 61

def grand_totals
  @grand_totals
end

#name ⇒ String (readonly)

The name of the table.

Returns:

  • (String) 


80
81
82
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 80

def name
  @name
end

#no_subtotals_on_headers ⇒ Array

Defines the headers in which subtotals are not to be included.

Returns:

  • (Array) 


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

def no_subtotals_on_headers
  @no_subtotals_on_headers
end

#pages ⇒ String

The pages

Returns:

  • (String) 


167
168
169
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 167

def pages
  @pages
end

#range ⇒ String

The range where the data for this pivot table lives.

Returns:

  • (String) 


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

def range
  @range
end

#ref ⇒ String (readonly)

The reference to the table data

Returns:

  • (String) 


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

def ref
  @ref
end

#rows ⇒ Array

The rows

Returns:

  • (Array) 


117
118
119
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 117

def rows
  @rows
end

#sheet ⇒ String (readonly)

The name of the sheet.

Returns:

  • (String) 


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

def sheet
  @sheet
end

#sort_on_headers ⇒ Hash

Defines the headers in which sort is applied. Can be an array of headers to sort ascending by default, or a hash for specific control (with headers as keys, :ascending or :descending as values).

Examples: ["year", "month"] or {"year" => :descending, "month" => :descending}

Returns:

  • (Hash) 


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

def sort_on_headers
  @sort_on_headers
end

#style_info ⇒ Hash

Style info for the pivot table

Returns:

  • (Hash) 


72
73
74
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 72

def style_info
  @style_info
end

#use_auto_formatting ⇒ Boolean

Whether to apply auto formatting to the pivot table.

Returns:

  • (Boolean) 


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

def use_auto_formatting
  @use_auto_formatting
end

Instance Method Details

#cache_definition ⇒ PivotTableCacheDefinition

The cache_definition for this pivot table

Returns:



198
199
200
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 198

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

#header_cell_refs ⇒ Array

References for header cells

Returns:

  • (Array) 


315
316
317
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 315

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) 


327
328
329
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 327

def header_cell_values
  header_cells.map(&:value)
end

#header_cells ⇒ Array

The header cells for the pivot table

Returns:

  • (Array) 


321
322
323
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 321

def header_cells
  data_sheet[header_range]
end

#header_cells_count ⇒ Integer

The number of cells in the header_cells collection

Returns:

  • (Integer) 


333
334
335
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 333

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) 


339
340
341
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 339

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) 


180
181
182
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 180

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

#pn ⇒ String

The part name for this table

Returns:

  • (String) 


186
187
188
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 186

def pn
  format(PIVOT_TABLE_PN, index + 1)
end

#relationships ⇒ Relationships

The relationships for this pivot table.

Returns:



204
205
206
207
208
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 204

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) 


192
193
194
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 192

def rels_pn
  format(PIVOT_TABLE_RELS_PN, index + 1)
end

#to_xml_string(str = +'')) ⇒ String

Serializes the object

Parameters:

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

Returns:

  • (String) 


213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
 
# File 'lib/axlsx/workbook/worksheet/pivot_table.rb', line 213

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 << '"'
  str << ' dataOnRows="1"' if data.size <= 1
  str << ' rowGrandTotals="0"' if grand_totals == :col_only || grand_totals == :none
  str << ' colGrandTotals="0"' if grand_totals == :row_only || grand_totals == :none
  str << ' applyNumberFormats="0" applyBorderFormats="0" applyFontFormats="0" applyPatternFormats="0" applyAlignmentFormats="0"'
  str << ' applyWidthHeightFormats="' << (@apply_width_height_formats ? '1' : '0') << '"'
  str << ' dataCaption="Data" showMultipleLabel="0" showMemberPropertyTips="0"'
  str << ' useAutoFormatting="' << (@use_auto_formatting ? '1' : '0') << '"'
  str << ' 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|
    subtotal = !no_subtotals_on_headers.include?(cell_value)
    sorttype = sort_on_headers[cell_value]
    str << pivot_field_for(cell_value, subtotal, sorttype)
  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
      str << '<i/>'
    end
    str << '</rowItems>'
  end
  if columns.empty?
    if data.size > 1
      str << '<colFields count="1"><field x="-2"/></colFields>'
      str << "<colItems count=\"#{data.size}\">"
      str << '<i><x/></i>'
      (data.size - 1).times do |i|
        str << "<i i=\"#{i + 1}\"><x v=\"#{i + 1}\"/></i>"
      end
      str << '</colItems>'
    else
      str << '<colItems count="1"><i/></colItems>'
    end
  elsif data.size > 1
    str << '<colFields count="' << (columns.size + 1).to_s << '">'
    columns.each do |column_value|
      str << '<field x="' << header_index_of(column_value).to_s << '"/>'
    end
    str << '<field x="-2"/></colFields>'
    str << "<colItems count=\"#{data.size}\">"
    str << '<i><x/></i>'
    (data.size - 1).times do |i|
      str << "<i i=\"#{i + 1}\"><x v=\"#{i + 1}\"/></i>"
    end
    str << '</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|
      subtotal_name = datum_value[:subtotal] || 'sum'
      subtotal_name = 'count' if datum_value[:subtotal] == 'countNums' # both count & countNums are labelled as count
      field_name = datum_value[:name] || "#{subtotal_name.capitalize} of #{datum_value[:ref]}"
      str << "<dataField name='#{field_name}' fld='#{header_index_of(datum_value[:ref])}' baseField='0' baseItem='0'"
      str << " numFmtId='#{datum_value[:num_fmt]}'" if datum_value[:num_fmt]
      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