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

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.

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

See Also:



65
66
67
68
69
70
71
72
73
74
75
76
77
78
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 65

def initialize(wb, options={})
  @drawing = @page_margins = @auto_filter = nil
  @rows = SimpleTypedList.new Row
  self.workbook = wb
  @workbook.worksheets << self
  @auto_fit_data = []
  self.name = options[:name] || "Sheet" + (index+1).to_s

  @magick_draw = Magick::Draw.new
  @cols = SimpleTypedList.new Cell
  @merged_cells = []

  @page_margins = PageMargins.new options[:page_margins] if options[:page_margins]
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.

Returns:

  • Array



36
37
38
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 36

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.

Returns:

  • (Array) —

    of Hash



25
26
27
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 25

def auto_fit_data
  @auto_fit_data
end

#merged_cells ⇒ Object (readonly)

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

Returns:

  • Array



30
31
32
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 30

def merged_cells
  @merged_cells
end

#name ⇒ String

The name of the worksheet

Returns:

  • (String) 


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

Returns:

  • (SimpleTypedList)

See Also:



20
21
22
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 20

def rows
  @rows
end

#workbook ⇒ Workbook

The workbook that owns this worksheet

Returns:



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

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:



116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 116

def [](cell_def)
  return rows[cell_def - 1] 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

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



314
315
316
317
318
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 314

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

Parameters:

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

    a customizable set of options

Options Hash (options):

  • [] (Object) —

    unknown

Yields:

  • (image) 


323
324
325
326
327
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 323

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]

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:

Returns:

See Also:



234
235
236
237
238
239
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 234

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

#cells ⇒ Array

convinience method to access all cells in this worksheet

Returns:

  • (Array) —

    cells



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

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

Parameters:

  • index (Integer) —

    the index of the column

  • the (Integer) —

    cellXfs index

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

    a customizable set of options

Options Hash (options):

  • row_offset (Integer) —

    only cells after this column will be updated.

See Also:



273
274
275
276
277
278
279
280
281
282
283
284
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 273

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



261
262
263
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 261

def cols
  @rows.transpose
end

#column_widths(*args) ⇒ Object

Note:

For updating only a single column it is probably easier to just set ws.auto_fit_data[:fixed] 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

Parameters:

  • values (Integer|Float|Fixnum|nil) 


293
294
295
296
297
298
299
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 293

def column_widths(*args)
  args.each_with_index do |value, index|
    raise ArgumentError, "Invalid column specification" unless index < @auto_fit_data.size
    Axlsx::validate_unsigned_numeric(value) unless value == nil
    @auto_fit_data[index][:fixed] = 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

Returns:

  • (String) —

    the A1:B2 style reference for the first and last row column intersection in the workbook



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

def dimension
  "#{rows.first.cells.first.r}:#{rows.last.cells.last.r}"
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:



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

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

#index ⇒ Integer

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

Returns:

  • (Integer) 


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

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"]

Parameters:

  • (Array, string) 


95
96
97
98
99
100
101
102
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 95

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

Parameters:

  • name (String) —

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

Returns:



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

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:

Returns:

See Also:



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

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

#pn ⇒ String

The part name of this worksheet

Returns:

  • (String) 


165
166
167
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 165

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

#relationships ⇒ Relationships

The worksheet relationships. This is managed automatically by the worksheet

Returns:



370
371
372
373
374
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 370

def relationships
    r = Relationships.new
    r << Relationship.new(DRAWING_R, "../#{@drawing.pn}") if @drawing
    r
end

#rels_pn ⇒ String

The relationship part name of this worksheet

Returns:

  • (String) 


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

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

#rId ⇒ String

The relationship Id of thiw worksheet

Returns:

  • (String) 


177
178
179
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 177

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

Parameters:

  • index (Integer) —

    or range of indexes in the table

  • the (Integer) —

    cellXfs index

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

    a customizable set of options

Options Hash (options):

  • col_offset (Integer) —

    only cells after this column will be updated.

See Also:



250
251
252
253
254
255
256
257
258
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 250

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

Serializes the worksheet document

Returns:

  • (String) 


331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 331

def to_xml
  builder = Nokogiri::XML::Builder.new(:encoding => ENCODING) do |xml|
    xml.worksheet(:xmlns => XML_NS,
                  :'xmlns:r' => XML_NS_R) {
      # another patch for the folks at rubyXL as thier parser depends on this optional element.
      xml.dimension :ref=>dimension unless rows.size == 0
      # this is required by rubyXL, spec says who cares - but it seems they didnt notice
      # however, it also seems to be causing some odd [Grouped] stuff in excel 2011 - so
      # removing until I understand it better.
      # xml.sheetViews {
      #  xml.sheetView(:tabSelected => 1, :workbookViewId => 0) {
      #    xml.selection :activeCell=>"A1", :sqref => "A1"
      #  }
      # }

      if @auto_fit_data.size > 0
        xml.cols {
          @auto_fit_data.each_with_index do |col, index|
            min_max = index+1
            xml.col(:min=>min_max, :max=>min_max, :width => auto_width(col), :customWidth=>1)
          end
        }
      end
      xml.sheetData {
        @rows.each do |row|
          row.to_xml(xml)
        end
      }
      xml.autoFilter :ref=>@auto_filter if @auto_filter
      xml.mergeCells(:count=>@merged_cells.size) { @merged_cells.each { | mc | xml.mergeCell(:ref=>mc) } } unless @merged_cells.empty?
      page_margins.to_xml(xml) if @page_margins
      xml.drawing :"r:id"=>"rId1" if @drawing
    }
  end
  builder.to_xml(:save_with => 0)
end