Does Excel have problems with 1D arrays?

[RobertoBozzolo]

Hi all,

I need to write some data to Excel and I am using Excel2000.fp to perform this task. I have not been able to write 1D arrays to a range of cells

Starting with those sample arrays:

    int  di[] = { 2, 4, 6, 8, 10, 12, 14, 16, 18, 20 };
    double dd[] = { 2.5, 4.5, 6.5, 8.5, 10.5, 12.5, 14.5, 16.5, 18.5, 20.5 };

and using excel2000dem sample as a starting point, I added these lines to WriteDataToExcel function:

    // Open new Range for Worksheet
    error = CA_VariantSetCString (&MyCellRangeV, "M2:M11");
    error = Excel_WorksheetRange (ExcelWorksheetHandle, NULL, MyCellRangeV, CA_DEFAULT_VAL, &ExcelRangeHandle);
    if (error<0) goto Error;

    // Make range Active   
    error = Excel_RangeActivate (ExcelRangeHandle, &ErrorInfo, NULL);
    if (error<0) goto Error;

 //error = CA_VariantSet1DArray (&MyVariant, CAVT_DOUBLE, 10, dd);
 //error = CA_VariantSet1DArray (&MyVariant, CAVT_INT, 10, di);

 //error = CA_VariantSet2DArray (&MyVariant, CAVT_DOUBLE, 10, 1, dd);
 //error = CA_VariantSet2DArray (&MyVariant, CAVT_INT, 10, 1, di);

    // Set Range with one call passing SAFEARRAY as Variant
    error = Excel_SetProperty (ExcelRangeHandle, &ErrorInfo, Excel_RangeValue2, CAVT_VARIANT, MyVariant);
    if (error < 0) goto Error;

As you can see, I want to write 10 values to the sheet in a single column, either integers or doubles (I will need to treat both data types). The result of using CA_VariantSet1DArray is always 10 cells all with the value of the first element in the array!

If I use CA_VariantSet2DArray all works well even if I have defined the arrays as 10*1 elements

OK, the trick of a 2d array of 1 column only works, but what is the reason for this behaviour?

[Alex D]

Roberto:

A quick search turned up this result, which may be of interest to you.

Essentially, he indicates that what you are trying seems like it should work, but doesn't. I'll quote the final solution from the article:

"So the long winded moral of the story (which is mainly an excuse to talk about a little of the inner workings that are going on in COM interop when you write this code) is that when you want to set a range of values to an array, you must declare that array as a 2 dimensional array where the left-most dimension is the number of rows you are going to set and the right-most dimension is the number of columns you are going to set.  Even if you are just setting one column, you can’t create a 1 dimensional array and have it work."

A quick test showed that this works.

Hope this helps,

-alex

[RobertoBozzolo]

Thank you Alex for linking that article. Ok, what I supposed to be a trick is really the only way to perform this operation: good to know
Even better to know that this is also the way it works in retrieving data from Excel (the next step in my application )

I wonder were a big warning could be placed in order to save time to the next person who will try to do this...