From 4d16bfc43780e5d3f7368625700b583e3e98217a Mon Sep 17 00:00:00 2001 From: Randy Morgan Date: Sun, 27 Nov 2011 18:11:42 +0900 Subject: adding in row_style and col_style methods to worksheet and active record 'acts_as_axlsx' to provide to_xlsx. --- doc/Axlsx/Worksheet.html | 1115 +++++++++++++++++++++++++++++++++++++--------- 1 file changed, 903 insertions(+), 212 deletions(-) (limited to 'doc/Axlsx/Worksheet.html') diff --git a/doc/Axlsx/Worksheet.html b/doc/Axlsx/Worksheet.html index db1e39f7..05bc4c18 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,57 @@ -
-

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

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

    +Set the style for cells in a specific column. +
    • @@ -310,8 +366,9 @@ -
    -

    The drawing associated with this worksheet.

    +

    +The drawing associated with this worksheet. +

     @@ -333,8 +390,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. +

     @@ -358,8 +416,9 @@ -
    -

    Creates a new worksheet.

    +

    +Creates a new worksheet. +

     @@ -381,8 +440,9 @@ -
    -

    The part name of this worksheet.

    +

    +The part name of this worksheet. +

     @@ -404,8 +464,9 @@ -
    -

    The worksheet relationships.

    +

    +The worksheet relationships. +

     @@ -427,8 +488,9 @@ -
    -

    The relationship part name of this worksheet.

    +

    +The relationship part name of this worksheet. +

     @@ -450,8 +512,33 @@ -
    -

    The relationship Id of thiw worksheet.

    +

    +The relationship Id of thiw worksheet. +

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

    +Set the style for cells in a specific row. +
    • @@ -473,8 +560,33 @@ -
    -

    Serializes the worksheet document.

    +

    +Serializes the worksheet document. +

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

    +Updates auto fit data. +
    • @@ -498,13 +610,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. +

    @@ -525,8 +639,9 @@ — -
    -

    a customizable set of options

    +

    +a customizable set of options +

    @@ -547,8 +662,9 @@ - —
    -

    The name of this sheet.

    + —

    +The name of this sheet. +

    @@ -582,15 +698,16 @@ 
    # 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
    +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 + @@ -616,14 +733,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. +

    @@ -641,8 +760,9 @@ — -
    -

    of Hash

    +

    +of Hash +

    @@ -662,9 +782,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 + @@ -682,8 +803,9 @@

    - -

    The name of the worksheet

    +

    +The name of the worksheet +

    @@ -717,9 +839,10 @@ 
    # File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 9
 
-def name
-  @name
-end
    +def name + @name +end + @@ -740,13 +863,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 +

    @@ -787,9 +912,10 @@ 
    # File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 20
 
-def rows
-  @rows
-end
    +def rows + @rows +end + @@ -807,8 +933,9 @@

    - -

    The workbook that owns this worksheet

    +

    +The workbook that owns this worksheet +

    @@ -842,9 +969,10 @@ 
    # File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 13
 
-def workbook
-  @workbook
-end
    +def workbook + @workbook +end + @@ -869,16 +997,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. +

    @@ -910,8 +1040,9 @@ and all the other dirty details.

    — -
    -

    a customizable set of options

    +

    +a customizable set of options +

    @@ -1009,20 +1140,21 @@ and all the other dirty details.

     
 
-105
-106
-107
-108
-109
    +132 +133 +134 +135 +136 - 
    # File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 105
-
-def add_chart(chart_type, options={})
-  chart = drawing.add_chart(chart_type, options)
-  yield chart if block_given?
-  chart
-end
    + 
    # 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
+
    @@ -1037,8 +1169,9 @@ and all the other dirty details.

    - -

    Adds a media item to the worksheets drawing

    +

    +Adds a media item to the worksheets drawing +

    @@ -1070,8 +1203,9 @@ and all the other dirty details.

    — -
    -

    a customizable set of options

    +

    +a customizable set of options +

    @@ -1090,8 +1224,9 @@ and all the other dirty details.

    - —
    -

    unknown

    + —

    +unknown +

    @@ -1118,20 +1253,21 @@ and all the other dirty details.

     
 
-114
-115
-116
-117
-118
    +141 +142 +143 +144 +145 - 
    # File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 114
-
-def add_image(options={})
-  image = drawing.add_image(options)
-  yield image if block_given?
-  image
-end
    + 
    # 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
+
    @@ -1146,8 +1282,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 +

    @@ -1168,8 +1305,9 @@ and all the other dirty details.

    — -
    -

    a customizable set of options

    +

    +a customizable set of options +

    @@ -1257,12 +1395,277 @@ and all the other dirty details.

    # 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
    +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
+
    @@ -1280,13 +1683,15 @@ and all the other dirty details.

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

    @@ -1327,9 +1732,10 @@ and all the other dirty details.

    # File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 76
 
-def drawing
-  @drawing || @drawing = Axlsx::Drawing.new(self)
-end
    +def drawing + @drawing || @drawing = Axlsx::Drawing.new(self) +end + @@ -1344,8 +1750,9 @@ and all the other dirty details.

    - -

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

    +

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

    @@ -1379,9 +1786,10 @@ and all the other dirty details.

    # File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 68
 
-def index
-  @workbook.worksheets.index(self)
-end
    +def index + @workbook.worksheets.index(self) +end + @@ -1396,8 +1804,9 @@ and all the other dirty details.

    - -

    The part name of this worksheet

    +

    +The part name of this worksheet +

    @@ -1431,9 +1840,10 @@ and all the other dirty details.

    # File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 50
 
-def pn
-  "#{WORKSHEET_PN % (index+1)}"
-end
    +def pn + "#{WORKSHEET_PN % (index+1)}" +end + @@ -1448,8 +1858,9 @@ and all the other dirty details.

    - -

    The worksheet relationships. This is managed automatically by the worksheet

    +

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

    @@ -1476,20 +1887,21 @@ and all the other dirty details.

     
 
-146
-147
-148
-149
-150
    +173 +174 +175 +176 +177 - 
    # File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 146
-
-def relationships
-    r = Relationships.new
-    r << Relationship.new(DRAWING_R, "../#{@drawing.pn}") if @drawing
-    r
-end
    + 
    # 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
+
    @@ -1504,8 +1916,9 @@ and all the other dirty details.

    - -

    The relationship part name of this worksheet

    +

    +The relationship part name of this worksheet +

    @@ -1539,9 +1952,10 @@ and all the other dirty details.

    # File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 56
 
-def rels_pn
-  "#{WORKSHEET_RELS_PN % (index+1)}"
-end
    +def rels_pn + "#{WORKSHEET_RELS_PN % (index+1)}" +end + @@ -1556,8 +1970,9 @@ and all the other dirty details.

    - -

    The relationship Id of thiw worksheet

    +

    +The relationship Id of thiw worksheet +

    @@ -1591,9 +2006,168 @@ and all the other dirty details.

    # File 'lib/axlsx/workbook/worksheet/worksheet.rb', line 62
 
-def rId
-  "rId#{index+1}"
-end
    +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
+
    @@ -1608,8 +2182,9 @@ and all the other dirty details.

    - -

    Serializes the worksheet document

    +

    +Serializes the worksheet document +

    @@ -1636,52 +2211,168 @@ and all the other dirty details.

     
 
-122
-123
-124
-125
-126
-127
-128
-129
-130
-131
-132
-133
-134
-135
-136
-137
-138
-139
-140
-141
-142
    +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 122
-
-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
    + 
    # 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
+
    @@ -1692,9 +2383,9 @@ and all the other dirty details.

    -- cgit v1.2.3