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

14 files changed, 121 insertions, 106 deletions

diff --git a/lib/axlsx/workbook/shared_strings_table.rb b/lib/axlsx/workbook/shared_strings_table.rb
index 43e8a1a8..7c08205e 100644
--- a/lib/axlsx/workbook/shared_strings_table.rb
+++ b/lib/axlsx/workbook/shared_strings_table.rb
@@ -26,10 +26,16 @@ module Axlsx
     # @see Cell#sharable
     attr_reader :unique_cells
 
+    # The xml:space attribute
+    # @see Workbook#xml_space
+    attr_reader :xml_space
+
     # Creates a new Shared Strings Table agains an array of cells
     # @param [Array] cells This is an array of all of the cells in the workbook
-    def initialize(cells)
+    # @param [Symbol] xml_space The xml:space behavior for the shared string table.
+    def initialize(cells, xml_space=:preserve)
       @index = 0
+      @xml_space = xml_space
       @unique_cells = {}
       @shared_xml_string = ""
       shareable_cells = cells.flatten.select{ |cell| cell.plain_string? }
@@ -40,8 +46,10 @@ module Axlsx
     # Serializes the object
     # @param [String] str
     # @return [String]
-    def to_xml_string
-      '<?xml version="1.0" encoding="UTF-8"?><sst xmlns="' << XML_NS << '" count="' << @count.to_s << '" uniqueCount="' << unique_count.to_s << '">' << @shared_xml_string << '</sst>'
+    def to_xml_string(str='')
+      str << '<?xml version="1.0" encoding="UTF-8"?><sst xmlns="' << XML_NS << '"'
+      str << ' count="' << @count.to_s << '" uniqueCount="' << unique_count.to_s << '"'
+      str << ' xml:space="' << xml_space.to_s << '">' << @shared_xml_string << '</sst>'
     end
 
     private
diff --git a/lib/axlsx/workbook/workbook.rb b/lib/axlsx/workbook/workbook.rb
index e329ae74..d122f253 100644
--- a/lib/axlsx/workbook/workbook.rb
+++ b/lib/axlsx/workbook/workbook.rb
@@ -270,14 +270,14 @@ require 'axlsx/workbook/worksheet/selection.rb'
     def relationships
       r = Relationships.new
       @worksheets.each do |sheet|
-        r << Relationship.new(WORKSHEET_R, WORKSHEET_PN % (r.size+1))
+        r << Relationship.new(sheet, WORKSHEET_R, WORKSHEET_PN % (r.size+1))
       end
       pivot_tables.each_with_index do |pivot_table, index|
-        r << Relationship.new(PIVOT_TABLE_CACHE_DEFINITION_R, PIVOT_TABLE_CACHE_DEFINITION_PN % (index+1))
+        r << Relationship.new(pivot_table.cache_definition, PIVOT_TABLE_CACHE_DEFINITION_R, PIVOT_TABLE_CACHE_DEFINITION_PN % (index+1))
       end
-      r << Relationship.new(STYLES_R,  STYLES_PN)
+      r << Relationship.new(self, STYLES_R,  STYLES_PN)
       if use_shared_strings
-          r << Relationship.new(SHARED_STRINGS_R,  SHARED_STRINGS_PN)
+          r << Relationship.new(self, SHARED_STRINGS_R,  SHARED_STRINGS_PN)
       end
       r
     end
@@ -285,7 +285,25 @@ require 'axlsx/workbook/worksheet/selection.rb'
     # generates a shared string object against all cells in all worksheets.
     # @return [SharedStringTable]
     def shared_strings
-      SharedStringsTable.new(worksheets.collect { |ws| ws.cells })
+      SharedStringsTable.new(worksheets.collect { |ws| ws.cells }, xml_space)
+    end
+
+    # The xml:space attribute for the worksheet.
+    # This determines how whitespace is handled withing the document.
+    # The most relevant part being whitespace in the cell text.
+    # allowed values are :preserve and :default. Axlsx uses :preserve unless
+    # you explicily set this to :default.
+    # @return Symbol
+    def xml_space
+      @xml_space ||= :preserve
+    end
+
+    # Sets the xml:space attribute for the worksheet
+    # @see Worksheet#xml_space
+    # @param [Symbol] space must be one of :preserve or :default
+    def xml_space=(space)
+      Axlsx::RestrictionValidator.validate(:xml_space, [:preserve, :default], space)
+      @xml_space = space;
     end
 
     # returns a range of cells in a worksheet
@@ -318,9 +336,8 @@ require 'axlsx/workbook/worksheet/selection.rb'
       defined_names.to_xml_string(str)
       unless pivot_tables.empty?
         str << '<pivotCaches>'
-        pivot_tables.each_with_index do |pivot_table, index|
-          rId = "rId#{@worksheets.size + index + 1  }"
-          str << '<pivotCache cacheId="' << pivot_table.cache_definition.cache_id.to_s << '" r:id="' << rId << '"/>'
+        pivot_tables.each do |pivot_table|
+          str << '<pivotCache cacheId="' << pivot_table.cache_definition.cache_id.to_s << '" r:id="' << pivot_table.cache_definition.rId << '"/>'
         end
         str << '</pivotCaches>'
       end
diff --git a/lib/axlsx/workbook/worksheet/comment.rb b/lib/axlsx/workbook/worksheet/comment.rb
index 3e54d2b3..7035f4cf 100644
--- a/lib/axlsx/workbook/worksheet/comment.rb
+++ b/lib/axlsx/workbook/worksheet/comment.rb
@@ -4,35 +4,33 @@ module Axlsx
   class Comment
 
     include Axlsx::OptionsParser
+    include Axlsx::Accessors
 
     # Creates a new comment object
-    # @param [Comments] comments
+    # @param [Comments] comments The comment collection this comment belongs to
     # @param [Hash] options
     # @option [String] author the author of the comment
     # @option [String] text The text for the comment
+    # @option [String] ref The refence (e.g. 'A3' where this comment will be anchored.
+    # @option [Boolean] visible This controls the visiblity of the associated vml_shape.
     def initialize(comments, options={})
       raise ArgumentError, "A comment needs a parent comments object" unless comments.is_a?(Comments)
+      @visible = true
       @comments = comments
       parse_options options
       yield self if block_given?
     end
 
-    # The text to render
-    # @return [String]
-    attr_reader :text
-
-    # The author of this comment
-    # @see Comments
-    # @return [String]
-    attr_reader :author
+    string_attr_accessor :text, :author
+    boolean_attr_accessor :visible
 
-    # The owning Comments object
+      # The owning Comments object
     # @return [Comments]
     attr_reader :comments
 
 
     # The string based cell position reference (e.g. 'A1') that determines the positioning of this comment
-    # @return [String]
+    # @return [String|Cell]
     attr_reader :ref
 
     # TODO
@@ -60,30 +58,20 @@ module Axlsx
       @ref = v.r if v.is_a?(Cell)
     end
 
-    # @see text
-    def text=(v)
-      Axlsx::validate_string(v)
-      @text = v
-    end
-
-    # @see author
-    def author=(v)
-      @author = v
-    end
-
     # serialize the object
     # @param [String] str
     # @return [String]
     def to_xml_string(str = "")
       author = @comments.authors[author_index]
       str << '<comment ref="' << ref << '" authorId="' << author_index.to_s << '">'
-      str << '<text><r>'
-      str << '<rPr> <b/><color indexed="81"/></rPr>'
-      str << '<t>' << author.to_s << ':
-</t></r>'
+      str << '<text>'
+      unless author.to_s == ""
+        str << '<r><rPr><b/><color indexed="81"/></rPr>'
+        str << "<t>" << ::CGI.escapeHTML(author.to_s) << ":\n</t></r>"
+      end
       str << '<r>'
       str << '<rPr><color indexed="81"/></rPr>'
-      str << '<t>' << text << '</t></r></text>'
+      str << '<t>' << ::CGI.escapeHTML(text) << '</t></r></text>'
       str << '</comment>'
     end
 
@@ -93,7 +81,7 @@ module Axlsx
     # by default, all columns are 5 columns wide and 5 rows high
     def initialize_vml_shape
       pos = Axlsx::name_to_indices(ref)
-      @vml_shape = VmlShape.new(:row => pos[1], :column => pos[0]) do |vml|
+      @vml_shape = VmlShape.new(:row => pos[1], :column => pos[0], :visible => @visible) do |vml|
         vml.left_column = vml.column
         vml.right_column = vml.column + 2 
         vml.top_row = vml.row
diff --git a/lib/axlsx/workbook/worksheet/comments.rb b/lib/axlsx/workbook/worksheet/comments.rb
index 25da9de2..03cce339 100644
--- a/lib/axlsx/workbook/worksheet/comments.rb
+++ b/lib/axlsx/workbook/worksheet/comments.rb
@@ -56,9 +56,9 @@ module Axlsx
     # The relationships required by this object
     # @return [Array]
     def relationships
-      [Relationship.new(VML_DRAWING_R, "../#{vml_drawing.pn}"),
-       Relationship.new(COMMENT_R, "../#{pn}"),
-       Relationship.new(COMMENT_R_NULL, "NULL")]
+      [Relationship.new(self, VML_DRAWING_R, "../#{vml_drawing.pn}"),
+       Relationship.new(self, COMMENT_R, "../#{pn}"),
+       Relationship.new(self, COMMENT_R_NULL, "NULL")]
     end
 
     # serialize the object
diff --git a/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb b/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb
index a0ce6a41..916b31c2 100644
--- a/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb
+++ b/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb
@@ -25,7 +25,7 @@ module Axlsx
     # @option options [Integer] stdDev The number of standard deviations above or below the average to match
     # @option options [Boolean] stopIfTrue Stop evaluating rules after this rule matches
     # @option options [Symbol]  timePeriod The time period in a date occuring... rule
-    # @option options [String] formula The formula to match against in i.e. an equal rule
+    # @option options [String] formula The formula to match against in i.e. an equal rule. Use a [minimum, maximum] array for cellIs between/notBetween conditionals.
     def initialize(options={})
       @color_scale = @data_bar = @icon_set = @formula = nil
       parse_options options
@@ -36,6 +36,8 @@ module Axlsx
                             :stopIfTrue, :timePeriod
 
     # Formula
+    # The formula or value to match against (e.g. 5 with an operator of :greaterThan to specify cell_value > 5).
+    # If the operator is :between or :notBetween, use an array to specify [minimum, maximum]
     # @return [String]
     attr_reader :formula
 
@@ -180,7 +182,7 @@ module Axlsx
     # @see timePeriod
     def timePeriod=(v); Axlsx::validate_time_period_type(v); @timePeriod = v end
     # @see formula
-    def formula=(v); Axlsx::validate_string(v); @formula = v end
+    def formula=(v); [*v].each {|x| Axlsx::validate_string(x) }; @formula = [*v].map { |form| ::CGI.escapeHTML(form) } end
 
     # @see color_scale
     def color_scale=(v)
@@ -208,7 +210,7 @@ module Axlsx
       str << '<cfRule '
       serialized_attributes str
       str << '>'
-      str << '<formula>' << self.formula << '</formula>' if @formula
+      str << '<formula>' << [*self.formula].join('</formula><formula>') << '</formula>' if @formula
       @color_scale.to_xml_string(str) if @color_scale && @type == :colorScale
       @data_bar.to_xml_string(str) if @data_bar && @type == :dataBar
       @icon_set.to_xml_string(str) if @icon_set && @type == :iconSet
diff --git a/lib/axlsx/workbook/worksheet/pivot_table.rb b/lib/axlsx/workbook/worksheet/pivot_table.rb
index 94edf80e..0de22f8f 100644
--- a/lib/axlsx/workbook/worksheet/pivot_table.rb
+++ b/lib/axlsx/workbook/worksheet/pivot_table.rb
@@ -95,10 +95,17 @@ module Axlsx
     # (see #data)
     def data=(v)
       DataTypeValidator.validate "#{self.class}.data", [Array], v
-      v.each do |ref|
-        DataTypeValidator.validate "#{self.class}.data[]", [String], ref
+      @data = []
+      v.each do |data_field|
+        if data_field.is_a? String
+          data_field = {:ref => data_field}
+        end
+        data_field.values.each do |value|
+          DataTypeValidator.validate "#{self.class}.data[]", [String], value
+        end
+        @data << data_field
       end
-      @data = v
+      @data
     end
 
     # The pages
@@ -138,20 +145,14 @@ module Axlsx
       @cache_definition ||= PivotTableCacheDefinition.new(self)
     end
 
-    # The worksheet relationships. This is managed automatically by the worksheet
+    # The relationships for this pivot table.
     # @return [Relationships]
     def relationships
       r = Relationships.new
-      r << Relationship.new(PIVOT_TABLE_CACHE_DEFINITION_R, "../#{cache_definition.pn}")
+      r << Relationship.new(cache_definition, PIVOT_TABLE_CACHE_DEFINITION_R, "../#{cache_definition.pn}")
       r
     end
 
-    # The relation reference id for this table
-    # @return [String]
-    def rId
-      "rId#{index+1}"
-    end
-
     # Serializes the object
     # @param [String] str
     # @return [String]
@@ -196,11 +197,11 @@ module Axlsx
         str << '</pageFields>'
       end
       unless data.empty?
-        str << '<dataFields count="' << data.size.to_s << '">'
+        str << "<dataFields count=\"#{data.size}\">"
         data.each do |datum_value|
-          str << '<dataField name="Sum of ' << datum_value << '" ' <<
-                            'fld="' << header_index_of(datum_value).to_s << '" ' <<
-                            'baseField="0" baseItem="0"/>'
+          str << "<dataField name='#{@subtotal} of #{datum_value[:ref]}' fld='#{header_index_of(datum_value[:ref])}' baseField='0' baseItem='0'"
+          str << " subtotal='#{datum_value[:subtotal]}' " if datum_value[:subtotal]
+          str << "/>"
         end
         str << '</dataFields>'
       end
diff --git a/lib/axlsx/workbook/worksheet/pivot_table_cache_definition.rb b/lib/axlsx/workbook/worksheet/pivot_table_cache_definition.rb
index 37f46c51..665384f4 100644
--- a/lib/axlsx/workbook/worksheet/pivot_table_cache_definition.rb
+++ b/lib/axlsx/workbook/worksheet/pivot_table_cache_definition.rb
@@ -35,10 +35,11 @@ module Axlsx
       index + 1
     end
 
-    # The relation reference id for this table
+    # The relationship id for this pivot table cache definition.
+    # @see Relationship#Id
     # @return [String]
     def rId
-      "rId#{index + 1}"
+      pivot_table.relationships.for(self).Id
     end
 
     # Serializes the object
diff --git a/lib/axlsx/workbook/worksheet/pivot_tables.rb b/lib/axlsx/workbook/worksheet/pivot_tables.rb
index f5625fc0..912d9f41 100644
--- a/lib/axlsx/workbook/worksheet/pivot_tables.rb
+++ b/lib/axlsx/workbook/worksheet/pivot_tables.rb
@@ -17,7 +17,7 @@ module Axlsx
     # returns the relationships required by this collection
     def relationships
       return [] if empty?
-      map{ |pivot_table| Relationship.new(PIVOT_TABLE_R, "../#{pivot_table.pn}") }
+      map{ |pivot_table| Relationship.new(pivot_table, PIVOT_TABLE_R, "../#{pivot_table.pn}") }
     end
   end
 
diff --git a/lib/axlsx/workbook/worksheet/table.rb b/lib/axlsx/workbook/worksheet/table.rb
index dce1a40e..94474c57 100644
--- a/lib/axlsx/workbook/worksheet/table.rb
+++ b/lib/axlsx/workbook/worksheet/table.rb
@@ -47,10 +47,11 @@ module Axlsx
       "#{TABLE_PN % (index+1)}"
     end
 
-    # The relation reference id for this table
+    # The relationship id for this table.
+    # @see Relationship#Id
     # @return [String]
     def rId
-      "rId#{index+1}"
+      @sheet.relationships.for(self).Id
     end
 
     # The name of the Table.
diff --git a/lib/axlsx/workbook/worksheet/tables.rb b/lib/axlsx/workbook/worksheet/tables.rb
index 2d9a1f3c..814f995f 100644
--- a/lib/axlsx/workbook/worksheet/tables.rb
+++ b/lib/axlsx/workbook/worksheet/tables.rb
@@ -17,7 +17,7 @@ module Axlsx
     # returns the relationships required by this collection
     def relationships
       return [] if empty?
-      map{ |table| Relationship.new(TABLE_R, "../#{table.pn}") }
+      map{ |table| Relationship.new(table, TABLE_R, "../#{table.pn}") }
     end
 
     def to_xml_string(str = "")
diff --git a/lib/axlsx/workbook/worksheet/worksheet.rb b/lib/axlsx/workbook/worksheet/worksheet.rb
index 5f650263..ecba9254 100644
--- a/lib/axlsx/workbook/worksheet/worksheet.rb
+++ b/lib/axlsx/workbook/worksheet/worksheet.rb
@@ -114,14 +114,19 @@ module Axlsx
       @rows ||=  SimpleTypedList.new Row
     end
 
-    # returns the sheet data as columnw
-    def cols
-      @rows.transpose
+    # returns the sheet data as columns
+    # If you pass a block, it will be evaluated whenever a row does not have a
+    # cell at a specific index. The block will be called with the row and column
+    # index in the missing cell was found.
+    # @example
+    #     cols { |row_index, column_index| p "warn - row #{row_index} is does not have a cell at #{column_index}
+    def cols(&block)
+      @rows.transpose(&block)
     end
 
-    # An range that excel will apply an autfilter to "A1:B3"
+    # An range that excel will apply an auto-filter to "A1:B3"
     # This will turn filtering on for the cells in the range.
-    # The first row is considered the header, while subsequent rows are considerd to be data.
+    # The first row is considered the header, while subsequent rows are considered to be data.
     # @return String
     def auto_filter
       @auto_filter ||= AutoFilter.new self
@@ -327,6 +332,10 @@ module Axlsx
       auto_filter.range = v
     end
 
+    # Accessor for controlling whether leading and trailing spaces in cells are
+    # preserved or ignored. The default is to preserve spaces.
+    attr_accessor :preserve_spaces
+
     # The part name of this worksheet
     # @return [String]
     def pn
@@ -339,10 +348,11 @@ module Axlsx
       "#{WORKSHEET_RELS_PN % (index+1)}"
     end
 
-    # The relationship Id of thiw worksheet
+    # The relationship id of this worksheet.
     # @return [String]
+    # @see Relationship#Id
     def rId
-      "rId#{index+1}"
+      @workbook.relationships.for(self).Id
     end
 
     # The index of this worksheet in the owning Workbook's worksheets list.
@@ -565,14 +575,6 @@ module Axlsx
       r
     end
 
-    # identifies the index of an object withing the collections used in generating relationships for the worksheet
-    # @param [Any] object the object to search for
-    # @return [Integer] The index of the object
-    def relationships_index_of(object)
-      objects = [tables.to_a, worksheet_comments.comments.to_a, hyperlinks.to_a, worksheet_drawing.drawing].flatten.compact || []
-      objects.index(object)
-    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]
@@ -629,6 +631,11 @@ module Axlsx
     end
 
     private
+
+    def xml_space
+      workbook.xml_space
+    end
+
     def outline(collection, range, level = 1, collapsed = true)
        range.each do |index|
         unless (item = collection[index]).nil?
@@ -699,7 +706,7 @@ module Axlsx
     # Helper method for parsingout the root node for worksheet
     # @return [String]
     def worksheet_node
-      "<worksheet xmlns=\"%s\" xmlns:r=\"%s\">" % [XML_NS, XML_NS_R]
+       "<worksheet xmlns=\"%s\" xmlns:r=\"%s\" xml:space=\"#{xml_space}\">" % [XML_NS, XML_NS_R]
     end
 
     def sheet_data
diff --git a/lib/axlsx/workbook/worksheet/worksheet_comments.rb b/lib/axlsx/workbook/worksheet/worksheet_comments.rb
index 8c700aa0..f9e4c8cd 100644
--- a/lib/axlsx/workbook/worksheet/worksheet_comments.rb
+++ b/lib/axlsx/workbook/worksheet/worksheet_comments.rb
@@ -40,10 +40,11 @@ module Axlsx
       !comments.empty?
     end
 
-    # The index in the worksheet's relationships for the VML drawing that will render the comments
-    # @return [Integer]
-    def index
-      worksheet.relationships.index { |r| r.Type == VML_DRAWING_R } + 1
+    # The relationship id of the VML drawing that will render the comments.
+    # @see Relationship#Id
+    # @return [String]
+    def drawing_rId
+      comments.relationships.find{ |r| r.Type == VML_DRAWING_R }.Id
     end
 
     # Seraalize the object
@@ -51,7 +52,7 @@ module Axlsx
     # @return [String]
     def to_xml_string(str = '')
       return unless has_comments?
-      str << "<legacyDrawing r:id='rId#{index}' />"
+      str << "<legacyDrawing r:id='#{drawing_rId}' />"
     end
   end
 end
diff --git a/lib/axlsx/workbook/worksheet/worksheet_drawing.rb b/lib/axlsx/workbook/worksheet/worksheet_drawing.rb
index 9deeeec3..08cad1f7 100644
--- a/lib/axlsx/workbook/worksheet/worksheet_drawing.rb
+++ b/lib/axlsx/workbook/worksheet/worksheet_drawing.rb
@@ -41,24 +41,18 @@ module Axlsx
       @drawing.is_a? Drawing
     end
 
-    # The relationship required by this object
+    # The relationship instance for this drawing.
     # @return [Relationship]
     def relationship
       return unless has_drawing?
-      Relationship.new(DRAWING_R, "../#{drawing.pn}") 
-    end
-
-    # returns the index of the worksheet releationship that defines this drawing.
-    # @return [Integer]
-    def index
-       worksheet.relationships.index{ |r| r.Type == DRAWING_R } +1 
+      Relationship.new(self, DRAWING_R, "../#{drawing.pn}") 
     end
 
     # Serialize the drawing for the worksheet
     # @param [String] str
     def to_xml_string(str = '')
       return unless has_drawing? 
-      str << "<drawing r:id='rId#{index}'/>"
+      str << "<drawing r:id='#{relationship.Id}'/>"
     end
   end
 end
diff --git a/lib/axlsx/workbook/worksheet/worksheet_hyperlink.rb b/lib/axlsx/workbook/worksheet/worksheet_hyperlink.rb
index d352ec90..2a241287 100644
--- a/lib/axlsx/workbook/worksheet/worksheet_hyperlink.rb
+++ b/lib/axlsx/workbook/worksheet/worksheet_hyperlink.rb
@@ -45,18 +45,13 @@ module Axlsx
       @ref = cell_reference
     end
 
-       # The relationship required by this hyperlink when the taget is :external
+    # The relationship instance for this hyperlink.
+    # A relationship is only required if `@target` is `:external`. If not, this method will simply return `nil`.
+    # @see #target=
     # @return [Relationship]
     def relationship
       return unless @target == :external
-      Relationship.new HYPERLINK_R, location, :target_mode => :External
-    end
-
-    # The id of the relationship for this object
-    # @return [String]
-    def id
-      return unless @target == :external
-      "rId#{(@worksheet.relationships_index_of(self)+1)}"
+      Relationship.new(self, HYPERLINK_R, location, :target_mode => :External)
     end
 
     # Seralize the object
@@ -73,7 +68,7 @@ module Axlsx
     # r:id should only be specified for external targets.
     # @return [Hash]
     def location_or_id
-      @target == :external ?  { :"r:id" => id } : { :location => Axlsx::coder.encode(location) }
+      @target == :external ?  { :"r:id" => relationship.Id } : { :location => Axlsx::coder.encode(location) }
     end
   end
 end