Usage Samples
In this section we will discuss various usage scenarios for the Excel read/write code. The following samples ship with the product:
1. An application that presents a workbook interface, enabling this application to save data in Excel file format. Sample: <stingray-installdir>\Grid\Excel\ExRdFor.
2. A grid in a dialog that supports Excel read/write. Sample: <stingray-installdir>\Grid\Excel\ExDlg.
3. Adding Excel read/write capability to an existing grid view or window. We will also discuss adding Excel clipboard support to an existing application. Sample: <stingray-installdir>\Grid\Excel\ExToExist.
Let us now look at these scenarios in greater detail.
Sample 1
To follow along with this sample, please refer to the exdlg sample that is installed under the Samples\Grid\Excel folder of your install.
1. Start with the application class implementation. Change the base class so that the application class additionally derives from CExcelReadAdapter and CGXExMapHolder.
class CExrdApp : public CWinApp, public CExcelReadAdapter,
public CGXExMapHolder
2. Define CExcelReadAdapter in your application’s header (exrd.h in sample) as shown below.
// derive from the abstract CGXAppAdapter
class CExcelReadAdapter : public CGXAppAdapter
{
public:
CExcelReadAdapter();
virtual ~CExcelReadAdapter();
CGXNodeManager<CGXTabWndMgr>* CreateTabWndHolder();
virtual CDocTemplate* GetDocTemplate(CRuntimeClass*
pViewClass, CRuntimeClass* pDocClass);
protected:
CMultiDocTemplate* m_pDocTemplate;
};
3. Implement this class as shown below.
// Implementation of CExcelReadAdapter,
// the application adapter for this application.
CExcelReadAdapter::CExcelReadAdapter()
{
m_pDocTemplate = NULL;
}
CExcelReadAdapter::~CExcelReadAdapter()
{
if(m_pDocTemplate != NULL)
{
m_pDocTemplate->CloseAllDocuments(TRUE);
delete m_pDocTemplate;
}
}
// you have to override this function and return a
// CDoctemplate class that is appropriate for this
// view and document. The frame is unimportant.
// Please note that you have to keep track of and
// delete all instances of CDocTemplates that you create
// here.
CDocTemplate* CExcelReadAdapter::GetDocTemplate(CRuntimeClass*
pViewClass, CRuntimeClass* pDocClass)
{
if (m_pDocTemplate != NULL)
return m_pDocTemplate;
m_pDocTemplate = new CMultiDocTemplate(
IDR_EXRDTYPE,
RUNTIME_CLASS(CExrdDoc),
RUNTIME_CLASS(CGXExcelChildFrame), // custom MDI child frame
RUNTIME_CLASS(CExrdView));
return m_pDocTemplate;
}
CGXNodeManager<CGXTabWndMgr>*
CExcelReadAdapter::CreateTabWndHolder()
{
return new
CGXNodeManager<CGXTabWndMgr>(RUNTIME_CLASS(CGXExTabWndMgr));
}
The implementation of this class does not vary much except for the part that specifies the run-time classes of the objects (shown above in bold) and can be copied from project to project for the most part. This can also be replaced with any other compatible implementation that suits your needs.
4. Add the following to the top of the application’s .cpp file (exrd.cpp in sample):
BEGIN_GX_EXCEL_MAP(CGXExcelTable)
GX_EXCEL_MAP_ENTRY(typeFont, CGXExcelFontTable, 2)
GX_EXCEL_MAP_ENTRY(typeStyle, CGXExcelStyleTable, 2)
GX_EXCEL_MAP_ENTRY(typeXF, CGXExcelXFTable, 2)
GX_EXCEL_MAP_ENTRY(typePalette, CGXExcelColorTable, 2)
GX_EXCEL_MAP_ENTRY(typeFormat, CGXExcelFormatTable, 2)
GX_EXCEL_MAP_ENTRY(typeSST, CGXExcelSSTTable, 2)
GX_EXCEL_MAP_ENTRY(typeBoundSheet, CGXExcelBoundSheetTable, 2)
END_GX_EXCEL_MAP()
BEGIN_GX_EXCEL_MAP(CGXExcelHandler)
GX_EXCEL_MAP_ENTRY(typeLabel, CGXExcelLabelHandler, 2)
GX_EXCEL_MAP_ENTRY(typeNumber, CGXExcelNumberHandler, 2)
GX_EXCEL_MAP_ENTRY(typeRk, CGXExcelRkHandler, 2)
GX_EXCEL_MAP_ENTRY(typeMulRk, CGXExcelMulRkHandler, 2)
GX_EXCEL_MAP_ENTRY(typeColInfo, CGXExcelColInfoHandler, 2)
GX_EXCEL_MAP_ENTRY(typeRow, CGXExcelRowHandler, 2)
GX_EXCEL_MAP_ENTRY(typeFormula, CGXExcelFormulaHandler, 2)
GX_EXCEL_MAP_ENTRY(typeBOF, CGXExcelBOFHandler, 2)
GX_EXCEL_MAP_ENTRY(typeBlank, CGXExcelBlankHandler, 2)
GX_EXCEL_MAP_ENTRY(typeMulBlank, CGXExcelMulBlankHandler, 2)
GX_EXCEL_MAP_ENTRY(typeSSLabel, CGXExcelLabelSSTHandler, 2)
GX_EXCEL_MAP_ENTRY(typeDimension, CGXExcelDimensionHandler, 2)
GX_EXCEL_MAP_ENTRY(typeWindow2, CGXExcelWindow2Handler, 2)
GX_EXCEL_MAP_ENTRY(typeMergedCell, CGXExcelMergedCellsHandler, 2)
END_GX_EXCEL_MAP()
These are the handler and table maps that were explained earlier. These maps have all the objects that are available for dealing with BIFF records. You can exclude records that you do not wish to use or add ones that you write to this map. (For further information on adding your own derived handlers and tables, please refer to the advanced section at the end of this chapter.)
5. Once the maps have been defined, we have to initialize the application object to hook these maps into the read chain. This is done by adding the following lines to the application object’s constructor.
SetHandlerMap(new CGXHandlerMapImpl);
SetTableMap(new CGXTableMapImpl);
We do not have to add code to clean up these objects. They will be cleaned up by the application object when it goes out of scope.
6. Add a call to AfxOleInit() to the application InitInstance() call. This call is very important; its absence will result in inexplicable failures.
8. Don’t forget to set the /GR compiler option.
9. Remember to open stdafx.h and add the following line at the end of the file:
#include "grid\gxexrd.h"
10. Add a member of type CGXExcelReadWnd to the dialog instance. (This can also be a class derived from this class.)
// Excel reader grid control
CGXExcelReadWnd m_gridWnd;
11. Add the following to the OnInitDialog() code:
m_gridWnd.SubclassDlgItem(IDC_GRID, this);
m_gridWnd.Initialize();
CString strPath;
CFileDialog dlgFile(
TRUE,
_T(".xls"),
NULL,
OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT,
_T("Excel 97 Files (*.xls)|*.xls|All Files (*.*)|*.*||")
);
if (dlgFile.DoModal() == IDCANCEL)
return FALSE;
strPath = dlgFile.GetFileName();
m_gridWnd.ReadExcelFile(strPath);
This will display a dialog at startup and, once you select an Excel file, will populate the grid with the 0th tab of the Excel grid.
12. Add one button for opening Excel files and another button for saving to Excel files. Use the following code for opening and saving files:
Opening Code (very similar to above):
CString strPath;
CFileDialog dlgFile(
TRUE,
_T(".xls"),
NULL,
OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT,
_T("Excel 97 Files (*.xls)|*.xls|All Files (*.*)|*.*||")
);
if (dlgFile.DoModal() == IDCANCEL)
return;
strPath = dlgFile.GetFileName();
BOOL b = m_gridWnd.LockUpdate(TRUE);
m_gridWnd.GetParam()->EnableUndo(FALSE);
m_gridWnd.SetRowCount(0);
m_gridWnd.SetColCount(0);
// If you want to read a particular index of the worksheet
// you can specify the index as the second argument.
m_gridWnd.ReadExcelFile(strPath, 1);
m_gridWnd.LockUpdate(b);
m_gridWnd.GetParam()->EnableUndo(TRUE);
m_gridWnd.Redraw(GX_UPDATENOW);
NOTE >> In the call to ReadExcelFile() you can change the index (the second argument) to change the tab being read in. (This goes from zero to the number of tabs minus 1.)
Saving Code:
CString strPath;
CFileDialog dlgFile(
FALSE,
_T(".xls"),
NULL,
OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT,
_T("Excel 97 Files (*.xls)|*.xls|All Files (*.*)|*.*||")
);
if (dlgFile.DoModal() == IDCANCEL)
return;
strPath = dlgFile.GetFileName();
m_gridWnd.SaveExcelFile(strPath);
13. Compile the application and try opening a file. You should see the tab that you want displayed.
Sample 2
In this case we will be adding support to an existing application to read and write Excel files.
1. Step 1 is the same as in Sample 1. Changes listed there need to be made to the application object for this sample.
2. Change the view class so that it derives from CGXExcelGrid.
class CExtoexistView : public CGXGridView, public CGXExcelGrid
3. Add the following overrides to the view class as shown below:
//Excel specific overrides that delegate to CGXExcelGrid
virtual BOOL OnPasteFromClipboard(const CGXRange& rg);
virtual BOOL OnCheckClipboardFormat();
virtual BOOL CopyRange(const CGXRangeList& selList);
virtual void OnPreExcelReadFile();
virtual void OnPostExcelReadFile();
// operations
void ReadExcelFile(const CString& strFileName, UINT index = 0);
void SaveExcelFile(const CString& strFileName);
4. The implementation for these is standard and for the most part can be copied over from one project to the other. This is shown below.
BOOL CExtoexistView::OnPasteFromClipboard(const CGXRange& rg)
{
return OnPasteFromClipboardImpl(rg);
}
BOOL CExtoexistView::OnCheckClipboardFormat()
{
return OnCheckClipboardFormatImpl();
}
BOOL CExtoexistView::CopyRange(const CGXRangeList& selList)
{
return CopyRangeImpl(selList);
}
void CExtoexistView::OnExcelopen()
{
CString strPath;
CFileDialog dlgFile(
TRUE,
_T(".xls"),
NULL,
OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT,
_T("Excel 97 Files (*.xls)|*.xls|All Files (*.*)|*.*||")
);
if (dlgFile.DoModal() == IDCANCEL)
return;
strPath = dlgFile.GetFileName();
BOOL b = LockUpdate(TRUE);
GetParam()->EnableUndo(FALSE);
SetRowCount(0);
SetColCount(0);
// If you want to read a particular index of the worksheet
// you can specify the index as the second argument.
ReadExcelFile(strPath, 0);
LockUpdate(b);
GetParam()->EnableUndo(TRUE);
Redraw(GX_UPDATENOW);
}
void CExtoexistView::OnExcelsave()
{
CString strPath;
CFileDialog dlgFile(
FALSE,
_T(".xls"),
NULL,
OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT,
_T("Excel 97 Files (*.xls)|*.xls|All Files (*.*)|*.*||")
);
if (dlgFile.DoModal() == IDCANCEL)
return;
strPath = dlgFile.GetFileName();
SaveExcelFile(strPath);
}
void CExtoexistView::ReadExcelFile(const CString& strFileName,
UINT index)
{
// add code here to intialize from an Excel file
CGXExcelWndDocAdapter docAdapt;
docAdapt.SetGrid(this);
OnPreExcelReadFile();
try
{
docAdapt.ReadExcelFile(strFileName, index);
}
catch(egxExcelRWFlags flags)
{
GX_EX_FMT_TRACE("OG Error:
CGXExcelReaderImpl::ExcelWriteImpl.")
CGXExRWErrorHandler::HandleError(flags);
}
catch(...)
{
GX_EX_FMT_MSG()
return;
}
OnPostExcelReadFile();
}
void CExtoexistView::OnPreExcelReadFile()
{
// add pre read code here
CGXGridParam* pParam = GetParam();
ASSERT(pParam != NULL);
pParam->EnableUndo(FALSE);
}
void CExtoexistView::OnPostExcelReadFile()
{
// add post read code here...typically common Excel
// emulation code is added
CGXGridParam* pParam = GetParam();
pParam->EnableUndo(TRUE);
SetFloatCellsMode(gxnFloatDelayEval);
ChangeStandardStyle(CGXStyle().SetWrapText(FALSE));
}
void CExtoexistView::SaveExcelFile(const CString& strFileName)
{
CGXExcelWndDocAdapter docAdapt;
docAdapt.SetGrid(this);
docAdapt.SaveExcelFile(strFileName);
}
5. As in the sample, hook the code that opens the file and saves the file to command messages.
6. Compile the project. Your existing view should be able to read and write Excel files as well as support rich data exchange with Excel through the clipboard.