XLS / CSV Importer for Rails 6

controller.rb

class LessonController < ApplicationController

  def import
    begin
      Lesson.transaction do
        @lessons = Importer.import(params[:file],method(:import_new_or_existing), method(:import_modify_row), allowed_import_params, allowed_params)
      end
      redirect_to admin_lessons_path, notice: "#{@lessons.count} Lektionen wurden erfolgreich importiert."
    rescue  => e
      redirect_to admin_lessons_path, alert: "#{e.message}"
    end
  end


  # defines whether an imported row creates a new lesson or overwrites an existing one
  def import_new_or_existing(row)
    (row["id"] && Lesson.find_by_id(row["id"])) || (row["slug"] && Lesson.find_by_slug(row["slug"])) || new
  end

  # modifies the rows before importing a lesson
  def import_modify_row (row, lesson)

    if row["parent_id"] && !Lesson.find_by_id(row["parent_id"])
      raise "Parent lesson with ID #{row["parent_id"]} could not be found. Make sure it exists before this lesson is imported."
    end

    if row["parent_slug"]
      parent = Lesson.find_by_slug(row["parent_slug"])
      unless parent
        raise "Parent lesson with slug '#{row["parent_slug"]}' could not be found. Make sure it exists before this lesson is imported."
      end
      row["parent_id"] = parent.id
    end

    if row["cover_url"]
      lesson.attach_cover_by_url(row["cover_url"])
    end
    row
  end


  private
  # defines the param keys that are allowed during import
  def allowed_import_params
    allowed_params.union([:parent_slug, :cover_url])
  end

  # defines the param keys that are allowed to be set on the model
  def allowed_params
    [:title, :slug, :description, :status, :icon, :parent_id, :order, :cover]
  end

  def lesson_params
    params.require(:lesson).permit(*allowed_params)
  end
end

import_error.rb

class ImportError < StandardError
  def initialize(msg="There was an error while importing your data.")
    super
  end
end

importer.rb

class Importer

  # Imports a CSV or XLS file and creates / updates models based on the rows in it
  # @param [String] path to the file
  # @param [Method] new_or_existing returns a new or existing ActiveRecord Model that will be modified and saved
  # @param [Method] modify_row modifies the rows before they are stored in the model
  # @param [Array<Symbol>] import_params a list of all allowed parameter keys for each row
  # @param [Array<Symbol>] allowed_params a list of all allowed parameters for the model
  def self.import(file, new_or_existing, modify_row, import_params, allowed_params)

    spreadsheet = self.open_spreadsheet(file).sheet(0)
    header = spreadsheet.row(1)
    @entities = []

    begin
      # roo's index starts at 1 (which is the header row - the content starts at 2)
      (2..spreadsheet.last_row).each do |i|
        row = Hash[[header, spreadsheet.row(i)].transpose]

        forbidden_attributes = row.to_hash.symbolize_keys.slice!(*import_params).keys
        unless forbidden_attributes.empty?
          raise ImportError, "The following columns are not allowed: #{forbidden_attributes.map{|v| v.to_s}.join(', ')}"
        end

        # determine when to overwrite and when to create a new entity
        entity = new_or_existing.call(row)

        # handling / transforming the rows
        row = modify_row.call(row, entity)

        # storing the rows
        entity.attributes = row.to_hash.symbolize_keys.slice(*allowed_params)
        entity.save! # the "bang version" causes the save method to raise an exception which will then make the transaction fail
        @entities.push(entity)

      end
      return @entities
    rescue ActiveRecord::ActiveRecordError => e
      raise "Import failed: #{e.message}"
    end
  end


  private

  # Opens a file and reads it using the roo library
  # @param [string] file path to the spreadsheet
  # @return [Roo::Spreadsheet] the roo spreadsheet
  def self.open_spreadsheet(file)
    case File.extname(file.original_filename)
    when '.csv' then Roo::Spreadsheet.open(file)
    when '.xlsx', '.xls' then Roo::Spreadsheet.open(file ,{ csv_options: { encoding: 'bom|utf-8' }})
    else raise "Unknown file type: #{file.original_filename}"
    end
  end
end

routes.rb

Rails.application.routes.draw do
  
  concern :importable do
    collection do
      post 'import'
    end
  end
  
  resources :lessons, concerns: :importable
end

view.rb

<%= form_tag import_lessons_path, multipart: true do  %>
  <%= file_field_tag :file %>
  <%= submit_tag "Import" %>
<% end %>