OWC11 Performance when setting values in a NxM range

[microsoft.public.office.developer.web.components]

Hi,

I have the following problem:

A windows-forms based application with embedded OWC11 spreadsheet. I want to
run a database query, and put the result-set values in a worksheet. To do
this, I am first converting the result-set to a NxM System.Array object
(with base 1, not zero). Then, I'm doing something like this, without
success:
' Contains the array object with result set values.
Dim array as System.Array
worksheet.Cells.Range(topLeftCell, bottomRightCell).Value2 = array


I've also tried:

worksheet.Cells.Range(topLeftCell, bottomRightCell).Value = array
'AND
worksheet.Cells.Range(topLeftCell, bottomRightCell).Formula = array


Any ideas on how I can do this? Or do I have to do a cell-by-cell copy of
values from the input array into the spreadsheet?

Thanks in advance,
Chinmay

 

[Alvin Bruney - ASP.NET MVP]

The most efficient way to do this is to use a CSV format since there is no
overhead of objects involved. Use a \n to delimit the rows and "," to
delimit the columns.
So documents.all.sp.CSVDATA = "1,2,3,4,5\n6\n7,8,,9" should give you this
matrix

A B C D E
1 2 3 4 5
6
7 8 9

on your spreadsheet so you don't lose any formatting control.
however, you lose the ability to copy the data into a specific cell since
the copy always
starts in cell A1. The workaround is to load the data as I have indicated
and then copy the used range object to the specific cell you are after. This
approach will load thousands of rows quickly and won't present memory issues
as in the array object approach. Makes sense?
--
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

 

[msnews.microsoft.com]

Alvin,

Thanks so much for this tip. Sounds like it should work!

I looked for a solution in your black book but didn't find it... perhaps you
can include this in your next edition!

The most efficient way to do this is to use a CSV format since there is no
overhead of objects involved. Use a \n to delimit the rows and "," to
delimit the columns.
So documents.all.sp.CSVDATA = "1,2,3,4,5\n6\n7,8,,9" should give you this
matrix

A B C D E
1 2 3 4 5
6
7 8 9

on your spreadsheet so you don't lose any formatting control.
however, you lose the ability to copy the data into a specific cell since
the copy always
starts in cell A1. The workaround is to load the data as I have indicated
and then copy the used range object to the specific cell you are after.
This
approach will load thousands of rows quickly and won't present memory
issues
as in the array object approach. Makes sense?
--
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
-------------------------------------------------------



"microsoft.public.office.developer.web.components"

Hi,

I have the following problem:

A windows-forms based application with embedded OWC11 spreadsheet. I want to
run a database query, and put the result-set values in a worksheet. To do
this, I am first converting the result-set to a NxM System.Array object
(with base 1, not zero). Then, I'm doing something like this, without
success:
' Contains the array object with result set values.
Dim array as System.Array
worksheet.Cells.Range(topLeftCell, bottomRightCell).Value2 = array


I've also tried:

worksheet.Cells.Range(topLeftCell, bottomRightCell).Value = array
'AND
worksheet.Cells.Range(topLeftCell, bottomRightCell).Formula = array


Any ideas on how I can do this? Or do I have to do a cell-by-cell copy of
values from the input array into the spreadsheet?

Thanks in advance,
Chinmay