Description

class Axlsx::Worksheet

The Worksheet class represents a worksheet in the workbook.

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)

    indicates if gridlines should be shown for this sheet.

Yields:

  • (_self)

Yield Parameters:

  • _self (Axlsx::Worksheet)

    the object that the method was called on

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

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)

Shortcut to worsksheet_comments#add_comment

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.

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.

Adds a new hyperlink to the worksheet

Parameters:

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

    for the hyperlink

Adds a media item to the worksheets drawing

Parameters:

  • [Hash] (Hash)

    a customizable set of options

Yields:

  • (image)

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

Yields:

  • (pivot_tables.last)

Adds a row to the worksheet and updates auto fit data.

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]

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)

Yields:

  • (row)

needs documentation

Yields:

  • (tables.last)

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.

The auto filter range for the worksheet

Parameters:

  • v (String)

convinience method to access all cells in this worksheet

Note:

Please do not use this directly. Instead use

A collection of column breaks added to this worksheet add_page_break

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

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| p "warn - row #{row_index} is does not have a cell at #{column_index}

Column info for the sheet

Note:

For updating only a single column it is probably easier to just set the width of the ws.column_info.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|Fixnum|nil)

The a shortcut to the worksheet_comments list of comments

conditional formattings

data validations array

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

Note:

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

The drawing associated with this worksheet.

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.

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:

  • (@header_footer)

A typed collection of hyperlinks associated with this worksheet

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

Initalizes page margin, setup and print options

Parameters:

  • options (Hash)

    Options passed in from the initializer

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)

merged cells array

The name of the worksheet

The name of the worksheet The name of a worksheet must be unique in the workbook, and must not exceed 31 characters

Parameters:

  • name (String)

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.

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

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

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:

  • (@page_margins)

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:

  • (@page_setup)

The pivot tables in this worksheet

The part name of this worksheet

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

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:

  • (@print_options)
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.

Note:

The recommended way to manage protected ranges is with Worksheet#protect_range

A collection of protected ranges in the worksheet

The worksheet relationships. This is managed automatically by the worksheet

The relationship part name of this worksheet

The relationship id of this worksheet.

Note:

Please do not use this directly. Instead use

A collection of row breaks added to this worksheet add_page_break

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

Note:

The recommended way to manage rows is Worksheet#add_row

The rows in this worksheet

Deprecated.

Use SheetView#tab_selected instead.

Indicates if the worksheet is selected in the workbook It is possible to have more than one worksheet selected, however it might cause issues in some older versions of excel when using copy and paste.

Deprecated.

Use SheetView#tab_selected= instead.

The sheet calculation properties

The sheet format pr for this worksheet

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

The sheet protection object for this workbook

Yields:

  • (@sheet_protection)

The sheet view object for this worksheet

Yields:

  • (@sheet_view)
Deprecated.

Use SheetView#show_grid_lines instead.

Indicates if the worksheet should show gridlines or not

Deprecated.

Use SheetView#show_grid_lines= instead.

Indicates if gridlines should be shown in the sheet. This is true by default.

The visibility of this sheet

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.

shortcut method to access styles direclty from the worksheet 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'

The tables in this worksheet

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

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

Raises:

  • (ArgumentError)

The workbook that owns this worksheet

The comments associated with this worksheet

Helper method for parsingout the root node for worksheet

Worksheet is referenced in 0 repositories