C++

Forum

 
Trouble installing XLNT library

Pages: 12
freddie1 (1838)
I think the ODBC code might work OK in instances where Excel is being used something like a csv file, i.e., data is stored there something like in database columns and rows.

If on the other hand, someone has created an XL spreadsheet and it is something like a data entry form, then the COM technique where you specify the cell or cells you want might be the way to go as the data you need might be scattered about anywhere.

I don't think too many C++ coders work with the Microsoft Office products. I expect most coders who need to get data out of these applications and/or their documents likely use .NET in one form or another. Its just way easier. In C++ its really, really, complicated. On the other hand there are libraries available that can be bought or downloaded.

Where I came at it from is from my extensive use of PowerBASIC over the years. PowerBASIC isn't at all like .NET. Its a very high performance compiled language like C++. Its as fast as C++ and produces binaries that are way smaller than anything that C++ can produce using the C++ Standard Library. Here is the PowerBASIC version of that C++ COM code I posted above. Note that there aren't even any includes listed as the capability of connecting to COM components is built right into the language. In fact, the object model PowerBASIC uses is the one based on OLE/COM - not the C++ object model...

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
 
#Compile Exe  ' 22,528 bytes
#Dim All

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

  pApp  = NewCom "Excel.Application"                     ' Instantiate New Excel COM Object
  vVnt1 = 1
  Object Let pApp.Visible=vVnt1                          ' Set Visibility To True
  vVnt1 = "C:\Code\PwrBasic\PBCC6\XLAuto\Book3.xls"      ' Specify Workbook To Open
  Object Call pApp.WorkBooks.Open(vVnt1) To pWorkBook    ' Open Workbook
  vVnt1 = "Sheet2"                                       ' Specify Sheet We Want To Select
  Object Call pWorkbook.Sheets(vVnt1).Select             ' Use Object Variable To Select Sheet
  Object Get pWorkbook.ActiveSheet To vVnt1
  Set pWorkSheet = vVnt1
  vVnt1          = "A1"                                  ' Select Cell We Want To Read
  Object Get pWorkSheet.Range(vVnt1).Value To vVnt2
  Con.Print Variant$(vVnt2)                              ' Data From Cell Will Be In Variant Object
  Console.WaitKey$                                       ' Hold Console Open
  Object Call pWorkbook.Close                            ' Close Workbook
  Object Call pApp.Quit()                                ' Close App

  PBMain=0
End Function


If the COM code I posted is more of what you need, I would very much recommend you not try to understand the code in the XLFunctions.cpp file. You'll lose two much time trying to figure that out. Treat it as something of a black box. Afterall, if you were to buy a wrapper library to read Excel data, that's exactly what you would have, i.e., a black box that does what you need. I would rather concentrate on the flow of operations necessary to read the cell as shown in Main.cpp.

The reason I figured that stuff out in C++ was that it aggravated me for a long time that I could do it so easily in PowerBASIC, but not in C++. I have a pretty long history in OLE/COM so I figured it out.

By the way, all that stuff should work with *.xlsx files. I asked about it here...

https://forum.powerbasic.com/forum/user-to-user-discussions/special-interest-groups/programming-microsoft-office/762472-xlsx-files
Last edited on
freddie1 (1838)
Another interesting thing about it I learned from that Connection Strings website that Thomas posted a link to - apparently Microsoft has some sort of code now that allows 64 bit executables to read 32 bit Excel data. Wait a minute...

That doesn't make sense. Or does it? A *.xls file isn't an x86 or x64 app. Its just a file. But if ODBC is being used, that requires that a 64 bit ODBC driver be installed on the box in use if the compiled binary is x64. Maybe that's where that new code touted on that site comes into play. I don't know. I'm just so confused. It hurts to think about it.

In terms of the COM code, it doesn't seem to matter in terms of x86 or x64. My COM code I posted above I did x64 (it'll work x86 fine). In this case, i.e., running x64, it'll start an x86 process - Excel.exe. The data between the x86 app, i.e., Excel, and my x64 process, will flow transparently because COM marshalls the data using IPC (Inter-process communication). That's a complicated COM thing you definitely don't want to know about, trust me.

If its all too complicated, maybe you could check out that XLLib link from the PowerBASIC link.
Thomas1965 (4571)
Thanks Freddie,
for me this COM code looks to complicated and messy. I will stick with C# when working with office files. As a hobby programmer I can freely choose the languages and tools that are easiest for a specific task.

BTW. If you want to work with .xls files in a native way have a look here:
http://www.codeproject.com/Articles/13852/BasicExcel-A-Class-to-Read-and-Write-to-Microsoft
http://www.codeproject.com/Articles/42504/ExcelFormat-Library
freddie1 (1838)
I've decided to post more Excel COM code as I'm working on modifying a lot of my apps coded a good many years ago to High DPI Aware. Its so miserable I occasionally need a break, and working with this COL /OLE code makes me happy.

Thomas said...

...for me this COM code looks to complicated and messy....


But I had a wonderful time figuring it out! Anyhow, every couple months somebody around here asks how to get data out of Excel spreadsheets. After they look at this 'complicated, messy' code, maybe it'll teach them to stop asking!

If you'll bear with me a bit though I'll uncomplicate it and clean it up. But not quite yet. Back in my ODBC demos I told you all to put data like this in an Excel Spreadsheet named Book1.xls in Sheet1....

Id Float_Point Date_Field Text_Field
1 3.1416 11/15/1952 My Birthday
2 1.2346 6/30/1969 Walk On Moon?
3 15.1234 1/1/2006 Some String
4 0.5432 4/1/2006 April Fools Day!

The data should go in...

A1, B1, C1, D1
A2, B2, C2, D2
A3, B3, C3, D3
A4, B4, C4, D4
A5, B5, C5, D5

Like I did with ODBC, I'll now show how to extract it with COM without any libraries you havn't bought or downloaded. I guess everybody except me uses IDEs to build their code, so you'll need to do whatever you have to with your IDE to allow it (the compiler) to link with ole32.lib, oleaut32.lib, and uuid.lib. Other than that, all you need do to run the code is create the *.xls file, fix your path to it, and put the above data in Sheet1. Here's Main.cpp...

next post...
freddie1 (1838)
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
 
// g++ Main.cpp XLFunctions.cpp -oXL19.exe -m64 -lole32 -loleaut32 -luuid -Os -s
// cl Main.cpp XLFunctions.cpp /O1 /Os /FeXL19_VC.exe kernel32.lib ole32.lib oleaut32.lib uuid.lib
// cl Main.cpp XLFunctions.cpp /O1 /Os /FeXL19_TCLib.exe /GS- TCLib.lib kernel32.lib ole32.lib oleaut32.lib uuid.lib
//  7,680 Bytes VC15; x64; TCLib Linkage /nodefaultlib:libcmt.lib
// 20,480 Bytes Mingw 4.8; x64. (Links with msvcrt.dll)
// 79,872 Bytes VC15; x64; C Std. Lib. Linkage
//#define TCLib
#ifndef   UNICODE         
    #define   UNICODE     
#endif                    
#ifndef   _UNICODE        
    #define   _UNICODE    
#endif                    
#include <windows.h>      
#ifdef TCLib   
   #include "string.h"           
   #include "stdio.h"  
   extern "C" int _fltused = 1;   
#else                     
   #include <stdio.h> 
   #include <string.h>  
#endif
#include "XLFunctions.h"


int main()
{
 const CLSID  CLSID_XLApplication = {0x00024500,0x0000,0x0000,{0xC0,0x00,0x00,0x00,0x00,0x00,0x00,0x46}};
 const IID    IID_Application     = {0x000208D5,0x0000,0x0000,{0xC0,0x00,0x00,0x00,0x00,0x00,0x00,0x46}};
 IDispatch*   pXLApp              = NULL;
 IDispatch*   pXLWorkbooks        = NULL;
 IDispatch*   pXLWorkbook         = NULL;
 IDispatch*   pXLWorksheets       = NULL;
 IDispatch*   pXLWorksheet        = NULL;
 DISPPARAMS   NoArgs              = {NULL,NULL,0,0};
 wchar_t      szWorkBookPath[]    = L"C:\\Code\\CodeBlks\\XL_Dispatch\\XL19\\Book1.xls"; // << Fix For Your Path!!!
 wchar_t      szSheet[]           = L"Sheet1";
 SYSTEMTIME   st;
 wchar_t      szNumber[4];
 wchar_t      szRange[4]; 
 wchar_t      szCell[64];
 wchar_t      szMonth[4];
 wchar_t      szDay[4];
 wchar_t      szYear[8];
 wchar_t      szDate[16];
 VARIANT      vReturn; 
 HRESULT      hr;
 LCID         lcid;

 CoInitialize(NULL);
 hr=CoCreateInstance(CLSID_XLApplication, NULL, CLSCTX_LOCAL_SERVER, IID_Application, (void**)&pXLApp); // Returns in last [out] parameter pointer to app object
 if(SUCCEEDED(hr))  
 {
    lcid=GetUserDefaultLCID();
    SetVisible(pXLApp,lcid);  
    pXLWorkbooks=GetDispatchObject(pXLApp,572,DISPATCH_PROPERTYGET,lcid);
    if(pXLWorkbooks)
    {
       pXLWorkbook=OpenXLWorkBook(pXLWorkbooks,lcid,szWorkBookPath);
       if(pXLWorkbook)
       {
          pXLWorksheets=GetDispatchObject(pXLWorkbook,494,DISPATCH_PROPERTYGET,lcid);
          if(pXLWorksheets)
          {
             pXLWorksheet=SelectWorkSheet(pXLWorksheets,lcid,szSheet);
             if(pXLWorksheet)
             {
                for(size_t i=0; i<4; i++)                                  // GetCell() and GetXLCell() are the two functions from XLFunctions.cpp
                {                                                          // that retrieve *.xls data.  Their use is fairly simple.  Basically,
                    szRange[0]=65+i;                                       // you just pass in an IDispatch* to the Excel SpreadSheet previously
                    szRange[1]=L'\0';                                      // obtained, and a character string such as A1, B2, C3, etc, for the cell
                    swprintf(szNumber,L"%u",1);                            // for which you want the cell contents retrieved.  The only thing a bit
                    wcscat(szRange,szNumber);                              // wierd is the data is returned in a VARIANT in the last parameter of the
                    GetXLCell(pXLWorksheet,lcid,szRange,szCell,64);        // function call in GetCell().  In GetXLCell() the cell contents in string
                    wprintf(L"%s\t",szCell);                               // form is returned in the next to last parameter, which is an [out] para-
                }                                                          // meter.
                printf("\n");
                for(size_t i=2; i<=5; i++) // data in rows 2 - 5           // Most data is returned as a C/C++ double (VARIANT.vt==VT_R8), a Windows
                {                                                          // BSTR (VARIANT.vt==VT_BSTR), or a Windows date (VARIANT.vt==VT_DATE); and
                    for(size_t j=65; j<=68; j++)  // 65=A, 66=B, etc.      // a VT_DATE is actually a double.  In many instances you need to test the
                    {                                                      // VARIANT.vt to find out in what form the data was returned to you, because
                        szRange[0]=j;     // A1, B1, C1, D1, etc.          // the user can right click on a cell and change the formatting.  
                        szRange[1]=L'\0'; // A2, B2, C2, D2, etc.
                        swprintf(szNumber,L"%u",i);                        // Note that VariantClear() should be called after each access to prevent
                        wcscat(szRange,szNumber);                          // memory leaks.  In this app its not an issue but its something to keep
                        switch(j)                                          // in mind.  Ask if you want further info.
                        {
                          case 65:  // 'A'                                 // Note I removed error checking on all my calls to GetCell() and GetXLCell().
                            hr=GetCell(pXLWorksheet,lcid,szRange,vReturn); // You probably shouldn't do this in a real app.  They return an HRESULT
                            printf("%-4.0f\t",vReturn.dblVal);             // and I'd recommend you always test this with the SUCCEEDED(hr) macro.                  
                            VariantClear(&vReturn);
                            break;
                          case 66:  // 'B'
                            GetCell(pXLWorksheet,lcid,szRange,vReturn);
                            printf("%10.4f\t",vReturn.dblVal);                               
                            VariantClear(&vReturn);
                            break;
                          case 67:  // 'C'
                            GetCell(pXLWorksheet,lcid,szRange,vReturn);
                            memset(&st,0,sizeof(st));
                            VariantTimeToSystemTime(vReturn.dblVal,&st); 
                            swprintf(szMonth,L"%d",st.wMonth);
                            swprintf(szDay,L"%d",st.wDay);
                            swprintf(szYear,L"%d",st.wYear);
                            wcscpy(szDate,szMonth);
                            wcscat(szDate,L"/");
                            wcscat(szDate,szDay);
                            wcscat(szDate,L"/");
                            wcscat(szDate,szYear);
                            wprintf(L"%s\t",szDate);                              
                            VariantClear(&vReturn);
                            break;
                          case 68:  // 'D'
                            GetXLCell(pXLWorksheet,lcid,szRange,szCell,64); 
                            wprintf(L"%s\t",szCell);
                            VariantClear(&vReturn);
                            break;
                        }
                    } 
                    printf("\n");
                }
                pXLWorksheet->Release();
             }
             pXLWorksheets->Release();
          }
          pXLWorkbook->Release();
       }
       pXLWorkbooks->Release();
    }
    getchar();
    pXLApp->Invoke(0x0000012e,IID_NULL,LOCALE_USER_DEFAULT,DISPATCH_METHOD,&NoArgs,NULL,NULL,NULL); // pXLApp->Quit() 0x12E
    pXLApp->Release();
 }
 CoUninitialize();

 return 0;
}


/*
Id      Float_Point     Date_Field      Text_Field
1           3.1416      11/15/1952      My Birthday
2           1.2346      6/30/1969       Walk On Moon?
3          15.1234      1/1/2006        Some String
4           0.5432      4/1/2006        April Fools Day!
*/
freddie1 (1838)
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
freddie1 (1838)
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 <stdio.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;
}
freddie1 (1838)
Might be to C ish for some. Next post I'll incorporate the Std. Lib's std::wstring class in it....
freddie1 (1838)
Here's the code C++ 'a fied' using the much beloved by C++ programmers <string> and <iostream> stuff. Also, I used a different architecture for error handling which is considerably more manageable....

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
 
// g++ Main.cpp XLFunctions.cpp -oXL20_GCC.exe -m64 -lole32 -loleaut32 -luuid -Os -s
// cl Main.cpp XLFunctions.cpp /O1 /Os /MT /EHsc /FeXL20_VC.exe kernel32.lib ole32.lib oleaut32.lib uuid.lib
// 638,976 Bytes Mingw 4.8; x64. (Links with msvcrt.dll)
// 169,472 Bytes VC15; x64; C Std. Lib. Linkage
#ifndef   UNICODE         
    #define   UNICODE     
#endif                    
#ifndef   _UNICODE        
    #define   _UNICODE    
#endif                    
#include <windows.h>      
#include <stdio.h> 
#include <iostream>
#include <string>  
#include "XLFunctions.h"


int main()
{
 const CLSID   CLSID_XLApplication = {0x00024500,0x0000,0x0000,{0xC0,0x00,0x00,0x00,0x00,0x00,0x00,0x46}};
 const IID     IID_Application     = {0x000208D5,0x0000,0x0000,{0xC0,0x00,0x00,0x00,0x00,0x00,0x00,0x46}};
 IDispatch*    pXLApp              = NULL;
 IDispatch*    pXLWorkbooks        = NULL;
 IDispatch*    pXLWorkbook         = NULL;
 IDispatch*    pXLWorksheets       = NULL;
 IDispatch*    pXLWorksheet        = NULL;
 DISPPARAMS    NoArgs              = {NULL,NULL,0,0};
 std::wstring  strWorkBookPath     = L"C:\\Code\\CodeBlks\\XL_Dispatch\\XL20\\Book1.xls"; // << Fix For Your Path!!!
 std::wstring  strSheet            = L"Sheet1";
 SYSTEMTIME    st;
 std::wstring  strRange; 
 std::wstring  strCell;
 std::wstring  strDate;
 wchar_t       szBuffer[8];
 VARIANT       vReturn; 
 HRESULT       hr;
 LCID          lcid;

 CoInitialize(NULL);
 hr=CoCreateInstance(CLSID_XLApplication, NULL, CLSCTX_LOCAL_SERVER, IID_Application, (void**)&pXLApp); 
 if(FAILED(hr))
    goto Sad_Ending;  
 lcid=GetUserDefaultLCID();
 SetVisible(pXLApp,lcid);  
 pXLWorkbooks=GetDispatchObject(pXLApp,572,DISPATCH_PROPERTYGET,lcid);
 if(!pXLWorkbooks)
    goto Sad_Ending;
 pXLWorkbook=OpenXLWorkBook(pXLWorkbooks,lcid,strWorkBookPath);  
 if(!pXLWorkbook)
    goto Sad_Ending;
 pXLWorksheets=GetDispatchObject(pXLWorkbook,494,DISPATCH_PROPERTYGET,lcid);
 if(!pXLWorksheets)
    goto Sad_Ending;
 pXLWorksheet=SelectWorkSheet(pXLWorksheets,lcid,strSheet); 
 if(!pXLWorksheet)
    goto Sad_Ending;
 for(size_t i=0; i<4; i++)                                  
 {      
     strRange=(wchar_t)(65+i);
     strRange=strRange+L"1";
     strCell=GetXLCell(pXLWorksheet,lcid,strRange);        
     std::wcout << strCell << L'\t';     
 }                                                          
 std::wcout << L'\n';
 for(size_t i=2; i<=5; i++) // data in rows 2 - 5           
 {                                                          
     for(size_t j=65; j<=68; j++)  // 65=A, 66=B, etc.      
     {                                                      
         strRange=(wchar_t)j; 
         swprintf(szBuffer,L"%d",i);                     
         strRange=strRange+szBuffer; 
         switch(j)                                          
         {
           case 65:  // 'A'  
             GetCell(pXLWorksheet,lcid,strRange,vReturn); 
             std::wcout << vReturn.dblVal << L'\t';             
             VariantClear(&vReturn);
             break;
           case 66:  // 'B'
             GetCell(pXLWorksheet,lcid,strRange,vReturn);
             std::wcout << vReturn.dblVal << L"\t\t";  
             VariantClear(&vReturn);
             break;
           case 67:  // 'C'
             GetCell(pXLWorksheet,lcid,strRange,vReturn);
             memset(&st,0,sizeof(st));
             VariantTimeToSystemTime(vReturn.dblVal,&st); 
             swprintf(szBuffer,L"%d",st.wMonth);
             strDate=szBuffer;
             strDate=strDate+L"/";
             swprintf(szBuffer,L"%d",st.wDay);
             strDate=strDate+szBuffer;
             strDate=strDate+L"/";
             swprintf(szBuffer,L"%d",st.wYear);
             strDate=strDate+szBuffer;
             std::wcout << strDate << L'\t';
             VariantClear(&vReturn);
             break;
           case 68:  // 'D'
             strCell=GetXLCell(pXLWorksheet,lcid,strRange); 
             std::wcout << strCell << L'\t';
             VariantClear(&vReturn);
             break;
         }
     }
     //printf("\n");
     std::wcout << L'\n';
 }     
 getchar();
  
 Sad_Ending:
 if(pXLWorksheet)
    pXLWorksheet->Release();
 if(pXLWorksheets)
    pXLWorksheets->Release();
 if(pXLWorkbook)
    pXLWorkbook->Release();
 if(pXLWorkbooks)
    pXLWorkbooks->Release();
 if(pXLApp)
 {
    pXLApp->Invoke(0x0000012e,IID_NULL,LOCALE_USER_DEFAULT,DISPATCH_METHOD,&NoArgs,NULL,NULL,NULL); // pXLApp->Quit() 0x12E  
    pXLApp->Release();
 }   
 CoUninitialize();

 return 0;
}



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);
std::wstring GetXLCell(IDispatch* pXLWorksheet, LCID& lcid, std::wstring& strRange);
HRESULT GetCell(IDispatch* pXLSheet, LCID lcid, std::wstring& strRange, VARIANT& pVt);
IDispatch* SelectWorkSheet(IDispatch* pXLWorksheets, LCID& lcid, std::wstring& strSheet);
IDispatch* OpenXLWorkBook(IDispatch* pXLWorkbooks, LCID& lcid, std::wstring& strWorkBookPath);
IDispatch* GetDispatchObject(IDispatch* pCallerObject, DISPID dispid, WORD wFlags, LCID lcid);

#endif


freddie1 (1838)
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
 
// XLFunctions.cpp
#ifndef   UNICODE
   #define   UNICODE
#endif
   #ifndef   _UNICODE
#define   _UNICODE
#endif
#include <windows.h>
#include <stdio.h>
#include <string>
#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;
}


std::wstring GetXLCell(IDispatch* pXLWorksheet, LCID& lcid, std::wstring& strRange)
{
 DISPPARAMS      NoArgs         = {NULL,NULL,0,0};
 IDispatch*      pXLRange       = NULL;
 VARIANT         vArgArray[1];
 VARIANT         vResult;
 DISPPARAMS      DispParams;
 HRESULT         hr;
 std::wstring    strCell;

 VariantInit(&vResult);
 vArgArray[0].vt                = VT_BSTR,
 vArgArray[0].bstrVal           = SysAllocString(strRange.c_str());
 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 strCell;
 pXLRange=vResult.pdispVal;

 VariantClear(&vArgArray[0]);
 hr=pXLRange->Invoke(6,IID_NULL,lcid,DISPATCH_PROPERTYGET,&NoArgs,&vResult,NULL,NULL);
 if(SUCCEEDED(hr))
 {
    if(vResult.vt==VT_BSTR)
       strCell=vResult.bstrVal;
    else
       strCell=L"";
    VariantClear(&vResult);
 }
 pXLRange->Release();

 return strCell;
}


HRESULT GetCell(IDispatch* pXLSheet, LCID lcid, std::wstring& strRange, 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(strRange.c_str());
 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;

 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, std::wstring& strSheet)
{
 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(strSheet.c_str());
 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);

 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, std::wstring& strWorkBookPath)
{
 VARIANT         vResult;
 VARIANT         vArgArray[1];
 DISPPARAMS      DispParams;
 DISPID          dispidNamed;
 HRESULT         hr;

 VariantInit(&vResult);         // Call Workbooks::Open() - 682  >> Gets pXLWorkbook
 vArgArray[0].vt                = VT_BSTR;
 vArgArray[0].bstrVal           = SysAllocString(strWorkBookPath.c_str());
 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;
}
freddie1 (1838)
Don't know if anyone noticed, but that one using Mingw is 639 k thanks to the much beloved recent additions to the C++ standards as well as especial thanks to std::wstring and std::cout. Lowly and much scorned basic can do it in 27,136 bytes.....

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
 
#Compile  Exe  ' 27,136 Bytes x86 PowerBASIC
#Dim      All

Type SYSTEMTIME
   wYear         As Word
   wMonth        As Word
   wDayOfWeek    As Word
   wDay          As Word
   wHour         As Word
   wMinute       As Word
   wSecond       As Word
   wMilliseconds As Word
End Type

Declare Function VariantTimeToSystemTime Import "OLEAUT32.DLL" Alias "VariantTimeToSystemTime" _
( _
  Byval vtime        As Double, _
  Byref lpSystemTime As SYSTEMTIME _
) As Long

Function PBMain() As Long
  Local pApp, pWorkBook, pWorkSheet As IDispatch
  Local vVnt1,vVnt2 As Variant
  Local strDate As WString
  Local st As SYSTEMTIME
  Register i As Long
  Register j As Long

  pApp = NewCom "Excel.Application"
  If IsObject(pApp) Then
     vVnt1 = 1
     Object Let pApp.Visible=vVnt1
     vVnt1 = "C:\Code\CodeBlks\XL_Dispatch\XL22\Book1.xls"
     Object Call pApp.WorkBooks.Open(vVnt1) To pWorkBook
     If IsObject(pWorkBook) Then
        vVnt1 = "Sheet1"
        Object Call pWorkbook.Sheets(vVnt1).Select
        Object Get pWorkbook.ActiveSheet To vVnt1
        Set pWorkSheet = vVnt1
        If IsObject(pWorkSheet) Then
           For i=65 To 68
             vVnt1 = Chr$(i) & "1"
             Object Get pWorkSheet.Range(vVnt1).Value To vVnt2
             Console.Print Variant$(vVnt2);,
           Next i
           Console.Print
           For i=2 To 5
             For j=65 To 68
               vVnt1=Chr$(j)+Trim$(Str$(i))
               Object Get pWorkSheet.Range(vVnt1).Value To vVnt2
               Select Case As Long j
                 Case 65
                   Console.Print Variant#(vVnt2);,
                 Case 66
                   Console.Print Variant#(vVnt2);,
                 Case 67
                   VariantTimeToSystemTime(Variant#(vVnt2), st)
                   strDate = Trim$(Str$(st.wMonth)) + "/" + Trim$(Str$(st.wDay)) + "/" + Trim$(Str$(st.wYear))
                   Console.Print strDate;,
                 Case 68
                   Console.Print Variant$(vVnt2)
               End Select
             Next j
           Next i
           Console.WaitKey$
        End If
        Object Call pWorkbook.Close()
     End If
     Object Call pApp.Quit()
  End If

  PBMain=0
End Function


' Id            Float_Point   Date_Field    Text_Field
'  1             3.14159      11/15/1952    My Birthday
'  2             1.23456      6/30/1969     Walk On Moon?
'  3             15.1234      1/1/2006      Some String
'  4             .54321       4/1/2006      April Fools Day!


But I've a real treat in store for those who like all the complexities removed. I decided to make more 'wrappers' around my difficult COM code to make it easier. I'm fully aware of what people want. They want something like so...

1
2
3
4
 
XLStart();                                  // Start Excel
OpenXLWorkBook();                  // Open *.xls WorkBook    
XLOpenWorkSheet(szSheet);   // Select/Open Specific WorkSheet
 GetXLCell(...);                          // Retrieve Cell Data



Right???

So how's this...

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;
}


That compiles to 6,144 bytes using my custom C Runtime (TCLib) in x64. Here is the new XLFunctions.h file, which you need to use for the above...

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

HRESULT SetVisible(IDispatch* pObject);
HRESULT GetXLCell(IDispatch* pXLWorksheet, wchar_t* pszRange, wchar_t* pszCell, size_t iBufferLength);
HRESULT GetCell(IDispatch* pXLSheet, wchar_t* pszRange, VARIANT& pVt);
IDispatch* SelectWorkSheet(IDispatch* pXLWorksheets, LCID& lcid, wchar_t* pszSheet);
IDispatch* OpenXLWorkBook(IDispatch* pXLWorkbooks, wchar_t* pszWorkBookPath);
IDispatch* GetDispatchObject(IDispatch* pCallerObject, DISPID dispid, WORD wFlags, LCID lcid);
IDispatch* XLStart(bool blnVisible, IDispatch** pXLWorkBooks);
IDispatch* XLOpenWorkSheet(IDispatch* pXLWorkBook, wchar_t* pSheet);
HRESULT XLQuit(IDispatch* pXLApp);

#endif


Next post is the new XLFunctions.cpp file...



freddie1 (1838)
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
 
#ifndef   UNICODE
   #define   UNICODE
#endif
   #ifndef   _UNICODE
#define   _UNICODE
#endif
#include <windows.h>
#include <stdio.h>
#include "XLFunctions.h"


HRESULT SetVisible(IDispatch* pObject)            
{                                                 
 VARIANT         vArgArray[1];                    
 DISPPARAMS      DispParams;                      
 DISPID          dispidNamed;                     
 VARIANT         vResult;                         
 HRESULT         hr;                              
 LCID            lcid;                            
 
 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;               
 lcid                          = GetUserDefaultLCID();
 VariantInit(&vResult);
 hr=pObject->Invoke(0x0000022e,IID_NULL,lcid,DISPATCH_PROPERTYPUT,&DispParams,&vResult,NULL,NULL);

 return hr;
}


HRESULT GetXLCell(IDispatch* pXLWorksheet, 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;                                   
 LCID            lcid;                                 
                                                       
 VariantInit(&vResult);                                
 lcid                           = GetUserDefaultLCID();
 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, wchar_t* pszRange, VARIANT& pVt)
{
 DISPPARAMS      NoArgs         = {NULL,NULL,0,0};           
 IDispatch*      pXLRange       = NULL;                      
 VARIANT         vArgArray[1];                               
 VARIANT         vResult;                                    
 DISPPARAMS      DispParams;                                 
 HRESULT         hr;                                         
 LCID            lcid;                                       
                                                             
 VariantInit(&vResult);                                      
 vArgArray[0].vt                = VT_BSTR,                   
 vArgArray[0].bstrVal           = SysAllocString(pszRange);  
 DispParams.rgvarg              = vArgArray;                 
 DispParams.rgdispidNamedArgs   = 0;                         
 DispParams.cArgs               = 1;                         
 DispParams.cNamedArgs          = 0;                         
 lcid                           = GetUserDefaultLCID();
 hr=pXLSheet->Invoke(0xC5,IID_NULL,lcid,DISPATCH_PROPERTYGET,&DispParams,&vResult,NULL,NULL);
 if(FAILED(hr))VariantClear(&vResult);
    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;

 // Member Get Item <170> (In Index As Variant<0>) As IDispatch  >> Gets pXLWorksheet
 // [id(0x000000aa), propget, helpcontext(0x000100aa)] IDispatch* Item([in] VARIANT Index);
 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, wchar_t* pszWorkBookPath)
{
 VARIANT         vResult;
 VARIANT         vArgArray[1];
 DISPPARAMS      DispParams;
 DISPID          dispidNamed;
 LCID            lcid;
 HRESULT         hr;

 VariantInit(&vResult);         // Call Workbooks::Open() - 682  >> Gets pXLWorkbook
 vArgArray[0].vt                = VT_BSTR;
 vArgArray[0].bstrVal           = SysAllocString(pszWorkBookPath);
 DispParams.rgvarg              = vArgArray;
 DispParams.rgdispidNamedArgs   = &dispidNamed;
 DispParams.cArgs               = 1;
 DispParams.cNamedArgs          = 0;
 lcid                           = GetUserDefaultLCID();
 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;
}


continued...
Last edited on
freddie1 (1838)
continued...

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
 
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;
}


IDispatch* XLStart(bool blnVisible, IDispatch** pXLWorkBooks)
{
 const CLSID  CLSID_XLApplication = {0x00024500,0x0000,0x0000,{0xC0,0x00,0x00,0x00,0x00,0x00,0x00,0x46}};
 const IID    IID_Application     = {0x000208D5,0x0000,0x0000,{0xC0,0x00,0x00,0x00,0x00,0x00,0x00,0x46}};
 IDispatch*   pXLApp              = NULL;
 LCID         lcid;
 HRESULT      hr;
 
 hr=CoCreateInstance(CLSID_XLApplication, NULL, CLSCTX_LOCAL_SERVER, IID_Application, (void**)&pXLApp); // Returns in last [out] parameter pointer to app object
 if(SUCCEEDED(hr))  // macro that tests HRESULT, which is a bit field entity, for S_OK, i.e., success
 {
    lcid=GetUserDefaultLCID();
    if(blnVisible)
       SetVisible(pXLApp);
    *pXLWorkBooks=GetDispatchObject(pXLApp,572,DISPATCH_PROPERTYGET,lcid);  // Wrapper function in XLFunctions.cpp will return IDispatch pointer tp WorkBooks Collection
    if(pXLWorkBooks)
       return pXLApp;
    else
    {
       pXLApp->Release();
       return NULL;
    }   
 }
 
 return NULL;
} 


IDispatch* XLOpenWorkSheet(IDispatch* pXLWorkBook, wchar_t* pSheet)
{
 IDispatch*   pXLWorkSheets       = NULL;
 IDispatch*   pXLWorkSheet        = NULL;
 LCID         lcid;
 
 lcid=GetUserDefaultLCID();
 pXLWorkSheets=GetDispatchObject(pXLWorkBook,494,DISPATCH_PROPERTYGET,lcid); 
 pXLWorkSheet=SelectWorkSheet(pXLWorkSheets,lcid,pSheet);
 pXLWorkSheets->Release();

 return pXLWorkSheet; 
}


HRESULT XLQuit(IDispatch* pXLApp)
{
 DISPPARAMS   NoArgs = {NULL,NULL,0,0};
 return pXLApp->Invoke(0x0000012e,IID_NULL,LOCALE_USER_DEFAULT,DISPATCH_METHOD,&NoArgs,NULL,NULL,NULL); // pXLApp->Quit() 0x12E
}
Topic archived. No new replies allowed.
Pages: 12