Cell Values (Take 2)

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

Cell Values (Take 2)
 
Peter Sippel


Guest


I'm obviously not able to reply to this thread: http://www.add-in-express.com/forum/read.php?FID=1&TID=10214
so I create another one her.

I had some performance issues by using Values property of Excelrange, so I used the item property instead. Both return a system.OleVariant value. Unfortunately the item property doesn't seem to set the VarType of the value and I can't tell e.g. if it's a date field and format it properly for some purposes.

In my opinion both variables should provide a proper vartype.
Posted 23 Jan, 2012 05:29:13 Top
Andrei Smolin


Add-in Express team


Posts: 18825
 Joined: 2006-05-11
Hello Peter,

Our topics are closed in 30 days since the last response.

You use ExcelRange.Item[RowIndex: OleVariant; ColumnIndex: OleVariant] to access an individual cell (not its value) within the range of cells.

Note that ExcelRange.Value returns a 2D array for a range of cells. This works definitely faster than walking through individual cells of the range.


Andrei Smolin
Add-in Express Team Leader
Posted 23 Jan, 2012 10:17:35 Top
Peter Sippel


Guest


Hi Andrei,

thanks for your reply. What I'm doing is

...
var
aws: ExcelWorksheet;
er: ExcelRange;
col,row: integer;
val: string;
begin
...
aws:= Self.ExcelApp.ActiveSheet as ExcelWorksheet;
er := ExcelRange(aws.Range['A1',EmptyParam].CurrentRegion);
...
for row := 1 to er.Rows.Count do
begin
for col := 1 to er.columns.Count do
begin
val := ValueToString(er.Item[row,col]); // <-- Fast, but no VarType
val := ValueToString(er.Value[row,col]); // <-- Vartype but very slow
end;
end;
...


Regards
Peter
Posted 24 Jan, 2012 03:19:56 Top
Peter Sippel


Guest


just made some measurements:

val := ValueToString(er.Item[row,col]); // 41 ms
val := ValueToString(er.Value[row,col]); // 14906 ms
Posted 24 Jan, 2012 03:51:48 Top
Andrei Smolin


Add-in Express team


Posts: 18825
 Joined: 2006-05-11
Pieter,

Try this: 

var
  aws: ExcelWorksheet;
  er: ExcelRange;
  col,row, sum: integer;
  arrV: OleVariant;
begin
aws := Self.ExcelApp.ActiveSheet as ExcelWorksheet;
er := ExcelRange(aws.Range['A1',EmptyParam].CurrentRegion);
sum := 0;
arrV := er.Value2;
for row := 1 to er.Rows.Count do
for col := 1 to er.Columns.Count do
sum := sum + arrV[row, col];
ShowMessage('Sum is ' + FloatToStr(sum));
end;


How does it work for you? Is this what you are looking for?


Andrei Smolin
Add-in Express Team Leader
Posted 24 Jan, 2012 06:07:18 Top
Peter Sippel


Guest


Hi Andrei,

Value2 is not giving the real type. But doing exactly this with Value instead, it's lightning fast (even 6 ms).

I don't understand the differnece though....
Posted 24 Jan, 2012 08:53:54 Top
Andrei Smolin


Add-in Express team


Posts: 18825
 Joined: 2006-05-11
Excel saves time if it uses its inner structures to supply you with data. Creating/releasing numerous COM objects in a loop produces too much overhead.


Andrei Smolin
Add-in Express Team Leader
Posted 24 Jan, 2012 09:09:19 Top
Peter Sippel


Guest


Ah, so er.Value[row,col] creates a COM Object ? I assumed "er" was a COM Object and Value a property (array).
Posted 24 Jan, 2012 10:14:19 Top
Andrei Smolin


Add-in Express team


Posts: 18825
 Joined: 2006-05-11
Hello,

er.Value[row,col] - this will not compile. A correct way is er.Item[row,col].Value; and this creates a COM object.


Andrei Smolin
Add-in Express Team Leader
Posted 26 Jan, 2012 08:35:52 Top