[export_excel.rb] 帮老婆做报表导出Excel的脚本。【大大集团】

export_excel.rb

#!/usr/bin/env ruby

require 'pathname'
require 'spreadsheet'
require 'roo'

def main resource_path = '', month = 1
  data = {}
  res = collect resource_path

  res.each do |province, file|
    data[province] = []
    if file[-4,4] == 'xlsx'
      # Handle *.xlsx
      Roo::Spreadsheet.open(file).sheet(1).each do |row|
        data[province].push row
      end

    else
      # Handle *.xls
      Spreadsheet.open(file).worksheet(1).each do |row|
        data[province].push row.to_a
      end
    end
  end

  result = '大大集团' + month.to_s + '月团费收缴情况汇总表'
  export result, data
end

# Get excel file list.
def collect resource_path = ''
  filter = ['.', '..', '.DS_Store']

  if resource_path.empty?
    resource_path = File.dirname(Pathname.new(__FILE__).realpath) + '/res/'
  end

  if resource_path[-1] != '/'
    resource_path += '/'
  end

  excel_file_list = {}

  Dir.foreach resource_path do |file|
    if filter.include? file then next end
    if file[0, 1] == '~' then next end

    realpath = resource_path + file
    province = File.basename(realpath).split('团费收缴情况登记表')[0].sub(/.?月份?/, '').sub('公司', '')
    excel_file_list[province] = realpath
  end

  excel_file_list
end

# Export to a new excel file.
def export filename, data
  Spreadsheet.client_encoding = 'UTF-8'
  book = Spreadsheet::Workbook.new
  sheet = book.create_worksheet :name => '汇总表'

  format = Spreadsheet::Format.new :size => 14
  font = Spreadsheet::Font.new 'Arial'
  book.add_font font

  data.each do |province, rows|
    sheet = book.create_worksheet :name => '大大集团' + province
    sheet.default_format = format
    p province + ' done'

    i = 0

    rows.each do |row|
      sheet.row(i).replace row
      i += 1
    end
  end

  book.write filename + '.xls'
end

resource_path = ARGV[0]
month = ARGV[1]

main resource_path, month

# end of this file