Ruby支持比较且用excel直接打前提excel没特别复杂表结构式渲染等
例:
Ruby代码
outfile = File.open('csvout', 'wb')
CSV::Writer.generate(outfile) do |csv|
csv <<['c1', nil, '', '"', "\r\n", 'c2']
...
end
outfile.close
其般用考虑spreadsheet像楼提供连接所说例适合读取复杂适合参考用复杂建议看面说明
excel文件
想excel文件首先像写文件先加载spreadsheet类库指定编码接着创建Workbook
Ruby代码
book = Spreadsheet::Workbook.new
workbook基础创建Worksheet表单
Ruby代码
sheet1 = book.create_worksheet
用式创建表单:
Ruby代码
sheet2 = book.create_worksheet :name =>'My Second Worksheet'
sheet1.name = 'My First Worksheet'
我采用式加载数据表单Worksheet#[]=,
Worksheet#update_row, 或者直接给指定单元格复制
Ruby代码
sheet1.row(0).concat %w{Name Country Acknowlegement}
sheet1[1,0] = 'Japan'
row = sheet1.row(1)
row.push 'Creator of Ruby'
row.unshift 'Yukihiro Matsumoto'
sheet1.row(2).replace [ 'Daniel J. Berger', 'U.S.A.',
'Author of original code for Spreadsheet::Excel' ]
sheet1.row(3).push 'Charles Lowe', 'Author of the ruby-ole Library'
sheet1.row(3).insert 1, 'Unknown'
sheet1.update_row 4, 'Hannes Wyss', 'Switzerland', 'Author'
于格式处理:
Ruby代码
sheet1.row(0).height = 18
format = Spreadsheet::Format.new :color =>:blue,
:weight =>:bold,
:size =>18
sheet1.row(0).default_format = format
bold = Spreadsheet::Format.new :weight =>:bold
4.times do |x| sheet1.row(x + 1).set_format(0, bold) end
保存excel文件
Ruby代码
book.write '/path/to/output/excel-file.xls'
windows平台考虑WIN23OLE,处理windows转换强
Ruby代码
require 'win23ole'
application = WIN32OLE.new('Excel.Application')
worksheet
=application.Workbooks.Open(excelFileName).Worksheets(workSheetName)
worksheet.Activate
contLoop = true # Dummy counter for the loop
while contLoop do
colVal = worksheet.Cells(row, column).Value
if (colVal) then
# 字段非空则表示行值
# 处理读取
do processing ....
else
# 表明结束
# End the loop
contLoop = false
end
# go to the next Row
row += 1
end
# we are done
application.Workbooks.Close
首先,只是想把一些数据生成excel文件的话,最简单的办法,应该考虑生成CSV,因为,Ruby支持比较好,而且,你用excel可以直接打开。当然,前提是你的excel没有特别复杂的表结构,样式渲染等
样例如下:
Ruby代码
outfile = File.open('csvout', 'wb')
CSV::Writer.generate(outfile) do |csv|
csv <<['c1', nil, '', '"', "\r\n", 'c2']
...
end
outfile.close
其次,一般用的话可以考虑spreadsheet。就像一楼的老大提供的连接所说,那个例子很好,只是适合你的读取复杂的适合参考。如果,你用不到那么复杂,建议你看下面的说明。
生成excel文件
如果,你想生成一个excel文件,那么首先,就像写文件一个先加载spreadsheet类库,然后,指定编码接着,就可以创建一个Workbook了
Ruby代码
book = Spreadsheet::Workbook.new
在workbook基础上创建Worksheet表单
Ruby代码
sheet1 = book.create_worksheet
当然,你也可以用如下方式创建表单:
Ruby代码
sheet2 = book.create_worksheet :name =>'My Second Worksheet'
sheet1.name = 'My First Worksheet'
那么,这时我们可以采用如下方式加载数据到表单Worksheet#[]=,
Worksheet#update_row, 或者直接给一个指定单元格复制
Ruby代码
sheet1.row(0).concat %w{Name Country Acknowlegement}
sheet1[1,0] = 'Japan'
row = sheet1.row(1)
row.push 'Creator of Ruby'
row.unshift 'Yukihiro Matsumoto'
sheet1.row(2).replace [ 'Daniel J. Berger', 'U.S.A.',
'Author of original code for Spreadsheet::Excel' ]
sheet1.row(3).push 'Charles Lowe', 'Author of the ruby-ole Library'
sheet1.row(3).insert 1, 'Unknown'
sheet1.update_row 4, 'Hannes Wyss', 'Switzerland', 'Author'
对于格式的处理,可以如下:
Ruby代码
sheet1.row(0).height = 18
format = Spreadsheet::Format.new :color =>:blue,
:weight =>:bold,
:size =>18
sheet1.row(0).default_format = format
bold = Spreadsheet::Format.new :weight =>:bold
4.times do |x| sheet1.row(x + 1).set_format(0, bold) end
最后,保存excel文件
Ruby代码
book.write '/path/to/output/excel-file.xls'
最后,如果是在windows平台下的话,也可以考虑WIN23OLE,处理windows下的转换很强大
Ruby代码
require 'win23ole'
application = WIN32OLE.new('Excel.Application')
worksheet
=application.Workbooks.Open(excelFileName).Worksheets(workSheetName)
worksheet.Activate
contLoop = true # Dummy counter for the loop
while contLoop do
colVal = worksheet.Cells(row, column).Value
if (colVal) then
# 如果这个字段非空,则表示这行有值
# 在这里处理读取
do processing ....
else
# 这里表明结束。
# End the loop
contLoop = false
end
# go to the next Row
row += 1
end
# we are done
application.Workbooks.Close