class Axlsx::Workbook

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

Attributes

charts[R]

A colllection of charts associated with this workbook @note The recommended way to manage charts is Worksheet#add_chart @see Worksheet#add_chart @see Chart @return [SimpleTypedList]

drawings[R]

A colllection of drawings associated with this workbook @note The recommended way to manage drawings is Worksheet#add_chart @see Worksheet#add_chart @see Drawing @return [SimpleTypedList]

images[R]

A colllection of images associated with this workbook @note The recommended way to manage images is Worksheet#add_image @see Worksheet#add_image @see Pic @return [SimpleTypedList]

pivot_tables[R]

A colllection of pivot tables associated with this workbook @note The recommended way to manage drawings is Worksheet#add_table @see Worksheet#add_table @see Table @return [SimpleTypedList]

tables[R]

A colllection of tables associated with this workbook @note The recommended way to manage drawings is Worksheet#add_table @see Worksheet#add_table @see Table @return [SimpleTypedList]

use_shared_strings[R]

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. @return [Boolean]

worksheets[R]

A collection of worksheets associated with this workbook. @note The recommended way to manage worksheets is add_worksheet @see Workbook#add_worksheet @see Worksheet @return [SimpleTypedList]

Public Class Methods

date1904() click to toggle source

retrieves the date1904 attribute @return [Boolean]

# File lib/axlsx/workbook/workbook.rb, line 232
def self.date1904() @@date1904; end
date1904=(v) click to toggle source

Sets the date1904 attribute to the provided boolean @return [Boolean]

# File lib/axlsx/workbook/workbook.rb, line 228
def self.date1904=(v) Axlsx::validate_boolean v; @@date1904 = v; end
new(options={}) { |self| ... } click to toggle source

Creates a new Workbook The recomended way to work with workbooks is via Package#workbook @option options [Boolean] date1904. If this is not specified, date1904 is set to false. Office 2011 for Mac defaults to false.

# File lib/axlsx/workbook/workbook.rb, line 201
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

Public Instance Methods

[](cell_def) click to toggle source

returns a range of cells in a worksheet @param [String] cell_def The excel style reference defining the worksheet and cells. The range must specify the sheet to 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. @return [Cell, Array]

# File lib/axlsx/workbook/workbook.rb, line 329
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) click to toggle source

Adds a defined name to this workbook @return [DefinedName] @param [String] formula @see DefinedName @param [Hash] options @see DefinedName

# File lib/axlsx/workbook/workbook.rb, line 280
def add_defined_name(formula, options)
  defined_names << DefinedName.new(formula, options)
end
add_view(options={}) click to toggle source
# File lib/axlsx/workbook/workbook.rb, line 272
def add_view(options={})
  views << WorkbookView.new(options)
end
add_worksheet(options={}) { |worksheet| ... } click to toggle source

Adds a worksheet to this workbook @return [Worksheet] @option options [String] name The name of the worksheet. @option options [Hash] page_margins The page margins for the worksheet. @see Worksheet#initialize

# File lib/axlsx/workbook/workbook.rb, line 266
def add_worksheet(options={})
  worksheet = Worksheet.new(self, options)
  yield worksheet if block_given?
  worksheet
end
comments() click to toggle source

A collection of comments associated with this workbook @note The recommended way to manage comments is WOrksheet#add_comment @see Worksheet#add_comment @see Comment @return [Comments]

# File lib/axlsx/workbook/workbook.rb, line 162
def comments
  worksheets.map { |sheet| sheet.comments }.compact
end
date1904() click to toggle source

Instance level access to the class variable 1904 @return [Boolean]

# File lib/axlsx/workbook/workbook.rb, line 221
def date1904() @@date1904; end
date1904=(v) click to toggle source

see @date1904

# File lib/axlsx/workbook/workbook.rb, line 224
def date1904=(v) Axlsx::validate_boolean v; @@date1904 = v; end
defined_names() click to toggle source

A collection of defined names for this workbook @note The recommended way to manage defined names is Workbook#add_defined_name @see DefinedName @return [DefinedNames]

# File lib/axlsx/workbook/workbook.rb, line 153
def defined_names
  @defined_names ||= DefinedNames.new
end
insert_worksheet(index=0, options={}) { |worksheet| ... } click to toggle source

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 @return [Worksheet] @param index The zero based position to insert the newly created worksheet @param [Hash] options Options to pass into the worksheed during initialization. @option options [String] name The name of the worksheet @option options [Hash] page_margins The page margins for the worksheet

# File lib/axlsx/workbook/workbook.rb, line 252
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() click to toggle source

The workbook relationships. This is managed automatically by the workbook @return [Relationships]

# File lib/axlsx/workbook/workbook.rb, line 286
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() click to toggle source

generates a shared string object against all cells in all worksheets. @return [SharedStringTable]

# File lib/axlsx/workbook/workbook.rb, line 303
def shared_strings
  SharedStringsTable.new(worksheets.collect { |ws| ws.cells }, xml_space)
end
sheet_by_name(name) click to toggle source

A quick helper to retrive a worksheet by name @param [String] name The name of the sheet you are looking for @return [Worksheet] The sheet found, or nil

# File lib/axlsx/workbook/workbook.rb, line 184
def sheet_by_name(name)
  index = @worksheets.index { |sheet| sheet.name == name }
  @worksheets[index] if index
end
styles() { |styles| ... } click to toggle source

The styles associated with this workbook @note The recommended way to manage styles is Styles#add_style @see Style#add_style @see Style @return [Styles]

# File lib/axlsx/workbook/workbook.rb, line 171
def styles
  yield @styles if block_given?
  @styles
end
to_xml_string(str='') click to toggle source

Serialize the workbook @param [String] str @return [String]

# File lib/axlsx/workbook/workbook.rb, line 339
def to_xml_string(str='')
  add_worksheet(name: 'Sheet1') 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 << '"/>')
  views.to_xml_string(str)
  str << '<sheets>'
  worksheets.each { |sheet| sheet.to_sheet_node_xml_string(str) }
  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() click to toggle source

Indicates if the workbook should use autowidths or not. @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.

@return [Boolean]

# File lib/axlsx/workbook/workbook.rb, line 239
def use_autowidth() @use_autowidth; end
use_autowidth=(v=true) click to toggle source

see @use_autowidth

# File lib/axlsx/workbook/workbook.rb, line 242
def use_autowidth=(v=true) Axlsx::validate_boolean v; @use_autowidth = v; end
use_shared_strings=(v) click to toggle source

@see use_shared_strings

# File lib/axlsx/workbook/workbook.rb, line 93
def use_shared_strings=(v)
  Axlsx::validate_boolean(v)
  @use_shared_strings = v
end
views() click to toggle source

A collection of views for this workbook

# File lib/axlsx/workbook/workbook.rb, line 145
def views
  @views ||= WorkbookViews.new
end
xml_space() click to toggle source

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. @return Symbol

# File lib/axlsx/workbook/workbook.rb, line 313
def xml_space
  @xml_space ||= :preserve
end
xml_space=(space) click to toggle source

Sets the xml:space attribute for the worksheet @see Worksheet#xml_space @param [Symbol] space must be one of :preserve or :default

# File lib/axlsx/workbook/workbook.rb, line 320
def xml_space=(space)
  Axlsx::RestrictionValidator.validate(:xml_space, [:preserve, :default], space)
  @xml_space = space;
end