Create Excel-files with a simple class

• Download Excel.zip - 8.0 KB

Introduction

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.

Steps

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.

CExcel export("TST");

The next step is to add all the columns you want to have in your file:

export.AddColumn(120.3);
export.AddColumn();

Use 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:

int bold,center,bolddate,large;

bold=export.AddStyle(XLS_BOLD);
center=export.AddStyle(XLS_HCENTER|XLS_VCENTER);
bolddate=export.AddStyle(XLS_BOLD|XLS_DATE);
large=export.AddStyle(XLS_NOSTYLE,"16");

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 with:

export.Open(12,"Test");

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 added.

After opening the file, you can add rows of data. Each row must begin with NewRow().

export.NewRow(bold,25.3);

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:

export.NewRow(XLS_NOSTYLE,40.2);

After a row is started, you can add values to every cell in your row:

export.SetCell("This is for strings");
export.SetCell(129.3); // for float
export.SetCell(2); // for integer

You can also specify a style:

export.SetCell("this will be bold",bold);
export.SetCell(2,large); // a really big 2
export.SetCell(23.33,currency); // as far as you have defined currency-style

You can also use a CTime-object in SetCell(). However, you have to specify a style defining the CTime as date or time:

int aTime=export.AddStyle(XLS_TIME|HCENTER);
CTime now;

export.SetCell(now,aTime); // a time horizontally centered

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: export.Close();

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.

Use

export.Delete();

to delete a CSV-file after showing. XML-files should not be deleted when Excel is still running, so use:

CExcel export;
export.DeleteAll();

instead in your OnClose() function.

Complete Example

Here is a complete example:

void OnExport()
{
    CExcel export("MYTMP");             // Create Files starting with MYTMP
    int x_bold,x_center,x_date,x_money; // all the styles I want
    CTime now=CTime::GetCurrentTime();  // this is now

    export.AddColumn();                 // first column    
    export.AddColumn();    
    export.AddColumn(123.3);            // a wide column

    x_bold=export.AddStyle(XLS_BOLD,"16");     // a bold-Font, 16 pt
    x_center=export.AddStyle
        (XLS_HCENTER|XLS_VCENTER|XLS_WRAP);    // Centered text
    x_date=export.AddStyle(XLS_DATE);          // used to format a date
    x_money=export.AddStyle(XLS_CURRENCY);     // used for currencies

    export.Open(3,"Example");           // Open the file with 3 rows

    export.NewRow(x_bold,23);           // a row of bold Text
    export.SetCell("Date");             // some headlines
    export.SetCell("Income");
    export.SetCell("Why");
    export.EndRow();                    // End of first row
    export.NewRow();                    // an empty line
    export.EndRow();
    export.NewRow();                    // next row
    export.SetCell(now,x_date);         // the CTime formatted as date
    export.SetCell(72637.23,x_money);   // a float value formatted as
                                        // currency
    export.SetCell("This is just long text, 
        which will be centered and wrapped around",x_center);
    export.EndRow();
    export.Close();                     // closing the file
    export.Run();                       // starting Excel and display data
}

void OnClose()
{
    CExcel export("MYTMP");
    export.DeleteAll();            // Delete all export-files
    CDialog::OnClose();
}

History

March 7th, 2007 First release