Export to a CSV or XLS file

Rails
1

hello everyone,

Any idea how can i export a resultset from a database to a CSV or XLS
file from the application ? would like the user to have the option to
export selected data to a CSV/XLS file.

Any plugins or libraries avaiable to accomplish this ?

Thanks for your time,
VASU.

2

http://blog.teksol.info/articles/2006/03/23/returning-csv-data-to-the-browser

-Jonathan.

3

VASU wrote:

Hi Vasu,

CSV read and write support is built-in to Ruby and therefore Rails as
well.

look at:
http://stdlib.rubyonrails.org/libdoc/csv/rdoc/index.html

There is a gem called FasterCSV is CSV is too slow for you.

I think there is XLS support as well.

Jason

4

On 7/29/06, Jason E. [email protected] wrote:

VASU wrote:
I think there is XLS support as well.

You just make a CSV file and give it a XLS ending.

–

5

The Following gets you and xml Excel file. So the client needs a
newer version of excel.

in your controller
def export
@name = “Decay”
@items = LkDecayClassDwd.find(:all)
headers[‘Content-Type’] = “application/vnd.ms-excel”
headers[‘Content-Disposition’] = ‘attachment; filename="’+@name
+‘-’+Date.today.strftime+‘.xls"’
headers[‘Cache-Control’] = ‘’
render :layout => false , :template => “shared/export”
end

as export.rxml view

xml.instruct! :xml, :version=>“1.0”, :encoding=>“UTF-8”
xml.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:html’ => “HTML 4.01 Specification”,
‘xmlns:ss’ => “urn:schemas-microsoft-com:office:spreadsheet”
}) do

xml.Styles do
xml.Style ‘ss:ID’ => ‘Default’, ‘ss:Name’ => ‘Normal’ do
xml.Alignment ‘ss:Vertical’ => ‘Bottom’
xml.Borders
xml.Font ‘ss:FontName’ => ‘Verdana’
xml.Interior
xml.NumberFormat
xml.Protection
end
xml.Style ‘ss:ID’ => ‘s22’ do
xml.NumberFormat ‘ss:Format’ => ‘General Date’
end
end

xml.Worksheet ‘ss:Name’ => @name do
xml.Table do
# Header
xml.Row do
get_column_names_in_hash(@items[0],Hash.new).sort.each do |key,value|
xml.Cell do
xml.Data value, ‘ss:Type’ => ‘String’
end
end
end
# Rows
for item in @items
xml.Row do
get_columns_in_hash(item,Hash.new).sort.each do |key,value|
xml.Cell do
xml.Data value, ‘ss:Type’ => ‘String’
end
end
end
end
end
end
end

and in the helper or you could replace the above calls to
get_column_names_in_hash and get_columns_in_hash hashes of each row

the following two functions make use of strict adherence to the

rails naming conventions for table and
#column names. Columns that end in _id are assumned to connect to
tables that are of the same name as foreign
#keys. This allows for the returning of all connected data for a
table recored to be returned. For any

record you can find related tables by looking for columns that

end in _id. For each of these related tables

you can return their data and any related table. Using this you

can retreive all the related data for a record

as long as it is up the tree. Data that belongs to tables that

connect back to this table are not found because

this table does not know of the relationship

def get_columns_in_hash(item,my_hash)
for column in item.class.columns do
if column.name =~ /(.*)(id)$/
my_hash.merge!( get_columns_in_hash(item.send
(Inflector.underscore(Regexp.last_match(1))),my_hash))
elsif column.name =~ /(id)$/
nil
else
my_hash[Inflector.underscore(item.class)+''+column.name]=
item.send(column.name)
end
end
my_hash
end

def get_column_names_in_hash(item,my_hash)
for column in item.class.columns do
if column.name =~ /(.*)(id)$/
my_hash.merge!( get_column_names_in_hash(item.send
(Inflector.underscore(Regexp.last_match(1))),my_hash))
elsif column.name =~ /(id)$/
nil
else
my_hash[Inflector.underscore(item.class)+'‘+column.name]=
Inflector.underscore(item.class)+’_'+column.name
end
end
my_hash
end

6

I have a plugin that handles making Excel sheets from your Rails apps.

http://www.napcsweb.com/blog/2006/02/10/excel-plugin-10/

You make a workbook and then add worksheets to it.

Simple example
Let’s assume we have two models… a Project and a Task.
A Project has_many :tasks.
In one of our controllers, we can create the following method which
will stream a new Microsoft Excel document to the client’s browser.

def export_project_to_excel
e = Excel::Workbook
@project = Project.find(:all)
@tasks = @project.tasks
e.addWorksheetFromActiveRecord “Project”, “project”, @project
e.addWorksheetFromActiveRecord “Tasks”, “task”, @tasks
headers[‘Content-Type’] = “application/vnd.ms-excel”
render_text(e.build)
end

More Advanced example
This time, let’s create an array of hashes. This way, we can
manipulate our data ourselves, instead
of letting the plugin do the mapping. This is really useful when you
have “has_many” or “belongs to”
relationships and you want to export meaningful values instead of the
foreign keys.

def export_book_info_to_excel
books = Book.find(:all)
array = Array.new
for book in books
item = Hash.new
item[“Title”] = book.title
item[“ISBN”] = book.isbn
item[“Author”] = book.author.last_name
item[“Category”] = book.category.name
item[“Total Sales”] = book.sales.size
array << item
end
addWorksheetFromArrayOfHashes(“Books info”, array)
headers[‘Content-Type’] = “application/vnd.ms-excel”
render_text(e.build)
end

7

Hi,
Is there any way to size the columns in the XLS file to some specified
width and how to format some of the text to be displayed as BOLD or in
any other format.

Thanks in advance for the help.

Neha

8

Thanks Guys !! I really appreciate all the response. Will check them out
and get back to this forum with any questions.

Thx,
VASU

Brian H. wrote:

I have a plugin that handles making Excel sheets from your Rails apps.

http://www.napcsweb.com/blog/2006/02/10/excel-plugin-10/

You make a workbook and then add worksheets to it.

Simple example
Let’s assume we have two models… a Project and a Task.
A Project has_many :tasks.
In one of our controllers, we can create the following method which
will stream a new Microsoft Excel document to the client’s browser.

def export_project_to_excel
e = Excel::Workbook
@project = Project.find(:all)
@tasks = @project.tasks
e.addWorksheetFromActiveRecord “Project”, “project”, @project
e.addWorksheetFromActiveRecord “Tasks”, “task”, @tasks
headers[‘Content-Type’] = “application/vnd.ms-excel”
render_text(e.build)
end

More Advanced example
This time, let’s create an array of hashes. This way, we can
manipulate our data ourselves, instead
of letting the plugin do the mapping. This is really useful when you
have “has_many” or “belongs to”
relationships and you want to export meaningful values instead of the
foreign keys.

def export_book_info_to_excel
books = Book.find(:all)
array = Array.new
for book in books
item = Hash.new
item[“Title”] = book.title
item[“ISBN”] = book.isbn
item[“Author”] = book.author.last_name
item[“Category”] = book.category.name
item[“Total Sales”] = book.sales.size
array << item
end
addWorksheetFromArrayOfHashes(“Books info”, array)
headers[‘Content-Type’] = “application/vnd.ms-excel”
render_text(e.build)
end

9

You can save XLS files as an HTML spread sheet which you can then make
dynamic with Rails and it will load back into Excel like a champ. I
have done this before.

On Mar 22, 6:40 pm, Neha C. [email protected]

10

On a related topic, does anyone know about saving to the new xlsx or
xlsm formats used by Excel 2007? I heard that they were supposed to be
open standards
.