worksheet comments step 2. Needs docs and specs - but it works in Excel

9 files changed, 195 insertions, 16 deletions

diff --git a/lib/axlsx/drawing/drawing.rb b/lib/axlsx/drawing/drawing.rb
index dfdd0075..943c0f2d 100644
--- a/lib/axlsx/drawing/drawing.rb
+++ b/lib/axlsx/drawing/drawing.rb
@@ -39,6 +39,9 @@ module Axlsx
   require 'axlsx/drawing/pic.rb'
   require 'axlsx/drawing/hyperlink.rb'
 
+  require 'axlsx/drawing/vml_drawing.rb'
+  require 'axlsx/drawing/vml_shape.rb'
+
   # A Drawing is a canvas for charts. Each worksheet has a single drawing that manages anchors.
   # The anchors reference the charts via graphical frames. This is not a trivial relationship so please do follow the advice in the note.
   # @note The recommended way to manage drawings is to use the Worksheet.add_chart method.
diff --git a/lib/axlsx/drawing/vml_drawing.rb b/lib/axlsx/drawing/vml_drawing.rb
new file mode 100644
index 00000000..8ff6469b
--- /dev/null
+++ b/lib/axlsx/drawing/vml_drawing.rb
@@ -0,0 +1,34 @@
+module Axlsx
+
+  class VmlDrawing
+
+    def initialize(comments)
+      raise ArgumentError, "you must provide a comments object" unless comments.is_a?(Comments)
+      @comments = comments
+    end
+
+    def pn
+      "#{VML_DRAWING_PN}" % (@comments.worksheet.index + 1)
+    end
+
+    def to_xml_string(str = '')
+      str = <<BAD_PROGRAMMER
+<xml xmlns:v="urn:schemas-microsoft-com:vml"
+ xmlns:o="urn:schemas-microsoft-com:office:office"
+ xmlns:x="urn:schemas-microsoft-com:office:excel">
+ <o:shapelayout v:ext="edit">
+  <o:idmap v:ext="edit" data="#{@comments.worksheet.index+1}"/>
+ </o:shapelayout>
+ <v:shapetype id="_x0000_t202" coordsize="21600,21600" o:spt="202"
+  path="m0,0l0,21600,21600,21600,21600,0xe">
+  <v:stroke joinstyle="miter"/>
+  <v:path gradientshapeok="t" o:connecttype="rect"/>
+ </v:shapetype>
+BAD_PROGRAMMER
+      @comments.comment_list.each { |comment| comment.vml_shape.to_xml_string str }
+      str << "</xml>"
+
+    end
+
+  end
+end
diff --git a/lib/axlsx/drawing/vml_shape.rb b/lib/axlsx/drawing/vml_shape.rb
new file mode 100644
index 00000000..d6de8b21
--- /dev/null
+++ b/lib/axlsx/drawing/vml_shape.rb
@@ -0,0 +1,61 @@
+module Axlsx
+
+  class VmlShape
+
+    attr_accessor :row
+
+    attr_accessor :column
+
+    attr_accessor :left_column
+    attr_accessor :left_offset
+    attr_accessor :top_row
+    attr_accessor :top_offset
+    attr_accessor :right_column
+    attr_accessor :right_offset
+    attr_accessor :bottom_row
+    attr_accessor :bottom_offset
+    attr_reader   :id
+
+    def initialize(comment, options={})
+      @id = "_x0000_s#{comment.comments.worksheet.index+1}08#{comment.index+1}"
+      @row = @column = @left_column = @top_row = @right_column = @bottom_row = 0
+      @left_offset = 15
+      @top_offset = 2
+      @right_offset = 50
+      @bottom_offset = 5
+      options.each do |o|
+        self.send("#{o[0]}=", o[1]) if self.respond_to? "#{o[0]}="
+      end
+      yield self if block_given?
+    end
+
+    def to_xml_string(str ='')
+str << <<SHAME_ON_YOU
+
+<v:shape id="#{id}" type="#_x0000_t202"
+style='position:absolute;margin-left:104pt;margin-top:2pt;width:800px;height:27pt;z-index:1;mso-wrap-style:tight'
+ fillcolor="#ffffa1 [80]" o:insetmode="auto">
+
+  <v:fill color2="#ffffa1 [80]"/>
+  <v:shadow on="t" obscured="t"/>
+  <v:path o:connecttype="none"/>
+
+  <v:textbox style='mso-fit-text-with-word-wrap:t'>
+   <div style='text-align:left'></div>
+  </v:textbox>
+
+  <x:ClientData ObjectType="Note">
+   <x:MoveWithCells/>
+   <x:SizeWithCells/>
+   <x:Anchor>#{left_column}, #{left_offset}, #{top_row}, #{top_offset}, #{right_column}, #{right_offset}, #{bottom_row}, #{bottom_offset}</x:Anchor>
+   <x:AutoFill>False</x:AutoFill>
+   <x:Row>#{row}</x:Row>
+   <x:Column>#{column}</x:Column>
+   <x:Visible/>
+  </x:ClientData>
+ </v:shape>
+SHAME_ON_YOU
+
+    end
+  end
+end
diff --git a/lib/axlsx/package.rb b/lib/axlsx/package.rb
index 3ab7b99a..75f574d4 100644
--- a/lib/axlsx/package.rb
+++ b/lib/axlsx/package.rb
@@ -180,15 +180,24 @@ module Axlsx
        {:entry => CONTENT_TYPES_PN, :doc => content_types.to_xml_string, :schema => CONTENT_TYPES_XSD},
        {:entry => WORKBOOK_PN, :doc => workbook.to_xml_string, :schema => SML_XSD}
       ]
+
       workbook.drawings.each do |drawing|
         @parts << {:entry => "xl/#{drawing.rels_pn}", :doc => drawing.relationships.to_xml_string, :schema => RELS_XSD}
         @parts << {:entry => "xl/#{drawing.pn}", :doc => drawing.to_xml_string, :schema => DRAWING_XSD}
       end
 
+
       workbook.tables.each do |table|
         @parts << {:entry => "xl/#{table.pn}", :doc => table.to_xml_string, :schema => SML_XSD}
       end
 
+      workbook.comments.each do|comment|
+        if comment.comment_list.size > 0
+          @parts << { :entry => "xl/#{comment.pn}", :doc => comment.to_xml_string, :schema => SML_XSD }
+          @parts << { :entry => "xl/#{comment.vml_drawing.pn}", :doc => comment.vml_drawing.to_xml_string, :schema => nil }
+        end
+      end
+
       workbook.charts.each do |chart|
         @parts << {:entry => "xl/#{chart.pn}", :doc => chart.to_xml_string, :schema => DRAWING_XSD}
       end
@@ -229,18 +238,33 @@ module Axlsx
     # @private
     def content_types
       c_types = base_content_types
+
       workbook.drawings.each do |drawing|
         c_types << Axlsx::Override.new(:PartName => "/xl/#{drawing.pn}",
                                        :ContentType => DRAWING_CT)
       end
+
       workbook.charts.each do |chart|
         c_types << Axlsx::Override.new(:PartName => "/xl/#{chart.pn}",
                                        :ContentType => CHART_CT)
       end
+
       workbook.tables.each do |table|
         c_types << Axlsx::Override.new(:PartName => "/xl/#{table.pn}",
                                        :ContentType => TABLE_CT)
       end
+
+      workbook.comments.each do |comment|
+        if comment.comment_list.size > 0
+        c_types << Axlsx::Override.new(:PartName => "/xl/#{comment.pn}",
+                                       :ContentType => COMMENT_CT)
+        end
+      end
+
+      if workbook.comments.size > 0
+        c_types << Axlsx::Default.new(:Extension => "vml", :ContentType => VML_DRAWING_CT)
+      end
+
       workbook.worksheets.each do |sheet|
         c_types << Axlsx::Override.new(:PartName => "/xl/#{sheet.pn}",
                                          :ContentType => WORKSHEET_CT)
diff --git a/lib/axlsx/util/constants.rb b/lib/axlsx/util/constants.rb
index 985c6e53..9ff0a119 100644
--- a/lib/axlsx/util/constants.rb
+++ b/lib/axlsx/util/constants.rb
@@ -80,7 +80,16 @@ module Axlsx
   HYPERLINK_R = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink"
 
   # comment rels namespace
-  COMMENT_R = "http://purl.oclc.org/ooxml/officeDocument/relationships/comments"
+  COMMENT_R = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments"
+  COMMENT_R_NULL = "http://purl.oclc.org/ooxml/officeDocument/relationships/comments"
+
+  #vml drawing relation namespace
+  VML_DRAWING_R = 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing'
+
+
+
+  # VML Drawing content type
+  VML_DRAWING_CT = "application/vnd.openxmlformats-officedocument.vmlDrawing"
 
   # table content type
   TABLE_CT = "application/vnd.openxmlformats-officedocument.spreadsheetml.table+xml"
@@ -112,6 +121,9 @@ module Axlsx
   # chart content type
   CHART_CT = "application/vnd.openxmlformats-officedocument.drawingml.chart+xml"
 
+  # comments content type
+  COMMENT_CT = "application/vnd.openxmlformats-officedocument.spreadsheetml.comments+xml"
+
   # jpeg content type
   JPEG_CT = "image/jpeg"
 
@@ -175,6 +187,9 @@ module Axlsx
   # drawing rels part
   DRAWING_RELS_PN = "drawings/_rels/drawing%d.xml.rels"
 
+  # vml drawing part
+  VML_DRAWING_PN = "drawings/vmlDrawing%d.vml"
+
   # drawing part
   TABLE_PN = "tables/table%d.xml"
 
@@ -185,7 +200,7 @@ module Axlsx
   IMAGE_PN = "media/image%d.%s"
 
   # comment part
-  COMMENT_PN = "xl/comments%d.xml"
+  COMMENT_PN = "comments%d.xml"
 
   # location of schema files for validation
   SCHEMA_BASE = File.dirname(__FILE__)+'/../../schema/'
diff --git a/lib/axlsx/util/validators.rb b/lib/axlsx/util/validators.rb
index 1ee0d75e..4387aae5 100644
--- a/lib/axlsx/util/validators.rb
+++ b/lib/axlsx/util/validators.rb
@@ -185,17 +185,17 @@ module Axlsx
   end
 
   # Requires that the value is a valid content_type
-  # TABLE_CT, WORKBOOK_CT, APP_CT, RELS_CT, STYLES_CT, XML_CT, WORKSHEET_CT, SHARED_STRINGS_CT, CORE_CT, CHART_CT, DRAWING_CT are allowed
+  # TABLE_CT, WORKBOOK_CT, APP_CT, RELS_CT, STYLES_CT, XML_CT, WORKSHEET_CT, SHARED_STRINGS_CT, CORE_CT, CHART_CT, DRAWING_CT, COMMENT_CT are allowed
   # @param [Any] v The value validated
   def self.validate_content_type(v)
-    RestrictionValidator.validate :content_type, [TABLE_CT, WORKBOOK_CT, APP_CT, RELS_CT, STYLES_CT, XML_CT, WORKSHEET_CT, SHARED_STRINGS_CT, CORE_CT, CHART_CT, JPEG_CT, GIF_CT, PNG_CT, DRAWING_CT], v
+    RestrictionValidator.validate :content_type, [TABLE_CT, WORKBOOK_CT, APP_CT, RELS_CT, STYLES_CT, XML_CT, WORKSHEET_CT, SHARED_STRINGS_CT, CORE_CT, CHART_CT, JPEG_CT, GIF_CT, PNG_CT, DRAWING_CT, COMMENT_CT, VML_DRAWING_CT], v
   end
 
   # Requires that the value is a valid relationship_type
   # XML_NS_R, TABLE_R, WORKBOOK_R, WORKSHEET_R, APP_R, RELS_R, CORE_R, STYLES_R, CHART_R, DRAWING_R, IMAGE_R, HYPERLINK_R, SHARED_STRINGS_R are allowed
   # @param [Any] v The value validated
   def self.validate_relationship_type(v)
-    RestrictionValidator.validate :relationship_type, [XML_NS_R, TABLE_R, WORKBOOK_R, WORKSHEET_R, APP_R, RELS_R, CORE_R, STYLES_R, CHART_R, DRAWING_R, IMAGE_R, HYPERLINK_R, SHARED_STRINGS_R, COMMENT_R], v
+    RestrictionValidator.validate :relationship_type, [XML_NS_R, TABLE_R, WORKBOOK_R, WORKSHEET_R, APP_R, RELS_R, CORE_R, STYLES_R, CHART_R, DRAWING_R, IMAGE_R, HYPERLINK_R, SHARED_STRINGS_R, COMMENT_R, VML_DRAWING_R, COMMENT_R_NULL], v
   end
 
   # Requires that the value is a valid table element type
diff --git a/lib/axlsx/workbook/workbook.rb b/lib/axlsx/workbook/workbook.rb
index 38336af3..daf522ea 100644
--- a/lib/axlsx/workbook/workbook.rb
+++ b/lib/axlsx/workbook/workbook.rb
@@ -100,9 +100,10 @@ require 'axlsx/workbook/worksheet/table.rb'
     # @note The recommended way to manage comments is Worksheet#add_comment
     # @see Worksheet#add_comment
     # @see Comment
-    # @return [SimpleTypedList]
-    attr_reader :comments
-
+    # @return [Comments]
+    def comments
+      self.worksheets.map { |ws| ws.comments }.compact
+    end
 
     # The styles associated with this workbook
     # @note The recommended way to manage styles is Styles#add_style
diff --git a/lib/axlsx/workbook/worksheet/comments.rb b/lib/axlsx/workbook/worksheet/comments.rb
index 310adb8f..90d3ec43 100644
--- a/lib/axlsx/workbook/worksheet/comments.rb
+++ b/lib/axlsx/workbook/worksheet/comments.rb
@@ -1,3 +1,4 @@
+# -*- coding: utf-8 -*-
 module Axlsx
 
   class Comments
@@ -10,20 +11,34 @@ module Axlsx
     # @return [SimpleTypedList]
     attr_reader :comment_list
 
+    attr_reader :vml_drawing
 
     # The worksheet that these comments belong to
     # @return [Worksheet]
     attr_reader :worksheet
 
+    def index
+      @worksheet.index
+    end
+
+    def pn
+      "#{COMMENT_PN % (index+1)}"
+    end
+
     # Creates a new Comments object
     # @param [Worksheet] worksheet The sheet that these comments belong to.
     def initialize(worksheet)
       raise ArgumentError, "you must provide a worksheet" unless worksheet.is_a?(Worksheet)
+
       @worksheet = worksheet
       @authors = SimpleTypedList.new String
       @comment_list = SimpleTypedList.new Comment
+      @vml_drawing = VmlDrawing.new(self)
     end
 
+    # LeftColumn, LeftOffset, TopRow, TopOffset, RightColumn, RightOffset, BottomRow, BottomOffset.
+
+
     # Adds a new comment to the worksheet that owns these comments.
     # @note the author, text and ref options are required
     # @option options [String] author The name of the author for this comment
@@ -50,7 +65,7 @@ module Axlsx
       comment_list.each do |comment|
         comment.to_xml_string str
       end
-      str << '<commentList></comments>'
+      str << '</commentList></comments>'
 
     end
 
@@ -65,6 +80,7 @@ module Axlsx
     attr_reader :comments
 
     attr_reader :ref
+
     # TODO
     # r (Rich Text Run)
     # rPh (Phonetic Text Run)
@@ -82,6 +98,20 @@ module Axlsx
       "#{COMMENT_PN % (index+1)}"
     end
 
+    def vml_shape
+      @vml_shape ||= initialize_vml_shape
+    end
+
+    def initialize_vml_shape
+      ws = self.comments.worksheet
+      @vml_shape = VmlShape.new(self, :row => ws[ref].row.index, :column => ws[ref].index) do |vml|
+        vml.left_column = vml.row + 1
+        vml.right_column = vml.column + 4
+        vml.top_row = vml.row
+        vml.bottom_row = vml.row + 4
+      end
+    end
+
     # The index of this comment
     # @return [Integer]
     def index
@@ -105,8 +135,15 @@ module Axlsx
     end
 
     def to_xml_string(str = "")
-      str << '<comment ref="' << ref << '" authorId="' << author_index << '">'
-      str << '<t xml:space="preserve">' << text << '</t>'
+      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 << '<r>'
+      str << '<rPr><color indexed="81"/></rPr>'
+      str << '<t>' << text << '</t></r></text>'
       str << '</comment>'
     end
 
diff --git a/lib/axlsx/workbook/worksheet/worksheet.rb b/lib/axlsx/workbook/worksheet/worksheet.rb
index 24120aa1..8b868765 100644
--- a/lib/axlsx/workbook/worksheet/worksheet.rb
+++ b/lib/axlsx/workbook/worksheet/worksheet.rb
@@ -16,6 +16,8 @@ module Axlsx
     # @return [Array] of Table
     attr_reader :tables
 
+    attr_reader :comments
+
     # The rows in this worksheet
     # @note The recommended way to manage rows is Worksheet#add_row
     # @return [SimpleTypedList]
@@ -143,7 +145,7 @@ module Axlsx
     def initialize(wb, options={})
       self.workbook = wb
       @workbook.worksheets << self
-
+      @page_marging = @page_setup = @print_options = nil
       @drawing = @page_margins = @auto_filter = nil
       @merged_cells = []
       @auto_fit_data = []
@@ -155,7 +157,6 @@ module Axlsx
       @page_margins = PageMargins.new options[:page_margins] if options[:page_margins]
       @page_setup = PageSetup.new options[:page_setup] if options[:page_setup]
       @print_options = PrintOptions.new options[:print_options] if options[:print_options]
-
       @rows = SimpleTypedList.new Row
       @column_info = SimpleTypedList.new Col
       # @cols = SimpleTypedList.new Cell
@@ -487,6 +488,7 @@ module Axlsx
       @conditional_formattings.each do |cf|
         str.concat cf.to_xml_string
       end
+      str << '<legacyDrawing r:id="rId1"/>' if @comments.comment_list.size > 0
       str + '</worksheet>'
     end
 
@@ -497,9 +499,11 @@ module Axlsx
       @tables.each do |table|
         r << Relationship.new(TABLE_R, "../#{table.pn}")
       end
-      @comments.comment_list.each do |comment|
-        r << Relationship.new(COMMENT_R, "#{comment.pn}")
-      end
+
+      r << Relationship.new(VML_DRAWING_R, "../#{@comments.vml_drawing.pn}") if @comments.comment_list.size > 0
+      r << Relationship.new(COMMENT_R, "../#{@comments.pn}") if @comments.comment_list.size > 0
+      r << Relationship.new(COMMENT_R_NULL, "NULL") if @comments.comment_list.size > 0
+
       r << Relationship.new(DRAWING_R, "../#{@drawing.pn}") if @drawing
       r
     end