Slowness of setting values to OWC spreadsheet cells

[dustbort]

I have a VB.NET application that uses OWC Spreadsheet. I have isolated a
bottleneck of my application to a loop where I populate the cells of a
spreadsheet, which is only like 50 rows by 18 columns. All this loop does is
iterate through rows and cloumns, assigning to the value property of the
cells and setting the interior.color property of some cells. This takes 3
seconds on my machine and 6 seconds on another machine. I altered the code
to assign literal values to the cells, and it ran just as slowly. I also
tried disabling features of the spreadsheet that I thought might be
responding to the assignments, such as events, undo, calculation, and column
resizing, buth this had no effect. Does anyone know why this is so slow and
how to speed it up?

 

[Alvin Bruney - ASP.NET MVP]

I assume you are using a foreach loop. You can gain a boost by using a
regular loop. Outside of that, it should not be performing so bad. Are you
up to date on patches etc?


--
Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The Microsoft Office Web Components Black Book with .NET
Now Available @ www.lulu.com/owc
Forth-coming VSTO.NET - Wrox/Wiley 2006

 

[dustbort]

I think I found out why it is so slow, and I definitely found a way to speed
it up. As I understand it, each call through the COM interface is fairly
slow, so something like 1000 cell value assignments can be measured in
seconds.

The way I got around it is to cast the range.value to a 2-dimensional object
array. I use the array to populate my values, and then I use a single
assignment to put all that data back into range.value.

Example:

Dim vector(,) as object
Dim rng as Range = ss.ActiveSheet.Range("A1:R1")
vector = Ctype(rng.value, object(,)) ' a 1-based 2D array
' Loop to set array values
vector(1, 1) = "hello"
....
rng.value = vector

I assume you are using a foreach loop. You can gain a boost by using a
regular loop. Outside of that, it should not be performing so bad. Are you
up to date on patches etc?


--
Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The Microsoft Office Web Components Black Book with .NET
Now Available @ www.lulu.com/owc
Forth-coming VSTO.NET - Wrox/Wiley 2006
-------------------------------------------------------

I have a VB.NET application that uses OWC Spreadsheet. I have isolated a
bottleneck of my application to a loop where I populate the cells of a
spreadsheet, which is only like 50 rows by 18 columns. All this loop does is
iterate through rows and cloumns, assigning to the value property of the
cells and setting the interior.color property of some cells. This takes
3
seconds on my machine and 6 seconds on another machine. I altered the
code
to assign literal values to the cells, and it ran just as slowly. I also
tried disabling features of the spreadsheet that I thought might be
responding to the assignments, such as events, undo, calculation, and column
resizing, buth this had no effect. Does anyone know why this is so slow and
how to speed it up?