Module providing generic xlsx (Excel/OpenOffice) import/export functionality

excel_integration.rb

# This module provides generic XLSX import/export capabilities.
# For more details check the gems;
# Exporting: 'caxlsx', https://github.com/caxlsx/caxlsx.git
# Importing: 'xsv', https://github.com/martijn/xsv
# 
module ExcelIntegration
  def self.included(base)
    base.send :include, InstanceMethods
    base.extend ClassMethods
  end

  module InstanceMethods
    # These instance methods will be included when the module is included
    #
    # def foo
    #   'foo'
    # end
    #
  end

  module ClassMethods
    # The base class will be extended with these methods when the module is included
    #
    # def bar
    #   'bar'
    # end
    #
    def export_to_excel(filepath="#{table_name}_#{Date.today.to_s}.xlsx", table_style='TableStyleMedium2')
      Axlsx::Package.new do |p|
        # Create a worksheet with the same name as the DB table
        p.workbook.add_worksheet(:name => table_name.humanize) do |sheet|
          # Define formatting for each DB attribute type
          styles = {}
          styles[:boolean] = sheet.styles.add_style format_code: '@'
          styles[:date] = sheet.styles.add_style format_code: 'yyyy-mm-dd'
          styles[:integer] = sheet.styles.add_style num_fmt: 1
          styles[:string] = sheet.styles.add_style format_code: '@'
          # Add a header row using the DB table column names
          sheet.add_row attribute_names
          # Add a data row for each record in the table
          all.each do |record|
            sheet.add_row record.attributes.values
          end
          # Apply the formatting for each column type (Excluding the Header row)
          columns_hash.each_with_index do |(_name, column),index|
            # p "#{index}: #{_name} => #{column.type}"
            sheet.col_style index, styles[column.type], row_offset: 1
          end
          # Add an excel table referencing our data
          sheet.add_table("A1:#{('A'..'Z').to_a[attribute_names.size - 1]}#{all.size + 1}", name: table_name, style_info: { name: table_style, show_row_stripes: true })
        end
        p.serialize(filepath)
      end
    end

    # import_from_excel(filepath, attr_allowed)
    #
    # Deletes all existing records in the database by truncating the table and then imports data from
    # the specified excel file (filepath).  By default, all excel columns with headings matching table column
    # names will be imported.  You can override this by passing an array to the 'attr_allowed' parameter.
    def import_from_excel(filepath="#{table_name}_#{Date.today.to_s}.xlsx",attr_allowed=attribute_names)
      # Delete existing records
      ActiveRecord::Base.connection.truncate(table_name)
      # Create new records from excel
      workbook = Xsv::Workbook.open(filepath)
      sheet = workbook.sheets_by_name(table_name.humanize).first
      # Parse headers and switch to hash mode
      sheet.parse_headers!
      sheet.each_row do |row|
        create(row.select { | k,_| attr_allowed.include? k })
      end
    end
  end
end