C++

Forum

 
Excel automation in C++ (continued)

Android1 (17)
Hello!

I would like to continue the topic Excel automation in C++:
http://www.cplusplus.com/forum/windows/58312/2/

Write data to Excel is not so difficult to implement, but how to read data from excel still do not understand.
But if you can write data to eksel, then it should and read them.
Maybe someone will be able to send the right thoughts in this direction?
freddie1 (1838)
I believe everything you will need is here....

http://www.cplusplus.com/forum/windows/218553/

freddie
freddie1 (1838)
The above link starts out with ODBC code, then gets into the low level COM/OLE Automation stuff.

freddie
Android1 (17)
Your code really works! But he is very difficult to understand.
You are really very smart in the code-com.

Please tell me if this is possible, how can your code be modified so that the data is read from the active open file, not from the specified path?

To write the data to the excel file, I changed code in example microsoft:
hr = CoCreateInstance ... on ... hr = GetActiveObjec...
and
change "add" on "item":

AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBooks, L"Add", 0);
on
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBooks, L"item", 0);

This allowed to write data to the open active file Excel.
But in your code, just can't do it. Very difficult :)
Last edited on
Thomas1965 (4571)
Android1 (17)
Another option might be this library:
https://www.codeproject.com/Articles/13852/BasicExcel-A-Class-to-Read-and-Write-to-Microsoft


Unfortunately, I need to read or write data to the file excel when it is open and active.
Ttherwise I would have long used library libxl or xlnt :)
Last edited on
freddie1 (1838)
I can't help you with that AutoWrap(...) stuff. That isn't my code. I believe the original source of that function was Microsoft documentation. In various posts about Excel Automation over the years to which I contributed that code came up. I tested it briefly and found it to work, although I never used it in any of my work. I guess it was simply a case of my wanting to 'roll my own', so that I would fully understand it.


Please tell me if this is possible, how can your code be modified so that the data is read from the active open file, not from the specified path?


Not exactly sure what you mean. Here is the main() function of the last example I provided which is on my Page 2. Note that the function OpenXLWorkBook() is called. The last parameter of that function is...

wchar_t szWorkBookPath[]

...that is, a pointer to the character string containing the path to the Excel WorkBook that you want to open. If a running instance of Excel already exists and a WorkBook is open in it, then you are going to have to write code to find that running instance and then provide that path to my code as shown below. That will require you to repost that question, i.e., "How do I find the path to an already running instance of Excel which I wish to interact with in some way?"

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
 
// g++ Main.cpp XLFunctions.cpp -oXL16.exe -m64 -lole32 -loleaut32 -luuid -Os -s
// cl Main.cpp /O1 /Os /MT /FeXL15_VC.exe kernel32.lib ole32.lib oleaut32.lib uuid.lib
// cl Main.cpp XLFunctions.cpp /O1 /Os /FeXL23.exe /GR- /GS- TCLib.lib kernel32.lib ole32.lib oleaut32.lib uuid.lib
//  6,144 Bytes VC15 x64 TCLib Linkage
// 19,456 Bytes GCC Mingw 4.8; x64
// 59,392 Bytes VC15 (Visual Studio 2008) x64 Microsoft's LIBCMT Linkage
//#define TCLib
#ifndef   UNICODE         
    #define   UNICODE     
#endif                    
#ifndef   _UNICODE        
    #define   _UNICODE    
#endif                    
#include <windows.h>      
#ifdef TCLib              
   #include "stdio.h"     
#else                     
   #include <stdio.h>     
#endif
#include "XLFunctions.h"

int main()
{
 IDispatch*   pXLApp           = NULL;
 IDispatch*   pXLWorkBooks     = NULL;
 IDispatch*   pXLWorkBook      = NULL;
 IDispatch*   pXLWorkSheet     = NULL;
 wchar_t      szWorkBookPath[] = L"C:\\Code\\CodeBlks\\XL_Dispatch\\XL15\\Book1.xls"; 
 wchar_t      szSheet[]        = L"Sheet2";
 wchar_t      szRange[]        = L"A1";  
 wchar_t      szCell[64];
  
 CoInitialize(NULL);                                           // Start COM Subsystem
 pXLApp       = XLStart(true,&pXLWorkBooks);                   // Start Excel
 pXLWorkBook  = OpenXLWorkBook(pXLWorkBooks,szWorkBookPath);   // Open *.xls WorkBook    
 pXLWorkSheet = XLOpenWorkSheet(pXLWorkBook,szSheet);          // Select/Open Specific WorkSheet
 GetXLCell(pXLWorkSheet,szRange,szCell,64);                    // Retrieve Cell Data
 wprintf(L"szCell = %s\n",szCell);                             // Output Cell Contents To Console
 pXLWorkSheet->Release();                                      // Release Pointer To Work Sheet
 pXLWorkBook->Release();                                       // Release Pointer To Work Book
 pXLWorkBooks->Release();                                      // Release Pointer To Work Books Collection
 getchar();                                                    // Hold Console & Excel Open
 XLQuit(pXLApp);                                               // Close Excel Application
 pXLApp->Release();                                            // Release Pointer To Excel App
 CoUninitialize();                                             // Shut Down COM Subsystem

 return 0;
}


Then the next function is where you specify the WorkSheet name you wish to open within the WorkBook as described above.....

XLOpenWorkSheet(pXLWorkBook,szSheet);

Note again the szSheet parameter, which is a pointer to a character string containing the name of the WorkSheet.

Note the above two functions are wrappers around wrappers around wrappers to make main() smaller and offload the 'dirty' code that actually does the work to functions in include files.
freddie1 (1838)
To continue with what I said above, if your intent is to locate from within your app an already running instance of Excel, then you are going to have to deal with locating that instance in the Windows COM/OLE system 'Running Object Table', i.e., ROT.....

https://docs.microsoft.com/en-us/windows/desktop/api/objidl/nn-objidl-irunningobjecttable

Good Luck With That! :)

If you are brave enough to try, start here....

https://docs.microsoft.com/en-us/windows/desktop/com/monikers

What I said above isn't completely correct. If your intent indeed is to locate a running instance of Excel and interact with it in some way, then you wouldn't be using much if any of the code I provided, which assumes you have the fully qualified path to the WorkBook you want to open, and you know the name of the WorkSheet within that WorkBook that you want to read/edit data from.

If, as I suspect from what you said, you are trying to interact with some running instance of Excel, then you'll need to use Windows 'Running Object Table', and the various Interfaces which work with that object, to get a pointer to the Excel instance of interest to you. At that point some of the functions I provided might be useful.

I have personally not explored this aspect of COM in my work, and am only slightly familiar with it, as, in my work, I always know the fully qualified path to the Excel WorkBook I want to open.

Last edited on
freddie1 (1838)
That might be a project I'd consider working on if I have time (locating and interacting with a somewhat unknown running instance of Excel), but not sure I have time. I'm retired and building a house in the mountains, and that's taking up most of my time.
Android1 (17)
Thanks for the explanation, I will try.

PS:A house in the mountains is great! Good luck with this! :)
freddie1 (1838)
Having any luck Android1?

Was I right that you are trying to interact with an already running Excel app that has some workbook or other opened?

I've been working on it and making progress, but I don't have it yet.
freddie1 (1838)
Finally got it. If you open Excel and type "Hello, World!" in "A1" of Sheet1 it'll print out to the console....

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
 
// cl Test10.cpp XLFunctions.cpp /O1 /Os /GS- TCLib.lib kernel32.lib ole32.lib oleaut32.lib
// cl Test10.cpp XLFunctions.cpp /O1 /Os /MT kernel32.lib ole32.lib oleaut32.lib
//#define TCLib
#include <windows.h>         // 57,856 bytes with static LIBCMT Linkage (VC15)
#ifdef TCLib                 //  4,096 bytes with static TCLib Linkage
  #include "stdio.h"
#else
  #include <stdio.h>
#endif 
#include "XLFunctions.h"

int main()
{
 const CLSID   CLSID_XLApplication   = {0x00024500,0x0000,0x0000,{0xC0,0x00,0x00,0x00,0x00,0x00,0x00,0x46}};
 IUnknown*     pXLApp                = NULL;
 IDispatch*    pXLDispatch           = NULL;
 IDispatch*    pXLActiveSheet        = NULL;
 wchar_t       szText[128];
 LCID          lcid;
 HRESULT       hr;
 
 CoInitialize(NULL);
 lcid=GetUserDefaultLCID(); 
 hr=GetActiveObject(CLSID_XLApplication,NULL,&pXLApp); 
 if(!pXLApp)
    goto Sad_Ending;
 hr=pXLApp->QueryInterface(IID_IDispatch,(void**)&pXLDispatch);
 if(!pXLDispatch)
    goto Sad_Ending;
 pXLActiveSheet=GetDispatchObject(pXLDispatch,0x00000133,DISPATCH_PROPERTYGET,lcid);   
 if(!pXLActiveSheet)
    goto Sad_Ending;
 printf("Got pXLActiveSheet!\n");  
 hr=GetXLCell(pXLActiveSheet,lcid,L"A1",szText,128);
 if(SUCCEEDED(hr))
    wprintf(L"%s\n",szText);
 else
    wprintf(L"Failed To Get Text Out Of A1\n");
   
 Sad_Ending:
 if(pXLActiveSheet)
    pXLActiveSheet->Release();
 if(pXLDispatch)
    pXLDispatch->Release();
 if(pXLApp)
    pXLApp->Release();  
 CoUninitialize();
 
 return 0;
}

#if 0

C:\Code\PwrBasic\PBCC6\GetRunningObjectTable>cl Test10.cpp XLFunctions.cpp /O1 /Os /GS- TCLib.lib kernel32.lib ole32.lib oleaut32.lib
Microsoft (R) C/C++ Optimizing Compiler Version 15.00.21022.08 for x64
Copyright (C) Microsoft Corporation.  All rights reserved.

Test10.cpp
XLFunctions.cpp
Generating Code...
Microsoft (R) Incremental Linker Version 9.00.21022.08
Copyright (C) Microsoft Corporation.  All rights reserved.

/out:Test10.exe
Test10.obj
XLFunctions.obj
TCLib.lib
kernel32.lib
ole32.lib
oleaut32.lib

C:\Code\PwrBasic\PBCC6\GetRunningObjectTable>Test10.exe
Got pXLActiveSheet!
Hello, World!

C:\Code\PwrBasic\PBCC6\GetRunningObjectTable>

#endif


Here is XLFunctions.h...

1
2
3
4
5
6
7
8
9
10
11
12
 
// XLFunctions.h
#ifndef XLFunctions_h
#define XLFunctions_h

HRESULT SetVisible(IDispatch* pObject, LCID lcid);
HRESULT GetXLCell(IDispatch* pXLWorksheet, LCID& lcid, wchar_t* pszRange, wchar_t* pszCell, size_t iBufferLength);
HRESULT GetCell(IDispatch* pXLSheet, LCID lcid, wchar_t* pszRange, VARIANT& pVt);
IDispatch* SelectWorkSheet(IDispatch* pXLWorksheets, LCID& lcid, wchar_t* pszSheet);
IDispatch* OpenXLWorkBook(IDispatch* pXLWorkbooks, LCID& lcid, wchar_t* pszWorkBookPath);
IDispatch* GetDispatchObject(IDispatch* pCallerObject, DISPID dispid, WORD wFlags, LCID lcid);

#endif

Last edited on
freddie1 (1838)
...and XLFunctions.cpp...

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
 
#ifndef   UNICODE
   #define   UNICODE
#endif
   #ifndef   _UNICODE
#define   _UNICODE
#endif
#include <windows.h>
#include "XLFunctions.h"


HRESULT SetVisible(IDispatch* pObject, LCID lcid) 
{                                                 
 VARIANT         vArgArray[1];                    
 DISPPARAMS      DispParams;                      
 DISPID          dispidNamed;                     
 VARIANT         vResult;                         
 HRESULT         hr;                              
                                                  
 VariantInit(&vArgArray[0]);                      
 vArgArray[0].vt               = VT_BOOL;         
 vArgArray[0].boolVal          = TRUE;                 
 dispidNamed                   = DISPID_PROPERTYPUT;   
 DispParams.rgvarg             = vArgArray;            
 DispParams.rgdispidNamedArgs  = &dispidNamed;    
 DispParams.cArgs              = 1;               
 DispParams.cNamedArgs         = 1;               
 VariantInit(&vResult);
 hr=pObject->Invoke(0x0000022e,IID_NULL,lcid,DISPATCH_PROPERTYPUT,&DispParams,&vResult,NULL,NULL);

 return hr;
}


HRESULT GetXLCell(IDispatch* pXLWorksheet, LCID& lcid, wchar_t* pszRange, wchar_t* pszCell, size_t iBufferLength)
{
 DISPPARAMS      NoArgs         = {NULL,NULL,0,0};     
 IDispatch*      pXLRange       = NULL;                
 VARIANT         vArgArray[1];                         
 VARIANT         vResult;                              
 DISPPARAMS      DispParams;                           
 HRESULT         hr;                                   
                                                       
 VariantInit(&vResult);                                
 vArgArray[0].vt                = VT_BSTR,             
 vArgArray[0].bstrVal           = SysAllocString(pszRange);
 DispParams.rgvarg              = vArgArray;
 DispParams.rgdispidNamedArgs   = 0;          
 DispParams.cArgs               = 1;
 DispParams.cNamedArgs          = 0;          
 hr=pXLWorksheet->Invoke                      
 (                                            
  0xC5,                                       
  IID_NULL,                                   
  lcid,                                       
  DISPATCH_PROPERTYGET,                       
  &DispParams,                                
  &vResult,                                   
  NULL,                                       
  NULL                                        
 );                                           
 if(FAILED(hr))                               
    return E_FAIL;                            
 pXLRange=vResult.pdispVal;                   
                                              
 //Member Get Value <6> () As Variant         
 VariantClear(&vArgArray[0]);                 
 hr=pXLRange->Invoke                          
 (                                            
  6,                                          
  IID_NULL,                                   
  lcid,                                       
  DISPATCH_PROPERTYGET,                       
  &NoArgs,                                    
  &vResult,                                   
  NULL,                                       
  NULL                                        
 );                                           
 if(SUCCEEDED(hr))                            
 {                                            
    if(vResult.vt==VT_BSTR)                   
    {                                                    
       if(SysStringLen(vResult.bstrVal)<iBufferLength)   
       {                                                 
          wcscpy(pszCell,vResult.bstrVal);               
          VariantClear(&vResult);             
          return S_OK;
       }
       else
       {
          VariantClear(&vResult);
          return E_FAIL;
       }
    }
    else
    {
       pszCell[0]=0;
       VariantClear(&vResult);
    }
 }
 pXLRange->Release();

 return E_FAIL;
}


HRESULT GetCell(IDispatch* pXLSheet, LCID lcid, wchar_t* pszRange, VARIANT& pVt)
{
 DISPPARAMS      NoArgs         = {NULL,NULL,0,0};           
 IDispatch*      pXLRange       = NULL;                      
 VARIANT         vArgArray[1];                               
 VARIANT         vResult;                                    
 DISPPARAMS      DispParams;                                 
 HRESULT         hr;                                         
                                                             
 VariantInit(&vResult);                                      
 vArgArray[0].vt                = VT_BSTR,                   
 vArgArray[0].bstrVal           = SysAllocString(pszRange);  
 DispParams.rgvarg              = vArgArray;                 
 DispParams.rgdispidNamedArgs   = 0;                         
 DispParams.cArgs               = 1;                         
 DispParams.cNamedArgs          = 0;                         
 hr=pXLSheet->Invoke(0xC5,IID_NULL,lcid,DISPATCH_PROPERTYGET,&DispParams,&vResult,NULL,NULL);
 if(FAILED(hr))
    return hr;
 pXLRange=vResult.pdispVal;

 //Member Get Value <6> () As Variant
 VariantClear(&vArgArray[0]);
 VariantClear(&pVt);
 hr=pXLRange->Invoke(6,IID_NULL,lcid,DISPATCH_PROPERTYGET,&NoArgs,&pVt,NULL,NULL);
 pXLRange->Release();

 return hr;
}


IDispatch* SelectWorkSheet(IDispatch* pXLWorksheets, LCID& lcid, wchar_t* pszSheet)
{
 VARIANT         vResult;
 HRESULT         hr;
 VARIANT         vArgArray[1];
 DISPPARAMS      DispParams;
 DISPID          dispidNamed;
 IDispatch*      pXLWorksheet   = NULL;

 VariantInit(&vResult);
 vArgArray[0].vt                = VT_BSTR;
 vArgArray[0].bstrVal           = SysAllocString(pszSheet);
 DispParams.rgvarg              = vArgArray;
 DispParams.rgdispidNamedArgs   = &dispidNamed;
 DispParams.cArgs               = 1;
 DispParams.cNamedArgs          = 0;
 hr=pXLWorksheets->Invoke(0xAA,IID_NULL,lcid,DISPATCH_PROPERTYGET,&DispParams,&vResult,NULL,NULL);
 if(FAILED(hr))
    return NULL;
 pXLWorksheet=vResult.pdispVal;
 SysFreeString(vArgArray[0].bstrVal);

 // Worksheet::Select()
 VariantInit(&vResult);
 VARIANT varReplace;
 varReplace.vt                  = VT_BOOL;
 varReplace.boolVal             = VARIANT_TRUE;
 dispidNamed                    = 0;
 DispParams.rgvarg              = &varReplace;
 DispParams.rgdispidNamedArgs   = &dispidNamed;
 DispParams.cArgs               = 1;
 DispParams.cNamedArgs          = 1;
 hr=pXLWorksheet->Invoke(0xEB,IID_NULL,lcid,DISPATCH_METHOD,&DispParams,&vResult,NULL,NULL);

 return pXLWorksheet;
}


IDispatch* OpenXLWorkBook(IDispatch* pXLWorkbooks, LCID& lcid, wchar_t* pszWorkBookPath)
{
 VARIANT         vResult;
 VARIANT         vArgArray[1];
 DISPPARAMS      DispParams;
 DISPID          dispidNamed;
 HRESULT         hr;

 VariantInit(&vResult);         
 vArgArray[0].vt                = VT_BSTR;
 vArgArray[0].bstrVal           = SysAllocString(pszWorkBookPath);
 DispParams.rgvarg              = vArgArray;
 DispParams.rgdispidNamedArgs   = &dispidNamed;
 DispParams.cArgs               = 1;
 DispParams.cNamedArgs          = 0;
 hr=pXLWorkbooks->Invoke(682,IID_NULL,lcid,DISPATCH_METHOD,&DispParams,&vResult,NULL,NULL);
 SysFreeString(vArgArray[0].bstrVal);
 if(FAILED(hr))
    return NULL;
 else
    return vResult.pdispVal;
}


IDispatch* GetDispatchObject(IDispatch* pCallerObject, DISPID dispid, WORD wFlags, LCID lcid)
{
 DISPPARAMS   NoArgs     = {NULL,NULL,0,0};
 VARIANT      vResult;
 HRESULT      hr;

 VariantInit(&vResult);
 hr=pCallerObject->Invoke(dispid,IID_NULL,lcid,wFlags,&NoArgs,&vResult,NULL,NULL);
 if(FAILED(hr))
    return NULL;
 else
    return vResult.pdispVal;
}

mbozzi (3909)
@freddie1, this is really cool. I didn't even know this was possible.
freddie1 (1838)

@freddie1, this is really cool. I didn't even know this was possible.


I can reach really, really, high, but its only because I'm standing on the shoulders of a giant...

http://www.jose.it-berater.org/smfforum/index.php?topic=5465.0

Jose was my inspiration for all this stuff, which goes back over 20 years. Whenever I get stuck, as in this case, I turn to Jose.

freddie1 (1838)
I work Microsoft Word in exactly the same manner.

The situation with Excel though is this...

Back in the 1990s, where I worked all the folks had all these Excel Forms/Spreadsheets where they would enter large volumes of complex data. They were pretty much standardized forms, and everybody used them. At that time I was automating our data collection and processing activities, and it didn't take me too long to realize that every one of these forms represented a data entry program I didn't have to code myself if I could find someway of reading the data off of these blasted forms!

For many years I used PowerBASIC - not C or C++. PowerBASIC is a very high performance Win32 SDK based language. I code in it exactly as in C++. In 2011 the creator of the language - Robert Zale, passed away. At the time he was working on an x64 version of the language, but he was never able to complete it. I needed a new programming language to replace PowerBASIC. C++ was the closest thing I could find that gave me the performance of PowerBASIC, but there were many issues I had to overcome.

The 1st issue was the large size of the binaries produced by C++. PowerBASIC executables were only a fraction of the size. I realize it doesn't matter, but to me it did. The only way I could solve this was by jettisoning the C++ Standard Library and writing my own replacement classes, writing my own C Runtime, and building /nodefaultlib.

The 2nd major issue was replacing a lot of built in PowerBASIC COM/OLE code such as this Excel stuff with low level COM/OLE code. That's what you see above in XLFunctions.h and XLFunctions.cpp.

It worked me pretty hard to accomplish all that (took years), but I'm satisfied with the result. The above program builds to only 4k for me as a stand alone x64 binary using my TCLib.lib as opposed to the MS C Runtime.

My inspiration for all this COM stuff was Jose Roca though. Back in the 1990s nobody at PowerBASIC had a clue how to access these COM/OLE objects that were being used with such success by Visual Basic programmers - no more than a C coder would have known how to access them. In terms of C++, access was through class libraries and/or templates produced by Microsoft which very, very, very few folks outside of Microsoft really understood. So it became just a 'cookbook' affair - copy/paste inheritance. To this day that's the standard and accepted C++ way of attacking the problem, as the post above by Thomas illustrates. So in a sense I had to do for C++ what Jose did for PowerBASIC many years ago - figure out the low level structures in memory and build myself efficient implementations that allowed me to read/write these various Microsoft Office applications.

Android1 (17)
Hello,
Everything turned out to be simple:

Take an example microsoft:
https://support.microsoft.com/en-us/help/216686/how-to-automate-excel-from-c-without-using-mfc-or-import

and in order to read and not write data, we do this:
1
2
3
4
5
6
7
8
9
10
 
//Get value from the range
	VARIANT rngResult;
	
	AutoWrap(DISPATCH_PROPERTYGET, &rngResult, pXlRange, (LPOLESTR)L"Value", 0);

	BSTR ival = rngResult.bstrVal; // We read Excel cell
	
	string Finaly_value = _com_util::ConvertBSTRToString(ival); // convert in Strng C++
	
	cout << Finaly_value << endl;  // Ready
Last edited on
Topic archived. No new replies allowed.