[RESOLVED] Excel Block Data Write Using COM

I have been exporting my MFC project data vectors directly to Excel using COM for many years. However, the write is iterative to a single Excel cell at each write using this code:

Code:

---

void CExcel2000Class::set_cells_numeric( int Row, int Column, double NumericValue )
{       

        IDispatch *l_Range;

        //  Select the cell
        {

                VARIANT l_Result;
                VariantInit( &l_Result );

                VARIANT l_Param;

                l_Param.vt = VT_BSTR;
                l_Param.bstrVal = Cells2000( Row, Column );
                //  Range2000 returns the Excel specifier for the cell defined by the row and column. For example:
                //
                //        *        Row == 462
                //        *        Column == 1
                //
                //        *        l_Param.bstrVal == L"A462"

                auto_wrap( DISPATCH_PROPERTYGET, &l_Result, mp_Worksheet, L"Range", 1, l_Param );
                l_Range        = l_Result.pdispVal;

        }

        //        Write the value
        {

                VARIANT l_Result;
                VariantInit( &l_Result );

                VARIANT l_Param;

                l_Param.vt = VT_R8;
                l_Param.dblVal = NumericValue;

                auto_wrap( DISPATCH_PROPERTYPUT, &l_Result, l_Range, L"Value", 1, l_Param );

        }

}  //  set_cells_numeric()

---

(Note that this code has not really changed since it was originally written for Excel 2000.)

This is very slow and inefficient. And, as data vectors exceed sizes of tens of thousands of values, this method starts to generate errors that must be acknowledged to continue the export.

I would like a way to write a full block of data to a specified range, similar to a copy-and-paste of a range of cells in Excel. I created this new function:

Code:

---

void CExcel2000Class::set_range_numeric( int StartRow, int StartColumn, int EndRow, int EndColumn, double **NumericArray )
{

        //  NumericArray is dimensioned NumericArray[ Column Index ][ Row Index ]. For the first cut this is a 1-dimensional
        //        array that allows only Column Index == 0.  This function should handle the general multi-column case.

        IDispatch *l_Range;

        //  Get the appropriate Excel Worksheet cell range
        {

                VARIANT l_Result;
                VariantInit( &l_Result );

                VARIANT l_Param;

                l_Param.vt = VT_BSTR;
                l_Param.bstrVal = Range2000( StartRow, StartColumn, EndRow, EndColumn );
                //  Range2000 returns the Excel specifiers for the range defined by the rows and columns. For example:
                //
                //        *        StartRow == 462
                //        *        StartColumn == 1
                //        *        EndRow == 878
                //        *        EndColumn == 2
                //
                //        *        l_Param.bstrVal == L"A462:B878"

                auto_wrap( DISPATCH_PROPERTYGET, &l_Result, mp_Worksheet, L"Range", 1, l_Param );
                l_Range        = l_Result.pdispVal;

        }

        //        Write the data to the Worksheet cell range
        {

                //        Currently a 1-dimensional data array
                DWORD numElements[ 2 ];

                numElements[ 0 ]=( EndColumn - StartColumn )+ 1;
                numElements[ 1 ]=( EndRow - StartRow )+ 1;

                COleSafeArray saRet;

                saRet.Create( VT_R8, 2, numElements );
                long index[ 2 ];

                //        If a COleSafeArray is the correct type to use in this application, is there a method
                //        to assign NumericArray directly rather than iteratively? I don't see an option in the
                //        type member function list.
                for( index[ 0 ] = 0;  index[ 0 ] < ( EndColumn - StartColumn )+ 1; index[ 0 ]++ )
                        for( index[ 1 ] = 0; index[ 1 ] < ( EndRow - StartRow )+ 1; index[ 1 ]++ )
                                saRet.PutElement( index, &NumericArray[ index[ 0 ] ][ index[ 1 ] ] );

                VARIANT l_Result;
                VariantInit( &l_Result );

                VARIANT l_Param;

                l_Param.vt = VT_SAFEARRAY;
                l_Param = saRet;

                //        This call is actually fairly close to working. It fills the appropriate range, but only
                //        with the value in NumericArray[ 0 ][ 0 ]
                auto_wrap( DISPATCH_PROPERTYPUT, &l_Result, l_Range, L"Value", 1, l_Param );

        }

        return;

}  // set_range_numeric()

---

This is coming close, but first it continues to be iterative in the copy from the double-valued array to the COleSafeArray and second the range is filled, but with only the value in the first entry in the data array.

I think that the L"Value" argument needs to simply be replaced by the appropriate Excel COM specifier. But I cannot find documentation on the appropriate COM specifier. I would also like to find a way to assign the data vector to COleSafeArray directly, rather than iteratively, if possible.

A direct solution would be most welcome, but I think what I really need is a good reference. Web searching has produced a lot of irrelevant or obsolete references. Visual Studio used to include a tool that detailed every COM interface exported on the development host, but I cannot find such a tool under VS 2017. If anyone knows of such a tool it would also be very helpful.

Thanks in advance for your advice.

FF

I have adjusted the multi-cell function to work properly. The PutElement() call to set values in saRet was being indexed with the indices reversed. This resolves the export issue, though not the iterative assignment to saRet or the more-general documentation issue.

Thanks to everyone who has had a look at this. I would still value any references that you might suggest and/or discussion of the (apparently obsolete) Visual Studio COM detection tool.

Thanks

FF

See OLEVIEW.EXE. It's in the Windows SDK. There might also be a 64 bit version.

Thanks, Arjay. This is exactly what I am looking for.

Now, how do I mark this post as answered/closed? I don't see the mechanism.

FF

Quote:

---

Originally Posted by FerroFun

...
Now, how do I mark this post as answered/closed? I don't see the mechanism.
...

---

However, you have done it! :thumb: