worksheet.rb in write_xlsx-0.60.0

- old
+ new

@@ -354,10 +354,11 @@
     attr_reader :autofilter_area # :nodoc:
     attr_reader :writer, :set_rows, :col_formats # :nodoc:
     attr_accessor :vml_shape_id, :rel_count, :hlink_refs # :nodoc:
     attr_reader :comments_author # :nodoc:
     attr_accessor :dxf_priority # :nodoc:
+    attr_reader :vba_codename # :nodoc:
 
     def initialize(workbook, index, name) #:nodoc:
       @writer = Package::XMLWriterSimple.new
 
       @workbook = workbook
@@ -419,11 +420,13 @@
       @outline_row_level = 0
       @outline_col_level = 0
 
       @merge = []
 
+      @has_vml  = false
       @comments = Package::Comments.new(self)
+      @buttons_array          = []
 
       @validations = []
 
       @cond_formats = {}
       @dxf_priority = 1
@@ -2100,10 +2103,12 @@
 
       # Check that row and col are valid and store max and min values
       check_dimensions(row, col)
       store_row_col_max_min_values(row, col)
 
+      @has_vml = true
+
       # Process the properties of the cell comment.
       @comments.add(Package::Comment.new(@workbook, self, row, col, string, options))
     end
 
     #
@@ -2641,13 +2646,26 @@
       index = shared_string_index(str[0, STR_MAX])
 
       # External links to URLs and to other Excel workbooks have slightly
       # different characteristics that we have to account for.
       if link_type == 1
-        # Substiture white space in url.
-        url = url.sub(/[\s\x00]/, '%20')
+        # Escape URL unless it looks already escaped.
+        unless url =~ /%[0-9a-fA-F]{2}/
+          # Escape the URL escape symbol.
+          url = url.gsub(/%/, "%25")
 
+          # Escape whitespae in URL.
+          url = url.gsub(/[\s\x00]/, '%20')
+
+          # Escape other special characters in URL.
+          re = /(["<>\[\]`^{}])/
+          while re =~ url
+            match = $~[1]
+            url = url.sub(re, sprintf("%%%x", match.ord))
+          end
+        end
+
         # Ordinary URL style external links don't have a "location" string.
         str = nil
       elsif link_type == 3
         # External Workbook links need to be modified into the right format.
         # The URL will look something like 'c:\temp\file.xlsx#Sheet!A1'.
@@ -3867,10 +3885,18 @@
 
       @sparklines << sparkline
     end
 
     #
+    # Insert a button form object into the worksheet.
+    #
+    def insert_button(*args)
+      @buttons_array << button_params(*(row_col_notation(args)))
+      @has_vml = 1
+    end
+
+    #
     # :call-seq:
     #   data_validation(cell_or_cell_range, options)
     #
     # Data validation is a feature of Excel which allows you to restrict
     # the data that a users enters in a cell and to display help and
@@ -4727,10 +4753,14 @@
 
     def comments_count # :nodoc:
       @comments.size
     end
 
+    def has_vml?  # :nodoc:
+      @has_vml
+    end
+
     def has_comments? # :nodoc:
       !@comments.empty?
     end
 
     def is_chartsheet? # :nodoc:
@@ -4992,10 +5022,42 @@
 
       # TODO Add the alpha part to the RGB.
       sprintf("FF%02X%02X%02X", *rgb[0, 3])
     end
 
+    def buttons_data  # :nodoc:
+      @buttons_array
+    end
+
+    #
+    # Turn the HoH that stores the comments into an array for easier handling
+    # and set the external links for comments and buttons.
+    #
+    def prepare_vml_objects(vml_data_id, vml_shape_id, comment_id)
+      @external_vml_links <<
+        [ '/vmlDrawing', "../drawings/vmlDrawing#{comment_id}.vml"]
+
+      if has_comments?
+        @comments_array = @comments.sorted_comments
+        @external_comment_links <<
+          [ '/comments', "../comments#{comment_id}.xml" ]
+      end
+
+      count         = @comments.size
+      start_data_id = vml_data_id
+
+      # The VML o:idmap data id contains a comma separated range when there is
+      # more than one 1024 block of comments, like this: data="1,2".
+      (1 .. (count / 1024)).each do |i|
+        vml_data_id = "#{vml_data_id},#{start_data_id + i}"
+      end
+      @vml_data_id = vml_data_id
+      @vml_shape_id = vml_shape_id
+
+      count
+    end
+
     private
 
     def check_for_valid_input_params(param)
       check_parameter(param, valid_validation_parameter, 'data_validation')
 
@@ -5711,10 +5773,74 @@
         raise "Shape #{index} (#{shape[:type]}) vertical alignment (#{shape[:valign]}) not in ['t', 'ctr', 'v']\n"
       end
     end
 
     #
+    # This method handles the parameters passed to insert_button as well as
+    # calculating the comment object position and vertices.
+    #
+    def button_params(row, col, params)
+      button = { :_row => row, :_col => col }
+
+      button_number = 1 + @buttons_array.size
+
+      # Set the button caption.
+      caption = params[:caption] || "Button #{button_number}"
+
+      button[:_font] = { :_caption => caption }
+
+      # Set the macro name.
+      if params[:macro]
+        button[:_macro] = "[0]!#{params[:macro]}"
+      else
+        button[:_macro] = "[0]!Button#{button_number}_Click"
+      end
+
+      # Ensure that a width and height have been set.
+      default_width  = 64
+      default_height = 20
+      params[:width]  = default_width  if !params[:width]
+      params[:height] = default_height if !params[:height]
+
+      # Set the x/y offsets.
+      params[:x_offset] = 0 if !params[:x_offset]
+      params[:y_offset] = 0 if !params[:y_offset]
+
+      # Scale the size of the comment box if required.
+      if params[:x_scale]
+        params[:width] = params[:width] * params[:x_scale]
+      end
+      if params[:y_scale]
+        params[:height] = params[:height] * params[:y_scale]
+      end
+
+      # Round the dimensions to the nearest pixel.
+      params[:width]  = (0.5 + params[:width]).to_i
+      params[:height] = (0.5 + params[:height]).to_i
+
+      params[:start_row] = row
+      params[:start_col] = col
+
+      # Calculate the positions of comment object.
+      vertices = position_object_pixels(
+                                        params[:start_col],
+                                        params[:start_row],
+                                        params[:x_offset],
+                                        params[:y_offset],
+                                        params[:width],
+                                        params[:height]
+                                        )
+
+      # Add the width and height for VML.
+      vertices << [params[:width], params[:height]]
+
+      button[:_vertices] = vertices
+
+      button
+    end
+
+    #
     # Based on the algorithm provided by Daniel Rentz of OpenOffice.
     #
     def encode_password(password) #:nodoc:
       i = 0
       chars = password.split(//)
@@ -5755,12 +5881,17 @@
 
     #
     # Write the <sheetPr> element for Sheet level properties.
     #
     def write_sheet_pr #:nodoc:
-      return if !fit_page? && !filter_on? && !tab_color? && !outline_changed?
+      if !fit_page? && !filter_on? && !tab_color? &&
+          !outline_changed? && !vba_codename?
+        return
+      end
+      codename = @vba_codename
       attributes = []
+      (attributes << 'codeName'   << codename) if codename
       (attributes << 'filterMode' << 1) if filter_on?
 
       if fit_page? || tab_color? || outline_changed?
         @writer.tag_elements('sheetPr', attributes) do
           write_tab_color
@@ -6676,11 +6807,11 @@
 
     #
     # Write the <legacyDrawing> element.
     #
     def write_legacy_drawing #:nodoc:
-      return unless has_comments?
+      return unless @has_vml
 
       # Increment the relationship id for any drawings or comments.
       @rel_count += 1
       id = @rel_count
 
@@ -7649,9 +7780,13 @@
       ptrue?(@tab_color)
     end
 
     def outline_changed?
       ptrue?(@outline_changed)
+    end
+
+    def vba_codename?
+      ptrue?(@vba_codename)
     end
 
     def zoom_scale_normal? #:nodoc:
       ptrue?(@zoom_scale_normal)
     end