excel_dumper.rb

class ExcelDumper
  def initialize(array)
    @data = array
  end

  def to_xls(options = {})
    output = %{<?xml version="1.0" encoding="UTF-8"?>
<Workbook xmlns:x="urn:schemas-microsoft-com:office:excel"
          xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
          xmlns:html="http://www.w3.org/TR/REC-html40"
          xmlns="urn:schemas-microsoft-com:office:spreadsheet"
          xmlns:o="urn:schemas-microsoft-com:office:office">}
    output << %{
     <Styles>
         <Style ss:ID="Default" ss:Name="Normal">
             <Font ss:FontName="Arial"/>
         </Style>
         <Style ss:ID="HeaderRow">
             <Font ss:Bold="1"/>
             <Interior ss:Color="#D8D8D8" ss:Pattern="Solid"/>
         </Style>
         <Style ss:ID="WrapText">
             <Alignment ss:Vertical="Center" ss:WrapText="1"/>
         </Style>
         <Style ss:ID="Ddate">
             <NumberFormat ss:Format="Short Date"/>
         </Style>
         <Style ss:ID="Dtime">
             <NumberFormat ss:Format="yyyy\\-mm\\-dd\\ hh:mm:ss;@"/>
         </Style>
     </Styles>}

   output << %{<Worksheet ss:Name="#{options[:name] || 'Sheet1'}"><Table>}

    if @data.any?
      keys = @data.first.keys

      widths = {}
      keys.each do |key|
        value_length = key.to_s.length
        widths[key] = value_length if !widths[key] || value_length > widths[key]
      end
      @data.each do |item|
        keys.each do |key|
          value_length = case item[key]
                           when Date then 8
                           when Time then 16
                           else
                             item[key].to_s.length
                         end
          widths[key] = value_length if !widths[key] || value_length > widths[key]
        end
      end

      keys.each do |key|
        width = %!ss:Width="#{(widths[key] > 50 ? 50 : widths[key]) * 7}"!
        style = %!ss:StyleID="WrapText"! if widths[key] > 50
        output << "<Column #{style} #{width}/>"
      end

      output << "<Row ss:StyleID=\"HeaderRow\">"
      keys.each { |key| output << "<Cell><Data ss:Type=\"String\" >#{key}</Data></Cell>" }
      output << "</Row>"

      @data.each do |item|
        output << "<Row>"
        keys.each do |key|
          value = item[key]
          type = case value
                   when Date then "DateTime"
                   when Time then "DateTime"
                   when Integer then "Number"
                   when Float then "Number"
                   else "String"
                 end
          case value
            when Date
              value = value.strftime("%Y-%m-%dT%H:%M:%S")
              format = 'ss:StyleID="Ddate"'
            when Time
              value = value.strftime("%Y-%m-%dT%H:%M:%S")
              format = 'ss:StyleID="Dtime"'
          end
          value = value.gsub('<','&lt;').gsub('>', '&gt;') if type == "String"
          output << "<Cell #{format}><Data ss:Type=\"#{type}\">#{value}</Data></Cell>"
        end
        output << "</Row>"
      end

    end
    output << '</Table></Worksheet></Workbook>'
  end
end