c++ Excel OLE automation. Setting the values of an entire cell-range 'at once'

Question

IDE: Embarcadero XE5

I am attempting to improve the performance(speed) of an 'export to excel' procedure. The procedure contains way too many OLE function calls and property read/write calls, hence the poor performance.

At present, a grid(2D array) is exported to excel by stepping through each cell in the grid and setting it's value.

I'm trying to export the entire grid into a excel cell-range at once, but failing in my attempts.

Now for Embarcadero Delphi-users this seems to be a trivial task:

// NOTE: This obviously won't compile, just showing the process.
var
   arrData: Variant;
begin
   // Create a 2D Variant array
   arrData := VarArrayCreate([1, RowCount, 1, ColCount], varVariant);

   // Fill array with values...

   // Get a range of cells, size equal to arrData dimensions...

   Range.Value := arrData;  // Done, easy.

end;

For Embarcadero c++ -users however(or perhaps just myself), it doesn't seem to be all that obvious.

I've managed to create and fill a single-dimensional array with data, using: VarArrayCreate, Embarcadero Example.

I tried to assign that array to a cell-range as follows:

Variant vArray;
// Create and fill vArray with data. Test case.
int bounds[2] = {0, 4};
// Creates Variant array containing 5 elements of type varVariant.
// Can set the element type to varInteger as well for this case, made no difference to the end result.
vArray = VarArrayCreate( bounds, 1, varVariant );
// Fill vArray with test data, values 0,1,2,3,4
for ( int index = 0; index < 5; index++ ) {
   VarArrayPut( vArray, index, &index, 0 ); 
}
Variant vWorkSheet; // Active excel worksheet
Variant vCells = vWorkSheet.OlePropertyGet( L"Cells" );

Variant vRange; // A excel cell-range, equal in size to my vArray's size.
Variant vRange = vCells.OlePropertyGet( L"Range", vCells.OlePropertyGet(L"Item", 1, 1), vCells.OlePropertyGet(L"Item", 5, 1) );

vRange.OlePropertySet( L"Value", vArray );  // Similar to what is done in Delphi in example above.

...resulting in the entire excel cell-range being filled with the first value in vArray.

How would you go about creating multi-dimensional Variant arrays(two-dimensional in this case)?

How do you assign the Variant array to an Excel cell-range using OLE?

Please show the C++ code that creates `vArray`. – David Heffernan Mar 28 '14 at 13:45 — David Heffernan, Mar 28 '14 at 13:45
Updated the c++ code segment to show more detail. – HvS Mar 28 '14 at 14:08 — HvS, Mar 28 '14 at 14:08

Remy Lebeau · Accepted Answer · 2014-03-31T07:46:27.777

Creating a multi-dimensional Variant array is simply a matter of specifying more than 1 set of low/high bounds, just like the Delphi code showed:

Variant arrData = VarArrayCreate(OPENARRAY(int, (1, RowCount, 1, ColCount)), varVariant);

Or:

int bounds[4] = {1, RowCount, 1, ColCount};
Variant arrData = VarArrayCreate(EXISTINGARRAY(bounds), varVariant);

Then you can do this:

// Fill arrData with values...

// Get a range of cells, size equal to arrData dimensions...
Variant Range = ...;

Range.OlePropertySet(L"Value", arrData);

When you call VarArrayPut(), you specify an array of indexes, one for each dimension, to indicate the specific element that you want to assign the value to:

VarArrayPut( arrData, value, OPENARRAY(int, (indexDim1, indexDim2)) );

Or:

int indexes[2] = {indexDim1, indexDim2};
VarArrayPut( arrData, value, EXISTINGARRAY(indexes) );

c++ Excel OLE automation. Setting the values of an entire cell-range 'at once'

1 Answers1

Linked