Skip to content

Instantly share code, notes, and snippets.

@raveman

raveman/excel_dumper.rb

Forked from arkadiyk/excel_dumper.rb
Created March 19, 2021 14:56
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save raveman/061a6f3ed2bbcf29f74449d76b7cdec5 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save raveman/061a6f3ed2bbcf29f74449d76b7cdec5 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @arkadiyk arkadiyk created this gist Sep 30, 2011.
    93 changes: 93 additions & 0 deletions excel_dumper.rb
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,93 @@
    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