c ++ Excel OLE自动化。设置整个单元格范围的值“一次” [英] c++ Excel OLE automation. Setting the values of an entire cell-range 'at once'

问题描述

IDE：Embarcadero XE5

我试图提高export to excel过程的性能（速度）。
该过程包含太多的OLE函数调用和属性读/写调用，因此性能很差。

目前，一个网格

我试图将整个网格一次性导出为ex​​cel单元格范围，但是，

现在对于Embarcadero Delphi用户来说这似乎是一个微不足道的任务：

  //注意：这显然不会编译，只是显示过程。 
 var 
 arrData：Variant; 
 begin 
 //创建一个二维变量数组
 arrData：= VarArrayCreate（[1，RowCount，1，ColCount]，varVariant）; 
 
 //用值填充数组... 
 
 //获取单元格范围，大小等于arrData尺寸... 
 
范围.Value：= arrData; //完成，容易。 
 
 end; 
  

对于Embarcadero c ++ - 用户（或者也许只是我自己），它似乎并不是全部这是显而易见的。

我已经设法创建和填充数据的一维数组，使用：
VarArrayCreate ， Embarcadero示例。

我尝试将该数组分配给单元格范围如下：

  Variant vArray; 
 //用数据创建和填充vArray。测试用例。 
 int bounds [2] = {0，4}; 
 //创建包含类型为varVariant的5个元素的Variant数组。 
 //对于这种情况，也可以将元素类型设置为varInteger，对最终结果没有影响。 
 vArray = VarArrayCreate（bounds，1，varVariant）; 
 //为测试数据填充vArray，值为0,1,2,3,4 
（int index = 0; index <5; index ++）{
 VarArrayPut（vArray，index ，& index，0）; 
} 
 Variant vWorkSheet; // Active excel worksheet 
 Variant vCells = vWorkSheet.OlePropertyGet（LCells）; 
 
 Variant vRange; //一个excel单元格范围，大小等于我的vArray的大小。 
 Variant vRange = vCells.OlePropertyGet（LRange，vCells.OlePropertyGet（LItem，1,1），vCells.OlePropertyGet（LItem，5,1））; 
 
 vRange.OlePropertySet（LValue，vArray）; //类似于在上面的例子中Delphi做的。 
  

...导致整个excel单元格范围填充<$ c中的第一个值$ c> vArray 。

如何创建多维Variant数组

如何使用OLE将Variant数组分配到Excel单元格范围？

解决方案

创建多维Variant数组只是指定一组以上的低/高边界，就像Delphi代码所示：

  Variant arrData = VarArrayCreate（OPENARRAY（int，（1，RowCount，1，ColCount）），varVariant）; 
  

或：

 code> int bounds [4] = {1，RowCount，1，ColCount}; 
 Variant arrData = VarArrayCreate（EXISTINGARRAY（bounds），varVariant）; 
  

然后你可以这样做：

  //使用值...填充arrData 
 
 //获取单元格范围，大小等于arrData尺寸... 
 Variant Range =。 ..; 
 
 Range.OlePropertySet（LValue，arrData）; 
  

当调用 VarArrayPut（）指定一个索引数组，每个维度一个，以指示要将值分配给的特定元素：

  VarArrayPut （arrData，value，OPENARRAY（int，（indexDim1，indexDim2）））; 
  

或：

 code> int indexes [2] = {indexDim1，indexDim2}; 
 VarArrayPut（arrData，value，EXISTINGARRAY（indexes））; 
  

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?

解决方案

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自动化。设置整个单元格范围的值“一次”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！