C++

Forum

 
how to read data from .xls files?

Pages: 12
peripheral (58)
I just simply want to read one column data from a .xls file.

how to know the format,and get the destinate column?

any advice is appreciated
peripheral (58)
good afternoon everybody！
Last edited on
kbw (9488)
The last time the .XLS format was described was in Excel 4 around 17 years ago.

The recomended way of getting hold of data in an Excel file is to use Excel as a COM object. You will need Excel installed on the target computer.
peripheral (58)
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
 
#include <iostream> 
#include <fstream>
#include <string> 
using namespace std; 

int main() 
{ 
    string str;
     printf("Please input xls file name");
     cin>>str;
     ifstream ifs(str.c_str()); 
    
     if(!ifs) 
    {  cerr <<"input file name is incorrect" <<endl;   exit(1);     } 
   
     ofstream ofs("new.xls");  
      
   ofs<<ifs.rdbuf(); 
    if (!ofs) 
    {   cerr <<"error: can not open \"new.xls\" for output\n";  exit(1);}                               
      ifs.close();
    
    ofs.close();
    cout<<"copied one file！"; 
    cin.get();
     return 0; 
}

my target computer already installed office xp.
above is my code .I don'have know why it doesn't work even I replace code in line 16 with
ofstream ofs("new.txt");.what is the reason?
kbw (9488)
peripheral (58)
thank you kbw!

I think it is too comlicated to .is there a easy way to read one column.

my task is to read a xls file.get the names in it.the names are put in column A and count how many there are ,and how many of them are the same name?
kbw (9488)
I think it is too comlicated
I agree. All COM programming is tricky to use because the technology pushes the details out to the user rather than hiding it, it's scandalous.

is there a easy way to read one column
Not directly. If you save the file as a .csv, you'll have comma delimeted text file that you can read easily.

For example, to print the first column. I've not compiled this so there may be errors.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
#include <fstream>
#include <string>
#include <string.h>

int main()
{
    std::ifstream is("info.csv");

    std::string line;
    while (std::getline(is, line))
    {
        const char *begin = line.c_str();

        // strip beyond first comma
        if (const char *end = strchr(begin, ','))
        {
            std::string column1(begin, end - begin);
            std::cout << column1 << std::endl;
        }
    }
    return 0;
}
freddie1 (1838)

I think it is too comlicated to .is there a easy way to read one column.


Everything in C++ is complicated - COM just a bit more so.

You can also use ODBC to read data from *.xls files. The advantage of that approach is that the Microsoft Jet Database driver is used, which is installed on all Windows versions by default. I'll post a simple demo of that in a bit for you. As I said though, nothing in C++ is particularly easy. However, the ODBC approach is a bit easier than IDispatch COM with Excel.
freddie1 (1838)
I used GNU CodeBlocks To test this code, but it should work with VC Express except likely for piles of warnings about unsecure krap, which can be eliminated with various defines. To use this code you need to create a blank Excel Workbook named Book1.xls and put it in the same folder/directory as the executable. The below data needs to be pasted into Cell A1 By copying it from below and right clicking and 'Paste' into Cell A1. Actually, the data contains four colums of data and should appear in columns A through D and the 1st five rows. If it doesn't work that way after following my directions above, then manually create the data. This is what the console screen output should look like...

1
2
3
4
5
6
7
8
9
10
11
 
Connection Succeeded!
iRecCt  = 4

Id      Float_Point     Date_Field      Text_Field
======================================================
1         3.14           11/15/1952    My Birthday
2         1.23           6/30/1969     Walk On Moon?
3        15.12           1/1/2006      Some String
4         0.54           4/1/2006      April Fools Day!

I Sincerely Hope This Isn't Too Hard!


!!! This Is What Needs To Be Pasted!!!!!

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!


Here is Main.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
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
 
/*
Libraries Needed To Link Against For This program.
Add to Linker Settings In Whichever Development
Environment You Are Using.  If A Microsoft Product
The Libraries Won't Start With The Prefix lib.

..\..\..\Program Files\CodeBlocks\MinGW\lib\libodbccp32.a
..\..\..\Program Files\CodeBlocks\MinGW\lib\libodbc32.a

*/
#include      <windows.h>
#include      <tchar.h>
#include      <stdio.h>          //iostream can eat s*** and die
#include      <string.h>
#include      <sql.h>            //ODBC header file
#include      <sqlext.h>         //ODBC header file
#include      "Strings.h"
#include      "Sql.h"            //Sql Class used to offer ODBC functionality.
#include      "SqlProcs.h"       //Some of my ODBC wrappers to hide some real grungy ODBC code

int GetExcelRecordCount(SQL& Sql)
{
 unsigned int iRecCt=0;
 TCHAR szQuery[128];
 SQLHSTMT hStmt;
 long iJnk;

 _tcscpy(szQuery,_T("SELECT Count(*)  As RecordCount From [Sheet1$];"));
 SQLAllocHandle(SQL_HANDLE_STMT,Sql.hConn,&hStmt);
 SQLBindCol(hStmt,1,SQL_C_ULONG,&iRecCt,0,&iJnk);
 if(SQLExecDirect(hStmt,(SQLTCHAR*)szQuery,SQL_NTS)!=SQL_SUCCESS)
 {
    SQLGetDiagRec(SQL_HANDLE_STMT,hStmt,1,Sql.szErrCode,&Sql.iNativeErrPtr,Sql.szErrMsg,512,&Sql.iTextLenPtr);
    SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
    return -1;
 }
 else
 {
    SQLFetch(hStmt);
    SQLCloseCursor(hStmt);
    SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
    return iRecCt;
 }
}

UINT blnDumpExcelData(SQL& sql)
{
 SQLHSTMT          hStmt;
 TCHAR             szQuery[100];
 TCHAR             szDate[16];
 SQLINTEGER        iJnk;
 UINT              iId;
 double            dblNum;
 TIMESTAMP_STRUCT  ts;
 SQLTCHAR          szString[64];
 TCHAR             szBuffer[128];

 if(SQLAllocHandle(SQL_HANDLE_STMT,sql.hConn,&hStmt)==SQL_SUCCESS)
 {
    _tcscpy(szQuery,_T("SELECT Id, Float_Point, Date_Field, Text_Field FROM [Sheet1$];"));
    SQLBindCol(hStmt,1,SQL_C_ULONG,&iId,0,&iJnk);
    SQLBindCol(hStmt,2,SQL_C_DOUBLE,&dblNum,0,&iJnk);
    SQLBindCol(hStmt,3,SQL_C_TYPE_DATE,&ts,0,&iJnk);
    SQLBindCol(hStmt,4,SQL_C_TCHAR,szString,64,&iJnk);
    if(SQLExecDirect(hStmt,(SQLTCHAR*)szQuery,SQL_NTS)==SQL_SUCCESS)
    {
       while(SQLFetch(hStmt)!=SQL_NO_DATA)
       {
        MkDate(ts,szDate);
        memset(szBuffer,0,128);
        _stprintf(szBuffer,_T("%-6u%8.2f           %-12.10s  %-20s"),iId,dblNum,szDate,szString);
        printf("%s\n",szBuffer);
       }
    }
    SQLCloseCursor(hStmt);
    SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
    return TRUE;
 }

 return FALSE;
}


int main()
{
 TCHAR lpBuffer[MAX_PATH];
 DWORD nBufLen=MAX_PATH;
 int iRecCt=0;
 SQL Sql;

 Sql.strDriver=_T("Microsoft Excel Driver (*.xls)");
 GetCurrentDirectory(nBufLen,lpBuffer);
 Sql.strDBQ = lpBuffer;
 Sql.strDBQ = Sql.strDBQ +_T("\\") + _T("Book1.xls");
 Sql.ODBCConnect();
 if(Sql.blnConnected==TRUE)
 {
    puts("Connection Succeeded!");
    iRecCt=GetExcelRecordCount(Sql);
    printf("iRecCt  = %d\n\n",iRecCt);
    printf("Id\tFloat_Point\tDate_Field\tText_Field\n");
    printf("======================================================\n");
    if(blnDumpExcelData(Sql))
       puts("\nI Sincerely Hope This Isn't Too Hard!\n");
    Sql.ODBCDisconnect();
 }
 else
    puts("Connection Failed!");
 getchar();

 return 0;
}

/*
Output In Console Window
======================================================


Connection Succeeded!
iRecCt  = 4

Id      Float_Point     Date_Field      Text_Field
======================================================
1         3.14           11/15/1952    My Birthday
2         1.23           6/30/1969     Walk On Moon?
3        15.12           1/1/2006      Some String
4         0.54           4/1/2006      April Fools Day!

I Sincerely Hope This Isn't Too Hard!
*/


/*
Here Is The Data You Need To Paste Into Cell A1 Of Sheet1 of An Excel
Workbook Named Book1.xls.  This file should be in the same folder/directory
as the executable.


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!
*/

/*
Main.cpp       112
Strings.cpp    766
SqlProcs.cpp    90
Sql.cpp         92

Sql.h           32
SqlProcs.h       9
Strings.h       57
========================
total         1178 lines


Compiles To 33K With Optimazations For
Small Size With CodeBlocks.
*/

Last edited on
freddie1 (1838)
Here is Sql.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
 
//Sql.cpp
#include  <windows.h>
//#define   _UNICODE
#include  <tchar.h>
#include  <stdio.h>
#include  <odbcinst.h>
#include  <sql.h>
#include  <sqlext.h>
#include  "Strings.h"
#include  "Sql.h"
#if defined(MY_DEBUG)
    extern FILE* fp;
#endif


SQL::SQL() //Constructor
{
 ZeroMemory(szCnOut, 512);
 ZeroMemory(szErrMsg,512);
 strDBQ=_T("");
}


SQL::~SQL()
{
 //Sql Destructor
}


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


void SQL::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.lpStr());
 iResult=SQLDriverConnect(hConn,NULL,(SQLTCHAR*)szCnIn,(SQLSMALLINT)_tcslen(szCnIn),(SQLTCHAR*)szCnOut,512,&swStrLen,SQL_DRIVER_NOPROMPT);
 if(iResult==0 || iResult==1)
    blnConnected=TRUE;
 else
 {
    SQLGetDiagRec(SQL_HANDLE_DBC,hConn,1,szErrCode,&iNativeErrPtr,szErrMsg,512,&iTextLenPtr);
    blnConnected=FALSE;
    SQLDisconnect(hConn);
    SQLFreeHandle(SQL_HANDLE_DBC,hConn);
    SQLFreeHandle(SQL_HANDLE_ENV,hEnvr);
 }
}


void SQL::ODBCDisconnect(void)
{
 if(blnConnected==TRUE)
 {
    SQLDisconnect(hConn);
    SQLFreeHandle(SQL_HANDLE_DBC,hConn);
    SQLFreeHandle(SQL_HANDLE_ENV,hEnvr);
    blnConnected=FALSE;
 }
}


Here is SqlProcs.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
 
//SqlProcs.cpp
#include  <windows.h>
//#define   _UNICODE
#include  <tchar.h>
#include  <stdio.h>
#include  <odbcinst.h>  
#include  <sql.h>       
#include  <sqlext.h>    
#include  "Strings.h"
#include  "SqlProcs.h"


unsigned int iInstallerError()
{
 DWORD pErr;
 TCHAR szErrMsg[512];
 WORD  cbMsgBuffer=512;
 WORD  cbRet;
 WORD  wErrNum=1;

 while(SQLInstallerError(wErrNum,&pErr,szErrMsg,cbMsgBuffer,&cbRet)!=SQL_NO_DATA)
 {
  wErrNum++;
 };

 return (unsigned int)pErr;
}


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

 _stprintf(szMonth,_T("%u"),ts.month);
 _stprintf(szDay,_T("%u"),ts.day);
 _stprintf(szYear,_T("%u"),ts.year);
 _tcscpy(szBuffer,szMonth);
 _tcscat(szBuffer,_T("/"));
 _tcscat(szBuffer,szDay);
 _tcscat(szBuffer,_T("/"));
 _tcscat(szBuffer,szYear);

 return;
}


TIMESTAMP_STRUCT ParseDate(TCHAR* szDate, TCHAR* szFormat, TCHAR* szDelimiter)
{
 UINT i=0,j=0,k=0;
 TIMESTAMP_STRUCT ts;
 TCHAR buf[3][8];             //buf[0] for month, buf[1] for day, buf[2] for year
 TCHAR* p;

 memset(buf,0,sizeof(buf));  //zero out buf[]
 p=szDate;
 for(i=0;i<_tcslen((TCHAR*)szDate);i++)
 {
     if(*p!=*szDelimiter)
     {
        buf[j][k++]=*p;
        buf[j][k+1]=_T('\0');
     }
     else
     {
        j++;
        k=0;
     }
     p++;
 }
 if(!_tcsicmp((TCHAR*)szFormat,_T("MDY")))
 {
    ts.month=(short)_ttoi(buf[0]);
    ts.day=(short)_ttoi(buf[1]);
    ts.year=(short)_ttoi(buf[2]);
 }
 if(!_tcsicmp((TCHAR*)szFormat,_T("DMY")))
 {
    ts.day=(short)_ttoi(buf[0]);
    ts.month=(short)_ttoi(buf[1]);
    ts.year=(short)_ttoi(buf[2]);
 }
 if(!_tcsicmp((TCHAR*)szFormat,_T("YMD")))
 {
    ts.year=(short)_ttoi(buf[0]);
    ts.month=(short)_ttoi(buf[1]);
    ts.day=(short)_ttoi(buf[2]);
 }

 return ts;
}

freddie1 (1838)
Here Is Strings.cpp (1st Third)

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
216
217
218
219
220
221
222
223
224
225
 
//Strings.cpp
#include  <windows.h>
#include  <tchar.h>
#include  <stdlib.h>
#include  <stdio.h>
#include  <string.h>
#include  "Strings.h"


String::String()    //Uninitialized Constructor
{
 pStrBuffer=new TCHAR[MINIMUM_ALLOCATION];
 pStrBuffer[0]='\0';
 this->iAllowableCharacterCount=MINIMUM_ALLOCATION-1;
}


String::String(const TCHAR ch)  //Constructor: Initializes with TCHAR
{
 pStrBuffer=new TCHAR[MINIMUM_ALLOCATION];
 pStrBuffer[0]=ch;
 pStrBuffer[1]='\0';
 iAllowableCharacterCount=MINIMUM_ALLOCATION-1;
}


String::String(const TCHAR* pStr)  //Constructor: Initializes with TCHAR*
{
 int iLen,iNewSize;

 iLen=_tcslen(pStr);
 iNewSize=(iLen/16+1)*16;
 pStrBuffer=new TCHAR[iNewSize];
 this->iAllowableCharacterCount=iNewSize-1;
 _tcscpy(pStrBuffer,pStr);
}


String::String(const String& s)  //Constructor Initializes With Another String, i.e., Copy Constructor
{
 int iLen,iNewSize;

 iLen=_tcslen(s.pStrBuffer);
 iNewSize=(iLen/16+1)*16;
 this->pStrBuffer=new TCHAR[iNewSize];
 this->iAllowableCharacterCount=iNewSize-1;
 _tcscpy(this->pStrBuffer,s.pStrBuffer);
}


String::String(const int iSize)  //Constructor Creates String With Custom Sized
{                                //Buffer (rounded up to paragraph boundary)
 int iNewSize;

 iNewSize=(iSize/16+1)*16;
 pStrBuffer=new TCHAR[iNewSize];
 this->iAllowableCharacterCount=iNewSize-1;
 this->pStrBuffer[0]=_T('\0');
}


String::String(const TCHAR ch, int iCount)
{
 int iNewSize;

 iNewSize=(iCount/16+1)*16;
 pStrBuffer=new TCHAR[iNewSize];
 this->iAllowableCharacterCount=iNewSize-1;
 for(int i=0; i<iCount; i++)
     pStrBuffer[i]=ch;
 pStrBuffer[iCount]=_T('\0');
}


String& String::operator=(const TCHAR ch)  //Overloaded operator = for assigning a TCHAR to a String
{
 this->pStrBuffer[0]=ch;
 this->pStrBuffer[1]=_T('\0');

 return *this;
}


String& String::operator=(const TCHAR* pStr)   //Constructor For If Pointer To Asciiz String Parameter
{
 int iLen,iNewSize;

 iLen=_tcslen(pStr);
 if(iLen<this->iAllowableCharacterCount)
    _tcscpy(pStrBuffer,pStr);
 else
 {
    delete [] pStrBuffer;
    iNewSize=(iLen/16+1)*16;
    pStrBuffer=new TCHAR[iNewSize];
    this->iAllowableCharacterCount=iNewSize-1;
    _tcscpy(pStrBuffer,pStr);
 }

 return *this;
}


String& String::operator=(const String& strRight)  //Overloaded operator = for
{                                                  //assigning another String to
 int iRightLen,iNewSize;                           //a String

 if(this==&strRight)
    return *this;
 iRightLen=_tcslen(strRight.pStrBuffer);
 if(iRightLen < this->iAllowableCharacterCount)
    _tcscpy(pStrBuffer,strRight.pStrBuffer);
 else
 {
    iNewSize=(iRightLen/16+1)*16;
    delete [] this->pStrBuffer;
    this->pStrBuffer=new TCHAR[iNewSize];
    this->iAllowableCharacterCount=iNewSize-1;
    _tcscpy(pStrBuffer,strRight.pStrBuffer);
 }

 return *this;
}


bool String::operator==(const String strCompare)
{
 if(_tcscmp(this->pStrBuffer,strCompare.pStrBuffer)==0)  //_tcscmp
    return true;
 else
    return false;
}


String& String::operator+(const TCHAR ch)      //Overloaded operator + (Puts TCHAR in String)
{
 int iLen,iNewSize;
 TCHAR* pNew;

 iLen=_tcslen(this->pStrBuffer);
 if(iLen<this->iAllowableCharacterCount)
 {
    this->pStrBuffer[iLen]=ch;
    this->pStrBuffer[iLen+1]='\0';
 }
 else
 {
    iNewSize=((this->iAllowableCharacterCount*EXPANSION_FACTOR)/16+1)*16;
    pNew=new TCHAR[iNewSize];
    this->iAllowableCharacterCount=iNewSize-1;
    _tcscpy(pNew,this->pStrBuffer);
    delete [] this->pStrBuffer;
    this->pStrBuffer=pNew;
    this->pStrBuffer[iLen]=ch;
    this->pStrBuffer[iLen+1]='\0';
 }

 return *this;
}


String& String::operator+(const TCHAR* pChar) //Overloaded operator + (Adds TCHAR literals
{                                             //or pointers to Asciiz Strings)
 int iLen,iNewSize;
 TCHAR* pNew;

 iLen=_tcslen(this->pStrBuffer)+_tcslen(pChar);
 if(iLen<this->iAllowableCharacterCount)
 {
    if(this->pStrBuffer)
       _tcscat(this->pStrBuffer,pChar);
    else
       _tcscpy(this->pStrBuffer, pChar);
 }
 else
 {
    iNewSize=(iLen*EXPANSION_FACTOR/16+1)*16;
    pNew=new TCHAR[iNewSize];
    this->iAllowableCharacterCount = iNewSize-1;
    if(this->pStrBuffer)
    {
       _tcscpy(pNew,this->pStrBuffer);
       delete [] pStrBuffer;
       _tcscat(pNew,pChar);
    }
    else
       _tcscpy(pNew,pChar);
    this->pStrBuffer=pNew;
 }

 return *this;
}


String& String::operator+(const String& strRight)  //Overloaded operator + Adds
{                                                  //Another String to the left
 int iLen,iNewSize;                                //operand
 TCHAR* pNew;

 iLen=_tcslen(this->pStrBuffer) + _tcslen(strRight.pStrBuffer);
 if(iLen < this->iAllowableCharacterCount)
 {
    if(this->pStrBuffer)
       _tcscat(this->pStrBuffer,strRight.pStrBuffer);
    else
       _tcscpy(this->pStrBuffer,strRight.pStrBuffer);
 }
 else
 {
    iNewSize=(iLen*EXPANSION_FACTOR/16+1)*16;
    pNew=new TCHAR[iNewSize];
    this->iAllowableCharacterCount=iNewSize-1;
    if(this->pStrBuffer)
    {
       _tcscpy(pNew,this->pStrBuffer);
       delete [] pStrBuffer;
       _tcscat(pNew,strRight.pStrBuffer);
    }
    else
       _tcscpy(pNew,strRight.pStrBuffer);
    this->pStrBuffer=pNew;
 }

 return *this;
}
freddie1 (1838)
Here is the 2nd third of Strings.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
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
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
 
String String::Left(unsigned int iNum)
{
 unsigned int iLen,i,iNewSize;
 String sr;

 iLen=_tcslen(this->pStrBuffer);
 if(iNum<iLen)
 {
    iNewSize=(iNum*EXPANSION_FACTOR/16+1)*16;
    sr.iAllowableCharacterCount=iNewSize-1;
    sr.pStrBuffer=new TCHAR[iNewSize];
    for(i=0;i<iNum;i++)
        sr.pStrBuffer[i]=this->pStrBuffer[i];
    sr.pStrBuffer[iNum]='\0';
    return sr;
 }
 else
 {
    sr=*this;
    return sr;
 }
}


String String::Right(unsigned int iNum)  //Returns Right$(strMain,iNum)
{
 unsigned int iLen,i,j,iNewSize;
 String sr;

 iLen=_tcslen(this->pStrBuffer);
 if(iNum<iLen)
 {
    iNewSize=(iNum*EXPANSION_FACTOR/16+1)*16;
    sr.iAllowableCharacterCount=iNewSize-1;
    sr.pStrBuffer=new TCHAR[iNewSize];
    j=0;
    for(i=iLen-iNum;i<=iLen;i++)
    {
        sr.pStrBuffer[j]=this->pStrBuffer[i];
        j++;
    }
    sr.pStrBuffer[iNum]='\0';
    return sr;
 }
 else
 {
    sr=*this;
    return sr;
 }
}


String String::Mid(unsigned int iStart, unsigned int iCount)
{
 unsigned int iLen,i,j,iNewSize;
 String sr;

 iLen=_tcslen(this->pStrBuffer);
 if(iStart && iStart<=iLen)
 {
    if(iCount && iStart+iCount-1<=iLen)
    {
       iNewSize=(iCount*EXPANSION_FACTOR/16+1)*16;
       sr. iAllowableCharacterCount=iNewSize-1;
       sr.pStrBuffer=new TCHAR[iNewSize];
       j=0;
       sr.pStrBuffer=new TCHAR[iNewSize];
       for(i=iStart-1;i<iStart+iCount-1;i++)
       {
           sr.pStrBuffer[j]=this->pStrBuffer[i];
           j++;
       }
       sr.pStrBuffer[iCount]='\0';
       return sr;
    }
    else
    {
       sr=*this;
       return sr;
    }
 }
 else
 {
    sr=*this;
    return sr;
 }
}


String& String::Make(const TCHAR ch, int iCount)    //Creates (Makes) a String with iCount TCHARs
{
 if(iCount>this->iAllowableCharacterCount)
 {
    delete [] pStrBuffer;
    int iNewSize=(iCount*EXPANSION_FACTOR/16+1)*16;
    this->pStrBuffer=new TCHAR[iNewSize];
    this->iAllowableCharacterCount=iNewSize-1;
 }
 for(int i=0; i<iCount; i++)
     pStrBuffer[i]=ch;
 pStrBuffer[iCount]=_T('\0');

 return *this;
}


String& String::GetModulePath()
{
 TCHAR szFileName[MAX_PATH];
 TCHAR* pChar=NULL;
 DWORD dwRet=0;

 dwRet=GetModuleFileName(NULL,szFileName,MAX_PATH);
 if(dwRet)
 {
    pChar=_tcsrchr(szFileName,(int)_T('\\')); //wcsrchr
    szFileName[pChar-(TCHAR*)szFileName]=_T('\0');
    if(dwRet>(unsigned)this->iAllowableCharacterCount)
    {
       delete [] pStrBuffer;
       int iNewSize=(dwRet*EXPANSION_FACTOR/16+1)*16;
       this->pStrBuffer=new TCHAR[iNewSize];
       this->iAllowableCharacterCount=iNewSize-1;
    }
    _tcscpy(this->pStrBuffer,szFileName);
 }

 return *this;
}


String String::Remove(const TCHAR* pToRemove, bool blnCaseSensitive)
{
 int i,j,iParamLen,iReturn=0;
 bool blnFound=false;

 if(*pToRemove==0)
    return *this;
 iParamLen=_tcslen(pToRemove);
 i=0, j=0;
 do
 {
  if(pStrBuffer[i]==0)
     break;
  if(blnCaseSensitive)
     iReturn=_tcsncmp(pStrBuffer+i,pToRemove,iParamLen);  //_tcsncmp
  else
     iReturn=_tcsnicmp(pStrBuffer+i,pToRemove,iParamLen); //__tcsnicmp
  if(iReturn!=0)
  {
     if(blnFound)
        pStrBuffer[j]=pStrBuffer[i];
     j++, i++;
  }
  else   //made a match
  {
     blnFound=true;
     i=i+iParamLen;
     pStrBuffer[j]=pStrBuffer[i];
     j++, i++;
  }
 }while(1);
 if(blnFound)
    pStrBuffer[i-iParamLen]=_T('\0');
 String sr=pStrBuffer;

 return sr;
}


String String::Remove(TCHAR* pStr)
{
 unsigned int i,j,iStrLen,iParamLen;
 TCHAR *pThis, *pThat, *p;
 bool blnFoundBadChar;

 iStrLen=this->LenStr();             //The length of this
 String sr((int)iStrLen);            //Create new String big enough to contain original String (this)
 iParamLen=_tcslen(pStr);            //Get length of parameter (pStr) which contains chars to be removed
 pThis=this->pStrBuffer;
 p=sr.lpStr();
 for(i=0; i<iStrLen; i++)
 {
     pThat=pStr;
     blnFoundBadChar=false;
     for(j=0; j<iParamLen; j++)
     {
         if(*pThis==*pThat)
         {
            blnFoundBadChar=true;
            break;
         }
         pThat++;
     }
     if(!blnFoundBadChar)
     {
        *p=*pThis;
         p++;
        *p=_T('\0');
     }
     pThis++;
 }

 return sr;
}


String String::Retain(TCHAR* pStr)
{
 unsigned int i,j,iStrLen,iParamLen;
 TCHAR *pThis, *pThat, *p;
 bool blnFoundGoodChar;

 iStrLen=this->LenStr();             //The length of this
 String sr((int)iStrLen);            //Create new String big enough to contain original String (this)
 iParamLen=_tcslen(pStr);            //Get length of parameter (pStr) which contains chars to be retained
 pThis=this->pStrBuffer;             //pThis will point to this String's buffer, and will increment through string.
 p=sr.lpStr();                       //p will start by pointing to new String's buffer and will increment through new string
 for(i=0; i<iStrLen; i++)
 {
     pThat=pStr;
     blnFoundGoodChar=false;
     for(j=0; j<iParamLen; j++)
     {
         if(*pThis==*pThat)
         {
            blnFoundGoodChar=true;
            break;
         }
         pThat++;
     }
     if(blnFoundGoodChar)
     {
        *p=*pThis;
         p++;
        *p=_T('\0');
     }
     pThis++;
 }

 return sr;
}


/*
String& String::Retain(TCHAR* pStr)
{
 unsigned int i,j,blnFoundGoodChar,iStrLen;
 TCHAR *pNewStr,*p,*r,*pOrigNewStr;

 pNewStr=new TCHAR[_tcslen(this->pStrBuffer)+1];
 pOrigNewStr=pNewStr;
 memset(pNewStr,_T('\0'),_tcslen(this->pStrBuffer));
 p=pStrBuffer;
 iStrLen=_tcslen(pStr);
 for(i=0; i<_tcslen(this->pStrBuffer); i++)
 {
     r=pStr;
     blnFoundGoodChar=0;
     for(j=0;j<iStrLen;j++)
     {
         if(*p==*r)
         {
            blnFoundGoodChar=1;
            break;
         }
         r++;
     }
     if(blnFoundGoodChar)
     {
        *pNewStr=*p;
        pNewStr++;
        p++;
        *pNewStr=0;
     }
     else
        p++;
 }
 delete [] pStrBuffer;
 pStrBuffer=pOrigNewStr;

 return *this;
}
*/
freddie1 (1838)
Here is the final third of Strings.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
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
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
 
int String::InStr(const TCHAR ch)
{
 int iLen,i;

 iLen=_tcslen(this->pStrBuffer);
 for(i=0;i<iLen;i++)
 {
     if(this->pStrBuffer[i]==ch)
        return (i+1);
 }

 return 0;
}


int String::InStr(const TCHAR* pStr, bool blnCaseSensitive)
{
 int i,iParamLen,iRange;

 if(*pStr==0)
    return 0;
 iParamLen=_tcslen(pStr);
 iRange=_tcslen(pStrBuffer)-iParamLen;
 if(iRange>=0)
 {
    for(i=0;i<=iRange;i++)
    {
        if(blnCaseSensitive)
        {
           if(_tcsncmp(pStrBuffer+i,pStr,iParamLen)==0)   //_tcsncmp
              return i+1;
        }
        else
        {
           if(_tcsnicmp(pStrBuffer+i,pStr,iParamLen)==0)  //__tcsnicmp
              return i+1;
        }
    }
 }

 return 0;
}


int String::InStr(const String& s, bool blnCaseSensitive)
{
 int i,iParamLen,iRange,iLen;

 iLen=_tcslen(s.pStrBuffer);
 if(iLen==0)
    return 0;
 iParamLen=iLen;
 iRange=_tcslen(pStrBuffer)-iParamLen;
 if(iRange>=0)
 {
    for(i=0;i<=iRange;i++)
    {
        if(blnCaseSensitive)
        {
           if(_tcsncmp(pStrBuffer+i,s.pStrBuffer,iParamLen)==0)  //_tcsncmp
              return i+1;
        }
        else
        {
           if(_tcsnicmp(pStrBuffer+i,s.pStrBuffer,iParamLen)==0) //__tcsnicmp
              return i+1;
        }
    }
 }

 return 0;
}


void String::LTrim()
{
 unsigned int i,iCt=0,iLenStr;

 iLenStr=this->LenStr();
 for(i=0;i<iLenStr;i++)
 {
     if(pStrBuffer[i]==32||pStrBuffer[i]==9)
        iCt++;
     else
        break;
 }
 if(iCt)
 {
    for(i=iCt;i<=iLenStr;i++)
        pStrBuffer[i-iCt]=pStrBuffer[i];
 }
}


void String::RTrim()
{
 unsigned int iCt=0, iLenStr;

 iLenStr=this->LenStr()-1;
 for(unsigned int i=iLenStr; i>0; i--)
 {
     if(this->pStrBuffer[i]==9||this->pStrBuffer[i]==10||this->pStrBuffer[i]==13||this->pStrBuffer[i]==32)
        iCt++;
     else
        break;
 }
 this->pStrBuffer[this->LenStr()-iCt]=0;
}


void String::Trim()
{
 this->LTrim();
 this->RTrim();
}


unsigned int String::ParseCount(const TCHAR c)  //returns one more than # of
{                                              //delimiters so it accurately
 unsigned int iCtr=0;                          //reflects # of strings delimited
 TCHAR* p;                                      //by delimiter.

 p=this->pStrBuffer;
 while(*p)
 {
  if(*p==c)
     iCtr++;
  p++;
 }

 return ++iCtr;
}


void String::Parse(String* pStr, TCHAR delimiter)
{
 unsigned int i=0;
 TCHAR* pBuffer=0;
 TCHAR* c;
 TCHAR* p;

 pBuffer=new TCHAR[this->LenStr()+1];
 if(pBuffer)
 {
    p=pBuffer;
    c=this->pStrBuffer;
    while(*c)
    {
     if(*c==delimiter)
     {
        pStr[i]=pBuffer;
        //printf("Assigned pStr[%u] In Parse()\n",i);
        //printf("pStr[%u]=%s\n\n",i,pStr[i].lpStr());
        p=pBuffer;
        i++;
     }
     else
     {
        *p=*c;
        p++;
        *p=0;
     }
     c++;
    }
    pStr[i]=pBuffer;
    delete [] pBuffer;
 }
}


int String::iVal()
{
 return _ttoi(this->pStrBuffer);  //_ttoi
}


String String::CStr(const int iNum)
{
 String sr;
 _stprintf(sr.pStrBuffer,_T("%d"),iNum);
 return sr;
}


String String::CStr(const unsigned int iNum)
{
 String sr;
 _stprintf(sr.pStrBuffer,_T("%u"),iNum);
 return sr;
}


String String::CStr(const DWORD dwNum)
{
 String sr;
 _stprintf(sr.pStrBuffer,_T("%u"),(unsigned)dwNum);
 return sr;
}


String String::CStr(const short int iNum)
{
 String sr;
 _stprintf(sr.pStrBuffer,_T("%d"),iNum);
 return sr;
}


String String::CStr(const double dblNum)
{
 String sr(32);
 _stprintf(sr.pStrBuffer,_T("%f"),dblNum);
 return sr;
}


int String::LenStr(void)
{
 return _tcslen(this->pStrBuffer);
}


TCHAR* String::lpStr()
{
 return pStrBuffer;
}


TCHAR String::GetChar(unsigned int iOffset)
{
 return this->pStrBuffer[iOffset-1];
}


void String::SetChar(unsigned int iOneBasedOffset, TCHAR tcChar)
{
 if((int)iOneBasedOffset<=this->iAllowableCharacterCount)
    this->pStrBuffer[iOneBasedOffset-1]=tcChar;
}


void String::Print(bool blnCrLf)
{
 _tprintf(_T("%s"),pStrBuffer);
 if(blnCrLf)
    _tprintf(_T("\n"));
}


String::~String()   //String Destructor
{
 delete [] pStrBuffer;
 pStrBuffer=0;
}
freddie1 (1838)
Here is Sql.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
 
//Sql.h
#if !defined(SQL_H)
#define SQL_H

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

 public:
 String            strConnectionString;
 String            strDatabase;
 String            strDriver;
 String            strServer;
 String            strDBQ;
 TCHAR             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


Here is SqlProcs.h

1
2
3
4
5
6
7
8
9
 
//SqlProcs.h
#ifndef SQL_PROCS_H
#define SQL_PROCS_H

unsigned int iInstallerError();
void MkDate(TIMESTAMP_STRUCT&, TCHAR*);
TIMESTAMP_STRUCT ParseDate(TCHAR*, TCHAR*, TCHAR*);

#endif


Here is Strings.h (Header For My String Class)

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
 
//Strings.h
#if !defined(STRINGS_H)
#define STRINGS_H
#define EXPANSION_FACTOR      2
#define MINIMUM_ALLOCATION    8

class __declspec(dllexport) String
{
 public:
 String();                                 //Uninitialized Constructor
 String(const TCHAR);                      //Constructor Initializes String With TCHAR
 String(const TCHAR*);                     //Constructor Initializes String With TCHAR*
 String(const String&);                    //Constructor Initializes String With Another String (Copy Constructor)
 String(const int);                        //Constructor Initializes Buffer To Specific Size
 String(const TCHAR ch, const int iCount); //Constructor initializes String with int # of chars
 String& operator=(const TCHAR);           //Assigns TCHAR To String
 String& operator=(const TCHAR*);          //Assigns TCHAR* To String
 String& operator=(const String&);         //Assigns one String to another (this one)
 String& operator+(const TCHAR);           //For adding TCHAR to String
 bool operator==(const String);            //For comparing Strings
 String& operator+(const TCHAR*);          //For adding null terminated TCHAR array to String
 String& operator+(const String&);         //For adding one String to Another
 String Left(unsigned int);                //Returns String of iNum Left Most TCHARs of this
 String Right(unsigned int);               //Returns String of iNum Right Most TCHARs of this
 String Mid(unsigned int, unsigned int);   //Returns String consisting of number of TCHARs from some offset
 String& Make(const TCHAR ch, int iCount); //Creates (Makes) a String with iCount TCHARs
 String& GetModulePath();                  //Returns reference To String Containing Path of Exe/dll running
 String Remove(const TCHAR*, bool);        //Returns A String With A Specified TCHAR* Removed
 String Remove(TCHAR* pStr);               //Returns A String With All The TCHARs In A TCHAR* Removed (Individual char removal)
 String Retain(TCHAR* pStr);               //Seems to return a String with some characters retained???
 int InStr(const TCHAR);                   //Returns one based offset of a specific TCHAR in a String
 int InStr(const TCHAR*, bool);            //Returns one based offset of a particular TCHAR pStr in a String
 int InStr(const String&, bool);           //Returns one based offset of where a particular String is in another String
 void LTrim();                             //Returns String with leading spaces/tabs removed
 void RTrim();                             //Returns String with spaces/tabs removed from end
 void Trim();                              //Returns String with both leading and trailing whitespace removed
 unsigned int ParseCount(const TCHAR);     //Returns count of Strings delimited by a TCHAR passed as a parameter
 void Parse(String*, TCHAR);               //Returns array of Strings in first parameter as delimited by 2nd TCHAR delimiter
 String CStr(const int);                   //Converts String to integer
 String CStr(const unsigned int);          //Converts String to unsigned int
 String CStr(const DWORD);
 String CStr(const short int);             //Converts String to 16 bit int
 String CStr(const double);                //Converts String to double
 int iVal();                               //Returns int value of a String
 int LenStr(void);                         //Returns length of string
 TCHAR* lpStr();                           //Returns address of pStrBuffer member variable
 TCHAR GetChar(unsigned int);              //Returns TCHAR at one based index
 void SetChar(unsigned int, TCHAR);        //Sets TCHAR at one based index
 void Print(bool);                         //Outputs String to Console with or without CrLf
 ~String();                                //String Destructor

 private:
 TCHAR* pStrBuffer;
 int    iAllowableCharacterCount;
};

#endif
peripheral (58)
thank you very much . freddie1

I compiled all your files.but there are many " 
1
2
3
4
5
6
 
 
[Linker error] undefined reference to `SQLFreeHandle@8' 
[Linker error] undefined reference to `SQLDriverConnect@32' 

[Linker error] undefined reference to `SQLBindCol@24' 
.....

do I need to install some SQL ？
freddie1 (1838)
My guess is that you havn't set the link paths correctly. All those functions beginning with SQL***** are in various ODBC dynamic link libraries on your system. Did you see my directions at the top of Main.cpp which I'll reproduce again here...

1
2
3
4
5
6
7
 
Libraries Needed To Link Against For This program.
Add to Linker Settings In Whichever Development
Environment You Are Using.  If A Microsoft Product
The Libraries Won't Start With The Prefix lib.

..\..\..\Program Files\CodeBlocks\MinGW\lib\libodbccp32.a
..\..\..\Program Files\CodeBlocks\MinGW\lib\libodbc32.a


Which compiler and development environment are you using Peripheral? The above directions will work for the GNU stuff. In whichever development environment you are using you need to link the program against thise libraries so the linker can resolve the externals. If you are using a Microsoft product the two libraries are odbc32.lib and odbccp32.lib. These references need to be set in your development environment or you'll get the linker errors you mentioned.

For example with Visual Studio you need to go to....

Project >>> Properties >>> Linker >>> Command Line

and add those two libs in the additional options area at the bottom, then click 'OK'.

With CodeBlocks, Dev-C++ and other development environments, the GUI for setting this is a bit different. Once you get it set though you'll be able to compile fine. Let me know how you are doing.

That code I posted for you I ripped out of an ODBC demo and tutorial I posted here...

http://www.jose.it-berater.org/smfforum/index.php?board=378.0

That tutorial uses direct low level ODBC to create and connect to Microsoft Access databases, SQL Server, Excel, and it dumps ODBC data sourses installed on the users computer. The code I posted wouldn't be so long if I hadn't wrapped the ODBC connection functions in a class wrapper which includes my own String class. If I hadn't of done that it would probably have only been 150 lines of code or so, but what I posted I created in only a few minutes for you. Anyway, that doesn't really relate to your linker errors.
Last edited on
EverBeginner (91)
I just simply want to read one column data from a .xls file.

Beg you pardon.
This file form is for your convinience to read humanly. I deeply in doubt that those who made it up had dreamt of it as something else. It is filled with colorful formats, miriads fonts
and sized, formulas, tables, e.t.c. No html editor compares to its richness.
It has even its own built language system that can import almost anything as well as
export.
kbw (9488)
You need to link with odbc32.lib.
freddie1 (1838)
As stated by EverBeginner...


Beg you pardon.
This file form is for your convinience to read humanly. I deeply in doubt that those who made it up had dreamt of it as something else. It is filled with colorful formats, miriads fonts
and sized, formulas, tables, e.t.c. No html editor compares to its richness.
It has even its own built language system that can import almost anything as well as
export.


Microsoft Excel is probably one of the most used applications in the whole world! Everybody stores everything from data to the kitchen sink in it. Therefore, it is not at all an unreasonable request that someone with programming skills should be able to read data out of it programatically. The fact of the matter is that C++ is one of the worst possible choices to do that. In fact, when I first gave it some thought, it occurred to me that C++ might be the worst language in the entire world for reading data out of Excel. For a bit I thought this indeed might be true. Then out of the blue it occurred to me that C++ wasn't the worst language for reading data out of Excel - assembler would even be worse! No sooner than I thought of that I realized that even the difficulties of assembler could be exceeded if one worked in machine code directly to read data out of Excel! So that's at least a comforting thought for someone who likes C++ and that is that it isn't the very worst language in the world for reading data out of Excel, not even the next to the worst, but rather the 3rd from the worst!

The fact of the matter is that if reading data out of Excel spreadsheets is something you have to do a lot of in your work you would be way ahead to get yourself another language product to do it such as a .NET something or other (VB, C#, whatever). In most high level languages it can easily be done in a few lines of code. As I think kbw said the real way to do it is through COM. I provided the ODBC code I did above because I had it handy, and IDispatch COM implementations in C++ are truely miserable (its the interface from h*** for C++ programmers). I use PowerBASIC for most of my desktop Windows programming because it does both low level assembler type stuff plus high level too, and I can call any Windows Api function I care to. Here is a PowerBASIC program that reads the exact Excel data I provided in the ODBC demo above, but using early binding IDispatch COM...

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
 
#Compile Exe
#Dim All
#Include "Win32Api.inc"
#Include "PBExcel.inc"

Function PBMain() As Long
  Local ptrExcelApp As XL_Application, oExcelWorkbook As XL_Workbook, oExcelWorkSheet As XL_WorkSheet
  Local vFileName,oVnt,vVnt,vVal As Variant
  Local strCell,strDate As String
  Local szBuffer As Asciiz*256
  Local nBufferLength As Dword
  Local st As SYSTEMTIME
  Register i As Long
  Register j As Long

  ptrExcelApp = Newcom "Excel.Application"
  If IsObject(ptrExcelApp) Then
     Print "Successfully Opened Excel!"
     nBufferLength=256
     Call GetCurrentDirectory(nBufferLength,szBuffer)
     Print szBuffer
     vFileName = szBuffer & "\Book1.xls"
     Object Call ptrExcelApp.WorkBooks.Open(vFileName) To oVnt
     Set oExcelWorkbook = oVnt
     If IsObject(oExcelWorkbook) Then
        Print "Successfully Opened Workbook!"
        vVnt="Sheet1"
        Object Call oExcelWorkbook.Sheets(vVnt).Select
        Object Get oExcelWorkbook.ActiveSheet To oVnt
        Set oExcelWorkSheet = oVnt
        Object Get oExcelWorkSheet.Name To oVnt
        Print "Sheet Name = " + Variant$(oVnt)
        Print
        For i=1 To 5
          For j=65 To 68
            strCell = Chr$(j) & Trim$(Str$(i))
            vVnt=strCell
            Object Get oExcelWorkSheet.Range(vVnt).Value To vVal
            Select Case As Long VariantVt(vVal)
              Case %VT_I4
                Print Variant#(vVal) "    ";
              Case %VT_R8
                Print Variant#(vVal) "    ";
              Case %VT_DATE
                Call VariantTimeToSystemTime(Variant#(vVal),st)
                strDate=Trim$(Str$(st.wMonth)) & "\" & Trim$(Str$(st.wDay)) & "\" & Trim$(Str$(st.wYear))
                Print strDate & "    ";
              Case %VT_BSTR
                Print Variant$(vVal) "    ";
            End Select
          Next j
          Print
        Next i
        Object Call oExcelWorkbook.Close
        Set oExcelWorkSheet=Nothing
        Set oExcelWorkbook=Nothing
     Else
        Print "    Failed To Open Workbook!"
     End If
     Object Call ptrExcelApp.Quit()
     Set ptrExcelApp=Nothing
  Else
     Print "Failure Opening Excel!"
  End If
  WaitKey$

  PBMain=0
End Function

'Successfully Opened Excel!
'C:\Code\PwrBasic\PBCC50\Excel
'Successfully Opened Workbook!
'Sheet Name = Sheet1
'
'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!


C++ is a wonderful and powerful language, but its just not the best choice for some things, and this is one of them.
Last edited on
EverBeginner (91)
freddy1, could you please direct me where to obtain above mentioned *.inc files along with
manuals?

Thanks in advance.
Pages: 12