查看完整版本: 一个用 Ruby 读取 Excel 的例子

一个用 Ruby 读取 Excel 的例子

读取当前目录的 “test.xls” 的 “Sheet1” 工作簿的内容，并保存成一个对象，方便以后直接读出[code]  require 'win32ole'
  $col_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"]
  
  class Excel_Info
    attr_accessor :file_name
    attr_accessor :range
         
    def initialize(file_name)
      @file_name = file_name
      @range = {}
    end
  end

  def load_data(filename)
    obj = nil
    File.open(filename, "rb") {|f| obj = Marshal.load(f)}
    return obj
  end
      
  def save_data(obj, filename)
    File.open(filename, "wb") {|f| Marshal.dump(obj, f)}
  end
  
  def get_range_name(row, col)
    return "ERR" if (col < 1) || (row < 1) || (col > 26 * 27) || (row > 65536)
    c2 = col % 26
    c2 = 26 if c2 == 0
    c1 = (col - c2) / 26
    return $col_map[c2] + row.to_s if c1 == 0
    return $col_map[c1] + $col_map[c2] + row.to_s
  end

  def read_excel(e_name, s_name)
    $excel.WorkBooks.Open("#{$MY_LOCATION}/" + e_name)
   
    $excel.WorkSheets(s_name).Activate
    $excel_info = Excel_Info.new(e_name)
   
    rows = $max_rows
    cols = $max_cols
    rows = $excel.WorkSheets(s_name).UsedRange.Rows.Count if rows == 0
      
    for row in 1..rows
      for col in 1..cols
        $excel_info.range[get_range_name(row, col)] = $excel.Cells(row, col).value.to_s
      end
    end
   
    $excel.WorkBooks.Close()
  
  end
  
  $MY_LOCATION = Dir.getwd

  $excel = WIN32OLE.new("excel.application")
  $excel.Visible = false
  
  $max_cols = 2
  $max_rows = 0
  
  read_excel("test.xls","Sheet1")
  save_data($excel_info, "excel_info.obj")
  
  info = load_data("excel_info.obj")
  puts info.file_name
  puts info.range["A1"]
  puts info.range["A2"]
  puts info.range["B1"]
  puts info.range["B2"]

  $excel.Quit()[/code]

[[i] 本帖最后由 bbschat 于 2008-4-17 14:42 编辑 [/i]]

windows下路径有点问题，改了一下，读取成功了。

代码学习中，谢谢分享

[quote]原帖由 [i]bbschat[/i] 于 2008-3-26 20:23 发表 [url=http://www.ruby-lang.org.cn/forums/redirect.php?goto=findpost&pid=13107&ptid=3950][img]http://www.ruby-lang.org.cn/forums/images/common/back.gif[/img][/url]
读取当前目录的 “test.xls” 的 “Sheet1” 工作簿的内容，并保存成一个对象，方便以后直接读出


  require 'win32ole'
  $col_map =["","A","B","C","D","E","F","G","H","I","J","K","L","M","N","O"," ... [/quote]


很好呀！

记得去年夏天，我在日本的一家大公司开发一个银行系统，我们的日本客户要我们调查用Ruby开发一些文档的（包括大量Excel文件）的工具，当时我们不知道Ruby可以处理这类文件，还特别报告了这一点，作为未知事项。就因为这点，我们没有采用Ruby了。再后来，我看到了用Ruby处理这些文件的例子，而且不难。那时，我才知道Ruby很强大。所以喜欢它了。

感谢版主把这个例子放到这里，很有必要的。我很忙就忘了这件事，现在看到版主的帖子，就想到这件事了。可以看出我们的新版主，很用心服务大家的，把有意义的例子拿来展示给大家。

谢谢有心人！
:handshake

初学。。感觉用纯Ruby来写这些好像没那么多必要···
如果能加上.net 或者 java。。那感觉好多了

class <span href="tag.php?name=Excel" onclick="tagshow(event)" class="t_tag">Excel</span>_Info
这句我一直执行不过去，不知道为什么？？相似的span块也执行不下去:Q

学习了~~谢谢分享~！

SPAN 统统去掉就OK了

require 'win32ole'
$col_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"]
class Excel_Info
   attr_accessor :file_name
   attr_accessor :range
   def initialize(file_name)
     @file_name = file_name
     @range = {}
   end
end

def load_data(filename)
   obj = nil
   File.open(filename, "rb") {|f| obj = Marshal.load(f)}
   return obj
end

def save_data(obj, filename)
   File.open(filename, "wb") {|f| Marshal.dump(obj, f)}
end

def get_range_name(row, col)
   return "ERR" if (col < 1) || (row < 1) || (col > 26 * 27) || (row > 65536)
   c2 = col % 26
   c2 = 26 if c2 == 0
   c1 = (col - c2) / 26
   return $col_map[c2] + row.to_s if c1 == 0
   return $col_map[c1] + $col_map[c2] + row.to_s
end

def read_excel(e_name, s_name)
   $excel.WorkBooks.Open(e_name)
   $excel.WorkSheets(s_name).Activate
   $excel_info = Excel_Info.new(e_name)
   rows = $max_rows
         cols = $max_cols
   rows = $excel.WorkSheets(s_name).UsedRange.Rows.Count if rows == 0
   for row in 1..rows
     for col in 1..cols
       $excel_info.range[get_range_name(row, col)] = $excel.Cells(row, col).value.to_s
     end
   end

   $excel.WorkBooks.Close()
end

$MY_LOCATION = Dir.getwd
$excel = WIN32OLE.new("excel.application")
$excel.Visible = false
$max_cols = 2
$max_rows = 0
read_excel("d:\\test.xls","Sheet1")
save_data($excel_info, "excel_info.obj")
info = load_data("excel_info.obj")
puts info.file_name
puts info.range["A1"]
puts info.range["A2"]
puts info.range["B1"]
puts info.range["B2"]
$excel.Quit()

谢谢楼上指点，那SPAN语句在这个程序中什么作用呢？

刚开始学习Ruby，嘿嘿~~要学的还很多啊~~~

我一般处理excel，直接解析xml，就不用什么win321ole那么繁琐了.。。。

[quote]原帖由 [i]conanin[/i] 于 2008-6-6 10:00 发表 [url=http://www.ruby-lang.org.cn/forums/redirect.php?goto=findpost&pid=16462&ptid=3950][img]http://www.ruby-lang.org.cn/forums/images/common/back.gif[/img][/url]
谢谢楼上指点，那SPAN语句在这个程序中什么作用呢？ [/quote]
我个人觉得……是代码转换器转换过程中有点小bug的结果