Merge branch 'master' of https://github.com/randym/axlsx

17 files changed, 496 insertions, 101 deletions

diff --git a/README.md b/README.md
index 6a466d34..a0ab2173 100644
--- a/README.md
+++ b/README.md
@@ -264,6 +264,21 @@ To install Axlsx, use the following command:
        end
      end
 
+##Generating A Scatter Chart
+
+     wb.add_worksheet(:name => "Scatter Chart") do |sheet|
+       sheet.add_row ["First",  1,  5,  7,  9]
+       sheet.add_row ["",       1, 25, 49, 81]
+       sheet.add_row ["Second", 5,  2, 14,  9]
+       sheet.add_row ["",       5, 10, 15, 20]
+       sheet.add_chart(Axlsx::ScatterChart, :title => "example 7: Scatter Chart") do |chart|
+         chart.start_at 0, 4
+         chart.end_at 10, 19
+         chart.add_series :xData => sheet["B1:E1"], :yData => sheet["B2:E2"], :title => sheet["A1"]
+         chart.add_series :xData => sheet["B3:E3"], :yData => sheet["B4:E4"], :title => sheet["A3"]
+       end
+     end
+
 ##Auto Filter
 
      wb.add_worksheet(:name => "Auto Filter") do |sheet|
diff --git a/axlsx.gemspec b/axlsx.gemspec
index c8fda02f..d67401d3 100644
--- a/axlsx.gemspec
+++ b/axlsx.gemspec
@@ -29,6 +29,7 @@ Gem::Specification.new do |s|
   s.add_development_dependency 'yard'
   s.add_development_dependency 'yard'
   s.add_development_dependency 'rdiscount'
+  s.add_development_dependency 'perftools.rb'
 
   s.required_ruby_version = '>= 1.8.7'
   s.require_path = 'lib'
diff --git a/examples/example.rb b/examples/example.rb
index 2c0f5fc1..604ab597 100644
--- a/examples/example.rb
+++ b/examples/example.rb
@@ -210,6 +210,21 @@ wb.add_worksheet(:name => "Line Chart") do |sheet|
   end
 end
 
+##Generating A Scatter Chart
+
+wb.add_worksheet(:name => "Scatter Chart") do |sheet|
+  sheet.add_row ["First",  1,  5,  7,  9]
+  sheet.add_row ["",       1, 25, 49, 81]
+  sheet.add_row ["Second", 5,  2, 14,  9]
+  sheet.add_row ["",       5, 10, 15, 20]
+  sheet.add_chart(Axlsx::ScatterChart, :title => "example 7: Scatter Chart") do |chart|
+    chart.start_at 0, 4
+    chart.end_at 10, 19
+    chart.add_series :xData => sheet["B1:E1"], :yData => sheet["B2:E2"], :title => sheet["A1"]
+    chart.add_series :xData => sheet["B3:E3"], :yData => sheet["B4:E4"], :title => sheet["A3"]
+  end
+end
+
 ##Auto Filter
 
 wb.add_worksheet(:name => "Auto Filter") do |sheet|
diff --git a/lib/axlsx.rb b/lib/axlsx.rb
index eb5068b5..09f351fc 100644
--- a/lib/axlsx.rb
+++ b/lib/axlsx.rb
@@ -5,6 +5,9 @@ require 'axlsx/util/simple_typed_list.rb'
 require 'axlsx/util/constants.rb'
 require 'axlsx/util/validators.rb'
 require 'axlsx/util/storage.rb'
+
+#not even close to being ready but it does not break anything so it stays for now.
+# needs a full re-write to use agile-encryption properly
 require 'axlsx/util/cbf.rb'
 require 'axlsx/util/ms_off_crypto.rb'
 
@@ -60,4 +63,25 @@ module Axlsx
     [v[:i]-1, ((name[/[1-9][0-9]*/]).to_i)-1]
 
   end
+
+  # converts the column index into alphabetical values.
+  # @note This follows the standard spreadsheet convention of naming columns A to Z, followed by AA to AZ etc.
+  # @return [String]
+  def self.col_ref(index)
+    chars = []
+    while index >= 26 do
+      chars << ((index % 26) + 65).chr
+      index /= 26
+    end
+    chars << ((chars.empty? ? index : index-1) + 65).chr
+    chars.reverse.join
+  end
+
+  # @return [String] The alpha(column)numeric(row) reference for this sell.
+  # @example Relative Cell Reference
+  #   ws.rows.first.cells.first.r #=> "A1"
+  def self.cell_r(c_index, r_index)
+      Axlsx::col_ref(c_index).to_s << (r_index+1).to_s
+    end
+
 end
diff --git a/lib/axlsx/package.rb b/lib/axlsx/package.rb
index 73e72493..35ab181a 100644
--- a/lib/axlsx/package.rb
+++ b/lib/axlsx/package.rb
@@ -184,7 +184,7 @@ module Axlsx
       end
 
       workbook.tables.each do |table|
-        @parts << {:entry => "xl/#{table.pn}", :doc => table.to_xml, :schema => SML_XSD} 
+        @parts << {:entry => "xl/#{table.pn}", :doc => table.to_xml, :schema => SML_XSD}
       end
 
       workbook.charts.each do |chart|
@@ -196,12 +196,12 @@ module Axlsx
       end
 
       if use_shared_strings
-        @parts << {:entry => "xl/#{SHARED_STRINGS_PN}", :doc => workbook.shared_strings.to_xml, :schema => SML_XSD}
+        @parts << {:entry => "xl/#{SHARED_STRINGS_PN}", :doc => workbook.shared_strings.to_xml_string, :schema => SML_XSD}
       end
 
       workbook.worksheets.each do |sheet|
         @parts << {:entry => "xl/#{sheet.rels_pn}", :doc => sheet.relationships.to_xml, :schema => RELS_XSD}
-        @parts << {:entry => "xl/#{sheet.pn}", :doc => sheet.to_xml, :schema => SML_XSD}
+        @parts << {:entry => "xl/#{sheet.pn}", :doc => sheet.to_xml_string, :schema => SML_XSD}
       end
       @parts
     end
diff --git a/lib/axlsx/stylesheet/color.rb b/lib/axlsx/stylesheet/color.rb
index 4e9806ad..2a08fd3c 100644
--- a/lib/axlsx/stylesheet/color.rb
+++ b/lib/axlsx/stylesheet/color.rb
@@ -5,9 +5,9 @@ module Axlsx
     # Determines if the color is system color dependant
     # @return [Boolean]
     attr_reader :auto
-    
-    # The color as defined in rgb terms. 
-    # @note 
+
+    # The color as defined in rgb terms.
+    # @note
     #  rgb colors need to conform to ST_UnsignedIntHex. That basically means put 'FF' before you color
     # When assigning the rgb value the behavior is much like CSS selectors and can use shorthand versions as follows:
     # If you provide a two character value it will be repeated for each r, g, b assignment
@@ -25,16 +25,16 @@ module Axlsx
     # no support for theme just yet
     # @return [Integer]
     #attr_reader :theme
-    
+
     # The tint value.
     # @note valid values are between -1.0 and 1.0
     # @return [Float]
     attr_reader :tint
-    
+
     # Creates a new Color object
     # @option options [Boolean] auto
     # @option options [String] rgb
-    # @option options [Float] tint    
+    # @option options [Float] tint
     def initialize(options={})
       @rgb = "FF000000"
       options.each do |o|
@@ -42,7 +42,7 @@ module Axlsx
       end
     end
     # @see auto
-    def auto=(v) Axlsx::validate_boolean v; @auto = v end    
+    def auto=(v) Axlsx::validate_boolean v; @auto = v end
     # @see color
     def rgb=(v)
       Axlsx::validate_string(v)
@@ -56,11 +56,18 @@ module Axlsx
     def tint=(v) Axlsx::validate_float v; @tint = v end
 
     # This version does not support themes
-    # def theme=(v) Axlsx::validate_unsigned_integer v; @theme = v end    
-    
+    # def theme=(v) Axlsx::validate_unsigned_integer v; @theme = v end
+
     # Indexed colors are for backward compatability which I am choosing not to support
-    # def indexed=(v) Axlsx::validate_unsigned_integer v; @indexed = v end     
+    # def indexed=(v) Axlsx::validate_unsigned_integer v; @indexed = v end
 
+    def to_xml_string
+      str = "<color "
+      self.instance_values.each do |key, value|
+        str << key << '="' << value.to_s << '" '
+      end
+      str << "/>"
+    end
 
     # Serializes the color
     # @param [Nokogiri::XML::Builder] xml The document builder instance this objects xml will be added to.
diff --git a/lib/axlsx/workbook/shared_strings_table.rb b/lib/axlsx/workbook/shared_strings_table.rb
index 91892c38..0bdd7936 100644
--- a/lib/axlsx/workbook/shared_strings_table.rb
+++ b/lib/axlsx/workbook/shared_strings_table.rb
@@ -3,7 +3,7 @@ module Axlsx
 
   # The Shared String Table class is responsible for managing and serializing common strings in a workbook.
   # While the ECMA-376 spec allows for both inline and shared strings it seems that at least some applications like iWorks Numbers
-  # and Google Docs require that the shared string table is populated in order to interoperate properly. 
+  # and Google Docs require that the shared string table is populated in order to interoperate properly.
   # As a developer, you should never need to directly work against this class. Simply set 'use_shared_strings'
   # on the package or workbook to generate a package that uses the shared strings table instead of inline strings.
   # @note Serialization performance is affected by using this serialization method so if you do not need interoperability
@@ -15,14 +15,14 @@ module Axlsx
     # @return [Integer]
     attr_reader :count
 
-    # The total number of unique strings in the workbook. 
+    # The total number of unique strings in the workbook.
     # @return [Integer]
     def unique_count
       @unique_cells.size
     end
 
     # An array of unique cells. Multiple attributes of the cell are used in comparison
-    # each of these unique cells is parsed into the shared string table.     
+    # each of these unique cells is parsed into the shared string table.
     # @see Cell#sharable
     attr_reader :unique_cells
 
@@ -32,13 +32,18 @@ module Axlsx
       cells = cells.flatten.reject { |c| c.type != :string || c.value.start_with?('=') }
       @count = cells.size
       @unique_cells = []
+      @shared_xml_string = ""
       resolve(cells)
     end
 
+    def to_xml_string
+      '<sst xmlns="' << XML_NS << '" count="' << @count.to_s << '" uniqueCount="' << unique_count.to_s << '">' << @shared_xml_string << '</sst>'
+    end
 
     # Generate the xml document for the Shared Strings Table
     # @return [String]
     def to_xml
+
       builder = Nokogiri::XML::Builder.new(:encoding => ENCODING) do |xml|
         xml.sst(:xmlns => Axlsx::XML_NS, :count => count, :uniqueCount => unique_count) {
           @unique_cells.each do |cell|
@@ -50,22 +55,26 @@ module Axlsx
     end
 
     private
-    
-    # Interate over all of the cells in the array. 
-    # if our unique cells array does not contain a sharable cell, 
+
+    # Interate over all of the cells in the array.
+    # if our unique cells array does not contain a sharable cell,
     # add the cell to our unique cells array and set the ssti attribute on the index of this cell in the shared strings table
     # if a sharable cell already exists in our unique_cells array, set the ssti attribute of the cell and move on.
     # @return [Array] unique cells
     def resolve(cells)
       cells.each do |cell|
-        index = @unique_cells.index { |item| item.shareable(cell) }
+        cell_hash = cell.shareable_hash
+        index = @unique_cells.index do |item|
+          item == cell_hash
+        end
         if index == nil
           cell.send :ssti=, @unique_cells.size
-          @unique_cells << cell
+          @shared_xml_string << '<si>' << cell.run_xml_string << '</si>'
+          @unique_cells << cell_hash
         else
           cell.send :ssti=, index
         end
       end
-    end    
+    end
   end
 end
diff --git a/lib/axlsx/workbook/worksheet/cell.rb b/lib/axlsx/workbook/worksheet/cell.rb
index ff759cdf..24b380cb 100644
--- a/lib/axlsx/workbook/worksheet/cell.rb
+++ b/lib/axlsx/workbook/worksheet/cell.rb
@@ -25,11 +25,18 @@ module Axlsx
 
 
     # An array of available inline styes.
+    # TODO change this to a hash where each key defines attr name and validator (and any info the validator requires)
+    # then move it out to a module so we can re-use in in other classes.
+    # needs to define bla=(v) and bla methods on the class that hook into a
+    # set_attr method that kicks the suplied validator and updates the instance_variable
+    # for the key
     INLINE_STYLES = ['value', 'type', 'font_name', 'charset',
                          'family', 'b', 'i', 'strike','outline',
                          'shadow', 'condense', 'extend', 'u',
                          'vertAlign', 'sz', 'color', 'scheme']
 
+    INLINE_ATTR = [:font_name => { :validator=>:validate_string}]
+
 
     # The index of the cellXfs item to be applied to this cell.
     # @return [Integer]
@@ -69,71 +76,79 @@ module Axlsx
       @value = cast_value(v)
     end
 
+
+    # Indicates that the cell has one or more of the custom cell styles applied.
+    # @return [Boolean]
+    def is_text_run?
+      @is_text_run ||= false
+    end
+
+
     # The inline font_name property for the cell
     # @return [String]
     attr_reader :font_name
     # @see font_name
-    def font_name=(v) Axlsx::validate_string(v); @font_name = v; end
+    def font_name=(v) set_run_style :validate_string, :font_name, v; end
 
     # The inline charset property for the cell
     # @return [String]
     attr_reader :charset
     # @see charset
-    def charset=(v) Axlsx::validate_unsigned_int(v); @charset = v; end
+    def charset=(v) set_run_style :validate_unsigned_int, :charset, v; end
 
     # The inline family property for the cell
     # @return [String]
     attr_reader :family
     # @see family
-    def family=(v) Axlsx::validate_string(v); @family = v; end
+    def family=(v) set_run_style :validate_string, :family, v; end
 
     # The inline bold property for the cell
     # @return [Boolean]
     attr_reader :b
     # @see b
-    def b=(v) Axlsx::validate_boolean(v); @b = v; end
+    def b=(v) set_run_style :validate_boolean, :b, v; end
 
     # The inline italic property for the cell
     # @return [Boolean]
     attr_reader :i
     # @see i
-    def i=(v) Axlsx::validate_boolean(v); @i = v; end
+    def i=(v) set_run_style :validate_boolean, :i, v; end
 
     # The inline strike property for the cell
     # @return [Boolean]
     attr_reader :strike
     # @see strike
-    def strike=(v) Axlsx::validate_boolean(v); @strike = v; end
+    def strike=(v) set_run_style :validate_boolean, :strike, v; end
 
     # The inline outline property for the cell
     # @return [Boolean]
     attr_reader :outline
     # @see outline
-    def outline=(v) Axlsx::validate_boolean(v); @outline = v; end
+    def outline=(v) set_run_style :validate_boolean, :outline, v; end
 
     # The inline shadow property for the cell
     # @return [Boolean]
     attr_reader :shadow
     # @see shadow
-    def shadow=(v) Axlsx::validate_boolean(v); @shadow = v; end
+    def shadow=(v) set_run_style :validate_boolean, :shadow, v; end
 
     # The inline condense property for the cell
     # @return [Boolean]
     attr_reader :condense
     # @see condense
-    def condense=(v) Axlsx::validate_boolean(v); @condense = v; end
+    def condense=(v) set_run_style :validate_boolean, :condense, v; end
 
     # The inline extend property for the cell
     # @return [Boolean]
     attr_reader :extend
     # @see extend
-    def extend=(v) Axlsx::validate_boolean(v); @extend = v; end
+    def extend=(v) set_run_style :validate_boolean, :extend, v; end
 
     # The inline underline property for the cell
     # @return [Boolean]
     attr_reader :u
     # @see u
-    def u=(v) Axlsx::validate_boolean(v); @u = v; end
+    def u=(v) set_run_style :validate_boolean, :u, v; end
 
     # The inline color property for the cell
     # @return [Color]
@@ -141,27 +156,34 @@ module Axlsx
     # @param [String] The 8 character representation for an rgb color #FFFFFFFF"
     def color=(v)
       @color = v.is_a?(Color) ? v : Color.new(:rgb=>v)
+      @has_run_style = true
     end
 
     # The inline sz property for the cell
     # @return [Boolean]
     attr_reader :sz
     # @see sz
-    def sz=(v) Axlsx::validate_unsigned_int(v); @sz = v; end
+    def sz=(v) set_run_style :validate_unsigned_int, :sz, v; end
 
     # The inline vertical alignment property for the cell
     # this must be one of [:baseline, :subscript, :superscript]
     # @return [Symbol]
     attr_reader :vertAlign
     # @see vertAlign
-    def vertAlign=(v) RestrictionValidator.validate "Cell.vertAlign", [:baseline, :subscript, :superscript], v; @vertAlign = v; end
+    def vertAlign=(v)
+      RestrictionValidator.validate "Cell.vertAlign", [:baseline, :subscript, :superscript], v
+      set_run_style nil, :vertAlign, v
+    end
 
     # The inline scheme property for the cell
     # this must be one of [:none, major, minor]
     # @return [Symbol]
     attr_reader :scheme
     # @see scheme
-    def scheme=(v) RestrictionValidator.validate "Cell.schema", [:none, :major, :minor], v; @scheme = v; end
+    def scheme=(v)
+      RestrictionValidator.validate "Cell.schema", [:none, :major, :minor], v
+      set_run_style nil, :scheme, v
+    end
 
     # @param [Row] row The row this cell belongs to.
     # @param [Any] value The value associated with this cell.
@@ -202,16 +224,10 @@ module Axlsx
 
     # equality comparison to test value, type and inline style attributes
     # this is how we work out if the cell needs to be added or already exists in the shared strings table
-    def shareable(v)
-
-      #using reject becase 1.8.7 select returns an array...
-      v_hash = v.instance_values.reject { |key, val| !INLINE_STYLES.include?(key) }
+    def shareable_hash
       self_hash = self.instance_values.reject { |key, val| !INLINE_STYLES.include?(key) }
-      # required as color is an object, and the comparison will fail even though both use the same color.
-      v_hash['color'] = v_hash['color'].instance_values if v_hash['color']
       self_hash['color'] = self_hash['color'].instance_values if self_hash['color']
-
-      v_hash == self_hash
+      self_hash
     end
 
     # @return [Integer] The index of the cell in the containing row.
@@ -222,8 +238,9 @@ module Axlsx
     # @return [String] The alpha(column)numeric(row) reference for this sell.
     # @example Relative Cell Reference
     #   ws.rows.first.cells.first.r #=> "A1"
+    # @note this will be discontinued in 1.1.0 -  prefer Axlsx.cell_r
     def r
-      "#{col_ref}#{@row.index+1}"
+      "#{Axlsx::col_ref(index)}#{@row.index+1}"
     end
 
     # @return [String] The absolute alpha(column)numeric(row) reference for this sell.
@@ -259,6 +276,29 @@ module Axlsx
       self.row.worksheet.merge_cells "#{self.r}:#{range_end}" unless range_end.nil?
     end
 
+    def run_xml_string(str = '')
+      if is_text_run?
+        puts 'text run'
+        data = self.instance_values.reject{|key, value| value == nil }
+        keys = data.keys & INLINE_STYLES
+        keys.delete ['value', 'type']
+        str << "<r><rPr>"
+        keys.each do |key|
+          case key
+          when 'font_name'
+            str << "<rFont val='"<< @font_name << "'/>"
+          when 'color'
+            str << data[key].to_xml_string
+          else
+            "<" << key.to_s << " val='" << data[key].to_s << "'/>"
+          end
+        end
+        str << "</rPr>" << "<t>" << value.to_s << "</t></r>"
+      else
+        str << "<t>" << value.to_s << "</t>"
+      end
+      str
+    end
     # builds an xml text run based on this cells attributes. This is extracted from to_xml so that shared strings can use it.
     # @param [Nokogiri::XML::Builder] xml The document builder instance this output will be added to.
     # @return [String] the xml for this cell's text run
@@ -294,11 +334,39 @@ module Axlsx
     # Serializes the cell
     # @param [Nokogiri::XML::Builder] xml The document builder instance this objects xml will be added to.
     # @return [String] xml text for the cell
+    def to_xml_string(r_index, c_index, str = '')
+      str << '<c r="' << Axlsx::cell_r(c_index, r_index) << '" s="' << @style.to_s << '" '
+      case @type
+      when :string
+        #parse formula
+        if @value.start_with?('=')
+          str  << 't="str"><f>' << value.to_s.gsub('=', '') << '</f>'
+        else
+          #parse shared
+          if @ssti
+            str << 't="s"><v>' << ssti << '</v>'
+          else
+            str << 't="inlineStr"><is>' << run_xml_string << '</is>'
+          end
+        end
+      when :date
+        # TODO: See if this is subject to the same restriction as Time below
+        str << '><v>' << DateTimeConverter::date_to_serial(@value).to_s << '</v>'
+      when :time
+        str << '><v>' << DateTimeConverter::time_to_serial(@value).to_s << '</v>'
+      when :boolean
+        str << 't="b"><v>' << @value.to_s << '</v>'
+      else
+        str << '><v>' << @value.to_s << '</v>'
+      end
+      str << '</c>'
+    end
+
     def to_xml(xml)
       if @type == :string
         #parse formula
         if @value.start_with?('=')
-          xml.c(:r => r, :t=>:str, :s=>style) {
+          xml.c(:r => r, :s=>style, :t=>:str) {
             xml.f @value.to_s.gsub('=', '')
           }
         else
@@ -330,6 +398,14 @@ module Axlsx
 
     private
 
+    # Utility method for setting inline style attributes
+    def set_run_style( validator, attr, value)
+      return unless INLINE_STYLES.include?(attr.to_s)
+      Axlsx.send(validator, value) unless validator == nil
+      self.instance_variable_set :"@#{attr.to_s}", value
+      @is_text_run = true
+    end
+
     # @see ssti
     def ssti=(v)
       Axlsx::validate_unsigned_int(v)
@@ -339,20 +415,6 @@ module Axlsx
     # assigns the owning row for this cell.
     def row=(v) DataTypeValidator.validate "Cell.row", Row, v; @row=v end
 
-    # converts the column index into alphabetical values.
-    # @note This follows the standard spreadsheet convention of naming columns A to Z, followed by AA to AZ etc.
-    # @return [String]
-    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 Date, the type is set to :date
diff --git a/lib/axlsx/workbook/worksheet/page_margins.rb b/lib/axlsx/workbook/worksheet/page_margins.rb
index f41e3426..1c456f2a 100644
--- a/lib/axlsx/workbook/worksheet/page_margins.rb
+++ b/lib/axlsx/workbook/worksheet/page_margins.rb
@@ -12,13 +12,13 @@ module Axlsx
 
     # Default left and right margin (in inches)
     DEFAULT_LEFT_RIGHT = 0.75
-    
+
     # Default top and bottom margins (in inches)
     DEFAULT_TOP_BOTTOM = 1.00
-    
+
     # Default header and footer margins (in inches)
     DEFAULT_HEADER_FOOTER = 0.50
-    
+
     # Left margin (in inches)
     # @return [Float]
     attr_reader :left
@@ -26,23 +26,23 @@ module Axlsx
     # Right margin (in inches)
     # @return [Float]
     attr_reader :right
-    
+
     # Top margin (in inches)
     # @return [Float]
     attr_reader :top
-    
+
     # Bottom margin (in inches)
     # @return [Float]
     attr_reader :bottom
-    
+
     # Header margin (in inches)
     # @return [Float]
     attr_reader :header
-    
+
     # Footer margin (in inches)
     # @return [Float]
     attr_reader :footer
-    
+
     # Creates a new PageMargins object
     # @option options [Numeric] left The left margin in inches
     # @option options [Numeric] right The right margin in inches
@@ -60,7 +60,7 @@ module Axlsx
         self.send("#{o[0]}=", o[1]) if self.respond_to? "#{o[0]}="
       end
     end
-    
+
     # Set some or all margins at once.
     # @param [Hash] margins the margins to set (possible keys are :left, :right, :top, :bottom, :header and :footer).
     def set(margins)
@@ -69,7 +69,7 @@ module Axlsx
         send("#{k}=", v)
       end
     end
-    
+
     # @see left
     def left=(v); Axlsx::validate_unsigned_numeric(v); @left = v end
     # @see right
@@ -83,6 +83,9 @@ module Axlsx
     # @see footer
     def footer=(v); Axlsx::validate_unsigned_numeric(v); @footer = v end
 
+    def to_xml_string
+      "<pageMargins left='%s' right='%s' top='%s' bottom='%s' header='%s' footer='%s'/>" % [left, right, top, bottom, header, footer]
+    end
     # Serializes the page margins element
     # @note For compatibility, this is a noop unless custom margins have been specified.
     # @param [Nokogiri::XML::Builder] xml The document builder instance this objects xml will be added to.
diff --git a/lib/axlsx/workbook/worksheet/row.rb b/lib/axlsx/workbook/worksheet/row.rb
index bb6a92a8..ac251ed8 100644
--- a/lib/axlsx/workbook/worksheet/row.rb
+++ b/lib/axlsx/workbook/worksheet/row.rb
@@ -59,6 +59,17 @@ module Axlsx
       worksheet.rows.index(self)
     end
 
+    def to_xml_string(r_index, str = '')
+      str << '<row r="' << (r_index + 1 ).to_s << '" '
+      if custom_height?
+        str << 'customHeight="1" ht="' << height.to_s << '">'
+      else
+        str << '>'
+      end
+      @cells.each_with_index { |cell, c_index| cell.to_xml_string(r_index, c_index, str) }
+      str << '</row>'
+      str
+    end
     # Serializes the row
     # @param [Nokogiri::XML::Builder] xml The document builder instance this objects xml will be added to.
     # @return [String]
diff --git a/lib/axlsx/workbook/worksheet/worksheet.rb b/lib/axlsx/workbook/worksheet/worksheet.rb
index 5325294f..20c13d8b 100644
--- a/lib/axlsx/workbook/worksheet/worksheet.rb
+++ b/lib/axlsx/workbook/worksheet/worksheet.rb
@@ -168,27 +168,6 @@ module Axlsx
       @fit_to_page = v
     end
 
-    # Returns the cell or cells defined using excel style A1:B3 references.
-    # @param [String|Integer] cell_def the string defining the cell or range of cells, or the rownumber
-    # @return [Cell, Array]
-    def [](cell_def)
-      return rows[cell_def - 1] if cell_def.is_a? Integer
-      parts = cell_def.split(':')
-      first = name_to_cell parts[0]
-
-      if parts.size == 1
-        first
-      else
-        cells = []
-        last = name_to_cell(parts[1])
-        rows[(first.row.index..last.row.index)].each do |r|
-          r.cells[(first.index..last.index)].each do |c|
-            cells << c
-          end
-        end
-        cells
-      end
-    end
 
     # returns the column and row index for a named based cell
     # @param [String] name The cell or cell range to return. "A1" will return the first cell of the first row.
@@ -375,6 +354,7 @@ module Axlsx
       chart
     end
 
+    # needs documentation
     def add_table(ref, options={})
       table = Table.new(ref, self, options)
       @tables << table
@@ -391,14 +371,42 @@ module Axlsx
       image
     end
 
+    def to_xml_string
+      str = "<worksheet xmlns=\"%s\" xmlns:r=\"%s\">" % [XML_NS, XML_NS_R]
+      str.concat "<sheetPr><pageSetUpPr fitToPage=\"%s\"></pageSetUpPr></sheetPr>" % fit_to_page if fit_to_page
+      str.concat "<dimension ref=\"%s\"></dimension>" % dimension unless rows.size == 0
+      str.concat "<sheetViews><sheetView tabSelected='%s' workbookViewId='0' showGridLines='%s'><selection activeCell=\"A1\" sqref=\"A1\"/></sheetView></sheetViews>" % [@selected, show_gridlines]
+
+      if @auto_fit_data.size > 0
+        str.concat "<cols>"
+        @auto_fit_data.each_with_index do |col, index|
+          min_max = index+1
+          str.concat "<col min='%s' max='%s' width='%s' customWidth='1'></col>" % [min_max, min_max, auto_width(col)]
+        end
+        str.concat '</cols>'
+      end
+
+      str.concat '<sheetData>'
+      @rows.each_with_index { |row, index| row.to_xml_string(index, str) }
+      str.concat '</sheetData>'
+      str.concat page_margins.to_xml_string if @page_margins
+      str.concat "<autoFilter ref='%s'></autoFilter>" % @auto_filter if @auto_filter
+      str.concat "<mergeCells count='%s'>%s</mergeCells>" % [@merged_cells.size, @merged_cells.reduce('') { |memo, obj| "<mergeCell ref='%s'></mergeCell>" % obj } ] unless @merged_cells.empty?
+      str.concat "<drawing r:id='rId1'></drawing>" if @drawing
+      unless @tables.empty?
+        str.concat "<tableParts count='%s'>%s</tableParts>" % [@tables.size, @tables.reduce('') { |memo, obj| memo += "<tablePart r:id='%s'/>" % obj.rId }]
+      end
+      str + '</worksheet>'
+    end
+
     # Serializes the worksheet document
     # @return [String]
     def to_xml
       builder = Nokogiri::XML::Builder.new(:encoding => ENCODING) do |xml|
         xml.worksheet(:xmlns => XML_NS,
                       :'xmlns:r' => XML_NS_R) {
-           xml.sheetPr {
-             xml.pageSetUpPr :fitToPage => fit_to_page if fit_to_page
+          xml.sheetPr {
+            xml.pageSetUpPr :fitToPage => fit_to_page if fit_to_page
           }
           # another patch for the folks at rubyXL as thier parser depends on this optional element.
           xml.dimension :ref=>dimension unless rows.size == 0
@@ -450,6 +458,29 @@ module Axlsx
         r
     end
 
+    # Returns the cell or cells defined using excel style A1:B3 references.
+    # @param [String|Integer] cell_def the string defining the cell or range of cells, or the rownumber
+    # @return [Cell, Array]
+
+    def [] (cell_def)
+      return rows[cell_def] if cell_def.is_a?(Integer)
+
+      parts = cell_def.split(':')
+      first = name_to_cell parts[0]
+      if parts.size == 1
+        first
+      else
+        cells = []
+        last = name_to_cell(parts[1])
+        rows[(first.row.index..last.row.index)].each do |r|
+          r.cells[(first.index..last.index)].each do |c|
+            cells << c
+          end
+        end
+        cells
+      end
+    end
+
     private
 
     # assigns the owner workbook for this worksheet
@@ -478,6 +509,7 @@ module Axlsx
         next if width == :ignore || (item.value.is_a?(String) && item.value.start_with?('='))
         # make sure we can turn that fixed with off!
         col[:fixed] = nil if width == :auto
+        next unless self.workbook.use_autowidth
 
         cell_xf = cellXfs[item.style]
         font = fonts[cell_xf.fontId || 0]
diff --git a/test/benchmark.rb b/test/benchmark.rb
new file mode 100644
index 00000000..bb9a7b7d
--- /dev/null
+++ b/test/benchmark.rb
@@ -0,0 +1,81 @@
+#!/usr/bin/env ruby -s
+# -*- coding: utf-8 -*-
+$:.unshift "#{File.dirname(__FILE__)}/../lib"
+require 'axlsx'
+require 'csv'
+
+require 'benchmark'
+row = []
+input = (32..126).to_a.pack('U*').chars.to_a
+20.times { row << input.shuffle.join}
+times = 1000
+Benchmark.bm(100) do |x|
+  # No Autowidth
+  x.report('axlsx_noautowidth') {
+
+    p = Axlsx::Package.new
+    p.use_autowidth = false
+    wb = p.workbook
+
+    #A Simple Workbook
+
+    wb.add_worksheet do |sheet|
+      times.times do
+        sheet << row
+      end
+    end
+    p.serialize("example.xlsx")
+  }
+
+  x.report('axlsx') {
+    p = Axlsx::Package.new
+    wb = p.workbook
+
+    #A Simple Workbook
+
+    wb.add_worksheet do |sheet|
+      times.times do
+        sheet << row
+      end
+    end
+    p.serialize("example.xlsx")
+  }
+
+  x.report('axlsx_shared') {
+    p = Axlsx::Package.new
+    wb = p.workbook
+
+    #A Simple Workbook
+
+    wb.add_worksheet do |sheet|
+      times.times do
+        sheet << row
+      end
+    end
+    p.use_shared_strings = true
+    p.serialize("example.xlsx")
+  }
+
+  x.report('axlsx_stream') {
+    p = Axlsx::Package.new
+    wb = p.workbook
+
+    #A Simple Workbook
+
+    wb.add_worksheet do |sheet|
+      times.times do
+        sheet << row
+      end
+    end
+
+    s = p.to_stream()
+    File.open('example_streamed.xlsx', 'w') { |f| f.write(s.read) }
+  }
+  x.report('csv') {
+    CSV.open("example.csv", "wb") do |csv|
+      times.times do
+        csv << row
+      end
+    end
+  }
+end
diff --git a/test/profile.rb b/test/profile.rb
new file mode 100644
index 00000000..97d2e2bc
--- /dev/null
+++ b/test/profile.rb
@@ -0,0 +1,33 @@
+#!/usr/bin/env ruby -s
+# -*- coding: utf-8 -*-
+
+# Usage:
+# > ruby test/profile.rb
+# > pprof.rb --gif /tmp/axlsx_noautowidth > /tmp/axlsx_noautowidth.gif
+# > open /tmp/axlsx_noautowidth.gif
+
+$:.unshift "#{File.dirname(__FILE__)}/../lib"
+require 'axlsx'
+require 'csv'
+
+# require 'benchmark'
+require 'perftools'
+row = []
+input = (32..126).to_a.pack('U*').chars.to_a
+20.times { row << input.shuffle.join}
+times = 1000
+
+PerfTools::CpuProfiler.start("/tmp/axlsx_noautowidth") do
+  p = Axlsx::Package.new
+  p.use_autowidth = false
+  wb = p.workbook
+  
+  #A Simple Workbook
+  
+  wb.add_worksheet do |sheet|
+    times.times do
+      sheet << row
+    end
+  end
+  p.serialize("example.xlsx")
+end
diff --git a/test/workbook/tc_shared_strings_table.rb b/test/workbook/tc_shared_strings_table.rb
index 259f5970..e3c9bf7b 100644
--- a/test/workbook/tc_shared_strings_table.rb
+++ b/test/workbook/tc_shared_strings_table.rb
@@ -26,7 +26,7 @@ class TestSharedStringsTable < Test::Unit::TestCase
 
   def test_valid_document
     schema = Nokogiri::XML::Schema(File.open(Axlsx::SML_XSD))
-    doc = Nokogiri::XML(@p.workbook.shared_strings.to_xml)
+    doc = Nokogiri::XML(@p.workbook.shared_strings.to_xml_string)
     errors = []
     schema.validate(doc).each do |error|
       puts error.message
diff --git a/test/workbook/worksheet/tc_cell.rb b/test/workbook/worksheet/tc_cell.rb
index 7a33e9b9..499f9209 100644
--- a/test/workbook/worksheet/tc_cell.rb
+++ b/test/workbook/worksheet/tc_cell.rb
@@ -69,7 +69,8 @@ class TestCell < Test::Unit::TestCase
   end
 
   def test_col_ref
-    assert_equal(@c.send(:col_ref), "A")
+    #TODO move to axlsx spec
+    assert_equal(Axlsx.col_ref(0), "A")
   end
 
   def test_cell_type_from_value
@@ -206,13 +207,14 @@ class TestCell < Test::Unit::TestCase
   end
 
   def test_equality
-      c2 = @row.add_cell 1, :type=>:float, :style=>1
-      assert(c2.shareable(@c))
+    c2 = @row.add_cell 1, :type=>:float, :style=>1
+
+    assert_equal(c2.shareable_hash,@c.shareable_hash)
       c3 = @row.add_cell 2, :type=>:float, :style=>1
       c4 = @row.add_cell 1, :type=>:float, :style=>1, :color => "#FFFFFFFF"
-      assert_equal(c4.shareable(c2) ,false)
+      assert_equal(c4.shareable_hash == c2.shareable_hash,false)
       c5 = @row.add_cell 1, :type=>:float, :style=>1, :color => "#FFFFFFFF"
-      assert(c5.shareable(c4))
+      assert_equal(c5.shareable_hash, c4.shareable_hash)
 
   end
 
@@ -222,6 +224,13 @@ class TestCell < Test::Unit::TestCase
     assert_equal(@c.ssti, 1)
   end
 
+  def test_to_xml_string
+    builder = Nokogiri::XML::Builder.new(:encoding => Axlsx::ENCODING) do |xml|
+      @c.to_xml(xml)
+    end
+    c_xml = Nokogiri::XML(builder.to_xml(:save_with => 0))
+    assert_equal(c_xml.xpath("/c[@s=1]").size, 1)
+  end
   def test_to_xml
     # TODO This could use some much more stringent testing related to the xml content generated!
     row = @ws.add_row [Time.now, Date.today, true, 1, 1.0, "text", "=sum(A1:A2)"]
diff --git a/test/workbook/worksheet/tc_row.rb b/test/workbook/worksheet/tc_row.rb
index cabdcde7..47b0f054 100644
--- a/test/workbook/worksheet/tc_row.rb
+++ b/test/workbook/worksheet/tc_row.rb
@@ -60,6 +60,18 @@ class TestRow < Test::Unit::TestCase
     assert_equal(0, doc.xpath(".//row[@customHeight]").size)
   end
 
+  def test_to_xml_string
+    r_s_xml = Nokogiri::XML(@row.to_xml_string(0, ''))
+    assert_equal(r_s_xml.xpath(".//row[@r=1]").size, 1)
+  end
+
+  def test_to_xml_string_with_custom_height
+    @row.add_cell 1
+    @row.height = 20
+    r_s_xml = Nokogiri::XML(@row.to_xml_string(0, ''))
+    assert_equal(r_s_xml.xpath(".//row[@r=1][@ht=20][@customHeight=1]").size, 1)
+  end
+
   def test_to_xml_with_custom_height
     @row.height = 20
     xml = Nokogiri::XML::Builder.new
diff --git a/test/workbook/worksheet/tc_worksheet.rb b/test/workbook/worksheet/tc_worksheet.rb
index 6e1991e6..d9a89a69 100644
--- a/test/workbook/worksheet/tc_worksheet.rb
+++ b/test/workbook/worksheet/tc_worksheet.rb
@@ -83,8 +83,8 @@ class TestWorksheet < Test::Unit::TestCase
     @ws.add_row [1, 2, 3]
     @ws.add_row [4, 5, 6]
     range = @ws["A1:C2"]
-    first_row = @ws[1]
-    last_row = @ws[2]
+    first_row = @ws[0]
+    last_row = @ws[1]
     assert_equal(@ws.rows[0],first_row)
     assert_equal(@ws.rows[1],last_row)
     assert_equal(range.size, 6)
@@ -153,6 +153,87 @@ class TestWorksheet < Test::Unit::TestCase
     assert_equal(@ws.rows[2].cells[1].style, 0)
   end
 
+  def test_to_xml_string_fit_to_page
+    @ws.fit_to_page = true
+    doc = Nokogiri::XML(@ws.to_xml_string)
+    assert_equal(doc.xpath('//xmlns:worksheet/xmlns:sheetPr/xmlns:pageSetUpPr[@fitToPage="true"]').size, 1)
+  end
+
+  def test_to_xml_string_dimensions
+    @ws.add_row [1,2,3]
+    doc = Nokogiri::XML(@ws.to_xml_string)
+    assert_equal(doc.xpath('//xmlns:worksheet/xmlns:dimension[@ref="A1:C1"]').size, 1)
+  end
+
+  def test_to_xml_string_selected
+    @ws.selected = true
+    doc = Nokogiri::XML(@ws.to_xml_string)
+    assert_equal(doc.xpath('//xmlns:worksheet/xmlns:sheetViews/xmlns:sheetView[@tabSelected="true"]').size, 1)
+  end
+
+  def test_to_xml_string_show_gridlines
+    @ws.show_gridlines = false
+    doc = Nokogiri::XML(@ws.to_xml_string)
+    assert_equal(doc.xpath('//xmlns:worksheet/xmlns:sheetViews/xmlns:sheetView[@showGridLines="false"]').size, 1)
+  end
+
+
+  def test_to_xml_string_show_selection
+    doc = Nokogiri::XML(@ws.to_xml_string)
+    assert_equal(doc.xpath('//xmlns:worksheet/xmlns:sheetViews/xmlns:sheetView/xmlns:selection[@activeCell="A1"]').size, 1)
+    assert_equal(doc.xpath('//xmlns:worksheet/xmlns:sheetViews/xmlns:sheetView/xmlns:selection[@sqref="A1"]').size, 1)
+  end
+
+  def test_to_xml_string_auto_fit_data
+    @ws.add_row [1, "two"]
+    doc = Nokogiri::XML(@ws.to_xml_string)
+    assert_equal(doc.xpath('//xmlns:worksheet/xmlns:cols/xmlns:col').size, 2)
+  end
+
+  def test_to_xml_string_sheet_data
+    @ws.add_row [1, "two"]
+    doc = Nokogiri::XML(@ws.to_xml_string)
+    assert_equal(doc.xpath('//xmlns:worksheet/xmlns:sheetData/xmlns:row').size, 1)
+  end
+
+  def test_to_xml_string_auto_filter
+    @ws.add_row [1, "two"]
+    @ws.auto_filter = "A1:B1"
+    doc = Nokogiri::XML(@ws.to_xml_string)
+    assert_equal(doc.xpath('//xmlns:worksheet/xmlns:autoFilter[@ref="A1:B1"]').size, 1)
+  end
+
+  def test_to_xml_string_merge_cells
+    @ws.add_row [1, "two"]
+    @ws.merge_cells "A1:D1"
+    doc = Nokogiri::XML(@ws.to_xml_string)
+    assert_equal(doc.xpath('//xmlns:worksheet/xmlns:mergeCells/xmlns:mergeCell[@ref="A1:D1"]').size, 1)
+  end
+
+  def test_to_xml_string_page_margins
+    @ws.page_margins do |pm|
+      pm.left = 9
+      pm.right = 7
+    end
+    doc = Nokogiri::XML(@ws.to_xml_string)
+    assert_equal(doc.xpath('//xmlns:worksheet/xmlns:pageMargins[@left="9"][@right="7"]').size, 1)
+  end
+
+  def test_to_xml_string_drawing
+    c = @ws.add_chart Axlsx::Pie3DChart
+    doc = Nokogiri::XML(@ws.to_xml_string)
+    assert_equal(doc.xpath('//xmlns:worksheet/xmlns:drawing[@r:id="rId1"]').size, 1)
+  end
+
+  def test_to_xml_string_tables
+    @ws.add_row ["one", "two"]
+    @ws.add_row [1, 2]
+    @ws.add_table "A1:B2"
+    doc = Nokogiri::XML(@ws.to_xml_string)
+    assert_equal(doc.xpath('//xmlns:worksheet/xmlns:tableParts[@count="1"]').size, 1)
+    assert_equal(doc.xpath('//xmlns:worksheet/xmlns:tableParts/xmlns:tablePart[@r:id="rId1"]').size, 1)
+  end
+
   def test_to_xml
     schema = Nokogiri::XML::Schema(File.open(Axlsx::SML_XSD))
     doc = Nokogiri::XML(@ws.to_xml)