locked
Reading Excel Data in VC++ RRS feed

  • 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

    Friday, September 12, 2008 2:05 PM

All replies

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

    }

    Monday, September 15, 2008 10:06 AM