读取当前目录的 “test.xls” 的 “Sheet1” 工作簿的内容，并保存成一个对象，方便以后直接读出

[Copy to clipboard] [ - ]

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()

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

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

代码学习中，谢谢分享

引用:

原帖由 bbschat 于 2008-3-26 20:23 发表
读取当前目录的 “test.xls” 的 “Sheet1” 工作簿的内容，并保存成一个对象，方便以后直接读出


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

很好呀！

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

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

谢谢有心人！

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

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

学习了~~谢谢分享~！

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那么繁琐了.。。。

引用:

原帖由 conanin 于 2008-6-6 10:00 发表
谢谢楼上指点，那SPAN语句在这个程序中什么作用呢？

我个人觉得……是代码转换器转换过程中有点小bug的结果