#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?

Hello,
I had an application with rails 4 & rubyzip gem installed.
I have 3 tables - students, exams & results.

I followed this video http://railscasts.com/episodes/362-exporting-csv-and-excel

I'm able to download individual table csv files but not all 3 at a single click.

And I want to download all these 3 table csv files as a zip file with rubyzip or something else.

Please help me. I searched every end of google but didn't find an accurate result.

I am running Excel 2007 and the XLS/XML format described in this Railscast doesn't work. I copied it over carefully. Excel 2007 just says that there's something wrong with the "Table" format but doesn't tell me what. It says to see a log file in a long path name it gives me for the details, but that path doesn't even exist (thanks Microsoft). Has anyone gotten an XLS format to work on Windows?

I'm having a problem with many browsers opening the file instead of downloading it.

The best solution so far has been to use send_data and set ' disposition: "attachment; filename=A_COOL_TITLE" instead

respond_to do |format|
      format.html
      format.csv { 
        send_data(
          @products.to_csv, 
          disposition: "attachment; filename=A_COOL_TITLE",
          type: 'text/csv',
          stream: 'true', 
          buffer_size: '4096' 
        )

      }
end

I also found that turbo-link can cause issues so I added 'data-no-turbolink' => true to the download link

This doesn't seem to work at all - file download or not. No matter what I do - even with the most basic example 100% identical to the shown source code - I get a "can’t be opened" error with Numbers (have not tried with windows). Just going to have to find a different solution entirely.

For those who have need PDFtoXLS...

https://github.com/Ricardosluiz/Ruby_on_Rails-PDFtoXLS

The XML Spreadsheet does not work. :(
My OS X 10.02 and Numbers version: 3.5
what i can see in Numbers are the following
<?xml version="1.0"?>

Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:o="urn:schemas-microsoft-com:office:office"

xmlns:x="urn:schemas-microsoft-com:office:excel"

xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:html="">http://www.w3.org/TR/REC-html40">

Worksheet ss:Name="Sheet1">

Table>

Row>

ID

Name

Release Date

Price

/Row>
........

What if we have something like

class CouponController < ApplicationController
  def index
    @coupons = Coupon.all.page(params[:page])
    @coupons = @coupons.for_amount(params[:amount]) if params[:amount].present?
    @coupons = PaginatingDecorator.new(@coupons)

    respond_with(@coupons) do |format|
      format.html
      format.csv { render :text => coupons_csv }
    end
  end

 private

  def coupons_csv
    column_names = %w(code name id amount expiration_date)
    CSV.generate do |csv|
      csv << column_names
      @coupons.each do |coupon|
        csv << column_names.map{ |attr| coupon.public_send(attr) }
      end
    end
  end

Here in coupons_csv I'll not receive the entire coupon list - I'll receive only the paginated set.So what would be the best way to get ALL filtered coupons into the csv and get per-page#paginated coupons in the index page?

Thanks you so much,saved a lot of time for a feature I had to add.
Wondering if there is a way for generating the same .csv file so it would be saved in the rails public direcoty instead.
I have a script I wrote for that csv file
help will be much appriciated,thanks

This gem may be helpful, pretty useful julia_builder

You can use this gem too

if you simply need to export data as admin to excel you can use this gem https://github.com/igorkasyanchuk/rails_db to excel or csv

This episode has been updated to Rails 5 as a blog post Exporting Records in CSV and Excel Formats in Rails 5

I know this is a pretty old thread, but I had a question about updating inputted files. I'm at the point where I can get files (only working with CSV right now) to update (adding new entries and updating existing ones) but I've noticed that deleting entries from the CSV file does not delete from the database. Would I have to write code to check each entry in the database with each on in the CSV?