Active Scaffold Export to Excel.

[PResines]

I modified Active Scaffold Export so it allows you to specify Excel
output as an option.
Is there any interest on adding this to the oficial version? Where can
I submit it?

Thanks,

[Ana]

Will you send me a copy/diff?

[Daniel E]

What exactly does "Excel output" mean? In the current version, if you
attempt to open a .csv file in Excel, it will be opened properly.

[PResines]

CSV output to excel has several drawbacks.

First, Excel does not handle Unicode chars in CSV files correctly, as
it expects it to be ANSI. And I couldn't make Rails to output ANSI.

Second, Excel tryes to interpret every cell after importing so if you
have a cell with, say, "1-2" Excel will change it to "Jan-2".

Third, Excel format (it is really excel XML format) allows you to
format output with borders, color, etc. Right now I only format the
header row, but I am looking into integrating the current support of
column helpers so you can have realy good formatted output.

[PResines]

By the way, anyone has noticed that after you do a Search with
Activescaffold, the export plugin only exports the records that where
returned from the search even if you have since resetted the search
form?

[PResines]

I am fixing some details and then send you a diff.

[Daniel E]

I'll be happy to accept a patch for providing an Excel XML format.
I haven't noticed a sticky search resultset. I'll take a look at that.

[Bonefish]

[Bonefish]

Hi Daniel E and PResines,

Firstly, Daniel, thanks for your nice work on the Export Plugin.

I've also done some work on this XML Export (patch below). We did it
for a Streamlined-based application and we integrated it into the
current release of Streamlined, but it's also working well in an
ActiveScaffold application we've written.

You can render the XML output to the browser or to a file. The file
option uses an xsl stylesheet for the formatting mentioned previously
in the thread. This stylesheet must be exported first and may be
customized as appropriate for use in all future exports. There is
also a Raw XML option to provide access to the model/db data if
needed.

Excel can open the XML file and automatically applies the styling in
the stylesheet. For OpenOffice you need to open the XML file in the
browser, save that as an HTML file and open that in OpenOffice.
OpenOffice unfortunately (and ironically) doesn't like XML files.

Notes:
This patch:
1. uses the list columns rather than content_columns so the output
mirrors the list
2. is localized to English
3. is against .../branches/AS_1.0.1_compatible/ (though the relevant
code is easy to extract and put into trunk.)
4. outputs all associations, not just the first 3

Index: frontends/default/views/_show_export.rhtml
===================================================================
--- frontends/default/views/_show_export.rhtml (revision 17)
+++ frontends/default/views/_show_export.rhtml (working copy)
@@ -1,10 +1,10 @@
<%

- url_options = params_for(:action => :export, :format => 'csv')

+ url_options = params_for(:action => :export)

export_config = active_scaffold_config.export

-%>

<%= form_tag url_for(url_options)%>

-<h3>Colonnes &agrave; exporter</h3>

+<h3>Columns to export</h3>

<p>

<% export_config.columns.each do |column| -%>

<%= content_tag(:label,
check_box_tag("export_columns[#{column.name}]", 1, !
export_config.default_deselected_columns.include?(column.name)) +
"&nbsp;#{column.label}") %>

@@ -13,11 +13,19 @@
<br/>

<h3>Options</h3>

<p>

-<%= content_tag(:label, check_box_tag('skip_header', 1,
export_config.default_skip_header) + '&nbsp;Sans&nbsp;entete') %>

-<%= content_tag(:label, text_field_tag('delimiter',
export_config.default_delimiter, :size => 1, :maxlength => 1) +
'&nbsp;Delimiteur') %>

-<%= content_tag(:label, radio_button_tag('full_download', false, !
export_config.default_full_download) + '&nbsp;Cette&nbsp;Page') if
export_config.allow_full_download %>

-<%= content_tag(:label, radio_button_tag('full_download', true,
export_config.default_full_download) +
'&nbsp;Toutes&nbsp;les&nbsp;Pages') if
export_config.allow_full_download %>

+<%= content_tag(:label, check_box_tag('skip_header', 1,
export_config.default_skip_header) + '&nbsp;Without&nbsp;header') %>

+<%= content_tag(:label, text_field_tag('delimiter',
export_config.default_delimiter, :size => 1, :maxlength => 1) +
'&nbsp;Delimiter') %>

+<%= content_tag(:label, radio_button_tag('full_download', false, !
export_config.default_full_download) + '&nbsp;This&nbsp;Page') if
export_config.allow_full_download %>

+<%= content_tag(:label, radio_button_tag('full_download', true,
export_config.default_full_download) + '&nbsp;All&nbsp;Pages') if
export_config.allow_full_download %>

</p>

+<h3>Format</h3>

+<p>

+<%= content_tag(:label, radio_button_tag('format',
"EnhancedXMLToFile", true) +
'&nbsp;Enhanced&nbsp;XML&nbsp;To&nbsp;File') %>

+<%= content_tag(:label, radio_button_tag('format', "EnhancedXML",
false) + '&nbsp;Enhanced&nbsp;XML') %>

+<%= content_tag(:label, radio_button_tag('format', "RawXML", false) +
'&nbsp;Raw&nbsp;XML') %>

+<%= content_tag(:label, radio_button_tag('format', "CSV", false) +
'&nbsp;CSV') %>

+<%= content_tag(:label, radio_button_tag('format', "Stylesheet",
false) + '&nbsp;Stylesheet') %>

+</p>

<%= submit_tag as_('Export'), :class => "submit" %>

<%= link_to as_('Cancel'), params_for(:action => 'list'), :class =>
'cancel' %>

Index: frontends/default/views/_stylesheet.rxml
===================================================================
--- frontends/default/views/_stylesheet.rxml (revision 0)
+++ frontends/default/views/_stylesheet.rxml (revision 0)
@@ -0,0 +1,30 @@
+xml.instruct!
+
+xml.tag! :"xsl:stylesheet", :version => "1.0", :"xmlns:xsl" =>
"http://www.w3.org/1999/XSL/Transform" do
+ xml.tag! :"xsl:template", :match => "/" do
+ xml.tag! "html" do
+ xml.tag! "body" do
+ xml.tag! "h2", active_scaffold_config.list.user.label
+ xml.tag! "table", :border => "1" do
+ xml.tag! "tr", :bgcolor => "#9acd32" do
+ @export_columns.each do |column|
+ xml.tag! "th", :align=>"left" do
+ xml.text! "#{Inflector.humanize(column.name)}"
+ end
+ end
+ end
+ xml.tag! "xsl:for-each", :select =>
"#{Inflector.camelize(Inflector.pluralize(active_scaffold_config.model_id))}/
#{Inflector.camelize(active_scaffold_config.model_id)}" do
+ xml.tag! "tr" do
+ @export_columns.each do |column|
+ xml.tag! "td" do
+ xml.tag! "xsl:value-of", :select=>"#{column.name}"
+ end
+ end
+ end
+ end
+ end
+ end
+ end
+ end
+end
+
Index: frontends/default/views/_listxml.rxml
===================================================================
--- frontends/default/views/_listxml.rxml (revision 0)
+++ frontends/default/views/_listxml.rxml (revision 0)
@@ -0,0 +1,13 @@
+xml.instruct!
+xml.instruct! :"xml-stylesheet", :type => "text/xsl", :href =>
"#{active_scaffold_config.model_id}.xsl" if @xml_file
+
+xml.tag!
Inflector.camelize(Inflector.pluralize(active_scaffold_config.model_id))
do
+ for record in @records
+ xml.tag! Inflector.camelize(active_scaffold_config.model_id) do
+ # model columns
+ @export_columns.each do |column|
+ xml.tag!(column.name, render_csv_column(record, column))
+ end
+ end
+ end
+end
Index: lib/actions/export.rb
===================================================================
--- lib/actions/export.rb (revision 17)
+++ lib/actions/export.rb (working copy)
@@ -49,8 +49,24 @@

find_items_for_export

- response.headers['Content-Disposition'] = "attachment;
filename=#{self.controller_name}.csv"
- render :partial => 'export', :content_type =>
Mime::CSV, :status => response_status
+ if params[:format] == "CSV"
+ response.headers['Content-Disposition'] = "attachment;
filename=#{self.controller_name}.csv"
+ render :partial => 'export', :content_type =>
Mime::CSV, :status => response_status
+ elsif params[:format] == "RawXML"
+ render :xml => @records.to_xml
+ elsif params[:format] == "EnhancedXML"
+ @xml_file = false
+ render :partial => 'listxml'
+ elsif params[:format] == "EnhancedXMLToFile"
+ @xml_file = true
+ headers["Content-Type"] = "text/xml"
+ headers["Content-Disposition"] = "attachment; filename=
\"#{self.controller_name}_#{Time.now.strftime('%Y%m%d')}.xml\""
+ render :partial => 'listxml'
+ elsif params[:format] == "Stylesheet"
+ headers["Content-Type"] = "text/xml"
+ headers["Content-Disposition"] = "attachment; filename=
\"#{Inflector.singularize(self.controller_name)}.xsl\""
+ render :partial => 'stylesheet', :layout => false
+ end
end

protected
Index: lib/helpers/list_helpers.rb
===================================================================
--- lib/helpers/list_helpers.rb (revision 17)
+++ lib/helpers/list_helpers.rb (working copy)
@@ -17,8 +17,7 @@
formatted_value =
clean_column_value(format_column(value.to_label))

when :has_many, :has_and_belongs_to_many

- firsts = value.first(4).collect { |v| v.to_label }

- firsts[3] = '...' if firsts.length == 4

+ firsts = value.collect { |v| v.to_label }

formatted_value =
clean_column_value(format_column(firsts.join(', ')))

end

end

Index: lib/config/export.rb
===================================================================
--- lib/config/export.rb (revision 17)
+++ lib/config/export.rb (working copy)
@@ -49,7 +49,8 @@
# provides access to the list of columns specifically meant for
this action to use
def columns
# self.columns = @core.columns._inheritable unless @columns
- self.columns = @core.columns.content_column_names unless
@columns # lazy evaluation
+# self.columns = @core.columns.content_column_names unless
@columns # lazy evaluation
+ self.columns = @core.list.columns.collect {|c| c.name} unless
@columns # lazy evaluation
@columns
end
def columns=(val)