gem包 spreadsheet 的使用方法

网上关于这个gem的使用大多都比较笼统，笔者也走了很多弯路。
就打算自己写一个使用方法，省得以后自己忘了，又要找好久。

spreadsheet 是把前段表格导出成excel表格的一个gem包
在gemflie中写入，bundle install 下就可以了，地址在下面
项目地址： https://github.com/zdavatz/spreadsheet
文档地址： http://spreadsheet.rubyforge.org/files/GUIDE_txt.html

首先在前段表格对应的Controller中写入下面代码。这里是传入Orde表中的数据。
Order.export_state_enterprise 是Order(model)中的类方法，需要传入一些表格的导出信息

respond_to do |f|
      f.html
      file_name = "导出的excel表格名字-(#{params[:created_at_first] }至#{ params[:created_at_last]}).xls"
      file_zh_name = Tool.get_agent_name(request, file_name)
      f.csv{send_data(Order.export_state_enterprise(@query,@state_data, created_time_first, created_time_last), :type => "text/excel;charset=utf-8; header=present", :filename => file_zh_name)}
    end

Tool类方法如下

#encoding: utf-8

class Tool

  class << self
    def get_agent_name(request, file_name)
      str = request.user_agent
      user_agent = UserAgent.parse(str)
      file_zh_name = user_agent.browser == 'Internet Explorer' ? CGI::escape(file_name) : file_name
    end
  end
end

在

def self.export_state_enterprise(orders, all, created_time_first, created_time_last)
    xls_report = StringIO.new
    Spreadsheet.client_encoding = "UTF-8"
    book = Spreadsheet::Workbook.new 
    style0 = Spreadsheet::Format.new :weight => :bold, :size => 16, :align => :center, :align => :merge #设置导出格式
    style = Spreadsheet::Format.new :align => :center, :size => 15, :border => :thick
    style_first = Spreadsheet::Format.new :weight => :bold, :size => 35, :align => :center, :align => :merge, :border => :thick, :color => :blue
    style_second = Spreadsheet::Format.new :weight => :bold, :size => 25, :align => :center, :align => :merge, :border => :thick, :color => :blue
    sheet1 = book.create_worksheet :name => "导出的excel文件名.xls"
    serial_number = 8 # 导出表格的最大列数 规定有多少列
    # 为2到4行设置格式
    (2..4).each do |i|
      sheet1.row(i).height = 30
    end
    #给前serial_number行设置格式
    serial_number.times do |i|
      sheet1.row(2).set_format(i, style)
    end
    sheet1.row(0).height = 55
    sheet1.row(1).height = 35
    first_row = sheet1.row(0)
    serial_number.times do |i|
      first_row.set_format(i, style_first)
    end
    second_row = sheet1.row(1)
    serial_number.times do |i|
      second_row.set_format(i, style_second)
    end
    first_row[0] = "第一行的标题名"
    second_row[0] = "统计时段：#{created_time_first} 至 #{created_time_last}"
    sheet1.row(2).concat ["序号", "单位", "总数", "金额（元）", "成交总数", "成交金额（元）", "等待收货总数", "等待收货金额（元）"]
    #对列进行设置宽度
    sheet1.column(0).width = 25
    sheet1.column(1).width = 40
    sheet1.column(2).width = 20
    sheet1.column(3).width = 20
    sheet1.column(4).width = 20
    sheet1.column(5).width = 30
    sheet1.column(6).width = 30
    sheet1.column(7).width = 30
    sheet1.column(8).width = 35
    serial_number.times do |i|
      sheet1.row(3).set_format(i, style)
    end
    sheet1.row(3).concat ["总计", "", all[0], all[1], all[2], all[3], all[4], all[5]] #一一对应对面上的数据。 序号，单位....
    sheet1.merge_cells(3, 0, 3, 1)
    current_row = 4
    orders.each_with_index do |order, index|
      sheet1.row(current_row).concat [index+1, order.dep_name, order.f_count.to_i+order.p_count.to_i, order.f_total.to_f+order.p_total.to_f, order.f_count, order.f_total, order.p_count, order.p_total]
      serial_number.times do |i|
        sheet1.row(current_row).set_format(i, style)
      end
      current_row += 1
      sheet1.row(current_row).height = 30
    end
    book.write xls_report #导出
    xls_report.string
  end

下面说一下导出表格的格式

设置单元格尺寸
设置列宽
sheet.column(0).width = 50
这样会设置第 1 列宽度为 50

设置行高
sheet.row(0).height = 60
这样会设置第 1 行行高为 60
不可能单独为某个单元格设置行高或者列宽

设置单元格颜色
更新单元格颜色，需要创建一个继承自 Spreadsheet::Format
的类

#设置cell 背景色
class ColorFormat < Spreadsheet::Format
  def initialize(gb_color, font_color)
    super :pattern => 1, :pattern_fg_color => gb_color,:color => font_color, :text_wrap => 1
  end
end

然后可以在单元格中这样设置颜色
sheet.row(0).set_format(0, ColorFormat.new(:green, :white)
这样会在第一行第一列设置单元为绿底白字
可用的颜色有

:builtin_black, :builtin_white, :builtin_red, :builtin_green,:builtin_blue, :builtin_yellow, :builtin_magenta, :builtin_cyan,:text, :border, :pattern_bg, :dialog_bg, :chart_text, :chart_bg,:chart_border, :tooltip_bg, :tooltip_text, :aqua,:black, :blue, :cyan, :brown, :fuchsia, :gray, :grey, :green,:lime, :magenta, :navy, :orange, :purple, :red, :silver, :white,:yellow

其中 :border
为默认的边框颜色

设置自动换行
与 设置单元格颜色 类似，需要在 Spreadsheet::Format
中设置 :text_wrap
属性值为 1

class ColorFormat < Spreadsheet::Format
  def initialize(gb_color, font_color)
    super :pattern => 1, :pattern_fg_color => gb_color,:color => font_color, :text_wrap => 1
  end
end

可用的边框
:border => :thick

:thin, :medium, :dashed, :dotted, :thick,:double, :hair, :medium_dashed, :thin_dash_dotted,:medium_dash_dotted, :thin_dash_dot_dotted,:medium_dash_dot_dotted, :slanted_medium_dash_dotted