Class: Axlsx::Workbook
- Inherits:
-
Object
- Object
- Axlsx::Workbook
- 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
-
#bold_font_multiplier ⇒ Float
Font size of bold fonts is multiplied with this Used for automatic calculation of cell widths with bold text.
-
#charts ⇒ SimpleTypedList
readonly
A collection of charts associated with this workbook.
-
#drawings ⇒ SimpleTypedList
readonly
A collection of drawings associated with this workbook.
-
#escape_formulas ⇒ Boolean
Whether to treat values starting with an equals sign as formulas or as literal strings.
-
#font_scale_divisor ⇒ Float
Font scale is calculated with this value (font_size / font_scale_divisor) Used for automatic calculation of cell widths.
-
#images ⇒ SimpleTypedList
readonly
A collection of images associated with this workbook.
-
#is_reversed ⇒ Boolean
If true reverse the order in which the workbook is serialized.
-
#pivot_tables ⇒ SimpleTypedList
readonly
A collection of pivot tables associated with this workbook.
-
#styles_applied ⇒ Object
Are the styles added with workbook.add_styles applied yet.
-
#tables ⇒ SimpleTypedList
readonly
A collection of tables associated with this workbook.
-
#use_autowidth ⇒ Boolean
Indicates if the workbook should use autowidths or not.
-
#use_shared_strings ⇒ Boolean
When true, the Package will be generated with a shared string table.
-
#worksheets ⇒ SimpleTypedList
readonly
A collection of worksheets associated with this workbook.
Class Method Summary collapse
-
.date1904 ⇒ Boolean
retrieves the date1904 attribute.
-
.date1904=(v) ⇒ Boolean
Sets the date1904 attribute to the provided boolean.
Instance Method Summary collapse
-
#[](cell_def) ⇒ Cell, Array
returns a range of cells in a worksheet retrieve the cells from.
-
#add_defined_name(formula, options) ⇒ DefinedName
Adds a defined name to this workbook.
-
#add_view(options = {}) ⇒ Object
Adds a new WorkbookView.
-
#add_worksheet(options = {}) {|worksheet| ... } ⇒ Worksheet
Adds a worksheet to this workbook.
-
#apply_styles ⇒ Boolean
A helper to apply styles that were added using
worksheet.add_style
. -
#comments ⇒ Comments
A collection of comments associated with this workbook.
-
#date1904 ⇒ Boolean
Instance level access to the class variable 1904.
-
#date1904=(v) ⇒ Object
see @date1904.
-
#defined_names ⇒ DefinedNames
A collection of defined names for this workbook.
-
#initialize(options = {}) {|_self| ... } ⇒ Workbook
constructor
Creates a new Workbook.
-
#insert_worksheet(index = 0, options = {}) {|worksheet| ... } ⇒ Worksheet
inserts a worksheet into this workbook at the position specified.
-
#relationships ⇒ Relationships
The workbook relationships.
-
#shared_strings ⇒ SharedStringTable
generates a shared string object against all cells in all worksheets.
-
#sheet_by_name(name) ⇒ Worksheet
A quick helper to retrieve a worksheet by name.
-
#styled_cells ⇒ Object
An array that holds all cells with styles.
-
#styles {|@styles| ... } ⇒ Styles
The styles associated with this workbook.
-
#to_xml_string(str = +'')) ⇒ String
Serialize the workbook.
-
#views ⇒ Object
A collection of views for this workbook.
-
#xml_space ⇒ Object
The xml:space attribute for the worksheet.
-
#xml_space=(space) ⇒ Object
Sets the xml:space attribute for the worksheet.
Constructor Details
#initialize(options = {}) {|_self| ... } ⇒ Workbook
Creates a new Workbook. The recommended way to work with workbooks is via Package#workbook.
231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 |
# File 'lib/axlsx/workbook/workbook.rb', line 231 def initialize( = {}) @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 = [:escape_formulas].nil? ? Axlsx.escape_formulas : [:escape_formulas] self.date1904 = ![:date1904].nil? && [: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
304 305 306 |
# File 'lib/axlsx/workbook/workbook.rb', line 304 def bold_font_multiplier @bold_font_multiplier end |
#charts ⇒ SimpleTypedList (readonly)
The recommended way to manage charts is Worksheet#add_chart
A collection of charts associated with this workbook
123 124 125 |
# File 'lib/axlsx/workbook/workbook.rb', line 123 def charts @charts end |
#drawings ⇒ SimpleTypedList (readonly)
The recommended way to manage drawings is Worksheet#add_chart
A collection of drawings associated with this workbook
137 138 139 |
# File 'lib/axlsx/workbook/workbook.rb', line 137 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.
279 280 281 |
# File 'lib/axlsx/workbook/workbook.rb', line 279 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
314 315 316 |
# File 'lib/axlsx/workbook/workbook.rb', line 314 def font_scale_divisor @font_scale_divisor end |
#images ⇒ SimpleTypedList (readonly)
The recommended way to manage images is Worksheet#add_image
A collection of images associated with this workbook
130 131 132 |
# File 'lib/axlsx/workbook/workbook.rb', line 130 def images @images end |
#is_reversed ⇒ Boolean
If true reverse the order in which the workbook is serialized
104 105 106 |
# File 'lib/axlsx/workbook/workbook.rb', line 104 def is_reversed @is_reversed end |
#pivot_tables ⇒ SimpleTypedList (readonly)
The recommended way to manage drawings is Worksheet#add_table
A collection of pivot tables associated with this workbook
153 154 155 |
# File 'lib/axlsx/workbook/workbook.rb', line 153 def pivot_tables @pivot_tables end |
#styles_applied ⇒ Object
Are the styles added with workbook.add_styles applied yet
195 196 197 |
# File 'lib/axlsx/workbook/workbook.rb', line 195 def styles_applied @styles_applied end |
#tables ⇒ SimpleTypedList (readonly)
The recommended way to manage drawings is Worksheet#add_table
A collection of tables associated with this workbook
146 147 148 |
# File 'lib/axlsx/workbook/workbook.rb', line 146 def tables @tables end |
#use_autowidth ⇒ Boolean
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.
293 294 295 |
# File 'lib/axlsx/workbook/workbook.rb', line 293 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.
94 95 96 |
# File 'lib/axlsx/workbook/workbook.rb', line 94 def use_shared_strings @use_shared_strings end |
#worksheets ⇒ SimpleTypedList (readonly)
The recommended way to manage worksheets is add_worksheet
A collection of worksheets associated with this workbook.
116 117 118 |
# File 'lib/axlsx/workbook/workbook.rb', line 116 def worksheets @worksheets end |
Class Method Details
.date1904 ⇒ Boolean
retrieves the date1904 attribute
271 272 273 |
# File 'lib/axlsx/workbook/workbook.rb', line 271 def self.date1904 @@date1904 end |
.date1904=(v) ⇒ Boolean
Sets the date1904 attribute to the provided boolean
264 265 266 267 |
# File 'lib/axlsx/workbook/workbook.rb', line 264 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.
410 411 412 413 414 415 416 |
# File 'lib/axlsx/workbook/workbook.rb', line 410 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
361 362 363 |
# File 'lib/axlsx/workbook/workbook.rb', line 361 def add_defined_name(formula, ) defined_names << DefinedName.new(formula, ) end |
#add_view(options = {}) ⇒ Object
Adds a new WorkbookView
353 354 355 |
# File 'lib/axlsx/workbook/workbook.rb', line 353 def add_view( = {}) views << WorkbookView.new() end |
#add_worksheet(options = {}) {|worksheet| ... } ⇒ Worksheet
Adds a worksheet to this workbook
343 344 345 346 347 |
# File 'lib/axlsx/workbook/workbook.rb', line 343 def add_worksheet( = {}) worksheet = Worksheet.new(self, ) yield worksheet if block_given? worksheet end |
#apply_styles ⇒ Boolean
A helper to apply styles that were added using worksheet.add_style
199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 |
# File 'lib/axlsx/workbook/workbook.rb', line 199 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
The recommended way to manage comments is WOrksheet#add_comment
A collection of comments associated with this workbook
173 174 175 |
# File 'lib/axlsx/workbook/workbook.rb', line 173 def comments worksheets.map(&:comments).compact end |
#date1904 ⇒ Boolean
Instance level access to the class variable 1904
252 253 254 |
# File 'lib/axlsx/workbook/workbook.rb', line 252 def date1904 @@date1904 end |
#date1904=(v) ⇒ Object
see @date1904
257 258 259 260 |
# File 'lib/axlsx/workbook/workbook.rb', line 257 def date1904=(v) Axlsx.validate_boolean v @@date1904 = v end |
#defined_names ⇒ DefinedNames
The recommended way to manage defined names is Workbook#add_defined_name
A collection of defined names for this workbook
164 165 166 |
# File 'lib/axlsx/workbook/workbook.rb', line 164 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
329 330 331 332 333 334 335 |
# File 'lib/axlsx/workbook/workbook.rb', line 329 def insert_worksheet(index = 0, = {}) worksheet = Worksheet.new(self, ) @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
367 368 369 370 371 372 373 374 375 376 377 378 379 380 |
# File 'lib/axlsx/workbook/workbook.rb', line 367 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) 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.
384 385 386 |
# File 'lib/axlsx/workbook/workbook.rb', line 384 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
223 224 225 226 |
# File 'lib/axlsx/workbook/workbook.rb', line 223 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
189 190 191 |
# File 'lib/axlsx/workbook/workbook.rb', line 189 def styled_cells @styled_cells ||= Set.new end |
#styles {|@styles| ... } ⇒ Styles
The recommended way to manage styles is Styles#add_style
The styles associated with this workbook
182 183 184 185 |
# File 'lib/axlsx/workbook/workbook.rb', line 182 def styles yield @styles if block_given? @styles end |
#to_xml_string(str = +'')) ⇒ String
Serialize the workbook
421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 |
# File 'lib/axlsx/workbook/workbook.rb', line 421 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
156 157 158 |
# File 'lib/axlsx/workbook/workbook.rb', line 156 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.
394 395 396 |
# File 'lib/axlsx/workbook/workbook.rb', line 394 def xml_space @xml_space ||= :preserve end |
#xml_space=(space) ⇒ Object
Sets the xml:space attribute for the worksheet
401 402 403 404 |
# File 'lib/axlsx/workbook/workbook.rb', line 401 def xml_space=(space) Axlsx::RestrictionValidator.validate(:xml_space, [:preserve, :default], space) @xml_space = space end |