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
require 'win32ole'module Excel
class WorkBook
#xlEdge
#xlEdgeBottom =9
#xlEdgeLeft = 7
#xlEdgeRight = 10
#xlEdgeTop = 8
#xlColor
#xlColorBlank = 1
#xlColorWhite =2
#xlColorRed = 3
#xlColorGreen =10
#xlColorBlue =5
#xlColorYellow =6
#xlColorPurple = 7 # zi se
#xlColorCyan =8 #qing se
#xlBgColorYellow =19
#xlBgColorCyan =20
#xlBgColorPurple =24
#xlDefaultLineStyle = 1
@@worksheets_name =[]
def initialize(encoding="utf-8")
@excel = WIN32OLE.new("excel.application")
@excel.visible = FALSE
@workbook = @excel.Workbooks.Add()
#@style_id = 0
@encoding = encoding
create_style
end
def add_worksheet(name)
while @@worksheets_name.include?(name)
name +="1"
end
@@worksheets_name << name
worksheet = @workbook.Worksheets.Add()
worksheet.Activate
worksheet.name = name
return WorkSheet.new(worksheet)
end
def show
@excel.visible = TRUE
end
def close
@workbook.Close(0)
@excel.Quit()
end
def create_style
[email protected]('NormalStyle')
sty.Font.Size = 12
sty.Borders(7).LineStyle=1
sty.Borders(8).LineStyle=1
sty.Borders(9).LineStyle=1
sty.Borders(10).LineStyle=1
[email protected]('TitleStyle')
sty.Font.Size = 16
sty.Font.Bold =true
sty.Font.ColorIndex =3
#sty.Interior.ColorIndex = 20
end
end
#worksheet
class WorkSheet
IMAGE_ROW_NUM = 56
@@worksheets_name =[]
def initialize(worksheet)
@row_count = 1
@worksheet = worksheet
end
def add_space_line(n=1)
return if n<1
@row_count +=n
end
def add_title(name)
add_space_line
add_row.add_cell(name,false,"TitleStyle")
end
def add_row()
@current_row = Row.new(@worksheet,@row_count)
@row_count +=1
return @current_row
end
def current_row
return @current_row
end
def add_image(image_path)
if not File.exist?(image_path)
return
end
add_space_line 1
add_row
cell_name=current_row.first_cell
@worksheet.Range(cell_name).Select
@worksheet.Pictures.Insert(image_path)
add_space_line IMAGE_ROW_NUM
end
end
#row
class Row
FILL_TYPE = 4
@@cell_map =["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"]
def initialize(worksheet,row_id)
@row_id =row_id
@cell_count=0
@worksheet = worksheet
end
def curent_cell
return cell_name(@cell_count)
end
def first_cell
return cell_name(0)
end
def add_cell(value,auto_fit = false,style = "NormalStyle")
range = @worksheet.Range(cell_name(@cell_count))
range['Value'] = value.to_s
range['Style']=style
range.Columns.AutoFit if auto_fit
@cell_count +=1
end
def cell_name(index)
second = index % 26
first = (index - second) / 26
if first == 0
return @@cell_map[second]+@row_id.to_s
end
first -=1
return @@cell_map[first]+@@cell_map[second]+@row_id.to_s
end
def set_cell(index,value,auto_fit = false,style = "NormalStyle")
[email protected](cell_name(index))
range['Value'] = value
range['Style']=style
range.Columns.AutoFit if auto_fit
end
end
end
require 'excel'
excel = Excel::WorkBook.new
worksheet = excel.add_worksheet("new sheet")
worksheet.add_title('title')
row = worksheet.add_row
row.add_cell("chenyan")
row.add_cell(0)
row.add_cell("2006-01-01 01:01:01")
worksheet.add_image("C://Sunset.jpg")
row = worksheet.add_row
row.add_cell("shenbin")
row.add_cell(0)
row.add_cell("2006-01-01 01:01:01")
worksheet.add_image("C://Sunset.jpg")
excel.show
