From: Joel Pearson <lists@...>
Date: 2013-01-24T22:34:54+09:00
Subject: Re: Reading and looping through Excel

Here's something to get you started, connecting to excel... after that 
you can just use the API to excel's functions (record a macro, and check 
out the functions it uses when you do stuff).

Caveat Lector: Some of the lines have been squashed into multiple lines 
by the width restrictions.

FYI I used tab-delimited as I find commas too common.

_______________________________
require 'win32ole'

begin
excel = WIN32OLE::connect('excel.application')
rescue
excel = WIN32OLE::new('excel.application')
end

excel.DisplayAlerts = false

wb = excel.Workbooks.Open(Dir.pwd.gsub('/','\\') + "\\DRM.xlsx")

output_string = ''

#Loop through sheets
wb.sheets.each do |sh|

  #Turn the sheet into a string
  temp_array = sh.range( sh.cells( 1, 1 ), sh.cells.specialcells( 11 ) 
).value
  temp_array.each { |ar| output_string << "#{ ar.join("\t") }\n" }

end

File.write 'test.txt', output_string

wb.Close(0)
excel.Quit

-- 
Posted via http://www.ruby-forum.com/.