Access to an excel file via C++

Pages: 1234
freddie1 (1838)

was asking about how you generated the excel.h files because I've seen the code there and it seems a little more self explanatory. An example below:


Yes, I hope my other comments explain the issue there for you. The whole reason I was working on that is because it is more self-explanatory. Its actually fairly easy. And I couldn't get the IDispatch stuff working. Actually, my converter code I wrote about isn't perfect. Somehow or other I'm not handling the optional parameters issue perfectly. Most of those parameters for pWorkBooks->Open() are optional, but since my declare that my code auto-generated didin't recognize that, I had to fill all those places in with nulls to get it to compile. But it did work then.
Last edited on
freddie1 (1838)
Well, its been a battle, but I finally got my code working so as to be able to Select a worksheet out of a workbook. Likely couldn't have done it without CBurn11's kind help. What I've got here is quite a bit different from CBurn11's. See, here's the deal. Having to call both IDispatch::GetIDsOfNames() to get the dispatch IDs, then IDispatch::Invoke() to make the call, is a lot of overhead. What PowerBASIC does if dispatch IDs are available beforehand - and they always will be if you have the typelib or *.idl file, is that it looks up the dispids at compile time if you supply the header/include. Then at run time all it needs do is the Invoke(). So that's what my example does here. To test it just fix the path to a more or less blank Workbook Book1.xls file with a couple sheets in it like Sheet1, Sheet2, etc. It Activates Sheet2. You need to link with ole32.lib, oleauto32.lib, and uuid.lib. No warnings with CodeBlocks, anyway.

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
 
#include <windows.h>
#include <cstdio>
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}};


int main()
{
 DISPPARAMS        NoArgs         = {NULL,NULL,0,0};
 IDispatch*        pXLApp         = NULL;
 IDispatch*        pXLWorkbooks   = NULL;
 IDispatch*        pXLWorkbook    = NULL;
 IDispatch*        pXLWorksheets  = NULL;
 IDispatch*        pXLWorksheet   = NULL;
 DISPPARAMS        DispParams;
 DISPID            dispidNamed;
 VARIANT           vArgArray[1];
 VARIANT           vResult;
 HRESULT           hr;
 LCID              lcid;

 CoInitialize(NULL);
 hr=CoCreateInstance(CLSID_XLApplication, NULL, CLSCTX_LOCAL_SERVER, IID_Application, (void**)&pXLApp);
 if(FAILED(hr))
    goto Sad_Ending;
 printf("CoCreateInstance() Succeeded!\n");
 printf("pXLApp        = %u\n",(unsigned)pXLApp);
 lcid=GetUserDefaultLCID();

 // Set _Application::Visible
 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=pXLApp->Invoke(558,IID_NULL,lcid,DISPATCH_PROPERTYPUT,&DispParams,&vResult,NULL,NULL);

 //572 Get _Application::Workbooks  >> Gets pXLWorkbooks
 VariantInit(&vResult);
 hr=pXLApp->Invoke(572,IID_NULL,lcid,DISPATCH_PROPERTYGET,&NoArgs,&vResult,NULL,NULL);
 if(FAILED(hr))
    goto Sad_Ending;
 pXLWorkbooks=vResult.pdispVal;
 printf("pXLWorkbooks  = %d\n",(int)pXLWorkbooks);

 // Call Workbooks::Open() - 682  >> Gets pXLWorkbook
 VariantInit(&vResult);
 vArgArray[0].vt              = VT_BSTR;
 vArgArray[0].bstrVal         = SysAllocString(L"C:\\Code\\CodeBlks\\XL_Dispatch\\Book1.xls");
 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);
 if(FAILED(hr))
    goto Sad_Ending;
 SysFreeString(vArgArray[0].bstrVal);
 pXLWorkbook=vResult.pdispVal;
 printf("pXLWorkbook   = %d\n",(int)pXLWorkbook);

 // Call Workbook::Worksheets()  //Member Get Worksheets  <494> () As XLSheets  >> Gets pXLWorksheets
 // Call Workbook::Sheets()      //Member Get Sheets      <485> () As XLSheets
 //[id(0x000001e5), propget, helpcontext(0x000101e5)] HRESULT Sheets([out, retval] Sheets** RHS);
 //[id(0x000001ee), propget, helpcontext(0x000101ee)] HRESULT Worksheets([out, retval] Sheets** RHS);
 VariantInit(&vResult);
 hr=pXLWorkbook->Invoke(494,IID_NULL,lcid,DISPATCH_PROPERTYGET,&NoArgs,&vResult,NULL,NULL);
 if(FAILED(hr))
    goto Sad_Ending;
 pXLWorksheets=vResult.pdispVal;
 printf("pXLWorksheets = %u\n",(unsigned)pXLWorksheets);

 // 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(L"Sheet2");
 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))
    goto Sad_Ending;
 pXLWorksheet=vResult.pdispVal;
 printf("pXLWorksheet  = %u\n",(unsigned)pXLWorksheet);
 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);
 if(FAILED(hr))
    goto Sad_Ending;
 getchar();
 pXLApp->Invoke(0x0000012e,IID_NULL,LOCALE_USER_DEFAULT,DISPATCH_METHOD,&NoArgs,NULL,NULL,NULL);

 Sad_Ending: // Workbooks::Close() 277
 if(pXLWorksheet)
    pXLWorksheet->Release();
 if(pXLWorksheets)
    pXLWorksheets->Release();
 if(pXLWorkbook)
    pXLWorkbook->Release();
 if(pXLWorkbooks)
    pXLWorkbooks->Release();
 if(pXLApp)
    pXLApp->Release();
 CoUninitialize();
 getchar();

 return 0;
}


oops! Ought to be a Couninitialize() there at the end :)
Last edited on
cburn11 (16)
For context, this 6 statement vbscript file does the same:

excel.vbs
1
2
3
4
5
6
7
8
9
 
dim app
set app = CreateObject("Excel.Application")

app.Visible = True

dim workbook
set workbook = app.Workbooks.Open("C:\temp\Book1.xlsx")

workbook.Sheets("Sheet2").Select
freddie1 (1838)
Good one CBurn! Let's make these hard core C++'ers feel guilty! I'll jump on the band wagon and add this one in PowerBASIC that does exactly the same thing as the complete program just after this post that completes it by outputting to the console cell A1 of Sheet2...

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\CodeBlks\XL_Dispatch\Book1.xls"
  Object Call pDisp.WorkBooks.Open(vVnt1) To pWorkBook
  vVnt1="Sheet2"
  Object Call pWorkbook.Sheets(vVnt1).Select
  Object Get pWorkbook.ActiveSheet To vVnt2
  Set pWorkSheet = vVnt2
  vVnt1="A1"
  Object Get pWorkSheet.Range(vVnt1).Value To vVnt2
  Con.Print Variant$(vVnt2)
  Con.WaitKey$
  Object Call pWorkbook.Close() : Object Call pDisp.Quit()

  PBMain=0
End Function


The above prints to the console whatever's in A1 of sheet2. It compiles to 22K in PowerBASIC compared to 8.5K for my C++ one below ...

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
 
#include <windows.h>
#include <cstdio>
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}};


int main()
{
 DISPPARAMS      NoArgs         = {NULL,NULL,0,0};
 IDispatch*      pXLApp         = NULL;
 IDispatch*      pXLWorkbooks   = NULL;
 IDispatch*      pXLWorkbook    = NULL;
 IDispatch*      pXLWorksheets  = NULL;
 IDispatch*      pXLWorksheet   = NULL;
 IDispatch*      pXLRange       = NULL;
 DISPPARAMS      DispParams;
 DISPID          dispidNamed;
 VARIANT         vArgArray[1];
 VARIANT         vResult;
 HRESULT         hr;
 LCID            lcid;

 CoInitialize(NULL);
 hr=CoCreateInstance(CLSID_XLApplication, NULL, CLSCTX_LOCAL_SERVER, IID_Application, (void**)&pXLApp);
 if(FAILED(hr))
    goto Sad_Ending;
 printf("CoCreateInstance() Succeeded!\n");
 printf("pXLApp          = %u\n",(unsigned)pXLApp);
 lcid=GetUserDefaultLCID();

 // Set _Application::Visible
 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=pXLApp->Invoke(558,IID_NULL,lcid,DISPATCH_PROPERTYPUT,&DispParams,&vResult,NULL,NULL);

 //572 Get _Application::Workbooks  >> Gets pXLWorkbooks
 VariantInit(&vResult);
 hr=pXLApp->Invoke(572,IID_NULL,lcid,DISPATCH_PROPERTYGET,&NoArgs,&vResult,NULL,NULL);
 if(FAILED(hr))
    goto Sad_Ending;
 pXLWorkbooks=vResult.pdispVal;
 printf("pXLWorkbooks    = %d\n",(int)pXLWorkbooks);

 // Call Workbooks::Open() - 682  >> Gets pXLWorkbook
 VariantInit(&vResult);
 vArgArray[0].vt                = VT_BSTR;
 vArgArray[0].bstrVal           = SysAllocString(L"C:\\Code\\CodeBlks\\XL_Dispatch\\Book1.xls");
 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);
 if(FAILED(hr))
    goto Sad_Ending;
 SysFreeString(vArgArray[0].bstrVal);
 pXLWorkbook=vResult.pdispVal;
 printf("pXLWorkbook     = %d\n",(int)pXLWorkbook);

 // Call Workbook::Worksheets()  //Member Get Worksheets  <494> () As XLSheets  >> Gets pXLWorksheets
 // Call Workbook::Sheets()      //Member Get Sheets      <485> () As XLSheets
 //[id(0x000001e5), propget, helpcontext(0x000101e5)] HRESULT Sheets([out, retval] Sheets** RHS);
 //[id(0x000001ee), propget, helpcontext(0x000101ee)] HRESULT Worksheets([out, retval] Sheets** RHS);
 VariantInit(&vResult);
 hr=pXLWorkbook->Invoke(494,IID_NULL,lcid,DISPATCH_PROPERTYGET,&NoArgs,&vResult,NULL,NULL);
 if(FAILED(hr))
    goto Sad_Ending;
 pXLWorksheets=vResult.pdispVal;
 printf("pXLWorksheets   = %u\n",(unsigned)pXLWorksheets);

 // 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(L"Sheet2");
 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))
    goto Sad_Ending;
 pXLWorksheet=vResult.pdispVal;
 printf("pXLWorksheet    = %u\n",(unsigned)pXLWorksheet);
 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);
 if(FAILED(hr))
    goto Sad_Ending;

 //Member Get Range <197, 0xc5> (In Cell1 As Variant<0>, Opt In Cell2 As Variant<1>) As XLRange
 //[id(0x000000c5), propget, helpcontext(0x000100c5)] HRESULT Range([in] VARIANT Cell1, [in, optional] VARIANT Cell2, [out, retval] Range** RHS);
 VariantInit(&vResult);
 vArgArray[0].vt                = VT_BSTR,
 vArgArray[0].bstrVal           = SysAllocString(L"A1");
 DispParams.rgvarg              = vArgArray;
 DispParams.rgdispidNamedArgs   = 0;
 DispParams.cArgs               = 1;  // Try to get Range
 DispParams.cNamedArgs          = 0;  // Invoke _Worksheet::Range("A1")  << returns IDispatch** to dispinterface Range
 hr=pXLWorksheet->Invoke(197,IID_NULL,lcid,DISPATCH_PROPERTYGET,&DispParams,&vResult,NULL,NULL);
 if(FAILED(hr))
 {
    printf("Failed To Get Range!\n");
    goto Sad_Ending;
 }
 pXLRange=vResult.pdispVal;
 printf("pXLRange        = %u\n",(unsigned)pXLRange);

 //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))
 {
    wprintf(L"vResult.bstrVal = %s\n",vResult.bstrVal);
    VariantClear(&vResult);
 }
 getchar();
 pXLApp->Invoke(0x0000012e,IID_NULL,LOCALE_USER_DEFAULT,DISPATCH_METHOD,&NoArgs,NULL,NULL,NULL);

 Sad_Ending: // Workbooks::Close() 277
 if(pXLRange)
    pXLRange->Release();
 if(pXLWorksheet)
    pXLWorksheet->Release();
 if(pXLWorksheets)
    pXLWorksheets->Release();
 if(pXLWorkbook)
    pXLWorkbook->Release();
 if(pXLWorkbooks)
    pXLWorkbooks->Release();
 if(pXLApp)
    pXLApp->Release();
 getchar();
 CoUninitialize();

 return 0;
}

/*
CoCreateInstance() Succeeded!
pXLApp          = 2595372
pXLWorkbooks    = 2478556
pXLWorkbook     = 2592940
pXLWorksheets   = 2588260
pXLWorksheet    = 2583332
pXLRange        = 2589684
vResult.bstrVal = This Is Sheet #2
*/


Thing is though, PowerBASIC includes awesome string handling as part of the compiler, i.e., its a built in datatype bases on Window's BSTR. Adding that to the C++ code above via a String class would bloat it pretty good.

I got you explanation of the Named Argument function CBurn. Thanks a lot! I put printf statements all over it to figure out what it did. Also had to review VarArg functions cuz I don't use them much (at all, really).

I'm real happy about all this. This was actually about the last thing I didn't know how to do in C++ that was easy in PowerBASIC. Couple months ago I put together for myself a template based multi-dimensional array class, and that's working great for me. So this caps it off!
freddie1 (1838)
By the way, I don't have anything against those wrappers around GetIDsOfNames() and Invoke() you use. Its just that I wanted to know how to do it myself first before adopting them, and of course its always good to see how someone skillfully wraps something like that. And I needed a review of the VarArg stuff too.
Cojones (71)
Freddie, thanks a lot for your help. I think I finally understood how to use the IDL file and Invoke from your last code you posted. I even managed to get the number of sheets in an excel file. :) Now I'm trying to find the names of the sheets but in any case, I couldn't do it without your help. 

1
2
3
4
5
6
7
 
 //[id(0x00000076), propget, helpcontext(0x0002520b)] HRESULT Count([out, retval] long* RHS);
 VariantInit(&vResult);
 hr=pXLWorkbook->Invoke(118,IID_NULL,lcid,DISPATCH_PROPERTYGET,&NoArgs,&vResult,NULL,NULL);
 if(FAILED(hr))
    goto Sad_Ending;
 pXLSheetCount=vResult.pdispVal;
 printf("pXLWorksheets   = %u\n",(unsigned)pXLSheetCount);


Right now I'm trying to figure it out how to find the sheets names.
Last edited on
Cojones (71)
So far I've done this...

1
2
3
4
5
6
7
8
 
//Get number of sheets
 VariantInit(&vResult);
 //[id(0x00000076), propget, helpcontext(0x0002520b)] HRESULT Count([out, retval] long* RHS);
 hr=pXLWorksheets->Invoke(0x00000076,IID_NULL,lcid,DISPATCH_PROPERTYGET,&NoArgs,&vResult,NULL,NULL); 
 if(FAILED(hr))
    goto Sad_Ending;
 pXLSheetCount=vResult.pdispVal;
 printf("pXLWorksheets   = %u\n",(unsigned)pXLSheetCount);


Now I'm trying to get the name of the sheets:
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
 
 for(UINT i = 0; i < (unsigned)pXLSheetCount; i++)
 {
// I still need to select the sheet with index i. Failed so far. :(
	 VariantInit(&vResult);
	 vArgArray[0].vt                = VT_UINT;
	 vArgArray[0].uintVal           = i;
	 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))
		goto Sad_Ending;
	pXLWorksheet=vResult.pdispVal;
	printf("pXLWorksheets   = %u\n",(unsigned)pXLWorksheet);
// end I still need to select the sheet with index i

// This works. It gets the name of the sheet
	//[id(0x0000006e), propget, helpcontext(0x0002a800)] HRESULT Name([out, retval] BSTR* RHS);
	VariantInit(&vResult);
	hr=pXLWorksheet->Invoke(110,IID_NULL,lcid,DISPATCH_PROPERTYGET,&NoArgs,&vResult,NULL,NULL);
	if(FAILED(hr))
	   goto Sad_Ending;
	pXLSheetCount=vResult.pdispVal;
	printf("pXLWorksheets   = %s\n",(char*)vResult.bstrVal);
 }

freddie1 (1838)
Sorry, I've been away for a bit. Did you finally get the Sheet name? I believe name is one of the members of the Worksheets enumeration.

One thing I might caution you about are the BSTRs. Note how I was calling SysFreeString() on them. Every time one is returned to you in any way at all, for example, getting the data from a cell, getting a sheet name, that memory has to be freed. I seem to recall you said your end goal was to get data from a rather large Excel spreadsheet. If you evenyually master this and end up reading data in a large loop, for example, you could rack up quite a toll of leaked memory if all the BSTRs aren't released. Here is a good overview of BSTRs. Its old but good ...

http://ecs.syr.edu/faculty/fawcett/handouts/CSE775/Presentations/BruceMcKinneyPapers/COMstrings.htm

Cojones (71)
Sorry for the delay... :)

I've managed to get the sheets names. :) Below is the code that does that. If you have a better idea, let me know. ;)

Now I need to know how to get the exact cell I want. :) I suppose the code you wrote would work fine if I change the A1 to something else... but from what I get, that's a range. Anyway, your code and your comments allowed me to understand how this works. :) 

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
 
#pragma region Get Sheet Names

#pragma region Get Sheet Count

 //Get number of sheets
 VariantInit(&vResult);
 //[id(0x00000076), propget, helpcontext(0x0002520b)] HRESULT Count([out, retval] long* RHS);
 hr=pXLWorksheets->Invoke(0x00000076,IID_NULL,lcid,DISPATCH_PROPERTYGET,&NoArgs,&vResult,NULL,NULL); 
 if(FAILED(hr))
    goto Sad_Ending;
 pXLSheetCount=vResult.pdispVal;
 printf("pXLWorksheets   = %u\n",(unsigned)pXLSheetCount);
#pragma endregion // end Get Sheet Count


 // Member Get Item <170> (In Index As Variant<0>) As IDispatch  >> Gets pXLWorksheet
 // [id(0x000000aa), propget, helpcontext(0x000100aa)] IDispatch* Item([in] VARIANT Index);

 for(unsigned int i = 1; i <= (unsigned)pXLSheetCount; i++)
 {
	 VariantInit(&vResult);
	 vArgArray[0].vt = VT_UINT;
	 vArgArray[0].uintVal = i;
	 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))
		goto Sad_Ending;
	 pXLWorksheet=vResult.pdispVal;
	 printf("pXLWorksheet    = %u\n",(unsigned)pXLWorksheet);
	 //SysFreeString(vArgArray[0].bstrVal);

	  VariantInit(&vResult);
	 hr=pXLWorksheet->Invoke(0x6e,IID_NULL,lcid,DISPATCH_PROPERTYGET,&NoArgs,&vResult,NULL,NULL);
	 if(FAILED(hr))
		goto Sad_Ending;
	wprintf(L"vResult.bstrVal = %s\n",vResult.bstrVal);
	VariantClear(&vResult);
 }
#pragma endregion // Get Sheet Names
freddie1 (1838)
I'm glad you are making progress. If the correct names are coming out, you must be doing something right!

In terms of the 'Range', yes that can be a range of cells, or just one cell such as "A1", or "B2". If you need the data from a range of cells such as A1 through A10 then the BSTR fed into the call will be like so ...

"A1:A10"

However, I've never done that in C++ because I fear it may involve Excel returning a SAFEARRAY. Maybe someone can correct me on that if I'm wrong, and I very well could be, because, like I said, I've never tried it in C++. In PowerBASIC and I imagine VB Script or VBA or whatever its very easy, because those languages handle all the messy low level details.

However, I hsave to admit that in my work with Excel I never even bothered to tackle that in PowerBASIC either. I do something else, which might work for you too.

Here is what I do in PowerBASIC, but I'll try to put the idea over with C++ syntax. I create an array of strings, something like so ...

wchar_t* szCell[]={L"A1", L"A2", L"A3", L"A4", L"A5", etc. };

The cells would of course be the ones I'm interested in, and you would of course need an array of BSTRs. Then I'd run through a loop feeding those cells in and extracting the data out of the variant and doing whatever with it.

Now in my case the Excel workbooks I work with are digitized versions of various previously paper forms we used to use, and I'm not talking about hundreds or thousands of entries, or anything like that. Usually no more than a few dozen calls. I'm sure it would be tremendously faster to simply ask for an array to be returned with values filled in, but since my data sets are small I never bothered with it.

A note on variable naming conventions: things like this ...

pWorkSheet

are named like that with the prefix 'p' because they are actually pointers to objects. In COM, pointers to object interfaces are usually returned as an [out] parameter as the last parameter of a function call. Of course, using IDispatch you extract them from variants. But in the case of extracting the count of sheets, I personally wouldn't use something like pExcelSheetCount, but, continuing with the idea of Hungarian Notation from Charles Simonyi, use iCount, i.e., an integer variable named iCount.

I believe there is an old Microsoft link that shows SAFEARRAY usage. I'll see if I can find it ...

freddie1 (1838)
freddie1 (1838)
If you do manage that thing with the SAFEARRAYs, it would be cool if you would post an example of extracting, for example, cells A1 through A10, where maybe names were in it such as "Charles", "Fred", "Sam", etc. I'd kind of like to try it too.

Cojones (71)
I haven't tried the SAFEARRAY method... I've achieved that using this...

I'll try later this day with SAFEARRAY. 

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
 
 for(int i = 1; i < 10; i++)
 {

	 //Member Get Range <197, 0xc5> (In Cell1 As Variant<0>, Opt In Cell2 As Variant<1>) As XLRange
	 //[id(0x000000c5), propget, helpcontext(0x000100c5)] HRESULT Range([in] VARIANT Cell1, [in, optional] VARIANT Cell2, [out, retval] Range** RHS);
	 wchar_t Range[16];
	 wsprintf(Range, L"A%d", i);
	 VariantInit(&vResult);
	 vArgArray[0].vt                = VT_BSTR,
// It seems it doesn't work if I use vArgArray[0].bstrVal = Range... 
// Anyway, this goes through the cells A1 to A9. i < 10
	 vArgArray[0].bstrVal           = SysAllocString(Range);
	 DispParams.rgvarg              = vArgArray;
	 DispParams.rgdispidNamedArgs   = 0;
	 DispParams.cArgs               = 1;  // Try to get Range
	 DispParams.cNamedArgs          = 0;  // Invoke _Worksheet::Range("A1")  << returns IDispatch** to dispinterface Range
	 hr=pXLWorksheet->Invoke(197,IID_NULL,lcid,DISPATCH_PROPERTYGET,&DispParams,&vResult,NULL,NULL);
	 if(FAILED(hr))
	 {
		printf("Failed To Get Range!\n");
		goto Sad_Ending;
	 }
	 pXLRange=vResult.pdispVal;
	 printf("pXLRange        = %u\n",(unsigned)pXLRange);

	 //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))
	 {
		wprintf(L"vResult.bstrVal = %s\n",vResult.bstrVal);
		VariantClear(&vResult);
	 }
 }
Last edited on
freddie1 (1838)
Yep! That's the idea! Either that or hardcode them in arrays. Being as I fancy myself as some kind of minor COM guru, I really ought to study up on SAFEARRAYs and learn how to use them. You know how it goes...you keep away from some subject trying to avoid it cuz you know its hard, and then after you take the time to learn it you wish you would have done it sooner.
Cojones (71)
Anyway, I tried running that in a loop... I've added a for(int j = 0; j < 1000; j++) before the code I pasted above... and I checked Task manager to see how much memory it takes and it seems it's a memory leak in there.

I used the code:

1
2
3
 
#include <crtdbg.h>
_CrtSetDbgFlag( _CRTDBG_ALLOC_MEM_DF | _CRTDBG_LEAK_CHECK_DF );


to see if any any mem leak occurs but... nothing... I even tried to use SysFreeString() on both vArgArray[0].bstrVal and vResult.bstrVal. The memory still goes up.
freddie1 (1838)
I'm coming up blank at the moment. Not sure what's causing it. Here's a thought though. Have you been checking Task Manager after your various experimentations? One way to know if COM reference counting is getting screwed up is if you see one or more instances of Excel.exe showing up in Task Manager after your program closes. What I always do is make sure first that no Excel processes are running before I start. Then I run my code and make sure none are showing up after my program ends. If any show up then there are interface pointers which haven't been released, and that's leaving an Excel 'hang' in memory.

freddie1 (1838)
I think I see the problem Alin! This ...

 
 
hr=pXLWorksheet->Invoke(197,IID_NULL,lcid,DISPATCH_PROPERTYGET,&DispParams,&vResult,NULL,NULL);


... is going to create two items that need to be dealt with from a resource usage standpoint. pXLRange is a live interface pointer which needs a Release() call before the next iteration of the loop. And this ...

VariantClear(&vArgArray[0]);

is simply clearing the BSTR in vArgArray. And these calls ...

1
2
3
 
hr=pXLRange->Invoke(6,IID_NULL,lcid,DISPATCH_PROPERTYGET,&NoArgs,&vResult,NULL,NULL);
VariantClear(&vResult);


...are again just clearing/releasing the BSTR returned from the Invoke call that gets the value in the cell. But it looks to me like the interface pointers are piling up with each iteration of the loop. So if you run it for 1000 iterations you may be getting 1000 unreleased pXLRange pointers which is like maybe 4000 bytes plus other overhead.

Or am I out in left fieldsomewhere? Check Task Manager. I bet Excel is hanging.




Last edited on
freddie1 (1838)
So what I'm saying if I have it right is that at the bottom of the loop stick a pXLRange->Release() call.
Cojones (71)
Excel is not hanging. I checked that. :) It's about Memory (Private Working Set) that goes up. I'll try what you mentioned earlier.
Cojones (71)
Fixed the problem. Thanks again. :)

1
2
3
4
5
6
7
8
9
10
11
12
 
			if(SUCCEEDED(hr))
			{
				wprintf(L"vResult.bstrVal = %s\n",vResult.bstrVal);
				SysFreeString(vResult.bstrVal);
				VariantClear(&vResult);
			}
// Adding this check after solves the memory leak problem
			if(pXLRange)
			{
				pXLRange->Release();
				pXLRange = NULL;
			}
Pages: 1234