Class: Axlsx::Workbook

Inherits:
Object
  • Object
show all
Defined in:
lib/axlsx/workbook/workbook.rb

Overview

The Workbook class is an xlsx workbook that manages worksheets, charts, drawings and styles. The following parts of the Office Open XML spreadsheet specification are not implemented in this version.

bookViews calcPr customWorkbookViews definedNames externalReferences extLst fileRecoveryPr fileSharing fileVersion functionGroups oleSize pivotCaches smartTagPr smartTagTypes webPublishing webPublishObjects workbookProtection workbookPr*

*workbookPr is only supported to the extend of date1904

Constant Summary collapse

BOLD_FONT_MULTIPLIER = 
1.5
FONT_SCALE_DIVISOR = 
10.0
@@date1904 =

Indicates if the epoc date for serialization should be 1904. If false, 1900 is used.

false

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(options = {}) {|_self| ... } ⇒ Workbook

Creates a new Workbook. The recommended way to work with workbooks is via Package#workbook.

Parameters:

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

    a customizable set of options

Options Hash (options):

  • date1904 (Boolean) —

    If this is not specified, date1904 is set to false. Office 2011 for Mac defaults to false.

Yields:

  • (_self)

Yield Parameters:



242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
 
# File 'lib/axlsx/workbook/workbook.rb', line 242

def initialize(options = {})
  @styles = Styles.new
  @worksheets = SimpleTypedList.new Worksheet
  @drawings = SimpleTypedList.new Drawing
  @charts = SimpleTypedList.new Chart
  @images = SimpleTypedList.new Pic
  # Are these even used????? Check package serialization parts
  @tables = SimpleTypedList.new Table
  @pivot_tables = SimpleTypedList.new PivotTable
  @comments = SimpleTypedList.new Comments
  @use_autowidth = true
  @bold_font_multiplier = BOLD_FONT_MULTIPLIER
  @font_scale_divisor = FONT_SCALE_DIVISOR

  self.escape_formulas = options[:escape_formulas].nil? ? Axlsx.escape_formulas : options[:escape_formulas]
  self.date1904 = !options[:date1904].nil? && options[:date1904]
  yield self if block_given?
end

Instance Attribute Details

#bold_font_multiplier ⇒ Float

Font size of bold fonts is multiplied with this Used for automatic calculation of cell widths with bold text

Returns:

  • (Float) 


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

def bold_font_multiplier
  @bold_font_multiplier
end

#charts ⇒ SimpleTypedList (readonly)

Note:

The recommended way to manage charts is Worksheet#add_chart

A collection of charts associated with this workbook

Returns:

  • (SimpleTypedList)

See Also:



128
129
130
 
# File 'lib/axlsx/workbook/workbook.rb', line 128

def charts
  @charts
end

#drawings ⇒ SimpleTypedList (readonly)

Note:

The recommended way to manage drawings is Worksheet#add_chart

A collection of drawings associated with this workbook

Returns:

  • (SimpleTypedList)

See Also:



142
143
144
 
# File 'lib/axlsx/workbook/workbook.rb', line 142

def drawings
  @drawings
end

#escape_formulas ⇒ Boolean

Whether to treat values starting with an equals sign as formulas or as literal strings. Allowing user-generated data to be interpreted as formulas is a security risk. See https://www.owasp.org/index.php/CSV_Injection for details.

Returns:

  • (Boolean) 


290
291
292
 
# File 'lib/axlsx/workbook/workbook.rb', line 290

def escape_formulas
  @escape_formulas
end

#font_scale_divisor ⇒ Float

Font scale is calculated with this value (font_size / font_scale_divisor) Used for automatic calculation of cell widths

Returns:

  • (Float) 


325
326
327
 
# File 'lib/axlsx/workbook/workbook.rb', line 325

def font_scale_divisor
  @font_scale_divisor
end

#images ⇒ SimpleTypedList (readonly)

Note:

The recommended way to manage images is Worksheet#add_image

A collection of images associated with this workbook

Returns:

  • (SimpleTypedList)

See Also:



135
136
137
 
# File 'lib/axlsx/workbook/workbook.rb', line 135

def images
  @images
end

#is_reversed ⇒ Boolean

If true reverse the order in which the workbook is serialized

Returns:

  • (Boolean) 


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

def is_reversed
  @is_reversed
end

#pivot_tables ⇒ SimpleTypedList (readonly)

Note:

The recommended way to manage drawings is Worksheet#add_table

A collection of pivot tables associated with this workbook

Returns:

  • (SimpleTypedList)

See Also:



158
159
160
 
# File 'lib/axlsx/workbook/workbook.rb', line 158

def pivot_tables
  @pivot_tables
end

#styles_applied ⇒ Object

Are the styles added with workbook.add_styles applied yet

Returns:

  • Boolean



206
207
208
 
# File 'lib/axlsx/workbook/workbook.rb', line 206

def styles_applied
  @styles_applied
end

#tables ⇒ SimpleTypedList (readonly)

Note:

The recommended way to manage drawings is Worksheet#add_table

A collection of tables associated with this workbook

Returns:

  • (SimpleTypedList)

See Also:



151
152
153
 
# File 'lib/axlsx/workbook/workbook.rb', line 151

def tables
  @tables
end

#use_autowidth ⇒ Boolean

Note:

This gem no longer depends on RMagick for autowidth calculation. Thus the performance benefits of turning this off are marginal unless you are creating a very large sheet.

Indicates if the workbook should use autowidths or not.

Returns:

  • (Boolean) 


304
305
306
 
# File 'lib/axlsx/workbook/workbook.rb', line 304

def use_autowidth
  @use_autowidth
end

#use_shared_strings ⇒ Boolean

When true, the Package will be generated with a shared string table. This may be required by some OOXML processors that do not adhere to the ECMA specification that dictates string may be inline in the sheet. Using this option will increase the time required to serialize the document as every string in every cell must be analzed and referenced.

Returns:

  • (Boolean) 


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

def use_shared_strings
  @use_shared_strings
end

#worksheets ⇒ SimpleTypedList (readonly)

Note:

The recommended way to manage worksheets is add_worksheet

A collection of worksheets associated with this workbook.

Returns:

  • (SimpleTypedList)

See Also:



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

def worksheets
  @worksheets
end

Class Method Details

.date1904 ⇒ Boolean

retrieves the date1904 attribute

Returns:

  • (Boolean) 


282
283
284
 
# File 'lib/axlsx/workbook/workbook.rb', line 282

def self.date1904
  @@date1904
end

.date1904=(v) ⇒ Boolean

Sets the date1904 attribute to the provided boolean

Returns:

  • (Boolean) 


275
276
277
278
 
# File 'lib/axlsx/workbook/workbook.rb', line 275

def self.date1904=(v)
  Axlsx.validate_boolean v
  @@date1904 = v
end

Instance Method Details

#[](cell_def) ⇒ Cell, Array

returns a range of cells in a worksheet retrieve the cells from. e.g. range(‘Sheet1!A1:B2’) will return an array of four cells [A1, A2, B1, B2] while range(‘Sheet1!A1’) will return a single Cell.

Parameters:

  • cell_def (String) —

    The Excel style reference defining the worksheet and cells. The range must specify the sheet to

Returns:

Raises:

  • (ArgumentError) 


422
423
424
425
426
427
428
 
# File 'lib/axlsx/workbook/workbook.rb', line 422

def [](cell_def)
  sheet_name = cell_def.split('!')[0] if cell_def.include?('!')
  worksheet =  worksheets.find { |s| s.name == sheet_name }
  raise ArgumentError, 'Unknown Sheet' unless sheet_name && worksheet.is_a?(Worksheet)

  worksheet[cell_def.gsub(/.+!/, "")]
end

#add_defined_name(formula, options) ⇒ DefinedName

Adds a defined name to this workbook

Parameters:

  • formula (String) —

    @see DefinedName

  • options (Hash) —

    @see DefinedName

Returns:



372
373
374
 
# File 'lib/axlsx/workbook/workbook.rb', line 372

def add_defined_name(formula, options)
  defined_names << DefinedName.new(formula, options)
end

#add_view(options = {}) ⇒ Object

Adds a new WorkbookView

Parameters:

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

    a customizable set of options

Options Hash (options):

  • options (Hash) —

    passed into the added WorkbookView

Returns:

  • WorkbookViews

See Also:



364
365
366
 
# File 'lib/axlsx/workbook/workbook.rb', line 364

def add_view(options = {})
  views << WorkbookView.new(options)
end

#add_worksheet(options = {}) {|worksheet| ... } ⇒ Worksheet

Adds a worksheet to this workbook

Parameters:

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

    a customizable set of options

Options Hash (options):

  • name (String) —

    The name of the worksheet.

  • page_margins (Hash) —

    The page margins for the worksheet.

Yields:

  • (worksheet)

Returns:

See Also:



354
355
356
357
358
 
# File 'lib/axlsx/workbook/workbook.rb', line 354

def add_worksheet(options = {})
  worksheet = Worksheet.new(self, options)
  yield worksheet if block_given?
  worksheet
end

#apply_styles ⇒ Boolean

A helper to apply styles that were added using worksheet.add_style

Returns:

  • (Boolean) 


210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
 
# File 'lib/axlsx/workbook/workbook.rb', line 210

def apply_styles
  return false unless styled_cells

  styled_cells.each do |cell|
    current_style = styles.style_index[cell.style]

    new_style = if current_style
                  Axlsx.hash_deep_merge(current_style, cell.raw_style)
                else
                  cell.raw_style
                end

    cell.style = styles.add_style(new_style)
  end

  self.styles_applied = true
end

#comments ⇒ Comments

Note:

The recommended way to manage comments is WOrksheet#add_comment

A collection of comments associated with this workbook

Returns:

See Also:



178
179
180
 
# File 'lib/axlsx/workbook/workbook.rb', line 178

def comments
  worksheets.map(&:comments).compact
end

#date1904 ⇒ Boolean

Instance level access to the class variable 1904

Returns:

  • (Boolean) 


263
264
265
 
# File 'lib/axlsx/workbook/workbook.rb', line 263

def date1904
  @@date1904
end

#date1904=(v) ⇒ Object

see @date1904



268
269
270
271
 
# File 'lib/axlsx/workbook/workbook.rb', line 268

def date1904=(v)
  Axlsx.validate_boolean v
  @@date1904 = v
end

#defined_names ⇒ DefinedNames

Note:

The recommended way to manage defined names is Workbook#add_defined_name

A collection of defined names for this workbook

Returns:

See Also:



169
170
171
 
# File 'lib/axlsx/workbook/workbook.rb', line 169

def defined_names
  @defined_names ||= DefinedNames.new
end

#insert_worksheet(index = 0, options = {}) {|worksheet| ... } ⇒ Worksheet

inserts a worksheet into this workbook at the position specified. It the index specified is out of range, the worksheet will be added to the end of the worksheets collection

Parameters:

  • index (defaults to: 0) —

    The zero based position to insert the newly created worksheet

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

    Options to pass into the worksheed during initialization.

Options Hash (options):

  • name (String) —

    The name of the worksheet

  • page_margins (Hash) —

    The page margins for the worksheet

Yields:

  • (worksheet)

Returns:



340
341
342
343
344
345
346
 
# File 'lib/axlsx/workbook/workbook.rb', line 340

def insert_worksheet(index = 0, options = {})
  worksheet = Worksheet.new(self, options)
  @worksheets.delete_at(@worksheets.size - 1)
  @worksheets.insert(index, worksheet)
  yield worksheet if block_given?
  worksheet
end

#relationships ⇒ Relationships

The workbook relationships. This is managed automatically by the workbook

Returns:



378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
 
# File 'lib/axlsx/workbook/workbook.rb', line 378

def relationships
  r = Relationships.new
  @worksheets.each do |sheet|
    r << Relationship.new(sheet, WORKSHEET_R, format(WORKSHEET_PN, r.size + 1))
  end
  pivot_tables.each_with_index do |pivot_table, index|
    r << Relationship.new(pivot_table.cache_definition, PIVOT_TABLE_CACHE_DEFINITION_R, format(PIVOT_TABLE_CACHE_DEFINITION_PN, index + 1))
  end
  r << Relationship.new(self, STYLES_R, STYLES_PN)
  r << Relationship.new(self, THEME_R, THEME_PN)
  if use_shared_strings
    r << Relationship.new(self, SHARED_STRINGS_R, SHARED_STRINGS_PN)
  end
  r
end

#shared_strings ⇒ SharedStringTable

generates a shared string object against all cells in all worksheets.

Returns:

  • (SharedStringTable) 


396
397
398
 
# File 'lib/axlsx/workbook/workbook.rb', line 396

def shared_strings
  SharedStringsTable.new(worksheets.collect(&:cells), xml_space)
end

#sheet_by_name(name) ⇒ Worksheet

A quick helper to retrieve a worksheet by name

Parameters:

  • name (String) —

    The name of the sheet you are looking for

Returns:



234
235
236
237
 
# File 'lib/axlsx/workbook/workbook.rb', line 234

def sheet_by_name(name)
  encoded_name = Axlsx.coder.encode(name)
  @worksheets.find { |sheet| sheet.name == encoded_name }
end

#styled_cells ⇒ Object

An array that holds all cells with styles

Returns:

  • Set



200
201
202
 
# File 'lib/axlsx/workbook/workbook.rb', line 200

def styled_cells
  @styled_cells ||= Set.new
end

#styles {|@styles| ... } ⇒ Styles

Note:

The recommended way to manage styles is Styles#add_style

The styles associated with this workbook

Yields:

Returns:

See Also:

  • Style#add_style
  • Style


187
188
189
190
 
# File 'lib/axlsx/workbook/workbook.rb', line 187

def styles
  yield @styles if block_given?
  @styles
end

#theme ⇒ Theme

The theme associated with this workbook

Returns:



194
195
196
 
# File 'lib/axlsx/workbook/workbook.rb', line 194

def theme
  @theme ||= Theme.new
end

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

Serialize the workbook

Parameters:

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

Returns:

  • (String) 


433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
 
# File 'lib/axlsx/workbook/workbook.rb', line 433

def to_xml_string(str = +'')
  add_worksheet(name: 'Sheet1') if worksheets.empty?
  str << '<?xml version="1.0" encoding="UTF-8"?>'
  str << '<workbook xmlns="' << XML_NS << '" xmlns:r="' << XML_NS_R << '">'
  str << '<workbookPr date1904="' << @@date1904.to_s << '"/>'
  views.to_xml_string(str)
  str << '<sheets>'
  if is_reversed
    worksheets.reverse_each { |sheet| sheet.to_sheet_node_xml_string(str) }
  else
    worksheets.each { |sheet| sheet.to_sheet_node_xml_string(str) }
  end
  str << '</sheets>'
  defined_names.to_xml_string(str)
  unless pivot_tables.empty?
    str << '<pivotCaches>'
    pivot_tables.each do |pivot_table|
      str << '<pivotCache cacheId="' << pivot_table.cache_definition.cache_id.to_s << '" r:id="' << pivot_table.cache_definition.rId << '"/>'
    end
    str << '</pivotCaches>'
  end
  str << '</workbook>'
end

#views ⇒ Object

A collection of views for this workbook



161
162
163
 
# File 'lib/axlsx/workbook/workbook.rb', line 161

def views
  @views ||= WorkbookViews.new
end

#xml_space ⇒ Object

The xml:space attribute for the worksheet. This determines how whitespace is handled within the document. The most relevant part being whitespace in the cell text. allowed values are :preserve and :default. Axlsx uses :preserve unless you explicily set this to :default.

Returns:

  • Symbol



406
407
408
 
# File 'lib/axlsx/workbook/workbook.rb', line 406

def xml_space
  @xml_space ||= :preserve
end

#xml_space=(space) ⇒ Object

Sets the xml:space attribute for the worksheet

Parameters:

  • space (Symbol) —

    must be one of :preserve or :default

See Also:

  • Axlsx::Worksheet#xml_space


413
414
415
416
 
# File 'lib/axlsx/workbook/workbook.rb', line 413

def xml_space=(space)
  Axlsx::RestrictionValidator.validate(:xml_space, [:preserve, :default], space)
  @xml_space = space
end