Ruby on Rails
HowToExportToExcel

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. (Note: this code hasn’t been updated since 2005. try parseexcel)

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