Skip to content

Instantly share code, notes, and snippets.

@nilesh
Forked from arkadiyk/excel_dumper.rb
Created August 2, 2013 12:21
Show Gist options
  • Save nilesh/6139480 to your computer and use it in GitHub Desktop.
Save nilesh/6139480 to your computer and use it in GitHub Desktop.
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment