Class: Axlsx::Worksheet - - - -

- -

Inherits:

- Object - -

Object
Axlsx::Worksheet

- show all - -

Defined in:

lib/axlsx/workbook/worksheet/worksheet.rb

- -

Overview

- -

The Worksheet class represents a worksheet in the workbook.

- - -

- -

Instance Attribute Summary (collapse)

- - - - (Array) auto_fit_data - - - - - - - readonly - - - - - - - - -
-
An array of content based calculated column widths.
-
- -
- - - - (String) name - - - - - - - - - - - - - - - -
-
The name of the worksheet.
-
- -
- - - - (SimpleTypedList) rows - - - - - - - readonly - - - - - - - - -
-
The rows in this worksheet.
-
- -
- - - - (Workbook) workbook - - - - - - - - - - - - - - - -
-
The workbook that owns this worksheet.
-
- -

- - - - - -

- Instance Method Summary - (collapse) -

- -

- - - - (Object) add_chart(chart_type, options = {}) {|chart| ... } - - - - - - - - - - - - -
-
Adds a chart to this worksheets drawing.
-
- -
- - - - (Object) add_image(options = {}) {|image| ... } - - - - - - - - - - - - -
-
Adds a media item to the worksheets drawing.
-
- -
- - - - (Row) add_row(values = [], options = {}) {|@rows.last| ... } - - - - - - - - - - - - -
-
Adds a row to the worksheet and updates auto fit data.
-
- -
- - - - (Float) auto_width(col) - - - - - - - - - - - - -
-
Determines the proper width for a column based on content.
-
- -
- - - - (Object) col_style(index, style, options = {}) - - - - - - - - - - - - -
-
Set the style for cells in a specific column.
-
- -
- - - - (Object) cols - - - - - - - - - - - - -
-
returns the sheet data as columnw.
-
- -
- - - - (Drawing) drawing - - - - - - - - - - - - -
-
The drawing associated with this worksheet.
-
- -
- - - - (Integer) index - - - - - - - - - - - - -
-
The index of this worksheet in the owning Workbook’s worksheets list.
-
- -
- - - - (Worksheet) initialize(wb, options = {}) - - - - - - constructor - - - - - - - - -
-
Creates a new worksheet.
-
- -
- - - - (String) pn - - - - - - - - - - - - -
-
The part name of this worksheet.
-
- -
- - - - (Relationships) relationships - - - - - - - - - - - - -
-
The worksheet relationships.
-
- -
- - - - (String) rels_pn - - - - - - - - - - - - -
-
The relationship part name of this worksheet.
-
- -
- - - - (String) rId - - - - - - - - - - - - -
-
The relationship Id of thiw worksheet.
-
- -
- - - - (Object) row_style(index, style, options = {}) - - - - - - - - - - - - -
-
Set the style for cells in a specific row.
-
- -
- - - - (String) to_xml - - - - - - - - - - - - -
-
Serializes the worksheet document.
-
- -
- - - - (Array) update_auto_fit_data(cells) - - - - - - - - - - - - -
-
Updates auto fit data.
-
- -

- - -

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

- - - -

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:

- - - (SimpleTypedList) - - - -

- -

Returns:

- - - (Workbook) - - - -

- -

- - - - -

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

- -

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:

- - - (Row) - - - -

- -

- - - - -

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

- - - -

Returns:

- - - (Drawing) - - - -

- -

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:

- - - (Relationships) - - - -

- -

- - - - -

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

- - - -

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
-

- -

Class: Axlsx::Worksheet - - - -

Overview

Instance Attribute Summary (collapse)

- Instance Method Summary - (collapse) -

Constructor Details

Parameters:

Options Hash (options):

See Also:

Instance Attribute Details

Returns:

Returns:

Returns:

See Also:

Returns:

Instance Method Details

Parameters:

Options Hash (options):

Yields:

See Also:

Parameters:

Options Hash (options):

Yields:

Parameters:

Options Hash (options):

Yields:

Returns:

Parameters:

Returns:

Parameters:

Options Hash (options):

See Also:

Returns:

See Also:

Returns:

Returns:

Returns:

Returns:

Returns:

Parameters:

Options Hash (options):

See Also:

Returns:

Parameters:

Returns:

Options Hash (`options`):

Options Hash (`options`):

Options Hash (`options`):

Options Hash (`options`):

Options Hash (`options`):

Options Hash (`options`):