C++

Forum

 
Access to an excel file via C++

Pages: 1234
Cojones (71)
Hello,

In the past few days I've been trying to open an excel (.xls, not .xlsx) and read some cells from it.

I've tried several libs found around the internet. To name a few...
https://numberduck.com/Documentation.php
http://www.codeproject.com/Articles/42504/ExcelFormat-Library
http://libxl.com/download.html

The main problem is that the excel files I have to open usually have over 100MB. Most of the libs I tried can't open those files. I managed to open the file using libxl but in the trial version it has only 100 cell operations. I went further and made a reader application that communicates with the GUI application. The reader application after those 100 cell operations closes and the GUI application requests it to open again, thus giving me 100 more cell operations, but this is inefficient. Loading an excel that has over 100MB takes a lot of time and I have to read at about 20-30 cols and 4000-60000 rows. It's easy to do the math and figure out how many times it needs to reopen the excel file to work with those 100 cell operations before I have to close and start the reader application again... :|

When all those things failed, I've started searching again for something to make it easy to open excel files and I've come across this: http://code.msdn.microsoft.com/CppAutomateExcel-be36992e. This is able to open the files, I suppose it doesn't have limits but it has a BIG negative part... It's not documented. Visual Studio doesn't have intellisense for this thing.

If you have any ideas where I could find documentation for CppAutomateExcel or any other excel lib that works with huge files, please share.

Thanks a lot,
Alin
kbw (9488)
You shouldn't try to do it directly. The last time Microsoft published code that accessed .xls files directly was back with Excel4 20 years ago.

The recomended way to access an Excel file (whatever version) is to use the COM interface. This requires a suitable version of Excel to be installed on the computer that processes the file.

Welcome to Windows and MS Office.
Last edited on
freddie1 (1838)
I'll see if I can't help you with this Cojones. Like kbw said, COM is the way to go. Another possibility is ODBC, and a number of years ago I posted extensive code here showing how to read/write *.xls data that way. The real justification for ODBC related to Excel, is that an installation of Excel isn't necessary like with COM.

Anyway, here is a "Hello, World!" C++ Excel program which uses the IDispatch Interface to open Excel and write "Hello, World!" to cell A1 ...

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
 
#include <windows.h>                // Program Demonstrates Late Bound OLE COM Access To MS Excel Spreadsheet Using C++.
#include <tchar.h>                  // "Hello, World! Is Written To Cell A1 Of Sheet #1 In Visible Workbook. IDispatch
#include <cstdio>                   // Interface Using GetIDsOfNames() And Invoke() Used Throughout.
const CLSID CLSID_XLApplication     = {0x00024500,0x0000,0x0000,{0xC0,0x00,0x00,0x00,0x00,0x00,0x00,0x46}}; // CLSID of Excel
const IID   IID_Application         = {0x000208D5,0x0000,0x0000,{0xC0,0x00,0x00,0x00,0x00,0x00,0x00,0x46}}; // IID of _Application

int main()
{
 DISPPARAMS NoArgs={NULL,NULL,0,0}; // This variable is used in easiest Invoke() call when the method has no parameters.  When
 IDispatch* pXLApp=NULL;            // using the IDispatch interface in conjunction with Invoke() method parameters must be loaded
 DISPPARAMS DispParams;             // into a DISPPARAMS struct.  The actual parameters are loaded into VARIANTs, and one of the
 VARIANT CallArgs[1];               // members of the DISPPARAMS struct is a pointer to the array of VARIANT.  The other members
 VARIANT vResult;                   // of the DISPARAMS struct tell Invoke() how many parameters are being passed, as well as other
 DISPID dispid;                     // specifics such as the type of the call (propput, propget, etc.).
 HRESULT hr;

 CoInitialize(NULL);
 hr=CoCreateInstance(CLSID_XLApplication, NULL, CLSCTX_LOCAL_SERVER, IID_Application, (void**)&pXLApp);
 if(SUCCEEDED(hr))
 {
    OLECHAR* szVisible=(OLECHAR*)L"Visible";
    hr=pXLApp->GetIDsOfNames(IID_NULL,&szVisible,1,GetUserDefaultLCID(),&dispid);
    if(SUCCEEDED(hr))
    {
       VariantInit(&CallArgs[0]);
       CallArgs[0].vt               = VT_BOOL;
       CallArgs[0].boolVal          = TRUE;
       DISPID dispidNamed           = DISPID_PROPERTYPUT;
       DispParams.rgvarg            = CallArgs;
       DispParams.rgdispidNamedArgs = &dispidNamed;
       DispParams.cArgs             = 1;
       DispParams.cNamedArgs        = 1;
       VariantInit(&vResult);       //  Call or Invoke _Application::Visible(true);
       hr=pXLApp->Invoke(dispid,IID_NULL,LOCALE_USER_DEFAULT,DISPATCH_PROPERTYPUT,&DispParams,&vResult,NULL,NULL);
       OLECHAR* szWorkbooks=(OLECHAR*)L"Workbooks";
       hr=pXLApp->GetIDsOfNames(IID_NULL,&szWorkbooks,1,GetUserDefaultLCID(),&dispid);
       if(SUCCEEDED(hr))
       {
          IDispatch* pXLBooks=NULL;    //  Get Workbooks Collection
          VariantInit(&vResult);       //  Invoke _Application::Workbooks(&pXLBooks) << returns IDispatch** of Workbooks Collection
          hr=pXLApp->Invoke(dispid,IID_NULL,LOCALE_USER_DEFAULT,DISPATCH_PROPERTYGET,&NoArgs,&vResult,NULL,NULL);
          if(SUCCEEDED(hr))
          {
             pXLBooks=vResult.pdispVal;
             IDispatch* pXLBook=NULL;  //  Try to add Workbook
             OLECHAR* szAdd=(OLECHAR*)L"Add";
             hr=pXLBooks->GetIDsOfNames(IID_NULL,&szAdd,1,GetUserDefaultLCID(),&dispid);
             if(SUCCEEDED(hr))
             {
                VariantInit(&vResult);    //  Invoke Workbooks::Add(&Workbook)  << returns IDispatch** of Workbook Object
                hr=pXLBooks->Invoke(dispid,IID_NULL,LOCALE_USER_DEFAULT,DISPATCH_METHOD|DISPATCH_PROPERTYGET,&NoArgs,&vResult,NULL,NULL);
                if(SUCCEEDED(hr))
                {
                   pXLBook=vResult.pdispVal;
                   OLECHAR* szActiveSheet=(OLECHAR*)L"ActiveSheet";
                   hr=pXLApp->GetIDsOfNames(IID_NULL,&szActiveSheet,1,GetUserDefaultLCID(),&dispid);
                   if(SUCCEEDED(hr))
                   {
                      IDispatch* pXLSheet=NULL;  // Try To Get ActiveSheet
                      VariantInit(&vResult);     // Invoke _Application::ActiveSheet(&pXLSheet);  << ret IDispatch** to Worksheet (Worksheet)
                      hr=pXLApp->Invoke(dispid,IID_NULL,LOCALE_USER_DEFAULT,DISPATCH_PROPERTYGET,&NoArgs,&vResult,NULL,NULL);
                      if(SUCCEEDED(hr))
                      {
                         pXLSheet=vResult.pdispVal;
                         OLECHAR* szRange=(OLECHAR*)L"Range";
                         hr=pXLSheet->GetIDsOfNames(IID_NULL,&szRange,1,GetUserDefaultLCID(),&dispid);
                         if(SUCCEEDED(hr))
                         {
                            IDispatch* pXLRange=NULL;
                            VariantInit(&vResult);
                            CallArgs[0].vt=VT_BSTR,
                            CallArgs[0].bstrVal=SysAllocString(L"A1");
                            DispParams.rgvarg            = CallArgs;
                            DispParams.rgdispidNamedArgs = 0;
                            DispParams.cArgs             = 1;  // Try to get Range
                            DispParams.cNamedArgs        = 0;  // Invoke _Worksheet::Range("A1")  << returns IDispatch** to dispinterface Range
                            hr=pXLSheet->Invoke(dispid,IID_NULL,LOCALE_USER_DEFAULT,DISPATCH_PROPERTYGET,&DispParams,&vResult,NULL,NULL);
                            if(SUCCEEDED(hr))
                            {
                               pXLRange=vResult.pdispVal;
                               OLECHAR* szValue=(OLECHAR*)L"Value";
                               hr=pXLRange->GetIDsOfNames(IID_NULL,&szValue,1,GetUserDefaultLCID(),&dispid);
                               if(SUCCEEDED(hr))
                               {
                                  VariantClear(&CallArgs[0]);
                                  CallArgs[0].vt               = VT_BSTR;
                                  CallArgs[0].bstrVal          = SysAllocString(L"Hello, World!");  //Try to set data to cell A1 using pXLRange
                                  DispParams.rgvarg            = CallArgs;
                                  DispParams.rgdispidNamedArgs = &dispidNamed;
                                  DispParams.cArgs             = 1;  // Try to write to Value member of Range dispinterface
                                  DispParams.cNamedArgs        = 1;  // Invoke Range::Value(L"Hello, World!")
                                  hr=pXLRange->Invoke(dispid,IID_NULL,LOCALE_USER_DEFAULT,DISPATCH_PROPERTYPUT,&DispParams,NULL,NULL,NULL);
                                  pXLRange->Release();
                               }
                            }
                         }
                         pXLSheet->Release();
                      }
                   }
                   pXLBook->Release();
                }
             }
             pXLBooks->Release();
          }
       }
       getchar();
    }
    VariantInit(&vResult);  // Try to do _Application::Close()
    hr=pXLApp->Invoke(0x0000012e,IID_NULL,LOCALE_USER_DEFAULT,DISPATCH_METHOD,&NoArgs,&vResult,NULL,NULL);
    pXLApp->Release();
 }
 CoUninitialize();

 return 0;
}
freddie1 (1838)
The above is just a C++ Console mode program Cojones, and I used the 10.05 version of Code::Blocks to develop and test it. I haven't tested it with VStudio (2008 is the newest version I have). I'd be real curious if it works for you. Please let me know. This is something I haven't worked with for two years or so, so any questions you may have I'd have to study up on it a bit, but I could answer them.

I don't know your background or level of proficiency as a programmer, so I'll state up front that isn't beginner level code. COM is very difficult to learn.

In terms of using C++ to access MS Office applications, about the only worse language I can think of for doing that besides C++ might be assembler, and that wouldn't be much worse.
freddie1 (1838)
And I see that you aren't interested in writing data but rather reading it. My application above would have to be modified to read cells, however, the general techniques I've outlined above are what has to happen.

One of the first if not the first question you are going to have is how does one find out the COM member function names and how to use them. Most COM objects (such as Excel) publish their interfaces in TypeLibs, which can be included within the binary itself, or as seperate *.tlb files. These can be read by OleView.exe - a free MS utility. In my case I spent months writing my own "typelib browser" utility to dump these type libs to a *.h file containing interface definitions I could use with cpp code where I wanted to access/use COM objects/components. It wouldn't have been necessary for me to do this because I have VStudio and a utility of sorts is included with that to do just that, but I didn't care for it so I wrote my own. Now, Excel has a particularly nasty type library. Of course, Excel is a pig, a dog, a very miserable thing, so this isn't too unusual for MS crap.

At the very top of the code above you'll see

OLECHAR* szVisible=(OLECHAR*)L"Visible";

'Visible' is one of the methods of the Application Interface of Excel, and that code there is attempting to use IDispatch to set this property method to true so Excel shows itself. Don't know if this will fit, but here's the code my utility generated just for the Application Interface of Excel. In the Interface below I see Imy code prefaced the "Visible" property method with "Get" or "Set", and that's a whole 'nother issue ...

freddie1 (1838)
Nope. Tried to post but just that Application interface, which is like 1% of the Excel interfaces, was something like 25000 characters, and this site only allows 8192. But the method looks like so ...

1
2
3
4
5
6
7
8
9
10
11
12
13
 
[odl, uuid(000208D5-0000-0000-C000-000000000046), helpcontext(0x00020001), dual, oleautomation] interface _Application : IDispatch
{
 ...
 [id(0x0000022e), propput, helpcontext(0x0001022e)] HRESULT Visible([in, lcid] long lcid, [in] VARIANT_BOOL RHS);
 [id(0x0000022e), propput, helpcontext(0x0001022e)] HRESULT Visible([in, lcid] long lcid, [in] VARIANT_BOOL RHS);
 ...
 [id(0x0000023c), propget, helpcontext(0x0001023c)] HRESULT Workbooks([out, retval] Workbooks** RHS);
 ...
 [id(0x00000133), propget, helpcontext(0x00010133)] HRESULT ActiveSheet([out, retval] IDispatch** RHS);
 ...
}
freddie1 (1838)
That would be the Idl (Interface Definition Language). My code outputs C++ headers of that. But the only use of it regarding Excel is to give the names to be fed into IDispatch::GetIDOfNames() and IDispatch::Invoke(). You can't call Excel COM interfaces directly - you need to use IDispatch which is a concept from h*** for C++ coders. Actually, that's only partly true. Some of the methods work through direct VTable access, but most don't. Its like a lot of MS stuff. Are you by now beginning to get the idea how miserable this is to work with in C++? I could likely make you cry by showing how easy it is to do in PowerBASIC - just a couple lines of code and you would be in and reading data.
freddie1 (1838)
Now I'm just doing this out of sheer meanness Alin, but here is the PowerBASIC code required to open an Excel Book1.xls file where Sheet1 has "Hello, World!" in cell A1, and retrieve that from cell A1 and print it to the console screen ...

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
#Compile Exe
#Dim All

Function PBMain() As Long
  Local pDisp, pWorkBook, pWorkSheet As IDispatch
  Local vVnt1,vVnt2 As Variant

  pDisp = NewCom "Excel.Application"
  vVnt1=1
  Object Let pDisp.Visible=vVnt1
  vVnt1="C:\Code\PwrBasic\PBCC6\ExcelAuto\Book1.xls"
  Object Call pDisp.WorkBooks.Open(vVnt1) To pWorkBook
  vVnt1="Sheet1"
  Object Call pWorkbook.Sheets(vVnt1).Select
  Object Get pWorkbook.ActiveSheet To vVnt1
  Set pWorkSheet = vVnt1
  vVnt1="A1"
  Object Get pWorkSheet.Range(vVnt1).Value To vVnt2
  Con.Print Variant$(vVnt2)
  Con.WaitKey$
  Object Call pWorkbook.Close

  PBMain=0
End Function



Like I said, pure meanness. I wanted to ruin your day!:)
Cojones (71)
Is there any possibility to make a code in a language that has easy access to excel and provide functions for a C++ program to load and read from the excel files?

The example you wrote above in C++... I didn't manage to find documentation for it. Can you please post a link where I can find any? All seem to spin around keywords like these L"Workbooks" L"Visible" L"Add" L"ActiveSheet" L"Range" L"Value". Is it a place where I can find a list of these keywords and the parameters I need to call ->GetIDsOfNames(); and ->Invoke(); to make it work?

I tried to find them in OLE/COM Object Viewer but... Tough luck.

I've seen that you wrote about having Excel installed. I have it installed.
closed account (N36fSL3A)
I believe you are looking for a C++ Excel library. A quick Google should do, and you should be able to figure out how to use it via the library's documentation.
Cojones (71)
Like I said in the first post. I've been doing that for several days. I've even linked some of those that I tried.
freddie1 (1838)

Is there any possibility to make a code in a language that has easy access to excel and provide functions for a C++ program to load and read from the excel files?


If I understand your question correctly, yes. I imagine I could easily write a PowerBASIC dll which internally would retrieve the values of some cell or cells passed in through a parameter, and I could export that function so it could be used by a C++ app. However, that seems awkward to me.

If you'll give me a bit of time, I'll see if I can modify my code posted above to retrieve data from whatever cells are specified. It is something I should be able to do, and I have been attempting it for the last half hour without success. But like I said, its been about two years since I've looked at this stuff from a C++ perspective, and I need to review the material and get myself up to speed with it again.


I tried to find them in OLE/COM Object Viewer but... Tough luck.


In OleView, go to the File menu and choose "View Typelib". In the dialog box which comes up navigate to your Excel.exe file wherever it is at. I believe the typelib for Excel is embedded within the exe. On the computer I writing this I have Excel 2010 installed, and my directions above worked. What version of Excel do you have?

In any case, if you succeed at that you'll get a truely massive output in OleView. Those are all the interfaces and methods of the interfaces. There appear to be literally tens of thousands of them. In there you will find the terms you listed asbove such as "Workbook", "Visible", etc. Workbook is an interface. Visible is a property of the _Application interface, etc.

Give me a little time - maybe a few hours or a day, and I'll see if I can't provide you what you need. Your question sparked my curiousity, as at this time I may have a use for this myself for something I've got going now. I always use PowerBASIC for this work because that language has a built in high level capability of handling it, but I should be able to do it in C++ too or I ain't worth a crap.

freddie1 (1838)
Alin, you still there?

I'm still working on it in a great state of depression. I'm stuck and can't believe I'm stuck. Couple years ago when I wrote the program I posted above I had no trouble getting to the point I reached where I was able to obtain an IDispatch interface pointer, set the Range to "A1", and print "Hello, World!" there. I figured it would be trieval to retrieve the text after writung it there but now I'm unable to.

However, as far as it goes, the program I posted above is completely 100% valid. You say it isn't documented and I don't have any idea where you are coming from with that because everything to do with COM, OLE, IDispatch is heavily documented in MSDN, and I am calling IDispatch members GetIDsOfNames() and Invoke() and testing the HRESULT on every call and every call is satisfactorily yielding an S_OK return which is success. The issue is simply how to set up the DISPPARAMS struct for the IDispatch::Invoke() call to retrieve the text in cell "A1".

To compile my program above and run it you need to link with Ole32.lib, OleAuto32.lib, and uuid.lib. I forgot to tell you that. Like I said, it was several years ago I created that program. I still haven't tested it with VS as I'm working with MinGW right now. Will get to it today. I would like if you would test my program though to establish to yourself that it is valid, and let me know how it turns out.
kbw (9488)
It's more painful than it has to be because you're not using ATL.
freddie1 (1838)

It's more painful than it has to be because you're not using ATL.


Right. I don't allow myself such luxuries! I'll beat the rotten SOB yet. Am getting on it now. Got an idea to try.
freddie1 (1838)
Ha! Ha! kbw! Success! Will tidy it up a mite and post.
freddie1 (1838)
Opens Excel and writes "Hello, World!" to Cell A1 of Sheet1. Then retrieves A1 and wprintf's A1 to console. Links with Ole32.lib, OleAuto32.lib, and uuid.lib. Compiles to 8.5K with couple year old Mingw. Will test with MSVC momentarily...

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
 
#include <windows.h>
#include <tchar.h>
#include <cstdio>
const CLSID CLSID_XLApplication     = {0x00024500,0x0000,0x0000,{0xC0,0x00,0x00,0x00,0x00,0x00,0x00,0x46}}; // CLSID of Excel
const IID   IID_Application         = {0x000208D5,0x0000,0x0000,{0xC0,0x00,0x00,0x00,0x00,0x00,0x00,0x46}}; // IID of _Application

int main()
{
 DISPPARAMS NoArgs={NULL,NULL,0,0};
 IDispatch* pXLApp=NULL;
 DISPPARAMS DispParams;
 VARIANT CallArgs[1];
 void* pMsgBuf=NULL;
 VARIANT vResult;
 DWORD dwFlags;
 DISPID dispid;
 HRESULT hr;
 LCID lcid;

 CoInitialize(NULL);
 dwFlags=FORMAT_MESSAGE_ALLOCATE_BUFFER | FORMAT_MESSAGE_FROM_SYSTEM;
 hr=CoCreateInstance(CLSID_XLApplication, NULL, CLSCTX_LOCAL_SERVER, IID_Application, (void**)&pXLApp);
 if(SUCCEEDED(hr))
 {
    OLECHAR* szVisible=(OLECHAR*)L"Visible";
    lcid=GetUserDefaultLCID();
    hr=pXLApp->GetIDsOfNames(IID_NULL,&szVisible,1,lcid,&dispid);
    if(SUCCEEDED(hr))
    {
       VariantInit(&CallArgs[0]);
       CallArgs[0].vt               = VT_BOOL;
       CallArgs[0].boolVal          = TRUE;
       DISPID dispidNamed           = DISPID_PROPERTYPUT;
       DispParams.rgvarg            = CallArgs;
       DispParams.rgdispidNamedArgs = &dispidNamed;
       DispParams.cArgs             = 1;
       DispParams.cNamedArgs        = 1;
       VariantInit(&vResult);       //  Call or Invoke _Application::Visible(true);
       hr=pXLApp->Invoke(dispid,IID_NULL,LOCALE_USER_DEFAULT,DISPATCH_PROPERTYPUT,&DispParams,&vResult,NULL,NULL);
       if(SUCCEEDED(hr))
       {
          OLECHAR* szWorkbooks=(OLECHAR*)L"Workbooks";
          hr=pXLApp->GetIDsOfNames(IID_NULL,&szWorkbooks,1,GetUserDefaultLCID(),&dispid);
          if(SUCCEEDED(hr))
          {
             IDispatch* pXLBooks=NULL;    //  Get Workbooks Collection
             VariantInit(&vResult);       //  Invoke _Application::Workbooks(&pXLBooks) << returns IDispatch** of Workbooks Collection
             hr=pXLApp->Invoke(dispid,IID_NULL,LOCALE_USER_DEFAULT,DISPATCH_PROPERTYGET,&NoArgs,&vResult,NULL,NULL);
             if(SUCCEEDED(hr))
             {
                pXLBooks=vResult.pdispVal;
                IDispatch* pXLBook=NULL;  //  Try to add Workbook
                OLECHAR* szAdd=(OLECHAR*)L"Add";
                hr=pXLBooks->GetIDsOfNames(IID_NULL,&szAdd,1,GetUserDefaultLCID(),&dispid);
                if(SUCCEEDED(hr))
                {
                   VariantInit(&vResult);    //  Invoke Workbooks::Add(&Workbook)  << returns IDispatch** of Workbook Object
                   hr=pXLBooks->Invoke(dispid,IID_NULL,LOCALE_USER_DEFAULT,DISPATCH_METHOD|DISPATCH_PROPERTYGET,&NoArgs,&vResult,NULL,NULL);
                   if(SUCCEEDED(hr))
                   {
                      pXLBook=vResult.pdispVal;
                      OLECHAR* szActiveSheet=(OLECHAR*)L"ActiveSheet";
                      hr=pXLApp->GetIDsOfNames(IID_NULL,&szActiveSheet,1,GetUserDefaultLCID(),&dispid);
                      if(SUCCEEDED(hr))
                      {
                         IDispatch* pXLSheet=NULL;  // Try To Get ActiveSheet
                         VariantInit(&vResult);     // Invoke _Application::ActiveSheet(&pXLSheet);  << ret IDispatch** to Worksheet (Worksheet)
                         hr=pXLApp->Invoke(dispid,IID_NULL,LOCALE_USER_DEFAULT,DISPATCH_PROPERTYGET,&NoArgs,&vResult,NULL,NULL);
                         if(SUCCEEDED(hr))
                         {
                            pXLSheet=vResult.pdispVal;
                            OLECHAR* szRange=(OLECHAR*)L"Range";
                            hr=pXLSheet->GetIDsOfNames(IID_NULL,&szRange,1,GetUserDefaultLCID(),&dispid);
                            if(SUCCEEDED(hr))
                            {
                               IDispatch* pXLRange=NULL;
                               VariantInit(&vResult);
                               CallArgs[0].vt=VT_BSTR,
                               CallArgs[0].bstrVal=SysAllocString(L"A1");
                               DispParams.rgvarg            = CallArgs;
                               DispParams.rgdispidNamedArgs = 0;
                               DispParams.cArgs             = 1;  // Try to get Range
                               DispParams.cNamedArgs        = 0;  // Invoke _Worksheet::Range("A1")  << returns IDispatch** to dispinterface Range
                               hr=pXLSheet->Invoke(dispid,IID_NULL,LOCALE_USER_DEFAULT,DISPATCH_PROPERTYGET,&DispParams,&vResult,NULL,NULL);
                               if(SUCCEEDED(hr))
                               {
                                  pXLRange=vResult.pdispVal;
                                  OLECHAR* szValue=(OLECHAR*)L"Value";
                                  hr=pXLRange->GetIDsOfNames(IID_NULL,&szValue,1,GetUserDefaultLCID(),&dispid);
                                  if(SUCCEEDED(hr))
                                  {
                                     printf("dispid (Value) = %d\n",(int)dispid);
                                     VariantClear(&CallArgs[0]);
                                     CallArgs[0].vt               = VT_BSTR;
                                     CallArgs[0].bstrVal          = SysAllocString(L"Hello, World!");  //Try to set data to cell A1 using pXLRange
                                     DispParams.rgvarg            = CallArgs;
                                     DispParams.rgdispidNamedArgs = &dispidNamed;
                                     DispParams.cArgs             = 1;  // Try to write to Value member of Range dispinterface
                                     DispParams.cNamedArgs        = 1;  // Invoke Range::Value(L"Hello, World!")
                                     hr=pXLRange->Invoke(dispid,IID_NULL,LOCALE_USER_DEFAULT,DISPATCH_PROPERTYPUT,&DispParams,NULL,NULL,NULL);

                                     // Now Retrieve!
                                     hr=pXLRange->Invoke(dispid,IID_NULL,LOCALE_USER_DEFAULT,DISPATCH_PROPERTYGET,&NoArgs,&vResult,NULL,NULL);
                                     if(SUCCEEDED(hr))
                                        wprintf(L"vResult.bstrVal = %s\n",vResult.bstrVal);
                                     else
                                     {
                                        FormatMessage(dwFlags,NULL,hr,MAKELANGID(LANG_NEUTRAL,SUBLANG_DEFAULT),(LPTSTR)&pMsgBuf,0,NULL);
                                        printf("%s\n",(char*)pMsgBuf);
                                        LocalFree(pMsgBuf);
                                     }
                                     pXLRange->Release();
                                  }
                               }
                            }
                            pXLSheet->Release();
                         }
                      }
                      pXLBook->Release();
                   }
                }
                pXLBooks->Release();
             }
          }
       }
       getchar();
    }
    VariantInit(&vResult);  // Try to do _Application::Close()
    hr=pXLApp->Invoke(0x0000012e,IID_NULL,LOCALE_USER_DEFAULT,DISPATCH_METHOD,&NoArgs,&vResult,NULL,NULL);
    pXLApp->Release();
 }
 CoUninitialize();

 return 0;
}


Next will implement code to open some specific workbook and navigate to selected sheet for extraction, as that is functionality that will in practice be needed. And no stinking ATL!
Last edited on
freddie1 (1838)
There needs to be I think a ...

VariantClear(&vResult);

after the wprintf call.
freddie1 (1838)
Have got Workbook::Open() working. You need to specify a path to the Workbook, i.e., *.xls file that you want to open. The program below will output to the console whatever text you have in A1 of the workbook you specify. The next step after this is to be able to select a specific sheet. As this one stands, it doesn't do that. It simply uses the default sheet, which isn't good enough. So I'll be working on the final step... 

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
 
#include <windows.h>
#include <tchar.h>
#include <cstdio>
const CLSID CLSID_XLApplication     = {0x00024500,0x0000,0x0000,{0xC0,0x00,0x00,0x00,0x00,0x00,0x00,0x46}}; // CLSID of Excel
const IID   IID_Application         = {0x000208D5,0x0000,0x0000,{0xC0,0x00,0x00,0x00,0x00,0x00,0x00,0x46}}; // IID of _Application

int main()
{
 DISPPARAMS NoArgs={NULL,NULL,0,0};
 IDispatch* pXLApp=NULL;
 DISPPARAMS DispParams;
 VARIANT CallArgs[1];
 void* pMsgBuf=NULL;
 VARIANT vResult;
 DWORD dwFlags;
 DISPID dispid;
 HRESULT hr;
 LCID lcid;

 CoInitialize(NULL);
 dwFlags=FORMAT_MESSAGE_ALLOCATE_BUFFER | FORMAT_MESSAGE_FROM_SYSTEM;
 hr=CoCreateInstance(CLSID_XLApplication, NULL, CLSCTX_LOCAL_SERVER, IID_Application, (void**)&pXLApp);
 if(SUCCEEDED(hr))
 {
    OLECHAR* szVisible=(OLECHAR*)L"Visible";
    lcid=GetUserDefaultLCID();
    hr=pXLApp->GetIDsOfNames(IID_NULL,&szVisible,1,lcid,&dispid);
    if(SUCCEEDED(hr))
    {
       VariantInit(&CallArgs[0]);
       CallArgs[0].vt               = VT_BOOL;
       CallArgs[0].boolVal          = TRUE;
       DISPID dispidNamed           = DISPID_PROPERTYPUT;
       DispParams.rgvarg            = CallArgs;
       DispParams.rgdispidNamedArgs = &dispidNamed;
       DispParams.cArgs             = 1;
       DispParams.cNamedArgs        = 1;
       VariantInit(&vResult);                   //  Call or Invoke _Application::Visible(true);
       hr=pXLApp->Invoke(dispid,IID_NULL,LOCALE_USER_DEFAULT,DISPATCH_PROPERTYPUT,&DispParams,&vResult,NULL,NULL);
       if(SUCCEEDED(hr))
       {
          OLECHAR* szWorkbooks=(OLECHAR*)L"Workbooks";
          hr=pXLApp->GetIDsOfNames(IID_NULL,&szWorkbooks,1,GetUserDefaultLCID(),&dispid);
          if(SUCCEEDED(hr))
          {
             IDispatch* pXLBooks=NULL;          //  Get Workbooks Collection
             VariantInit(&vResult);             //  Invoke _Application::Workbooks(&pXLBooks) << returns IDispatch** of Workbooks Collection
             hr=pXLApp->Invoke(dispid,IID_NULL,LOCALE_USER_DEFAULT,DISPATCH_PROPERTYGET,&NoArgs,&vResult,NULL,NULL);
             if(SUCCEEDED(hr))
             {
                pXLBooks=vResult.pdispVal;
                IDispatch* pXLBook=NULL;        //  Try to Open Workbook
                OLECHAR* szOpen=(OLECHAR*)L"Open";
                hr=pXLBooks->GetIDsOfNames(IID_NULL,&szOpen,1,GetUserDefaultLCID(),&dispid);
                if(SUCCEEDED(hr))
                {
                   VariantInit(&vResult);       //  Invoke Workbooks::Open(&Workbook)  << returns IDispatch** of Workbook Object
                   CallArgs[0].vt               = VT_BSTR;
                   CallArgs[0].bstrVal          = SysAllocString(L"C:\\Code\\CodeBlks\\XL_Dispatch\\Book1.xls");
                   DispParams.rgvarg            = CallArgs;
                   DispParams.rgdispidNamedArgs = &dispidNamed;
                   DispParams.cArgs             = 1;
                   DispParams.cNamedArgs        = 0;  // Invoke _Worksheet::Range("A1")  << returns IDispatch** to dispinterface Range
                   hr=pXLBooks->Invoke(dispid,IID_NULL,LOCALE_USER_DEFAULT,DISPATCH_METHOD|DISPATCH_METHOD,&DispParams,&vResult,NULL,NULL);
                   // [id(0x000000b5), helpcontext(0x000100b5)] HRESULT Add([in, lcid] long lcid, [out, retval] Workbook** RHS);
                   // [id(0x000002aa), helpcontext(0x000102aa)] HRESULT Open([in] BSTR Filename, [in, lcid] long lcid, [out, retval] Workbook** RHS);
                   if(SUCCEEDED(hr))
                   {
                      printf("We Succeeded!\n");
                      pXLBook=vResult.pdispVal;
                      OLECHAR* szActiveSheet=(OLECHAR*)L"ActiveSheet";
                      hr=pXLApp->GetIDsOfNames(IID_NULL,&szActiveSheet,1,GetUserDefaultLCID(),&dispid);
                      if(SUCCEEDED(hr))
                      {
                         IDispatch* pXLSheet=NULL;  // Try To Get ActiveSheet
                         VariantInit(&vResult);     // Invoke _Application::ActiveSheet(&pXLSheet);  << ret IDispatch** to Worksheet (Worksheet)
                         hr=pXLApp->Invoke(dispid,IID_NULL,LOCALE_USER_DEFAULT,DISPATCH_PROPERTYGET,&NoArgs,&vResult,NULL,NULL);
                         if(SUCCEEDED(hr))
                         {
                            pXLSheet=vResult.pdispVal;
                            OLECHAR* szRange=(OLECHAR*)L"Range";
                            hr=pXLSheet->GetIDsOfNames(IID_NULL,&szRange,1,GetUserDefaultLCID(),&dispid);
                            if(SUCCEEDED(hr))
                            {
                               IDispatch* pXLRange=NULL;
                               VariantInit(&vResult);
                               CallArgs[0].vt=VT_BSTR,
                               CallArgs[0].bstrVal=SysAllocString(L"A1");
                               DispParams.rgvarg            = CallArgs;
                               DispParams.rgdispidNamedArgs = 0;
                               DispParams.cArgs             = 1;  // Try to get Range
                               DispParams.cNamedArgs        = 0;  // Invoke _Worksheet::Range("A1")  << returns IDispatch** to dispinterface Range
                               hr=pXLSheet->Invoke(dispid,IID_NULL,LOCALE_USER_DEFAULT,DISPATCH_PROPERTYGET,&DispParams,&vResult,NULL,NULL);
                               if(SUCCEEDED(hr))
                               {
                                  pXLRange=vResult.pdispVal;
                                  OLECHAR* szValue=(OLECHAR*)L"Value";
                                  hr=pXLRange->GetIDsOfNames(IID_NULL,&szValue,1,GetUserDefaultLCID(),&dispid);
                                  if(SUCCEEDED(hr))
                                  {
                                     printf("dispid (Value) = %d\n",(int)dispid);
                                     VariantClear(&CallArgs[0]);
                                     hr=pXLRange->Invoke(dispid,IID_NULL,LOCALE_USER_DEFAULT,DISPATCH_PROPERTYGET,&NoArgs,&vResult,NULL,NULL);
                                     if(SUCCEEDED(hr))
                                     {
                                        wprintf(L"vResult.bstrVal = %s\n",vResult.bstrVal);
                                        VariantClear(&vResult);
                                     }
                                     else
                                     {
                                        FormatMessage(dwFlags,NULL,hr,MAKELANGID(LANG_NEUTRAL,SUBLANG_DEFAULT),(LPTSTR)&pMsgBuf,0,NULL);
                                        printf("%s\n",(char*)pMsgBuf);
                                        LocalFree(pMsgBuf);
                                     }
                                     pXLRange->Release();
                                  }
                               }
                            }
                            pXLSheet->Release();
                         }
                      }
                      pXLBook->Release();
                   }
                   else
                   {
                      FormatMessage(dwFlags,NULL,hr,MAKELANGID(LANG_NEUTRAL,SUBLANG_DEFAULT),(LPTSTR)&pMsgBuf,0,NULL);
                      printf("%s\n",(char*)pMsgBuf);
                      LocalFree(pMsgBuf);
                   }
                }
                pXLBooks->Release();
             }
          }
       }
       getchar();
    }
    VariantInit(&vResult);  // Try to do _Application::Close()
    hr=pXLApp->Invoke(0x0000012e,IID_NULL,LOCALE_USER_DEFAULT,DISPATCH_METHOD,&NoArgs,&vResult,NULL,NULL);
    pXLApp->Release();
 }
 CoUninitialize();

 return 0;
}


Last edited on
freddie1 (1838)
What looks promising is the Sheets collection of the Workbook interface. Once having that, it should be possible to call the Select method of that to get bthe sheet one wants ...

1
2
3
4
5
6
7
8
9
10
11
12
 
[odl, uuid(000208DA-0000-0000-C000-000000000046), helpcontext(0x00020021), dual, oleautomation] interface _Workbook : IDispatch 
{
 ...
 [id(0x000001e5), propget, helpcontext(0x000101e5)] HRESULT Sheets([out, retval] Sheets** RHS);
 ...
};

[odl, uuid(000208D7-0000-0000-C000-000000000046), helpcontext(0x0002000c), dual, nonextensible, oleautomation] interface Sheets : IDispatch 
{
 [id(0x000000eb), helpcontext(0x000100eb)] HRESULT Select([in, optional] VARIANT Replace, [in, lcid] long lcid);
};
Pages: 1234