Ruby操作Excel并在其中加入图片

注意：代码来源于网上。

excel.rb

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 sty=@workbook.Styles.Add('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 sty=@workbook.Styles.Add('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") range=@worksheet.Range(cell_name(index)) range['Value'] = value; range['Style']=style range.Columns.AutoFit if auto_fit end end end

test.rb

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

 

Ruby读取Excel：

require 'win32ole' excel = WIN32OLE::new('excel.Application') workbook = excel.Workbooks.Open('d://test.xls') worksheet = workbook.Worksheets(1) worksheet.Select worksheet.range("a1:a10").each{|cell| puts cell.value} excel.ActiveWorkbook.Close(0) excel.Quit()

其他资料：

http://hi.baidu.com/marshluca/blog/item/d3fc7f2fd50a53301f3089f4.html

http://hlee.javaeye.com/blog/200205

 

Ruby的Excel工具库：

http://poi.apache.org/poi-ruby.html

http://rubyspreadsheet.sourceforge.net/

http://hlee.javaeye.com/blog/356460