Export multiple excel and zip files in Rails

I. Introduction

Exporting excel file and zip files is not somethings new, but sometimes we must to integrate both. So today, I want to share my task.

II. Installation

Firstly, we need to install gem axslx to customize our file easily:

  • Add to Gemfile
gem "axlsx"
  • Run command bundle install

III. Services

In this example, each team had many member who have a unique budget, and we want to download budget of all teams or members. Then, to be clear, we need to create 2 services: export excel files and export file zip:

1. Export Excel files

For exporting excel service, we have some function:

app/services/budget/export_xls.rb

  • Initialize attributes:
TITLE_COLUMN = ["ID", "Name", "Birthday"]
def initialize_attributes
      budget_dates = []
      @deadlines = []
      @files = []
      (Date.today.cweek..Date.today.end_of_year.cweek).to_a.map do |d|
      	deadlines << Date.commercial(Date.today.year, d, 7)
        date = Date.commercial(Date.today.year, d, 1)
        budget_dates << date.date.to_s
      end
      @first_row = [*TITLE_COLUMN, *budget_dates]
    end
  • Create temporally files
def create_files
      BudgetType.pluck(:name).each_with_index do |file_name, index|
        package = Axlsx::Package.new
        workbook = package.workbook
        workbook.add_worksheet(name: file_name) do |sheet|
          sheet.add_row first_row
        end
        files << package
      end
    end
  • Add data for these files by customize format
def add_data_to_files object
      budgets = object.budgets.deadlines_in deadlines
      info = [object.id, object.name, object.birthday]
      files.each do |file|
        row = [*info, *budgets]
        file.workbook.worksheets.first.add_row row
      end
    end
  • Save temporally files
def save_file
      files.each do |file|
        file.serialize "#{file.workbook.worksheets.first.name}.xls"
      end
    end
  • Finally, in perform, we use those methods as:
    def perform
      initialize_attributes
      return unless create_file
      Team.each do |team|
        add_data_to_files team
        team.members.each do |member|
          add_data_to_files member
        end
      end
      save_file
    end
2. Export file zip

In zip files service, we need to create temporally zip file, add created excel files to zip and delete all temporally excel files:

app/services/budget/zip_budgets.rb

def perform files
      begin
        temp = Tempfile.new FILE_NAME
        Zip::File.open(temp.path, Zip::File::CREATE) do |zipfile|
          files.each do |file|
            zipfile.add "#{file}.xls", "#{Rails.root}/#{file}.xls"
          end
        end
        files.each do |file|
          File.delete "#{file}.xls"
        end
      rescue Errno::ENOENT, IOError => e
        Rails.logger.error e.message
        temp.close
      end
      temp
    end

IV. Controller and view

  • To download zip file, we need to return this file in controller:

app/controllers/export/budgets_controller.rb

def index
  if Budget::ExportXls.perform
    zip = Budget::ZipBudgets.perform BudgetType.pluck(:name)
    send_file zip.path, type: "application/zip", x_sendfile: true,
      disposition: "attachment", filename: "Budgets.zip"
  end
end
  • Finally, we add button download to view:

app/views/budgets/index.html.erb

<%= link_to export_budgets_path do %>
  <%= button_tag "Download" %>
<% end %>

V. Conclusion

This is a good way to export multiple excel and zip files in rails. I hope you find this article useful. Thanks for reading!