CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2008
    Posts
    29

    [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
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2008
    Posts
    29

    Re: Excel Block Data Write Using COM

    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

  3. #3
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,490

    Re: Excel Block Data Write Using COM

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

  4. #4
    Join Date
    Feb 2008
    Posts
    29

    Re: Excel Block Data Write Using COM

    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

  5. #5
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,396

    Re: Excel Block Data Write Using COM

    Quote Originally Posted by FerroFun View Post
    ...
    Now, how do I mark this post as answered/closed? I don't see the mechanism.
    ...
    However, you have done it!
    Victor Nijegorodov

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured