C++

Forum

 
C++ and Excel with ole2.h

wcrosby (2)
I am trying to simply open a excel file in the root directory of the program and save certain sheets as .csv

I have the following so far which compiles and runs but needs a few tweaks:

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
 
#include <ole2.h> // OLE2 Definitions
    #include <windows.h>
    #include <string>
    #include <cstdio>
    
    using namespace std;
    
    // AutoWrap() - Automation helper function...
    HRESULT AutoWrap(int autoType, VARIANT *pvResult, IDispatch *pDisp, LPOLESTR ptName, int cArgs...) {
        // Begin variable-argument list...
        va_list marker;
        va_start(marker, cArgs);
    
        if(!pDisp) {
            MessageBox(NULL, L"NULL IDispatch passed to AutoWrap()", L"Error", 0x10010);
            _exit(0);
        }
    
        // Variables used...
        DISPPARAMS dp = { NULL, NULL, 0, 0 };
        DISPID dispidNamed = DISPID_PROPERTYPUT;
        DISPID dispID;
        HRESULT hr;
        char buf[200];
        char szName[200];
    
        
        // Convert down to ANSI
        WideCharToMultiByte(CP_ACP, 0, ptName, -1, szName, 256, NULL, NULL);
        
        // Get DISPID for name passed...
        hr = pDisp->GetIDsOfNames(IID_NULL, &ptName, 1, LOCALE_USER_DEFAULT, &dispID);
        if(FAILED(hr)) {
            //sprintf(buf, "IDispatch::GetIDsOfNames(\"%s\") failed w/err 0x%08lx", szName, hr);
            //MessageBox(NULL, buf, L"AutoWrap()", 0x10010);
            _exit(0);
            return hr;
        }
        
        // Allocate memory for arguments...
        VARIANT *pArgs = new VARIANT[cArgs+1];
        // Extract arguments...
        for(int i=0; i<cArgs; i++) {
            pArgs[i] = va_arg(marker, VARIANT);
        }
        
        // Build DISPPARAMS
        dp.cArgs = cArgs;
        dp.rgvarg = pArgs;
        
        // Handle special-case for property-puts!
        if(autoType & DISPATCH_PROPERTYPUT) {
            dp.cNamedArgs = 1;
            dp.rgdispidNamedArgs = &dispidNamed;
        }
        
        // Make the call!
        hr = pDisp->Invoke(dispID, IID_NULL, LOCALE_SYSTEM_DEFAULT, autoType, &dp, pvResult, NULL, NULL);
        if(FAILED(hr)) {
            //sprintf(buf, "IDispatch::Invoke(\"%s\"=%08lx) failed w/err 0x%08lx", szName, dispID, hr);
            //MessageBox(NULL, buf, L"AutoWrap()", 0x10010);
            _exit(0);
            return hr;
        }
        // End variable-argument section...
        va_end(marker);
        
        delete [] pArgs;
        
        return hr;
    }
    
    
    int main(){
    
    // Initialize COM for this thread...
       CoInitialize(NULL);
    
       // Get CLSID for our server...
       CLSID clsid;
       HRESULT hr = CLSIDFromProgID(L"Excel.Application", &clsid);
    
       if(FAILED(hr))
       {
          ::MessageBox(NULL, L"CLSIDFromProgID() failed", L"Error", 0x10010);
          return -1;
       }
    
       // Start server and get IDispatch...
       IDispatch *pXlApp;
       hr = CoCreateInstance(clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **)&pXlApp);
       if(FAILED(hr)) 
       {
          ::MessageBox(NULL, L"Excel not registered properly", L"Error", 0x10010);
          return -2;
       }
    
       // Make it visible (i.e. app.visible = 1)
       {
    
          VARIANT x;
          x.vt = VT_I4;
          x.lVal = 1;
          AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlApp, L"Visible", 1, x);
       }
    
       // Get Workbooks collection
       IDispatch *pXlBooks;
       {
          VARIANT result;
          VariantInit(&result);
          AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"Workbooks", 0);
          pXlBooks = result.pdispVal;
       }
    
       // Call Workbooks.Add() to get a new workbook...
       IDispatch *pXlBook;
       {
          VARIANT result;
          VariantInit(&result);
          AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBooks, L"Add", 0);
          pXlBook = result.pdispVal;
       }
    
       // Get ActiveSheet object
       IDispatch *pXlSheet;
       {
          VARIANT result;
          VariantInit(&result);
          AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveSheet", 0);
          pXlSheet = result.pdispVal;
       }
    
       // Set .Saved property of workbook to TRUE so we aren't prompted
       // to save when we tell Excel to quit...
       {
          VARIANT x;
          x.vt = VT_I4;
          x.lVal = 1;
          AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlBook, L"Saved", 1, x);
       }
    
       // Tell Excel to quit (i.e. App.Quit)
       AutoWrap(DISPATCH_METHOD, NULL, pXlApp, L"Quit", 0);
    
       // Release references...
       pXlSheet->Release();
       pXlBook->Release();
       pXlBooks->Release();
       pXlApp->Release();
    
       // Uninitialize COM for this thread...
       CoUninitialize();
    
       return 0;
    }


Any help would be appreciated.

Thanks!
wcrosby (2)
solution was:

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
212
213
214
215
 

#include <ole2.h> // OLE2 Definitions
#include <string>

// AutoWrap() - Automation helper function...
HRESULT AutoWrap(int autoType, VARIANT *pvResult, IDispatch *pDisp, LPOLESTR ptName, int cArgs...) {
    // Begin variable-argument list...
    va_list marker;
    va_start(marker, cArgs);

    if(!pDisp) {
        MessageBox(NULL, L"NULL IDispatch passed to AutoWrap()", L"Error", 0x10010);
        _exit(0);
    }

    // Variables used...
    DISPPARAMS dp = { NULL, NULL, 0, 0 };
    DISPID dispidNamed = DISPID_PROPERTYPUT;
    DISPID dispID;
    HRESULT hr;
    char buf[200];
    char szName[200];


    // Convert down to ANSI
    WideCharToMultiByte(CP_ACP, 0, ptName, -1, szName, 256, NULL, NULL);

    // Get DISPID for name passed...
    hr = pDisp->GetIDsOfNames(IID_NULL, &ptName, 1, LOCALE_USER_DEFAULT, &dispID);
    if(FAILED(hr)) {
        //sprintf(buf, "IDispatch::GetIDsOfNames(\"%s\") failed w/err 0x%08lx", szName, hr);
        //MessageBox(NULL, buf, "AutoWrap()", 0x10010);
        _exit(0);
        return hr;
    }

    // Allocate memory for arguments...
    VARIANT *pArgs = new VARIANT[cArgs+1];
    // Extract arguments...
    for(int i=0; i<cArgs; i++) {
        pArgs[i] = va_arg(marker, VARIANT);
    }

    // Build DISPPARAMS
    dp.cArgs = cArgs;
    dp.rgvarg = pArgs;

    // Handle special-case for property-puts!
    if(autoType & DISPATCH_PROPERTYPUT) {
        dp.cNamedArgs = 1;
        dp.rgdispidNamedArgs = &dispidNamed;
    }

    // Make the call!
    hr = pDisp->Invoke(dispID, IID_NULL, LOCALE_SYSTEM_DEFAULT, autoType, &dp, pvResult, NULL, NULL);
    if(FAILED(hr)) {
        //sprintf(buf, "IDispatch::Invoke(\"%s\"=%08lx) failed w/err 0x%08lx", szName, dispID, hr);
        //MessageBox(NULL, buf, "AutoWrap()", 0x10010);
        _exit(0);
        return hr;
    }
    // End variable-argument section...
    va_end(marker);

    delete [] pArgs;

    return hr;
}

int main()
{
    // Initialize COM for this thread...
    CoInitialize(NULL);

    // Get CLSID for our server...
    CLSID clsid;
    HRESULT hr = CLSIDFromProgID(L"Excel.Application", &clsid);

    if(FAILED(hr)) {

        ::MessageBox(NULL, L"CLSIDFromProgID() failed", L"Error", 0x10010);
        return -1;
    }

    // Start server and get IDispatch...
    IDispatch *pXlApp;
    hr = CoCreateInstance(clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **)&pXlApp);
    if(FAILED(hr)) {
        ::MessageBox(NULL, L"Excel not registered properly", L"Error", 0x10010);
        return -2;
    }

    /* Make it visible (i.e. app.visible = 1)
    {

        VARIANT x;
        x.vt = VT_I4;
        x.lVal = 1;
        AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlApp, L"Visible", 1, x);
    }
    */

    // Get Workbooks collection
    IDispatch *pXlBooks;
    {
        VARIANT result;
        VariantInit(&result);
        AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"Workbooks", 0);
        pXlBooks = result.pdispVal;
    }

    // Call Workbooks.Open() to get a new workbook...
    IDispatch *pXlBook;
    {
        VARIANT vtFileName;
        vtFileName.vt = VT_BSTR;
        vtFileName.bstrVal = SysAllocString( L"C:\\Users\\UserName\\Desktop\\test.xls" );

        VARIANT result;
        VariantInit(&result);
        AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBooks, L"Open", 1, vtFileName );             // reversed order is using more options
        pXlBook = result.pdispVal;
    }

    // Get ActiveSheet object
    IDispatch *pXlSheet;
    {
        VARIANT result;
        VariantInit(&result);
        AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveSheet", 0);
        pXlSheet = result.pdispVal;
    }

        // pXlBook->SaveAs
    {
        // Convert the NULL-terminated string to BSTR.
        VARIANT vtFileName;
        vtFileName.vt = VT_BSTR;
        vtFileName.bstrVal = SysAllocString( L"C:\\Users\\UserName\\Desktop\\test_sheet1.csv" );

        VARIANT vtFormat;
        vtFormat.vt = VT_I4;
        vtFormat.lVal = 6;     // XlFileFormat::xlCSV

        // If there are more than 1 parameters passed, they MUST be pass in 
        // reversed order. Otherwise, you may get the error 0x80020009.
        AutoWrap(DISPATCH_METHOD, NULL, pXlBook, L"SaveAs", 2, vtFormat, 
        vtFileName);

        VariantClear(&vtFileName);
    }

    //Changing the ActiveSheet    
    {        
        {            
            VARIANT itemn;            
            itemn.vt = VT_I4;
            itemn.lVal = 2;

            VARIANT result;            
            VariantInit(&result);
            AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"Worksheets", 1, itemn);            
            pXlSheet = result.pdispVal;        
        }     

        {            
            VARIANT result;            
            VariantInit(&result);            
            AutoWrap(DISPATCH_METHOD, &result, pXlSheet, L"Activate", 0);        
        }    
    }

    // pXlBook->SaveAs
    {
        // Convert the NULL-terminated string to BSTR.
        VARIANT vtFileName;
        vtFileName.vt = VT_BSTR;
        vtFileName.bstrVal = SysAllocString( L"C:\\Users\\UserName\\Desktop\\test_sheet2.csv" );

        VARIANT vtFormat;
        vtFormat.vt = VT_I4;
        vtFormat.lVal = 6;     // XlFileFormat::xlCSV

        // If there are more than 1 parameters passed, they MUST be pass in 
        // reversed order. Otherwise, you may get the error 0x80020009.
        AutoWrap(DISPATCH_METHOD, NULL, pXlBook, L"SaveAs", 2, vtFormat, 
        vtFileName);

        VariantClear(&vtFileName);
    }

    // Set .Saved property of workbook to TRUE so we aren't prompted
    // to save when we tell Excel to quit...
    {
        VARIANT x;
        x.vt = VT_I4;
        x.lVal = 1;
        AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlBook, L"Saved", 1, x);
    }

    // Tell Excel to quit (i.e. App.Quit)
    AutoWrap(DISPATCH_METHOD, NULL, pXlApp, L"Quit", 0);

    // Release references...
    pXlSheet->Release();
    pXlBook->Release();
    pXlBooks->Release();
    pXlApp->Release();

    // Uninitialize COM for this thread...
    CoUninitialize();

    return 0;
}
EisenhowerPDF (15)
Broadly speaking, before we can apply a certain excel document reading and processing library, we can simply load and open excel source file in two ways: loading excel from local file or loading excel file from memory stream. Things go in the same way for excel document saving and storing. Check the detailed guides.

http://www.rasteredge.com/how-to/vb-net-imaging/excel-reading/
http://www.rasteredge.com/how-to/vb-net-imaging/excel-get-to-start/






Topic archived. No new replies allowed.