转载:http://blog.csdn.net/xianqiang1/article/details/7298203
require 'win32ole'
require 'iconv' #一个编码转换库，在这里由于声明了采用编码为UTF-8，故没用
WIN32OLE.codepage = WIN32OLE::CP_UTF8
# Get the Excel Application API
excel = WIN32OLE::new('excel.Application')
# Open the Excel file
workbook = excel.Workbooks.open("C:/Users/XXX/Desktop/test.xls")
# Focus on Sheet1
worksheet = workbook.worksheets(1)
worksheet.Select
results = File.open("test.txt", "w")
# conv = Iconv.new("UTF-8", "")
# Output the the values
(1..22).collect do |line|
('a'..'ad').collect do |row|
result = worksheet.Range("#{row}#{line}")["Value"]
#result = conv.iconv("#{result}")
results.write "#{result} "
end
results.write " "
end
# Exit the application
workbook.close()
excel.Quit()
results.close()
写入Excel表
# 测试用于生成"バグ管理票"的电子表格文件
require "win32ole"
# 声明Excel的采用的编码为UTF-8
WIN32OLE.codepage = WIN32OLE::CP_UTF8
excel = WIN32OLE::new('excel.Application')
# 设置显示Excel程序
excel.visible = true
# 默认创建有三个表的电子表格，可选的参数有:
#1 单工作表
#2 图表
#3 宏表
#4 国际通用宏表
#5 与默认的相同
#6 与1同
#7 对话框
workbook = excel.Workbooks.Add(1)
# 定位于第一个表格
worksheet = workbook.worksheets(1)
worksheet.Select
# 设置单元格宽度
worksheet.Range("A1:AG1").ColumnWidth = 3
# 合并单元格
worksheet.Range("A1:K1").Merge(0)
worksheet.Range("L1:Y2").Merge(0)
worksheet.Range("A1")["Value"]="バグ管理番号"
# 字体加粗
worksheet.Range("L1").Font.Bold=true
# 字体大小
worksheet.Range("L1").Font.Size=14
# 字体颜色
worksheet.Range("L1").Font.ColorIndex = 1
worksheet.Range("L1")["Value"]="バグ管理票"
worksheet.Range("Z1:AC1").Merge(0)
worksheet.Range("AD1:AG1").Merge(0)
worksheet.Range("Z1")["Value"]="决裁者"
worksheet.Range("AD1")["Value"]="申请者"
# 设置单元格的高度
worksheet.Range("A2:AG2").RowHeight=50
worksheet.Range("A2:C2").Merge(0)
worksheet.Range("D2:E2").Merge(0)
worksheet.Range("F2:H2").Merge(0)
worksheet.Range("I2:K2").Merge(0)
worksheet.Range("Z2:AC3").Merge(0)
worksheet.Range("AD2:AG3").Merge(0)
worksheet.Range("A2")["Value"] = "バグ区分"
worksheet.Range("D2")["Value"] = "部署略称"
worksheet.Range("F2")["Value"] = "PJNO-SEQ区分"
worksheet.Range("I2")["Value"] = "通し番号"
worksheet.Range("A3:C3").Merge(0)
worksheet.Range("D3:E3").Merge(0)
worksheet.Range("F3:H3").Merge(0)
worksheet.Range("I3:K3").Merge(0)
worksheet.Range("L3:Y3").Merge(0)
worksheet.Range("A3")["Value"] = "Ｂ_外_"
worksheet.Range("D3")["Value"] = "経企"
worksheet.Range("F3")["Value"] = "_41030_"
# 设置单元格格式为文本格式，常见的还有：
# @ # 文本
# $#,##0.00 # 贷币
# 0.00_ # 浮点数
# #,##0 # 整数
# 0.00% # 百分数
# m/d/yyyy# 时间
worksheet.Range("I3").NumberFormat ="@"
worksheet.Range("I3")["Value"] = "0005"
worksheet.Range("L3")["Value"] = "（ 外部仕様不良 ）"
worksheet.Range("A4:A5").Merge(0)
worksheet.Range("A5:AG5").RowHeight=50
worksheet.Range("A4").Value = "査阅栏"
values = ["取缔役会", "経営会议", "社长", "常务", "管掌役员", "事业部长", "部长", "课长"]
count = 0
last_column = ""
('A'..'AG').each_with_index do |column, index|
next if index == 0
if (index-1)%4 == 0
last_column = column
elsif (index-1)%4 == 3
worksheet.Range("#{last_column}4:#{column}4").Merge(0)
worksheet.Range("#{last_column}5:#{column}5").Merge(0)
worksheet.Range("#{last_column}4").Value = values[count]
count = count + 1
end
end
values = ["バグ内容", "修正内容", "确认方法"]
authors = ["発见者", "修正者", "结果确认者"]
times = ["発生日", "修正日", "确认日"]
3.times do |i|
worksheet.Range("B#{6+i*2}:E#{7+i*2}").Merge(0)
worksheet.Range("F#{6+i*2}:Y#{7+i*2}").Merge(0)
worksheet.Range("Z#{6+i*2}:AC#{6+i*2}").Merge(0)
worksheet.Range("Z#{7+i*2}:AC#{7+i*2}").Merge(0)
worksheet.Range("AD#{6+i*2}:AG#{6+i*2}").Merge(0)
worksheet.Range("AD#{7+i*2}:AG#{7+i*2}").Merge(0)
worksheet.Range("B#{6+i*2}").Value= values[i]
worksheet.Range("Z#{6+i*2}").Value= times[i]
worksheet.Range("AD#{6+i*2}").Value = authors[i]
worksheet.Range("A#{7+i*2}:AG#{7+i*2}").RowHeight = 50
end
worksheet.Range("B12:E12").Merge(0)
worksheet.Range("F12:M12").Merge(0)
worksheet.Range("N12:Q12").Merge(0)
worksheet.Range("R12:Y12").Merge(0)
worksheet.Range("Z12:AC12").Merge(0)
worksheet.Range("AD12:AG12").Merge(0)
worksheet.Range("B12").Value = "予定バグ数"
worksheet.Range("N12").Value = "発见バグ数"
worksheet.Range("Z12").Value = "解决日"
worksheet.Range("AD12").Value = "6/22/2012"
worksheet.Range("B13:E13").Merge(0)
worksheet.Range("F13:Y13").Merge(0)
worksheet.Range("Z13:AC13").Merge(0)
worksheet.Range("AD13:AG13").Merge(0)
worksheet.Range("B13").Value = "リリースバージョン"
worksheet.Range("Z13").Value = "リリース日"
worksheet.Range("AD13").Value = "6/30/2012"
values = ["PJNO-SEQ", "PJ略称", "开発担当", "PJリーダ"]
items = ["バグ 摘出タイプ", "PJ名称", "部门名", "部名/Grp名"]
4.times do |i|
worksheet.Range("B#{14+i}:E#{14+i}").Merge(0)
if i==0
worksheet.Range("F14:K14").Merge(0)
else
worksheet.Range("F#{14+i}:M#{14+i}").Merge(0)
end
worksheet.Range("N#{14+i}:Q#{14+i}").Merge(0)
worksheet.Range("R#{14+i}:AG#{14+i}").Merge(0)
worksheet.Range("B#{14+i}").Value = values[i]
worksheet.Range("N#{14+i}").Value = items[i]
end
worksheet.Range("A6:A17").Merge(0)
worksheet.Range("A6").Value = "必须项目"
# 设置单元格的水平与垂直文本对齐方式
worksheet.Range("A1:AG17").HorizontalAlignment = 8
worksheet.Range("A1:AG17").VerticalAlignment = 2 #（1～5）
# 设置表格的边框
worksheet.Range("A1:AG17").Borders.Weight = 1
worksheet.Range("A1:AG17").Borders.ColorIndex = 1
worksheet.Range("A1:AG17").Borders.LineStyle = 7 #(1~13)
# 设置表格的背影颜色
worksheet.Range("A1:AG17").Interior.ColorIndex=2
# 保存电子表格
workbook.saveas "C:\Users\XXX\Desktop\Book2.xls"
workbook.close
# 退出Excel程序
excel.Quit()