Click here to Skip to main content
15,867,308 members
Articles / Desktop Programming / MFC
Article

Printing to Excel

Rate me:
Please Sign up or sign in to vote.
4.00/5 (8 votes)
20 Apr 20022 min read 251.3K   3.9K   55   42
This article shows how to send data to Excel instead of a printer.

Introduction

Printing lists to a printer is a normal thing. But it is better if you can send data to Excel so the user of a business application is able to make changes, send the sheet via e-mail, and so on. Sending data to excel via ODBC is not the best thing, because there is no formatting possible (borders, colours, fonts, ...).

The compiled EXE-file was testet with Win95, Win98, WinNT, Win2000, WinXP and Excel95, Excel97, Excel2000. The developing system was Win98 with Office 97.

How does the system work?

Every report has the following scheme (per page):

  • section: HEADER
  • section: POSITION
  • section: FOOTER

Image 1

When you send data to excel you have to transform the CRect-structures to rows and columns. The best way is to use only the CRect-structures from the POSITION section for transforming them to columns.

How to use the class CPrintExcel?

  • Append the two files PrintExcel.cpp and PrintExcel.h to your project.

  • Make sure you go to Tools.Options.Directories and add the paths to mso97.dll and vbeext1.olb. Mso97.dll will usually be in c:\Program Files\Microsoft Office\Office, and vbeext1.olb will be in c:\Program Files\Common Files\Microsoft Shared\VBA

    When you look to the demoproject you will find the function: CTestExcelView::OnMenuExcel()

  • The Object PrintExcel is created.

  • The CRect-Elements are transformed to columns.

  • All the data is sent to Excel.

After all this the function

printingToExcel.PrintExcel (ID_EXCEL_CLOSE, NULL, "Clientreport", 0, NULL, ID_FORMAT_PORTRAIT);

is called.

ID_EXCEL_CLOSE
sets the sheetname to "Clientreport", printing in portrait-mode and shows Excel.

Image 2

The function PrintExcel has the following parameters:

nFunction

is a text, a font, a line, a background sent

pRect

the rect-structure you use for printing

cText

the text to print

cref

the backgroundcolor or the textcolor

logfont

information for the font to set

nFormat

justify left, right, center, use masks like "#,##0.00"

when you look at PrintExcel.h you will find the constants for formatting text und the different possible Excel-functions.

As you can see, the body for the functions CTestExcelView::OnDraw(CDC* pDC) and CTestExcelView::OnMenuExcel() is nearly the same. So it should be easy to expand your application with this functionality.

Additional information:

Only the array A## - ZZ## will be transformed, that means there are up to 56 Columns available. It is no problem to expand this. The source for calculating the cols and rows is not difficult, so it is easy to adapt the code to your specific reports.

German uses have to set the variable m_nGerman = TRUE, because the decimal sign must be changed from "," to ".".

English users: Look at "int CPrintExcel::PrintExcel (int nFunction, ..." (File PrintExcel.cpp). You will find

pSheet->Range[COleVariant(cCell)]->PutNumberFormat (COleVariant("#.##0,00"));

If you don't use a German version of Excel it may be necessary to change these lines to " (COleVariant("#,##0.00")", because the German version uses a different decimal sign. I was not able to test this, because I only have a German version.

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

 
QuestionHow to import excel8.olb?? Pin
xiaosongxia12-May-04 11:25
xiaosongxia12-May-04 11:25 
AnswerRe: How to import excel8.olb?? Pin
Franz Maerzinger12-May-04 22:23
Franz Maerzinger12-May-04 22:23 
GeneralRe: How to import excel8.olb?? Pin
xiaosongxia14-May-04 15:46
xiaosongxia14-May-04 15:46 
GeneralRe: How to import excel8.olb?? Pin
Franz Maerzinger15-May-04 21:36
Franz Maerzinger15-May-04 21:36 
GeneralRe: How to import excel8.olb?? Pin
briantmali14-Dec-06 14:55
briantmali14-Dec-06 14:55 

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.