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



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

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) 


141
142
143
144
145
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 141

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:



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

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) 


216
217
218
219
220
221
222
223
224
225
226
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 216

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.

Raises:

  • (ArgumentError)

See Also:



113
114
115
116
117
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 113

def col_style(index, style, options={})
  raise ArgumentError, "Invalid Column Index" unless index < @rows.first.cells.size
  offset = options.delete(:row_offset) || 0
  @rows[(offset..-1)].each { |r| r.cells[index].style = style }
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:



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

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) 


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

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

- (String) pn

The part name of this worksheet

Returns:

  • (String) 


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

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

- (Relationships) relationships

The worksheet relationships. This is managed automatically by the worksheet

Returns:



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

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) 


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

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

- (String) rId

The relationship Id of thiw worksheet

Returns:

  • (String) 


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

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) —

    the index of the row

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

Raises:

  • (ArgumentError)

See Also:



99
100
101
102
103
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 99

def row_style(index, style, options={})
  raise ArgumentError, "Invalid Row Index" unless index < @rows.size
  offset = options.delete(:col_offset) || 0
  @rows[index].cells[(offset..-1)].each { |c| c.style = style }
end

- (String) to_xml

Serializes the worksheet document

Returns:

  • (String) 


149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 149

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 



188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
 
# File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 188

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