When I wrote my latest application, I had to find a way to print data from a CListCtrl.
Since I am not very familiar with printing in MFC, I thought about exporting the data
to Excel and leaving all the formating and printing to Excel.
My first attempt was to create a CSV-file with all required data. It worked fine, but
users complained that they had to do the formating all by themselves. So I tried to find
out how to create XLS-files.
With Excel 2003 it is possible to import XML-files. So I created an XML-file and analyzed it.
The result is a new class that can help generating Excel-files from with your MFC-Application.
How it works
Excel-XML-files contain a number of predefined styles. Each cell that is defined needs its own style. So when using different format-options, you may end up with a large number
of styles. These styles have to be defined in the beginning of your XML-file.
Furthermore, you have to define every column that you use in your XML file.
And you need to know exactly how many lines of data you will have in your XML-file.
So whenever you create an XML-file, a file in your temp-folder is created. Excel can be opened with this XML-file and afterwards you may delete your temporary XML-file.
For compatibility reasons, I left the creation of CSV-files in my class, in case you do not
have Excel installed on your system, but want to export your data.
The first step is to add the files Excel.cpp and Excel.h to your project. Include Excel.h
in your source wherever you want to create XML-files.
To start the export you have to declare a variable of type
CExcel. The constructor can also be
used with a part of a filename. All temporary files will begin with this string. You
can create your own files in your temp-folder. The default for the beginning of your files is XLS.
The next step is to add all the columns you want to have in your file:
AddCloumn() for every additional column you need. You can specify the width of this column. If you do
not specify a value, Excel uses a default.
The Next step is a bit tricky. You have to define the styles you want to use. Every cell of data may have its own style, fontsize and datatype.
AddStyle() returns a unique identifier for every style you create:
The above example creates a style (stored in
bold) which simply sets the font to bold.
Center centers the value horizontally and vertically.
bolddate will hold the identifier for a style that displays a bold date. Finally,
large sets the fontsize to 16 pt.
You can use a combination of these flags in setting your styles:
XLS_NOSTYLE No change in style
XLS_HCENTER horizontaly centered
XLS_HLEFT left aligned
XLS_HRIGHT right aligned
XLS_VTOP aligned at the top of the cell
XLS_VCENTER vertically centered
XLS_VBOTTOM aligned at the bottom of the cell
XLS_WRAP text is wrapped to fit in the cell
XLS_BORDER there is a single line border around the cell
XLS_BOLD the font is bold
XLS_LINE the font is underlined with a single line
XLS_ITALICS the font is italics
XLS_DATE the cell contains a date
XLS_TIME the cell contains a time
XLS_CURRENCY the cell contains a currency
Each combination of these flags result in a new style. CExcel stores the styles in a CStringArray,
so there is no limitation of styles.
After defining all styles and adding all columns, you can start to create your XML-file
Excel needs to know the number of lines before the lines are written, so you have to specify
this number now. You also have to give a name for the Excel-worksheet.
If you use
Open() without parameters, no XML-file will be created. Instead a CSV-file will be created.
Open() writes a lot of XML-header stuff in your file, defines all the styles and columns you
After opening the file, you can add rows of data. Each row must begin with
This begins a new row. The row is in the bold-style, and has a height of 25.3. If you do not specify any values, Excel-defaults are used. If you simply want to set the new height and don't want to use a style, observe the following example:
After a row is started, you can add values to every cell in your row:
export.SetCell("This is for strings");
You can also specify a style:
export.SetCell("this will be bold",bold);
You can also use a
SetCell(). However, you have to specify a style defining
CTime as date or time:
When you have set all values for a cell, you have to end your row with the following command:
export.EndRow(); Now you can start your next row.
When you have finished with all data, you have to close your file using:
This means all XML-tags will be closed and the temporary file is saved.
export.Run(); will open Excel with your XML-file. Or with your CSV-file, if you did not specify parameters when opening your file.
to delete a CSV-file after showing. XML-files should not be deleted when Excel is still running,
instead in your
Here is a complete example:
CExcel export("MYTMP"); int x_bold,x_center,x_date,x_money; CTime now=CTime::GetCurrentTime();
(XLS_HCENTER|XLS_VCENTER|XLS_WRAP); x_date=export.AddStyle(XLS_DATE); x_money=export.AddStyle(XLS_CURRENCY);
export.NewRow(x_bold,23); export.SetCell("Date"); export.SetCell("Income");
export.EndRow(); export.NewRow(); export.EndRow();
export.NewRow(); export.SetCell(now,x_date); export.SetCell(72637.23,x_money); export.SetCell("This is just long text,
which will be centered and wrapped around",x_center);
export.Close(); export.Run(); }
March 7th, 2007 First release
Juergen Wagner just lives in Augsburg, Germany and writes applications to help him out in his job as CIO in a mental hospital.