Multiprocessing: Use Win32 API to modify Excel-Cells from Python
<p>I'm really looking for a good solution here, maybe the complete concept how I did it or at elast tried to do it is wrong!?</p>

<p>I want to make my code capable of using all my cores. In the code I'm modifying Excel Cells using Win32 API. I wrote a small xls-Class which can check whether the desired file is already open (or open it if not so) and set Values to Cells. My stripped down code looks like this:</p>

<pre><code>#!/usr/bin/env python
# -*- coding: utf-8 -*-
import os
import win32com.client as win32
from multiprocessing import Pool
from time import sleep

class xls:
    excel = None
    filename = None    
    wb = None
    ws = None

    def __init__(self, file):
        self.filename = file

    def getNumOpenWorkbooks(self):
        return self.excel.Workbooks.Count

    def openExcelOrActivateWb(self):
        self.excel = win32.gencache.EnsureDispatch('Excel.Application')

        # Check whether one of the open files is the desired file (self.filename)
        if self.getNumOpenWorkbooks() &gt; 0:
            for i in range(self.getNumOpenWorkbooks()):
                if self.excel.Workbooks.Item(i+1).Name == os.path.basename(self.filename):
                    self.wb = self.excel.Workbooks.Item(i+1)
                    break
        else:    
            self.wb = self.excel.Workbooks.Open(self.filename)

    def setCell(self, row, col, val):
        self.ws.Cells(row, col).Value = val  

    def setLastWorksheet(self):
        self.ws = self.wb.Worksheets(self.wb.Worksheets.Count)



if __name__ == '__main__':    
    dat = zip(range(1, 11), [1]*10)

    # Create Object
    xls = xls('blaa.xls')
    xls.openExcelOrActivateWb()
    xls.setLastWorksheet()

    for (row, col) in dat:
        # Calculate some value here (only depending on row,col):
        # val = some_func(row, col)
        val = 'test'
        xls.setCell(row, col, val)
</code></pre>

<p>Now as the loop does ONLY depend on the both iterated vars, I wanted to make it run in parallel on many cores. So I've heard of Threading and Multiprocessing, but the latter seemed easier to me so I gave it a go.</p>

<p>So I changed the code like this:</p>

<pre><code>import os
import win32com.client as win32
from multiprocessing import Pool
from time import sleep

class xls:
    ### CLASS_DEFINITION LIKE BEFORE ###

''' Define Multiprocessing Worker '''
def multiWorker((row, col)):
    xls.setCell(row, col, 'test')

if __name__ == '__main__':    
    # Create Object
    xls = xls('StockDatabase.xlsm')
    xls.openExcelOrActivateWb()
    xls.setLastWorksheet()

    dat = zip(range(1, 11), [1]*10)

    p = Pool()  
    p.map(multiWorker, dat) 
</code></pre>

<p>Didn't seem to work because after some reading, Multiprocessing starts new Processes hence <code>xls</code> is not known to the workers.</p>

<p>Unfortunately I can neither pass <code>xls</code> to them as a third parameter as the Win32 can't be pickled :( Like this:</p>

<pre><code>def multiWorker((row, col, xls)):
    xls.setCell(row, col, 'test')

if __name__ == '__main__':    
    # Create Object
    xls = xls('StockDatabase.xlsm')
    xls.openExcelOrActivateWb()
    xls.setLastWorksheet()

    dat = zip(range(1, 11), [1]*10, [xls]*10)

    p = Pool()  
    p.map(multiWorker, dat) 
</code></pre>

<p>The only way would be to initialize the Win32 for each process right before the definition of the multiWorker:</p>

<pre><code># Create Object
xls = xls('StockDatabase.xlsm')
xls.openExcelOrActivateWb()
xls.setLastWorksheet()

def multiWorker((row, col, xls)):
    xls.setCell(row, col, 'test')

if __name__ == '__main__':    
    dat = zip(range(1, 11), [1]*10, [xls]*10)

    p = Pool()  
    p.map(multiWorker, dat) 
</code></pre>

<p>But I don't like it because my constructor of xls has some more logic, which automatically tries to find column ids for known header substrings... So that is a little bit more effort then wanted (and I don't think each process should really open it's own Win32 COM Interface), and this also gives me an error because gencache.EnsureDispatch might not be possible to call so often....</p>

<p><strong>What to do? How is the solution?
Thanks!!</strong></p>

<p>While Excel can use multiple cores when recalculating spreadsheets, its programmatic interface is strongly tied to the UI model, which is single threaded.  The active workbook, worksheet, and selection are all singleton objects; this is why you cannot interact with the Excel UI at the same time you're driving it using COM (or VBA, for that matter).</p>

<p>tl;dr </p>

<p>Excel doesn't work that way.</p>

python excel winapi multiprocessing