diff options
| -rw-r--r-- | lib/axlsx.rb | 3 | ||||
| -rw-r--r-- | lib/axlsx/package.rb | 4 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/cell.rb | 42 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/page_margins.rb | 23 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/row.rb | 7 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/worksheet.rb | 74 | ||||
| -rw-r--r-- | test/benchmark.rb | 81 | ||||
| -rw-r--r-- | test/workbook/worksheet/tc_cell.rb | 7 | ||||
| -rw-r--r-- | test/workbook/worksheet/tc_row.rb | 12 | ||||
| -rw-r--r-- | test/workbook/worksheet/tc_worksheet.rb | 81 |
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) |