#coding: utf-8
require 'rubygems'
require 'roo'
require 'spreadsheet'
class MyExcel
# 功能: 初始化
# 描述: 根据提供的文件,返回文件地址,文件,文件名,文件路径,文件扩展名.
# 参数: url
def initialize(url=nil)
if !url.nil?
set_config(url)
#"/var/www/two_server_git/商圈图/12.9商场图表.xlsx"
end
end
# 功能: 返回文件信息
# 描述: 根据提供的文件名,返回文件地址,文件名,文件路径,文件扩展名
# 参数: url
def set_config(url)
@url = url
@file_name = File.basename(url)
@file_dirname = File.dirname(url)
@file_extname = File.extname(url)
end
# 功能: 查看输入的文件是否存在
# 描述: 如果输入的文件不存在,提示用户重新输入;否则,放行。
# 参数: url
def check_file?(url)
if url.empty?
raise "请输入需要打开的文件完整路径!"
end
if !File.exist?(url)
raise "文件不存在,请输入真实存在的文件路径!"
end
end
# 功能: 查看目录是否存在
# 描述: 查看目录是否存在,如果不存在,直接创建此目录
# 参数: 文件目录
def exists_or_mkdir(address)
FileUtils.makedirs(address) unless Dir.exists?(address)
end
# 功能: 导出excel
# 描述: 将数据导出,生成一个excel文件。
# 参数: 数据源, 文件url
def write_excel(excel_data, url=nil)
url ||= @url
#check_file?(url)
#set_config(url)
@url = url
@file_name = File.basename(url)
@file_dirname = File.dirname(url)
@file_extname = File.extname(url)
puts url, excel_data
file_name = @file_name
file_name = file_name.force_encoding('GBK')
file_name = file_name.encode('UTF-8')
exists_or_mkdir(@file_dirname)
Spreadsheet.client_encoding = "UTF-8"
file_path = File.join(@file_dirname, file_name)
puts file_path
begin
book = Spreadsheet::Workbook.new
sheet1 = book.create_worksheet
sheet1.name = 'My First Worksheet'
excel_data.each_with_index do |row, index|
puts "#{excel_data[index]}"
sheet1.row(index).replace excel_data[index]
end
puts file_path
book.write file_path
rescue => e
puts e
end
return file_path
end
def open_excel(url=nil, &block)
url ||= @url
check_file?(url)
set_config(url)
file_extname = @file_extname
begin
case file_extname.upcase
when ".XLS"
#Spreadsheet.client_encoding = "UTF-8"
s = Roo::Spreadsheet.open(url)
#s = Roo::Excel.new(file.path, nil, :ignore)
when ".ODS"
s = Roo::OpenOffice.new(url)
when ".XLSX"
s = Roo::Excelx.new(url)
when ".CSV"
s = Roo::CSV.new(url, csv_options: {encoding: Encoding::ISO_8859_1})
when ".TSV"
s = Roo::CSV.new(url, csv_options: {col_sep: "\t"})
end
if block.nil?
#s.default_sheet = s.sheets.first
else
puts "==into block==="
s = yield s.sheets
end
return s
rescue
return nil
end
end
def test_excel
sheet1 = open_excel
sheets = open_excel { |book| book.worksheets }
puts "1================="
sheet1.each do |row|
puts "----#{row}"
end
puts "2================="
sheets.each_with_index do |sheet, index|
puts "#{2+index+1}================="
puts sheet.name
sheet.each do |row|
puts "----#{row}"
end
end
end
def test_write_excel
title = ['name', 'sex', 'age', 'address', 'phone']
data = [['zh##', 'man', '##', 'beijing', '185######'],
['z###', 'man', '##', 'beijing', '151######']]
write_excel(data,"#{Rails.root.to_s}/test_write_excel.xls")
end
def test_yaml(file=nil)
require 'yaml'
file= file || File.join("doc", "test", "test_yaml.yml")
thing = YAML.load_file(file)
puts thing.inspect
puts "========"
puts thing["doc"]
end
def test_block
puts "please input argv of main:"
params = $stdin.gets.chomp()
puts main_block(params) { |name|
name+" is a engeneer"
}
end
def main_block(name, &block)
new_name = yield name
end
end
MyExcel.new.send(ARGV[0])