Reading Excel Data in VC++

[AdrianMorris]

Hello All,

I am trying to read an Excel spreadsheet (xls) programmatically. I have used
the ODBC database route and have opened the Excel spreadsheet and then using
a 'CRecordset', I read each row.

I correctly get that I have 13 columns, their names, etc... The problem
comes when I am reading the data. I use the function 'GetFieldValue'. It
correctly retrieves all data for each cell where numbers are stored as text.
If a cell has a number that has been formatted as a number with thousand
separators, etc, I always get null data returned. I have also tried forcing
the 'GetFieldValue' function to use a specific data format...have tried every
combination, but if I try read it as anything other than text, I get an
error. If I use the 'GetODBCFieldInfo' function, it returns that each cell in
the spreadsheet is text....

Where am I going wrong?


Regards.....Adrian

 

[AdrianMorris]

Below is a function that reads an xls spreadsheet that is on the C drive
called 'FullStats05092008.xls'. I compiled it as a win32 consol aplication
with
#include "stdafx.h"
#include <odbcinst.h>
#include <afxdb.h>

It reads each line and prints to the consol......
If one of the cells being read has been formatted as a number, it reads it
as a null string.....



int ReadExcelFile( void )

{

CDatabase *Database;

CRecordset *Sheet;

int TotalColumns, TotalRows, column;

CString DriverString, RowString, CellValue, SqlCmd;

CString ExcelDriver, xlsFile;

Database = new CDatabase;

DriverString = "DRIVER={Microsoft Excel Driver
(*.xls)};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"C:\\FullStats05092008.xls\";DBQ=C:\\FullStats05092008.xls";

Database->OpenEx(DriverString, CDatabase::noOdbcDialog);

// Open Sheet

Sheet = new CRecordset( Database );

SqlCmd = "SELECT * FROM [Sheet1$A1:IV65536]";

try

{

Sheet->Open(CRecordset::forwardOnly, SqlCmd, CRecordset::readOnly);

}

catch(...)

{

delete Sheet;

Sheet = NULL;

Database->Close();

return( false );

}

TotalColumns = Sheet->m_nResultCols;

TotalRows = 0;

while (!Sheet->IsEOF())

{

TotalRows++; // Keep count of total number of rows

try

{

// Get all the columns in a row

RowString.Empty();

for (column = 0; column < TotalColumns; column++)

{

Sheet->GetFieldValue(column, CellValue);

RowString += CellValue + " ";

}

RowString += "\r\n";

printf( RowString );

Sheet->MoveNext();

}

catch (...)

{

printf( "Error reading row\n" );

delete Sheet;

Sheet = NULL;

Database->Close();

return( false );

}

}



Sheet->Close();

delete Sheet;

Sheet = NULL;

Database->Close();


return( true );

}