Click here to Skip to main content
15,867,308 members
Articles / Database Development / SQL Server
Article

Creating Excel Sheets using ODBC

Rate me:
Please Sign up or sign in to vote.
4.69/5 (25 votes)
12 Jan 2000 341.4K   106   42
Writing to Excel spreadsheets using only ODBC

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 <afxdb.h> 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;
    
  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;
}

License

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


Written By
Austria Austria
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionDelete command odbc Pin
Member 1109944418-Jun-16 16:34
Member 1109944418-Jun-16 16:34 
QuestionExcel 2010--not working Pin
diptipanchal13-Oct-15 20:50
diptipanchal13-Oct-15 20:50 
QuestionNeed explanation Pin
TijanaPavlovic26-Dec-13 20:58
TijanaPavlovic26-Dec-13 20:58 
QuestionHow can add column in existing table that also contains some record? Pin
Le@rner14-Nov-11 23:07
Le@rner14-Nov-11 23:07 
GeneralMy vote of 2 Pin
Mahdi Nejadsahebi22-Oct-11 2:21
Mahdi Nejadsahebi22-Oct-11 2:21 
GeneralColumn selection Pin
Roger Handwerker10-Jun-11 23:07
Roger Handwerker10-Jun-11 23:07 
QuestionHow to control the column width? Pin
danlei012020-Apr-11 20:59
danlei012020-Apr-11 20:59 
GeneralData type - custom date Pin
florinvistig14-Jan-10 1:51
florinvistig14-Jan-10 1:51 
GeneralGood Work! Pin
prasad0210-Dec-08 6:16
prasad0210-Dec-08 6:16 
GeneralProblem with long data field Pin
yongdiego29-Jul-08 14:13
yongdiego29-Jul-08 14:13 
GeneralRe: Problem with long data field Pin
Alexander Tsoi23-Oct-08 23:29
Alexander Tsoi23-Oct-08 23:29 
QuestionWriting to Multiple Sheets Pin
Gana15-Nov-07 22:47
Gana15-Nov-07 22:47 
AnswerRe: Writing to Multiple Sheets Pin
JamesHoward97228-Dec-11 5:43
JamesHoward97228-Dec-11 5:43 
General10 for you Pin
IBrana18-Jul-07 17:07
IBrana18-Jul-07 17:07 
QuestionAccessing Excel via ADO while the sheet is open Pin
k1n6b0b6-Jun-07 9:35
k1n6b0b6-Jun-07 9:35 
QuestionCan I create an Access databae file (.mdb) using the same principle? [modified] Pin
pani6827-Dec-06 22:44
pani6827-Dec-06 22:44 
GeneralWhich one first execute please tell me Pin
esambath28-Nov-06 23:02
esambath28-Nov-06 23:02 
GeneralI want the code for Ms project Pin
jesukaran john silvester27-Jul-06 19:56
jesukaran john silvester27-Jul-06 19:56 
GeneralAdding records Pin
Sangeetha Jayaraman19-Apr-06 2:38
Sangeetha Jayaraman19-Apr-06 2:38 
GeneralMs Excel Pin
Anonymous25-Oct-05 21:18
Anonymous25-Oct-05 21:18 
GeneralInsert into A1 Pin
Orgen Kl17-Oct-05 23:27
Orgen Kl17-Oct-05 23:27 
Questionwhat to include? Pin
drekon5-Sep-05 9:38
drekon5-Sep-05 9:38 
Questionwhat to include Pin
drekon5-Sep-05 9:38
drekon5-Sep-05 9:38 
Generalso simple so good ! Pin
LeonidPh31-Aug-05 4:02
LeonidPh31-Aug-05 4:02 
Generalgood thank u Pin
vikas amin23-Aug-05 21:31
vikas amin23-Aug-05 21:31 

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

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