Quick and Dirty run on trying interpolated strings instead of nokogiri for sheet generation.

10 files changed, 298 insertions, 36 deletions

diff --git a/lib/axlsx.rb b/lib/axlsx.rb
index eb5068b5..cf3beb87 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'
 
diff --git a/lib/axlsx/package.rb b/lib/axlsx/package.rb
index 73e72493..505f8cc9 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|
@@ -201,7 +201,7 @@ module Axlsx
 
       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/workbook/worksheet/cell.rb b/lib/axlsx/workbook/worksheet/cell.rb
index ff759cdf..5e59aa40 100644
--- a/lib/axlsx/workbook/worksheet/cell.rb
+++ b/lib/axlsx/workbook/worksheet/cell.rb
@@ -259,6 +259,15 @@ module Axlsx
       self.row.worksheet.merge_cells "#{self.r}:#{range_end}" unless range_end.nil?
     end
 
+
+    def run_xml_string
+      #if (self.instance_values.keys & INLINE_STYLES).size > 0
+      #  str = "<r><rPr></rPr><r>"
+      #else
+      #  "<t>%s</t>" % value.to_s
+      #end
+      "<t>%s</t>" % value.to_s
+    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 +303,42 @@ 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
+
+    FORMULA = "<c r=\"%s\" t=\"str\" s=\"%i\"><f>%s</f></c>"
+    SHARED_STRING = "<c r=\"%s\" t=\"s\" s=\"%i\"><v>%i</v></c>"
+    INLINE_STRING = "<c r=\"%s\" t=\"inlineStr\" s=\"%i\"><is>%s</is></c>"
+    OTHER = "<c r=\"%s\" s=\"%i\"><v>%s</v></c>"
+    BOOLEAN = "<c r=\"%s\" t=\"b\" s=\"%i\"><v>%s</v></c>"
+    def to_xml_string
+      if @type == :string
+        #parse formula
+        if @value.start_with?('=')
+          FORMULA % [r, style, value.to_s.gsub('=', '')]
+        else
+          #parse shared
+          if @ssti
+            SHARED_STRING % [r, style, ssti]
+          else
+            INLINE_STRING % [r, style, run_xml_string]
+          end
+        end
+      elsif @type == :date
+        # TODO: See if this is subject to the same restriction as Time below
+        OTHER % [r, style, DateTimeConverter::date_to_serial(@value)]
+      elsif @type == :time
+        OTHER % [r, style, DateTimeConverter::time_to_serial(@value)]
+      elsif @type == :boolean
+        BOOLEAN % [r, style, value]
+      else
+        OTHER % [r, style, value]
+      end
+    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
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..db7a56e5 100644
--- a/lib/axlsx/workbook/worksheet/row.rb
+++ b/lib/axlsx/workbook/worksheet/row.rb
@@ -59,6 +59,13 @@ module Axlsx
       worksheet.rows.index(self)
     end
 
+    def to_xml_string
+      if custom_height?
+        "<row r=\"%s\" customHeight=\"1\" ht=\"%s\">%s</row>" % [index+1, height, @cells.inject("") { |memo, obj| obj.to_xml_string }]
+      else
+        "<row r=\"%s\">%s</row>" % [index+1, @cells.inject("") { |memo, obj| memo.concat obj.to_xml_string }]
+      end
+    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..1d128235 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,40 @@ 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>%s</sheetData>" % [@rows.reduce('') { |memo, obj| memo += obj.to_xml_string }]
+      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
+    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 +456,28 @@ 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 - 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
+
     private
 
     # assigns the owner workbook for this worksheet
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/workbook/worksheet/tc_cell.rb b/test/workbook/worksheet/tc_cell.rb
index 7a33e9b9..fb3814ae 100644
--- a/test/workbook/worksheet/tc_cell.rb
+++ b/test/workbook/worksheet/tc_cell.rb
@@ -222,6 +222,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.to_xml_string, c_xml.xpath("/c").to_xml(:save_with => 0))
+  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..b77715c5 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)
+    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)
+    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..9f8a06e1 100644
--- a/test/workbook/worksheet/tc_worksheet.rb
+++ b/test/workbook/worksheet/tc_worksheet.rb
@@ -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)