From: Patrick Fernie <patrick.fernie@...>
Date: 2005-08-08T03:22:12+09:00
Subject: Re: xls2csv

Here's some code I used to do some xls-> csv dumping. It's currently
rigged up to drop the files in tempfiles that some other code I have
use, so you'll need to monkey it a bit for your needs. Also, the dump
method returns a list of the temp files it created which you probably
won't need, but this is hopefully a good guide for you to hack up.

require 'win32ole'
require 'tempfile'

# The XLSdumper object is used to interface with an excel instance
# via OLE. It is used to load an xls file, and dump all non-empty
# sheets to csv files
class Tempfile
  def name
    /\:(.*)\>/.match(self.inspect).captures[0]
  end
end

module XLSdumper
  @@initialized = false
  @@excel = nil

  def XLSdumper.die
    Proc.new {
    	@@excel.Quit
    	@@excel.ole_free
	 }
  end

  # This is used to load an xls file of name _filename_ with an excel object,
  # and dump the sheets containing data to csv files.
  def XLSdumper.dump(filename, directory)
    raise IOError, "No such file #{filename}" unless File.exists? filename

    directory = directory.chomp(File::SEPARATOR)

    files = []
    begin
      unless @@initialized
       @@excel = WIN32OLE.new("excel.application")
	@@excel.DisplayAlerts = false # This should stop annoying dialogs
	ObjectSpace.define_finalizer(XLSdumper, XLSdumper.die)
        WIN32OLE.const_load(@@excel, XLSdumper)
        @@initialized = true
      end
      workbook = @@excel.workbooks.Open(filename)
      workbook.worksheets.each { |ws| 
        if ws.usedrange.cells.value # only save sheets w/ data
          base = File.basename(filename, ".xls")
          csv_file = Tempfile.new(base+"-"+ws.name)
          csv_filename = File.expand_path(csv_file.name)
          csv_file.close
          files << csv_filename
          ws.SaveAs("#{csv_filename}", XLSdumper::XlCSV)
        end
      }
      workbook.close
    rescue WIN32OLERuntimeError => error
      puts "There was an error using Excel w/ OLE:\n#{error}"
    #ensure
      #excel.Quit
      #excel.ole_free
    end
    files
  end
end

On 8/7/05, Ara.T.Howard <Ara.T.Howard@noaa.gov> wrote:
> 
> i'm trying to hack together a little win32ole program named xls2csv - function
> is obvious.  basically i want to mass convert a whole slew of xls doccuments
> into csv using the windows excel application to create validation files (the
> csv files) for testing a *nix excel file reader.  i'm sure i could batch this
> under excel but i wanted a way to spawn the command remotely to a windows box
> running sshd under cygwin so i don't have to get up - after all the windows
> box is a least 10 feet away ;-)
> 
> any pointers appreciated.
> 
> -a
> --
> ===============================================================================
> | email :: ara [dot] t [dot] howard [at] noaa [dot] gov
> | phone :: 303.497.6469
> | Your life dwells amoung the causes of death
> | Like a lamp standing in a strong breeze.  --Nagarjuna
> ===============================================================================
> 
>