[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.ruby

excel is not closed in runtime using excel.Quit

Ruhul Amin

3/28/2007 8:41:00 AM

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-....
7 Answers

Ruhul Amin

3/28/2007 9:22:00 AM

0

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-....

Dave Burt

3/28/2007 10:32:00 AM

0

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

Mike Woodhouse

3/28/2007 11:09:00 AM

0


> 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

3/28/2007 11:38:00 AM

0

> 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-....

Dave Burt

3/28/2007 12:15:00 PM

0

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

3/28/2007 12:29:00 PM

0

Hello,

In message "Re: excel is not closed in runtime using excel.Quit()"
on 07/03/28, Ruhul Amin <tuhin_cse99@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

Wang Dong

3/29/2007 3:57:00 PM

0

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

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