Exporting data from a Rails's Application

Ruby On Rails

Exporting data from a Rails's Application

Exporting data from Rails models to *.csv and and *.xlsx

If you’ve got a Rails Application and want to export information from the database there are many options. We’ll use Rail’s ability to respond to different formats within the standard RESTful routes to export data from the Student and School models in the sample app (view the repository here).

The application is hosted on Heroku here and has been seeded with some basic data. Each student ‘belongs_to’ a school which has it’s own model that contains the school’s name and address.

Export to CSV

First we will start with exporting a comma separated value file. This file is very simple, can be read by text editors and widely supported so it will allow you to import the data into another application.

The CSV class is now included in the standard Ruby library since ~> 1.9.2 so all we have to do is to ‘require CSV’ within the Rail’s Application. Although it is a simple format, it can be troublesome to save and load data which itself contains quotes and commas.

In the sample application the goal is to save the data in the student model and the rows association data in the school model. To do this we will use the ActiveRecord joins and select methods to query our database and return our Student::ActiveRecord_Relation.

require 'csv'

class Student < ApplicationRecord
  
  belongs_to :school
  
  [...]
  
  def self.all_with_school_details
    Student.select("students.*, schools.name as school_name, schools.address as school_address").joins(:school)
  end
  
  [...]
  
end

#ActiveRecord will turn this into the following SQL

#SELECT students.*, schools.name as school_name, schools.address as school_address 
#FROM "students" INNER JOIN "schools" ON "schools"."id" = "students"."school_id";

If we open the Rail’s Console and run the code you will notice it does not return the fields from the joined schools table.

Student.all_with_school_details.first

=> #<Student:0x007f9c26baa580
 id: 1,
 first_name: "Bart",
 last_name: "Simpson",
 DOB: Sun, 17 Dec 1989,
 email: "bart@the-simpsons.com",
 created_at: Sun, 04 Dec 2016 06:26:52 UTC +00:00,
 updated_at: Sun, 04 Dec 2016 06:26:52 UTC +00:00,
school_id: 1>

ActiveRecord will only return items in Student class. To see the fully joined result we would need to open the database. (Postgres in this case and *psql csv-export_development *from the terminal {not the Rail’s console} to open the database, then we can run the raw SQL) We can however check from the console the columns are included in the result by chaining the column name (and including first so we only have one result) onto the end of the method like so…

Student.all_with_school_details.first.school_name

=> "Springfield Elementary School"

Now that we have our complete ActiveRecord object we need a method in the Student model to turn it into a CSV.

def self.as_csv
  CSV.generate do |csv|
    columns = %w(id first_name last_name DOB school_name school_address)
    csv << columns.map(&:humanize)
    all_with_school_details.each do |student|
      csv << student.attributes.values_at(*columns)
    end
  end
end

Here we create an array ‘columns’ with the names of the columns in our table we wish to be included in the CSV file. As we renamed the columns in the schools table via the ActiveRecord query (i.e. schools.name as schoolname) we must now use ‘schoolname’ and ‘school_address’ to access the data in these columns. We use Ruby’s map enumerable and Rail’s humanize method to create the first row in the CSV file from the columns array, this will be the title for each column.

Then we use the class method we created earlier with all the data from the tables in the database with the each enumerable to add a new row for every record in the students table. Now all we need is to be able call this method from within our controller.

students GET /students(.:format) students#index

You can see from the Rail’s routes that Rail’s standard RESTful routes allow use to use different formats apart from HTML.

def index
  @students = Student.all_with_school_details

  respond_to do |format|
    format.html
    format.csv { send_data @students.as_csv }
end

With the respond_to block we now allow a .csv request to send_data (save a file) from the ‘as_csv’ method we created earlier in the Student model. Now all we need is to include a link within the view of the app so a user can access the method.

<%= link_to "Export CSV", students_path(format: :csv) %>

Which is just a matter of specifying the format in the normal Rail’s link_to helper. Now we have everything in place when we click the link we should see…

Pop up to save the linkPop up to save the link

Once we save an open the file we have all the the data from our tables saved to the local computer and easily viewable in a table like this.

Our CSV table generated through the Rail’s App.Our CSV table generated through the Rail’s App.

Export to XLSX

The next challenge is to save to an XLSX file. For this will be using the Axlsx-Rail’s — Spreadsheet templates for Rail’s gem. The first step will be to include the required Gems in the Rail’s App Gemfile…

wb = xlsx_package.workbook
wb.add_worksheet(name: "Student") do |sheet|
  title = wb.styles.add_style(b: true, bg_color: "395870", fg_color: "FFFFFF",
                              alignment: {horizontal: :center})
  sheet.add_row ["ID", "Name", "Date of Birth", "Email",
                               "School", "School Address"], style: title
  @students.each do |student|
    sheet.add_row [student.id, student.full_name, student.DOB, student.email,
                   student.school_name, student.school_address]
  end
end

Once added to the Gemfile we must save and run* bundle install* from the terminal. Now we have installed it, it would be wise to look at the readme for the Aslsx gem. You will see it allows you to do quite a lot but there are known issues with Google Docs, Libre Office and Apple Numbers. You should check these and make sure it is the right option for you app.

We will use the allwithschool_details class method we created in the CSV section to provide the data. This gem requires us to make view with the extension *.xlsx.axlsx. So we will setup the controller to respond to XLSX requests and add a link to index.html.erb like so…

wb = xlsx_package.workbook
wb.add_worksheet(name: "Student") do |sheet|
  title = wb.styles.add_style(b: true, bg_color: "395870", fg_color: "FFFFFF",
                              alignment: {horizontal: :center})
  sheet.add_row ["ID", "Name", "Date of Birth", "Email",
                               "School", "School Address"], style: title
  @students.each do |student|
    sheet.add_row [student.id, student.full_name, student.DOB, student.email,
                   student.school_name, student.school_address]
  end
end

Now it is a matter of creating the view and reading the documentation to output the XLSX file with the data and format desire. The view extension must be *.xlsx.axlsx *and as we are in the Students#index we will call the file *index.xlsx.axlsx *in the */app/views/students folder which means we are using the Standard Rail’s convention.

wb = xlsx_package.workbook
wb.add_worksheet(name: "Student") do |sheet|
  title = wb.styles.add_style(b: true, bg_color: "395870", fg_color: "FFFFFF",
                              alignment: {horizontal: :center})
  sheet.add_row ["ID", "Name", "Date of Birth", "Email",
                               "School", "School Address"], style: title
  @students.each do |student|
    sheet.add_row [student.id, student.full_name, student.DOB, student.email,
                   student.school_name, student.school_address]
  end
end

The above code in the view above will style the headings like on the website. There is many styling options which can be applied to individual columns and the ability to create multiple sheets in the one file.

The final output of students.xlsxThe final output of students.xlsx

Once you click and save the file you can see here the output of the App. It looks very similar to the CSV but the AXSLX gem includes many more features not included in this article and not available with simple CSV file exports.

Conclusion

Working with Rail’s conventions makes exporting data from your models straightforward. F]If you have any problems you can contact me on Twitter @rhysonrails or click the email link below to send me an email.