`

Ruby : 操作Excel的API介绍

阅读更多

语言这个东西还是需要有事儿没事儿写一写的,所以已经打算把工作中使用的脚本语言改成Ruby,

项目需要,前些天刚写了篇C# API: 生成和读取Excel文件

顺便也看一下Ruby是怎么操作Excel的吧.

 

在网上搜了一下,作为展示excel用法的例子,

这个链接是我最喜欢的链接之一: 使用win32ole操作Excel-2

这个例子很简单, 只有source, 为我们演示了如何进行下面的操作:

(本文最后的其他资源部分中, 给出了些更详细丰富内容的链接)

■更改sheet的名字, 以及通过名字索引sheet

■修改单元格的内容, 包括将单元格的内容设置为Excel公式

■修改单元格字体的大小和颜色

■修改单元格的背景颜色

■修改单元格的其他格式(时间日期的显示方式, 显示方式是居左还是居右等)

■为单元格增加批注

■为单元格指定超链接

■合并单元格

■修改单元格的网格线

■如何设置窗口锁定

■......

 

这些功能足以满足我的需求, 生成一份容易读也容易用的Excel文档.

只是原链接只提供了source, 致使通过搜索引擎搜到他的人要少一些.

代码转帖如下:

 

require 'win32ole'

class Engines

  def time_now
    initialize
    @t =Time.now
    a = @t.to_s.split(" ")
    s = a[-1].to_s+'-'+"#{@t.mon}"+'-'+a[2].to_s
    @time_now= s+' '+a[3].to_s
    #@time_now=@t.year+''+@t.ymonth+''+@t.mday
    @time_now_hsm =a[3].to_s
  end
  def initialize
    @OnlyInitOnce = true
    @t
    @objSheet
    @excel
    @sStatus
    @sStepName
    @sStepName
    @sStepName
    @sDetails
    @@TestcaseName=@@a
  end
  def excel_new(encoding="utf-8")
    initialize
    @worksheets_name =[]
    @excel = WIN32OLE::new("EXCEL.APPLICATION")
    @excel.visible=true
    @workbook = @excel.Workbooks.Add()
    @encoding = encoding
  end
  def excelsheet_name(name)

    while @@worksheets_name.include?(name)
      name +="1"
    end
    @@worksheets_name << name
    worksheet = @workbook.Worksheets.Add()
    worksheet.Activate
    worksheet.name = name
  end
  def excel_quit
    @excel.Quit                      # 退出当前Excel文件
    # @workbook.close                        关闭表sheet空间
    # exec('taskkill /f /im Excel.exe ')  强制关闭所有的Excel进程
  end
  def CreateResultFile(filepath)
    excel_new
    @excel.DisplayAlerts = false

    @objSheet =  @excel.Sheets.Item(1)
    @excel.Sheets.Item(1).Select
    @objSheet.Name = "测试概要"

    @objSheet.Range("B1").Value = "测试结果"
    #合并单元格
    @objSheet.Range("B1:E1").Merge
    #水平居中 -4108
    @objSheet.Range("B1:E1").HorizontalAlignment = -4108
    @objSheet.Range("B1:E1").Interior.ColorIndex = 53
    @objSheet.Range("B1:E1").Font.ColorIndex = 5
    @objSheet.Range("B1:E1").Font.Bold = true
    @objSheet.Range("B1:E1").Font.Size =24

    @objSheet.Range("B2:E2").Merge
    @objSheet.Rows(2).RowHeight = 20

    rowNum = [3,4,5,6,7,8]
    rowNum.each {|re|  @objSheet.Range("C#{re}:E#{re}").Merge}

    @objSheet.Range("B9:E9").Merge
    @objSheet.Rows(9).RowHeight = 30

    #Set the Date and time of Execution
    @objSheet.Range("B3").Value = "测试日期: "
    @objSheet.Range("B4").Value = "开始时间: "
    @objSheet.Range("B5").Value = "结束时间: "
    @objSheet.Range("B6").Value = "持续时间: "
    #@objSheet.Range("C3").Value = Date
    @objSheet.Range("C4").Value = time_now
    @objSheet.Range("C5").Value = time_now
    @objSheet.Range("C6").Value = "=R[-1]C-R[-2]C"
    @objSheet.Range("C6").NumberFormat ="[h]:mm:ss;@"

    #Set the Borders for the Date & Time Cells
    @objSheet.Range("B3:E8").Borders(1).LineStyle = 1
    @objSheet.Range("B3:E8").Borders(2).LineStyle = 1
    @objSheet.Range("B3:E8").Borders(3).LineStyle = 1
    @objSheet.Range("B3:E8").Borders(4).LineStyle = 1

    #Format the Date and Time Cells
    @objSheet.Range("B3:E8").Interior.ColorIndex = 40
    @objSheet.Range("B3:E8").Font.ColorIndex = 12
    @objSheet.Range("B3:A8").Font.Bold = true

    #Track the Row Count and insrtuct the viewer not to disturb this
    @objSheet.Range("C7").AddComment
    @objSheet.Range("C7").Comment.Visible = false
    @objSheet.Range("C7").Comment.Text "这点生成的数据大家不要删除哦"
    @objSheet.Range("C7").Value = "0"
    @objSheet.Range("B7").Value = "用例总数:"

    #Track the Testcase Count Count and insrtuct the viewer not to disturb this
    @objSheet.Range("C8").AddComment
    @objSheet.Range("C8").Comment.Visible = false
    @objSheet.Range("C8").Comment.Text "这点数据别删除了 删除了 你会后悔的"
    @objSheet.Range("C8").Value = "0"
    @objSheet.Range("B8").Value = "步骤总数:"

    @objSheet.Range("B10").Value = "测试用例名称"
    @objSheet.Range("D10").Value = "状态"
    @objSheet.Range("E10").Value = "步骤数"
    @objSheet.Hyperlinks.Add(@objSheet.Range("B9"), "","测试结果!A1")
    @objSheet.Range("B9").Value = "点击测试用例名称打开详情页面."



    #      @objSheet.Hyperlinks.Add(@objSheet.Range("B9"), "http://www.163.com")
    #Format the Heading for the Result Summery
    @objSheet.Range("B10:C10").Merge
    @objSheet.Range("B10:E10").Interior.ColorIndex = 53
    @objSheet.Range("B10:E10").Font.ColorIndex = 19
    @objSheet.Range("B10:E10").Font.Bold = true

    #Set the Borders for the Result Summery
    @objSheet.Range("B10:E10").Borders(1).LineStyle = 1
    @objSheet.Range("B10:E10").Borders(2).LineStyle = 1
    @objSheet.Range("B10:E10").Borders(3).LineStyle = 1
    @objSheet.Range("B10:E10").Borders(4).LineStyle = 1

    #Set Column width
    @objSheet.Columns("B:E").Select

    #@objSheet.Columns("B:D").Autofit

    @objSheet.Range("B11").Select
    @objSheet.Range("B11").ColumnWidth=12
    @objSheet.Range("C11").ColumnWidth=50
    @objSheet.Range("D11").ColumnWidth=15
    @objSheet.Range("E11").ColumnWidth=15

    #Freez pane
    @excel.ActiveWindow.FreezePanes = true

    #Get the object of the first sheet in the workbook
    @objSheet = @excel.Sheets.Item(2)
    @excel.Sheets.Item(1).Select


    #Rename the first sheet to "Test_Result"
    @objSheet.Name = "测试结果"

    #Set the Column widths
    @objSheet.Columns("A:A").ColumnWidth = 30
    @objSheet.Columns("B:B").ColumnWidth = 8
    @objSheet.Columns("C:D").ColumnWidth = 35
    @objSheet.Columns("E:E").ColumnWidth = 35
    @objSheet.Columns("A:E").HorizontalAlignment =  -4131

    @objSheet.Columns("A:E").WrapText = true

    #Set the Heading for the Result Columns
    @objSheet.Range("A1").Value = "步骤"
    @objSheet.Range("B1").Value = "状态"
    @objSheet.Range("C1").Value = "期望结果"
    @objSheet.Range("D1").Value = "实际结果"
    @objSheet.Range("E1").Value = "错误信息"

    #Format the Heading for the Result Columns
    @objSheet.Range("A1:E1").Interior.ColorIndex = 53
    @objSheet.Range("A1:E1").Font.ColorIndex = 19
    @objSheet.Range("A1:E1").Font.Bold = true

    #Set the Borders for the Result Header
    @objSheet.Range("A1:E1").Borders(1).LineStyle = 1
    @objSheet.Range("A1:E1").Borders(2).LineStyle = 1
    @objSheet.Range("A1:E1").Borders(3).LineStyle = 1
    @objSheet.Range("A1:E1").Borders(4).LineStyle = 1
    #                    .Range("A2").Select

    #Freez pane
    @excel.ActiveWindow.FreezePanes = true

    @objSheet = @excel.Sheets.Item(3)
    @excel.Sheets.Item(1).Select

    @objSheet.Name = "使用说明"
    @objSheet.Columns("A:A").ColumnWidth = 100
    @objSheet.Rows("2:2").RowHeight = 150
    @objSheet.Range("A1:A1").Font.Bold = true
    @objSheet.Range("A1").Value = "测试报告使用说明"
    @objSheet.Range("A2").Value = "点击测试用例名称即可打开测试结果页面"


    @excel.ActiveWindow.FreezePanes = true
    #Save the Workbook at the specified Path with the Specified Name
    @excel.ActiveWorkbook.saveas "#{filepath}"
    @workbook.close

  end
  def reporter (sStatus, sStepName,sExpected,sActual, sDetails)

    #path =  File.join(File.dirname(__FILE__))
    #定位到具体的excel文件,本功能对应excel为:发布宝贝.xls

    #data_source = File.join(path,'发布宝贝.xls')
    #@@TestcaseName =@@a #__FILE__
    @WorkBookopen= @excel.Workbooks.Open("D:\\test.xls")
    @objSheet = @excel.Sheets("测试概要")
    @excel.Sheets("测试概要").Select
    @Row = (@objSheet.Range("C8").Value + 2*@objSheet.Range("C7").Value + 2).to_i
    @TCRow = (@objSheet.Range("C7").Value + 11).to_i
    @NewTC = false
    @objSheet.Range("B#{@Row+9}:C#{@Row+9}").Merge
    #Check if it is a new Tetstcase
    if @objSheet.Cells(@TCRow-1, 2).Value != @@TestcaseName
      @objSheet.Range("B#{@Row+9}:C#{@Row+9}").Merge
      @objSheet.Cells(@TCRow, 2).Value = @@TestcaseName
      @objSheet.Hyperlinks.Add @objSheet.Cells(@TCRow, 2), "", "测试结果!A#{ @Row+1}", @@TestcaseName
      @objSheet.Cells(@TCRow,4).Value = sStatus

      case sStatus
      when "Fail"
        @objSheet.Range("D#{@TCRow}").Font.ColorIndex = 3
      when "Pass"
        @objSheet.Range("D#{@TCRow}").Font.ColorIndex = 50
      when "Warning"
        @objSheet.Range("D#{@TCRow}").Font.ColorIndex = 46
      else
        puts "报告参数书写错误:请输入 Fail or Pass or Warning 三个值"
      end

      @objSheet.Cells(@TCRow, 5).Value = 1
      @NewTC = true
      @objSheet.Range("C7").Value = @objSheet.Range("C7").Value + 1

      #Set the Borders for the Result Header
      @objSheet.Range("B#{@TCRow}:E#{@TCRow}").Borders(1).LineStyle = 1
      @objSheet.Range("B#{@TCRow}:E#{@TCRow}").Borders(2).LineStyle = 1
      @objSheet.Range("B#{@TCRow}:E#{@TCRow}").Borders(3).LineStyle = 1
      @objSheet.Range("B#{@TCRow}:E#{@TCRow}").Borders(4).LineStyle = 1

      #Set color and Fonts for the Header
      @objSheet.Range("B#{@TCRow}:E#{@TCRow}").Interior.ColorIndex = 19
      @objSheet.Range("B#{@TCRow}").Font.ColorIndex = 53
      @objSheet.Range("B#{@TCRow}:E#{@TCRow}").Font.Bold = true
    else
      @objSheet.Range("E#{@TCRow-1}").Value = (@objSheet.Range("E#{@TCRow-1}").Value) + 1
    end

    if  (@NewTC!=true) and (sStatus == "Fail")
      @objSheet.Cells(@TCRow-1, 4).Value = "Fail"
      @objSheet.Range("D#{@TCRow-1}").Font.ColorIndex = 3
    end
    if  (@NewTC!=true) and (sStatus == "Warning")
      if @objSheet.Cells(@TCRow-1, 4).Value == "Pass"
        @objSheet.Cells(@TCRow-1, 4).Value = "Warning"
        @objSheet.Range("D#{@TCRow-1}").Font.ColorIndex = 46
      end
    end

    @objSheet.Range("C8").Value = @objSheet.Range("C8").Value + 1
    #Update the End Time
    @objSheet.Range("C5").Value = time_now

    #Set Column width
    @objSheet.Columns("B:E").Select
    @objSheet.Columns("B:E").Autofit


    #Select the Result Sheet
    @objSheet = @excel.Sheets("测试结果")
    @excel.Sheets("测试结果").Select

    #Enter the Result
    if @NewTC
      @objSheet.Range("A#{@Row}:E#{@Row}").Interior.ColorIndex = 15
      @objSheet.Range("A#{@Row}:E#{@Row}").Merge
      @Row = @Row + 1
      @objSheet.Range("A#{@Row}:E#{@Row}").Merge
      @objSheet.Range("A#{@Row}").Value = @@TestcaseName
      #Set color and Fonts for the Header
      @objSheet.Range("A#{@Row}:E#{@Row}").Interior.ColorIndex = 19
      @objSheet.Range("A#{@Row}:E#{@Row}").Font.ColorIndex = 53
      @objSheet.Range("A#{@Row}:E#{@Row}").Font.Bold = true
      @Row = @Row + 1
    end
    @objSheet.Range("A#{@Row}").Value = sStepName
    @objSheet.Range("B#{@Row}").Value = sStatus
    @objSheet.Range("B#{@Row}").Font.Bold = true

    case sStatus
    when "Pass"
      @objSheet.Range("B#{@Row}").Font.ColorIndex = 50
      @objSheet.Range("B#{@Row}").Font.Bold = true
    when "Fail"
      @objSheet.Range("A#{@Row}:E#{@Row}").Font.ColorIndex = 3
    when "Warning"
      @objSheet.Range("A#{@Row}:E#{@Row}").Font.ColorIndex = 46
    else
      puts "你的报告参数书写错误:请输入 Fail or Pass or Warning 三个值"
    end

    @objSheet.Range("B#{@Row}").Font.Bold = true
    @objSheet.Range("C#{@Row}").Value = sExpected
    @objSheet.Range("D#{@Row}").Value = sActual
    @objSheet.Range("E#{@Row}").Value = sDetails

    #Set the Borders
    @objSheet.Range("A#{@Row}:E#{@Row}").Borders(1).LineStyle = 1
    @objSheet.Range("A#{@Row}:E#{@Row}").Borders(2).LineStyle = 1
    @objSheet.Range("A#{@Row}:E#{@Row}").Borders(3).LineStyle = 1
    @objSheet.Range("A#{@Row}:E#{@Row}").Borders(4).LineStyle = 1
    @objSheet.Range("A#{@Row}:E#{@Row}").VerticalAlignment = -4160

    @excel.Sheets("测试概要").Select
    @excel.Sheets("测试概要").Range("B1").Select
    #Save the Workbook
    @WorkBookopen.save

  end
end

 

话不多说, 想看个究竟, 写下下面的代码开始Debug吧.

运行的时候会发现下面代码中@@TestcaseName=@@a的@@a没有被定义,

把@@a随便替换成一个字符串即可, 相信大家能自行搞定.

 

engines = Engines.new()
engines.CreateResultFile("c:\\test.xls")
 

 

 

其他资源

 

资源1

Programming Ruby: Ruby and Microsoft Windows

也可以下载电子书, 这一章有关于Windows Automation的讨论

 

资源2

The Ruby Spreadsheet

spreadsheet @rubygems.org

How To Generate Excel Files with Ruby

另一套api,可操作excel, open office等

 

资源3

RubyGarden Archives: Scripting Excel@Ruby on Windows

我很喜欢很喜欢这篇文章, 他跟最前面的例子不同, 并非一个完整的例子,

而是针对一个一个问题,以及对应的答案(如果你想干这个事儿, 那么这么干...)

我最喜欢的是下面的代码:

在对Excel进行设置的时候, 很多时候用到的都是一些数字,

我想要的效果,对应的设置数字是多少,这个问题一直很困惑我,

这个脚本可以帮助我们查询Excel都有预定义了哪些常量, 以及他们的值都是多少.

 

require 'win32ole'

module ExcelConsts
end

excel = WIN32OLE.new("Excel.Application")
WIN32OLE.const_load(excel, ExcelConsts)
excel.quit()

puts 'Matches for: ' + ARGV[0]
ExcelConsts.constants.each {|const|
    match = const.match(/#{ARGV[0]}/)
    value = eval("ExcelConsts::#{const}")
    puts ' '*4 + const + ' => ' + value.to_s unless match.nil?
}

 

比如我们使用Center参数, 运行上面的脚本:

ruby search_excel_consts.rb Center

 

 我们便会得到这样的结果:

XlCenterAcrossSelection => 7
XlVAlignCenter => -4108
XlCenter => -4108
XlLabelPositionCenter => -4108
XlPhoneticAlignCenter => 2
XlHAlignCetner => -4108
XlHAlignCenterAcrossSelection => 7

 

更棒的是, 如果我们将所有的常量导入到了一个类中,

我们便可以使用这些常量来代替那些魔幻数字, 来对Excel进行设置和操作,

比如像这样(注意ExcelConst::XlColorIndexNone的使用)...

 

worksheet.Range('a3:f5').Interior['ColorIndex'] = 36 #pale yellow
# Set background color back to uncoloured (rnicz)
worksheet.Range('a3:f5').Interior['ColorIndex'] = -4142 # XlColorIndexNone constant
# or use Excel constant to set background color back to uncoloured
worksheet.Range('a3:f5').Interior['ColorIndex'] = ExcelConst::XlColorIndexNone

 

 

 

资源4

Color Palette and the 56 Excel ColorIndex Colors

Excel Color Palette and Color Index change using VBA

如果要设置颜色,可以直接设置Color, 或者设置相应的ColorIndex

我更愿意使用ColorIndex, 关于ColorIndex和对应的颜色的色表, 可以参考上面的两个连接.

第一个连接特别详细,

第二个连接则介绍了如何使用VBA在Excel文件中生成这些色表, 同时还提供了一个xls格式的色表文件下载.

 

@objSheet.Range("B1:E1").Interior.Color = 255 # red
@objSheet.Range("B1:E1").Interior.ColorIndex = 3 # red

 

 

 

资源5

如果对Excel都是读操作, 可以试一下这个gem:

http://roo.rubyforge.org/

http://roo.rubyforge.org/rdoc/index.html

 

他支持下面四种文件格式的读操作

■Open-office spreadsheets (.ods)
■Excel spreadsheets (.xls)
■Google (online) spreadsheets
■Excel’s new file format .xlsx

 

 

 

 

 

分享到:
评论
2 楼 wjason 2012-06-26  
使用ruby来操作excel文件 Win32ole
http://hlee.iteye.com/blog/407450
1 楼 wjason 2012-06-21  
代码好长, javaeye的代码要是能折叠起来就好了

相关推荐

    Intrinio-Ruby:用Ruby编写的Intrinio的HTTP包装器

    这是Intrinio API( )的Ruby包装器 “欢迎使用Intrinio API!我们为美国上市公司提供广泛的基本,财务和价格数据选择。IntrinioAPI的主要应用程序是程序设计应用程序或使用Excel加载项的最终用户。” 内部文档 请...

    tabledata:使用通用API处理各种基于表格的格式,例如Excel(.xls,.xlsx)和CSV

    读取各种格式的表格数据,例如Excel .xls,Excel .xlsx,CSV。 安装 gem install tabledata 用法 table1 = Tabledata.table file: 'some/excelfile.xls' table2 = Tabledata.table header: %w[header1 header2], ...

    sepomex:SEPOMEX数据库的REST API

    Sepomex是一个REST API,可映射墨西哥当前邮政... 您可以从获取CSV或Excel文件 我们构建此API的目的是为开发人员提供一种查询全国邮政编码,州和城市的方法。 目录 快速开始 开始使用JSON响应的基本URI在以下位置: ...

    xmlss:使用 XML 电子表格标记为 MS Excel 生成电子表格文档

    它提供了一个用于构建电子表格数据的 api,然后使用该数据生成可由 MS Excel 解释的 xml。 ** 注意:此 gem 仅根据 2001 年 8 月 XML 电子表格规范 ( ) 的子集生成 XML。 它不会生成更现代的开放式办公室电子表格...

    ASP EXCEL导入SQL

     RubyonRails框架(简称Rails或者Rails框架)是一个基于Ruby语言的越来越流行的网络应用软件开发框架。它提供了关于REST最好的支持,也是当今应用REST最成功的一个软件开发框架。Rails框架(从版本1.2.x起)成为了第一...

    spreet:spr[eadsh]eets 的通用处理程序

    兼容 Ruby ≥ 1.9.2。 为什么 ? 这个 gems 是电子表格的处理程序。 凭借其独立的 API,可以创建、更新某些格式的文件。 今天的名单不是很长: CSV:带逗号的 UTF-8(读取和写入) CSV for Excel:带分号的 CP...

    aws_training:该项目是 Stark & Wayne 用于使用 AWS 进行培训课程的脚本集合

    要求手动创建比您需要的更多的 AWS 账户将 AWS 账户详细信息存储在 Excel/Google 电子表格中并导出为 CSV 文件电子邮件、主密码、主 API 密钥OS X/Linux(目前使用 Bash 编写的帮助脚本) 重击Ruby 1.9+(首选 Ruby ...

    qor:QOR是用Go编写的一组库,抽象了业务应用程序,CMS和电子商务系统所需的常用功能

    这是对Go的原始QOR的完全重写,它是用Ruby on Rails编写并在内部使用的专有框架。 QOR 1.0是第一个以MIT许可证开源和分发的版本。 什么不是QOR QOR不是“盒装交钥匙解决方案”。 您需要适当的编码技巧才能使用它。...

    prolaera-excel-export

    该项目是通过引导的。 您将在下面找到一些有关如何执行...Ruby on Rails 在开发中代理API请求 配置代理后出现“无效的主机头”错误 手动配置代理 配置WebSocket代理 在开发中使用HTTPS 在服务器上生成动态&lt;met

    squealer:将MongoDB文档以声明方式转换为SQL表(树到矩形)

    尽管许多业务问题最好用电子表格(元组空间)来表示,尽管尽管有些令人不安的事实是,世界上大多数关键的商业系统都依赖于Excel电子表格,但并非每个问题都以这种方式来建模最好 MongoDB(以及越来越多的其他noSQL

    PDF文件JAVA去水印源码-PDF-Converter-Services-Online:使用基于REST的云服务转换、合并、水印、安全和OC

    PDF文件JAVA去水印源码Muhimbi PDF Converter 在线服务 关于 这是一项基于 REST 的服务,可以轻松地将文档转换、合并、水印、保护和光学字符识别 ...API 的定义。 适用于PHP、Ruby、Python、Java、C#、Curl、JavaS

    Animania:动画片

    实现 Flash Card System 和 Google Translate API 实现添加用户。 实现样式永久链接到图像模型: ERD大纲: 未来版本: 实施更严格的用户控制 在管理面板中添加一项功能以通过 Web 应用上传词汇表的 Excel 文档 ...

    Rally-Recycle-Bin-Utilities

    回收站条目的 WSAPI URL 引用 结果输出到名为 recyclebin.csv 的 CSV 输出文件 raise_delete_recyclebin_items.rb 该脚本将输入一个 CSV 文件,该文件的字段格式与rally_recyclebin_report.rb 脚本输出的字段格式...

Global site tag (gtag.js) - Google Analytics