table.rb in write_xlsx-0.59.0

- old
+ new

@@ -6,17 +6,53 @@
 module Writexlsx
   module Package
     class Table
       include Writexlsx::Utility
 
-      attr_writer :properties
+      class ColumnData
+        attr_reader :id
+        attr_accessor :name, :format, :formula
+        attr_accessor :total_string, :total_function
 
-      def initialize
-        @writer = Package::XMLWriterSimple.new
-        @properties = {}
+        def initialize(id, param = {})
+          @id             = id
+          @name           = "Column#{id}"
+          @total_string   = ''
+          @total_function = ''
+          @formula        = ''
+          @format         = nil
+          @user_data      = param[id-1] if param
+        end
       end
 
+      attr_reader :id
+
+      def initialize(worksheet, id, *args)
+        @worksheet = worksheet
+        @writer  = Package::XMLWriterSimple.new
+        @id      = id
+
+        @row1, @row2, @col1, @col2, @param = handle_args(*args)
+        @columns = []
+        @col_formats = []
+
+        # Set the data range rows (without the header and footer).
+        @first_data_row = @row1
+        @first_data_row += 1 if ptrue?(@param[:header_row])
+        @last_data_row  = @row2
+        @last_data_row  -= 1 if @param[:total_row]
+
+        set_the_table_options
+        set_the_table_style
+        set_the_table_name
+        set_the_table_and_autofilter_ranges
+        set_the_autofilter_range
+
+        add_the_table_columns
+        write_the_cell_data_if_supplied
+      end
+
       def set_xml_writer(filename)
         @writer.set_xml_writer(filename)
       end
 
       #
@@ -39,65 +75,263 @@
         # Close the XML writer object and filehandle.
         @writer.crlf
         @writer.close
       end
 
-      #
-      # Set the document properties.
-      #
-      def set_properties(properties)
-        @properties = properties
+      def add_the_table_columns
+        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)
+
+          # 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)
+        if @param[:columns]
+          # Check if there are user defined values for this column.
+          if user_data = @param[:columns][col_id]
+            # Map user defined values to internal values.
+            if user_data[:header] && !user_data[:header].empty?
+              col_data.name = user_data[:header]
+            end
+            # Handle the column formula.
+            handle_the_column_formula(
+                                      col_data, col_num, user_data[:formula], user_data[:format]
+                                      )
+
+            # Handle the function for the total row.
+            if user_data[:total_function]
+              handle_the_function_for_the_table_row(
+                                                    @row2, col_data, col_num,
+                                                    user_data[:total_function],
+                                                    user_data[:format]
+                                                    )
+            elsif user_data[:total_string]
+              total_label_only(
+                               @row2, col_num, col_data, user_data[:total_string], user_data[:format]
+                               )
+            end
+
+            # Get the dxf format index.
+            if user_data[:format]
+              col_data.format = user_data[:format].get_dxf_index
+            end
+
+            # Store the column format for writing the cell data.
+            # It doesn't matter if it is undefined.
+            @col_formats[col_id] = user_data[:format]
+          end
+        end
+      end
+
+      def write_the_column_headers_to_the_worksheet(col_num, col_data)
+        if @param[:header_row] != 0
+          @worksheet.write_string(@row1, col_num, col_data.name)
+        end
+      end
+
+      def write_the_cell_data_if_supplied
+        return unless @param[:data]
+
+        data = @param[:data]
+        i = 0    # For indexing the row data.
+        (@first_data_row..@last_data_row).each do |row|
+          next unless data[i]
+
+          j = 0    # For indexing the col data.
+          (@col1..@col2).each do |col|
+            token = data[i][j]
+            @worksheet.write(row, col, token, @col_formats[j]) if token
+            j += 1
+          end
+          i += 1
+        end
+      end
+
       private
 
-      #
-      # Write the XML declaration.
-      #
-      def write_xml_declaration
-        @writer.xml_decl('UTF-8', 1)
+      def handle_args(*args)
+        # Check for a cell reference in A1 notation and substitute row and column
+        row1, col1, row2, col2, param = row_col_notation(args)
+
+        # Check for a valid number of args.
+        raise "Not enough parameters to add_table()" if [row1, col1, row2, col2].include?(nil)
+
+        # Check that row and col are valid without storing the values.
+        check_dimensions_and_update_max_min_values(row1, col1, 1, 1)
+        check_dimensions_and_update_max_min_values(row2, col2, 1, 1)
+
+        # Swap last row/col for first row/col as necessary.
+        row1, row2 = row2, row1 if row1 > row2
+        col1, col2 = col2, col1 if col1 > col2
+
+      # The final hash contains the validation parameters.
+        param ||= {}
+
+        # Turn on Excel's defaults.
+        param[:banded_rows] ||= 1
+        param[:header_row]  ||= 1
+        param[:autofilter]  ||= 1
+
+        # If the header row if off the default is to turn autofilter off.
+        param[:autofilter] = 0 if param[:header_row] == 0
+
+        check_parameter(param, valid_table_parameter, 'add_table')
+
+        [row1, row2, col1, col2, param]
       end
 
+      # List of valid input parameters.
+      def valid_table_parameter
+        [
+         :autofilter,
+         :banded_columns,
+         :banded_rows,
+         :columns,
+         :data,
+         :first_column,
+         :header_row,
+         :last_column,
+         :name,
+         :style,
+         :total_row
+        ]
+      end
+
+      def handle_the_column_formula(col_data, col_num, formula, format)
+        return unless formula
+
+        # Remove the leading = from formula.
+        formula.sub!(/^=/, '')
+        # Covert Excel 2010 "@" ref to 2007 "#This Row".
+        formula.gsub!(/@/,'[#This Row],')
+
+        col_data.formula = formula
+
+        (@first_data_row..@last_data_row).each do |row|
+          @worksheet.write_formula(row, col_num, formula, format)
+        end
+      end
+
+      def handle_the_function_for_the_table_row(row2, col_data, col_num, total_function, format)
+        function = total_function
+
+        # Massage the function name.
+        function = function.downcase
+        function.gsub!(/_/, '')
+        function.gsub!(/\s/,'')
+
+        function = 'countNums' if function == 'countnums'
+        function = 'stdDev'    if function == 'stddev'
+
+        col_data.total_function = function
+
+        formula = table_function_to_formula(function, col_data.name)
+        @worksheet.write_formula(row2, col_num, formula, format)
+      end
+
       #
-      # Write the <autoFilter> element.
+      # Convert a table total function to a worksheet formula.
       #
-      def write_auto_filter
-        autofilter = @properties[:_autofilter]
+      def table_function_to_formula(function, col_name)
+        subtotals = {
+          :average   => 101,
+          :countNums => 102,
+          :count     => 103,
+          :max       => 104,
+          :min       => 105,
+          :stdDev    => 107,
+          :sum       => 109,
+          :var       => 110
+        }
 
-        return unless autofilter
+        unless func_num = subtotals[function.to_sym]
+          raise "Unsupported function '#{function}' in add_table()"
+        end
+        "SUBTOTAL(#{func_num},[#{col_name}])"
+      end
 
-        attributes = ['ref', autofilter]
+      # Total label only (not a function).
+      def total_label_only(row2, col_num, col_data, total_string, format)
+        col_data.total_string = total_string
 
-        @writer.empty_tag('autoFilter', attributes)
+        @worksheet.write_string(row2, col_num, total_string, format)
       end
 
+      def set_the_table_options
+        @show_first_col   = ptrue?(@param[:first_column])   ? 1 : 0
+        @show_last_col    = ptrue?(@param[:last_column])    ? 1 : 0
+        @show_row_stripes = ptrue?(@param[:banded_rows])    ? 1 : 0
+        @show_col_stripes = ptrue?(@param[:banded_columns]) ? 1 : 0
+        @header_row_count = ptrue?(@param[:header_row])     ? 1 : 0
+        @totals_row_shown = ptrue?(@param[:total_row])      ? 1 : 0
+      end
+
+      def set_the_table_style
+        if @param[:style]
+          @style = @param[:style]
+          # Remove whitespace from style name.
+          @style.gsub!(/\s/, '')
+        else
+          @style = "TableStyleMedium9"
+        end
+      end
+
+      def set_the_table_name
+        if @param[:name]
+          @name = @param[:name]
+        else
+          # Set a default name.
+          @name = "Table#{id}"
+        end
+      end
+
+      def set_the_table_and_autofilter_ranges
+        @range   = xl_range(@row1, @row2,          @col1, @col2)
+        @a_range = xl_range(@row1, @last_data_row, @col1, @col2)
+      end
+
+      def set_the_autofilter_range
+        @autofilter = @a_range if ptrue?(@param[:autofilter])
+      end
+
       #
+      # Write the XML declaration.
+      #
+      def write_xml_declaration
+        @writer.xml_decl('UTF-8', 1)
+      end
+
+      #
       # Write the <table> element.
       #
       def write_table
         schema           = 'http://schemas.openxmlformats.org/'
         xmlns            = "#{schema}spreadsheetml/2006/main"
-        id               = @properties[:id]
-        name             = @properties[:_name]
-        display_name     = @properties[:_name]
-        ref              = @properties[:_range]
-        totals_row_shown = @properties[:_totals_row_shown]
-        header_row_count = @properties[:_header_row_count]
 
         attributes = [
                       'xmlns',       xmlns,
                       'id',          id,
-                      'name',        name,
-                      'displayName', display_name,
-                      'ref',         ref
+                      'name',        @name,
+                      'displayName', @name,
+                      'ref',         @range
                      ]
 
-        unless ptrue?(header_row_count)
+        unless ptrue?(@header_row_count)
           attributes << 'headerRowCount' << 0
         end
 
-        if ptrue?(totals_row_shown)
+        if ptrue?(@totals_row_shown)
           attributes << 'totalsRowCount' << 1
         else
           attributes << 'totalsRowShown' << 0
         end
         @writer.start_tag('table', attributes)
@@ -105,80 +339,68 @@
 
       #
       # Write the <autoFilter> element.
       #
       def write_auto_filter
-        autofilter = @properties[:_autofilter]
+        return unless ptrue?(@autofilter)
 
-        return unless ptrue?(autofilter)
+        attributes = ['ref', @autofilter]
 
-        attributes = ['ref', autofilter]
-
         @writer.empty_tag('autoFilter', attributes)
       end
 
       #
       # Write the <tableColumns> element.
       #
       def write_table_columns
-        columns = @properties[:_columns]
+        count = @columns.size
 
-        count = columns.size
-
         attributes = ['count', count]
 
         @writer.tag_elements('tableColumns', attributes) do
-          columns.each {|col_data| write_table_column(col_data)}
+          @columns.each {|col_data| write_table_column(col_data)}
         end
       end
 
       #
       # Write the <tableColumn> element.
       #
       def write_table_column(col_data)
         attributes = [
-                      'id',   col_data[:_id],
-                      'name', col_data[:_name]
+                      'id',   col_data.id,
+                      'name', col_data.name
                      ]
 
-        if ptrue?(col_data[:_total_string])
-          attributes << :totalsRowLabel << col_data[:_total_string]
-        elsif ptrue?(col_data[:_total_function])
-          attributes << :totalsRowFunction << col_data[:_total_function]
+        if ptrue?(col_data.total_string)
+          attributes << :totalsRowLabel << col_data.total_string
+        elsif ptrue?(col_data.total_function)
+          attributes << :totalsRowFunction << col_data.total_function
         end
 
-        if col_data[:_format]
-          attributes << :dataDxfId << col_data[:_format]
+        if col_data.format
+          attributes << :dataDxfId << col_data.format
         end
 
-        if ptrue?(col_data[:_formula])
+        if ptrue?(col_data.formula)
           @writer.tag_elements('tableColumn', attributes) do
             # Write the calculatedColumnFormula element.
-            write_calculated_column_formula(col_data[:_formula])
+            write_calculated_column_formula(col_data.formula)
           end
         else
           @writer.empty_tag('tableColumn', attributes)
         end
       end
 
       #
       # Write the <tableStyleInfo> element.
       #
       def write_table_style_info
-        props = @properties
-
-        name                = props[:_style]
-        show_first_column   = props[:_show_first_col]
-        show_last_column    = props[:_show_last_col]
-        show_row_stripes    = props[:_show_row_stripes]
-        show_column_stripes = props[:_show_col_stripes]
-
         attributes = [
-                      'name',              name,
-                      'showFirstColumn',   show_first_column,
-                      'showLastColumn',    show_last_column,
-                      'showRowStripes',    show_row_stripes,
-                      'showColumnStripes', show_column_stripes
+                      'name',              @style,
+                      'showFirstColumn',   @show_first_col,
+                      'showLastColumn',    @show_last_col,
+                      'showRowStripes',    @show_row_stripes,
+                      'showColumnStripes', @show_col_stripes
                      ]
 
         @writer.empty_tag('tableStyleInfo', attributes)
       end
 