Reading Excel Data in VC++

Question

 

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

Answer 1

  Hello!

Below is a function that read 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......

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 );

}