C++

Forum

 
Trouble installing XLNT library

Pages: 12
erixliechtenstein (16)
Hello everyone,

I am writing code which requires reading stuff from xls files and I settled on using the XLNT library.

I did the following:
- Configure and generate a Visual Studio project using CMAKE gui
- Compiled it in visual studio - generated a build folder with a .lib file
- Followed instructions on: http://www.learncpp.com/cpp-tutorial/a3-using-libraries-with-codeblocks/ - added /lib/include directory to both linker and compiler search directories and added the .lib file to project build options
- Added "-std=c++14 -Ixlnt/include -lxlnt" to Other compiler options

The errors I have are:
- "undefined reference to 'xlnt::workbook::workbook()' - in total 18 identical errors

I looked around on forums what some people are saying re similar problems is to include the .cpp files as well. I don't understand how to do it so I haven't tried it yet (all instructions seem to say to add .cpp library file name) to the command line when compiling - but I am not even using the command line to compile and there are like 50 cpp files in the source folder of the library download file

How can I get this library up and running?

thank you!
freddie1 (1838)
I don't know anything about XLNT but I use raw COM to access *.xls files. If you don't have Excel installed you can read *.xls files with ODBC. I could show you either way if you like.
freddie1 (1838)
Here would be about the simplest example I can come up with of reading a line of data from an Excel *.xls file using first ODBC. Note that ODBC Drivers are a standard or system install on all versions of Windows going as far back as I can remember, which would be Windows 95 or thereabouts. To run the code create a blank *.xls file with Excel named Book1.xls. In cells A1, B1, C1, D1, and E1 type Col1, Col2, Col3, Col4, and Col5. In cells A2, B2, C2, D2, and E2 put any unsigned integers you like. I used 1, 2, 3, 4, and 5. I believe the column headings are necessary, because the ODBC Driver tries to make Excel act like a bona fide database (even though that's stretching the term a bit), and in databases one has columns or 'field types'.

Then put the Book1.xls file in whatever directory your *.exe will be executing within. Sounds easy and common sense, but Visual Studio users won't successfully make it past this beginning point, due to Visual Studio's unalterable propensity to proliferate directories and files in every direction. At the top of the file you can see my command line compilation strings for MS VC (version 15) and GCC Mingw 4.8.

Here is the Main.cpp file...

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
 
// cl Main.cpp CSql.cpp /O1 /Os /MT /EHsc odbc32.lib /FeExcel.exe
// g++ Main.cpp CSql.cpp -lodbc32 -oExcel.exe -mconsole -m32 -s -Os
// VC   71 k
// GCC 169 k
#include <windows.h>
#include <sqlext.h>
#include "CSql.h"

int main()
{
 std::string strQuery;  // One uses SQL (Structured Query Language) to describe to the database driver the data one wants
 unsigned int iCol[5];  // The addresses of these variables are bound to the resultant database curson generated
 SQLINTEGER iRead[5];   // This parameter in SQLBindCol() is an OUT parameter which will contain the # bytes read into bound variable
 HSTMT hStmt = NULL;    // ODBC Statement HANDLE
 CSql Sql;              // Connection Object/Class

 Sql.strDriver="Microsoft Excel Driver (*.xls)";  // Identify Database Driver To ODBC
 Sql.strDBQ = "Book1.xls";                        // Identify 'Database', so to speak, such as it is
 Sql.ODBCConnect();                               // Connect using generated connection string
 if(Sql.blnConnected==TRUE)                       //  Everything's gotta be tested for errors!!!
 {
    printf("Sql.blnConnected = TRUE\n");
    if(SQLAllocHandle(SQL_HANDLE_STMT,Sql.hConn,&hStmt)==SQL_SUCCESS)  // Allocate Statement Handle
    {
       printf("SQLAllocHandle() Succeeded!\n");
       strQuery="SELECT Col1, Col2, Col3, Col4, Col5 FROM [Sheet1$];";
       SQLBindCol(hStmt,1,SQL_C_ULONG,&iCol[0],0,&iRead[0]);           // Bind app variables to
       SQLBindCol(hStmt,2,SQL_C_ULONG,&iCol[1],0,&iRead[1]);           // retrieved database cursor
       SQLBindCol(hStmt,3,SQL_C_ULONG,&iCol[2],0,&iRead[2]);
       SQLBindCol(hStmt,4,SQL_C_ULONG,&iCol[3],0,&iRead[3]);
       SQLBindCol(hStmt,5,SQL_C_ULONG,&iCol[4],0,&iRead[4]);
       if(SQLExecDirect(hStmt,(SQLTCHAR*)strQuery.c_str(),SQL_NTS)==SQL_SUCCESS)  // execute SQL Statement
       {
          SQLFetch(hStmt);  // Retrieve database cursor into app variables
          printf("%u\t%u\t%u\t%u\t%u\n",iCol[0],iCol[1],iCol[2],iCol[3],iCol[4]);
          printf("%d\t%d\t%d\t%d\t%d\n",iRead[0],iRead[1],iRead[2],iRead[3],iRead[4]);
          SQLCloseCursor(hStmt);   // Close Database Cursor
       }
       SQLFreeHandle(SQL_HANDLE_STMT,hStmt);  // Free Statement Handle
    }
    Sql.ODBCDisconnect();  // Disconnect from database
 }
 else
    printf("Sql.blnConnected == FALSE!\n");
 getchar();

 return 0;
}

#if 0

C:\Code\VStudio\VC++9\Excel_ODBC>cl Main.cpp CSql.cpp /O1 /Os /MT /EHsc odbc32.lib /FeExcel.exe
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 15.00.21022.08 for 80x86
Copyright (C) Microsoft Corporation.  All rights reserved.

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

/out:Excel.exe
Main.obj
CSql.obj
odbc32.lib

C:\Code\VStudio\VC++9\Excel_ODBC>Excel.exe
Sql.blnConnected = TRUE
SQLAllocHandle() Succeeded!
1       2       3       4       5
4       4       4       4       4

C:\Code\VStudio\VC++9\Excel_ODBC>
#endif


#if 0

C:\Code\VStudio\VC++9\Excel_ODBC>g++ Main.cpp CSql.cpp -lodbc32 -oExcel.exe -mconsole -m32 -s -Os

C:\Code\VStudio\VC++9\Excel_ODBC>Excel.exe
Sql.blnConnected = TRUE
SQLAllocHandle() Succeeded!
1       2       3       4       5
4       4       4       4       4

C:\Code\VStudio\VC++9\Excel_ODBC>
#endif


Note I've provided console output of my builds and runs with both MS VC and Mingw 4.8. To build this code yourself you'll need some additional files included above in Main.cpp, which are CSql.cpp and CSql.h. What these are is a class I put together which wraps some of the grungy ODBC Connection code. Next post I'll provide those....
Last edited on
freddie1 (1838)
Here is CSql.h....

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
 
//MySql.h
#if !defined(SQL_H)
#define SQL_H
#include <string>
#include <sqltypes.h>

class CSql
{
 public:
 CSql();
 ~CSql();
 void MakeConnectionString(void);
 void ODBCConnect(void);
 void ODBCDisconnect(void);

 public:
 std::string       strConnectionString;
 std::string       strDatabase;
 std::string       strDriver;
 std::string       strServer;
 std::string       strDBQ;
 char              szCnOut[512];
 short int         iBytes;
 SWORD             swStrLen;
 SQLHENV           hEnvr;
 SQLHDBC           hConn;
 SQLINTEGER        iNativeErrPtr;
 SQLSMALLINT       iTextLenPtr;
 SQLTCHAR          szErrMsg[512];
 SQLTCHAR          szErrCode[8];
 unsigned int      blnConnected;
};

#endif


...and here is CSql.cpp...

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
 
//Sql.cpp
#include  <windows.h>
#include  <string>
#include  <cstdio>
#include  <tchar.h>
#include  <odbcinst.h>
#include  <sqlext.h>
#include  "CSql.h"

CSql::CSql()        // CSql Constructor
{
 szCnOut[0]         = 0;
 szErrMsg[0]        = 0;
 szErrCode[0]       = 0;
 this->hConn        = NULL;
 this->hEnvr        = NULL;
 this->blnConnected = 0;
}


CSql::~CSql()       // CSql Destructor
{
 if(this->hConn)
 {
    if(this->blnConnected)
       SQLDisconnect(this->hConn);
    SQLFreeHandle(SQL_HANDLE_DBC,this->hConn);
 }
 if(this->hEnvr)
    SQLFreeHandle(SQL_HANDLE_ENV,this->hEnvr);
}


void CSql::MakeConnectionString(void)
{
 if(this->strConnectionString==_T(""))
 {
    if(strDriver==_T("SQL Server"))
    {
       if(strDBQ==(TCHAR*)_T(""))
       {
          strConnectionString=(TCHAR*)_T("DRIVER=");
          strConnectionString=strConnectionString+strDriver+(TCHAR*)_T(";")+(TCHAR*)_T("SERVER=")+strServer+(TCHAR*)_T(";");
          if(strDatabase!=_T(""))
             strConnectionString=strConnectionString+(TCHAR*)_T("DATABASE=")+strDatabase+(TCHAR*)_T(";");
       }
       else
       {
          strConnectionString=(TCHAR*)_T("DRIVER=");
          strConnectionString=strConnectionString+strDriver+(TCHAR*)_T(";")+(TCHAR*)_T("SERVER=")+strServer+(TCHAR*)_T(";")+ \
          (TCHAR*)_T("DATABASE=") + strDatabase + (TCHAR*)_T(";") + (TCHAR*)_T("DBQ=") + strDBQ + (TCHAR*)_T(";");
       }
    }
    else if(strDriver==(TCHAR*)_T("Microsoft Access Driver (*.mdb)"))
    {
       strConnectionString=(TCHAR*)_T("DRIVER=");
       strConnectionString=strConnectionString+strDriver+(TCHAR*)_T(";")+(TCHAR*)_T("DBQ=")+strDBQ+(TCHAR*)_T(";");
    }
    else if(strDriver==(TCHAR*)_T("Microsoft Access Driver (*.mdb, *.accdb)"))
    {
       strConnectionString=_T("DRIVER=");
       strConnectionString=strConnectionString+strDriver+_T(";")+_T("DBQ=")+strDBQ+_T(";");
    }
    else if(strDriver==(TCHAR*)_T("Microsoft Excel Driver (*.xls)"))
    {
       strConnectionString=(TCHAR*)_T("DRIVER=");
       strConnectionString=strConnectionString+strDriver+(TCHAR*)_T(";")+(TCHAR*)_T("DBQ=")+strDBQ+(TCHAR*)_T(";");
    }
 }
}


void CSql::ODBCConnect(void)
{
 TCHAR szCnIn[512];
 UINT iResult;

 MakeConnectionString();
 SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&hEnvr);
 SQLSetEnvAttr(hEnvr,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,SQL_IS_INTEGER);
 SQLAllocHandle(SQL_HANDLE_DBC,hEnvr,&hConn);
 _tcscpy(szCnIn,strConnectionString.c_str());
 iResult=SQLDriverConnect(hConn,NULL,(SQLTCHAR*)szCnIn,(SQLSMALLINT)_tcslen(szCnIn),(SQLTCHAR*)szCnOut,512,&swStrLen,SQL_DRIVER_NOPROMPT);
 if(iResult==SQL_SUCCESS || iResult==SQL_SUCCESS_WITH_INFO)
 {
    blnConnected=TRUE;
    this->strConnectionString=szCnOut;
 }
 else
 {
    SQLGetDiagRec(SQL_HANDLE_DBC,hConn,1,szErrCode,&iNativeErrPtr,szErrMsg,512,&iTextLenPtr);
    blnConnected=FALSE;
    SQLFreeHandle(SQL_HANDLE_DBC,this->hConn), this->hConn=NULL;
    SQLFreeHandle(SQL_HANDLE_ENV,this->hEnvr), this->hEnvr=NULL;
 }
}


void CSql::ODBCDisconnect(void)
{
 if(blnConnected==TRUE)
 {
    SQLDisconnect(hConn);
    SQLFreeHandle(SQL_HANDLE_DBC,hConn), hConn=NULL;
    SQLFreeHandle(SQL_HANDLE_ENV,hEnvr), hEnvr=NULL;
    this->blnConnected=FALSE;
 }
 this->strConnectionString=_T("");
}
freddie1 (1838)
Here is some additional information somewhat related to all of the above...

https://forum.powerbasic.com/forum/user-to-user-discussions/special-interest-groups/programming-microsoft-office/757750-about-libxl-excel-library-for-developers

I also code in PowerBASIC a lot, as it is just as fast as C or C++ and generates much more efficient code.

My understanding is that the newer *.xlsx files are some kind of xhtml format or zip format or something. I'm pretty sure my above code won't read them - its specifically for the older *.xls format I think.

From my link above there is info on the BIFF format, which is what *.xls files are.
freddie1 (1838)
The above example is overly simplistic because it only uses integers. In any real application one will need to be able to read ints, floats, dates and strings. All that complicates things quite a bit. So lets get started. First, create a blank Book2.xls, and do whatever you have to to get this data into Sheet1...

1
2
3
4
5
 
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	0.54321	4/1/2006	April Fools Day!


The string 'Id' has to end up in A1, the string 'Float_Point' in B1, 'Date_Field' in C1, etc. Lines 2, 3, 4, and 5 has to contain the other data in the respective Excel columns A through D. This example therefore shows how to extract ints, floats, dates, and text (strings).

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
 
// cl Main.cpp CSql.cpp /O1 /Os /MT /EHsc odbc32.lib /FeEx02.exe
// g++ Main.cpp CSql.cpp -lodbc32 -oEx02.exe -mconsole -m32 -s -Os
// MSVC v15 (VStudio 2008) --  85 k
// MinGW 4.8               -- 169 k

/*
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	0.54321	4/1/2006	April Fools Day!
*/

#include <windows.h>
#include <sqlext.h>
#include "CSql.h"


void MkDate(TIMESTAMP_STRUCT& ts, char* szBuffer)
{
 char szMonth[4],szDay[4],szYear[8];

 sprintf(szMonth,"%u",ts.month);
 sprintf(szDay,"%u",ts.day);
 sprintf(szYear,"%u",ts.year);
 strcpy(szBuffer,szMonth);
 strcat(szBuffer,"/");
 strcat(szBuffer,szDay);
 strcat(szBuffer,"/");
 strcat(szBuffer,szYear);

 return;
}


int main()
{
 std::string        strQuery;
 char               szDate[16];
 UINT               iId;
 double             dblNum;
 TIMESTAMP_STRUCT   ts;
 SQLCHAR            szString[64];
 SQLINTEGER         iRead[5];
 CSql               Sql;
 HSTMT              hStmt  = NULL;

 Sql.strDriver="Microsoft Excel Driver (*.xls)";  // Identify Database Driver To ODBC
 Sql.strDBQ = "Book2.xls";                        // Identify 'Database', so to speak, such as it is
 Sql.ODBCConnect();                               // Connect using generated connection string
 if(Sql.blnConnected==TRUE)                       // Everything's gotta be tested for errors!!!
 {
    printf("Sql.blnConnected = TRUE\n");
    if(SQLAllocHandle(SQL_HANDLE_STMT,Sql.hConn,&hStmt)==SQL_SUCCESS)  // Allocate Statement Handle
    {
       printf("SQLAllocHandle() Succeeded!\n");
       strQuery = "SELECT Id, Float_Point, Date_Field, Text_Field FROM [Sheet1$];";
       SQLBindCol(hStmt,1,SQL_C_ULONG,&iId,0,&iRead[0]);
       SQLBindCol(hStmt,2,SQL_C_DOUBLE,&dblNum,0,&iRead[1]);
       SQLBindCol(hStmt,3,SQL_C_TYPE_DATE,&ts,0,&iRead[2]);
       SQLBindCol(hStmt,4,SQL_C_CHAR,szString,64,&iRead[3]);
       if(SQLExecDirect(hStmt,(SQLCHAR*)strQuery.c_str(),SQL_NTS)==SQL_SUCCESS)
       {
          while(SQLFetch(hStmt)!=SQL_NO_DATA)
          {
             MkDate(ts,szDate);
             printf("%u\t%10.6f\t%s\t%s\n",iId,dblNum,szDate,szString);
          }
          SQLCloseCursor(hStmt);
       }
       SQLFreeHandle(SQL_HANDLE_STMT,hStmt);  // Free Statement Handle
    }
    Sql.ODBCDisconnect();  // Disconnect from database
 }
 else
    printf("Sql.blnConnected == FALSE!\n");
 getchar();

 return 0;
}


#if 0
C:\Code\VStudio\VC++9\Excel_ODBC\Ex02>g++ Main.cpp CSql.cpp -lodbc32 -oEx02.exe -mconsole -m32 -s -Os

C:\Code\VStudio\VC++9\Excel_ODBC\Ex02>Ex02
Sql.blnConnected = TRUE
SQLAllocHandle() Succeeded!
1         3.141590      11/15/1952      My Birthday
2         1.234560      6/30/1969       Walk On Moon?
3        15.123400      1/1/2006        Some String
4         0.543210      4/1/2006        April Fools Day!


C:\Code\VStudio\VC++9\Excel_ODBC\Ex02>
#endif


Above is my build script for g++ (Mingw C++ Compiler) showing console output and run of program. In studying the above code you'll no doubt find difficulty in understanding the connection code (which my class wraps to make it disappear), and the SQLBindCol() code for setting up the reads of various types of variables. Don't go thinking I'm smart or anything - it took me years to figure out this stuff.
freddie1 (1838)
We can do all of the above with COM now if you are still with me????
Thomas1965 (4571)
My understanding is that the newer *.xlsx files are some kind of xhtml format or zip format or something. I'm pretty sure my above code won't read them - its specifically for the older *.xls format I think.

freddie1,
you can rename a xlsx file to .zip and have a look. It's actually a bunch of xml files.

In .NET it's quite common to use OLE DB to read modern Excel files. I wonder if you can do it without .NET
freddie1 (1838)

you can rename a xlsx file to .zip and have a look. It's actually a bunch of xml files.

In .NET it's quite common to use OLE DB to read modern Excel files. I wonder if you can do it without .NET


Yea, that's something I really ought to look into at some point Thomas. Where I work we use Excel extensively, and my mission critical apps need to read these files. What the folks have been doing is using the older *.xls formats, simply because when our various forms were first made back in the late 90s, those were in use. I don't think the above code I posted will work with *.xlsx files but I'm not sure. Today later I'll dump the database drivers on a system and see if any of the stock ODBC drivers list *.xlsx files. If so, a connection string might exist just for those.

Same deal with COM. I'm not sure. Will have to look into it. My guess is that if .NET does it, then there must be a low level procedural API for doing it, as .NET is, at least in my understanding, mostly wrappers around lower level stuff which is documented somewhere. Or not :) !!!
Thomas1965 (4571)
I am not sure if that is what you are looking for but it seems the same Excel driver works for .xlsx files.
https://pasteboard.co/EylbZeVZ.jpg
freddie1 (1838)
Just tried this driver connection string....

 
 
Sql.strDriver="Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)";


...and I modified my CSql Class to recognize that string. Then I changed my workbook name to Book1.xlsb after saving one in that format. Couldn't get it to work Thomas. Not sure what's wrong. You know how you sometimes get funny feelings about things? I mean, you decide to try something, make the changes in the code, recompile, and right before running it get the feeling that 'this likely won't work'. Well, that's the feeling I got and it didn't work. Bad karma I guess.

Now I'm wondering if my COM code will work with *.xlsb. I might ask about this over in the PowerBASIC forums.
Thomas1965 (4571)
If you want to try some other connection strings....
https://www.connectionstrings.com/excel-2007-odbc/
freddie1 (1838)
If you look close at all those connection strings listed there Thomas, they are all the same.

From my inquiries at the PowerBASIC Forum I learned that *.xlsx files should work. Only thing is, they don't work for me, but I think the deal is that I have a bad installation of Office 2016 / Excel 2016 on the box I use (but not much) at work. There is another machine at work I could test, and I'll look at it Monday if I have a chance.


In .NET it's quite common to use OLE DB to read modern Excel files. I wonder if you can do it without .NET


I expect. Over at PowerBASIC I learned that ADO works, and while that's ActiveX and COM through and through, it isn't .NET but rather native 'unmanaged'code, i.e., the kind I like, as I'm a rather unmanaged sort of fellow.

I reviewed some of my COM code for reading *.xls files, and I could post that, but will only do so if anyone is interested.

I think I'll look at the ADO thing too just for kicks and giggles.
freddie1 (1838)
I'll post some Excel IDispatch based COM code anyhow, even though no one other than the OP asked for it. Here would be an example using the low level direct COM Api to read the text "Hello, World!" out of Sheet2 of an Excel Workbook named Book1.xls. To use the example simply create the Book1.xls file and type "Hello, World!" or whatever into 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
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
 
// 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 /FeXL16_TCLib.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         // The logic of this COM code is as follows.  CoCreateInstance() is a Constructor function which returns a void**
    #define   UNICODE     // to an instantiated object in its last parameter.  It works by scouring the Registry for the CLSID (Class ID)
#endif                    // passed in the 1st parameter of the call.  When it locates that registry sub key it will find there the path to
#ifndef   _UNICODE        // the binary housing the COM Component, and it will start the process.  After that my wrapper function
    #define   _UNICODE    // GetDispatchObject() will return a pointer to the App's internal Workbooks Collection.  My OpenXLWorkBook()
#endif                    // wrapper function takes the pointer to the Workbooks Collection as its 1st parameter, and the last parameter is
#include <windows.h>      // the path of the Workbook, i.e., *.xls file, to open.  After opening the Workbook, one may use GetDispatchObject()
#ifdef TCLib              // again to request a pointer to the WorkSheets Collection.  Then my wrapper function SelectWorkSheet() can be used
   #include "stdio.h"     // to select and open the specific WorkSheet one wishes to access.  Then one can call GetXLCell() to read a string
#else                     // out of whatever cell is chosen.  Note that GetXLCell() is for reading text data, and GetCell() is for reading
   #include <stdio.h>     // everything else.
#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\\XL15\\Book1.xls"; // << Fix This For Your Path!!!
 wchar_t      szSheet[]           = L"Sheet2";
 wchar_t      szRange[]           = L"A1";  // Put some string such as "Hello, World!" in A1 of Sheet1
 wchar_t      szCell[64];Workbooks
 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))  // macro that tests HRESULT, which is a bit field entity, for S_OK, i.e., success
 {
    lcid=GetUserDefaultLCID();
    SetVisible(pXLApp,lcid);  // Wrapper function in XLFunctions.cpp will make Excel.exe visible (its already started by this point).
    pXLWorkbooks=GetDispatchObject(pXLApp,572,DISPATCH_PROPERTYGET,lcid);  // Wrapper function in XLFunctions.cpp will return IDispatch pointer tp WorkBooks Collection
    if(pXLWorkbooks)
    {
       pXLWorkbook=OpenXLWorkBook(pXLWorkbooks,lcid,szWorkBookPath);       // Wrapper Function in XLFunctions.cpp will return IDispatch pointer to WorkBook Object
       if(pXLWorkbook)
       {
          pXLWorksheets=GetDispatchObject(pXLWorkbook,494,DISPATCH_PROPERTYGET,lcid); // Wrapper Function in XLFunctions.cpp will return IDispatch pointer to WorkSheets Collection
          if(pXLWorksheets)
          {
             pXLWorksheet=SelectWorkSheet(pXLWorksheets,lcid,szSheet);  // Wrapper Function in XLFunctions.cpp will return IDispatch pointer to specific WorkSheet Object
             if(pXLWorksheet)
             {
                GetXLCell(pXLWorksheet,lcid,szRange,szCell,64); // Wrapper Function in XLFunctions.cpp will return test string in [out] parameter szCell
                wprintf(L"szCell = %s\n",szCell);
                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;
}


The reason I didn't specify using the moe obvious Sheet1 is that the mechanics of selecting alternate sheets is a bit tricky, so I wanted to show that. If you glance at the code above you'll see at top that there are some includes you'll need. I'll post them next. I believe XLFunctions.h will fit here yet though...

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)
XLFunctions.cpp will take several posts - not because its really that much executable code, but I've a lot of comments. Here is the 1st part...

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
 
// XLFunctions.cpp
#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) // Visible is one of the members of the _Application object ...
{                                                 //
 VARIANT         vArgArray[1];                    // [odl, uuid(000208D5-0000-0000-C000-000000000046), helpcontext(0x00020001), dual, oleautomation] interface _Application : IDispatch
 DISPPARAMS      DispParams;                      // {
 DISPID          dispidNamed;                     //   ...
 VARIANT         vResult;                         //   [id(0x0000022e), propget, helpcontext(0x0001022e)] HRESULT Visible([in, lcid] long lcid, [out, retval] VARIANT_BOOL* RHS);
 HRESULT         hr;                              //   [id(0x0000022e), propput, helpcontext(0x0001022e)] HRESULT Visible([in, lcid] long lcid, [in] VARIANT_BOOL RHS);
                                                  // };
 VariantInit(&vArgArray[0]);                      //
 vArgArray[0].vt               = VT_BOOL;         // This information can be obtained by dumping the object's typelib with OleView.exe.  You'll note the hexidicimal number 0x22e in
 vArgArray[0].boolVal          = TRUE;                 // both the propget and propput Visible _Application::Visible() members.  That number is a dispatch id or dispid for short.
 dispidNamed                   = DISPID_PROPERTYPUT;   // Since here we want to set the property to TRUE we use the DISPATCH_PROPERTYPUT as the wFlags parameter of the
 DispParams.rgvarg             = vArgArray;            // IDispatch::Invoke() call. Note above that the Excel Application Object is listed as being a dual interface.  The unfortunate
 DispParams.rgdispidNamedArgs  = &dispidNamed;    // fact of the matter is that the direct VTable part of the dual interface is largely nonfunctional in all the MS Office applications
 DispParams.cArgs              = 1;               // of which I'm familiar, specifically Word and Excel.  So this horrendous IDispatch access is forced upon us.  Its only redeeming
 DispParams.cNamedArgs         = 1;               // feature is that it does work in spite of the fact that it is horribly awkward.
 VariantInit(&vResult);
 hr=pObject->Invoke(0x0000022e,IID_NULL,lcid,DISPATCH_PROPERTYPUT,&DispParams,&vResult,NULL,NULL);

 return hr;
}


continued...
Last edited on
freddie1 (1838)
2nd part...

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
 
HRESULT GetXLCell(IDispatch* pXLWorksheet, LCID& lcid, wchar_t* pszRange, wchar_t* pszCell, size_t iBufferLength)
{
 DISPPARAMS      NoArgs         = {NULL,NULL,0,0};     // With IDispatch all of the parameters found in the dumped *.idl code (OleView.exe can dump the original IDL (Interface
 IDispatch*      pXLRange       = NULL;                // Definition Language) which forms the basis of the COM Object) must be passed into the IDispatch::Invoke() call through
 VARIANT         vArgArray[1];                         // an array of VARIANT objects.  That's what DISPARAMS is - an array of VARIANT Objects.  Believe it or not - they get
 VARIANT         vResult;                              // loaded in reverse order.  It really just keeps getting worse without mercy or limit.  Before getting any further, why
 DISPPARAMS      DispParams;                           // don't you just get yourself a copy of VBA, .NET, or PowerBASIC and save yourself this torture???  This procedure is only
 HRESULT         hr;                                   // useful if you know that the contents of a cell you are trying to retrieve is a String.  And by a string, I mean a BSTR.
                                                       // The BSTR type is the OLE/COM String Type.  Its close to a wchar_t but a little bit more.  The length of the string is
 VariantInit(&vResult);                                // prefaced to a size_t sized memory block right before the start of the string.  And the string can contain NULLs.  Like
 vArgArray[0].vt                = VT_BSTR,             // I said, it'll only get worse - without limit.
 vArgArray[0].bstrVal           = SysAllocString(pszRange);
 DispParams.rgvarg              = vArgArray;
 DispParams.rgdispidNamedArgs   = 0;          // Here is what the _Worksheet object looks like  with regard to its Range() member (from an OleView.exe IDL dump)...
 DispParams.cArgs               = 1;
 DispParams.cNamedArgs          = 0;          // [odl, uuid(000208D8-0000-0000-C000-000000000046), helpcontext(0x00020010), dual, nonextensible, oleautomation] interface _Worksheet : IDispatch
 hr=pXLWorksheet->Invoke                      // {
 (                                            //  ....
  0xC5,                                       //  [id(0x000000c5), propget, helpcontext(0x000100c5)] HRESULT Range([in] VARIANT Cell1, [in, optional] VARIANT Cell2, [out, retval] Range** RHS);
  IID_NULL,                                   //  ....
  lcid,                                       // };
  DISPATCH_PROPERTYGET,                       //
  &DispParams,                                // Why are we doing this?  Maybe I should back up.  To get data out of a cell you 1st have to open an Excel object.  That gives you an App
  &vResult,                                   // object in IDispatch* form.  Using that app object you get an IDispatch* to the App Object's WorkBooks Collection.  You then use that Work-
  NULL,                                       // books collection pointer to get a pointer to a WorkBook object, also in IDispatch* form.  With that pointer you can finally call the 'Open()'
  NULL                                        // Method of the WorkBook Object to open a specific *.xls file.  Once you get an IDispatch* to the opened Workbook you can query that interface
 );                                           // for a pointer (an IDispatch one, naturally) to the Workbook's WorkSheets Collection.  And with that pointer you open a specific Worksheet.
 if(FAILED(hr))                               // That brings us up to my code blurb above.  One of the members of the _Worksheet object is Range, and that would be an Excel Cell such as "A1",
    return E_FAIL;                            // "A2", etc.  You need to first set the range before you can extract its contents.  And the dispid (Dispatch ID) of the Range property
 pXLRange=vResult.pdispVal;                   // DISPATCH_PROPERTYGET call is 0xC5.  That pXLWorksheet->Invoke(0xC5) call will return an HRESULT which can be checked for SUCCESS, and in
                                              // that positive case an IDispatch* to an Excel Range object will be returned....
 //Member Get Value <6> () As Variant         //
 VariantClear(&vArgArray[0]);                 // [uuid(00020846-0000-0000-C000-000000000046),helpcontext(0x00020006)] dispinterface Range
 hr=pXLRange->Invoke                          // {
 (                                            //  ...
  6,                                          //  [id(0x00000006), propget, helpcontext(0x00010006)] VARIANT Value();
  IID_NULL,                                   //  [id(0x00000006), propput, helpcontext(0x00010006)] void Value([in] VARIANT rhs);
  lcid,                                       // };
  DISPATCH_PROPERTYGET,                       //
  &NoArgs,                                    // As you can see above, the Range dispinterface contains, among many others, getters and setters (mutators in OOP speak) for the 'Value'
  &vResult,                                   // contained within the cell.  You can see we're making this next IDispatch::Invoke() call just left using the IDispatch* to a Range object
  NULL,                                       // we've just received.  Now to continue with the brutality of all this, the reason I said this function ( GetXLCell() ) is only good for
  NULL                                        // retrieving BSTRs, is that just below left you see I'm testing the VARIANT::vt member of type VARTYPE to see what type of variable the VARIANT
 );                                           // contains.  A VARIANT essentially is comprised of a VARTYPE such as VT_BSTR for a BSTR, VT_I4 for a 32 bit integer, etc., and an unnamed union
 if(SUCCEEDED(hr))                            // which contains the actual value.  The pain really is without limit, like I said.  So in the case left I test the [out] VARIANT parameter of
 {                                            // the Invoke() call which would be the 6th parameter - vResult, to see if it contains a BSTR.  If it does, and if the character count returned
    if(vResult.vt==VT_BSTR)                   // by SysStringLen() is less than iBufferLength passed into this function, I use wcscpy() from the C Runtime to copy the characters to the buffer
    {                                                    // passed into this function in pszCell. Then I return S_OK.  Note all the other possibilities I return E_FAIL.  Note that memory leaks
       if(SysStringLen(vResult.bstrVal)<iBufferLength)   // are an ever present problem with all of this. Realize that when a BSTR is returned to you from the operating system, a memory
       {                                                 // allocation was performed to give you that BSTR, and you are responsible for freeing that memory.  There is a BSTR function for that
          wcscpy(pszCell,vResult.bstrVal);               // named SysFreeString(), but in this context here calls to VariantClear() will see that the contents of the VARIANT are released if
          VariantClear(&vResult);             // necessary.  Also note I did an explicit IDispatch::Release() call on the pXLRange object.
          return S_OK;
       }
       else
       {
          VariantClear(&vResult);
          return E_FAIL;
       }
    }
    else
    {
       pszCell[0]=0;
       VariantClear(&vResult);
    }
 }
 pXLRange->Release();

 return E_FAIL;
}


continued...
freddie1 (1838)
last part...

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
 
HRESULT GetCell(IDispatch* pXLSheet, LCID lcid, wchar_t* pszRange, VARIANT& pVt)
{
 DISPPARAMS      NoArgs         = {NULL,NULL,0,0};           // Now this function here - 'GetCell()', is more general purpose than my 'GetXLCell()' above.  Use this
 IDispatch*      pXLRange       = NULL;                      // one when you aren't sure what type of value is contained within a cell, or, stated even more precisely,
 VARIANT         vArgArray[1];                               // how Excel itself is internally handling the value contained within a cell.  For example, if you type
 VARIANT         vResult;                                    // 123456 in a cell, Excel the miserable but intelligent beaste will recognize that as a number, and when
 DISPPARAMS      DispParams;                                 // the cell contents is returned in a VARIANT, the VARIANT::vt member will likely be VT_I4 or VT_R8.
 HRESULT         hr;                                         // However, if the user or creator of the Excel SpreadSheet's data explicitely typed the cell as a 'Text'
                                                             // cell, (Right Click On A Cell And Choose 'Format Cells') a VT_BSTR will be returned in the VARIANT.  So
 VariantInit(&vResult);                                      // in other words, you can't just look at the data in an Excel spreadsheet and determine what the data
 vArgArray[0].vt                = VT_BSTR,                   // type of the data is.  You had better reread that last sentence and think long and hard about it.  But
 vArgArray[0].bstrVal           = SysAllocString(pszRange);  // this function begins exactly like the one above, but when it gets the IDispatch* to the Excel Range,
 DispParams.rgvarg              = vArgArray;                 // it simply makes the IDispatch::Invoke() call to obtain the contents of the cell, and returns that
 DispParams.rgdispidNamedArgs   = 0;                         // contents to the caller in the [out] parameter VARIANT& pVt.  The caller then has the responsibility
 DispParams.cArgs               = 1;                         // for determining what the VARIANT contains, e.g, a number, a string, a date, an interface pointer, etc.,
 DispParams.cNamedArgs          = 0;                         // and for freeing that memory if necessary.
 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, LCID& lcid, wchar_t* pszWorkBookPath)
{
 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(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;
}


If anyone wants it, I have a version of the above available that uses the C++ Standard Library std::wstring class.
Thomas1965 (4571)
Thanks freddie1,
I will have a look at it later. I am afraid the OP lost interest.
freddie1 (1838)

I am afraid the OP lost interest.


We had been corresponding by PM Thomas. Don't know though if any of my code will help. I realize it is rather complex. I deal with these things a lot at work, so I'm familiar with them. I think I'll put off the ADO thing for a bit though. Have other stuff needs done. :)

By the way, the techniques I've used above are fully applicable with Microsoft Word too. I do that all the time and my various applications write voluminous Word Documents from my data processing applications. I've an include file for that too I can make available to anyone who needs it.
erixliechtenstein (16)
OP here - haven't lost interest :)

To duplicate what freddie1 has said - (it is rather complex) - so I will take some time to figure it out and post my views here
Pages: 12