#362 Exporting CSV and Excel

Thanks a lot!
For a pro episode about XLS you can maybe try https://github.com/randym/axlsx
Really useful for advanced excel usage.

I needed a simple, straightforward way of exporting AR collections to excel, and I ended up doing the to_xls gem.

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.

First Name,Last Name,Email Address,Status,Company,Last Login,Last IP

<% @users.each do |user| %>

<%= user.first_name %>,<%= user.last_name %>,<%= user.email %>,<%= "Active" if user.user_status %><%= "Inactive" if not user.user_status %>,<%= user.company.company_name %>,<%= time_ago_in_words(user.updated_at.localtime) %>,<%= user.ipaddress %>

<% end %>

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!

How about an episode on Importing csv/xls files to your app?

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?

Importing CSV files would be a great pro epsidoe also.

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?

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...

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'

   respond_to do |format|
      format.html
      format.csv {
        render_csv('myfile')
      }
  end 

Excel XML is known as SpreadsheetML

FAIR WARNING:

• SpreadsheetML file sizes will be about 3-4 times larger than native XLS files. Not really a problem for small files, but with large data sets this could eat up your system resources to generate the file.
• SpreadsheetML will only be readable by Microsoft Office. Other mainstream office suites will open it as an XML document.

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.

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

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).

BOM = "\377\376"
BOM + Iconv.conv('utf-16le', 'utf8', data)

XML spreadsheets MUST have the .xlsx extension

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") }

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?

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) :(

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?

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?

Hi

Can anyone tell how to export 100,000 records of data to csv in seconds.

Thanks in advance.

Anyone know how to solve encoding issues with xls files generated by the simple "html" definition of a table described in the cast?

I have problems on import in LibreOffice, MS Office with special characters and umlauts (§, ö etc.).

Use
csv << product.attributes.values

instead of:
csv << product.attributes.values_at(*column_names)

Anyone know how I can do a spec test about a csv export? Thanks

Is anyone else getting the message (when opening up in Windows Excel): 'The file you are trying to open, .... , is in a different format than specified by the file extension. etc..' ? I don't get this when using Mac Excel (2011), but it seems to be an issue with Windows Excel (2010). Any workarounds?

I have a document that has spanish characters, accents and ñ. Is there way ensure that these are exported correctly? Or better put, how can I change the encoding to 'Latin-1'?

I am pasting my code here, it does the following
- downloads the file, not only make it in file system
- libre complaint, also windows, and browser

Even though I dont like to write and delete , but with spreadsheet seems like the only solution.

puts "# write the file
    
book.write "Employee_History_#{ params[:id]}.xls"
    
# send the file
    
send_file "Employee_History_#{ params[:id]}.xls", :type => "application/vnd.ms-excel", :filename => "data.xls", :stream => false
    
# and then delete the file
    
File.delete("Employee_History_#{ params[:id]}.xls")"

I am pasting my code here, it does the following
- downloads the file, not only make it in file system
- libre complaint, also windows, and browser

Even though I dont like to write and delete , but with spreadsheet seems like the only solution.

puts "# write the file
    
book.write "Employee_History_#{ params[:id]}.xls"
    
# send the file
    
send_file "Employee_History_#{ params[:id]}.xls", :type => "application/vnd.ms-excel", :filename => "data.xls", :stream => false
    
# and then delete the file
    
File.delete("Employee_History_#{ params[:id]}.xls")"

Has anyone figured out how to insert a new line in a cell using the Microsoft XML approach?

Hi,
How can I format the index.xls.erb so that the xls which is exported has columns with different width and :word-wrap: normal ?
Thanks,
christina2013

Does anyone know how to add totals with this approach?