In one of our web application, we had a specific requirement wherein the client wanted the data from the app into a specific xls format. Thus we needed to export data from our app in this custom format and save the xls file on S3. Our app will then show a link to "Download" in ActiveAdmin UI clicking which it will start downloading this xls file from S3 on clients local PC. We have broken down the process into numerous steps, hope this will be helpful.

The process goes as follows:

  1. The data from the app is exported i.e. stored in xls format. (i.e. export the data from app to public folder of Heroku as only public folder is writable.)

  2. The xls file is saved on S3 (without direct downloading the file. This xls file is saved first in public folder of Heroku and from there its uploaded to S3).

  3. Download xls file from S3.

This exported data in xls files is in a special format that the client's back-office system can import for inventory control and revenue control.

1. Generating xls format

For generating xls format, we used [[axlsx_rails[][1] gem.
[axlsx_rails][2] gem. plugin provides a renderer and template handler for xlsx.

By using this gem, we have created templates with ".xlsx.axlsx" extension.

ex:

app/views/admin/export_order.xlsx.axlsx

wb = xlsx\_package.workbook
wb.add_worksheet(name: 'export_orders') do |sheet|
  orders.each do |order|
    sheet.add_row [order.order_number]
  end
end

app/workers/export_worker.rb

render\_to\_string(template: "admin/export_order.xlsx.axlsx",
        layout: false, formats: [:axlsx], locals: { orders: orders })

we have used this in controller as:

format: xlsx

Or, if you need to specify the filename:

render xlsx: "export\_order", filename: "export\_orders.xlsx"

this template generates xls file and saves it in public folder of Heroku without downloading on client PC.

 File.open("public/orders_exported_#{formatted_date}.xls", 'w') do |file|
    file.write(ActionController::Base.new()
    .render_to_string(template: "admin/export_order.xlsx.axlsx",
    layout: false, formats: [:axlsx], locals: { orders: orders }))
  end

2. Save xls file on S3

we can setup default credentials for all AWS services via AWS.config:

Credentials:

AWS.config(
  access\_key\_id:     'YOUR_ACCESS_KEY_ID',
  secret\_access\_key: 'YOUR_SECRET_ACCESS_KEY')

Or we can set them directly on the S3 interface:

s3 = AWS::S3.new(
  access\_key\_id:     'YOUR_ACCESS_KEY_ID',
  secret\_access\_key: 'YOUR_SECRET_ACCESS_KEY')

Buckets:

Before saving file or uploading to S3, we need to create a bucket.

s3 = AWS::S3.new
bucket = s3.buckets.create('my-bucket')

If a bucket already exists, you can get a reference to the bucket.

bucket = s3.buckets['my-bucket']

Buckets contain objects. Each object in a bucket has a unique key.

Getting an Object:

If the object already exists, you can get a reference to the object.

obj = bucket.objects['key']

ex:

obj = bucket.objects[Time.zone.now.to_s(:number) + '/' + filename]

Reading and Writing an Object:

obj.write(Pathname.new('/path/to/file.txt'))

The example above returns an S3 Object. You call S3Object#write and S3Object#read to save and download from S3 respectively.

For both buckets and objects, we can use the acl (access control list) method as public_read so anyone can read them.

ex:

obj.acl = :public_read

3. Download xls file from S3

In this example we can download xls file from s3 as

resource = ExportedOrder.find(params[:id])
bucket = AWS::S3.new.buckets[BUCKET]
obj = bucket.objects[resource.file_path]
send\_data(obj.read,
          disposition: 'attachment',
          filename: resource.file_name,
          type: MIME::Types.type_for(resource.file_path).first)

here send_data sends the given binary data to the browser. This method is similar to render text: data, but also allows us to specify whether the browser should display the response as a file attachment (i.e. in a download dialog) or as inline data.

For more details about [axlsx_rails gem click here][3]

For more details about [aws-sdk gem click here][4]

For more details about [AWS::S3 documnetation click here][5]

[1]: https://github.com/straydogstudio/axlsx_rails
[2]: https://github.com/straydogstudio/axlsx_rails
[3]: https://github.com/straydogstudio/axlsx_rails
[4]: https://github.com/aws/aws-sdk-ruby
[5]: http://docs.aws.amazon.com/AWSRubySDK/latest/AWS/S3.html