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