Improve performance of function that makes many Excel Interop calls

Add-in Express™ Support Service
That's what is more important than anything else

Improve performance of function that makes many Excel Interop calls
Can cell format data be extracted in a more efficient way? 
Posts 1 - 10 of 15
First | Prev. | 1 2 | Next | Last 
David Jones


Guest


Hello again

I am working on an XLL using ADX. One of my functions has to extract formatting information (colours, fonts etc.) for each cell in a specified range. So I have to process the range cell-by-cell like this: 


For Each row In contentRange.Rows
    For Each cell In row.Cells
        ...
        Dim font As Excel.Font = cell.Font
        Dim interior As Excel.Interior = cell.Interior
        Dim currentCellStyle as MyCellStyleStruct

        currentCellStyle = New CellStyle(interior.Color,
                                         font.Color,
                                         font.Name.ToString,
                                         font.Size.ToString,
                                         font.Italic,
                                         font.Bold,
                                         cell.HorizontalAlignment)
        ...
    Next
Next


This is painfully slow because of the number of Interop calls. I have tried various approaches to improve performance but nothing has worked:
* extracting style data for entire ranges in one go does not work
* caching font data from previous cells in the range doesn't work because font.Equals does a reference rather than value comparison

Do you know any other methods that might improve this? Can I get this data without using the Interop? Can I batch multiple calls together?

Thanks very much

David
Posted 12 Nov, 2012 15:33:46 Top
Andrei Smolin


Add-in Express team


Posts: 19100
 Joined: 2006-05-11
David,

Consider using a combination of a COM add-in and XLL add-in. The COM add-in could gather those data 1) at startup, 2) when a formula refering your UDF is entered/modified, and 3) when formatting changes. The XLL could call a public method defined in the add-in in order to get the latest version of required data.

What do you think?


Andrei Smolin
Add-in Express Team Leader
Posted 13 Nov, 2012 04:10:37 Top
David Jones


Guest


Hi Andrei

Do you mean something like this?
1. The XLL UDF gets called when the sheet recalculates.
2. Instead of processing the range itself, it passes the cell references to a public function on the new COM add-in.
3. The COM add-in extracts the formatting info via the interop and returns it to the UDF for further processing. It caches the formatting info internally.
4. The COM add-in handles sheet change events and updates its cache each time any cells we are interested in change
5. Next time the UDF calls the COM add-in for the same range the results are served from the cache instead of via the interop

It's an interesting idea. I am dealing with rapidly-changing sheets so it might not be the best solution, though. I will experiment with it.

I might also post this question on Stack Overflow to see if I get any suggestions there.

Supplementary question: If I follow this approach would I get around the problem with accessing the excel object model from an xll (described here: http://www.add-in-express.com/docs/net-excel-udf-tips.php#excel-object-model-xll)?

Thanks

David
Posted 13 Nov, 2012 15:24:19 Top
Andrei Smolin


Add-in Express team


Posts: 19100
 Joined: 2006-05-11
Hi David,

Yes, this was the idea.

David Jones writes:
If I follow this approach would I get around the problem with accessing the excel object model from an xll


To bypass these problems you need that the COM add-in caches the info BEFORE it is called by the XLL so that when XLL calls the COM add-in, it retrieves the info from the cache, not from Excel. Note that this call will be performed in the context of XLL and this means the use of the Excel object model can spoil everything.

There's a potential problem of invoking your XLL before the COM add-in is loaded e.g. to recalculate soem sheet(s) at Excel startup . I hope that Excel's architecture foresees this situation and such recalculations start after all add-ins are loaded. Nevertheless, this requires verification.


Andrei Smolin
Add-in Express Team Leader
Posted 14 Nov, 2012 04:55:14 Top
David Jones


Guest


I can replace several of the interop calls with C API calls like this: 


        Dim fontName As String = _Module.CallWorksheetF unction(ADXExcelWorksheetFunction.GetCell, {18, adxCellRef})
        Dim fontSize As String = _Module.CallWorksheetF unction(ADXExcelWorksheetFunction.GetCell, {19, adxCellRef})
        Dim fontItalic As Boolean = _Module.CallWorksheetF unction(ADXExcelWorksheetFunction.GetCell, {21, adxCellRef})
        Dim fontBold As Boolean = _Module.CallWorksheetF unction(ADXExcelWorksheetFunction.GetCell, {20, adxCellRef})


This cuts the time my function takes by about 60%. Can you foresee any problems with this approach?

Thanks

David
Posted 14 Nov, 2012 16:28:55 Top
Andrei Smolin


Add-in Express team


Posts: 19100
 Joined: 2006-05-11
David,

I believe this is by far better solution than the COM add-in approach. If what GetCell returns suits you, then the solution is absolutely okay.


Andrei Smolin
Add-in Express Team Leader
Posted 15 Nov, 2012 00:32:24 Top
Henri Pellemans


Guest


Hi Andrei,

I was looking in the ADX class reference for some information about ADXExcelWorksheetFunction.GetCell but I not find much.

However, with the help of Google I found the following http://www.tech-archive.net/Archive/Excel/microsoft.public.excel.worksheet.functions/2004-11/4788.html with info about GET.CELL

Do I understand well: the use of ADXExcelWorksheetFunction is not a forbidden call of the Excel object model in a XLL?

Regards,

Henri
Posted 15 Nov, 2012 02:25:13 Top
Andrei Smolin


Add-in Express team


Posts: 19100
 Joined: 2006-05-11
Hi Henri,

I use macrofun.hlp from http://support.microsoft.com/kb/128185. To open it in Windows 7, see http://support.microsoft.com/kb/917607.

ADXExcelWorksheetFunction doesn't call into the Excel object model. Functions listed in macrofun.hlp are part of the API that Excel has provided since pre-historic times.


Andrei Smolin
Add-in Express Team Leader
Posted 15 Nov, 2012 03:13:21 Top
David Jones


Guest


Thanks, Andrei. The only restriction I have with the C API is that GET.CELL doesn't return full cell/font colour information for later versions of Excel. I'm sure I'll find some way around this ;-)

David
Posted 15 Nov, 2012 03:25:59 Top
Andrei Smolin


Add-in Express team


Posts: 19100
 Joined: 2006-05-11
David,

David Jones writes:
GET.CELL doesn't return full cell/font colour information for later versions of Excel


Can you please provide more details?


Andrei Smolin
Add-in Express Team Leader
Posted 15 Nov, 2012 04:30:23 Top

Posts 1 - 10 of 15
First | Prev. | 1 2 | Next | Last