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 sheet.

See Also:



42
43
44
45
46
47
48
49
50
51
52
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 42

def initialize(wb, options={})
  @drawing = 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 = []
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) —

    the string defining the cell or range of cells

Returns:



76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 76

def [](cell_def)
  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:



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

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) 


228
229
230
231
232
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 228

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

#add_row(values = [], options = {}) {|@rows.last| ... } ⇒ Row

Adds a row to the worksheet and updates auto fit data

Parameters:

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

    a customizable set of options

Options Hash (options):

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

Yields:

Returns:



157
158
159
160
161
162
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 157

def add_row(values=[], options={})
  Row.new(self, values, options)
  update_auto_fit_data @rows.last.cells
  yield @rows.last if block_given?
  @rows.last
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:



194
195
196
197
198
199
200
201
202
203
204
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 194

def col_style(index, style, options={})
  offset = options.delete(:row_offset) || 0
  @rows[(offset..-1)].each do |r| 
    cells = r.cells[index]
    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



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

def cols
  @rows.transpose
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:



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

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) 


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

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) 


63
64
65
66
67
68
69
70
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 63

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:



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

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

#pn ⇒ String

The part name of this worksheet

Returns:

  • (String) 


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

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

#relationships ⇒ Relationships

The worksheet relationships. This is managed automatically by the worksheet

Returns:



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

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) 


128
129
130
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 128

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

#rId ⇒ String

The relationship Id of thiw worksheet

Returns:

  • (String) 


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

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:



171
172
173
174
175
176
177
178
179
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 171

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) 


236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 236

def to_xml
  builder = Nokogiri::XML::Builder.new(:encoding => ENCODING) do |xml|
    xml.worksheet(:xmlns => XML_NS, 
                  :'xmlns:r' => XML_NS_R) {
      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?
      xml.drawing :"r:id"=>"rId1" if @drawing          
    }
  end
  builder.to_xml
end