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
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]
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]
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]
If true reverse the order in which the workbook is serialized @return [Boolean]
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]
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]
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
retrieves the date1904 attribute @return [Boolean]
# File lib/axlsx/workbook/workbook.rb, line 233 def self.date1904() @@date1904; end
Sets the date1904 attribute to the provided boolean @return [Boolean]
# File lib/axlsx/workbook/workbook.rb, line 229 def self.date1904=(v) Axlsx::validate_boolean v; @@date1904 = v; end
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 202 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
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 334 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
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 285 def add_defined_name(formula, options) defined_names << DefinedName.new(formula, options) end
Adds a new WorkbookView
@return WorkbookViews
@option options [Hash] options passed into the added WorkbookView
@see WorkbookView#initialize
# File lib/axlsx/workbook/workbook.rb, line 277 def add_view(options={}) views << WorkbookView.new(options) end
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 267 def add_worksheet(options={}) worksheet = Worksheet.new(self, options) yield worksheet if block_given? worksheet end
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 172 def comments worksheets.map { |sheet| sheet.comments }.compact end
Instance level access to the class variable 1904 @return [Boolean]
# File lib/axlsx/workbook/workbook.rb, line 222 def date1904() @@date1904; end
see @date1904
# File lib/axlsx/workbook/workbook.rb, line 225 def date1904=(v) Axlsx::validate_boolean v; @@date1904 = v; end
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 163 def defined_names @defined_names ||= DefinedNames.new end
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 253 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
# File lib/axlsx/workbook/workbook.rb, line 103 def is_reversed=(v) Axlsx::validate_boolean(v) @is_reversed = v end
The workbook relationships. This is managed automatically by the workbook @return [Relationships]
# File lib/axlsx/workbook/workbook.rb, line 291 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
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 194 def sheet_by_name(name) index = @worksheets.index { |sheet| sheet.name == name } @worksheets[index] if index end
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 181 def styles yield @styles if block_given? @styles end
Serialize the workbook @param [String] str @return [String]
# File lib/axlsx/workbook/workbook.rb, line 344 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>' 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
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 240 def use_autowidth() @use_autowidth; end
see @use_autowidth
# File lib/axlsx/workbook/workbook.rb, line 243 def use_autowidth=(v=true) Axlsx::validate_boolean v; @use_autowidth = v; end
A collection of views for this workbook
# File lib/axlsx/workbook/workbook.rb, line 155 def views @views ||= WorkbookViews.new end
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 318 def xml_space @xml_space ||= :preserve end
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 325 def xml_space=(space) Axlsx::RestrictionValidator.validate(:xml_space, [:preserve, :default], space) @xml_space = space; end