Quite often we need data to be displayed in tabular form. We use list control to display such data. So wouldn’t it be nice if we could save the data displayed in the list control directly in an Excel sheet? ODBC does make this possible. However, there is one drawback. To be able to use ODBC there has to be a registered data source (DSN) in the ODBC manager. If the required DSN is not installed you program will crash. This program provides the solution. In this program we will see how to read from and write to an Excel spreadsheet without having a DSN registered.
Create a dialog-based application ‘excel’. Add the controls to the dialog template as shown in the following figure
Give the IDs and add the variables to the controls as shown in the following table.
|
|
|
|
| Filename Edit box | IDC_EDIT1 | Value | CString |
| Table Edit box | IDC_EDIT2 | Value | CString |
| List Control | IDC_LIST1 | Control | m_list |
| CreateFile button | IDC_CREATEFILE | ||
| Add button | IDC_ADD | ||
| Show button | IDC_SHOW |
When you click on ‘Add’ button another dialog should appear. Create this dialog in Resource Editor and add three edit boxes to the dialog template as shown below.
Insert a new class called adddialog for this dialog by selecting ‘Insert | New Class’. Add variables for the controls present in the dialog. The list of variables is given in the following table.
|
|
|
|
| Name Edit box | IDC_NAME | Value | CString |
| Age Edit box | IDC_AGE | Value | int |
| Salary Edit box | IDC_SALARY2 | Value | int |
Add the following variables to CExcelDlg class.
CDatabase m_database ;
CRecordset *m_rc ;
CString m_driver ;
#include ‘afxdb.h’ file in ‘excelDlg.h’ file. Add columns to the list controls and initialise variables in CExcelDlg::OnInitDialog( ) handler as shown below:
BOOL CExcelDlg::OnInitDialog( )
{
m_list.InsertColumn ( 0, "Name", LVCFMT_LEFT, 150 ) ;
m_list.InsertColumn ( 1, "Age", LVCFMT_LEFT, 80 ) ;
m_list.InsertColumn ( 2, "Salary", LVCFMT_LEFT, 100 ) ;
m_rc = new CRecordset ( &m_database ) ;
m_driver = "MICROSOFT EXCEL DRIVER (*.XLS)" ;
return TRUE ;
}
The driver name stored in the variable m_driver should be exactly the same name as in the ODBC-Manager.
The user should first enter the filename with extension ‘.xls’ and table name in the edit boxes. Then the user should click on the ‘CreateFile’ button. The ‘.xls’ file would get created in the current directory if path is not specified. Here, table name specifies the sheet name. Add a handler for the ‘CreateFile’ button and add code in it as shown below:
void CExcelDlg::OnCreatefile( )
{
UpdateData ( TRUE ) ;
CString sql ;
sql.Format ( "DRIVER={%s};DSN='';READONLY=FALSE; CREATE_DB=\"%s\";DBQ=%s", driver, m_filestr, m_filestr ) ;
try
{
m_database.OpenEx ( sql, CDatabase::noOdbcDialog ) ;
sql.Format ( "CREATE TABLE %s (Name TEXT, Age NUMBER, Salary NUMBER)", m_tablestr ) ;
m_database.ExecuteSQL ( sql ) ;
m_database.Close( ) ;
}
catch ( CDBException e )
{
MessageBox ( "Driver not installed: %s", driver ) ;
}
}
Here, firstly we have created a SQL statement specifying the driver, DSN and the filename. We have created the database file by calling CDataBase::OpenEx( ) function. We have passed the SQL string as the first parameter to this function. In the second parameter we have specified not to display ODBC connection dialog box regardless of whether enough connection information is supplied. Next, we have created the SQL statement to create a table in the file. In this statement we specify the table name entered by the user, fields and the type of data to be displayed in the columns. We have executed the SQL command by calling CDatabase::ExecuteSQL( ) function. We have then disconnected from the data source. After creating the file and the table we can now add the records in it. Add a handler for the ‘Add’ button. Write code in it as shown below:
void CExcelDlg::OnAdd( )
{
UpdateData ( TRUE ) ;
if ( m_filestr.IsEmpty( ) || m_tablestr.IsEmpty( ) )
{
MessageBox ( "Please enter filename and table name" ) ;
return ;
}
adddialog d ;
if ( d.DoModal( ) != IDOK )
return ;
CString name = d.m_name ;
int age = d.m_age ;
int sal = d.m_salary ;
CString sql ;
sql.Format ("DRIVER={%s};DSN='';READONLY=FALSE;DBQ=%s", m_driver, m_filestr ) ;
try
{
m_database.OpenEx ( sql, CDatabase::noOdbcDialog ) ;
sql.Format ( "INSERT INTO %s (Name,Age,Salary) VALUES ('%s','%d', '%d')", m_tablestr, name, age, sal ) ;
m_database.ExecuteSQL ( sql ) ;
m_database.Close( ) ;
}
catch ( CDBException e )
{
MessageBox ( "Driver not installed: %s", m_driver ) ;
}
}
Here, we have retrieved the file and table entered by the user by calling UpdateData ( TRUE ) function. Then we have displayed a dialog box wherein user can enter the record. Do not forget to #include ‘adddialog.h’ file in ‘excelDlg.cpp’ file. We have retrieved the data entered by the user in Add dialog and stored it in local variables. We have opened the file using OpenEx( ) function. After that, we have created the SQL command to insert the record in the table. After we have inserted the record we have closed the data source by calling CDatabase::Close( ) function.
Once the records are inserted in the file we can list them in the list control. For this, add a handler for ‘Show’ button. The OnShow( ) handler is given below:
void CExcelDlg::OnShow( )
{
m_list.DeleteAllItems( ) ;
UpdateData ( TRUE ) ;
CString sql, str ;
sql.Format ( "DRIVER={%s};DSN='';READONLY=TRUE;DBQ=%s",m_driver, m_filestr ) ;
try
{
if ( m_database.OpenEx ( sql, CDatabase::noOdbcDialog ) )
{
sql.Format ( "SELECT * FROM %s", m_tablestr ) ;
m_database.ExecuteSQL ( sql ) ;
m_rc -> Open( CRecordset::snapshot, sql, CRecordset::none ) ;
int count = 0 ;
while ( m_rc -> IsEOF( ) != 1 )
{
m_rc -> GetFieldValue ( "Name", str ) ;
m_list.InsertItem ( count, str ) ;
m_rc -> GetFieldValue ( "Age", str ) ;
m_list.SetItemText ( count, 1, str ) ;
m_rc -> GetFieldValue ( "Salary", str ) ;
m_list.SetItemText ( count, 2, str ) ;
m_rc -> MoveNext( ) ;
count++ ;
}
}
m_rc -> Close( ) ;
m_database.Close( ) ;
}
catch ( CDBException e )
{
MessageBox ( "Driver not installed: %s", m_driver ) ;
}
}
Here, firstly we have cleared the previously listed items if any from the list control. Then we have opened the file to read the records, created SQL command and executed it. Since we use CRecordset object to operate on the records we have opened the connection to data source using a pointer to CRecordset object. The values of the record are retrieved using CRecordset::GetFieldValue( ) function and inserted in the list control using member functions of CListCtrl class. We have browsed all the records until end of file is encountered using MoveNext( ) member function of CRecordset class. Following figure shows the records read from the Excel Worksheet.
No comments:
Post a Comment