# Help with Excel (win32ole) and threads!?

**URL:** <https://www.ruby-forum.com/t/help-with-excel-win32ole-and-threads/89880>\
**Category:** Ruby\
**Created:** [March 23, 2007, 2:40pm UTC](https://www.ruby-forum.com/t/help-with-excel-win32ole-and-threads/89880 "2007-03-23T14:40:21Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![combas](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/aca169/32.png) [@combas](https://www.ruby-forum.com/u/combas)\
**Post date:** [March 23, 2007, 2:40pm UTC](https://www.ruby-forum.com/t/help-with-excel-win32ole-and-threads/89880/1 "2007-03-23T14:40:21Z")

</div>

I have an application which is supposed to create a set of excel  
documents to display sales information for dining areas. The core of it  
is a class called SaleReport which is initialized with a sqlite database  
connection. The main function takes an excel worksheet object, a row  
number and some info about the location and queries the database to  
populate the excel sheet. Since none of the locations rely on each  
others data, I thought it would be pretty useful to thread that part, so  
that each location report is run in its own thread. The problem is, even  
though this seems to work, it has not improved performance at all and I  
am not sure where the bottleneck is. Here are my theories and the  
example code is at the bottom:

1. My first theory was that using a single excel application instance  
was blocking the threads so that only one could have access at a time,  
but now I rewrote it to use multiple excel instances and still no dice.

2. The connection to the database is limiting access to a single thread  
at a time. This shouldnt be the case since each instance of the  
SaleReport class gets it own connection, and SQLite is threadsafe.

3. I’m flat out using the threads incorrectly.

The example code:  
#################################################################################  
require ‘win32ole’  
require ‘SaleReport.rb’

startTime = Time.now

excel\_1 = WIN32OLE::new(‘excel.Application’)  
WIN32OLE.const\_load(excel\_1)  
excel\_2 = WIN32OLE::new(‘excel.Application’)

book1 = excel\_1.Workbooks.add  
book2 = excel\_2.Workbooks.add

books = {‘book1’ =\> book1, ‘book2’ =\> book2}

locations = [ {‘name’ =\> ‘location1’, ‘num’ =\> 841},  
{‘name’ =\> ‘location2’, ‘num’ =\> 842},  
{‘name’ =\> ‘location3’, ‘num’ =\> 843} ]

books.each do |name, book|  
puts “Working on #{name}.”

threads = []  
locations.each do |loc|  
threads \<\< Thread.new(loc, name, book) { |l, n, b|  
row = 1  
worksheet = b.Worksheets(1)  
sales = SaleReport.new  
puts “Starting thread - #{l} || #{n} || #{b}”  
sales.outputLocation(ws, row, l[‘num’], l[‘name’], 2007)  
}  
end  
puts “Joining…”  
threads.each { |t| t.join }

book.SaveAs “#{name}.xls”  
book.Close(1)  
end  
excel\_1.quit  
excel\_2.quit  
excel\_1 = excel\_2 = nil  
GC.start #garbage collect

endTime = Time.now

puts “Time to run: #{endTime-startTime} seconds.”  
###############################################################################

Running this with 2 locations without any threading gave a running time  
of ~240 seconds. When I added the threading it was almost exactly the  
same. The running time increases linearly with each location I add even  
with multiple threads.

Thanks,  
-Alex

---

<div class="post-metadata">

**Author:** ![combas](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/aca169/32.png) [@combas](https://www.ruby-forum.com/u/combas)\
**Post date:** [March 23, 2007, 3:03pm UTC](https://www.ruby-forum.com/t/help-with-excel-win32ole-and-threads/89880/2 "2007-03-23T15:03:50Z")

</div>

On Fri, 23 Mar 2007, Alex C. wrote:

> example code is at the bottom:
> 
> 1. My first theory was that using a single excel application instance  
> was blocking the threads so that only one could have access at a time,  
> but now I rewrote it to use multiple excel instances and still no dice.
> 
> 2. The connection to the database is limiting access to a single thread  
> at a time. This shouldnt be the case since each instance of the  
> SaleReport class gets it own connection, and SQLite is threadsafe.

sqlite is threadsafe, but supports access by only one thread at a time.  
eg  
it’s not concurrent at the c level. the only level of concurancy sqlite  
provides is at the process level.

> 1. I’m flat out using the threads incorrectly.

it’s easy to do on windows - anything which blocks one thread as the os  
level  
will block all threads. this is suprisingly easy to do. your code  
looks  
fine. i’m not on windows, but if i were you i’d write some code that  
proves  
to myself that concurent access to an excel doc by threads does not end  
up  
blocking the whole process as i suspect it does. same goes for your  
SaleReport object.

cheers.

-a

---

<div class="post-metadata">

**Author:** ![combas](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/aca169/32.png) [@combas](https://www.ruby-forum.com/u/combas)\
**Post date:** [March 24, 2007, 11:15am UTC](https://www.ruby-forum.com/t/help-with-excel-win32ole-and-threads/89880/3 "2007-03-24T11:15:39Z")

</div>

On 3/23/07, removed\_email\_address@domain.invalid [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> > though this seems to work, it has not improved performance at all and I
> 
> blocking the whole process as i suspect it does. same goes for your  
> SaleReport object.

I suppose that as well. As ruby threads are only interpreter threads,  
I assume that each call to OLE blocks the entire interpreter.  
Therefore it should not make any difference if you call it in threads  
or not. Threads may be even slower, due to more overhead. You should  
be able to check this by printing something to screen repeatedly in  
one thread (remember setting $stdout.sync=true), and doing a long OLE  
operation in another. The hyphothesis is that the printing will stop  
while OLE is running.

To parallelize this you’d probably use more processes, either using  
Win32::Process from win32utils or manually spawning some worker  
processes and communicating with the main process using drb or  
similar.

---

<div class="post-metadata">

**Author:** ![combas](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/aca169/32.png) [@combas](https://www.ruby-forum.com/u/combas)\
**Post date:** [March 28, 2007, 6:37pm UTC](https://www.ruby-forum.com/t/help-with-excel-win32ole-and-threads/89880/4 "2007-03-28T18:37:07Z")

</div>

Jano S. wrote:

> To parallelize this you’d probably use more processes, either using  
> Win32::Process from win32utils or manually spawning some worker  
> processes and communicating with the main process using drb or  
> similar.

I did a test in the way you suggested and the WIN32OLE is blocking I’m  
pretty sure. I don’t think its blocking continuously until the thread is  
finished, but when I output 1000 rows of data to excel in one thread and  
print the numbers 1-50 to the screen in another, there is a noticeable  
pause between each number being printed (as opposed to them printing  
almost instantly w/o win32ole). I figure this means that the OLE is  
blocking on each output call as you suggested.

I did figured out how to spawn the new process with win32-process and  
found out about Marshaling. I figured I could just create a process so  
that it takes a Worksheet object (marshaled from a file) and does all  
the output to that sheet. The only problem is WIN32OLE objects have no  
marshal\_dump method defined, so I have no way to pass the sheets around  
to different processes. I did not try DRb but I figured it would have  
the same problem in this case. I could write my own marshal\_dump but I  
doubt that writing such a method for a win32ole object is within my  
scope of knowledge.

I have pretty much eliminated SQLite as the reason for the threads not  
running in parallel since I read up more about read/write locks and how  
they are used in sqlite3. All my database connections are reading and  
should be able to do so concurrently with no problems.

So anymore ideas on how to parallelize excel output over multiple  
worksheets?

Thanks for all the help!  
-Alex