There has been the C++ project BasicExcel for quite few years now. It is very usefull to read and write Excel sheets in .xls
format. However is contains only very basic functionality. There are missing features like text formating using fonts, display formats and formulas are not yet supported. This article covers this additional functionality. The new, derived project ExcelFormat
is based on the old BasicExcel code and just adds what I was missing for my own needs. If you want to see, wehre I am using the new library, you can look at the Export functions of Service Manager and Uninstall Manager.
This library processes Excel files in the BIFF8
XLS file format. For a detailes documentation of the format and all its internas there are two documents available:
OpenOffice provides the OpenOffice.org's Documentation of the Microsoft Excel File Format.
Microsoft published the Microsoft Office Binary File Formats a few years later.
To use the new formating functionality, first create an XLSFormatManager
object, like in the example1()
function and attach it to an existing BasicExcel
object:
void example1(const char* path) { BasicExcel xls; // create sheet 1 and get the associated BasicExcelWorksheet pointer xls.New(1); BasicExcelWorksheet* sheet = xls.GetWorksheet((size_t)0); XLSFormatManager fmt_mgr(xls);
To define a custom font, create an ExcelFont
object and set any needed properties, for example the font weight for a bold font:
ExcelFont font_bold; font_bold._weight = 700; // 700=bold, 400=normal
The format of an Excel cell can be defined by a CellFormat
object, which holds the chosen font and some more properties:
CellFormat fmt_bold(fmt_mgr); fmt_bold.set_font(font_bold);
After you have prepared the CellFormat
, you can choose the font and display settings of Excel cells by calling SetFormat()
:
// Create a table containing an header row in bold and four rows below. size_t col, row = 0; for(col=0; col<10; ++col) { BasicExcelCell* cell = sheet->Cell(row, col); cell->Set("TITLE"); cell->SetFormat(fmt_bold); } while(++row < 4) { for(size_t col=0; col<10; ++col) sheet->Cell(row, col)->Set("text"); } ++row;
Text color is specified by setting color indizes in ExcelFont
, for example:
ExcelFont font_red_bold; font_red_bold._weight = 700; // bold font_red_bold._color_index = 2; // red CellFormat fmt_red_bold(fmt_mgr, font_red_bold); CellFormat fmt_green(fmt_mgr, ExcelFont().set_color_index(3)); // green for(col=0; col<10; ++col) { BasicExcelCell* cell = sheet->Cell(row, col); cell->Set("xxx"); cell->SetFormat(fmt_red_bold); cell = sheet->Cell(row, ++col); cell->Set("yyy"); cell->SetFormat(fmt_green); }
After that, all you have to do, is to save the new Excel sheet as file:
xls.SaveAs(path); }
This should be enough for a quick overview on how to use the new ExcelFormat objects. In the source code file ExcelFormat.cpp
you can find some more examples.
There are available this nine font properties:
struct ExcelFont { ExcelFont() ... wstring _name; short _height; short _weight; short _options; short _color_index; short _escapement_type; char _underline_type; char _family; char _character_set; ... };
For in detail information please look into the XLS file format documentation.
There are available this CellFormat
properties in addition to the chosen ExcelFont
and the display format:
struct CellFormat { CellFormat(XLSFormatManager& mgr) ... char _alignment; char _rotation; char _text_props; int _borderlines; int _color1; short _color2; ... };
In example2()
you can see how to change font face and font size:
ExcelFont font_header; font_header.set_weight(700); // bold font_header.set_underline_type(1); // underlined font_header.set_font_name(L"Times New Roman"); // font face "Times New Roman" font_header.set_color_index(4); // blue font_header._options = 8; // strike out CellFormat fmt_header(fmt_mgr, font_header); fmt_header.set_rotation(30); // rotate the header cell text 30° to the left size_t row = 0; for(size_t col=0; col<10; ++col) { BasicExcelCell* cell = sheet->Cell(row, col); cell->Set("TITLE"); cell->SetFormat(fmt_header); }
The example3()
function shows how to use CellFormat::set_format_string()
and BasicExcelCell::SetFormat()
to define text, number and date format strings. There are some predefined constants for format strings:
#define XLS_FORMAT_GENERAL L"General" #define XLS_FORMAT_TEXT L"@" #define XLS_FORMAT_INTEGER L"0" #define XLS_FORMAT_DECIMAL L"0.00" #define XLS_FORMAT_PERCENT L"0%" #define XLS_FORMAT_DATE L"M/D/YY" #define XLS_FORMAT_TIME L"h:mm:ss" #define XLS_FORMAT_DATETIME L"M/D/YY h:mm"
However you can use any valid Excel format string to define custom display formats.
Formulas are now preserved when reading and writing an Excel sheet. You can even store a formula into an Excel cell by calling BasicExcelCell::SetFormula()
. However currently you either have to copy a Worksheet::CellTable::RowBlock::CellBlock::Formula
object from an already existing cell, or you have to define it yourself, which is quite tricky because Excel uses a pre-parsed formula containing RPN
tokens.
For those, who are interested in a bit of background information, I want to provide a description, what has been changed since the predecessor library BasicExcel.
There are two different BasicExcel implementations, one for VC++ version 6.0 and one for newer compilers. ExcelFormat now merges this two code bases by using conditional compilation to handle the differences. Most of this is accomplished in the header file by this code snippet (and modifications using the new defined macros):
#if _MSC_VER<=1200 // VC++ 6.0 #pragma warning(disable: 4786) #define LONGINT __int64 #define LONGINT_CONST(x) x #define COMPOUNDFILE #else // newer MS compilers #define LONGINT long long #define COMPOUNDFILE CompoundFile:: #define LONGINT_CONST(x) x##LL #endif
The #pragma warning
statement disables compiler warnings of VC++ 6.0 because of long compiler internal names in object files, which occur for non-trivial usage of STL classes.
Immediately after this defines, there are this preprocessor statements for VS 2005 and above:
#if _MSC_VER>=1400 // VS 2005 #define _CRT_SECURE_NO_WARNINGS //MF #define _SCL_SECURE_NO_WARNINGS //MF #endif
They disable the VC++ security library warnings, because the BasicExcel code is not yet prepared for this new runtime library additions. By the way - this MF
comments mark additions and fixes of myself to the old BasicExcel code.
Additionally I eliminated some compiler warnings by fixing data types. Typically this was a change from integral types like int
to the C runtime library type size_t
.
In addition to the new API described above, I had to add code in BasicExcel in order to implement the new features:
- read and write the Formula
structure from/to XLS files
- define, read, and write the FORMAT structure from/to XLS files
- get/set the XF index values BasicExcelCell
objects for all the various cell types
The information about the format of an Excel cell in the BIFF8 file format is stored using a so called XF index
. This refers to an XF record, which consists of the following members:
XF { short fontRecordIndex // FORMAT index short formatRecordIndex // FONT index short protectionType char alignment char rotation char textProperties char usedAttributes int borderLines int colour1 short colour2 }Besides a few directly associated attributes specifiying alignment, rotation, etc. of the Excel cell, there are two more index values:
fontRecordIndex
and formatRecordIndex
.
These are used to define font and display format descriptions. In the whole picture this two-level index based formating architecture enables cell formating with
small file sizes and low memory usage, because there are typically only a few different fonts and display formats used in an Excel worksheet.
The font index referes to a FONT record with the following attributes:
FONT { short height short options short colourIndex short weight short escapementType char underlineType char family char characterSet char unused SmallString name }The third index is a special one. This format index is associated with a record containing only the index itself and a text representation of the display format:
FORMAT { short index LargeString fmtstring }XLSFormatManager manages this this three formating substructures to format Excel cells using the C++ structures CellFormat and ExcelFont:
struct CellFormat -> XF {FORMAT index, FONT index, XF attributes} struct ExcelFont -> FONT {FONT attributes}On calling
CellFormat::set_font(const ExcelFont& font)
, the manager class searches for an already registered matching font description. If there is none yet, a new FONT record is created
to be stored in the Excel sheet. On calling CellFormat::set_format_string(const wstring& fmt_str)
, the manager class searches for an already registered matching display format string.
If there is none yet, a new FORMAT record stores the display format string.
The same strategy is used when applying a CellFormat to a cell object by calling BasicExcelCell::SetFormat(const CellFormat& fmt)
: The manager class searches for an already registered matching XF description with identical
font and format indizes as well as matching XF attributes. If there is none yet, a new XF record is created to be stored in the Excel sheet.
In the end, this results in a list of FORMAT, FONT and XF records, which are stored in the header if the Excel workbook file. Each cell is formated by storing a single related XF index, which
determines all the cell formating attributes, the font and the display format.
For further implementation details, please have a look into BasicExcel.hpp
and BasicExcel.cpp
of the source code.
To limit memory usage, a reference counting SmartPtr
is used to manage the heap structures needed for formula storage. Every cell contains only an optionally filled pointer to struct Formula
. For text or number cells, formula information is not needed. So the smart pointer stays empty and just contains the value NULL
.
In the following section you can see the implementation of the struct RefCnt
as base for struct Formula
and the template struct SmartPtr
to hold the reference counted heap objects:
// reference counter for SmartPtr managed objects struct RefCnt { // On construction the reference counter is initialized with an usage count of 1. RefCnt() : _ref_cnt(1) { } int _ref_cnt; }; // reference counting smart pointer template<typename T> struct SmartPtr { SmartPtr(T* p=NULL) : _ptr(p) { } // The copy constructor increments the reference counter. SmartPtr(const SmartPtr& other) : _ptr(other._ptr) { if (_ptr) ++_ptr->_ref_cnt; } // The destructor decreases the reference counter and // frees the managed memory as the counter reaches zero. ~SmartPtr() { if (_ptr) { if (!--_ptr->_ref_cnt) delete _ptr; } } // The assignment operator increments the reference counter. SmartPtr& operator=(T* p) { if (_ptr) { if (!--_ptr->_ref_cnt) delete _ptr; _ptr = NULL; } if (p) { _ptr = p; ++_ptr->_ref_cnt; } return *this; } // operator bool() to check for non-empty smart pointers operator bool() const {return _ptr != NULL;} // operator!() to check for empty smart pointers bool operator!() const {return !_ptr;} // operator->() to access the managed objects T* operator->() {return _ptr;} private: T* _ptr; };
There are some new string conversion functions: stringFromSmallString()
, stringFromLargeString()
, wstringFromSmallString()
, wstringFromLargeString()
using narrow_string()
/widen_string()
are used convert from internal Excel string structures to STL string classes and vice versa. You can use them to access the internal data storage of BasicExcel.
20.09.2009 - Version 1.0
28.09.2009 - new chapter Formating storage structures