In this post, I talk about an itch of mine and how we at Rising Sun went about scratching it.
It is a clear and present reality that our clients or users of our applications are very comfortable with using Excel. They like to see data in it, they like to update and even store data in it.
It’s a very practical tool with them and makes them feel in control. So, as such my business apps all need/require an “excel interface” in some fashion.
Requirements can generally be divided into the following -
1. Slurp initial application data from an excel file (seed data).
2. Provide reports/backup as an excel file.
3. Import bulk data into the application from an Excel file (as a normal regular standard operating procedure).
CSV is a real alternative and one we will probably use in the future, but for now this post talks about using the “Spreadsheet” Gem to achieve the objectives listed above.
This blog post aims to help you hook in export/import logic with the rest of the rails app. The usage of Spreadsheet API is not in scope of this blog post.
Installing the “spreadsheet” gem.
config/environment.rb
config.gem "spreadsheet"
Followed by
rake gems:install
Note: You do not need the parseexcel gem anymore to read an Excel file. This has already been included in the spreadsheet gem.
Design
As a design principle and to keep things dry, I really recommend creating a (one or more) Export-Import class in your app to handle all “Excel” related matters.
app/models/excel_exim.rb
class ExcelExim
def initialize(file = nil)
Spreadsheet.client_encoding = 'UTF-8'
@file = file
end
def export_data(workbook)
# write export logic here
# Use the spreadsheet API to create worksheets in the workbook
# Use the worksheet apis to write content in cells/rows etc
end
def import_data
# write import logic here
# Delete models etc
# Use the Spreadsheet API to read cell/row content etc and save your models.
end
end
You can invoke methods within this module for various import export tasks. You could also do this as a module and include that module in your class where you need excel functionality but the aim here is to get things done in the simplest possible way. Refactoring can follow to your taste once the tests and functionality is ready.
Seeding Data
Since Rails 2.3.4, we have the rake db:seed task which runs the db/seeds.rb file. You are advised to put your seeding code in that file. We assume you want to read an excel file to seed your database.
db/seeds.rb
file = ENV["FILE"] || "public/products.xls" ExcelExim.new(file).import_data
Running the import is then possible as (FILE param is optional of course)
rake db:seed FILE=public/myfile.xls
Excel reports
Excel reports are quite easy to generate and fit well into the Rails’ MVC pattern as well as the RESTful way of doing things.
You would like to respond to requests based on file_type. A request for /products.xls would hit the ProductsController’s index action. You would fetch all the products within the controller and ideally you would want the app/views/products/index.xls.rxls view invoked. This is all possible and quite easy with installing the “spreadsheet_on_rails” plugin.
Install the plugin as
ruby script/plugin install git://github.com/10to1/spreadsheet_on_rails.git
app/controllers/products_controller.rb
def index
@products = Product.find(:all)
respond_to do |format|
format.html # index.html.erb
format.xml { render :xml => @products }
format.xls { render :action => "index", :layout => false }
end
end
app/views/products/index.xls.rxls
ExcelExim.new.export_data(workbook)
workbook is method available for us to generate excel document with. The plugin takes care of rendering it and returning it to the browser as well as setting the required MIME::Types.
Import an Excel file from the front end
This process is similar to the above described earlier. We’ve made our life fairly easy by pushing the required logic in the ExcelExim class’ import_file method.
We now use a simple Multipart form to pull the Excel file from the browser and slurp up the content of the excel file.
Assuming we have an import action in our products controller (ProductsController) that deal with reading the excel file. It would look something like
config/routes.rb
map.resources :products, :collection => {:new_import => :get, :import => :post}
app/views/product/new_import.html.erb
<% form_tag(import_products_path, :multipart => true) do %>
<fieldset>
<legend>Import Data via Excel</legend>
<label for="file_import">Import File</label>
<%= file_field_tag(:excel_file) %>
</fieldset>
<div class="buttons"><%= submit_tag("Upload", :class => "button") %></div>
<% end %>
app/controllers/products_controller.rb
def new_import
# do nothing, this method is not required but added here just to let you know that its an endpoint
end
def import
# Error Handling using True/False or Using Exception is left to your preference.
# The aim is to highlight that .local_path is important to get hold of the uploaded excel file
# not controller flow
if ExcelExim.new(params[:excel_file].local_path).import_data
flash[:notice] = "Products imported"
redirect_to root_path
else
flash[:error] = "Error uploading Excel file : #{e.to_s}"
redirect_to new_import_products_path
end
end
Conclusion
I hope this post helps you deal with Excel spreadsheet a bit more easily.
It is important to note that this process works only for Excel 2003 and older spreadsheets (the .xls files) and not the newer Office 2007 .xlsx files.
In another post, I hope to cover other spreadsheet formats such as the Google Spreadhseet, Open Doc etc.
