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/Cell.html | 790 ++++++++++++++++++++++++++++++++++++++++++---------- 1 file changed, 642 insertions(+), 148 deletions(-) (limited to 'doc/Axlsx/Cell.html') diff --git a/doc/Axlsx/Cell.html b/doc/Axlsx/Cell.html index 0873021a..f5e8ca97 100644 --- a/doc/Axlsx/Cell.html +++ b/doc/Axlsx/Cell.html @@ -97,20 +97,22 @@
Note: -
-

The recommended way to generate cells is via Worksheet#add_row

+

+The recommended way to generate cells is via Worksheet#add_row +

- -

A cell in a worksheet. Cell stores inforamation requried to serialize a +

+A cell in a worksheet. Cell stores inforamation requried to serialize a single worksheet cell to xml. You must provde the Row that the cell belongs to and the cells value. The data type will automatically be determed if you do not specify the :type option. The default style will be applied if you -do not supply the :style option. Changing the cell's type will recast the -value to the type specified. Altering the cell's value via the property -accessor will also automatically cast the provided value to the cell's -type.

+do not supply the :style option. Changing the cell’s type will recast +the value to the type specified. Altering the cell’s value via the +property accessor will also automatically cast the provided value to the +cell’s type. +

@@ -120,23 +122,25 @@ type.

Examples:

-

-

Manually creating and manipulating Cell objects

+

+Manually creating and manipulating Cell objects +

- 
ws = Workbook.new.add_worksheet 
-# This is the simple, and recommended way to create cells. Data types will automatically be determined for you.
-ws.add_row :values => [1,"fish",Time.now]
+      
ws = Workbook.new.add_worksheet 
+# This is the simple, and recommended way to create cells. Data types will automatically be determined for you.
+ws.add_row :values => [1,"fish",Time.now]
 
-# but you can also do this
-r = ws.add_row
-r.add_cell 1
+# but you can also do this
+r = ws.add_row
+r.add_cell 1
 
-# or even this
-r = ws.add_row
-c = Cell.new row, 1, :value=>integer
+# or even this
+r = ws.add_row
+c = Cell.new row, 1, :value=>integer
 
-# cells can also be accessed via Row#cells. The example here changes the cells type, which will automatically updated the value from 1 to 1.0
-r.cells.last.type = :float

+# cells can also be accessed via Row#cells. The example here changes the cells type, which will automatically updated the value from 1 to 1.0
+r.cells.last.type = :float
+
@@ -172,8 +176,9 @@ type.

-
-

The row this cell belongs to.

+

+The row this cell belongs to. +

 @@ -198,8 +203,9 @@ type.

-
-

The index of the cellXfs item to be applied to this cell.

+

+The index of the cellXfs item to be applied to this cell. +

 @@ -224,8 +230,9 @@ type.

-
-

The cell's data type.

+

+The cell’s data type. +

 @@ -250,8 +257,9 @@ type.

-
-

The value of this cell.

+

+The value of this cell. +

 @@ -273,6 +281,78 @@ type.

  • + - (Object) cast_value(v) + + + + + + + + + + + + +

    +Cast the value into this cells data type. +

    +
     + +
    • + + +
  • + + + - (Symbol) cell_type_from_value(v) + + + + + + + + + + + + +

    +Determines the cell type based on the cell value. +

    +
     + +
    • + + +
  • + + + - (String) col_ref + + + + + + + + + + + + +

    +converts the column index into alphabetical values. +

    +
     + +
    • + + +
  • + + - (Integer) index @@ -286,8 +366,9 @@ type.

    -
    -

    The index of the cell in the containing row.

    +

    +The index of the cell in the containing row. +
    • @@ -311,8 +392,9 @@ type.

    -
    -

    A new instance of Cell.

    +

    +A new instance of Cell. +

     @@ -334,8 +416,9 @@ type.

    -
    -

    The alpha(column)numeric(row) reference for this sell.

    +

    +The alpha(column)numeric(row) reference for this sell. +

     @@ -357,8 +440,9 @@ type.

    -
    -

    The absolute alpha(column)numeric(row) reference for this sell.

    +

    +The absolute alpha(column)numeric(row) reference for this sell. +

     @@ -380,8 +464,9 @@ type.

    -
    -

    Serializes the cell.

    +

    +Serializes the cell. +

     @@ -402,8 +487,9 @@ type.

    - -

    A new instance of Cell

    +

    +A new instance of Cell +

    @@ -422,8 +508,9 @@ type.

    — -
    -

    The row this cell belongs to.

    +

    +The row this cell belongs to. +

    @@ -440,8 +527,9 @@ type.

    — -
    -

    The value associated with this cell.

    +

    +The value associated with this cell. +

    @@ -458,8 +546,9 @@ type.

    — -
    -

    a customizable set of options

    +

    +a customizable set of options +

    @@ -482,9 +571,10 @@ type.

    - —
    -

    The intended data type for this cell. If not specified the data type will -be determined internally based on the vlue provided.

    + —

    +The intended data type for this cell. If not specified the data type will +be determined internally based on the vlue provided. +

    @@ -494,9 +584,10 @@ be determined internally based on the vlue provided.

    - —
    -

    The index of the cellXfs item to be applied to this cell. If not specified, -the default style (0) will be applied.

    + —

    +The index of the cellXfs item to be applied to this cell. If not specified, +the default style (0) will be applied. +

    @@ -523,15 +614,16 @@ the default style (0) will be applied.

    # File 'lib/axlsx/workbook/worksheet/cell.rb', line 69
 
-def initialize(row, value="", options={})
-  self.row=row
-  #reference for validation
-  @styles = row.worksheet.workbook.styles
-  @type= options[:type] || cell_type_from_value(value)
-  self.style = options[:style] || 0 
-  @value = cast_value(value)
-  @row.cells << self
-end
    +def initialize(row, value="", options={}) + self.row=row + #reference for validation + @styles = row.worksheet.workbook.styles + @type= options[:type] || cell_type_from_value(value) + self.style = options[:style] || 0 + @value = cast_value(value) + @row.cells << self +end + @@ -554,8 +646,9 @@ the default style (0) will be applied.

    - -

    The row this cell belongs to.

    +

    +The row this cell belongs to. +

    @@ -589,9 +682,10 @@ the default style (0) will be applied.

    # File 'lib/axlsx/workbook/worksheet/cell.rb', line 33
 
-def row
-  @row
-end
    +def row + @row +end + @@ -609,8 +703,9 @@ the default style (0) will be applied.

    - -

    The index of the cellXfs item to be applied to this cell.

    +

    +The index of the cellXfs item to be applied to this cell. +

    @@ -651,9 +746,10 @@ the default style (0) will be applied.

    # File 'lib/axlsx/workbook/worksheet/cell.rb', line 29
 
-def style
-  @style
-end
    +def style + @style +end + @@ -674,22 +770,26 @@ the default style (0) will be applied.

    Note: -
    -

    If the value provided cannot be cast into the type specified, type is -changed to :string and the following logic is applied.

    - -
    :string to :integer or :float, type coversions always return 0 or 0.0    
-:string, :integer, or :float to :time conversions always return the original value as a string and set the cells type to :string.
    - -

    No support is currently implemented for parsing time strings.

    +

    +If the value provided cannot be cast into the type specified, type is +changed to :string and the following logic is applied. +

    +
    + :string to :integer or :float, type coversions always return 0 or 0.0
+ :string, :integer, or :float to :time conversions always return the original value as a string and set the cells type to :string.
+
    +

    +No support is currently implemented for parsing time strings. +

    - -

    The cell's data type. Currently only four types are supported, :time, +

    +The cell’s data type. Currently only four types are supported, :time, :float, :integer and :string. Changing the type for a cell will recast the value into that type. If no type option is specified in the constructor, -the type is automatically determed.

    +the type is automatically determed. +

    @@ -707,8 +807,9 @@ the type is automatically determed.

    — -
    -

    The type of data this cell’s value is cast to.

    +

    +The type of data this cell’s value is cast to. +

    @@ -725,8 +826,9 @@ the type is automatically determed.

    — -
    -

    Cell.type must be one of [:time, :float, :integer, :string]

    +

    +Cell.type must be one of [:time, :float, :integer, :string] +

    @@ -753,9 +855,10 @@ the type is automatically determed.

    # File 'lib/axlsx/workbook/worksheet/cell.rb', line 46
 
-def type
-  @type
-end
    +def type + @type +end + @@ -773,8 +876,9 @@ the type is automatically determed.

    - -

    The value of this cell.

    +

    +The value of this cell. +

    @@ -792,8 +896,9 @@ the type is automatically determed.

    -
    -

    casted value based on cell’s type attribute.

    +

    +casted value based on cell’s type attribute. +

    @@ -813,9 +918,10 @@ the type is automatically determed.

    # File 'lib/axlsx/workbook/worksheet/cell.rb', line 57
 
-def value
-  @value
-end
    +def value + @value +end + @@ -829,16 +935,387 @@ the type is automatically determed.

    -

    +

    - - (Integer) index + - (Object) cast_value(v) + + + +

    +
    + +
    + Note: +

    +About Time - Time in OOXML is different from what you might expect. +The history as to why is interesting, but you can safely assume that if you +are generating docs on a mac, you will want to specify Workbook.1904 as +true when using time typed values. +

    +
    +
    + +

    +Cast the value into this cells data type. +

    + + +
    +
    +
    + + +

    See Also:

    +
      + +
    • Axlsx#date1904
      • + +
    + +
    + + + + +
    + 
    +
+
+169
+170
+171
+172
+173
+174
+175
+176
+177
+178
+179
+180
+181
+182
+183
+184
+185
+186
+187
+188
+189
+190
+191
    +     		+ 
    # File 'lib/axlsx/workbook/worksheet/cell.rb', line 169
+
+def cast_value(v)
+  if @type == :time && v.is_a?(Time)
+    #todo consider a time parsing method to convert strings to time
+    epoc = Workbook.date1904 ? Time.local(1904,1,1,0,0,0,0,v.zone) : Time.local(1900,1,1,0,0,0,0,v.zone)
+    ((v - epoc) /60.0/60.0/24.0).to_f
+  elsif @type == :float
+    v.to_f
+  elsif @type == :integer
+    v.to_i
+  else
+    @type = :string
+    v.to_s
+    # curious as to why this would be the cells responsibility
+    # convert your values before passing them in wankers! CGI.unescapeHTML(v.to_s).to_xs
+    # to revert, load this once when the gem is loaded.
+    # unless String.method_defined? :to_xs
+    #   require 'fast_xs' #dep
+    #   class String
+    #     alias_method :to_xs, :fast_xs
+    #   end
+    # end
+  end
+end
+
    +
    +
    + +
    +

    + + - (Symbol) cell_type_from_value(v) + + + +

    +
    + +
    + Note: +

    +This is only used when a cell is created but no :type option is specified, +the following rules apply: +

    +
      +
    1. If the value is an instance of Time, the type is set to :time + +
      2. +
    2. :float and :integer types are determined by regular expression matching. + +
      3. +
    3. Anything that does not meet either of the above is determined to be +:string. + +
      4. +
    +
    +
    + +

    +Determines the cell type based on the cell value. +

    + + +
    +
    +
    + +

    Returns:

    +
      + +
    • + + + (Symbol) + + + + — +

      +The determined type +

      +
      + +
      • + +
    + +
    + + + + +
    + 
    +
+
+153
+154
+155
+156
+157
+158
+159
+160
+161
+162
+163
    +     		+ 
    # File 'lib/axlsx/workbook/worksheet/cell.rb', line 153
+
+def cell_type_from_value(v)      
+  if v.is_a? Time
+    :time
+  elsif v.to_s.match(/\A[+-]?\d+?\Z/) #numeric
+    :integer
+  elsif v.to_s.match(/\A[+-]?\d+\.\d+?\Z/) #float
+    :float
+  else
+    :string
+  end
+end
+
    +
    +
    + +
    +

    + + - (String) col_ref

    -

    The index of the cell in the containing row.

    +
    + Note: +

    +This follows the standard spreadsheet convention of naming columns A to Z, +followed by AA to AZ etc. +

    +
    +
    + +

    +converts the column index into alphabetical values. +

    + + +
    +
    +
    + +

    Returns:

    +
      + +
    • + + + (String) + + + +
      • + +
    + +
    + + + + +
    + 
    +
+
+136
+137
+138
+139
+140
+141
+142
+143
+144
+145
+146
+147
+148
+149
+150
+151
+152
+153
+154
+155
+156
+157
+158
+159
+160
+161
+162
+163
+164
+165
+166
+167
+168
+169
+170
+171
+172
+173
+174
+175
+176
+177
+178
+179
+180
+181
+182
+183
+184
+185
+186
+187
+188
+189
+190
+191
+192
    +     		+ 
    # File 'lib/axlsx/workbook/worksheet/cell.rb', line 136
+
+def col_ref
+    chars = []
+    index = self.index
+    while index >= 26 do
+      chars << ((index % 26) + 65).chr
+      index /= 26
+    end
+    chars << ((chars.empty? ? index : index-1) + 65).chr
+    chars.reverse.join
+  end
+
+  # Determines the cell type based on the cell value. 
+  # @note This is only used when a cell is created but no :type option is specified, the following rules apply:
+  #   1. If the value is an instance of Time, the type is set to :time
+  #   2. :float and :integer types are determined by regular expression matching.
+  #   3. Anything that does not meet either of the above is determined to be :string.
+  # @return [Symbol] The determined type
+  def cell_type_from_value(v)      
+    if v.is_a? Time
+      :time
+    elsif v.to_s.match(/\A[+-]?\d+?\Z/) #numeric
+      :integer
+    elsif v.to_s.match(/\A[+-]?\d+\.\d+?\Z/) #float
+      :float
+    else
+      :string
+    end
+  end
+
+  # Cast the value into this cells data type. 
+  # @note 
+  #   About Time - Time in OOXML is *different* from what you might expect. The history as to why is interesting,  but you can safely assume that if you are generating docs on a mac, you will want to specify Workbook.1904 as true when using time typed values.
+  # @see Axlsx#date1904
+  def cast_value(v)
+    if @type == :time && v.is_a?(Time)
+      #todo consider a time parsing method to convert strings to time
+      epoc = Workbook.date1904 ? Time.local(1904,1,1,0,0,0,0,v.zone) : Time.local(1900,1,1,0,0,0,0,v.zone)
+      ((v - epoc) /60.0/60.0/24.0).to_f
+    elsif @type == :float
+      v.to_f
+    elsif @type == :integer
+      v.to_i
+    else
+      @type = :string
+      v.to_s
+      # curious as to why this would be the cells responsibility
+      # convert your values before passing them in wankers! CGI.unescapeHTML(v.to_s).to_xs
+      # to revert, load this once when the gem is loaded.
+      # unless String.method_defined? :to_xs
+      #   require 'fast_xs' #dep
+      #   class String
+      #     alias_method :to_xs, :fast_xs
+      #   end
+      # end
+    end
+  end    
+end
+
    +
    +
    + +
    +

    + + - (Integer) index + + + +

    +
    +

    +The index of the cell in the containing row. +

    @@ -856,8 +1333,9 @@ the type is automatically determed.

    — -
    -

    The index of the cell in the containing row.

    +

    +The index of the cell in the containing row. +

    @@ -877,9 +1355,10 @@ the type is automatically determed.

    # File 'lib/axlsx/workbook/worksheet/cell.rb', line 80
 
-def index
-  @row.cells.index(self)
-end
    +def index + @row.cells.index(self) +end + @@ -894,8 +1373,9 @@ the type is automatically determed.

    - -

    The alpha(column)numeric(row) reference for this sell.

    +

    +The alpha(column)numeric(row) reference for this sell. +

    @@ -905,10 +1385,12 @@ the type is automatically determed.

    Examples:

    -

    -

    Relative Cell Reference

    +

    +Relative Cell Reference +

    - 
    ws.rows.first.cells.first.r #=> "A1"
    + 
    ws.rows.first.cells.first.r #=> "A1" 
+
    @@ -923,8 +1405,9 @@ the type is automatically determed.

    — -
    -

    The alpha(column)numeric(row) reference for this sell.

    +

    +The alpha(column)numeric(row) reference for this sell. +

    @@ -944,9 +1427,10 @@ the type is automatically determed.

    # File 'lib/axlsx/workbook/worksheet/cell.rb', line 87
 
-def r
-  "#{col_ref}#{@row.index+1}"      
-end
    +def r + "#{col_ref}#{@row.index+1}" +end + @@ -961,8 +1445,9 @@ the type is automatically determed.

    - -

    The absolute alpha(column)numeric(row) reference for this sell.

    +

    +The absolute alpha(column)numeric(row) reference for this sell. +

    @@ -972,10 +1457,12 @@ the type is automatically determed.

    Examples:

    -

    -

    Absolute Cell Reference

    +

    +Absolute Cell Reference +

    - 
    ws.rows.first.cells.first.r #=> "$A$1"
    + 
    ws.rows.first.cells.first.r #=> "$A$1" 
+
    @@ -990,8 +1477,9 @@ the type is automatically determed.

    — -
    -

    The absolute alpha(column)numeric(row) reference for this sell.

    +

    +The absolute alpha(column)numeric(row) reference for this sell. +

    @@ -1011,9 +1499,10 @@ the type is automatically determed.

    # File 'lib/axlsx/workbook/worksheet/cell.rb', line 94
 
-def r_abs
-  "$#{r.split('').join('$')}"
-end
    +def r_abs + "$#{r.split('').join('$')}" +end + @@ -1031,14 +1520,16 @@ the type is automatically determed.

    Note: -
    -

    Shared Strings are not used in this library. All values are set directly in -the each sheet.

    +

    +Shared Strings are not used in this library. All values are set directly in +the each sheet. +

    - -

    Serializes the cell

    +

    +Serializes the cell +

    @@ -1057,8 +1548,9 @@ the each sheet.

    — -
    -

    The document builder instance this objects xml will be added to.

    +

    +The document builder instance this objects xml will be added to. +

    @@ -1076,8 +1568,9 @@ the each sheet.

    — -
    -

    xml text for the cell

    +

    +xml text for the cell +

    @@ -1106,18 +1599,19 @@ the each sheet.

    # File 'lib/axlsx/workbook/worksheet/cell.rb', line 114
 
-def to_xml(xml)
-  # Both 1.8 and 1.9 return the same 'fast_xf'
-  # &#12491;&#12507;&#12531;&#12468;
-  # &#12491;&#12507;&#12531;&#12468;
-  
-  # however nokogiri does a nice 'force_encoding' which we shall remove!
-  if @type == :string 
-    xml.c(:r => r, :t=>:inlineStr, :s=>style) { xml.is { xml.t @value.to_s } }
-  else
-    xml.c(:r => r, :s => style) { xml.v value }
-  end
-end
    +def to_xml(xml) + # Both 1.8 and 1.9 return the same 'fast_xf' + # &#12491;&#12507;&#12531;&#12468; + # &#12491;&#12507;&#12531;&#12468; + + # however nokogiri does a nice 'force_encoding' which we shall remove! + if @type == :string + xml.c(:r => r, :t=>:inlineStr, :s=>style) { xml.is { xml.t @value.to_s } } + else + xml.c(:r => r, :s => style) { xml.v value } + end +end + @@ -1128,9 +1622,9 @@ the each sheet.

    -- cgit v1.2.3