Excel is not closed in runtime using excel.Quit()

Ruhul_Amin · 28 March 2007 08:41

hi guys,
I want to read some data from each of the files from a directory
and save it to database.In the time of read operation each excel file is
open but not close after read from the file. After processing of all
files the excel is closed.
here is the code..
code

count=100
i=0
while i<count
i=i+1
begin
excel = WIN32OLE::new('excel.Application') # create winole Object
    workbook = excel.Workbooks.Open("#{path}") # Open the Excel file
    worksheet = workbook.Worksheets(1) #get hold of the first worksheet
    worksheet.Select # select the worksheet
    title = worksheet.Range('h3')['Value'] #get value of title
    excel.ActiveWorkbook.Close(0) # close the workbook
    excel.Quit() # close Excel file
   rescue
     excel.Quit()
   ensure
     excel.Quit() unless excel.nil?
   end
end

code end

For 50/100 or more files, too many number of excel processes are shown
in the process list of Test manager.The cpu utility becomes 100% and
memory(RAM) becomes full and computer becomes very slow, almost hung.

Please review the code where I made mistake.

please help me.

···

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

Ruhul_Amin · 28 March 2007 09:21

Ruhul Amin wrote:

hi guys,

# close Excel file

   rescue
     excel.Quit()
   ensure
     excel.Quit() unless excel.nil?
   end
end

code end

For 50/100 or more files, too many number of excel processes are shown
in the process list of Test manager.The cpu utility becomes 100% and
memory(RAM) becomes full and computer becomes very slow, almost hung.

Please review the code where I made mistake.

please help me.

please please please please help me.

···

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

Dave_Burt2 · 28 March 2007 10:35

Ruhul Amin wrote:

hi guys,
I want to read some data from each of the files from a directory
and save it to database.In the time of read operation each excel file is
open but not close after read from the file. After processing of all
files the excel is closed.
here is the code..
...

The problem itself isn't immediately obvious to me, but:
1) You haven't listed actual working code, which makes helping hard.
(what are path and title?)
2) Why don't you reuse just a single Excel instance?
3) In tests I've just performed against Win32OLE version 0.6.5, the
Excel process doesn't terminate until the WIN32OLE instance is garbage
collected. Try using GC.start to toast those old instances. (This may be
a new thing, and may be a bug.)

Cheers,
Dave

_Pena_Botp1 · 28 March 2007 11:20

On Behalf Of Ruhul Amin:
# count=100
# i=0
# while i<count
# i=i+1
# begin
# excel = WIN32OLE::new('excel.Application') # create winole Object
             ^^^^^
      put the above line of code outside of your while loop
      we'll just be opening and closing workbooks

# workbook = excel.Workbooks.Open("#{path}") # Open the Excel file
^^^^^^^^^^^^
make sure this path works

# worksheet = workbook.Worksheets(1) #get hold of the first
# worksheet
# worksheet.Select # select the worksheet
# title = worksheet.Range('h3')['Value'] #get value of title
# excel.ActiveWorkbook.Close(0) # close the workbook
# excel.Quit() # close Excel file

remove all the rescues first (many times they hides errors).
let all errors go splat on the screen.

# rescue
# excel.Quit()
# ensure
# excel.Quit() unless excel.nil?
# end
# end

···

#

Mike_Woodhouse · 28 March 2007 11:10

3) In tests I've just performed against Win32OLE version 0.6.5, the
Excel process doesn't terminate until the WIN32OLE instance is garbage
collected. Try using GC.start to toast those old instances. (This may be
a new thing, and may be a bug.)

That looks to be about right - without GC.start inside the loop, a
wodge (collective noun?) of Excel processes appears - with garbage
collection the total wodge size seems to stay fairly steady at about 3
instances.

- Mike

Ruhul_Amin · 28 March 2007 11:37

The problem itself isn't immediately obvious to me, but:
1) You haven't listed actual working code, which makes helping hard.
(what are path and title?)
2) Why don't you reuse just a single Excel instance?
3) In tests I've just performed against Win32OLE version 0.6.5, the
Excel process doesn't terminate until the WIN32OLE instance is garbage
collected. Try using GC.start to toast those old instances. (This may be
a new thing, and may be a bug.)

Cheers,
Dave

Dear Dave,
Thx for ur responce. This is my working code.I can not understand how
can I use the single Excel instance for different files.

def read_excel(file_name)
destination_file_name=file_name
          begin
         excel = WIN32OLE::new('excel.Application') # create winole
Object
        workbook = excel.Workbooks.Open("#{curdir}
\\checkedfiles\\#destination_file_name}") # Open the Excel file

         worksheet = workbook.Worksheets(1) #get hold of the first
worksheet
         worksheet.Select # select the worksheet
         title = worksheet.Range('h3')['Value'] #get value of title
         excel.ActiveWorkbook.Close(0) # close the workbook
         excel.Quit() # close Excel file
         excel.Quit() unless excel.nil?
       rescue
          puts "Excel file problem"
          excel.Quit()
       ensure
          excel.Quit() unless excel.nil?
           end
       end

read_excel(file_name) # This function will be called for atleast 100
times #for 100 different files.

Waiting for ur responce. Thx in advamced.
Amin

···

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

Dave_Burt2 · 28 March 2007 12:20

Ruhul Amin wrote:

Dear Dave,
Thx for ur responce. This is my working code.I can not understand how
can I use the single Excel instance for different files.

Just take WIN32OLE.new out of the loop.

You can do this in a couple of ways. Both ways involve taking the
WIN32OLE.new and #Quit calls out of the loop:

@excel = WIN32OLE.new("Excel.Application")

main_loop {|filename| read_excel(filename) }

@excel.Quit

@excel = nil
GC.start

Now, in read_excel, you can either:

1) remove the WIN32OLE::new and excel.Quit lines, and replace all
"excel" with "@excel"
OR
2) replace WIN32OLE::new with WIN32OLE::connect, and remove excel.Quit.

Cheers,
Dave

Masaki_Suketa · 28 March 2007 12:28

Hello,

Dear Dave,
Thx for ur responce. This is my working code.I can not understand how
can I use the single Excel instance for different files.

Try the following style.
Create single Excel instance out of read_excel method, and
call excel.quit out of read_excel method.
And, pass the single Excel instance to read_excel method
as a argument.

def read_excel(excel, file_name)
  destination_file_name=file_name
  begin
    # You should not call WIN32OLE.new('excel.Application') in
    # read_excel method.
    # excel = WIN32OLE::new('excel.Application')
    workbook = excel.Workbooks.Open("#{curdir}
     ....
  rescue
    # You should not call excel.quit
    # excel.quit in read_excel method.
  end
end

excel = WIN32OLE.new('excel.Application')
read_excel(excel, file_name) # 100 times call
excel.quit

Regards

···

In message "Re: excel is not closed in runtime using excel.Quit()" on 07/03/28, Ruhul Amin <tuhin_cse99@yahoo.com> writes:

_Pena_Botp1 · 29 March 2007 07:17

On Behalf Of Ruhul Amin:
# This is my working code.I can not understand how
# can I use the single Excel instance for different files.

just open an excel instance or app.
fr there, nest the opening and
closing of multiple workbooks.
close/quit the excel app when finish.

to use ruby's power and elegance, you may create an excel class that automatically closes the opened instances and or workbooks.

eg.

C:\family\ruby\excel>cat test4.rb

···

#---------------------------------------------------------------
# make an Excel class to simplify opening & closing of classes
# place this in a separate file, and just require it
#---------------------------------------------------------------
class Excel
   attr_accessor :excel
   def initialize
      require 'win32ole'
      @excel = WIN32OLE::new('Excel.Application')
      yield self
      @excel.Quit
   end

   def open_book file
      book = self.excel.Workbooks.Open file
      yield book
      self.excel.ActiveWorkbook.Close(0)
   end
end

#---------------------------------------------------------------
# let's try using our excel class
#---------------------------------------------------------------
# declare array of files we want to read
Xfiles = %w(
            c:\\family\\ruby\\test.xls
            c:\\family\\ruby\\test1.xls
            c:\\family\\ruby\\test2.xls
            c:\\family\\ruby\\test3.xls
            c:\\family\\ruby\\test4.xls
            c:\\temp\\test1.xls
            c:\\temp\\test2.xls
            )

#----------------
# ok, lets do it!
#----------------

         # do whatever you want to do with your workbook here
         worksheet = workbook.Worksheets(1)
         worksheet.Select # select the worksheet
         title = worksheet.Range('H3')['Value'] #get value of title

         # we just display the count, filename, and H3 value
         puts "#{i+1}:"
         puts " pathname : #{file}"
         puts " title: #{title}"

end #excel.open_book
end #xfiles.each
end #Excel.open

C:\family\ruby\excel>ruby test4.rb
1:
   pathname : c:\family\ruby\test.xls
   title: Title of Workbook of file test
2:
   pathname : c:\family\ruby\test1.xls
   title: this is test 1 title
3:
   pathname : c:\family\ruby\test2.xls
   title: the value of h3
4:
   pathname : c:\family\ruby\test3.xls
   title: another title for test3 file
5:
   pathname : c:\family\ruby\test4.xls
   title: Title of Workbook
6:
   pathname : c:\temp\test1.xls
   title: Title of Workbook
7:
   pathname : c:\temp\test2.xls
   title: Title of Workbook

C:\family\ruby\excel>

in this example, it is important to note that
   1 we did not have to specify the closing of the instance/workbooks
   2 code blocks allowed us to very flexible
   3 using array#each we didn't need to count

the above run took less than 2 seconds to finish; and i didn't even notice any tiny memory hiccup whatsoever. for 100 average excel files, it should take you less than a minute to process all.

hth.
kind regards -botp

Dong_Wang · 29 March 2007 16:00

Usually, I use %x{tskill excel} for finall clean.

···

On Mar 28, 8:28 pm, Masaki Suketa <masaki.suk...@nifty.ne.jp> wrote:

Hello,

In message "Re: excel is not closed in runtime using excel.Quit()" > on 07/03/28, Ruhul Amin <tuhin_cs...@yahoo.com> writes:

> Dear Dave,
> Thx for ur responce. This is my working code.I can not understand how
> can I use the single Excel instance for different files.

Try the following style.
Create single Excel instance out of read_excel method, and
call excel.quit out of read_excel method.
And, pass the single Excel instance to read_excel method
as a argument.

def read_excel(excel, file_name)
  destination_file_name=file_name
  begin
    # You should not call WIN32OLE.new('excel.Application') in
    # read_excel method.
    # excel = WIN32OLE::new('excel.Application')
    workbook = excel.Workbooks.Open("#{curdir}
     ....
  rescue
    # You should not call excel.quit
    # excel.quit in read_excel method.
  end
end

excel = WIN32OLE.new('excel.Application')
read_excel(excel, file_name) # 100 times call
excel.quit

  Regards

Topic		Replies	Views
Too many number of excel processes are shown in the process ruby-talk	1	88	28 March 2007
Win32ole: application.quit() leave process running ruby-talk	9	81	30 July 2007
How to operate an existing Excel file? ruby-talk	3	167	9 August 2007
Using ole_free for Excel on W2K (Ruby 1.8.0-10) ruby-talk	0	96	10 February 2004
How to completely terminate a WIN32OLE Excel object? ruby-talk	6	91	28 May 2007

Excel is not closed in runtime using excel.Quit()

Related Topics