ExportToSpreadsheet
This plugin allows to add some export functionalities to a Rails Application. The supported output formats are Microsoft Excel and Google Spreadsheets.
Dependencies
It requires both JAVA (JDK and JRE) and the following gems :
-
portablecontacts
-
google-spreadsheet-ruby
-
RJB
-
oauth
-
oauth-plugin
-
restful-authentication
-
highline
Apache POI is also necessary but provided with this plugin.
Depending on the target plateform (Linux, Mac or Windows), RJB could require some path configuration, see rjb.rubyforge.org/
For the documentation generation the yard gem is required : github.com/lsegal/yard
An optionnal rake task to clean the generated files was created : rake cron:tidy_exported_tmp_files
General installation remark
You need to set the JAVA_HOME environment variable either directly in your system or in a ruby file contained in config/initializers/ where you would write for example :
ENV['JAVA_HOME'] = "/opt/SDK/jdk"
In some OS (seen in Windows), you might also have to set the CLASS_PATH adding the POI jar files :
ENV['CLASS_PATH'] = .;D:\PATH_TO_APACHE_POI\lib\apache-poi-3.7\poi-3.7-beta2-20100630.jar;D:\PATH_TO_APACHE_POI\lib\apache-poi-3.7\poi-contrib-3.7-beta2-20100630.jar;D:\PATH_TO_APACHE_POI\lib\apache-poi-3.7\poi-ooxml-3.7-beta2-20100630.jar;D:\PATH_TO_APACHE_POI\lib\apache-poi-3.7\poi-ooxml-schemas-3.7-beta2-20100630.jar;D:\PATH_TO_APACHE_POI\lib\apache-poi-3.7\poi-scratchpad-3.7-beta2-20100630.jar;C:\Program Files (x86)\Java\jre6\lib\ext\QTJava.zip
After installing the oauth-plugin (for Google Spreadsheet support), you need to generate a consumer : (check oauth -plugin documentation for full details)
RAILS_ROOT/script/generate oauth_consumer
rake db:migrate
Installation on a production server with Apache and Passenger
Passenger has to be launched with the same user as the application files’ owner to be able to write files into RAILS_ROOT/tmp
To customize this, add the following line in /etc/apache2/mods-enabled/passenger.conf :
PassengerDefaultUser app_user
Tests
The tests of this plugin requires a Google account.
Getting started
A full sample that uses the plugin is available here: github.com/nimbleapps/export_to_spreadsheet_sample_app/
The tutorial below allows you to re-build this sample step by step.
Simple Excel export
Once the plugin is installed (see Installation section for that), you can do the following to get a valid Excel export in 10 steps :
1/ Create your Rails app :
rails my_application
2/ Add the reference to the gem in your config/environment.rb :
config.gem “export_to_spreadsheet”
3/ Generate a scaffold (for instance a User one) :
ruby script/generate scaffold User first_name:string last_name:string
4/ Perform the migration to create the SQL table :
rake db:migrate
5/ Launch your server, go to your app URL (localhost:3000/users)
6/ Use the interface to add some users.
7/ Add a class in your models to perform the export, this class has to include the plugin :
class UserExporter
include ExportToSpreadsheet
# Actually building the output file whatever the format
def compose_export
@doc.write do |d|
d.title_1("Sample Title User Export").title_2("We are going to export the users")
d.title_3("Date : " + Date.today.to_s)
d.newline.newline.freezepane
keys_array = ["First Name", "Last Name"]
values = []
User.all.each_with_index do |user,i|
values << [user.first_name, user.last_name]
end
d.h_table(values, keys_array, {:border_bottom => true})
d.newline.newline
end
@doc.save
end
end
8/ In the User controller, add a method for the export and the download of the document :
def export
# Option which is handled by default by the plugin
= {:filename => "Users Export Example"}
u_e = UserExporter.new
export = u_e.to_excel()
download export.path
end
private
def download(server_file_path)
send_file server_file_path
rescue => e
raise Exception, "File not found. Please ensure that your file was saved. Error was: #{e}"
end
9/ Modify the routes.rb file to map this new action :
map.resources :users, :collection => { :export => :get }
10/ Congratulations ! You can now export all the users in an Excel Spreadsheet using the link : localhost:3000/users/export
Advanced Spreadsheet export capabilities
Your first export worked. Now, what if you want to add a parameter to your export such as the number of users to export.
Let’s do this :
1/ Modify your export export action in the users controller :
def export
export_format = params[:export_format]
records_nb_to_export = params[:records_nb_to_export]
# first option which is handled by default by the plugin
= {:filename => "Users Export Example"}
# adding an option which is personalized in lib/personalized_export_properties
.merge!({:records_nb_to_export => records_nb_to_export})
u_e = UserExporter.new
if export_format == "excel"
export = u_e.to_excel()
download export.path
end
end
2/ Modify the routes to handle these new parameters
map.resources :users
map.connect 'users/export/:export_format/:records_nb_to_export', :controller => 'users', :action => 'export'
3/ Add a plugin customization to handle this records_nb_to_export parameter. To do so, add a file before_prepare_export.rb in your lib folder and add the following code :
module ExportToSpreadsheet
# Configures export before generating a document
def before_prepare_export_first(target, = {})
@records_nb_to_export = ( && ! .empty?) ? .delete(:records_nb_to_export) : nil
return
end
end
4/ Change the ExportUser class where the variable @records_nb_to_export is now available :
class UserExporter
include ExportToSpreadsheet
# Actually building the output file whatever the format
def compose_export
@doc.write do |d|
d.title_1("Sample Title User Export").title_2("We are going to export the first #{@records_nb_to_export} users")
d.title_3("Date : " + Date.today.to_s)
d.newline.newline.freezepane
keys_array = ["First Name", "Last Name"]
values = []
User.all.each_with_index do |user,i|
if i < @records_nb_to_export.to_i
values << [user.first_name, user.last_name]
else
break
end
end
d.h_table(values, keys_array, {:border_bottom => true})
d.newline.newline
end
@doc.save
end
end
5/ Congratulations ! You can now export some data based on any users input parameter following this example. To check the example, visit localhost:3000/users/export/excel/3 which will export the three first users.
Google Spreadsheet export
To perform the Google Spreadsheet export, you need to follow the bellow :
1/ Ensure the oauth_consumer script run properly. (See General installation remark)
2/ Follow the documentation of oauth and oauth-plugin gems to manage the oauth permission process. Among the required steps, don’t forget to add your application credentials (the credentials it uses to connect to Google Apps) in the file config/initializers/oauth_consumers.rb :
OAUTH_CREDENTIALS = {
:google => {
:key => 'your_key',
:secret => 'your_secret',
:scope => 'https://docs.google.com/feeds/ https://spreadsheets.google.com/feeds/ https://www.google.com/m8/feeds/ https://www.google.com/calendar/feeds/',
:options => {
:site => 'https://www.google.com',
:request_token_path => '/accounts/OAuthGetRequestToken',
:access_token_path => '/accounts/OAuthGetAccessToken',
:authorize_path => '/accounts/OAuthAuthorizeToken',
:signature_method => 'HMAC-SHA1'
}
}
}
3/ Modify the export action of the user controller :
def export
export_format = params[:export_format]
records_nb_to_export = params[:records_nb_to_export]
# first option which is handled by default by the plugin
= {:filename => "Users Export Example"}
# adding an option which is personalized in lib/personalized_export_properties
.merge!({:records_nb_to_export => records_nb_to_export})
u_e = UserExporter.new
if export_format == "google"
# assuming the user's token is the first one in the table
google_token_client = GoogleToken.first.client
export = u_e.to_google_spreadsheets({:access_token => google_token_client}.merge )
render :text => "Generation Complete. Check your Google Apps account."
elsif export_format == "excel"
export = u_e.to_excel()
download export.path
end
end
4/ Congratulations ! You can now export also spreadsheet in a Google account. To check the example, visit localhost:3000/users/export/google/3 which will export the three first users in a Google Spreadsheet.
Environments
Tested on Rails 2.3.8 and Ruby 1.8.7.
Version
0.1.1 (2011)
Disclaimer
This plugin was originaly written by Michel Pigassou and published by Nimble Apps Limited. www.salesclic.com
At the time of the publication, the core features of the plugin are fully functional. Yet, Nimble Apps does not consider it as complete. We are sure that many enhancements can be made to the plugin and hope you will contribute to it.
Licence
MIT license