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. -- Posted via http://www.ruby-forum.com/.
http://blog.teksol.info/articles/2006/03/23/returning-csv-data-to-the-browser -Jonathan. On 7/29/06, VASU <vasuboy@gmail.com> wrote:> 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. > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
VASU wrote:> 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. > >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
On 7/29/06, Jason Edgecombe <jedgecombe@carolina.rr.com> wrote:> VASU wrote: > I think there is XLS support as well.You just make a CSV file and give it a XLS ending. -- -------------- Jon Gretar Borgthorsson http://www.jongretar.net/
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'' =>
"http://www.w3.org/TR/REC-html40",
''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
On Jul 28, 2006, at 9:50 PM, VASU wrote:
> 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.
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
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
On 7/31/06, Erik Ordway <ordwaye@evergreen.edu>
wrote:>
> 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'' =>
"http://www.w3.org/TR/REC-html40",
> ''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
>
>
>
>
> On Jul 28, 2006, at 9:50 PM, VASU wrote:
>
> > 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.
> >
> > --
> > Posted via http://www.ruby-forum.com/.
> > _______________________________________________
> > Rails mailing list
> > Rails@lists.rubyonrails.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060731/986f9542/attachment-0001.html
Thanks Guys !! I really appreciate all the response. Will check them out and get back to this forum with any questions. Thx, VASU Brian Hogan 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-- Posted via http://www.ruby-forum.com/.
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 -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 Chopra <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> 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 > > -- > Posted viahttp://www.ruby-forum.com/.--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 . On Mar 22, 9:43 pm, "dysinger" <dysin...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> 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 Chopra <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> > wrote: > > > > > 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 > > > -- > > Posted viahttp://www.ruby-forum.com/.- Hide quoted text - > > - Show quoted text ---~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---