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

- (Worksheet) initialize(wb, options = {})

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

- (Array) auto_fit_data (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

- (String) name

The name of the worksheet

Returns:

  • (String) 


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

def name
  @name
end

- (SimpleTypedList) rows (readonly)

Note:

The recommended way to manage rows is Worksheet#add_row

The rows in this worksheet

Returns:

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

- (Object) add_chart(chart_type, options = {}) {|chart| ... }

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:



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

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

- (Object) add_image(options = {}) {|image| ... }

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) 


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

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

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

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:

  • (@rows.last)

Returns:



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

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

- (Float) auto_width(col)

Note:

From ECMA docs 

 Column width measured as the number of characters of the maximum digit width of the numbers 0 .. 9 as
 rendered in the normal style's font. There are 4 pixels of margin padding (two on each side), plus 1 pixel padding for the gridlines.
 width = Truncate([!{Number of Characters} * !{Maximum Digit Width} + !{5 pixel padding}]/{Maximum Digit Width}*256)/256

Determines the proper width for a column based on content.

Parameters:

  • A (Hash) —

    hash of auto_fit_data

Returns:

  • (Float) 


232
233
234
235
236
237
238
239
240
241
242
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 232

def auto_width(col)
  mdw = 6.0 # maximum digit with is always 6.0 with RMagick's default font
  mdw_count = 0 
  best_guess = 1.5  #direct testing shows the results of the documented formula to be a bit too small. This is a best guess scaling
  font_scale = col[:sz].to_f / (self.workbook.styles.fonts[0].sz.to_f || 11.0)
  
  col[:longest].scan(/./mu).each do |i|
    mdw_count +=1 if @magick_draw.get_type_metrics(i).width >= mdw 
  end
  ((mdw_count * mdw + 5) / mdw * 256) / 256.0 * best_guess * font_scale      
end

- (Object) col_style(index, style, options = {})

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:



123
124
125
126
127
128
129
130
131
132
133
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 123

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

- (Object) cols

returns the sheet data as columnw 



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

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:



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

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

- (Integer) index

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

Returns:

  • (Integer) 


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

def index
  @workbook.worksheets.index(self)
end

- (String) pn

The part name of this worksheet

Returns:

  • (String) 


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

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

- (Relationships) relationships

The worksheet relationships. This is managed automatically by the worksheet

Returns:



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

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

- (String) rels_pn

The relationship part name of this worksheet

Returns:

  • (String) 


57
58
59
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 57

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

- (String) rId

The relationship Id of thiw worksheet

Returns:

  • (String) 


63
64
65
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 63

def rId
  "rId#{index+1}"
end

- (Object) row_style(index, style, options = {})

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:



100
101
102
103
104
105
106
107
108
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 100

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

- (String) to_xml

Serializes the worksheet document

Returns:

  • (String) 


165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 165

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=>"true")
          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

- (Array) update_auto_fit_data(cells)

Updates auto fit data. Autofit data attempts to determine the cell in a column that has the greatest width by comparing the length of the text multiplied by the size of the font.

Parameters:

  • cells (Array) —

    an array of cells

Returns:

  • (Array) —

    of Cell objects 



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

def update_auto_fit_data(cells)
  styles = self.workbook.styles
  cellXfs, fonts = styles.cellXfs, styles.fonts
  sz = fonts[0].sz

  cells.each_with_index do |item, index|
    col = @auto_fit_data[index] || {:longest=>"", :sz=>sz} 
    cell_xf = cellXfs[item.style]
    font = fonts[cell_xf.fontId || 0]
    sz = font.sz || sz

    if (col[:longest].scan(/./mu).size * col[:sz]) < (item.value.to_s.scan(/./mu).size * sz)
      col[:sz] =  sz
      col[:longest] = item.value.to_s
    end
    @auto_fit_data[index] = col
  end
  cells
end