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:



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

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
end

Instance Attribute Details

#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

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



48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 48

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:



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

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) 


192
193
194
195
196
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 192

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:



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

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:



158
159
160
161
162
163
164
165
166
167
168
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 158

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



146
147
148
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 146

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:



112
113
114
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 112

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) 


104
105
106
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 104

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



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

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) 


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

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

#relationships ⇒ Relationships

The worksheet relationships. This is managed automatically by the worksheet

Returns:



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

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) 


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

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

#rId ⇒ String

The relationship Id of thiw worksheet

Returns:

  • (String) 


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

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:



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

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) 


200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 200

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.drawing :"r:id"=>"rId1" if @drawing          
    }
  end
  builder.to_xml
end