#362 Exporting CSV and Excel
Jul 02, 2012 | 6 minutes |
Views
As you will see it is easy to add a CSV export option to Rails. Here I also show how to export for Excel in a variety of formats.
- Download:
- source codeProject Files in Zip (56.3 KB)
- mp4Full Size H.264 Video (21 MB)
- m4vSmaller H.264 Video (8.94 MB)
- webmFull Size VP8 Video (9.65 MB)
- ogvFull Size Theora Video (22.1 MB)
Thanks a lot!
For a pro episode about XLS you can maybe try https://github.com/randym/axlsx
Really useful for advanced excel usage.
+1
+1
acts_as_xlsx is also a very quick way to get up and running with rails.
grab me on #axlsx if you have any questions.
Thanks Randy! You really helped us with that gem:)
Thanks Yannick. It has been a true community effort. Huge respect to the people out there who forked and added the feature they needed. I'd love to see a Pro cast showing chart generation, multiple sheets, styled and localized headers and all that jazz. For now, here is a blog post showing how easy it is.
http://axlsx.blogspot.jp/2011/12/using-actsasxlsx-to-generate-excel-data.html
+1 for axlsx cast
Can we have subsheets under worksheet in axlsx? Let me know.
+1 axlsx cast. It saved my time
This is great! Does anyone know about similar gems for Open Document?
This is the only one I know about - not quite as smooth of an API but if you are dealing with a fixed template it is great.
https://www.ruby-toolbox.com/projects/odf-report
+1, we use axlsx for reporting and found it very helpful. Also Randy has been very responsive in case of questions!
Thanks guys for AXLSX ! It perfectly suits my needs !
+1 for axlsx
I needed a simple, straightforward way of exporting AR collections to excel, and I ended up doing the to_xls gem.
This is the to_xls gem that I'm using. It's perfect for spitting out simple arrays with limited formatting but unable to tackle anything more involved.
https://github.com/splendeo/to_xls
An example of my use:
http://stackoverflow.com/questions/11112581/calling-an-activerecord-association-method-from-an-array-in-rails-3-2
Mr. Bates, thank you!
I thought that I need some heavy gem to be able to generate medium complex XLS files. I am so glad you showed me wrong, I am up and running without any additional dependency. Btw. you could post a link to that XML reference you mentioned in Show Notes:
http://msdn.microsoft.com/en-us/library/aa140066%28v=office.10%29
The only issue that I saw with the CSV export was that it would return all data from that table. I put it on a user list and has to make a index.csv.erb file that would post just the required data.
Not a big deal since I was able to put in whatever information I wanted.
generate_csv([:id, :username], members, options[:csv])
While it is primarily a tool for making PDFs, you can also use DocRaptor to generate XLS, if what you need is more involved. Being able to select pdf instead of xls without too much additional effort is a nice bonus, and it is also available as a heroku addon. Yes, there is a small document fee after the first five, so clearly not suitable for everything.
Another useful idea for a "Pro" episode on this topic would be generating the document while using pagination for just a page of data, or after filtering results and exporting a document with just those results.
Awesome episode as always though, thanks!
Well that's maybe a bit short for a "Pro", you just need a few lines to do that ;)
I would like to export the filtered results, as well. What are those few lines I'd need? Appreciate the help...
How about an episode on Importing csv/xls files to your app?
+1
+1
+1
Yes ++
I need it all the time when a customer has a huge amount of legacy date in XLS format they want to move online
+1
i need it..
I need it too
+1
I use the Roo gem to import data in .xls files.
See http://roo.rubyforge.org/ -and- https://github.com/hmcgowan/roo
+1 Please do this Ryan... I'm a pro customer too : ).
in an index view that already does sorting and filtering, stuck on one point: creating a link_to that passes those parameters back to index.
so from http://0.0.0.0:3000/users/198/contacts?letter=C
this link_to
<%= link_to "Excel", user_contacts_path(format: 'xls') %>
passes
{"action"=>"index", "controller"=>"contacts", "user_id"=>"198"}
How do I preserve my variable number of parameters?
You can use
<%= link_to "Excel", user_contacts_path(params.merge(format: 'xls')) %>
and this will maintain the current parameters you have plus the ones you add or edit inside merge.
A bit belated, but I just implemented this feature into a new app I am working on and was looking for how to download only records in the current view or in a query. +1 for your tip above. Thank you. It works! It works!
I was banging my head on how to preserve my search criteria into the CSV/XLS export. This worked great! Thanks for saving me the headache. I owe you Starbucks.
Hi. Does this work only for xls? I am trying to do the same thing for 'csv' but it does not seem to work. Would really appreciate some help on this.
<%= link_to "Excel", user_contacts_path(format: 'xls'), :letter => 'c' %>
should do it...
Importing CSV files would be a great pro epsidoe also.
+1
+1
I am using will_paginate in a search page. After restricting the number of records according to the search form fields I am calling paginate on them.
When I export to CSV I only export the current number of records on the page as defined in :per_page. How do I trick my controller to export all records when export to CSV?
apply the pagination until the format.html inside respond_to
format.html { over here }
this way format.csv is not affected by paging
Erick, is there any way you could provide an example of your suggestion? I'm also using pagination and would like the exported files to contain only the search results. (Not just the first 25 results as defined in my controller.)
Keep in mind that Excel XML output is not readable by Apple Numbers.
Numbers will display the raw XML. It will take the older 'native' Excel format.
Come on, Apple, I want to like the program but the last update was 2009...
While there are some things like graphs that do not interop very well, I have never experienced Numbers opening up an xlsx file as raw XML. Interested to hear what version of Numbers you are working with. I Currently using 1.2 (436) for confirming https://github.com/randym/axlsx interoperability with LibraOffice and Numbers
Numbers '09 version 2.1 (436) ... should be the latest (did you transpose 1 and 2?)
yes, you are quite right. the version numbers where transposed. sorry about that That version supports a considerable amount of the ECMA-376 specification - also known as office open xml. what did you use to generate your data that opened raw xml? I think you'll find axlsx to actually work. we've been sucessful in interop with Numbers and LibreOffice and expect to release google docs operability this week.
We had the same error described above in a legacy App, but the cause was that the XML is sent with an extension of XLS not XLSX.
Changing the extension to .XLSX solved it for us.
In general I find using a csv view template to be more useful that a to_csv method in a model.
The problem with the view template is that the file will be downloaded with the last component of the URL as the name.
For example from a Show action with a URL like /posts/2.csv you'll get a file called 2.csv, which is not very friendly
Clinton Nixon has a nice solution in this stackoverflow post
http://stackoverflow.com/questions/94502/in-rails-how-to-return-records-as-a-csv-file
He creates a render_csv method in the application controller that adds headers to the response that use either the user supplied filename or defaults to the action name
For example, this produces a file called 'myfile.csv'
Excel XML is known as SpreadsheetML
FAIR WARNING:
The CSV approach fits my needs 9 times out of 10. However if you require a native Excel format, please use a gem and avoid using the SpreadsheetML method.
Excel will also always throw a "unreadable content" error when opening a SpreadsheetML file. It's just a crappy user experience all the way around; do not want.
+1
A painful memory while you are exporting 50K above of row in SpreadsheetML
And this occupy one of the worker while in production.
Even you finish export with SpreadsheetML, your customer may can't open it because it is too big and eat up the memory in excel.
I end up writing a rake task and put that in background with spreadsheet gem.
Smaller output and better user experience.
May be I am doing it wrong but that's my experience.
Hi ano1chan
I am having the same Issue now , i am exporting excel files with large records , consuming my all memory, can you help ?
I haven't used it, but this looks like a viable option for more complex needs where you really want to use a template approach:
https://github.com/dasil003/csv_builder
I followed this RailsCast tutorial, then later discovered csv_builder. Like you said, the template/view approach is much better! I think this RailsCast should be revised to use csv_builder rather than putting CSV view logic in the Controller as it recommends!
Note new repo URL: https://github.com/gtd/csv_builder
It is possible to get around the encoding issues for csv-formatted excel files: use UTF-16 little endian encoding (with a byte order mark).
Where did you put that code?
XML spreadsheets MUST have the
.xlsx
extensionMaybe that's changed. In Office 2013 I can open the XML file (with a warning) if it has a .xml extension, but Excel rejects it completely if I rename it to .xlsx.
Remember to set the
Content-Disposition
header for such downloads.Using send_data method I can pass filename like that
format.csv { send_data @attendees.to_csv, {filename: filename}}
How can I change the default filename for xls without using send_date?
format.xls # { send_data @products.to_csv(col_sep: "\t") }
want to know that as well... did you find a solution?
I'd like to know this as well.
Put it before your render ;)
Thank you very much Esteban. It was just what I was looking for.
Genius, thank you very much.
Can anyone explain why the class method Products::to_csv is successfully getting called from the controller code below?
@products = Product.order(:name)
send_data @products.to_csv
@products is not the Products class object, but the method gets called. Then inside to_csv, it calls the all method. Does that repeat the query? or does it somehow apply all to the array @products?
To make it even more confusing, if I repeat this setup with an arbitrary method name instead of to_csv inside the rails console, I get NoMethodError. What's going on?
That is because
@products.class
isArray
, whichresponds_to? :to_csv
. I guess you should call to_csv or whatever method name you have as class method:send_data Products.order(:name).to_csv
For me Products::to_csv did not successfully get called. Instead, a comma separated list of objecs was rendered. e.g
#<MyObject:0x007fb05ee9eb00>,#<MyObject:0x007fb05ee9e4e8>
(which is the same output as when there is nodef self.to_csv
)I was only able to use this design by changing
format.csv { send_data @products.to_csv }
to
format.csv { send_data Product.to_csv(@products) }
ruby-1.9.3-p125
Rails 3.2.8
I had the same issue but your change gave me the error:
can't convert Array into Hash
format.csv { send_data Product.to_csv }
worked for me.Thank you, I had to do the same.
I got the same "can't convert Array into Hash" error from
but it seems to work if you pass @products as a variable into the model by adding it in the parentheses and in the .each loop as I'm passing all_products in below.
The advantage is that you can define @products in the controller in order to only get the products you want such as in this example:
Thank you, I had run into the same issue. This fixed it.
thanks @lucascioffi
Update: To make this work in XLS format, we need to pass in the tab character, so I modified my controller to look like this:
And then this is how product.rb receives the call of .to_csv with the tab character and @products passed in as variables from the controller:
I tried following this screencast and gave up because I was running into an issue identical to what Andy Weber found.
Am currently using the following code, which does work:
format.csv { render :text => Product.to_csv }
I ran into the exact same issue. Thanks for sharing, really helped me out.
I think you define products as following:
@products = Product.all
@products.class #---> Array
but when you fetch data by calling order():
@products = Product.order(:name)
@products.class #---> ActiveRecord::Relation
in this case you do not have to call Product twice, like Product.to_csv.
Does anybody know how I can rename the .xls file when using
respond_to do |format|
format.html
format.xls
end
The file is always called as the controller actions...
thanks !
You might be interested in this gem I made called CSV shaper that allows you to create CSV output using a really nice Ruby DSL.
https://github.com/paulspringett/csv_shaper
I'm having a problem with the xls.erb file. HTML or XML, when I open it in excel, the file isn't being parsed. I just see all the code, line by line, the way it is in the erb file. However, any of the erb lines, like <% if ... %> statements are correctly interpreted.
I've done the mime type in the initializer as well.
Any help?
How to use subsheets in a worksheet. Any idea?
Thanks
Great!
Knight's Spreadsheets (Ruby) section compiles a list of gems that can help with parsing and writing Spreadsheets and CSV. Knight.io is a gem wiki for developers, a current project of mine.
not working after joins
is there a way to insert images on the "non dependency way" to export xls with html in the file?
my images dont work fine, i think i can't do this by this way.
Wonderful cast! I used roo to import some legency-excel data. Exporting excel is useful in rails application.
Thanks a lot!
Not working with Office in Windows and Google Docs.
Office in Windows and Google Docs don't show de spreadsheet (corrupted file) :(
Same for me. File is corrupted. "General input/output" error.
I am using liber-office on Debian Ubuntu 12.04. Dont know why its corrupted.
I am not sure why this is happening but .xls is showing up in the browser window and not downloading.
Here's another nice gem that can handle exporting data to CSV, Excel, JSON, and HTML:
Dossier
https://github.com/adamhunter/dossier
Is there a way to follow this same direction for a partial? My view starts with a form that passes params back to the same URI which formats a partial. I'm struggling to make this lesson translate to my scenario. Advice? Ideas?
see this SO question for details on my problem: http://stackoverflow.com/questions/15121588/rails-3-export-csv-from-partial
File is corrupted. "General input/output" error.
I am using liber-office on Debian Ubuntu 12.04. Dont know why its corrupted.
For CSV files, I was able to set the send_data type param as "text/csv". For the XLS xlm schema approach, are you all getting the file downloaded to have the .xls extension? My filename is just the name of the view, without extension.
Thanks
JavaScript way, for table:
http://datatables.net/extras/tabletools/
Followed the cast and for some reason when it exports CSV file, i get all records in one row and each cell is #User:0x007fb87415c460 and etc...any idea what could be causing this?
Might want to try something like this, Al. (Stolen from a post above.)
format.csv { render :text => Product.to_csv }
Hi
Can anyone tell how to export 100,000 records of data to csv in seco