使用Ruby生成图文并茂的Excel

可以实现插入文字，图像。
excel.rb代码如下

ruby 代码

 

1. require 'win32ole'  
2. module Excel  
3.     class WorkBook  
4.         #xlEdge  
5.         #xlEdgeBottom =9   
6.         #xlEdgeLeft  = 7   
7.         #xlEdgeRight = 10   
8.         #xlEdgeTop  = 8   
9.         #xlColor  
10.         #xlColorBlank = 1  
11.         #xlColorWhite =2  
12.         #xlColorRed = 3  
13.         #xlColorGreen =10  
14.         #xlColorBlue =5  
15.         #xlColorYellow =6  
16.         #xlColorPurple = 7 # zi se  
17.         #xlColorCyan =8 #qing se  
18.         #xlBgColorYellow =19  
19.         #xlBgColorCyan =20  
20.         #xlBgColorPurple =24  
21.         #xlDefaultLineStyle = 1   
22.         @@worksheets_name =[]  
23.         def initialize(encoding="GB2312")  
24.             @excel = WIN32OLE.new("excel.application")  
25.             @excel.visible = FALSE  
26.             @workbook = @excel.Workbooks.Add()  
27.             #@style_id   = 0  
28.             @encoding = encoding  
29.             create_style  
30.         end  
31.         def add_worksheet(name)  
32.             while @@worksheets_name.include?(name)  
33.                 name +="1"  
34.             end  
35.             @@worksheets_name << name  
36.             worksheet = @workbook.Worksheets.Add()  
37.             worksheet.Activate  
38.             worksheet.name = name  
39.             return WorkSheet.new(worksheet)  
40.         end  
41.         def show  
42.             @excel.visible = TRUE  
43.         end  
44.         def close  
45.             @workbook.Close(0)  
46.             @excel.Quit()  
47.         end  
48.         def create_style  
49.             sty=@workbook.Styles.Add('NormalStyle')  
50.             sty.Font.Size = 12  
51.             sty.Borders(7).LineStyle=1  
52.             sty.Borders(8).LineStyle=1  
53.             sty.Borders(9).LineStyle=1  
54.             sty.Borders(10).LineStyle=1  
55.   
56.             sty=@workbook.Styles.Add('TitleStyle')  
57.             sty.Font.Size = 16  
58.             sty.Font.Bold =true  
59.             sty.Font.ColorIndex =3  
60.             #sty.Interior.ColorIndex = 20  
61.         end  
62.     end  
63.     #worksheet  
64.     class WorkSheet  
65.         IMAGE_ROW_NUM = 56  
66.         @@worksheets_name =[]  
67.         def initialize(worksheet)  
68.             @row_count = 1  
69.             @worksheet = worksheet  
70.         end  
71.         def add_space_line(n=1)  
72.             return if n<1>
73.             @row_count +=n  
74.         end  
75.         def add_title(name)  
76.             add_space_line  
77.             add_row.add_cell(name,false,"TitleStyle")  
78.         end  
79.         def add_row()  
80.             @current_row = Row.new(@worksheet,@row_count)  
81.             @row_count +=1  
82.             return  @current_row  
83.         end  
84.         def current_row  
85.             return  @current_row  
86.         end  
87.         def add_image(image_path)  
88.             if not File.exist?(image_path)  
89.                 return  
90.             end  
91.             add_space_line 1  
92.             add_row  
93.             cell_name=current_row.first_cell  
94.             @worksheet.Range(cell_name).Select  
95.             @worksheet.Pictures.Insert(image_path)  
96.             add_space_line  IMAGE_ROW_NUM  
97.         end  
98.     end  
99.     #row  
100.     class Row  
101.         FILL_TYPE = 4  
102.         @@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"]  
103.         def initialize(worksheet,row_id)  
104.             @row_id     =row_id  
105.             @cell_count=0  
106.             @worksheet = worksheet  
107.         end  
108.         def curent_cell  
109.             return  cell_name(@cell_count)  
110.         end  
111.         def first_cell  
112.             return cell_name(0)  
113.         end  
114.         def add_cell(value,auto_fit = false,style = "NormalStyle")  
115.             range = @worksheet.Range(cell_name(@cell_count))  
116.             range['Value'] = value.to_s;  
117.             range['Style']=style  
118.             range.Columns.AutoFit if auto_fit  
119.             @cell_count +=1  
120.         end  
121.         def cell_name(index) 
     
122.             second = index % 26  
123.             first = (index - second) / 26  
124.             if first == 0  
125.                 return @@cell_map[second]+@row_id.to_s    
126.             end  
127.             first -=1  
128.             return @@cell_map[first]+@@cell_map[second]+@row_id.to_s  
129.         end  
130.         def set_cell(index,value,auto_fit = false,style = "NormalStyle")  
131.             range=@worksheet.Range(cell_name(index))  
132.             range['Value'] = value;  
133.             range['Style']=style  
134.             range.Columns.AutoFit if auto_fit         
135.         end  
136.     end  
137. end  

测试程序

ruby 代码

 

1. require 'excel'  
2. excel = Excel::WorkBook.new  
3. worksheet = excel.add_worksheet("玛雅牛")  
4. worksheet.add_title('标题')  
5. row = worksheet.add_row  
6. row.add_cell("myaniu")  
7. row.add_cell(0)  
8. row.add_cell("2006-01-01 01:01:01")  
9. worksheet.add_image("C:\\AutoTest\\out.bmp")  
10. row = worksheet.add_row  
11. row.add_cell("玛雅牛")  
12. row.add_cell(0)  
13. row.add_cell("2006-01-01 01:01:01")  
14. worksheet.add_image("C:\\AutoTest\\out.bmp")  
15. excel.show  

由于当时该程序是具有针对性，没有太考虑通用性，有些地方还是要修改。