Ruby on Rails | Screencasts | Download | Documentation | Weblog | Community | Source

Ruby on Rails
HowToExportToExcel (Version #26)

Home Page | All Pages | Recently Revised | Feed

You can easily export an XML file into Excel.

There’s an introductory tutorial at the MSDN Blog: Office XML Formats and more complete information is available in the Office 2003 XML Reference Schemas (you’ll need a CHM viewer for your platform and a way to extract an MSI to make use of it though).

Here’s a quick example:

In your controller (replace “Record” with your model):

def export
  headers['Content-Type'] = "application/vnd.ms-excel"
  headers['Content-Disposition'] = 'attachment; filename="excel-export.xls"'
  headers['Cache-Control'] = ''
  @records = Record.find(:all)
end

The export.rxml view for this (will generate a simple table form any ActiveRecord object, again replace “Record” with your model):

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' => 'Blahblah' do
    xml.Table do
      
      # Header
      xml.Row do
        for column in Record.content_columns do
          xml.Cell do
            xml.Data column.human_name, 'ss:Type' => 'String'
          end
        end
      end
      
      # Rows
      for record in @records
        xml.Row do
          for column in Record.content_columns do
            xml.Cell do
              xml.Data record.send(column.name), 'ss:Type' => 'String'
            end
          end
        end
      end
      
    end
  end
  
end

If you want more formatting etc, just use Excel to layout/design, save as Excel xml file and inspect the file with your favourite text or XML editor.

Setting the “Cache-Control” header to nil prevents the “Internet Explorer was not able to open this internet site” error.

For Excel 2000 ( only work on Linux )

It seems that the above method only work for Office 2003 , and for the version before that, we may use the Apache POI Ruby Bindings which is original written in Java. For more information, please check here
http://jakarta.apache.org/poi/poi-ruby.html

Example Excel generate by the above method is here

Alternative Methods

Also see rubyspreadsheet The functionality is quite basic, eg no charts, but it works.

Or just use CSVs (Ruby comes with a CSV library). Note that using the XML format has some advantages, even when you’re not generating formatted content, like support of UTF-8 and multiple worksheets.

Disable your layout

Make sure you disable layout for your export method. _Note: This can also be accomplished with render :layout => false

class YourController < ApplicationController
  layout "layouts/yourLayout" ,  :except => :export

  def export
     ..
  end
end

Question: I tried to figure out how to create formatting styles and apply them, but with no luck. Any help out there?

You could try Scio Excel library:

http://rolando.cl/media/ruby/scio_excel.rb

The rdoc is here:

http://rolando.cl/media/ruby/doc/index.html

It allows you to apply simple formatting and styling.

Using Spreadsheet::Excel and Spreadsheet::Parseexcel with latin1 and scandinavian letters (such as ä and å)

When writing xls-format for send_file you might want to use something like

ic = Iconv.new('latin1','utf-8')

worksheet.write(row_index, column_index, ic.iconv(your_text_from_db), your_text_format)

and importing file from form you will want to parse text-cells from xls with

worksheet.each(number_of_lines_to_skip) { |row|
  text = row.at(column_index).to_s('utf-8')
}

Screencast on Practical way to export excel in Rails

Perhaps you want to figure out HowToImportFromExcel
category: Howto

You can easily export an XML file into Excel.

There’s an introductory tutorial at the MSDN Blog: Office XML Formats and more complete information is available in the Office 2003 XML Reference Schemas (you’ll need a CHM viewer for your platform and a way to extract an MSI to make use of it though).

Here’s a quick example:

In your controller (replace “Record” with your model):

def export
  headers['Content-Type'] = "application/vnd.ms-excel"
  headers['Content-Disposition'] = 'attachment; filename="excel-export.xls"'
  headers['Cache-Control'] = ''
  @records = Record.find(:all)
end

The export.rxml view for this (will generate a simple table form any ActiveRecord object, again replace “Record” with your model):

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' => 'Blahblah' do
    xml.Table do
      
      # Header
      xml.Row do
        for column in Record.content_columns do
          xml.Cell do
            xml.Data column.human_name, 'ss:Type' => 'String'
          end
        end
      end
      
      # Rows
      for record in @records
        xml.Row do
          for column in Record.content_columns do
            xml.Cell do
              xml.Data record.send(column.name), 'ss:Type' => 'String'
            end
          end
        end
      end
      
    end
  end
  
end

If you want more formatting etc, just use Excel to layout/design, save as Excel xml file and inspect the file with your favourite text or XML editor.

Setting the “Cache-Control” header to nil prevents the “Internet Explorer was not able to open this internet site” error.

For Excel 2000 ( only work on Linux )

It seems that the above method only work for Office 2003 , and for the version before that, we may use the Apache POI Ruby Bindings which is original written in Java. For more information, please check here
http://jakarta.apache.org/poi/poi-ruby.html

Example Excel generate by the above method is here

Alternative Methods

Also see rubyspreadsheet The functionality is quite basic, eg no charts, but it works.

Or just use CSVs (Ruby comes with a CSV library). Note that using the XML format has some advantages, even when you’re not generating formatted content, like support of UTF-8 and multiple worksheets.

Disable your layout

Make sure you disable layout for your export method. _Note: This can also be accomplished with render :layout => false

class YourController < ApplicationController
  layout "layouts/yourLayout" ,  :except => :export

  def export
     ..
  end
end

Question: I tried to figure out how to create formatting styles and apply them, but with no luck. Any help out there?

You could try Scio Excel library:

http://rolando.cl/media/ruby/scio_excel.rb

The rdoc is here:

http://rolando.cl/media/ruby/doc/index.html

It allows you to apply simple formatting and styling.

Using Spreadsheet::Excel and Spreadsheet::Parseexcel with latin1 and scandinavian letters (such as ä and å)

When writing xls-format for send_file you might want to use something like

ic = Iconv.new('latin1','utf-8')

worksheet.write(row_index, column_index, ic.iconv(your_text_from_db), your_text_format)

and importing file from form you will want to parse text-cells from xls with

worksheet.each(number_of_lines_to_skip) { |row|
  text = row.at(column_index).to_s('utf-8')
}

Screencast on Practical way to export excel in Rails

Perhaps you want to figure out HowToImportFromExcel
category: Howto

i2 on Rails, powered by Phusion Passenger™ and Ruby Enterprise Edition.