Excel Automation with Objective Grid
Developed by Microsoft, the Automation approach allows reading from and writing to Excel files and creating applications with run-time data exchange. Automation provides more flexibility and allows developers to use all of the power of MS Excel in their custom applications.
To ensure the ability to build with UNICODE, the Excel read/write option should be unchecked in Grid Build Wizard.
Review the following descriptions and examples of Excel Automation before developing applications with Excel-Grid interoperability:
• Microsoft KB Article 178781 HOWTO: "Automate Excel Using MFC and Worksheet Functions": http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q178/7/81.asp&NoWebContent=1
• Microsoft KB Article 178749 HOWTO: "Create Automation Project Using MFC and a Type Library": http://support.microsoft.com/default.aspx?scid=kb;EN-US;178749#appliesto
• Microsoft KB Article 184663 HOWTO: "Embed and Automate a Microsoft Excel Worksheet with MFC": http://support.microsoft.com/default.aspx?scid=kb;EN-US;184663
• Microsoft KB Article 192348 HOWTO: "Automate Excel and Then Know the User Closed It": http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/KB/Articles/Q192/3/48.asp&NoWebContent=1
• Microsoft KB Article 311546 HOW TO: "Embed and Automate an Excel Worksheet by Using C++ .NET and MFC": http://support.microsoft.com/?kbid=311546
• Microsoft KB Article 179706 HOWTO: "Use MFC to Automate Excel and Create/Format a New Workbook": http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q179/7/06.asp&NoWebContent=1
• Microsoft Office Development with Visual Studio: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_2003_ta/html/odc_ancoffsol.asp
NOTE >> The following sample demonstrates only a limited implementation of the Excel-Grid integration using Microsoft’s Automation. Support for the implementation of additional features based on this technology is beyond regular Technical Support Services; however, Consulting Services may provide additional assistance. Contact your sales representative for more information.
Enabling Excel Integration in a Grid Project
The ExcelGrid sample discussed in this section demonstrates how to enable integration with Excel in a Grid project.
This sample is provided at <stingray-installdir> \Samples\Grid\Excel\ExcelGridAutomation.
Creating an ExcelGrid Sample
Create an ExcelGrid sample by adding Excel-related functionality to the regular Tab Grid MDI project (see CGXTabWnd in Objective Grid Class Reference) with Formula Engine-enabled and Excel-like features set, as follows:
1. Generate Excel classes from Excel type library as described in KB articles (files Excel9, .h and .cpp), #include "excel9.h" in stdafx.h.
2. Override the CGXCurrencyEdit class to implement more Excel-looking formats. (A new control is registered in CTabGridView::OnInitialUpdate.)
Using the Excel-Related Classes
To use the Excel-related classes:
1. Add CExcelExchange * m_pExcelExchange; into class CTabGridView, as follows:
— m_pExcelExchange is created in CTabGridView::OnInitialUpdate:
m_pExcelExchange = new CExcelExchange(this,((CChildFrame *)GetParentFrame())->app);
— m_pExcelExchange is deleted in the destructor for CTabGridView:
CTabGridView::~CTabGridView()
{
delete m_pExcelExchange;
}
2. Note that members for Excel objects are added into class CChildFrame:
_Application app;
LPDISPATCH lpDisp;
_Workbook book;
_Worksheet sheet;
Workbooks books;
Worksheets sheets;
3. Use the function CChildFrame::OpenCloseExcel for processing an open/close Excel object and exceptions handling.
4. The following function is just a wrapper for Excel function Workbooks::Open(…):
OpenExcel(CString strExcelFile = _T(""));
In our sample, we call this function with a number of parameters that are required for Excel 9 files. If Excel files are generated from later versions of Excel, the number of parameters should be changed (see Microsoft’s Knowledge Base articles).
5. If the project is built on a machine with Excel installed, then the fully qualified path to the Excel file should be passed into function Open to run the executable on another machine. In our sample, we decided to keep the initial Excel file with the format examples in the same folder as the executable. We use the following code to determine the path:
TCHAR szBuffer[1024];
GetModuleFileName(NULL, szBuffer, 1024);
CString str(szBuffer);
str.Replace(_T("TabGrid.exe"), _T("ExcelFormats.xls"));
6. To verify that the Excel file exists:
WIN32_FIND_DATA wfd;
HANDLE hFind = FindFirstFile(str, &wfd);
if (hFind == INVALID_HANDLE_VALUE)
str = "";
FindClose(hFind);
If there is no Excel file where it is expected, an empty string is passed into function Open(…) and a new workbook is opened.
In the destructor, we use the exception processing module to call app.Quit(); as shown here:
CChildFrame::~CChildFrame()
{
OpenCloseExcel(FALSE);
}
An ExcelExchange object is created for each view in the tab/worksheet, and the following code is used to iterate through views/worksheets in functions CChildFrame::OnReadBook and CChildFrame::OnWriteBook:
CView * pView;
POSITION pos = pDoc->GetFirstViewPosition();
short nSheet = 0;
while (pos != NULL)
{
nSheet++;
pView = pDoc->GetNextView(pos);
if (pView->IsKindOf(RUNTIME_CLASS(CTabGridView)))
{
((CTabGridView *)pView)->GetExcelExchange()->ReadWrite(
WRITE,TRUE,TRUE,nSheet);
}
}
NOTE >> The technique used in this sample fixes an Objective Grid bug which affects scrollbar behavior. For more details on this bug fix, please refer to these CTabGridView functions: GetParentTabWnd, GetScrollBarCtrl, UpdateScrollbars, and GetScrollBarCtrl.
Data And Style Exchange Classes
This section describes the classes and functions provided in the Objective Grid integration with Excel.
Class CExcelExchange
The function CExcelExchange::ReadWrite provides general processing of read/write procedures and exception handling.
Performance of exchange between Grid and an automated Excel object depends significantly on how this exchange is implemented. For example, you can set all style formatting both in Grid and Excel only in OnInitialUpdate and then change values in Excel as soon as they are changed in Grid. This scenario will demonstrate very good performance, although it will depend on specific logic of the application. In our sample, we demonstrate the worst-case scenario to show the approach is usable even in this case. You’ll see that the lookup of all styles is provided as styles and values are translated and sent between Grid and Excel. In this scenario, it would be advantageous to review which styles are actually used in a particular application and exclude unused styles from translation. When all styles are excluded and only values lookup and exchange are provided, performance is adequate.
The CExcelExchange::Read and CExcelExchange::Write functions determine if styles or only values lookup should be provided. The function CExcelExchange::IndexToString is used to transform cell coordinates used in Grid (nRow, nCol) into Excel specific coordinates (such as AD55).
Class CExcelWrite
Class CExcelWrite includes WriteStyles and FormatRangeWrite functions, just for improving the structure of the code. The function WriteValueFormat translates the format of the value (such as string, datetime, currency, etc.) from Grid to Excel and includes the following functions:
WriteCoveredCells
WriteFont
WriteInterior
WriteBorders
WriteFormula
WriteVerticalAlignment
WriteHorizontalAlignment
WriteWrapText
There are four style types in Grid: Table, Column, Row, and Cell. As indicated by their names, each of the following functions translates one of these styles:
WriteTableStyles
WriteColStyles
WriteRowStyles
WriteCellStyles
The following functions are used to write an array of values from Grid to Excel (as described in Microsoft’s Knowledge Base articles):
WriteValues
WriteValuesArray
FillSafeArray
ValuesToArray
Class CExcelRead
The following functions simply improve the structure of the code:
ReadValues
ReadStyles
FormatRangeRead
Function ReadValueFormat translates the format of a value (for example: string, datetime, currency, et cetera) from Excel to Grid and includes the following functions:
ReadCoveredCells
ReadFont
ReadInterior
ReadBorders
ReadVerticalAlignment
ReadHorizontalAlignment
ReadFormula
The following functions translate Excel styles into four Grid style types -- Table, Column, Row, and Cell -- as indicated by the function name:
ReadTableStyles
ReadColStyles
ReadRowStyles
ReadCellStyles
The function ReadValuesArray is used to read an array of values from Excel to Grid (as described in Microsoft’s Knowledge Base articles).
Notes for ExcelGrid Sample
• There is no one-to-one correspondence between formats and styles in Grid and Excel, so there is room for setting this correspondence more or less voluntarily. For example, in our sample datetime controls in Grid are set to correspond with datetime formatting in Excel, while thick non-solid borders in Excel are set to correspond with thin borders in Grid.
• It is difficult to find descriptions of many C++ functions that are implemented in generated Excel classes. Nevertheless it is usually not difficult to find and use the descriptions of similar VB functions instead.
• To build the sample on a machine with some version other than Excel 2000, Excel files should be generated as described in Microsoft’s Knowledge Base articles, files Excel9 in our sample should be replaced, and the call to function Open(…) should be changed.
• To enable copying a formula that is different between Grid and Excel, the formula should be translated (only the name or, if necessary, parameters also) in functions ReadFormula and WriteFormula.
Testing the ExcelGrid Sample
This section describes how to test the
ExcelGrid sample that we created in
“Enabling Excel Integration in a Grid Project”, and also includes a description of the menu items that get implemented in this sample.
To test the ExcelGrid sample located at <stingray-installdir>\ Samples\Grid\Excel\ExcelGridAutomation:
1. Run the sample; the ExcelFormats file will open simultaneously with Grid.
2. On Sheet 1, all implemented formats are displayed. Any different format should be implemented in code.
3. On Sheet 3, there are samples of formula usage. For other functions, if name or parameters are not identical in Grid and Excel, translation should be implemented.
4. For testing, we recommend opening a new workbook, reading from ExcelFormat into Grid, and then writing from Grid into a new workbook.
The following menu items are implemented in the sample:
• Exchange between current sheet in Excel and current View in Grid: ReadSheet and WriteSheet.
• Exchange between current workbook in Excel and current TabGrid: ReadBook and WriteBook.
• Exchange values only between current sheet in Excel and current View in Grid: ReadSheetData and WriteSheetData.
Benefits of Excel-Grid Integration
The sample outlined above demonstrates the ease of integrating Excel into your Objective Grid project. This Automation-based integration also offers the following new features:
• UNICODE support
• Compliance with the latest versions of Excel (2007, 2010, 2012)
• More formats implemented and easy customization of formatting
• Easy customization of formula translation
• Ability to implement run-time exchange and access to all features of Excel (use formulas, web-services, etc.)