Easy creation of Excel Sheets using only the ODBC

Problem

Many apps fancy an export function. So wouldn4t it be nice to be able to easily save that result you4ve got there as an Excel sheet?
ODBC does make this possible, but there4s one little drawback: Using ODBC the usual way there has to be a registered datasource (DSN) in the ODBC manager.
This is not very useful because you4d have to install that DSN locally on every machine that should support your export function.

Solution

Omiting the DSN tag in the connect string of CDatabase:OpenEx() gives us the opportunity to refer the ODBC-Driver directly using its name so we don4t have to have a DSN registered.
This, of course, implies that the name of the ODBC-Driver is exactly known.

If you just want to test if a certain driver is present (to show the supported extensions in the CFileOpenDlg for example) just try to CDatabase:OpenEx() it.
If it isn4t installed an exception gets thrown.

To create and write to that Excel sheet you simply use SQL as shown in the code sample below.

Needed

In order to get the code below going you have to

  1. have <afxdb.h> included
  2. have an installed ODBC-driver called "MICROSOFT EXCEL DRIVER (*.XLS)"
Source code

// this example creates the Excel file C:\DEMO.XLS, puts in a worksheet with two
// columns (one text the other numeric) an appends three no-sense records.   
//  
void MyDemo::Put2Excel()
{
  CDatabase database;
  CString sDriver = "MICROSOFT EXCEL DRIVER (*.XLS)"; // exactly the same name as in the ODBC-Manager
  CString sExcelFile = "c:\\demo.xls";                // Filename and path for the file to be created
  CString sSql;
    
  TRY
  {
    // Build the creation string for access without DSN
       
    sSql.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s", sDriver,sExcelFile,sExcelFile);

    // Create the database (i.e. Excel sheet)
    if( database.OpenEx(sSql,CDatabase::noOdbcDialog) )
    {
      // Create table structure
      sSql = "CREATE TABLE demo (Name TEXT,Age NUMBER)";
      database.ExecuteSQL(sSql);

      // Insert data
      sSql = "INSERT INTO demo (Name,Age) VALUES ('Bruno Brutalinsky',45)";
      database.ExecuteSQL(sSql);

      sSql = "INSERT INTO demo (Name,Age) VALUES ('Fritz Pappenheimer',30)";
      database.ExecuteSQL(sSql);

      sSql = "INSERT INTO demo (Name,Age) VALUES ('Hella Wahnsinn',28)";
      database.ExecuteSQL(sSql);
    }      

    // Close database
    database.Close();
  }
  CATCH_ALL(e)
  {
    TRACE1("Driver not installed: %s",sDriver);
  }
  END_CATCH_ALL;
}



Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Savvy enterprises are discovering that the cloud holds the power to transform IT processes and support business objectives. IT departments can use the cloud to redefine the continuum of development and operations—a process that is becoming known as DevOps. Download the Executive Brief DevOps: Why IT Operations Managers Should Care About the Cloud—prepared by Frost & Sullivan and sponsored by IBM—to learn how IBM SmartCloud Application services provide a robust platform that streamlines …

  • "Security" is the number one issue holding business leaders back from the cloud. But does the reality match the perception? Keeping data close to home, on premises, makes business and IT leaders feel inherently more secure. But the truth is, cloud solutions can offer companies real, tangible security advantages. Before you assume that on-site is the only way to keep data safe, it's worth taking a comprehensive approach to evaluating risks. Doing so can lead to big benefits.

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds