We were asked to add a "Download as CSV" link to the top of each of our reporting actions. We already had a "revenue_report" controller-action with it's own html view template... and just wanted to add something similar in CSV.
FasterCSV seemed to provide some great CSV-generation functionality... but it builds the csv and spits it out right there in the controller.
uuuuugly!
We shouldn't put view-generation code into the controllers - that splits up our nicely-decoupled MVC stack!
I've also seen some ideas about putting a to_csv method into an individual model. Now, if I'm leery about putting view-generation in the controller... I'm even less impressed by putting it into the model! But I can see the benefits of simplicity.
However - I'm still not sure it fits with our requirements. A @widget.to_csv call works just fine for the WidgetsController#show action... and probably even the WidgetsController#index action (where you can run @widgets.map &:to_csv or similar)... but most of our reports span multiple associations and the data is grouped, filtered, and summarised. Each of these sets needs headings explaining what it is and laying it out nicely in a way that makes sense to the user reading the CSV file. Putting that kind of visual-only functionality in your model is where it really starts to get ugly again. I am left with one big thought:
Why can't I just put my CSV-template into the views directory?
enter: csv_builder plugin
csv_builder comes with extremely minimal documentation... but it's not that hard to use. Here's a step-by-step with examples to follow:
- install fastercsv and csv_builder
- add a .csv option to the respond_to part of your action
- add your <actionname>.csv.csvbuilder template into your views directory
- Add a "Download as CSV" link for your user
Step 1: Install FasterCSV & csv_builder
sudo gem install fastercsv ./script/plugin install git://github.com/econsultancy/csv_builder.git
Don't forget to restart your server!
csv_builder has already set up a format-extension handler for csv so you don't need to add it to the mime-types yourself, just start using it in your actions.
Step 2: In your controller action:
csv_builder will work out of the box even if you just add the simplest possible respond_to statement:
respond_to |format| do
format.html # revenue_report.html.erb
format.xml { render :xml => @payments }
format.csv # revenue_report.csv.csvbuilder
end
With nothing more than the above, csv_builder will grab out your template and render it in csv-format to the user.
You can tweak the FasterCSV options by setting some @-params in your action. The csv_builder gem README explains how to use those, but the most useful would be to set @filename to give your users a meaningful filename for their csv-file. You can also specify things like the file-encoding, but go look at the gem README to find out the details
Here's an example of a full controller action:
# GET /sites/1/revenue_report
# GET /sites/1/revenue_report.xml
# GET /sites/1/revenue_report.csv
def revenue_report
@site = Site.find(params[:id])
@payments = @site.revenue_report_payments
respond_to do |format|
format.html # revenue_report.html.erb
format.xml { render :xml => @payments }
format.csv do
# timestamping your files is a nice idea if the user runs this action more than once...
timestamp = Time.now.strftime('%Y-%m-%d_%H:%M:%S')
# passing a meaningful filename is a nice touch
@filename = "revenue_report_for_site_#{@site.to_param}_#{timestamp}.csv"
end # revenue_report.csv.csvbuilder
end
end
Step 3: add a template into your views directory
The final step is to add a .csv.csvbuilder template to your views directory. This is just like adding an html.erb template for your action, except that the file will have the extension .csv.csvbuilder. AFAIK csv_builder can only support template that have the same name as your action, so in my example the template would be called revenue_report.csv.csvbuilder
Your view template is where you can stash away all the FasterCSV guff that will build your csv file. This works the same way as any other FasterCSV-generated content - but just make sure you generate it into a magic array that is named 'csv'. Here's an example:
# header row
csv << ["Revenue report for site: #{@site.name}"]
csv << [] # gap between header and data for visibility
# header-row for the data
csv << ["Date", "", "Amt (ex tax)", "Amt (inc tax)"]
row_data = [] # for scoping
@payments.each do |payment|
row_data = [payment.created_at.to_s(:short)]
row_data << "" # column gap for visibility
# note you can use the usual view-helpers
row_data << number_to_currency(payment.amount_ex_tax)
row_data << number_to_currency(payment.amount_inc_tax)
# and don't forget to add the row to the csv
csv << row_data.dup # it breaks if you don't dup
end # each date in date-range
csv << [] # gap for visbility
# and the totals-row at the very bottom
totals_data = ["Totals", ""] # don't forget the column-gap
totals_data << @payments.sum &:amount_ex_tax
totals_data << @payments.sum &:amount_inc_tax
csv << totals_data
Step 4: add a CSV link
Something like the above code will generate a nice plain csv file and spit it out at the user in the correct encoding... but now we need something to actually let the user know that they can do this.
This is pretty simple - it just requires adding the format to your usual path-links. eg:
<%= link_to 'Download Report as CSV', site_revenue_report_path(:id => @site.to_param, :format => :csv) -%>
Testing
Testing for non-html formats still seems pretty crude. You have to manually insert the "accepts" header in the request before firing off the actual request - then manually check the response content_type. It'd be nice if we could just add :format => :csv to the request... anyway, here's a sample shoulda-style test case:
context "CSV revenue_report" do
setup do
@request.accept = 'text/csv'
get :revenue_report, :id => @site.to_param
end
# variables common to all formats of revenue-report
should_assign_to :site
should_assign_to :payments
should_respond_with :success
should "respond with csv" do
assert_equal 'text/csv', @response.content_type
end
end # context - CSV for revenue report
Gotchas:
Don't forget to add config.gem!"
I fond I needed config.gem 'fastercsv' and a require 'csv_builder' in my environment.rb. You may need a gem-listing for both (esp if you're using bundler)
Rails 3 compatitbility?
The original csv_builder plugin is not rails 3 compatible and is in fact no longer being supported. But it has officially changed hands, to the newer csv_builder gem. This is Rails-3 compatible and not backwards compatible - though he maintains a 2.3.X branch for people to submit bugs.
csvbuilder or csv_builder
While the plugin is named csv_builder, be careful to name your files with the extension: csv.csvbuilder or you'll spend hours pulling your hair out about a Missing template error while it fruitlessly searches for your csv.csvbuilder file!
All your report data must be generated in the controller!
This makes you more MVC-compliant anyway, but if you had any collections being fetched or formatted in the view... now's the time to move them into the controller action as your CSV-view will need them too.
duplicate inserted arrays
You'll notice that the example template has a set of data being added to the csv array... you'll also notice that each row is generated on the fly - then I save a *duplicated* version into the csv array. If you don't duplicate it... you may do funky things with overwriting the row each time. In my case I also needed to declare the temporary array outside the loop in order to preserve scope. YMMV - I'd appreciate any Ruby-guru answer as to why this doesn't work without that.
don't reset csv
Don't do this: csv = [] it breaks everything!
Rendering as html
I've noticed there's a pathalogical condition where everything is going well - and it's even getting o the template... but it's *still8 rendering html.
I eventually figured out that it's actually rendering the layout around the csv... and it occurs when you've explicitly stated a layout (eg layout 'admin') somewhere at the top of your controller. To fix it, you just need an empty render without layout false eg
respond_to do |format|
format.csv do
timestamp = Time.now.strftime('%Y-%m-%d_%H:%M:%S')
@filename = "revenue_report_for_site_#{@site.to_param}_#{timestamp}.csv"
render :layout => false # add this to override a specifically-stated layout
end # revenue_report.csv.csvbuilder
end
