Class: Axlsx::Worksheet

Inherits:
Object
  • Object
show all
Includes:
OptionsParser, SerializedAttributes
Defined in:
lib/axlsx/workbook/worksheet/worksheet.rb

Overview

The Worksheet class represents a worksheet in the workbook.

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from SerializedAttributes

included, #serialized_attributes, #serialized_element_attributes, #serialized_tag

Methods included from OptionsParser

#parse_options

Constructor Details

#initialize(wb, options = {}) {|_self| ... } ⇒ Worksheet

Note:

the recommended way to manage worksheets is Workbook#add_worksheet

Creates a new worksheet.

Parameters:

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

    a customizable set of options

Options Hash (options):

  • name (String) —

    The name of this worksheet.

  • page_margins (Hash) —

    A hash containing page margins for this worksheet. @see PageMargins

  • print_options (Hash) —

    A hash containing print options for this worksheet. @see PrintOptions

  • header_footer (Hash) —

    A hash containing header/footer options for this worksheet. @see HeaderFooter

  • show_gridlines (Boolean) —

    Whether gridlines should be shown for this sheet.

  • escape_formulas (Boolean) —

    Whether formulas should be escaped by default. Can be overridden at a row/cell level.

Yields:

  • (_self)

Yield Parameters:

See Also:



21
22
23
24
25
26
27
28
29
30
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 21

def initialize(wb, options = {})
  self.workbook = wb
  @sheet_protection = nil
  initialize_page_options(options)
  parse_options options
  self.escape_formulas = wb.escape_formulas unless defined? @escape_formulas
  @workbook.worksheets << self
  @sheet_id = index + 1
  yield self if block_given?
end

Instance Attribute Details

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


55
56
57
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 55

def escape_formulas
  @escape_formulas
end

#preserve_spaces ⇒ Object

Accessor for controlling whether leading and trailing spaces in cells are preserved or ignored. The default is to preserve spaces.



343
344
345
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 343

def preserve_spaces
  @preserve_spaces
end

#workbook ⇒ Workbook

The workbook that owns this worksheet

Returns:



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

def workbook
  @workbook
end

Instance Method Details

#[](cell_def) ⇒ Cell, Array

Returns the cell or cells defined using Excel style A1:B3 references.

Parameters:

  • cell_def (String|Integer) —

    the string defining the cell or range of cells, or the rownumber

Returns:



680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 680

def [](cell_def)
  return rows[cell_def] if cell_def.is_a?(Integer)

  parts = cell_def.split(':').map { |part| name_to_cell part }

  if parts.size == 1
    parts.first
  else
    if parts.size > 2
      raise ArgumentError, format(ERR_CELL_REFERENCE_INVALID, cell_def)
    elsif parts.first.nil?
      raise ArgumentError, format(ERR_CELL_REFERENCE_MISSING_CELL, cell_def.split(":").first, cell_def)
    elsif parts.last.nil?
      raise ArgumentError, format(ERR_CELL_REFERENCE_MISSING_CELL, cell_def.split(":").last, cell_def)
    end

    range(*parts)
  end
end

#add_border(cell_refs, options = nil) ⇒ Object

Set the style for cells in a specific column

Parameters:

  • cell_refs (String|Array) —

    Cell references

  • options (Hash|Array|Symbol) (defaults to: nil) —

    border options



620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 620

def add_border(cell_refs, options = nil)
  if options.is_a?(Hash)
    border_edges = options[:edges]
    border_style = options[:style]
    border_color = options[:color]
  else
    border_edges = options
  end

  unless cell_refs.is_a?(Array)
    cell_refs = [cell_refs]
  end

  cell_refs.each do |cell_ref|
    item = self[cell_ref]

    cells = item.is_a?(Array) ? item : [item]

    Axlsx::BorderCreator.new(worksheet: self, cells: cells, edges: border_edges, style: border_style, color: border_color).draw
  end
end

#add_chart(chart_type, options = {}) {|chart| ... } ⇒ Object

Note:

each chart type also specifies additional options

Adds a chart to this worksheets drawing. This is the recommended way to create charts for your worksheet. This method wraps the complexity of dealing with ooxml drawing, anchors, markers graphic frames chart objects and all the other dirty details.

Parameters:

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

    a customizable set of options

Options Hash (options):

  • start_at (Array)
  • end_at (Array)
  • title (Cell, String)
  • show_legend (Boolean)
  • style (Integer)

Yields:

  • (chart)

See Also:



489
490
491
492
493
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 489

def add_chart(chart_type, options = {})
  chart = worksheet_drawing.add_chart(chart_type, options)
  yield chart if block_given?
  chart
end

#add_comment(options = {}) ⇒ Object

Shortcut to worsksheet_comments#add_comment



509
510
511
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 509

def add_comment(options = {})
  worksheet_comments.add_comment(options)
end

#add_conditional_formatting(cells, rules) ⇒ Object

Add conditional formatting to this worksheet.

Examples:

This would format column A whenever it is FALSE.

# for a longer example, see examples/example_conditional_formatting.rb (link below)
worksheet.add_conditional_formatting( "A1:A1048576", { :type => :cellIs, :operator => :equal, :formula => "FALSE", :dxfId => 1, :priority => 1 }

Parameters:

  • cells (String) —

    The range to apply the formatting to

  • rules (Array|Hash) —

    An array of hashes (or just one) to create Conditional formatting rules from.

See Also:



450
451
452
453
454
455
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 450

def add_conditional_formatting(cells, rules)
  cf = ConditionalFormatting.new(sqref: cells)
  cf.add_rules rules
  conditional_formattings << cf
  conditional_formattings
end

#add_data_validation(cells, data_validation) ⇒ Object

Add data validation to this worksheet.

Parameters:

  • cells (String) —

    The cells the validation will apply to.

  • data_validation (hash) —

    options defining the validation to apply.

See Also:

  • for an example


462
463
464
465
466
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 462

def add_data_validation(cells, data_validation)
  dv = DataValidation.new(data_validation)
  dv.sqref = cells
  data_validations << dv
end

#add_hyperlink(options = {}) ⇒ WorksheetHyperlink

Adds a new hyperlink to the worksheet

Parameters:

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

    for the hyperlink

Returns:

See Also:



472
473
474
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 472

def add_hyperlink(options = {})
  hyperlinks.add(options)
end

#add_image(options = {}) {|image| ... } ⇒ Object

Adds a media item to the worksheets drawing

Parameters:

  • [Hash] (Hash) —

    a customizable set of options

Yields:

  • (image) 


515
516
517
518
519
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 515

def add_image(options = {})
  image = worksheet_drawing.add_image(options)
  yield image if block_given?
  image
end

#add_page_break(cell) ⇒ Object

Adds a page break (row break) to the worksheet should be added to the sheet.

Examples:

ws.add_page_break("A4")

Parameters:

  • cell —

    A Cell object or Excel style string reference indicating where the break



526
527
528
529
530
531
532
533
534
535
536
537
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 526

def add_page_break(cell)
  DataTypeValidator.validate :worksheet_page_break, [String, Cell], cell
  column_index, row_index = if cell.is_a?(String)
                              Axlsx.name_to_indices(cell)
                            else
                              cell.pos
                            end
  if column_index > 0
    col_breaks.add_break(id: column_index)
  end
  row_breaks.add_break(id: row_index)
end

#add_pivot_table(ref, range, options = {}) {|pivot_tables.last| ... } ⇒ Object

Yields:



502
503
504
505
506
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 502

def add_pivot_table(ref, range, options = {})
  pivot_tables << PivotTable.new(ref, range, self, options)
  yield pivot_tables.last if block_given?
  pivot_tables.last
end

#add_row(values = [], options = {}) {|row| ... } ⇒ Row Also known as: <<

Adds a row to the worksheet and updates auto fit data. (see https://www.owasp.org/index.php/CSV_Injection for details).

Examples:

  • put a vanilla row in your spreadsheet
ws.add_row [1, 'fish on my pl', '8']
  • specify a fixed width for a column in your spreadsheet
# The first column will ignore the content of this cell when calculating column autowidth.
# The second column will include this text in calculating the columns autowidth
# The third cell will set a fixed with of 80 for the column.
# If you need to un-fix a column width, use :auto. That will recalculate the column width based on all content in the column

ws.add_row ['I wish', 'for a fish', 'on my fish wish dish'], :widths=>[:ignore, :auto, 80]
  • specify a fixed height for a row
ws.add_row ['I wish', 'for a fish', 'on my fish wish dish'], :height => 40
  • create and use a style for all cells in the row
blue = ws.styles.add_style :color => "#00FF00"
ws.add_row [1, 2, 3], :style=>blue
  • only style some cells
blue = ws.styles.add_style :color => "#00FF00"
red = ws.styles.add_style :color => "#FF0000"
big = ws.styles.add_style :sz => 40
ws.add_row ["red fish", "blue fish", "one fish", "two fish"], :style=>[red, blue, nil, big] # the last nil is optional
  • force the second cell to be a float value
ws.add_row [3, 4, 5], :types => [nil, :float]
  • use << alias
ws << [3, 4, 5], :types => [nil, :float]
  • specify whether a row should escape formulas or not
ws.add_row ['=IF(2+2=4,4,5)', 2, 3], :escape_formulas=>true
  • specify whether a certain cells in a row should escape formulas or not
ws.add_row ['=IF(2+2=4,4,5)', '=IF(13+13=4,4,5)'], :escape_formulas=>[true, false]
  • add a column offset when adding a row (inserts 'n' blank, unstyled columns before data)
ws.add_row ['I wish', 'for a fish', 'on my fish wish dish'], offset: 3

Parameters:

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

    a customizable set of options

Options Hash (options):

  • values (Array)
  • types (Array, Symbol)
  • style (Array, Integer)
  • widths (Array) —

    each member of the widths array will affect how auto_fit behavies.

  • height (Float) —

    the row's height (in points)

  • offset (Integer) —
    • add empty columns before values
  • escape_formulas (Array, Boolean) —
    • Whether to treat a value starting with an equal sign as formula (default) or as simple string. Allowing user generated data to be interpreted as formulas can be dangerous

Yields:

  • (row)

Returns:

See Also:



431
432
433
434
435
436
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 431

def add_row(values = [], options = {})
  row = Row.new(self, values, options)
  update_column_info row, options.delete(:widths)
  yield row if block_given?
  row
end

#add_style(cell_refs, *styles) ⇒ Object

Set the style for cells in a specific column

Parameters:

  • cell_refs (String|Array) —

    Cell references

  • styles (Hash) 


599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 599

def add_style(cell_refs, *styles)
  unless cell_refs.is_a?(Array)
    cell_refs = [cell_refs]
  end

  cell_refs.each do |cell_ref|
    item = self[cell_ref]

    cells = item.is_a?(Array) ? item : [item]

    cells.each do |cell|
      styles.each do |style|
        cell.add_style(style)
      end
    end
  end
end

#add_table(ref, options = {}) {|tables.last| ... } ⇒ Object

needs documentation

Yields:



496
497
498
499
500
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 496

def add_table(ref, options = {})
  tables << Table.new(ref, self, options)
  yield tables.last if block_given?
  tables.last
end

#auto_filter ⇒ Object

A range that Excel will apply an auto-filter to "A1:B3" This will turn filtering on for the cells in the range. The first row is considered the header, while subsequent rows are considered to be data.

Returns:

  • String



180
181
182
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 180

def auto_filter
  @auto_filter ||= AutoFilter.new self
end

#auto_filter=(v) ⇒ Object

The auto filter range for the worksheet

Parameters:

  • v (String)

See Also:



336
337
338
339
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 336

def auto_filter=(v)
  DataTypeValidator.validate :worksheet_auto_filter, String, v
  auto_filter.range = v
end

#cells ⇒ Array

convenience method to access all cells in this worksheet

Returns:

  • (Array) —

    cells



285
286
287
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 285

def cells
  rows.flatten
end

#col_breaks ⇒ Object

Note:

Please do not use this directly. Instead use

A collection of column breaks added to this worksheet add_page_break

See Also:



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

def col_breaks
  @col_breaks ||= ColBreaks.new
end

#col_style(index, style, options = {}) ⇒ Object

Note:

You can also specify the style for specific columns in the call to add_row by using an array for the :styles option

Set the style for cells in a specific column

Parameters:

  • index (Integer) —

    the index of the column

  • style (Integer) —

    the cellXfs index

  • options (Hash) (defaults to: {})
  • [Integer] (Hash) —

    a customizable set of options

See Also:



576
577
578
579
580
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 576

def col_style(index, style, options = {})
  offset = options.delete(:row_offset) || 0
  cells = @rows[(offset..-1)].map { |row| row[index] }.flatten.compact
  cells.each { |cell| cell.style = style }
end

#cols(&block) ⇒ Object

returns the sheet data as columns If you pass a block, it will be evaluated whenever a row does not have a cell at a specific index. The block will be called with the row and column index in the missing cell was found.

Examples:

cols { |row_index, column_index| puts "warn - row #{row_index} does not have a cell at #{column_index}" }


172
173
174
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 172

def cols(&block)
  @rows.transpose(&block)
end

#column_info ⇒ SimpleTypedList

Column info for the sheet

Returns:

  • (SimpleTypedList) 


197
198
199
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 197

def column_info
  @column_info ||= Cols.new self
end

#column_styles(*styles) ⇒ Object

This is a helper method that lets you specify a default style for multiple columns in a worksheet in one go. This style will be applied for all not-yet-defined cells in a column. Note that you must call column_styles BEFORE adding data, otherwise the styles will not be set successfully for new cells.

Parameters:

  • styles (Integer) —

    the cellXfs indexes



559
560
561
562
563
564
565
566
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 559

def column_styles(*styles)
  styles.each_with_index do |style, index|
    next if style.nil?

    Axlsx.validate_unsigned_int(style)
    find_or_create_column_info(index).style = style
  end
end

#column_widths(*widths) ⇒ Object

Note:

For updating only a single column it is probably easier to just set the width of the ws.column_info[col_index].width directly

This is a helper method that Lets you specify a fixed width for multiple columns in a worksheet in one go. Note that you must call column_widths AFTER adding data, otherwise the width will not be set successfully. Setting a fixed column width to nil will revert the behaviour back to calculating the width for you on the next call to add_row.

Examples:

This would set the first and third column widhts but leave the second column in autofit state.

ws.column_widths 7.2, nil, 3

Parameters:

  • widths (Integer|Float|nil) 


546
547
548
549
550
551
552
553
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 546

def column_widths(*widths)
  widths.each_with_index do |value, index|
    next if value.nil?

    Axlsx.validate_unsigned_numeric(value) unless value.nil?
    find_or_create_column_info(index).width = value
  end
end

#comments ⇒ Array|SimpleTypedList

The a shortcut to the worksheet_comments list of comments

Returns:

  • (Array|SimpleTypedList) 


154
155
156
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 154

def comments
  worksheet_comments.comments if worksheet_comments.has_comments?
end

#dimension ⇒ Dimension

The dimensions of a worksheet. This is not actually a required element by the spec, but at least a few other document readers expect this for conversion

Returns:



313
314
315
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 313

def dimension
  @dimension ||= Dimension.new self
end

#drawing ⇒ Drawing

Note:

the recommended way to work with drawings and charts is Worksheet#add_chart

The drawing associated with this worksheet.

Returns:

See Also:



374
375
376
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 374

def drawing
  worksheet_drawing.drawing
end

#fit_to_page? ⇒ Boolean

Indicates if the worksheet will be fit by witdh or height to a specific number of pages. To alter the width or height for page fitting, please use page_setup.fit_to_widht or page_setup.fit_to_height. If you want the worksheet to fit on more pages (e.g. 2x2), set PageSetup#fit_to_width and PageSetup#fit_to_height accordingly.

Returns:

  • (Boolean) —

    Boolean

See Also:



189
190
191
192
193
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 189

def fit_to_page?
  return false unless Axlsx.instance_values_for(self).key?('page_setup')

  page_setup.fit_to_page?
end

#header_footer {|@header_footer| ... } ⇒ HeaderFooter

Options for headers and footers.

Examples:

wb = Axlsx::Package.new.workbook
# would generate something like: "file.xlsx : sheet_name 2 of 7 date with timestamp"
header = {:different_odd_ => false, :odd_header => "&L&F : &A&C&Pof%N%R%D %T"}
ws = wb.add_worksheet :header_footer => header

Yields:

Returns:

See Also:



277
278
279
280
281
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 277

def header_footer
  @header_footer ||= HeaderFooter.new
  yield @header_footer if block_given?
  @header_footer
end

#hyperlinks ⇒ WorksheetHyperlinks

A typed collection of hyperlinks associated with this worksheet

Returns:



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

def hyperlinks
  @hyperlinks ||= WorksheetHyperlinks.new self
end

#index ⇒ Integer

The index of this worksheet in the owning Workbook's worksheets list.

Returns:

  • (Integer) 


366
367
368
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 366

def index
  @workbook.worksheets.index(self)
end

#initialize_page_options(options) ⇒ Object

Initalizes page margin, setup and print options

Parameters:

  • options (Hash) —

    Options passed in from the initializer



36
37
38
39
40
41
42
43
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 36

def initialize_page_options(options)
  @page_margins = PageMargins.new options[:page_margins] if options[:page_margins]
  @page_setup = PageSetup.new options[:page_setup] if options[:page_setup]
  @print_options = PrintOptions.new options[:print_options] if options[:print_options]
  @header_footer = HeaderFooter.new options[:header_footer] if options[:header_footer]
  @row_breaks = RowBreaks.new
  @col_breaks = ColBreaks.new
end

#merge_cells(cells) ⇒ Object

Creates merge information for this worksheet. Cells can be merged by calling the merge_cells method on a worksheet.

Examples:

This would merge the three cells C1..E1 #

worksheet.merge_cells "C1:E1"
# you can also provide an array of cells to be merged
worksheet.merge_cells worksheet.rows.first.cells[(2..4)]
#alternatively you can do it from a single cell
worksheet["C1"].merge worksheet["E1"]

Parameters:

  • cells (Array, string) 


298
299
300
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 298

def merge_cells(cells)
  merged_cells.add cells
end

#name ⇒ String

The name of the worksheet

Returns:

  • (String) 


47
48
49
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 47

def name
  @name ||= "Sheet#{index + 1}"
end

#name=(name) ⇒ Object

The name of the worksheet The name of a worksheet must be unique in the workbook, and must not exceed the number of characters defined in Axlsx::WORKSHEET_MAX_NAME_LENGTH

Parameters:

  • name (String) 


328
329
330
331
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 328

def name=(name)
  validate_sheet_name name
  @name = Axlsx.coder.encode(name)
end

#name_to_cell(name) ⇒ Cell

returns the column and row index for a named based cell

Parameters:

  • name (String) —

    The cell or cell range to return. "A1" will return the first cell of the first row.

Returns:



703
704
705
706
707
708
709
710
711
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 703

def name_to_cell(name)
  col_index, row_index = *Axlsx.name_to_indices(name)

  r = rows[row_index]

  if r
    r[col_index]
  end
end

#outline_level_columns(start_index, end_index, level = 1, collapsed = true) ⇒ Object

shortcut level to specify the outline level for a series of columns Oulining is what lets you add collapse and expand to a data set.

Parameters:

  • start_index (Integer) —

    The zero based index of the first column of outlining.

  • end_index (Integer) —

    The zero based index of the last column to be outlined

  • level (integer) (defaults to: 1) —

    The level of outline to apply

  • collapsed (Integer) (defaults to: true) —

    The initial collapsed state of the outline group



746
747
748
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 746

def outline_level_columns(start_index, end_index, level = 1, collapsed = true)
  outline column_info, (start_index..end_index), level, collapsed
end

#outline_level_rows(start_index, end_index, level = 1, collapsed = true) ⇒ Object

shortcut level to specify the outline level for a series of rows Oulining is what lets you add collapse and expand to a data set.

Parameters:

  • start_index (Integer) —

    The zero based index of the first row of outlining.

  • end_index (Integer) —

    The zero based index of the last row to be outlined

  • level (integer) (defaults to: 1) —

    The level of outline to apply

  • collapsed (Integer) (defaults to: true) —

    The initial collapsed state of the outline group



736
737
738
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 736

def outline_level_rows(start_index, end_index, level = 1, collapsed = true)
  outline rows, (start_index..end_index), level, collapsed
end

#page_margins {|@page_margins| ... } ⇒ PageMargins

Page margins for printing the worksheet.

Examples:

wb = Axlsx::Package.new.workbook
# using options when creating the worksheet.
ws = wb.add_worksheet :page_margins => {:left => 1.9, :header => 0.1}

# use the set method of the page_margins object
ws.page_margins.set(:bottom => 3, :footer => 0.7)

# set page margins in a block
ws.page_margins do |margins|
  margins.right = 6
  margins.top = 0.2
end

Yields:

Returns:

See Also:



217
218
219
220
221
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 217

def page_margins
  @page_margins ||= PageMargins.new
  yield @page_margins if block_given?
  @page_margins
end

#page_setup {|@page_setup| ... } ⇒ PageSetup

Page setup settings for printing the worksheet.

Examples:

wb = Axlsx::Package.new.workbook

# using options when creating the worksheet.
ws = wb.add_worksheet :page_setup => {:fit_to_width => 2, :orientation => :landscape}

# use the set method of the page_setup object
ws.page_setup.set(:paper_width => "297mm", :paper_height => "210mm")

# setup page in a block
ws.page_setup do |page|
  page.scale = 80
  page.orientation = :portrait
end

Yields:

Returns:

See Also:



240
241
242
243
244
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 240

def page_setup
  @page_setup ||= PageSetup.new
  yield @page_setup if block_given?
  @page_setup
end

#pivot_tables ⇒ Array

The pivot tables in this worksheet

Returns:

  • (Array) —

    of Table



126
127
128
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 126

def pivot_tables
  @pivot_tables ||= PivotTables.new self
end

#pn ⇒ String

The part name of this worksheet

Returns:

  • (String) 


347
348
349
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 347

def pn
  format(WORKSHEET_PN, index + 1)
end

#print_options {|@print_options| ... } ⇒ PrintOptions

Options for printing the worksheet.

Examples:

wb = Axlsx::Package.new.workbook
# using options when creating the worksheet.
ws = wb.add_worksheet :print_options => {:grid_lines => true, :horizontal_centered => true}

# use the set method of the page_margins object
ws.print_options.set(:headings => true)

# set page margins in a block
ws.print_options do |options|
  options.horizontal_centered = true
  options.vertical_centered = true
end

Yields:

Returns:

See Also:



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

def print_options
  @print_options ||= PrintOptions.new
  yield @print_options if block_given?
  @print_options
end

#protect_range(cells) ⇒ ProtectedRange

Note:

When using an array of cells, a contiguous range is created from the minimum top left to the maximum top bottom of the cells provided.

Adds a new protected cell range to the worksheet. Note that protected ranges are only in effect when sheet protection is enabled.

Parameters:

  • cells (String|Array) —

    The string reference for the cells to protect or an array of cells.

Returns:



306
307
308
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 306

def protect_range(cells)
  protected_ranges.add_range(cells)
end

#relationships ⇒ Relationships

The worksheet relationships. This is managed automatically by the worksheet

Returns:



667
668
669
670
671
672
673
674
675
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 667

def relationships
  r = Relationships.new
  r + [tables.relationships,
       worksheet_comments.relationships,
       hyperlinks.relationships,
       worksheet_drawing.relationship,
       pivot_tables.relationships].flatten.compact || []
  r
end

#rels_pn ⇒ String

The relationship part name of this worksheet

Returns:

  • (String) 


353
354
355
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 353

def rels_pn
  format(WORKSHEET_RELS_PN, index + 1)
end

#rId ⇒ String

The relationship id of this worksheet.

Returns:

  • (String)

See Also:



360
361
362
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 360

def rId
  @workbook.relationships.for(self).Id
end

#row_breaks ⇒ Object

Note:

Please do not use this directly. Instead use

A collection of row breaks added to this worksheet add_page_break

See Also:



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

def row_breaks
  @row_breaks ||= RowBreaks.new
end

#row_style(index, style, options = {}) ⇒ Object

Note:

You can also specify the style in the add_row call

Set the style for cells in a specific row

Parameters:

  • index (Integer) —

    or range of indexes in the table

  • style (Integer) —

    the cellXfs index

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

    the options used when applying the style

  • [Integer] (Hash) —

    a customizable set of options

See Also:



590
591
592
593
594
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 590

def row_style(index, style, options = {})
  offset = options.delete(:col_offset) || 0
  cells = cols[(offset..-1)].map { |column| column[index] }.flatten.compact
  cells.each { |cell| cell.style = style }
end

#rows ⇒ SimpleTypedList

Note:

The recommended way to manage rows is Worksheet#add_row

The rows in this worksheet

Returns:

  • (SimpleTypedList)

See Also:



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

def rows
  @rows ||= SimpleTypedList.new Row
end

#sheet_calc_pr ⇒ SheetCalcPr

The sheet calculation properties

Returns:



83
84
85
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 83

def sheet_calc_pr
  @sheet_calc_pr ||= SheetCalcPr.new
end

#sheet_format_pr {|@sheet_format_pr| ... } ⇒ SheetFormatPr

The sheet format pr for this worksheet

Yields:

Returns:

See Also:

  • [SheetFormatPr]


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

def sheet_format_pr
  @sheet_format_pr ||= SheetFormatPr.new
  yield @sheet_format_pr if block_given?
  @sheet_format_pr
end

#sheet_pr ⇒ SheetPr

The sheet properties for this workbook. Currently only pageSetUpPr -> fitToPage is implemented

Returns:



320
321
322
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 320

def sheet_pr
  @sheet_pr ||= SheetPr.new self
end

#sheet_protection {|@sheet_protection| ... } ⇒ SheetProtection

The sheet protection object for this workbook

Yields:

Returns:

See Also:



90
91
92
93
94
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 90

def sheet_protection
  @sheet_protection ||= SheetProtection.new
  yield @sheet_protection if block_given?
  @sheet_protection
end

#sheet_view {|@sheet_view| ... } ⇒ SheetView

The sheet view object for this worksheet

Yields:

Returns:

See Also:

  • [SheetView]


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

def sheet_view
  @sheet_view ||= SheetView.new
  yield @sheet_view if block_given?
  @sheet_view
end

#state ⇒ Object

The visibility of this sheet



77
78
79
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 77

def state
  @state ||= :visible
end

#state=(sheet_state) ⇒ Object

Specifies the visible state of this sheet. Allowed states are :visible, :hidden or :very_hidden. The default value is :visible.

Worksheets in the :hidden state can be shown using the sheet formatting properties in Excel. :very_hidden sheets should be inaccessible to end users.

Parameters:

  • sheet_state (Symbol) —

    The visible state for this sheet.



71
72
73
74
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 71

def state=(sheet_state)
  RestrictionValidator.validate :worksheet_state, [:visible, :hidden, :very_hidden], sheet_state
  @state = sheet_state
end

#styles ⇒ Object

Note:

The XLSX format does not support worksheet-specific styles. Even when using this method you're still working with the single global Styles object in the workbook.

Shortcut method to access workbook styles

This lets us do stuff like:

Examples:

p = Axlsx::Package.new
p.workbook.add_worksheet(:name => 'foo') do |sheet|
  my_style = sheet.styles.add_style { :bg_color => "FF0000" }
  sheet.add_row ['Oh No!'], :styles => my_style
end
p.serialize 'foo.xlsx'


726
727
728
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 726

def styles
  @styles ||= workbook.styles
end

#tables ⇒ Array

The tables in this worksheet

Returns:

  • (Array) —

    of Table



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

def tables
  @tables ||= Tables.new self
end

#to_sheet_node_xml_string(str = +'')) ⇒ Object

Returns a sheet node serialization for this sheet in the workbook.



643
644
645
646
647
648
649
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 643

def to_sheet_node_xml_string(str = +'')
  add_autofilter_defined_name_to_workbook
  str << '<sheet '
  serialized_attributes str
  str << 'name="' << name << '" '
  str << 'r:id="' << rId << '"></sheet>'
end

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

Serializes the worksheet object to an xml string This intentionally does not use nokogiri for performance reasons

Returns:

  • (String) 


654
655
656
657
658
659
660
661
662
663
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 654

def to_xml_string(str = +'')
  add_autofilter_defined_name_to_workbook
  auto_filter.apply if auto_filter.range
  str << '<?xml version="1.0" encoding="UTF-8"?>'
  str << worksheet_node
  serializable_parts.each do |item|
    item.to_xml_string(str) if item
  end
  str << '</worksheet>'
end