From cdfc7251bde8347678d355e5fe65fc20e120bc23 Mon Sep 17 00:00:00 2001 From: Randy Morgan Date: Sun, 27 Nov 2011 21:45:19 +0900 Subject: updating docs for 1.0.10a release --- doc/Axlsx/Worksheet.html | 795 +++++++++++++++++++++++++++++++++-------------- 1 file changed, 570 insertions(+), 225 deletions(-) (limited to 'doc/Axlsx/Worksheet.html') diff --git a/doc/Axlsx/Worksheet.html b/doc/Axlsx/Worksheet.html index 89fb693b..0630d08b 100644 --- a/doc/Axlsx/Worksheet.html +++ b/doc/Axlsx/Worksheet.html @@ -94,8 +94,9 @@

Overview

- -

The Worksheet class represents a worksheet in the workbook.

+

+The Worksheet class represents a worksheet in the workbook. +

@@ -127,8 +128,9 @@ -
-

An array of content based calculated column widths.

+

+An array of content based calculated column widths. +

 @@ -153,8 +155,9 @@ -
-

The name of the worksheet.

+

+The name of the worksheet. +

 @@ -179,8 +182,9 @@ -
-

The rows in this worksheet.

+

+The rows in this worksheet. +

 @@ -205,8 +209,9 @@ -
-

The workbook that owns this worksheet.

+

+The workbook that owns this worksheet. +

 @@ -241,8 +246,9 @@ -
-

Adds a chart to this worksheets drawing.

+

+Adds a chart to this worksheets drawing. +

 @@ -264,8 +270,9 @@ -
-

Adds a media item to the worksheets drawing.

+

+Adds a media item to the worksheets drawing. +

 @@ -287,8 +294,33 @@ -
-

Adds a row to the worksheet and updates auto fit data.

+

+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. +
    • @@ -310,8 +342,9 @@ -
    -

    Set the style for cells in a specific column.

    +

    +Set the style for cells in a specific column. +

     @@ -333,8 +366,9 @@ -
    -

    returns the sheet data as columnw.

    +

    +returns the sheet data as columnw. +

     @@ -356,8 +390,9 @@ -
    -

    The drawing associated with this worksheet.

    +

    +The drawing associated with this worksheet. +

     @@ -379,8 +414,9 @@ -
    -

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

    +

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

     @@ -404,8 +440,9 @@ -
    -

    Creates a new worksheet.

    +

    +Creates a new worksheet. +

     @@ -427,8 +464,9 @@ -
    -

    The part name of this worksheet.

    +

    +The part name of this worksheet. +

     @@ -450,8 +488,9 @@ -
    -

    The worksheet relationships.

    +

    +The worksheet relationships. +

     @@ -473,8 +512,9 @@ -
    -

    The relationship part name of this worksheet.

    +

    +The relationship part name of this worksheet. +

     @@ -496,8 +536,9 @@ -
    -

    The relationship Id of thiw worksheet.

    +

    +The relationship Id of thiw worksheet. +

     @@ -519,8 +560,9 @@ -
    -

    Set the style for cells in a specific row.

    +

    +Set the style for cells in a specific row. +

     @@ -542,8 +584,33 @@ -
    -

    Serializes the worksheet document.

    +

    +Serializes the worksheet document. +

    +
     + + + + +
  • + + + - (Array) update_auto_fit_data(cells) + + + + + + + + + + + + +

    +Updates auto fit data. +
    • @@ -567,13 +634,15 @@
    Note: -
    -

    the recommended way to manage worksheets is Workbook#add_worksheet

    +

    +the recommended way to manage worksheets is Workbook#add_worksheet +

    - -

    Creates a new worksheet.

    +

    +Creates a new worksheet. +

    @@ -594,8 +663,9 @@ — -
    -

    a customizable set of options

    +

    +a customizable set of options +

    @@ -616,8 +686,9 @@ - —
    -

    The name of this sheet.

    + —

    +The name of this sheet. +

    @@ -652,16 +723,17 @@ 
    # 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
    +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 + @@ -687,14 +759,16 @@
    Note: -
    -

    a single auto fit data item is a hash with :longest => [String] and -:sz=> [Integer] members.

    +

    +a single auto fit data item is a hash with :longest => [String] and +:sz=> [Integer] members. +

    - -

    An array of content based calculated column widths.

    +

    +An array of content based calculated column widths. +

    @@ -712,8 +786,9 @@ — -
    -

    of Hash

    +

    +of Hash +

    @@ -733,9 +808,10 @@ 
    # File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 25
 
-def auto_fit_data
-  @auto_fit_data
-end
    +def auto_fit_data + @auto_fit_data +end + @@ -753,8 +829,9 @@

    - -

    The name of the worksheet

    +

    +The name of the worksheet +

    @@ -788,9 +865,10 @@ 
    # File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 9
 
-def name
-  @name
-end
    +def name + @name +end + @@ -811,13 +889,15 @@
    Note: -
    -

    The recommended way to manage rows is Worksheet#add_row

    +

    +The recommended way to manage rows is Worksheet#add_row +

    - -

    The rows in this worksheet

    +

    +The rows in this worksheet +

    @@ -858,9 +938,10 @@ 
    # File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 20
 
-def rows
-  @rows
-end
    +def rows + @rows +end + @@ -878,8 +959,9 @@

    - -

    The workbook that owns this worksheet

    +

    +The workbook that owns this worksheet +

    @@ -913,9 +995,10 @@ 
    # File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 13
 
-def workbook
-  @workbook
-end
    +def workbook + @workbook +end + @@ -940,16 +1023,18 @@
    Note: -
    -

    each chart type also specifies additional options

    +

    +each chart type also specifies additional options +

    - -

    Adds a chart to this worksheets drawing. This is the recommended way to +

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

    +and all the other dirty details. +

    @@ -981,8 +1066,9 @@ and all the other dirty details.

    — -
    -

    a customizable set of options

    +

    +a customizable set of options +

    @@ -1089,11 +1175,12 @@ and all the other dirty details.

    # 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
    +def add_chart(chart_type, options={}) + chart = drawing.add_chart(chart_type, options) + yield chart if block_given? + chart +end + @@ -1108,8 +1195,9 @@ and all the other dirty details.

    - -

    Adds a media item to the worksheets drawing

    +

    +Adds a media item to the worksheets drawing +

    @@ -1141,8 +1229,9 @@ and all the other dirty details.

    — -
    -

    a customizable set of options

    +

    +a customizable set of options +

    @@ -1161,8 +1250,9 @@ and all the other dirty details.

    - —
    -

    unknown

    + —

    +unknown +

    @@ -1198,11 +1288,12 @@ and all the other dirty details.

    # 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
    +def add_image(options={}) + image = drawing.add_image(options) + yield image if block_given? + image +end + @@ -1217,8 +1308,9 @@ and all the other dirty details.

    - -

    Adds a row to the worksheet and updates auto fit data

    +

    +Adds a row to the worksheet and updates auto fit data +

    @@ -1239,8 +1331,9 @@ and all the other dirty details.

    — -
    -

    a customizable set of options

    +

    +a customizable set of options +

    @@ -1328,12 +1421,118 @@ and all the other dirty details.

    # 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
    +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
+
    @@ -1351,14 +1550,16 @@ and all the other dirty details.

    Note: -
    -

    You can also specify the style for specific columns in the call to add_row -by using an array for the :styles option

    +

    +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

    +

    +Set the style for cells in a specific column +

    @@ -1377,8 +1578,9 @@ by using an array for the :styles option

    — -
    -

    the index of the column

    +

    +the index of the column +

    @@ -1393,8 +1595,9 @@ by using an array for the :styles option

    — -
    -

    cellXfs index

    +

    +cellXfs index +

    @@ -1411,8 +1614,9 @@ by using an array for the :styles option

    — -
    -

    a customizable set of options

    +

    +a customizable set of options +

    @@ -1435,8 +1639,9 @@ by using an array for the :styles option

    - —
    -

    only cells after this column will be updated.

    + —

    +only cells after this column will be updated. +

    @@ -1474,17 +1679,18 @@ by using an array for the :styles option

    # 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
    +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 + @@ -1499,8 +1705,9 @@ by using an array for the :styles option

    - -

    returns the sheet data as columnw

    +

    +returns the sheet data as columnw +

    @@ -1521,9 +1728,10 @@ by using an array for the :styles option

    # File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 111
 
-def cols
-  @rows.transpose
-end
    +def cols + @rows.transpose +end + @@ -1541,13 +1749,15 @@ by using an array for the :styles option

    Note: -
    -

    the recommended way to work with drawings and charts is Worksheet#add_chart

    +

    +the recommended way to work with drawings and charts is Worksheet#add_chart +

    - -

    The drawing associated with this worksheet.

    +

    +The drawing associated with this worksheet. +

    @@ -1588,9 +1798,10 @@ by using an array for the :styles option

    # File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 77
 
-def drawing
-  @drawing || @drawing = Axlsx::Drawing.new(self)
-end
    +def drawing + @drawing || @drawing = Axlsx::Drawing.new(self) +end + @@ -1605,8 +1816,9 @@ by using an array for the :styles option

    - -

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

    +

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

    @@ -1640,9 +1852,10 @@ by using an array for the :styles option

    # File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 69
 
-def index
-  @workbook.worksheets.index(self)
-end
    +def index + @workbook.worksheets.index(self) +end + @@ -1657,8 +1870,9 @@ by using an array for the :styles option

    - -

    The part name of this worksheet

    +

    +The part name of this worksheet +

    @@ -1692,9 +1906,10 @@ by using an array for the :styles option

    # File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 51
 
-def pn
-  "#{WORKSHEET_PN % (index+1)}"
-end
    +def pn + "#{WORKSHEET_PN % (index+1)}" +end + @@ -1709,8 +1924,9 @@ by using an array for the :styles option

    - -

    The worksheet relationships. This is managed automatically by the worksheet

    +

    +The worksheet relationships. This is managed automatically by the worksheet +

    @@ -1746,11 +1962,12 @@ by using an array for the :styles option

    # 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
    +def relationships + r = Relationships.new + r << Relationship.new(DRAWING_R, "../#{@drawing.pn}") if @drawing + r +end + @@ -1765,8 +1982,9 @@ by using an array for the :styles option

    - -

    The relationship part name of this worksheet

    +

    +The relationship part name of this worksheet +

    @@ -1800,9 +2018,10 @@ by using an array for the :styles option

    # File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 57
 
-def rels_pn
-  "#{WORKSHEET_RELS_PN % (index+1)}"
-end
    +def rels_pn + "#{WORKSHEET_RELS_PN % (index+1)}" +end + @@ -1817,8 +2036,9 @@ by using an array for the :styles option

    - -

    The relationship Id of thiw worksheet

    +

    +The relationship Id of thiw worksheet +

    @@ -1852,9 +2072,10 @@ by using an array for the :styles option

    # File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 63
 
-def rId
-  "rId#{index+1}"
-end
    +def rId + "rId#{index+1}" +end + @@ -1872,13 +2093,15 @@ by using an array for the :styles option

    Note: -
    -

    You can also specify the style in the add_row call

    +

    +You can also specify the style in the add_row call +

    - -

    Set the style for cells in a specific row

    +

    +Set the style for cells in a specific row +

    @@ -1897,8 +2120,9 @@ by using an array for the :styles option

    — -
    -

    or range of indexes in the table

    +

    +or range of indexes in the table +

    @@ -1913,8 +2137,9 @@ by using an array for the :styles option

    — -
    -

    cellXfs index

    +

    +cellXfs index +

    @@ -1931,8 +2156,9 @@ by using an array for the :styles option

    — -
    -

    a customizable set of options

    +

    +a customizable set of options +

    @@ -1955,8 +2181,9 @@ by using an array for the :styles option

    - —
    -

    only cells after this column will be updated.

    + —

    +only cells after this column will be updated. +

    @@ -1992,15 +2219,16 @@ by using an array for the :styles option

    # 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
    +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 + @@ -2015,8 +2243,9 @@ by using an array for the :styles option

    - -

    Serializes the worksheet document

    +

    +Serializes the worksheet document +

    @@ -2068,27 +2297,143 @@ by using an array for the :styles option

    # 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
    +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
+
    @@ -2099,9 +2444,9 @@ by using an array for the :styles option

    -- cgit v1.2.3