In this section we demonstrate how support for additional handlers can be added. Much of this information can be applied to adding support for tables, too.
The reader is fully extensible. If there is a field that you would like to read you just have to decide whether it is to be a table (global data) or handler (local data). Tables usually have globally accessible data referenced by index. You will normally not have to add support for additional tables. Adding support for handlers will probably be more common.
Stripped of trivial data members and such, CGXExcelHandler looks like this:
class CGXExcelHandler : public CObject
{
// data declarations
enum gxexType
{
nullPtr,
corePtr,
stylePtr,
paramPtr,
dataPtr
};
// nested classes
struct gxexpointer
{
// default is core pointer
gxexpointer(void* pv);
gxexpointer();
gxexpointer(CGXGridCore* p);
virtual ~gxexpointer();
// data
gxexType vt;
union
{
CGXGridCore* pCore;
CGXGridParam* pParam;
CGXStyle* pStyle;
CGXData* pData;
};
};
// operations
// Read/Write from/to stream
virtual BOOL ReadFromStream(IStream* pStream,
gxexpointer p, DWORD dwLength);
virtual BOOL WriteToStream(IStream* pStream,
gxexpointer p, DWORD& dwLength, UINT nTabIndex = 0);
// logging support
// members ommited
// public for the base class
virtual void ResetInternalState() = 0;
// static functions
protected:
virtual BOOL DelegateHelper(gxexpointer p, BOOL bStore = FALSE);
// actual init functions
virtual BOOL InitializeStyle(CGXStyle* pStyle,
BOOL bStore = FALSE);
virtual BOOL InitializeCore(CGXGridCore* pCore,
BOOL bStore = FALSE);
virtual BOOL InitializeParam(CGXGridParam* pParam,
BOOL bStore = FALSE);
virtual BOOL InitializeData(CGXData* pData, BOOL bStore = FALSE);
};
|
Let us take a brief look at the nested data structures gxexType and gxexpointer. gxexType denotes the type of pointer that is contained inside the gxexpointer structure. This structure gets passed in from the read and write code when the object, which is derived from CGXExcelHandler, is asked to decipher the field. The actual type of the object can change with the field being interpreted. This is normally not the case, however, and the most probable use if this will contain a CGXGridCore pointer. However, if you need an object that does not need access to the core pointer but operates on any other grid data structure, the support for handling this is already built in.
The initialize functions correspond to the data structures that are supported by default. As we mentioned earlier, the structure that is normally used is the CGXGridCore pointer and the corresponding initialization function is InitializeCore().
To better understand this, look at the sequence of what happens during a typical read operation. The reader code reads the type of field and attempts to locate a handler for it. If a handler is found, it stuffs the CGXGridCore pointer that it wants initialized with data that is interpreted in the form of a gxexpointer and calls the object's ReadFromStream() method (assuming that this is a handler and not a table).
The object would (inside its ReadFromStream() code) read the data structure that pertains to this field and then interpret and apply this to the CGXGridCore object that was passed in. To make this a little more systematic and easier to extend, the actual read and application to the object take place as two steps. First, the actual read takes place. Next, the object calls its base class. Finally, the base class resolves the type of initialization function that is to be called to initialize the object (based on the type of the object as specified by the gxexType (vt) member). The DelegateHelper() function is responsible for this. The following code should clarify this:
BOOL CGXExcelHandler::DelegateHelper(CGXExcelHandler::gxexpointer p,
BOOL bStore /*=FALSE*/)
{
BOOL b(FALSE);
switch(p.vt)
{
case CGXExcelHandler::corePtr:
{
b = InitializeCore(p.pCore, bStore);
break;
}
case CGXExcelHandler::stylePtr:
{
b = InitializeStyle(p.pStyle, bStore);
break;
}
case CGXExcelHandler::paramPtr:
{
b = InitializeParam(p.pParam, bStore);
break;
}
case CGXExcelHandler::dataPtr:
{
b = InitializeData(p.pData, bStore);
break;
}
default:
{
TRACE0("Error in CGXExcelHandler::DelegateHelper. Unrecognized
format\n");
}
return b;
}
|
Now let us step back and look at how the data read is performed. To make things simpler we have a read mechanism that you can also use in your derived handlers. (You are not limited to this and can read the stream in any manner that you find convenient.)
Typical read code from ReadFromStream() is shown below:
static GXREADSEEKDATA readData[6]; readData[0].Init(&m_lStruct.row); //row readData[1].Init(&m_lStruct.col); //column readData[2].Init(&m_lStruct.ixfe); //index to XF record readData[3].Init(&m_lStruct.wLength); // Length of the string readData[4].Init(&m_lStruct.grbit); //grbit flags GXEX_TRY(ReadSeekHelper(pStream, &readData[0], 5)) |
GXREADSEEKDATA can contain any (common) kind of data that needs to be read in. This can be initialized with the structures that are to hold the data to be read in. These are normally members of a structure that is a member of this class. You can declare variables on the stack if you have cases where the data does not need to persist until the initialization functions are called to initialize the data. (One typical example would be the length of a string. Once the string is read in, this data does not need to be retained.)
ReadSeekHelper() can then be called to read this data from the stream and fill the data members that have been passed in. ReadSeekHelper() takes care of checking for errors, much like Data Exchange in dialogs.
Writing is very similar. WriteToStream() is called. The object that has the data that is to be used to compose the record is passed in as a CGXExcelHandler::gxexpointer. Here you can choose types that you want to handle and reject others.
BOOL CGXExcelWindow2Handler::WriteToStream(IStream* pStream, CGXExcelHandler::gxexpointer p, DWORD& dwLength, UINT nTabIndex /*= 0*/) |
A helper implementation that can ease the process of writing out data is shown below:
BYTE pszBuffer[GX_EX_SIZEOF_WINDOW2];
_gxdata(wr)
_gxw(typeWindow2)
_gxw(GX_EX_SIZEOF_WINDOW2 - 4)
_gxw(ws.grbit)
_gxw(ws.rwTop)
_gxw(ws.colLeft)
_gxdw(64)
_gxw(ws.wScaleSLV)
_gxw(ws.wScaleNormal)
_gxdw(0) // reserved
_gxend
ULONG cb(0);
_gxExWrite(wr, pszBuffer);
GX_EX_WRITEEX(pStream->Write(pszBuffer, GX_EX_SIZEOF_WINDOW2,
&cb));
GXEXDUMP(pszBuffer, GX_EX_SIZEOF_WINDOW2)
dwLength+=cb;
|
The above code is self explanatory. You can compose a map containing the structures and values that you would like to be written out. These can then be written to a buffer with a call to _gxExWrite(wr, pszBuffer). This buffer can be written to the stream.
One key aspect to remember is to increment the DWORD parameter dwLength that is passed in by reference. This count is used to compute various offsets that get written into the file. There is no surer way to get Microsoft Excel to go down than to forget to increment this count.
From what was detailed above, we can list the overrides that are essential for adding support for a new record type.
virtual BOOL ReadFromStream(IStream* pStream, gxexpointer p, DWORD dwLength); virtual BOOL WriteToStream(IStream* pStream, gxexpointer p, DWORD& dwLength, UINT nTabIndex = 0); virtual BOOL InitializeCore(CGXGridCore* pCore, BOOL bStore = FALSE); |
One other method needs to be overridden, the pure virtual function ResetInternalState():
virtual void ResetInternalState() = 0; |
In your override of this function, clean up any data that you allocate to hold data that is read in or written out. For example, if you had allocated a string buffer to read in a string, delete it in this override. This will be called for clean up.
The essential logic is much the same for tables. The overrides are:
virtual BOOL ReadFromStream(IStream* pStream, DWORD dwLength); virtual BOOL WriteToStream(IStream* pStream, DWORD& dwLength, CGXGridParam** pParam, UINT nTabs = 1); virtual BOOL ApplyToObject(DWORD index, CGXStyle*& pStyleFont); virtual void ResetInternalState() = 0; |
The major differences in the implementation logic are explained below:
While handlers are typically used to manipulate CGXGridCore objects, tables are usually associated with a type of object at compile time. They manipulate this object alone. For example, the XF field corresponds to CGXStyle in the grid and correspondingly the XF table manipulates CGXStyle objects. This is specified at compile time by deriving from CGXExcelBaseTable as shown below.
class CGXExcelXFTable : public CGXExcelBaseTable<XFStruct, CGXStyle> |
CGXExcelBaseTable provides certain memory management features for derived classes. It allocates and tracks objects of both types passed as template arguments on demand.
A reason for this difference in implementation is the manner in which tables are applied to data when compared to handlers. Tables are almost never applied directly. In fact, you will notice that the ReadFromStream() override for tables does not take any objects pointers. Instead, tables are read in and retained in memory. When a handler needs the services of a table it looks it up and then calls the special function ApplyToObject(). ApplyToObject() takes two parameters, one an index into the table and the other a pointer to a object that can be modified by this table. (The meaning of the index depends on the table, but it is usually interpreted as a zero based index into the table.)
One way to call ApplyToObject() is to allocate an object of this type and then ask the table to apply the data that it has at the index specified to the passed in object. Another way to call it is to simply pass a NULL pointer. The base class will perform the requisite memory management and return a fully composed instance of this object with the data at the specified index.
The purpose of ResetInternalState() is much the same as it is with handlers. One difference is that it is called at the end of the cumulative read or write operation and not after each record is accessed. This is because once a table is read in it must remain accessible to all records that are read in after that. Once the read or write is complete, the table can be freed and ResetInternalState() will be called to do just that.
The following Excel fields are supported by the Objective Grid library. For a description of these fields, please refer to the Excel SDK documentation.
FONT STYLE XF PALETTE FORMAT SST BOUNDSHEET LABEL RK MULRK COLINFO ROW FORMULA BOF BLANK MULBLANK SST DIMENSIONS WINDOW2 MERGEDCELL |
The above records form the core of the BIFF file structure. Almost all of the other records can be added with ease, based on the current architecture.
While we write a good subset of Objective Grid features out in Excel file format, we do not include all of the Excel fields. This means that you might not be able to pull a rich native Excel file in its entirety into Objective Grid. The emphasis of the current implementation is on being able to write out Objective Grid data as .xls rather than on being able to read all the Excel fields. We do leave this extensible to allow users to add additional fields as required.
We would appreciate your feedback on the basic structure. In addition please advise us of other essential fields that you would like added as defaults in future releases of this product.
If you change the background color of cells in your grid, try to stick to Excel's default color palette. When you set the background color of a cell in the grid to certain colors—like RGB(255, 255, 159) for example— and then you copy and paste those cells from a grid into Excel, their background shows up as black in Excel.
If you search the Excel help about colors, you will see information about setting the Excel color palette. If you go to the Tools | Options menu in Excel and click the Color tab, you will see the palette of colors Excel is using. Excel has a palette of 40 standard colors that you can use for setting the background color of a cell (these are the 40 boxes above the line in the Color tab). You may modify any of these 40 colors and redefine them to whatever you wish. However, the palette of 40 colors that you see is the default Excel palette, and it is only these colors that are supported as cell background colors. Although RGB(255, 255, 159) may be a true color in the Windows sense, it still isn't in the Excel default palette. If the exact color you have chosen for the grid cell is in the Excel default palette, it will show correctly in Excel. If there is no exact match, Excel maps it to black. Excel does not seem to use a 'nearest-color' match; it's either an exact match or it's black.
The Excel documentation goes on to say that you can redefine any of the colors of the palette and save it with the workbook. This would seem at first to solve the problem, but it does not. The problem is that the XLS file is completely generated from the grid information and there is no good deterministic way for the grid to alter the default palette to suit your custom color needs. If you happen to have more than 40 different background colors for your grid cells, you will never be able to see all of them in Excel, no matter what you do to the palette in the XLS file; it just isn't possible. Therefore, the black color is simply a limitation of Excel.
The bottom line is: if you want Excel to reflect your color settings, stick to colors in the default Excel palette.
If you want to remove only the changed background color for a cell style put in place with a SetInterior() call, you can simply call SetIncludeInterior(FALSE).
Copyright © Rogue Wave Software, Inc. All Rights Reserved.
The Rogue Wave name and logo, and Stingray, are registered trademarks of Rogue Wave Software. All other trademarks are the property of their respective owners.
Provide feedback to Rogue Wave about its documentation.