使用Ruby生成图文并茂的Excel

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

ruby 代码

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="GB2312")
            @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

测试程序

ruby 代码

require 'excel'
excel = Excel::WorkBook.new
worksheet = excel.add_worksheet("玛雅牛")
worksheet.add_title('标题')
row = worksheet.add_row
row.add_cell("myaniu")
row.add_cell(0)
row.add_cell("2006-01-01 01:01:01")
worksheet.add_image("C:\\AutoTest\\out.bmp")
row = worksheet.add_row
row.add_cell("玛雅牛")
row.add_cell(0)
row.add_cell("2006-01-01 01:01:01")
worksheet.add_image("C:\\AutoTest\\out.bmp")
excel.show

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