Class: Axlsx::Worksheet

Inherits:
Object
  • Object
show all
Defined in:
lib/axlsx/workbook/worksheet/worksheet.rb

Overview

The Worksheet class represents a worksheet in the workbook.

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

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

Note:

the recommended way to manage worksheets is Workbook#add_worksheet

Creates a new worksheet.

Options Hash (options):

  • name (String) —

    The name of this worksheet.

  • page_margins (Hash) —

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

  • show_gridlines (Boolean) —

    indicates if gridlines should be shown for this sheet.

See Also:



97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 97

def initialize(wb, options={})
  self.workbook = wb
  @workbook.worksheets << self

  @drawing = @page_margins = @auto_filter = nil
  @merged_cells = []
  @auto_fit_data = []

  @selected = false
  @show_gridlines = true
  self.name = "Sheet" + (index+1).to_s
  @page_margins = PageMargins.new options[:page_margins] if options[:page_margins]

  @rows = SimpleTypedList.new Row
  @column_info = SimpleTypedList.new Col
  # @cols = SimpleTypedList.new Cell
  @tables = SimpleTypedList.new Table

  options.each do |o|
    self.send("#{o[0]}=", o[1]) if self.respond_to? "#{o[0]}="
  end
end

Instance Attribute Details

#auto_filter ⇒ Object

An range that excel will apply an autfilter 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 considerd to be data.



39
40
41
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 39

def auto_filter
  @auto_filter
end

#auto_fit_data ⇒ Array (readonly)

Note:

a single auto fit data item is a hash with :longest => [String] and :sz=> [Integer] members.

An array of content based calculated column widths.



28
29
30
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 28

def auto_fit_data
  @auto_fit_data
end

#column_info ⇒ SimpleTypedList (readonly)

Column info for the sheet



59
60
61
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 59

def column_info
  @column_info
end

#fit_to_page ⇒ Object

Indicates if the worksheet should print in a single page



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

def fit_to_page
  @fit_to_page
end

#merged_cells ⇒ Object (readonly)

An array of merged cell ranges e.d “A1:B3” Content and formatting is read from the first cell.



33
34
35
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 33

def merged_cells
  @merged_cells
end

#name ⇒ String

The name of the worksheet



9
10
11
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 9

def name
  @name
end

#rows ⇒ SimpleTypedList (readonly)

Note:

The recommended way to manage rows is Worksheet#add_row

The rows in this worksheet

See Also:



23
24
25
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 23

def rows
  @rows
end

#selected ⇒ Object

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.



50
51
52
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 50

def selected
  @selected
end

#show_gridlines ⇒ Object

Indicates if the worksheet should show gridlines or not



43
44
45
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 43

def show_gridlines
  @show_gridlines
end

#tables ⇒ Array (readonly)

The tables in this worksheet



17
18
19
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 17

def tables
  @tables
end

#workbook ⇒ Workbook

The workbook that owns this worksheet



13
14
15
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 13

def workbook
  @workbook
end

Class Method Details

.thin_chars ⇒ String

definition of characters which are less than the maximum width of 0-9 in the default font for use in String#count. This is used for autowidth calculations



87
88
89
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 87

def self.thin_chars
  @thin_chars ||= "^.acefijklrstxyzFIJL()-"
end

Instance Method Details

#[](cell_def) ⇒ Cell, Array

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



431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 431

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

  parts = cell_def.split(':')
  first = name_to_cell parts[0]
  if parts.size == 1
    first
  else
    cells = []
    last = name_to_cell(parts[1])
    rows[(first.row.index..last.row.index)].each do |r|
      r.cells[(first.index..last.index)].each do |c|
        cells << c
      end
    end
    cells
  end
end

#abs_auto_filter ⇒ Object

The absolute auto filter range

See Also:



201
202
203
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 201

def abs_auto_filter
  Axlsx.cell_range(@auto_filter.split(':').collect { |name| name_to_cell(name)}) if @auto_filter
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.

Options Hash (options):

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

Yields:

  • (chart)

See Also:



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

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

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

Adds a media item to the worksheets drawing

Options Hash (options):

  • [] (Object) —

    unknown

Yields:

  • (image) 


383
384
385
386
387
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 383

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

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

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]

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:

See Also:



284
285
286
287
288
289
290
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 284

def add_row(values=[], options={})
  Row.new(self, values, options)
  update_column_info @rows.last.cells, options.delete(:widths) ||[], options.delete(:style) || []
  # update_auto_fit_data @rows.last.cells, options.delete(:widths) || []
  yield @rows.last if block_given?
  @rows.last
end

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

needs documentation

Yields:

  • (table) 


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

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

#cells ⇒ Array

convinience method to access all cells in this worksheet



122
123
124
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 122

def cells
  rows.flatten
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

Options Hash (options):

  • row_offset (Integer) —

    only cells after this column will be updated.

See Also:



324
325
326
327
328
329
330
331
332
333
334
335
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 324

def col_style(index, style, options={})
  offset = options.delete(:row_offset) || 0
  @rows[(offset..-1)].each do |r|
    cells = r.cells[index]
    next unless cells
    if cells.is_a?(Array)
      cells.each { |c| c.style = style }
    else
      cells.style = style
    end
  end
end

#cols ⇒ Object

returns the sheet data as columnw



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

def cols
  @rows.transpose
end

#column_widths(*args) ⇒ Object

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. Axlsx is sparse, so if you have not set data for a column, you cannot set the width. Setting a fixed column width to nil will revert the behaviour back to calculating the width for you.

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



344
345
346
347
348
349
350
351
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 344

def column_widths(*args)
  args.each_with_index do |value, index|
    next if value == nil
    Axlsx::validate_unsigned_numeric(value) unless value == nil
    @column_info[index] ||= Col.new index+1, index+1
    @column_info[index].width = value
  end
end

#dimension ⇒ String

The demensions 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



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

def dimension
  dim_start = rows.first.cells.first == nil ? 'A1' : rows.first.cells.first.r
  dim_end = rows.last.cells.last == nil ? 'AA:200' : rows.last.cells.last.r
  "#{dim_start}:#{dim_end}"
end

#drawing ⇒ Drawing

Note:

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

The drawing associated with this worksheet.

See Also:



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

def drawing
  @drawing || @drawing = Axlsx::Drawing.new(self)
end

#index ⇒ Integer

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



233
234
235
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 233

def index
  @workbook.worksheets.index(self)
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"]



135
136
137
138
139
140
141
142
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 135

def merge_cells(cells)
  @merged_cells << if cells.is_a?(String)
                     cells
                   elsif cells.is_a?(Array)
                     cells = cells.sort { |x, y| x.r <=> y.r }
                     "#{cells.first.r}:#{cells.last.r}"
                   end
end

#name_to_cell(name) ⇒ Cell

returns the column and row index for a named based cell



182
183
184
185
186
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 182

def name_to_cell(name)
  col_index, row_index = *Axlsx::name_to_indices(name)
  r = rows[row_index]
  r.cells[col_index] if r
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:

See Also:



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

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

end

#pn ⇒ String

The part name of this worksheet



215
216
217
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 215

def pn
  "#{WORKSHEET_PN % (index+1)}"
end

#relationships ⇒ Relationships

The worksheet relationships. This is managed automatically by the worksheet



419
420
421
422
423
424
425
426
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 419

def relationships
    r = Relationships.new
    @tables.each do |table|
      r << Relationship.new(TABLE_R, "../#{table.pn}")
    end
    r << Relationship.new(DRAWING_R, "../#{@drawing.pn}") if @drawing
    r
end

#rels_pn ⇒ String

The relationship part name of this worksheet



221
222
223
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 221

def rels_pn
  "#{WORKSHEET_RELS_PN % (index+1)}"
end

#rId ⇒ String

The relationship Id of thiw worksheet



227
228
229
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 227

def rId
  "rId#{index+1}"
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

Options Hash (options):

  • col_offset (Integer) —

    only cells after this column will be updated.

See Also:



301
302
303
304
305
306
307
308
309
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 301

def row_style(index, style, options={})
  offset = options.delete(:col_offset) || 0
  rs = @rows[index]
  if rs.is_a?(Array)
    rs.each { |r| r.cells[(offset..-1)].each { |c| c.style = style } }
  else
    rs.cells[(offset..-1)].each { |c| c.style = style }
  end
end

#to_xml_string ⇒ String

Serializes the object



392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 392

def to_xml_string
  str = '<?xml version="1.0" encoding="UTF-8"?>'
  str.concat "<worksheet xmlns=\"%s\" xmlns:r=\"%s\">" % [XML_NS, XML_NS_R]
  str.concat "<sheetPr><pageSetUpPr fitToPage=\"%s\"></pageSetUpPr></sheetPr>" % fit_to_page if fit_to_page
  str.concat "<dimension ref=\"%s\"></dimension>" % dimension unless rows.size == 0
  str.concat "<sheetViews><sheetView tabSelected='%s' workbookViewId='0' showGridLines='%s'><selection activeCell=\"A1\" sqref=\"A1\"/></sheetView></sheetViews>" % [@selected, show_gridlines]

  if @column_info.size > 0
    str << "<cols>"
    @column_info.each { |col| col.to_xml_string(str) }
    str.concat '</cols>'
  end
  str.concat '<sheetData>'
  @rows.each_with_index { |row, index| row.to_xml_string(index, str) }
  str.concat '</sheetData>'
  str.concat "<autoFilter ref='%s'></autoFilter>" % @auto_filter if @auto_filter
  str.concat "<mergeCells count='%s'>%s</mergeCells>" % [@merged_cells.size, @merged_cells.reduce('') { |memo, obj| memo += "<mergeCell ref='%s'></mergeCell>" % obj } ] unless @merged_cells.empty?
  page_margins.to_xml_string(str) if @page_margins
  str.concat "<drawing r:id='rId1'></drawing>" if @drawing
  unless @tables.empty?
    str.concat "<tableParts count='%s'>%s</tableParts>" % [@tables.size, @tables.reduce('') { |memo, obj| memo += "<tablePart r:id='%s'/>" % obj.rId }]
  end
  str + '</worksheet>'
end