table.rb in write_xlsx-1.11.2

- old
+ new

@@ -10,17 +10,18 @@
       include Writexlsx::Utility
 
       class ColumnData
         attr_reader :id
         attr_accessor :name, :format, :formula, :name_format
-        attr_accessor :total_string, :total_function
+        attr_accessor :total_string, :total_function, :custom_total
 
         def initialize(id, param = {})
           @id             = id
           @name           = "Column#{id}"
           @total_string   = ''
           @total_function = ''
+          @custom_total   = ''
           @formula        = ''
           @format         = nil
           @name_format    = nil
           @user_data      = param[id - 1] if param
         end
@@ -49,10 +50,11 @@
         set_the_table_and_autofilter_ranges
         set_the_autofilter_range
 
         add_the_table_columns
         write_the_cell_data_if_supplied
+        write_any_columns_formulas_after_the_user_supplied_table_data
         store_filter_cell_positions
       end
 
       def set_xml_writer(filename)
         @writer.set_xml_writer(filename)
@@ -76,22 +78,22 @@
         col_id = 0
         (@col1..@col2).each do |col_num|
           # Set up the default column data.
           col_data = Package::Table::ColumnData.new(col_id + 1, @param[:columns])
 
-          overrite_the_defaults_with_any_use_defined_values(col_id, col_data, col_num)
+          overwrite_the_defaults_with_any_use_defined_values(col_id, col_data, col_num)
 
           # Store the column data.
           @columns << col_data
 
           write_the_column_headers_to_the_worksheet(col_num, col_data)
 
           col_id += 1
         end    # Table columns.
       end
 
-      def overrite_the_defaults_with_any_use_defined_values(col_id, col_data, col_num)
+      def overwrite_the_defaults_with_any_use_defined_values(col_id, col_data, col_num)
         # Check if there are user defined values for this column.
         if @param[:columns] && (user_data = @param[:columns][col_id])
           # Map user defined values to internal values.
           col_data.name = user_data[:header] if user_data[:header] && !user_data[:header].empty?
 
@@ -154,10 +156,27 @@
           end
           i += 1
         end
       end
 
+      def write_any_columns_formulas_after_the_user_supplied_table_data
+        col_id = 0
+
+        (@col1..@col2).each do |col|
+          column_data = @columns[col_id]
+          if ptrue?(column_data) && ptrue?(column_data.formula)
+            formula_format = @col_formats[col_id]
+            formula        = column_data.formula
+
+            (@first_data_row..@last_data_row).each do |row|
+              @worksheet.write_formula(row, col, formula, formula_format)
+            end
+          end
+          col_id += 1
+        end
+      end
+
       def store_filter_cell_positions
         if ptrue?(@param[:autofilter])
           (@col1..@col2).each do |col|
             @worksheet.filter_cells["#{@row1}:#{col}"] = 1
           end
@@ -232,40 +251,60 @@
           style
           total_row
         ]
       end
 
-      def handle_the_column_formula(col_data, col_num, formula, format)
+      def handle_the_column_formula(col_data, _col_num, formula, _format)
         return unless formula
 
-        col_data.formula = formula.sub(/^=/, '').gsub(/@/, '[#This Row],')
+        formula = formula.sub(/^=/, '').gsub(/@/, '[#This Row],')
 
-        (@first_data_row..@last_data_row).each do |row|
-          @worksheet.write_formula(row, col_num, col_data.formula, format)
-        end
+        # Escape any future functions.
+        col_data.formula = @worksheet.prepare_formula(formula, 1)
+
+        # (@first_data_row..@last_data_row).each do |row|
+        #   @worksheet.write_formula(row, col_num, col_data.formula, format)
+        # end
       end
 
       def handle_the_function_for_the_table_row(row2, col_data, col_num, user_data)
-        function = user_data[:total_function].downcase.gsub(/[_\s]/, '')
+        formula = ''
+        function = user_data[:total_function]
+        function = 'countNums' if function == 'count_nums'
+        function = 'stdDev'    if function == 'std_dev'
 
-        function = 'countNums' if function == 'countnums'
-        function = 'stdDev'    if function == 'stddev'
+        subtotals = {
+          average:   101,
+          countNums: 102,
+          count:     103,
+          max:       104,
+          min:       105,
+          stdDev:    106,
+          sum:       109,
+          var:       110
+        }
 
-        col_data.total_function = function
+        if subtotals[function.to_sym]
+          formula = table_function_to_formula(function, col_data.name)
+        else
+          formula = @worksheet.prepare_formula(function, 1)
+          col_data.custom_total = formula
+          function = 'custom'
+        end
 
-        formula = table_function_to_formula(function, col_data.name)
+        col_data.total_function = function
         @worksheet.write_formula(row2, col_num, formula, user_data[:format], user_data[:total_value])
       end
 
       #
       # Convert a table total function to a worksheet formula.
       #
       def table_function_to_formula(function, col_name)
-        col_name = col_name.gsub(/'/, "''")
-                           .gsub(/#/, "'#")
-                           .gsub(/\[/, "'[")
-                           .gsub(/\]/, "']")
+        col_name = col_name.gsub("'", "''")
+                           .gsub("#", "'#")
+                           .gsub("[", "'[")
+                           .gsub("]", "']")
 
         subtotals = {
           average:   101,
           countNums: 102,
           count:     103,
@@ -393,14 +432,20 @@
           attributes << [:totalsRowFunction, col_data.total_function]
         end
 
         attributes << [:dataDxfId, col_data.format] if col_data.format
 
-        if ptrue?(col_data.formula)
+        if ptrue?(col_data.formula) || ptrue?(col_data.custom_total)
           @writer.tag_elements('tableColumn', attributes) do
-            # Write the calculatedColumnFormula element.
-            write_calculated_column_formula(col_data.formula)
+            if ptrue?(col_data.formula)
+              # Write the calculatedColumnFormula element.
+              write_calculated_column_formula(col_data.formula)
+            end
+            if ptrue?(col_data.custom_total)
+              # Write the totalsRowFormula element.
+              write_totals_row_formula(col_data.custom_total)
+            end
           end
         else
           @writer.empty_tag('tableColumn', attributes)
         end
       end
@@ -422,9 +467,18 @@
       #
       # Write the <calculatedColumnFormula> element.
       #
       def write_calculated_column_formula(formula)
         @writer.data_element('calculatedColumnFormula', formula)
+      end
+
+      #
+      # _write_totals_row_formula()
+      #
+      # Write the <totalsRowFormula> element.
+      #
+      def write_totals_row_formula(formula)
+        @writer.data_element('totalsRowFormula', formula)
       end
     end
   end
 end