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

@@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 recomended 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:



195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
 
# File 'lib/axlsx/workbook/workbook.rb', line 195

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

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

Instance Attribute Details

#charts ⇒ SimpleTypedList (readonly)

Note:

The recommended way to manage charts is Worksheet#add_chart

A colllection of charts associated with this workbook

Returns:

  • (SimpleTypedList)

See Also:



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

def charts
  @charts
end

#drawings ⇒ SimpleTypedList (readonly)

Note:

The recommended way to manage drawings is Worksheet#add_chart

A colllection of drawings associated with this workbook

Returns:

  • (SimpleTypedList)

See Also:



123
124
125
 
# File 'lib/axlsx/workbook/workbook.rb', line 123

def drawings
  @drawings
end

#images ⇒ SimpleTypedList (readonly)

Note:

The recommended way to manage images is Worksheet#add_image

A colllection of images associated with this workbook

Returns:

  • (SimpleTypedList)

See Also:



116
117
118
 
# File 'lib/axlsx/workbook/workbook.rb', line 116

def images
  @images
end

#pivot_tables ⇒ SimpleTypedList (readonly)

Note:

The recommended way to manage drawings is Worksheet#add_table

A colllection of pivot tables associated with this workbook

Returns:

  • (SimpleTypedList)

See Also:



140
141
142
 
# File 'lib/axlsx/workbook/workbook.rb', line 140

def pivot_tables
  @pivot_tables
end

#tables ⇒ SimpleTypedList (readonly)

Note:

The recommended way to manage drawings is Worksheet#add_table

A colllection of tables associated with this workbook

Returns:

  • (SimpleTypedList)

See Also:



133
134
135
 
# File 'lib/axlsx/workbook/workbook.rb', line 133

def tables
  @tables
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) 


88
89
90
 
# File 'lib/axlsx/workbook/workbook.rb', line 88

def use_shared_strings
  @use_shared_strings
end

#worksheets ⇒ SimpleTypedList (readonly)

Note:

The recommended way to manage worksheets is add_worksheet

Returns:

  • (SimpleTypedList)

See Also:



102
103
104
 
# File 'lib/axlsx/workbook/workbook.rb', line 102

def worksheets
  @worksheets
end

Class Method Details

.date1904 ⇒ Boolean

retrieves the date1904 attribute

Returns:

  • (Boolean) 


226
 
# File 'lib/axlsx/workbook/workbook.rb', line 226

def self.date1904() @@date1904; end

.date1904=(v) ⇒ Boolean

Sets the date1904 attribute to the provided boolean

Returns:

  • (Boolean) 


222
 
# File 'lib/axlsx/workbook/workbook.rb', line 222

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) 


319
320
321
322
323
324
 
# File 'lib/axlsx/workbook/workbook.rb', line 319

def [](cell_def)
  sheet_name = cell_def.split('!')[0] if cell_def.match('!')
  worksheet =  self.worksheets.select { |s| s.name == sheet_name }.first
  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:



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

def add_defined_name(formula, options)
  defined_names << DefinedName.new(formula, 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:



260
261
262
263
264
 
# File 'lib/axlsx/workbook/workbook.rb', line 260

def add_worksheet(options={})
  worksheet = Worksheet.new(self, options)
  yield worksheet if block_given?
  worksheet
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:



156
157
158
 
# File 'lib/axlsx/workbook/workbook.rb', line 156

def comments
  worksheets.map { |sheet| sheet.comments }.compact
end

#date1904 ⇒ Boolean

Instance level access to the class variable 1904

Returns:

  • (Boolean) 


215
 
# File 'lib/axlsx/workbook/workbook.rb', line 215

def date1904() @@date1904; end

#date1904=(v) ⇒ Object

see @date1904



218
 
# File 'lib/axlsx/workbook/workbook.rb', line 218

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:



147
148
149
 
# File 'lib/axlsx/workbook/workbook.rb', line 147

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:



246
247
248
249
250
251
252
 
# File 'lib/axlsx/workbook/workbook.rb', line 246

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:



276
277
278
279
280
281
282
283
284
285
286
287
288
289
 
# File 'lib/axlsx/workbook/workbook.rb', line 276

def relationships
  r = Relationships.new
  @worksheets.each do |sheet|
    r << Relationship.new(sheet, WORKSHEET_R, 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, PIVOT_TABLE_CACHE_DEFINITION_PN % (index+1))
  end
  r << Relationship.new(self, STYLES_R,  STYLES_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) 


293
294
295
 
# File 'lib/axlsx/workbook/workbook.rb', line 293

def shared_strings
  SharedStringsTable.new(worksheets.collect { |ws| ws.cells }, xml_space)
end

#sheet_by_name(name) ⇒ Worksheet

A quick helper to retrive a worksheet by name

Parameters:

  • name (String) —

    The name of the sheet you are looking for

Returns:



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

def sheet_by_name(name)
  index = @worksheets.index { |sheet| sheet.name == name }
  @worksheets[index] if index
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


165
166
167
168
 
# File 'lib/axlsx/workbook/workbook.rb', line 165

def styles
  yield @styles if block_given?
  @styles
end

#to_xml_string(str = '') ⇒ String

Serialize the workbook

Parameters:

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

Returns:

  • (String) 


329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
 
# File 'lib/axlsx/workbook/workbook.rb', line 329

def to_xml_string(str='')
  add_worksheet unless worksheets.size > 0
  str << '<?xml version="1.0" encoding="UTF-8"?>'
  str << '<workbook xmlns="' << XML_NS << '" xmlns:r="' << XML_NS_R << '">'
  str << '<workbookPr date1904="' << @@date1904.to_s << '"/>'
  str << '<sheets>'
  @worksheets.each_with_index do |sheet, index|
    str << '<sheet name="' << sheet.name << '" sheetId="' << (index+1).to_s << '" r:id="' << sheet.rId << '"/>'
    if defined_name = sheet.auto_filter.defined_name
      add_defined_name defined_name, :name => '_xlnm._FilterDatabase', :local_sheet_id => index, :hidden => 1
    end
  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

#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) 


233
 
# File 'lib/axlsx/workbook/workbook.rb', line 233

def use_autowidth() @use_autowidth; end

#use_autowidth=(v = true) ⇒ Object

see @use_autowidth



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

def use_autowidth=(v=true) Axlsx::validate_boolean v; @use_autowidth = v; end

#xml_space ⇒ Object

The xml:space attribute for the worksheet. This determines how whitespace is handled withing 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



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

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


310
311
312
313
 
# File 'lib/axlsx/workbook/workbook.rb', line 310

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