Click here to Skip to main content
Click here to Skip to main content

Creating Excel Sheets using ODBC

, 12 Jan 2000
Rate this:
Please Sign up or sign in to vote.
Writing to Excel spreadsheets using only ODBC
<!-- Article Starts -->

The Problem

Many apps offer an export function. So wouldn´t it be nice to be able to easily save that result as an Excel sheet?

ODBC does make this possible, but there´s 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 you´d have to install that DSN locally on every machine that should support your export function.

The 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 don´t 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 isn´t installed an exception gets thrown.

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

What is Needed

In order to get the code below going you have to

  • have included
  • have an installed ODBC-driver called "MICROSOFT EXCEL DRIVER (*.XLS)"

The 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;
    // Build the creation string for access without DSN
                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)";

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

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

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

    // Close database
    TRACE1("Driver not installed: %s",sDriver);


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

No Biography provided

Comments and Discussions

QuestionNeed explanation PinmemberTijanaPavlovic26-Dec-13 21:58 
QuestionHow can add column in existing table that also contains some record? PinmemberLe@rner15-Nov-11 0:07 

here how can add column in existing table that also contains some record.

GeneralMy vote of 2 PingroupMahdi Nejadsahebi22-Oct-11 3:21 
GeneralColumn selection PinmemberRoger Handwerker11-Jun-11 0:07 
QuestionHow to control the column width? Pinmemberdanlei012020-Apr-11 21:59 
GeneralData type - custom date Pinmemberflorinvistig14-Jan-10 2:51 
GeneralGood Work! Pinmemberprasad0210-Dec-08 7:16 
GeneralProblem with long data field Pinmemberyongdiego29-Jul-08 15:13 
GeneralRe: Problem with long data field PinmemberAlexander Tsoi24-Oct-08 0:29 
QuestionWriting to Multiple Sheets Pinmembergana15-Nov-07 23:47 
AnswerRe: Writing to Multiple Sheets PinmemberJamesHoward97228-Dec-11 6:43 
General10 for you PinmemberIBrana18-Jul-07 18:07 
QuestionAccessing Excel via ADO while the sheet is open Pinmemberk1n6b0b6-Jun-07 10:35 
QuestionCan I create an Access databae file (.mdb) using the same principle? [modified] Pinmemberpani6827-Dec-06 23:44 
GeneralWhich one first execute please tell me Pinmemberesambath29-Nov-06 0:02 
GeneralI want the code for Ms project Pinmemberjesukaran john silvester27-Jul-06 20:56 
GeneralAdding records PinmemberSangeetha Jayaraman19-Apr-06 3:38 
GeneralMs Excel PinsussAnonymous25-Oct-05 22:18 
GeneralInsert into A1 PinmemberJuergen Klingler18-Oct-05 0:27 
Questionwhat to include? Pinmemberdrekon5-Sep-05 10:38 
Questionwhat to include Pinmemberdrekon5-Sep-05 10:38 
Generalso simple so good ! PinmemberLeonidPh31-Aug-05 5:02 
Generalgood thank u Pinmembervikas amin23-Aug-05 22:31 
Questionhow to insert more than one table in same sheet Pinsussshsak6-May-04 23:49 
AnswerRe: how to insert more than one table in same sheet Pinmemberphucid9-Oct-05 18:06 
Questionchange font style and colors in spreadsheet? Pinsussxxhimanshu23-Feb-04 20:11 
GeneralWorks, but why not this?: Pinmemberkleinb21-Apr-03 21:33 
GeneralRe: Works, but why not this?: Pinmemberpolofreak8-Jun-05 0:46 
GeneralODBC excel driver PinsussGianluca Nastasi24-Mar-03 0:25 
GeneralRe: ODBC excel driver PinmemberHerschel S. Krustofsky11-Apr-03 9:55 
GeneralPlain C Version Available PinsussAnonymous19-Feb-03 5:08 
GeneralRe: Plain C Version Available PinmemberBilloKhan18-Nov-05 16:34 
GeneralRe: Plain C Version Available Pinmemberliaohaiwen10-Apr-08 20:10 
GeneralStoring date and time values in Excel using ODBC PinmemberKlaus Kurt9-Jan-03 6:41 
Generalsingle quote mark PinmemberMartin Step10-Jul-02 0:34 
GeneralRe: single quote mark Pinmemberoneleg14-Mar-05 2:46 
Generaloverwriting cells in existing table PinmemberAnonymous26-Jun-02 2:26 
GeneralRe: overwriting cells in existing table PinmemberObliterator11-Jul-02 4:31 
Generalexcel template PinmemberJohnny007_24/710-Jun-02 8:49 
General... PinmemberAnonymous10-Jun-02 8:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150414.1 | Last Updated 13 Jan 2000
Article Copyright 2000 by Alexander Mikula
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid