Printing to Excel

• Download source files - 24 Kb

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

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.

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.