sending an array to Excel

[Lynn McGuire]

Does anyone have experience with sending an array of information to
Excel using OLE Automation and C++ ? I am looking for a way to speed
up the sending of data from our app to Excel. Currently, we send
data to Excel a cell at a time using the following code:

void PutDoubleInTableHorizontal (int row, int column, int count, const double num [])
{
// put the doubles in horizontally
for (int i = 0; i < count; i++)
{
if (uninitializedValue == num )
continue;
std::string cellAddress = GetCellEquivalent (row, column + i);
VARIANT result1;
VariantInit ( & result1);
VARIANT range;
VariantInit ( & range);
range.vt = VT_BSTR;
_bstr_t address = _bstr_t (cellAddress.c_str ());
range.bstrVal = address;
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelSheet, L"Range", 1, range);
if (result1.vt == VT_DISPATCH)
{
VARIANT result2;
VariantInit ( & result2);
VARIANT value;
VariantInit ( & value);
value.vt = VT_R8;
value.dblVal = num ;
OLEMethod (DISPATCH_PROPERTYPUT, & result2, result1.pdispVal, L"Value", 1, value);
int res = result2.vt;
result1.pdispVal -> Release ();
}
}
}

I am wondering if sending an array of cells to Excel would be faster ?

Sincerely,
Lynn McGuire

 

[Mike S]

Does anyone have experience with sending an array of information to
Excel using OLE Automation and C++ ? I am looking for a way to speed
up the sending of data from our app to Excel. Currently, we send
data to Excel a cell at a time using the following code:

void PutDoubleInTableHorizontal (int row, int column, int count, const
double num [])
{
// put the doubles in horizontally
for (int i = 0; i < count; i++)
{
if (uninitializedValue == num )
continue;
std::string cellAddress = GetCellEquivalent (row, column + i);
VARIANT result1;
VariantInit ( & result1);
VARIANT range;
VariantInit ( & range);
range.vt = VT_BSTR;
_bstr_t address = _bstr_t (cellAddress.c_str ());
range.bstrVal = address;
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelSheet, L"Range", 1,
range);
if (result1.vt == VT_DISPATCH)
{
VARIANT result2;
VariantInit ( & result2);
VARIANT value;
VariantInit ( & value);
value.vt = VT_R8;
value.dblVal = num ;
OLEMethod (DISPATCH_PROPERTYPUT, & result2, result1.pdispVal, L"Value",
1, value);
int res = result2.vt;
result1.pdispVal -> Release ();
}
}
}

I am wondering if sending an array of cells to Excel would be faster ?

Sincerely,
Lynn McGuire

If the array is large what about saving the data to a csv file and
opening the file with Excel?

 

[Lynn McGuire]

If the array is large what about saving the data to a csv file and opening the file with Excel?

I send live data to Excel. Using a CSV file would destroy the
interactiveness of the data transfer and would not install my
cell formatting.

Thanks,
Lynn

 

[Dave Peterson]

First, I don't use C++ or OLE Automation, but in excel's VBA, it's usually
quicker to write the values to a range in as few calls to the worksheet as possible.

This may not help, but...

Option Explicit
Sub testme()

Dim myArr() As Long
Dim rCtr As Long
Dim cCtr As Long
Dim myCell As Range
Dim iRow As Long
Dim iCol As Long

Dim StartTime As Double
Dim EndTime As Double

ReDim myArr(1 To 1000, 1 To 100)

For rCtr = LBound(myArr, 1) To UBound(myArr, 1)
For cCtr = LBound(myArr, 2) To UBound(myArr, 2)
myArr(rCtr, cCtr) = (rCtr * 1000) + cCtr
Next cCtr
Next rCtr

Set myCell = ActiveSheet.Range("a1")

ActiveSheet.Cells.Clear
StartTime = Now
myCell.Resize(UBound(myArr, 1) - LBound(myArr, 1) + 1, _
UBound(myArr, 2) - LBound(myArr, 2) + 1).Value = myArr
EndTime = Now
Debug.Print " One write: " _
& Format(EndTime - StartTime, "hh:mm:ss.000")

ActiveSheet.Cells.Clear
StartTime = Now
iRow = -1
For rCtr = LBound(myArr, 1) To UBound(myArr, 1)
iRow = iRow + 1
iCol = -1
For cCtr = LBound(myArr, 2) To UBound(myArr, 2)
iCol = iCol + 1
myCell.Offset(iRow, iCol).Value = myArr(rCtr, cCtr)
Next cCtr
Next rCtr
EndTime = Now
Debug.Print "Multiple writes: " _
& Format(EndTime - StartTime, "hh:mm:ss.000")

End Sub

Does anyone have experience with sending an array of information to
Excel using OLE Automation and C++ ? I am looking for a way to speed
up the sending of data from our app to Excel. Currently, we send
data to Excel a cell at a time using the following code:

void PutDoubleInTableHorizontal (int row, int column, int count, const double
num [])
{
// put the doubles in horizontally
for (int i = 0; i < count; i++)
{
if (uninitializedValue == num )
continue;
std::string cellAddress = GetCellEquivalent (row, column + i);
VARIANT result1;
VariantInit ( & result1);
VARIANT range;
VariantInit ( & range);
range.vt = VT_BSTR;
_bstr_t address = _bstr_t (cellAddress.c_str ());
range.bstrVal = address;
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelSheet, L"Range", 1, range);
if (result1.vt == VT_DISPATCH)
{
VARIANT result2;
VariantInit ( & result2);
VARIANT value;
VariantInit ( & value);
value.vt = VT_R8;
value.dblVal = num ;
OLEMethod (DISPATCH_PROPERTYPUT, & result2, result1.pdispVal, L"Value", 1, value);
int res = result2.vt;
result1.pdispVal -> Release ();
}
}
}

I am wondering if sending an array of cells to Excel would be faster ?

Sincerely,
Lynn McGuire

 

[Lynn McGuire]

First, I don't use C++ or OLE Automation, but in excel's VBA, it's usually quicker to write the values to a range in as few calls to

the worksheet as possible.

That is what I am thinking. I have been looking at the SAFEARRAY
http://www.codeproject.com/KB/office/A_C___dll_for_Excel.aspx?display=Print
but the examples that I find are fairly confusing.

I would like to see some code building a SAFEARRAY in C++ for
sending to Excel.

Thanks,
Lynn

 

[Dave Peterson]

I still don't use C++, but maybe someone can chime in.

 

[Mike S]

I still don't use C++, but maybe someone can chime in.

This is from MS, might be useful.

How to automate Excel from MFC and Visual C++ 2005 or Visual C++ .NET to
fill or obtain data in a range using arrays
http://support.microsoft.com/kb/308407
------------------------

This looks like it's worth a read, it shows how to work with Safearrays.

Microsoft wrote a whitepaper many years ago detailing some best
practices for writing DLLs to use with VB5/VB6, which of course would
also apply to VBA. It is reprinted with permission, and available in the
original Word form, here: (see below)
Microsoft: Developing DLLs for VB5 http://vb.mvps.org/tips/vb5dll.asp
See section 8 ("Passing and Returning Arrays") for help with this task.
There is an example that is written specifically to demonstrate passing
and returning arrays of strings.
http://stackoverflow.com/questions/987010/how-to-pass-string-array-from-c-c-dll-to-vba-excel

http://vb.mvps.org/tips/vb5dll.asp
....This example demonstrates passing and returning arrays of strings.
But it can easily be modified to work for arrays of any permitted
datatype. The only modifications that have to be made are changing the
Declare statements and the VT_XXXX flags to match the appropriate type.
Of course, you don't have to worry about Unicode conversions when
dealing with non-string data-types.

Example
-------

#include <windows.h>
#include <stdio.h>

#define CCONV _stdcall

// hold the SAFEARRAY pointer to be returned in a Variant
LPSAFEARRAY lpsa1;
LPSAFEARRAY lpsa2;
....
------------------------

If you don't get any of these working you might try posting in these forums:

Re: Use Vba Pass Array To C++ Dll, and return another array back to vba
http://www.ozgrid.com/forum/showthread.php?t=63142&pagenumber=
(the discussion covers 2 pages, the working code is on page 2)

pass a 2D array from VBA to a C++ DLL
http://www.xtremevbtalk.com/showthread.php?t=303521

Exporting Char Array to Excel
http://www.cplusplus.com/forum/beginner/2351/

 

[Lynn McGuire]

I would like to see some code building a SAFEARRAY in C++ for

This is from MS, might be useful.

How to automate Excel from MFC and Visual C++ 2005 or Visual C++ .NET to fill or obtain data in a range using arrays
http://support.microsoft.com/kb/308407

That MS KB uses the MFC automation library which I am not using
currently so I can not use it for additional code. The MS KB code
used the importing of the Excel object which is specific to the
version of Excel imported. My code is non-specific and can talk to
Excel 1997 to Excel 2010 (and future versions).

That said, I went back to the MS KB and stumbled across "How to
automate Excel from C++ without using MFC or #import"
http://support.microsoft.com/kb/216686
which talks about SAFEARRAYs and is exactly what I wanted !

Of course, this is the most obtuse interface and requires much
reading and rereading in order to begin to understand it.

Thanks,
Lynn