Click here to Skip to main content
6,305,776 members and growing! (17,396 online)
Email Password   helpLost your password?
Database » Database » General     Intermediate

Creating Excel Sheets using ODBC

By Alexander Mikula

Writing to Excel spreadsheets using only ODBC
SQL, VC6, SQL Server, Visual Studio, DBA, Dev
Posted:12 Jan 2000
Views:167,596
Bookmarked:72 times
Announcements
Loading...
 
Search    
Advanced Search
printPrint   Broken Article?Report       add Share
  Discuss Discuss   Recommend Article Email
46 votes for this article.
Popularity: 7.45 Rating: 4.48 out of 5
1 vote, 5.6%
1

2
1 vote, 5.6%
3
4 votes, 22.2%
4
12 votes, 66.7%
5

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

About the Author

Alexander Mikula


Member

Location: Austria Austria

Other popular Database articles:

Article Top
You must Sign In to use this message board.
FAQ FAQ 
 
Noise Tolerance  Layout  Per page   
 Msgs 1 to 25 of 33 (Total in Forum: 33) (Refresh)FirstPrevNext
GeneralGood Work! Pinmemberprasad027:16 10 Dec '08  
GeneralProblem with long data field Pinmemberyongdiego15:13 29 Jul '08  
GeneralRe: Problem with long data field PinmemberAlexander Tsoi0:29 24 Oct '08  
QuestionWriting to Multiple Sheets Pinmembergana23:47 15 Nov '07  
General10 for you PinmemberIBrana18:07 18 Jul '07  
QuestionAccessing Excel via ADO while the sheet is open Pinmemberk1n6b0b10:35 6 Jun '07  
GeneralCan I create an Access databae file (.mdb) using the same principle? [modified] Pinmemberpani6823:44 27 Dec '06  
GeneralWhich one first execute please tell me Pinmemberesambath0:02 29 Nov '06  
GeneralI want the code for Ms project Pinmemberjesukaran john silvester20:56 27 Jul '06  
GeneralAdding records PinmemberSangeetha Jayaraman3:38 19 Apr '06  
GeneralMs Excel PinsussAnonymous22:18 25 Oct '05  
GeneralInsert into A1 PinmemberJuergen Klingler0:27 18 Oct '05  
Generalwhat to include? Pinmemberdrekon10:38 5 Sep '05  
Generalwhat to include Pinmemberdrekon10:38 5 Sep '05  
Generalso simple so good ! PinmemberLeonidPh5:02 31 Aug '05  
Generalgood thank u Pinmembervikas amin22:31 23 Aug '05  
Generalhow to insert more than one table in same sheet Pinsussshsak23:49 6 May '04  
GeneralRe: how to insert more than one table in same sheet Pinmemberphucid18:06 9 Oct '05  
Generalchange font style and colors in spreadsheet? Pinsussxxhimanshu20:11 23 Feb '04  
GeneralWorks, but why not this?: Pinmemberkleinb21:33 21 Apr '03  
GeneralRe: Works, but why not this?: Pinmemberpolofreak0:46 8 Jun '05  
GeneralODBC excel driver PinsussGianluca Nastasi0:25 24 Mar '03  
GeneralRe: ODBC excel driver PinmemberHerschel S. Krustofsky9:55 11 Apr '03  
GeneralPlain C Version Available PinsussAnonymous5:08 19 Feb '03  
GeneralRe: Plain C Version Available PinmemberBilloKhan16:34 18 Nov '05  

General General    News News    Question Question    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

PermaLink | Privacy | Terms of Use
Last Updated: 12 Jan 2000
Editor: Chris Maunder
Copyright 2000 by Alexander Mikula
Everything else Copyright © CodeProject, 1999-2009
Web19 | Advertise on the Code Project