Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sending an array to Excel
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 [i]) 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 [i]; 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sending an array to Excel
On 9/2/2010 1:03 PM, Lynn McGuire wrote:[i]
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 [i]) 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sending an array to Excel
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sending an array to Excel
On 9/2/2010 4:13 PM, Lynn McGuire wrote:
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 Would it help to send a row at a time? Would it help to send a whole range of rows and columns at a time? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
sending an array to Excel
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 On 09/02/2010 15:03, Lynn McGuire wrote:[i] 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 [i]) 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
sending an array to Excel
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...?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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
sending an array to Excel
I still don't use C++, but maybe someone can chime in.
On 09/03/2010 10:25, Lynn McGuire wrote: 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...?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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
sending an array to Excel
On 9/3/2010 9:31 AM, Dave Peterson wrote:
I still don't use C++, but maybe someone can chime in. On 09/03/2010 10:25, Lynn McGuire wrote: 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...?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 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, he (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/9...l-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: Use Vba Pass Array To C++ Dll, and return another array back to vba http://www.ozgrid.com/forum/showthre...42&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/ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
sending an array to Excel
I would like to see some code building a SAFEARRAY in C++ for
sending to Excel. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sending An Email In Excel Programmatically W/O Sending An Object | Excel Programming | |||
Sending SMS from excel | Excel Programming | |||
sending excel | Excel Discussion (Misc queries) | |||
Sending array data to Word with DDEPoke | Excel Programming | |||
Sending an array to DLL Function | Excel Programming |