L
Lynn McGuire
// fm2n_old.cpp
// The OLE Automation interface to Excel is mostly written using:
// "How to automate Excel from C++ without using MFC or #import"
// http://support.microsoft.com/kb/216686
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <windows.h>
#include <comdef.h>
#include "ole2.h"
#include <ctype.h>
#include <shellapi.h>
#include "asstring.h"
#include "fm2n_old.h"
// main pointer for Excel
IDispatch * pExcelApplication = NULL;
// Workbooks collection
IDispatch * pExcelWorkbooks = NULL;
// Workbook object
IDispatch * pExcelWorkbook = NULL;
std::vector <IDispatch *> ExcelWorkbookObjects;
// Sheet object
IDispatch * pExcelSheet = NULL;
// function prototype for this private function
HRESULT OLEMethod (int autoType, VARIANT *pvResult, IDispatch *pDisp, LPOLESTR ptName, std::string errorStr, int cArgs...);
char g_Fm2nbookTitle [] = { "my application" };
std::string g_Fm2nbookStartupDir;
std::string g_notebookName;
std::string g_notebookPrefix;
std::string g_buffer;
int g_Fm2nbookNumberOfErrors = 0;
int RunInTestMode = false;
int DisconnectFromNotebook (int runInTestMode)
{
if (runInTestMode)
{
int numWorkbooks = ExcelWorkbookObjects.size ();
for (int i = 0; i < numWorkbooks; i++)
{
pExcelWorkbook = ExcelWorkbookObjects ;
if (pExcelWorkbook)
{
VARIANT result;
VariantInit ( & result);
OLEMethod (DISPATCH_METHOD, & result, pExcelWorkbook, L"Save",
"Saving the Spreadsheet (DisconnectFromNotebook)", 0);
int ret = result.vt;
}
if (pExcelWorkbook)
{
VARIANT result;
VariantInit ( & result);
OLEMethod (DISPATCH_METHOD, & result, pExcelWorkbook, L"Close",
"Closing the Spreadsheet (DisconnectFromNotebook)", 0);
int ret = result.vt;
pExcelWorkbook -> Release ();
pExcelWorkbook = NULL;
}
}
ExcelWorkbookObjects.clear ();
if (pExcelApplication)
{
VARIANT result;
VariantInit ( & result);
OLEMethod (DISPATCH_METHOD, & result, pExcelApplication, L"Quit",
"Shutting Excel down (DisconnectFromNotebook)", 0);
int ret = result.vt;
}
}
//MessageBox (0, "Point 300", Fm2nbookTitle, MB_ICONINFORMATION | MB_TASKMODAL);
if (pExcelSheet)
{
pExcelSheet -> Release ();
pExcelSheet = NULL;
}
if (pExcelWorkbook)
{
pExcelWorkbook -> Release ();
pExcelWorkbook = NULL;
}
if (pExcelWorkbooks)
{
pExcelWorkbooks -> Release ();
pExcelWorkbooks = NULL;
}
if (pExcelApplication)
{
pExcelApplication -> Release ();
pExcelApplication = NULL;
}
// MessageBox (0, "Point 302", Fm2nbookTitle, MB_ICONINFORMATION | MB_TASKMODAL);
return TRUE;
}
// stuff startup path into global
void Fm2nbookGetStartupDir (void)
{
// get the directory that the exe was started from
char directory [4096];
GetModuleFileName (NULL, directory, sizeof (directory));
// cut the exe name from string
char * p = & (directory [strlen (directory) - 1]);
while (p >= directory && *p && '\\' != *p)
p--;
*p = '\0';
g_Fm2nbookStartupDir = directory;
}
// NOTE: if this function returns FALSE then there is a major error
int MakeSureNotebookExists (std::string newNotebookName)
{
HANDLE found = NULL;
WIN32_FIND_DATA findData;
memset ( & findData, 0, sizeof (findData));
// copy from the template file if:
// a. if I could not find the file
// b. the file is there but has zero size
found = FindFirstFile (newNotebookName.c_str (), & findData);
// sprintf (buffer, "Got result %d when doing findfirst on\n%s",
// found, newNotebookName);
// MessageBox (0, buffer, Fm2nbookTitle, MB_ICONSTOP | MB_TASKMODAL);
if (INVALID_HANDLE_VALUE == found || findData.nFileSizeLow == 0)
{
// copy the standard template spreadsheet file from the
// startup directory
FILE *preExisting = NULL;
FILE *templateNotebook = NULL;
std::string path = g_Fm2nbookStartupDir + "\\template.xls";
if (strstr (newNotebookName.c_str (), ".xlsx"))
path = g_Fm2nbookStartupDir + "\\template.xlsx";
errno_t err = fopen_s ( & templateNotebook, path.c_str (), "rb");
if (err != 0)
{
g_buffer = "Could not open spreadsheet template file:\n\"" + path + "\".";
MessageBox (0, g_buffer.c_str (), g_Fm2nbookTitle, MB_ICONSTOP | MB_TASKMODAL);
return FALSE;
}
err = fopen_s ( & preExisting, newNotebookName.c_str (), "wb");
if (err == 0) // copy the template to the new file
{
int numRead = 0;
char tempBuffer [4096];
while (numRead = fread (tempBuffer, sizeof (char), sizeof (tempBuffer), templateNotebook))
{
fwrite (tempBuffer, sizeof (char), numRead, preExisting);
}
fclose (templateNotebook);
fclose (preExisting);
}
else
{
g_buffer = "Could not open new spreadsheet file:\n\"";
g_buffer += newNotebookName + ".\n"
"If this file is open by another application then\n"
"please close the file and restart the data transfer.";
MessageBox (0, g_buffer.c_str (), g_Fm2nbookTitle, MB_ICONSTOP | MB_TASKMODAL);
fclose (templateNotebook);
return FALSE; // kill the run
}
}
// make sure that we release the file handle if valid
if (found != INVALID_HANDLE_VALUE)
FindClose (found);
return TRUE;
}
// OLEMethod() - Automation helper function...
// from http://support.microsoft.com/kb/216686
HRESULT OLEMethod (int autoType, VARIANT *pvResult, IDispatch *pDisp, LPOLESTR ptName, std::string errorStr, int cArgs...)
{
// Begin variable-argument list...
va_list marker;
va_start(marker, cArgs);
char buf [2000];
char szName [2000];
// Convert down to ANSI
WideCharToMultiByte (CP_ACP, 0, ptName, -1, szName, 256, NULL, NULL);
if ( ! pDisp)
{
sprintf_s (buf, sizeof (buf), "ERROR: NULL IDispatch passed to OLEMethod() for \"%s\" (OLEMethod).", szName);
strcat_s (buf, sizeof (buf), "\n\nIf there are any warning messages in Excel then please\n"
"clear them and restart the data transfer.\n\n");
strcat_s (buf, sizeof (buf), "Activity: ");
strcat_s (buf, sizeof (buf), errorStr.c_str ());
MessageBox (NULL, buf, g_Fm2nbookTitle, 0x10010);
// _exit (0);
}
// Variables used...
DISPPARAMS dp = { NULL, NULL, 0, 0 };
DISPID dispidNamed = DISPID_PROPERTYPUT;
DISPID dispID;
HRESULT hr = -1;
// Get DISPID for name passed...
if (pDisp)
hr = pDisp -> GetIDsOfNames (IID_NULL, & ptName, 1, LOCALE_USER_DEFAULT, & dispID);
if (FAILED (hr))
{
sprintf_s (buf, sizeof (buf), "ERROR: IDispatch::GetIDsOfNames (\"%s\") failed w/err 0x%08lx (OLEMethod).",
szName, hr);
strcat_s (buf, sizeof (buf), "\n\nIf there are any warning messages in Excel then please\n"
"clear them and restart the data transfer.\n\n");
strcat_s (buf, sizeof (buf), "Activity: ");
strcat_s (buf, sizeof (buf), errorStr.c_str ());
MessageBox (NULL, buf, g_Fm2nbookTitle, 0x10010);
// _exit(0);
return hr;
}
// Allocate memory for arguments...
VARIANT * pArgs = new VARIANT [cArgs+1];
// Extract arguments...
for (int i = 0; i < cArgs; i++)
{
pArgs = va_arg (marker, VARIANT);
}
// Build DISPPARAMS
dp.cArgs = cArgs;
dp.rgvarg = pArgs;
// Handle special-case for property-puts!
if(autoType & DISPATCH_PROPERTYPUT)
{
dp.cNamedArgs = 1;
dp.rgdispidNamedArgs = &dispidNamed;
}
// Make the call! try up to 10 times and then quit after notifying user
hr = -1;
int counter = 0;
while (FAILED (hr) && counter <= 10)
{
hr = pDisp -> Invoke (dispID, IID_NULL, LOCALE_SYSTEM_DEFAULT, autoType, & dp, pvResult, NULL, NULL);
// if we failed then sleep for half a second
if (FAILED (hr))
Sleep (500);
counter++;
}
if (FAILED (hr))
{
sprintf_s (buf, sizeof (buf), "ERROR: IDispatch::Invoke (\"%s\"=%08lx) failed w/err 0x%08lx (OLEMethod).",
szName, dispID, hr);
strcat_s (buf, sizeof (buf), "\n\nIf there are any warning messages in Excel then please\n"
"clear them and restart the data transfer.\n\n");
strcat_s (buf, sizeof (buf), "Activity: ");
strcat_s (buf, sizeof (buf), errorStr.c_str ());
MessageBox (NULL, buf, g_Fm2nbookTitle, 0x10010);
// _exit(0);
return hr;
}
// End variable-argument section...
va_end (marker);
delete [] pArgs;
return hr;
}
int StartExcelServer (void)
{
// this code is somewhat from http://support.microsoft.com/kb/216686
// and from http://support.microsoft.com/kb/238610
// if there is a current server then release it
if (pExcelApplication)
{
pExcelApplication -> Release ();
pExcelApplication = NULL;
}
// Get CLSID for our server...
CLSID clsid;
HRESULT hr = CLSIDFromProgID (L"Excel.Application", & clsid);
if (FAILED (hr))
{
::MessageBox (NULL, "CLSIDFromProgID() failed", "Error", MB_ICONSTOP | MB_OK);
return false;
}
// see if we can connect to existing excel server and get idispatch
// NOTE: the process permission levels must be the same for this process
// and the excel process for GetActiveObject to work correctly.
// So, if running deswin.exe from visual studio then excel must
// be running as administrator also.
IUnknown * pIUnknown = NULL;
hr = GetActiveObject (clsid, NULL, (IUnknown**) & pIUnknown);
if (SUCCEEDED (hr))
{
// convert the iunknown pointer to an idispatch pointer
hr = pIUnknown -> QueryInterface (IID_IDispatch, (void**) & pExcelApplication);
// release the iunknown pointer since we dont need it anymore
pIUnknown -> Release ();
}
// if failed to talk to an existing excel then start server and get IDispatch...
if (FAILED (hr))
hr = CoCreateInstance (clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **) & pExcelApplication);
if (FAILED (hr))
{
::MessageBox (NULL, "Could not start Excel OLE Automation Server", "Error", MB_ICONSTOP | MB_OK);
return false;
}
// Make excel visible (i.e. app.visible = 1)
// if this fails then it is OK
if ( ! FAILED (hr))
{
VARIANT x;
x.vt = VT_I4;
x.lVal = 1;
OLEMethod (DISPATCH_PROPERTYPUT, NULL, pExcelApplication, L"Visible",
"Making Excel visible on the screen (StartExcelServer)", 1, x);
}
// if there is a current workbooks then release it
if (pExcelWorkbooks)
{
pExcelWorkbooks -> Release ();
pExcelWorkbooks = NULL;
}
// Get Workbooks collection
{
VARIANT result;
VariantInit ( & result);
OLEMethod (DISPATCH_PROPERTYGET, & result, pExcelApplication, L"Workbooks",
"Getting the Workbooks collection pointer (StartExcelServer)", 0);
pExcelWorkbooks = result.pdispVal;
}
return true;
}
// return a standard string with the file + sheet + current selected cell
// for DDE, will get something like '[EXPANDER-in.xls]Mat Bal'!R3C6
// for OLE, will get something like '[EXPANDER-in.xls]Mat Bal'!F3
std::string GetExcelCurrentSelection (void)
{
std::string selection = "";
// for OLE, will get something like '[EXPANDER-in.xls]Mat Bal'!F3
// first get the name of the spreadsheet
VARIANT result1;
VariantInit ( & result1);
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelApplication, L"ActiveWorkbook",
"Getting the pointer of the active spreadsheet (GetExcelCurrentSelection)", 0);
if (result1.vt == VT_DISPATCH)
{
IDispatch *pDisp = result1.pdispVal;
VARIANT result2;
VariantInit ( & result2);
OLEMethod (DISPATCH_PROPERTYGET, & result2, pDisp, L"Name",
"Getting the name of the active spreadsheet (GetExcelCurrentSelection)", 0);
if (result2.vt == VT_BSTR)
{
selection += "\'[";
selection += _bstr_t (result2.bstrVal);
selection += "]";
#ifdef CLEAR_VARIANTS
VariantClear ( & result2);
#endif
}
pDisp -> Release ();
}
// get the name of the sheet
VARIANT result2;
VariantInit ( & result2);
OLEMethod (DISPATCH_PROPERTYGET, & result2, pExcelApplication, L"ActiveSheet",
"Getting the pointer of the active sheet of the active spreadsheet (GetExcelCurrentSelection)", 0);
if (result2.vt == VT_DISPATCH)
{
IDispatch *pDisp = result2.pdispVal;
VARIANT result3;
VariantInit ( & result3);
OLEMethod (DISPATCH_PROPERTYGET, & result3, pDisp, L"Name",
"Getting the name of the active sheet of the active spreadsheet (GetExcelCurrentSelection)", 0);
if (result3.vt == VT_BSTR)
{
selection += _bstr_t (result3.bstrVal);
selection += "\'!";
#ifdef CLEAR_VARIANTS
VariantClear ( & result3);
#endif
}
pDisp -> Release ();
}
// get the selected cell(s) addresses
VARIANT result3;
VariantInit ( & result3);
OLEMethod (DISPATCH_PROPERTYGET, & result3, pExcelApplication, L"Selection",
"Getting the pointer of the selected cell of the active spreadsheet (GetExcelCurrentSelection)", 0);
if (result3.vt == VT_DISPATCH)
{
IDispatch *pDisp = result3.pdispVal;
// this will get the contents of the selected cell
// VariantInit ( & result);
// OLEMethod (DISPATCH_PROPERTYGET, & result, pDisp, L"Value", 0);
// int res_value = result.vt;
// this will get the address of the selected cell
VARIANT result4;
VariantInit ( & result4);
OLEMethod (DISPATCH_PROPERTYGET, & result4, pDisp, L"Address",
"Getting the address of the selected cell of the active spreadsheet (GetExcelCurrentSelection)", 0);
if (result4.vt == VT_BSTR)
{
std::string absoluteReference = _bstr_t (result4.bstrVal);
int len = absoluteReference.size ();
for (int i = 0; i < len; i++)
{
if (absoluteReference != '$')
selection += absoluteReference ;
}
#ifdef CLEAR_VARIANTS
VariantClear ( & result4);
#endif
}
pDisp -> Release ();
}
return selection;
}
int ConnectToNotebook (std::string newNotebookName, int runInTestMode, int firstTime)
{
// sprintf (buffer, "FM2NBOOK DLL built %s %s", __DATE__, __TIME__);
// MessageBox (0, buffer, Fm2nbookTitle, MB_ICONINFORMATION | MB_TASKMODAL);
// set the error counter to zero each time
g_Fm2nbookNumberOfErrors = 0;
g_buffer = newNotebookName;
int len = g_buffer.size ();
// remove the .OUT extension on the name if there is one
if (len > 4 && ! _strnicmp (& (g_buffer.c_str () [len - 4]), ".out", 4))
g_buffer.erase (len - 4);
// copy the new name in with a .XLS extension if not there already
len = g_buffer.size ();
if ((len < 4 || 0 != _strnicmp (& (g_buffer.c_str () [len - 4]), ".xls", 4)) &&
(len < 5 || 0 != _strnicmp (& (g_buffer.c_str () [len - 5]), ".xlsx", 5)))
g_notebookName = g_buffer + ".xls";
else
g_notebookName = g_buffer;
if (firstTime)
{
g_notebookPrefix = g_notebookName;
// strip the suffix from the notebook prefix, can be .xls or .xlsx
int len5 = g_notebookPrefix.size ();
g_notebookPrefix.erase (len5 - 4);
len5 = g_notebookPrefix.size ();
if (g_notebookPrefix [len5 - 1] == '.')
g_notebookPrefix.erase (len5 - 1);
}
Fm2nbookGetStartupDir ();
if ( ! MakeSureNotebookExists (g_notebookName))
return FALSE;
int ret = StartExcelServer ();
if ( ! ret)
{
g_buffer = "Can't start conversation with Excel.\n"
"Problem with either Excel or OLE automation.";
MessageBox (0, g_buffer.c_str (), g_Fm2nbookTitle, MB_ICONSTOP | MB_TASKMODAL);
return false;
}
// if we got here then Excel is alive and ready to take input
// get the names of the currently open spreadsheets and see if this one is open already
// otherwise open the spreadsheet
VARIANT result1;
VariantInit ( & result1);
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelWorkbooks, L"Count",
"Getting the number of currently open spreadsheets (ConnectToNotebook)", 0);
int numberOfWorkbooks = 0;
if (result1.vt == VT_I4)
numberOfWorkbooks = result1.intVal;
int workbookOpenAlready = false;
if (numberOfWorkbooks > 0)
{
for (int i = 0; i < numberOfWorkbooks && ! workbookOpenAlready; i++)
{
VARIANT result2;
VariantInit ( & result2);
VARIANT itemNumber;
itemNumber.vt = VT_I4;
// put the index of the workbook to get into the variant, the index starts with 1
itemNumber.intVal = i + 1;
OLEMethod (DISPATCH_PROPERTYGET, & result2, pExcelWorkbooks, L"Item",
"Getting the index of the spreadsheet (ConnectToNotebook)", 1, itemNumber);
if (result2.vt == VT_DISPATCH)
{
IDispatch *pDisp = result2.pdispVal;
VARIANT result3;
VariantInit ( & result3);
OLEMethod (DISPATCH_PROPERTYGET, & result3, pDisp, L"Name",
"Getting the name of the indexed spreadsheet (ConnectToNotebook)", 0);
if (result3.vt == VT_BSTR)
{
// this will be the workbook name without the path
std::string workbookName = _bstr_t (result3.bstrVal);
// strip the path from the current workbook name
std::string noPathNotebookName = g_notebookName;
int lastSlash = g_notebookName.size ();
while (lastSlash >= 0 && g_notebookName [lastSlash] != '\\')
lastSlash--;
if (lastSlash >= 0)
noPathNotebookName.erase (0, lastSlash + 1);
// do a case insensitive comparison of the file names
if (_stricmp (workbookName.c_str (), noPathNotebookName.c_str ()) == 0)
{
workbookOpenAlready = true;
// copy the dispatch pointer to the workbook pointer
pExcelWorkbook = pDisp;
// save the workbook pointer to close it later if needed
if (pExcelWorkbook)
ExcelWorkbookObjects.push_back (pExcelWorkbook);
// now activate the workbook
VARIANT result5;
VariantInit ( & result5);
OLEMethod (DISPATCH_PROPERTYGET, & result5, pDisp, L"Activate",
"Activating the spreadsheet that we want to use (ConnectToNotebook)", 0);
int res = result5.vt;
}
else
pDisp -> Release ();
#ifdef CLEAR_VARIANTS
VariantClear ( & result3);
#endif
}
}
}
}
// we need to open the spreadsheet file if not done already
if ( ! workbookOpenAlready)
{
VARIANT result;
VariantInit ( & result);
VARIANT fname;
fname.vt = VT_BSTR;
_bstr_t notebookNameBstr = _bstr_t (g_notebookName.c_str ());
fname.bstrVal = notebookNameBstr;
std::string errorMsg = "Opening the spreadsheet that we want to use, " + g_notebookName + " (ConnectToNotebook)";
OLEMethod (DISPATCH_METHOD, & result, pExcelWorkbooks, L"Open", errorMsg, 1, fname);
// copy the dispatch pointer to the workbook pointer
if (result.vt == VT_DISPATCH)
{
pExcelWorkbook = result.pdispVal;
// save the workbook pointer to close it later if needed
if (pExcelWorkbook)
ExcelWorkbookObjects.push_back (pExcelWorkbook);
}
else
{
VARIANT result5;
VariantInit ( & result5);
// adding a new spreadsheet
std::string errorMsg = "Creating the spreadsheet that we want to use, " + g_notebookName + " (ConnectToNotebook)";
OLEMethod (DISPATCH_METHOD, & result5, pExcelWorkbooks, L"Add", errorMsg, 0);
if (result5.vt == VT_DISPATCH)
{
pExcelWorkbook = result5.pdispVal;
// save the workbook pointer to close it later if needed
if (pExcelWorkbook)
ExcelWorkbookObjects.push_back (pExcelWorkbook);
}
else
return false;
VARIANT result6;
VariantInit ( & result6);
std::string errorMsg2 = "Saving the spreadsheet that we want to use, " + g_notebookName + " (ConnectToNotebook)";
OLEMethod (DISPATCH_METHOD, & result6, pExcelWorkbooks, L"Save", errorMsg2, 1, fname);
}
#ifdef CLEAR_VARIANTS
VariantClear ( & fname);
#endif
}
return TRUE;
}
int ConnectToNewNoteBook (int caseNumber)
{
// get the name of the new spreadsheet if this is not the base case
if (caseNumber > 0)
{
g_notebookName = g_notebookPrefix + " Case " + asString (caseNumber) + ".xls";
if ( ! MakeSureNotebookExists (g_notebookName))
return false;
if ( ! ConnectToNotebook (g_notebookName, RunInTestMode, false))
return false;
}
else
{
g_notebookName = g_notebookPrefix + ".xls";
}
return true;
}
int ConnectToNewSheet (std::string newSheetName, int createNewSheet, std::string baseSheetName, int deleteExistingSheet)
{
int sheetDeleted = FALSE;
int sheetOpenAlready = false;
IDispatch * pExcelSheets = NULL;
// get the names of the currently open sheets and see if this one is open already
// otherwise add the sheet and rename it
VARIANT result1;
VariantInit ( & result1);
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelWorkbook, L"Sheets",
"Getting the pointer to the Sheets collection in the active spreadsheet (ConnectToNewSheet)", 0);
if (result1.vt == VT_DISPATCH)
{
pExcelSheets = result1.pdispVal;
VARIANT result2;
VariantInit ( & result2);
OLEMethod (DISPATCH_PROPERTYGET, & result2, pExcelSheets, L"Count",
"Get the number of sheets in the spreadsheet (ConnectToNewSheet)", 0);
int numberOfSheets = 0;
if (result2.vt == VT_I4)
numberOfSheets = result2.intVal;
if (numberOfSheets > 0)
{
for (int i = 0; i < numberOfSheets && ! sheetOpenAlready; i++)
{
VARIANT result3;
VariantInit ( & result3);
VARIANT itemNumber;
itemNumber.vt = VT_I4;
// put the index of the workbook to get into the variant, the index starts with 1
itemNumber.intVal = i + 1;
OLEMethod (DISPATCH_PROPERTYGET, & result3, pExcelSheets, L"Item",
"Set the index of the sheet of the active spreadsheet (ConnectToNewSheet)", 1, itemNumber);
if (result3.vt == VT_DISPATCH)
{
IDispatch *pDisp = result3.pdispVal;
VARIANT result4;
VariantInit ( & result4);
OLEMethod (DISPATCH_PROPERTYGET, & result4, pDisp, L"Name",
"Get the name of the indexed sheet of the spreadsheet (ConnectToNewSheet)", 0);
if (result4.vt == VT_BSTR)
{
// this will be the sheet name
std::string sheetName = _bstr_t (result4.bstrVal);
if (sheetName == newSheetName)
{
sheetOpenAlready = true;
// if there is a current excel sheet then release it
if (pExcelSheet)
{
pExcelSheet -> Release ();
pExcelSheet = NULL;
}
// copy the dispatch pointer to the workbook pointer
pExcelSheet = pDisp;
VARIANT result5;
VariantInit ( & result5);
OLEMethod (DISPATCH_PROPERTYGET, & result5, pDisp, L"Activate",
"Activate the sheet of the spreadsheet (ConnectToNewSheet)", 0);
int res = result5.vt;
if (deleteExistingSheet)
{
sheetOpenAlready = false;
pExcelSheet = NULL;
// gotta turn off the display alerts or will get a message from excel asking if it can delete
VARIANT result6;
VariantInit ( & result6);
VARIANT displayAlerts;
VariantInit ( & displayAlerts);
displayAlerts.vt = VT_BOOL;
displayAlerts.boolVal = false;
OLEMethod (DISPATCH_PROPERTYPUT, & result6, pExcelApplication, L"DisplayAlerts",
"Turn display alerts off so we can delete a sheet in silence (ConnectToNewSheet)",
1, displayAlerts);
int res6 = result6.vt;
VARIANT result7;
VariantInit ( & result7);
OLEMethod (DISPATCH_PROPERTYGET, & result7, pDisp, L"Delete",
"Delete the current sheet in the spreadsheet (ConnectToNewSheet)", 0);
int res7 = result7.vt;
// gotta turn off the display alerts or will get a message from excel asking if it can delete
VARIANT result8;
VariantInit ( & result8);
VARIANT displayAlerts8;
VariantInit ( & displayAlerts8);
displayAlerts8.vt = VT_BOOL;
displayAlerts8.boolVal = true;
OLEMethod (DISPATCH_PROPERTYPUT, & result8, pExcelApplication, L"DisplayAlerts",
"Turn display alerts back on after deleting a sheet in the spreadsheet (ConnectToNewSheet)",
1, displayAlerts8);
int res8 = result8.vt;
pDisp -> Release ();
// get out of this for loop since we are finished with it
break;
}
}
else
pDisp -> Release ();
#ifdef CLEAR_VARIANTS
VariantClear ( & result4);
#endif
}
}
}
}
}
else
return false;
// we need to create the sheet file if not done already
if ( ! sheetOpenAlready && pExcelSheets && createNewSheet)
{
pExcelSheets = result1.pdispVal;
VARIANT result2;
VariantInit ( & result2);
OLEMethod (DISPATCH_PROPERTYGET, & result2, pExcelSheets, L"Count",
"Getting the number of sheets in the active spreadsheet (ConnectToNewSheet)", 0);
int numberOfSheets = 0;
if (result2.vt == VT_I4)
numberOfSheets = result2.intVal;
if (numberOfSheets > 0)
{
for (int i = 0; i < numberOfSheets && ! sheetOpenAlready; i++)
{
VARIANT result3;
VariantInit ( & result3);
VARIANT itemNumber;
itemNumber.vt = VT_I4;
// put the index of the workbook to get into the variant, the index starts with 1
itemNumber.intVal = i + 1;
OLEMethod (DISPATCH_PROPERTYGET, & result3, pExcelSheets, L"Item",
"Set the index of the active sheet of the spreadsheet (ConnectToNewSheet)", 1, itemNumber);
if (result3.vt == VT_DISPATCH)
{
IDispatch *pDisp = result3.pdispVal;
VARIANT result4;
VariantInit ( & result4);
OLEMethod (DISPATCH_PROPERTYGET, & result4, pDisp, L"Name",
"Get the name of the indexed sheet of the active spreadsheet (ConnectToNewSheet)", 0);
if (result4.vt == VT_BSTR)
{
// this will be the sheet name
std::string sheetName = _bstr_t (result4.bstrVal);
if (sheetName == baseSheetName)
{
sheetOpenAlready = true;
// if there is a current excel sheet then release it
if (pExcelSheet)
{
pExcelSheet -> Release ();
pExcelSheet = NULL;
}
// copy the dispatch pointer to the workbook pointer
pExcelSheet = pDisp;
VARIANT result5;
VariantInit ( & result5);
OLEMethod (DISPATCH_PROPERTYGET, & result5, pDisp, L"Activate",
"Set the spreadsheet back to the sheet in the active spreadsheet (ConnectToNewSheet)", 0);
int res = result5.vt;
}
else
pDisp -> Release ();
#ifdef CLEAR_VARIANTS
VariantClear ( & result4);
#endif
}
}
}
}
// now create the new sheet, hopefully in the proper place
VARIANT result3;
VariantInit ( & result3);
OLEMethod (DISPATCH_METHOD, & result3, pExcelSheets, L"Add",
"Add a new sheet to the active spreadsheet (ConnectToNewSheet)", 0);
// copy the dispatch pointer to the sheet pointer
if (result3.vt == VT_DISPATCH)
{
// if there is a current excel sheet then release it
if (pExcelSheet)
{
pExcelSheet -> Release ();
pExcelSheet = NULL;
}
pExcelSheet = result3.pdispVal;
// now name the sheet to the new name
VARIANT result4;
VariantInit ( & result4);
VARIANT fnameNew;
fnameNew.vt = VT_BSTR;
_bstr_t sheetNameNewBstr = _bstr_t (newSheetName.c_str ());
fnameNew.bstrVal = sheetNameNewBstr;
OLEMethod (DISPATCH_PROPERTYPUT, & result4, pExcelSheet, L"Name",
"Set the name of the new sheet in the active spreadsheet (ConnectToNewSheet)", 1, fnameNew);
int res = result4.vt;
#ifdef CLEAR_VARIANTS
VariantClear ( & fnameNew);
#endif
}
else
return false;
}
if (pExcelSheets)
pExcelSheets -> Release ();
return true;
}
void ReportError (DWORD result, std::string job, std::string msg)
{
g_Fm2nbookNumberOfErrors++;
// only report the first ten errors
if (g_Fm2nbookNumberOfErrors < 11)
{
#ifdef USE_DDE_FOR_EXCEL
char buff [10000];
char * ddeError = "unknown";
UINT lastError = DdeGetLastError (InstId);
switch (lastError)
{
case DMLERR_ADVACKTIMEOUT:
ddeError = "A request for a synchronous advise transaction has timed out.";
break;
case DMLERR_BUSY:
ddeError = "The response to the transaction caused the DDE_FBUSY flag to be set.";
break;
case DMLERR_DATAACKTIMEOUT:
ddeError = "A request for a synchronous data transaction has timed out.";
break;
case DMLERR_DLL_NOT_INITIALIZED:
ddeError = "A DDEML function was called without first calling the DdeInitialize \n function, or an invalid instance identifier was
passed to a DDEML function.";
break;
case DMLERR_DLL_USAGE:
ddeError = "An application initialized as APPCLASS_MONITOR has attempted to \n perform a Dynamic Data Exchange (DDE) transaction";
break;
case DMLERR_EXECACKTIMEOUT:
ddeError = "A request for a synchronous execute transaction has timed out.";
break;
case DMLERR_INVALIDPARAMETER:
ddeError = "A parameter failed to be validated by the DDEML.";
break;
case DMLERR_LOW_MEMORY:
ddeError = "A DDEML application has created a prolonged race condition (in \n which the server application outruns the client),
causing large amounts of \n memory to be consumed.";
break;
case DMLERR_MEMORY_ERROR:
ddeError = "A memory allocation has failed.";
break;
case DMLERR_NO_CONV_ESTABLISHED:
ddeError = "A client's attempt to establish a conversation has failed.";
break;
case DMLERR_NOTPROCESSED:
ddeError = "A transaction has failed.";
break;
case DMLERR_POKEACKTIMEOUT:
ddeError = "A request for a synchronous poke transaction has timed out.";
break;
case DMLERR_POSTMSG_FAILED:
ddeError = "An internal call to the PostMessage function has failed.";
break;
case DMLERR_REENTRANCY:
ddeError = "An application instance with a synchronous transaction already in \n progress attempted to initiate another
synchronous transaction, or the \n DdeEnableCallback function was called from within a DDEML callback function.";
break;
case DMLERR_SERVER_DIED:
ddeError = "A server-side transaction was attempted on a conversation terminated \n by the client, or the server terminated before
completing a transaction.";
break;
case DMLERR_SYS_ERROR:
ddeError = "An internal error has occurred in the DDEML.";
break;
case DMLERR_UNADVACKTIMEOUT:
ddeError = "A request to end an advise transaction has timed out.";
break;
case DMLERR_UNFOUND_QUEUE_ID:
ddeError = "An invalid transaction identifier was passed to a DDEML function.";
break;
}
sprintf_s (buff, sizeof (buff), "Spreadsheet DDE server reported error %x\n"
"(DDE error code %x) for %s\n\n"
"\"%s\"\n\n"
"DDE Error: %s\n\n"
"Number Of Rows = %d\n"
"Current Column = %d\n\n"
"The Excel Reply for the Initial Selection is:\n"
"\"%s\"\n\n"
"The Excel Row Character is '%c'\n"
"The Excel Column Character is '%c'\n",
result, lastError, job.c_str (), msg.c_str (), ddeError, numberOfRows, currentColumn,
SavedResponseFromExcel, ExcelRowCharacter, ExcelColumnCharacter);
MessageBox (0, buff, Fm2nbookTitle, MB_ICONSTOP | MB_TASKMODAL);
#endif
}
}
void PutStringInServer (int row, int column, const char * str)
{
if (str)
{
std::string newStr = str;
PutStringInServer (row, column, newStr);
}
}
void PutStringInServer (int row, int column, std::string string)
{
// get rid of the trailing blanks
string.erase (string.find_last_not_of (' ') + 1);
int len = string.size ();
if (len == 0)
return;
std::string cellAddress = GetCellEquivalent (row, column);
VARIANT result1;
VariantInit ( & result1);
VARIANT range;
VariantInit ( & range);
range.vt = VT_BSTR;
_bstr_t address = _bstr_t (cellAddress.c_str ());
range.bstrVal = address;
std::string errorMsg = "Select a range in the active spreadsheet, " + cellAddress +
" (PutStringInServer)";
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelSheet, L"Range", errorMsg, 1, range);
if (result1.vt == VT_DISPATCH)
{
VARIANT result2;
VariantInit ( & result2);
VARIANT value;
VariantInit ( & value);
value.vt = VT_BSTR;
_bstr_t valueBSTR = _bstr_t (string.c_str ());
value.bstrVal = valueBSTR;
std::string errorMsg = "Put the string into the selected cell, " + cellAddress + ", \"" + string + "\" (PutStringInServer)";
OLEMethod (DISPATCH_PROPERTYPUT, & result2, result1.pdispVal, L"Value", errorMsg, 1, value);
int res = result2.vt;
result1.pdispVal -> Release ();
#ifdef CLEAR_VARIANTS
VariantClear ( & value);
#endif
}
#ifdef CLEAR_VARIANTS
VariantClear ( & range);
#endif
}
void PutDoubleInServer (int row, int column, const double num)
{
if (uninitializedValue == num)
return;
std::string cellAddress = GetCellEquivalent (row, column);
VARIANT result1;
VariantInit ( & result1);
VARIANT range;
VariantInit ( & range);
range.vt = VT_BSTR;
_bstr_t address = _bstr_t (cellAddress.c_str ());
range.bstrVal = address;
std::string errorMsg = "Select a range in the active spreadsheet, " + cellAddress + " (PutDoubleInServer)";
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelSheet, L"Range", errorMsg, 1, range);
if (result1.vt == VT_DISPATCH)
{
VARIANT result2;
VariantInit ( & result2);
VARIANT value;
VariantInit ( & value);
value.vt = VT_R8;
value.dblVal = num;
std::string errorMsg = "Put the double into the selected cell, " + cellAddress + " (PutDoubleInServer)";
OLEMethod (DISPATCH_PROPERTYPUT, & result2, result1.pdispVal, L"Value", errorMsg, 1, value);
int res = result2.vt;
result1.pdispVal -> Release ();
#ifdef CLEAR_VARIANTS
VariantClear ( & value);
#endif
}
#ifdef CLEAR_VARIANTS
VariantClear ( & range);
#endif
}
void PutDoubleInServerVertical (int row, int column, int count, const double num [])
{
// put the doubles in vertically
// Create a 1 by count safearray of variants...
// get safe array info from http://support.microsoft.com/kb/216686
VARIANT arr;
VariantInit ( & arr);
arr.vt = VT_ARRAY | VT_VARIANT;
{
SAFEARRAYBOUND sab[2];
sab[0].lLbound = 1;
sab[0].cElements = count;
sab[1].lLbound = 1;
sab[1].cElements = 1;
arr.parray = SafeArrayCreate (VT_VARIANT, 2, sab);
}
// Fill safearray with some values...
for (int i = 1; i <= count; i++)
{
for (int j = 1; j <= 1; j++)
{
// Create entry value for (i,j)
VARIANT tmp;
VariantInit ( & tmp);
if (uninitializedValue == num [i - 1])
{
tmp.vt = VT_EMPTY;
}
else
{
tmp.vt = VT_R8;
tmp.dblVal = num [i - 1];
}
// Add to safearray...
long indices [] = {i, j};
SafeArrayPutElement (arr.parray, indices, (void *) & tmp);
}
}
// send the array to excel
std::string cellAddress = GetCellEquivalent (row, column) + ":" + GetCellEquivalent (row + count - 1, column);
VARIANT result1;
VariantInit ( & result1);
VARIANT range;
VariantInit ( & range);
range.vt = VT_BSTR;
_bstr_t address = _bstr_t (cellAddress.c_str ());
range.bstrVal = address;
std::string errorMsg = "Select a range in the active spreadsheet, " + cellAddress + " (PutDoubleInServerVertical)";
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelSheet, L"Range", errorMsg, 1, range);
if (result1.vt == VT_DISPATCH)
{
VARIANT result2;
VariantInit ( & result2);
std::string errorMsg = "Put the double into the selected cell, " + cellAddress + " (PutDoubleInServerVertical)";
OLEMethod (DISPATCH_PROPERTYPUT, & result2, result1.pdispVal, L"Value", errorMsg, 1, arr);
int res = result2.vt;
result1.pdispVal -> Release ();
}
#ifdef CLEAR_VARIANTS
VariantClear ( & arr);
VariantClear ( & range);
#endif
}
void PutDoubleInServerHorizontal (int row, int column, int count, const double num [])
{
// put the doubles in horizontally
// Create a 1 by count safearray of variants...
// get safe array info from http://support.microsoft.com/kb/216686
VARIANT arr;
VariantInit ( & arr);
arr.vt = VT_ARRAY | VT_VARIANT;
{
SAFEARRAYBOUND sab[2];
sab[0].lLbound = 1;
sab[0].cElements = 1;
sab[1].lLbound = 1;
sab[1].cElements = count;
arr.parray = SafeArrayCreate (VT_VARIANT, 2, sab);
}
// Fill safearray with some values...
for (int i = 1; i <= 1; i++)
{
for (int j = 1; j <= count; j++)
{
// Create entry value for (i,j)
VARIANT tmp;
VariantInit ( & tmp);
if (uninitializedValue == num [j - 1])
{
tmp.vt = VT_EMPTY;
}
else
{
tmp.vt = VT_R8;
tmp.dblVal = num [j - 1];
}
// Add to safearray...
long indices [] = {i, j};
SafeArrayPutElement (arr.parray, indices, (void *) & tmp);
}
}
// send the array to excel
std::string cellAddress = GetCellEquivalent (row, column) + ":" + GetCellEquivalent (row, column + count - 1);
VARIANT result1;
VariantInit ( & result1);
VARIANT range;
VariantInit ( & range);
range.vt = VT_BSTR;
_bstr_t address = _bstr_t (cellAddress.c_str ());
range.bstrVal = address;
std::string errorMsg = "Select a range in the active spreadsheet, " + cellAddress + " (PutDoubleInServerHorizontal)";
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelSheet, L"Range", errorMsg, 1, range);
if (result1.vt == VT_DISPATCH)
{
VARIANT result2;
VariantInit ( & result2);
std::string errorMsg = "Put the double into the selected cell, " + cellAddress + " (PutDoubleInServerHorizontal)";
OLEMethod (DISPATCH_PROPERTYPUT, & result2, result1.pdispVal, L"Value", errorMsg, 1, arr);
int res = result2.vt;
result1.pdispVal -> Release ();
}
#ifdef CLEAR_VARIANTS
VariantClear ( & arr);
VariantClear ( & range);
#endif
}
void PutIntegerInServer (const int row, const int column, const int num)
{
std::string cellAddress = GetCellEquivalent (row, column);
VARIANT result1;
VariantInit ( & result1);
VARIANT range;
VariantInit ( & range);
range.vt = VT_BSTR;
_bstr_t address = _bstr_t (cellAddress.c_str ());
range.bstrVal = address;
std::string errorMsg = "Select a range in the active spreadsheet, " + cellAddress + " (PutIntegerInServer)";
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelSheet, L"Range", errorMsg, 1, range);
if (result1.vt == VT_DISPATCH)
{
VARIANT result2;
VariantInit ( & result2);
VARIANT value;
VariantInit ( & value);
value.vt = VT_I4;
value.intVal = num;
std::string errorMsg = "Put the integer into the selected cell, " + cellAddress + " (PutIntegerInServer)";
OLEMethod (DISPATCH_PROPERTYPUT, & result2, result1.pdispVal, L"Value", errorMsg, 1, value);
int res = result2.vt;
result1.pdispVal -> Release ();
}
#ifdef CLEAR_VARIANTS
VariantClear ( & range);
#endif
}
std::string GetCellEquivalent (const int row, const int column)
{
// column can be A to IV or A to XFD for Excel 2007
// column basis is one
// row can be 1 to 65,536 or 1 to 1,048,576 for Excel 2007
// row basis is one
std::string ret = "";
// calculate on a zero basis so subtract one
int columnZeroBased = column - 1;
// Repeatedly divide the number by 26 and convert the
// remainder into the appropriate letter.
do
{
int remainder = columnZeroBased % 26;
ret = (char)(remainder + 'A') + ret;
columnZeroBased = columnZeroBased / 26 - 1;
}
while (columnZeroBased >= 0);
ret += asString (row);
return ret;
}
std::string GetCellRowEquivalent (const int row)
{
// column can be A to IV or A to XFD for Excel 2007
// row can be 1 to 65,536 or 1 to 1,048,576 for Excel 2007
std::string ret = "";
ret += asString (row);
return ret;
}
std::string GetCellColumnEquivalent (const int column)
{
// column can be A to IV or A to XFD for Excel 2007
// column basis is one
// row can be 1 to 65,536 or 1 to 1,048,576 for Excel 2007
// row basis is one
std::string ret = "";
// calculate on a zero basis so subtract one
int columnZeroBased = column - 1;
// Repeatedly divide the number by 26 and convert the
// remainder into the appropriate letter.
do
{
int remainder = columnZeroBased % 26;
ret = (char)(remainder + 'A') + ret;
columnZeroBased = columnZeroBased / 26 - 1;
}
while (columnZeroBased >= 0);
return ret;
}
void NameCellInServer (std::string cellName, std::string sheetName,
const int row, const int column)
{
std::string cellAddress = GetCellEquivalent (row, column);
VARIANT result1;
VariantInit ( & result1);
VARIANT range;
VariantInit ( & range);
range.vt = VT_BSTR;
_bstr_t address = _bstr_t (cellAddress.c_str ());
range.bstrVal = address;
std::string errorMsg = "Select a range in the active spreadsheet, " + cellAddress + " (NameCellInServer)";
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelSheet, L"Range", errorMsg, 1, range);
if (result1.vt == VT_DISPATCH)
{
VARIANT result2;
VariantInit ( & result2);
VARIANT name;
VariantInit ( & name);
name.vt = VT_BSTR;
_bstr_t rangeName = _bstr_t (cellName.c_str ());
name.bstrVal = rangeName;
std::string errorMsg = "Name the selected cell, " + cellAddress + " (NameCellInServer)";
OLEMethod (DISPATCH_PROPERTYPUT, & result2, result1.pdispVal, L"Name", errorMsg, 1, name);
int res = result2.vt;
result1.pdispVal -> Release ();
#ifdef CLEAR_VARIANTS
VariantClear ( & name);
#endif
}
#ifdef CLEAR_VARIANTS
VariantClear ( & range);
#endif
}
// sheetName is the Excel sheet name
// column1 is A to IV for Excel 95 to 2003, A to XFP for Excel 2007+
// column2 is probably the same as column1
// row1 is 1 to 65536 for Excel 95 to 2003, 1 to
// row2 is below row1, hopefully
std::vector <std::string> getCellInfo (std::string sheetName, std::string column1, std::string column2,
std::string row1, std::string row2)
{
std::vector <std::string> cell;
// do create the sheet if it does not exist yet but do not delete it
if (ConnectToNewSheet (sheetName, true, "", false))
{
int row1Int = atoi (row1.c_str ());
int row2Int = atoi (row2.c_str ());
for (int i = row1Int; i <= row2Int; i++)
{
int row = i;
int len = column1.size ();
int column = 0;
// convert "AC" to numeric 29 or "XFD" to 16384 or "IV" to 256
for (int j = 0; j < len; j++)
column = column * 26 + column1.c_str () [j] - 'A' + 1;
std::string cellAddress = GetCellEquivalent (row, column);
std::string reply = "";
VARIANT result1;
VariantInit ( & result1);
VARIANT range;
VariantInit ( & range);
range.vt = VT_BSTR;
_bstr_t address = _bstr_t (cellAddress.c_str ());
range.bstrVal = address;
std::string errorMsg = "Select a range in the active spreadsheet (getCellInfo)";
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelSheet, L"Range", errorMsg, 1, range);
if (result1.vt == VT_DISPATCH)
{
VARIANT result2;
VariantInit ( & result2);
std::string errorMsg = "Get the data of the selected cell, " + cellAddress + " (getCellInfo)";
OLEMethod (DISPATCH_PROPERTYGET, & result2, result1.pdispVal, L"Value", errorMsg, 0);
if (result2.vt == VT_I4)
reply = asString (result2.intVal);
else if (result2.vt == VT_R4)
reply = asString (result2.fltVal);
else if (result2.vt == VT_R8)
reply = asString (result2.dblVal);
else if (result2.vt == VT_BSTR)
reply = _bstr_t (result2.bstrVal);
result1.pdispVal -> Release ();
#ifdef CLEAR_VARIANTS
VariantClear ( & result2);
#endif
}
// MessageBox (0, reply.c_str (), cellAddress.c_str (), MB_ICONINFORMATION | MB_TASKMODAL);
cell.push_back (reply);
#ifdef CLEAR_VARIANTS
VariantClear ( & range);
#endif
}
}
return cell;
}
// sheetName is the Excel sheet name
// column1 is A to IV for Excel 95 to 2003, A to XFP for Excel 2007+
// column2 is probably the same as column1
// row1 is 1 to 65536 for Excel 95 to 2003, 1 to
// row2 is below row1, hopefully
// dimensional units placement has the following value range:
// 0 = dont transfer dimensional units, 1 = above this cell, 2 = below, 3 = left, 4 = right
int putCellInfo (std::vector <std::string> results, std::string sheetName, std::string column1,
std::string column2, std::string row1, std::string row2,
std::string dimensionalUnitsValue, int dimensionalUnitsPlacement)
{
int error = false;
// do create the sheet if it does not exist yet but do not delete it
if (ConnectToNewSheet (sheetName, true, "", false))
{
int row1Int = atoi (row1.c_str ());
int row2Int = atoi (row2.c_str ());
for (int i = row1Int; i <= row2Int; i++)
{
int row = i;
int len = column1.size ();
int column = 0;
// convert "AC" to numeric 29 or "XFD" to 16384 or "IV" to 256
for (int j = 0; j < len; j++)
column = column * 26 + column1.c_str () [j] - 'A' + 1;
std::string cellAddress = GetCellEquivalent (row, column);
VARIANT result1;
VariantInit ( & result1);
VARIANT range;
VariantInit ( & range);
range.vt = VT_BSTR;
_bstr_t address = _bstr_t (cellAddress.c_str ());
range.bstrVal = address;
std::string errorMsg = "Select a range in the active spreadsheet, " + cellAddress + " (putCellInfo)";
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelSheet, L"Range", errorMsg, 1, range);
if (result1.vt == VT_DISPATCH)
{
VARIANT result2;
VariantInit ( & result2);
VARIANT value;
VariantInit ( & value);
value.vt = VT_BSTR;
_bstr_t valueBSTR = _bstr_t (results [i - row1Int].c_str ());
value.bstrVal = valueBSTR;
std::string errorMsg = "Put the data into the selected cell, " + cellAddress + " (putCellInfo)";
OLEMethod (DISPATCH_PROPERTYPUT, & result2, result1.pdispVal, L"Value", errorMsg, 1, value);
int res = result2.vt;
result1.pdispVal -> Release ();
#ifdef CLEAR_VARIANTS
VariantClear ( & value);
#endif
}
// MessageBox (0, reply.c_str (), cellAddress.c_str (), MB_ICONINFORMATION | MB_TASKMODAL);
#ifdef CLEAR_VARIANTS
VariantClear ( & range);
#endif
}
if (dimensionalUnitsPlacement > 0)
{
int row = row1Int;
int column = 0;
int len = column1.size ();
// convert "AC" to numeric 29 or "XFD" to 16384 or "IV" to 256
for (int j = 0; j < len; j++)
column = column * 26 + column1.c_str () [j] - 'A' + 1;
// 0 = dont transfer dimensional units, 1 = above this cell, 2 = below, 3 = left, 4 = right
switch (dimensionalUnitsPlacement)
{
case 1:
row -= 1;
if (row < 1)
row = 1;
break;
case 2:
// if the user wants the units below then must be below ALL the numbers
row += row2Int - row1Int + 1;
if (row > 1048576)
row = 1048576;
break;
case 3:
column -= 1;
if (column < 1)
column = 1;
break;
case 4:
column += 1;
if (column > 16384)
column = 16384;
break;
}
std::string cellAddress = GetCellEquivalent (row, column);
VARIANT result1;
VariantInit ( & result1);
VARIANT range;
VariantInit ( & range);
range.vt = VT_BSTR;
_bstr_t address = _bstr_t (cellAddress.c_str ());
range.bstrVal = address;
std::string errorMsg = "Select a range for the dimensional units in the active spreadsheet, " + cellAddress + " (putCellInfo)";
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelSheet, L"Range", errorMsg, 1, range);
if (result1.vt == VT_DISPATCH)
{
VARIANT result2;
VariantInit ( & result2);
VARIANT value;
VariantInit ( & value);
value.vt = VT_BSTR;
_bstr_t valueBSTR = _bstr_t (dimensionalUnitsValue.c_str ());
value.bstrVal = valueBSTR;
std::string errorMsg = "Put the dimensional units into the selected cell, " + cellAddress + " (putCellInfo)";
OLEMethod (DISPATCH_PROPERTYPUT, & result2, result1.pdispVal, L"Value", errorMsg, 1, value);
int res = result2.vt;
result1.pdispVal -> Release ();
#ifdef CLEAR_VARIANTS
VariantClear ( & value);
#endif
}
#ifdef CLEAR_VARIANTS
VariantClear ( & range);
#endif
}
}
else
error = true;
return error;
}
// colorValue should be an RGB (r,g,b)
int setRangeFont (std::string rangeValue, std::string fontNameValue, int boldValue, int sizeValue, int colorValue)
{
VARIANT result1;
VariantInit ( & result1);
VARIANT range;
VariantInit ( & range);
range.vt = VT_BSTR;
_bstr_t address = _bstr_t (rangeValue.c_str ());
range.bstrVal = address;
std::string errorMsg = "Select a range in the active spreadsheet, " + rangeValue + " (setRangeFont)";
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelSheet, L"Range", errorMsg, 1, range);
if (result1.vt == VT_DISPATCH)
{
VARIANT result2;
VariantInit ( & result2);
std::string errorMsg = "Get the font type of the selected range, " + rangeValue + " (setRangeFont)";
OLEMethod (DISPATCH_PROPERTYGET, & result2, result1.pdispVal, L"Font", errorMsg, 0);
if (result2.vt == VT_DISPATCH)
{
if (fontNameValue.size () > 0)
{
VARIANT result3;
VariantInit ( & result3);
VARIANT fontName;
VariantInit ( & fontName);
fontName.vt = VT_BSTR;
_bstr_t fontNameBSTR = _bstr_t (fontNameValue.c_str ());
fontName.bstrVal = fontNameBSTR;
std::string errorMsg = "Set the current selection to a font in the active spreadsheet, " + rangeValue + " (setRangeFont)";
OLEMethod (DISPATCH_PROPERTYPUT, & result3, result2.pdispVal, L"Name", errorMsg, 1, fontName);
int res3 = result3.vt;
#ifdef CLEAR_VARIANTS
VariantClear ( & fontName);
#endif
}
if (boldValue > 0)
{
VARIANT result4;
VariantInit ( & result4);
VARIANT bold;
VariantInit ( & bold);
bold.vt = VT_BOOL;
bold.boolVal = boldValue;
std::string errorMsg = "Set the current selection to bold in the active spreadsheet, " + rangeValue + " (setRangeFont)";
OLEMethod (DISPATCH_PROPERTYPUT, & result4, result2.pdispVal, L"Bold", errorMsg, 1, bold);
int res4 = result4.vt;
}
if (sizeValue > 0)
{
VARIANT result5;
VariantInit ( & result5);
VARIANT size;
VariantInit ( & size);
size.vt = VT_I4;
size.intVal = sizeValue;
std::string errorMsg = "Set the current selection to a size in the active spreadsheet, " + rangeValue + " (setRangeFont)";
OLEMethod (DISPATCH_PROPERTYPUT, & result5, result2.pdispVal, L"Size", errorMsg, 1, size);
int res5 = result5.vt;
}
if (colorValue > 0)
{
VARIANT result6;
VariantInit ( & result6);
VARIANT color;
VariantInit ( & color);
color.vt = VT_I4;
color.intVal = colorValue;
std::string errorMsg = "Set the current selection to a color in the active spreadsheet, " + rangeValue + " (setRangeFont)";
OLEMethod (DISPATCH_PROPERTYPUT, & result6, result2.pdispVal, L"Color", errorMsg, 1, color);
int res6 = result6.vt;
}
result2.pdispVal -> Release ();
}
result1.pdispVal -> Release ();
}
#ifdef CLEAR_VARIANTS
VariantClear ( & range);
#endif
return true;
}
int SetRangeWidthAlignment (std::string rangeValue, int columnWidthValue, int horizontalAlignmentValue)
{
VARIANT result1;
VariantInit ( & result1);
VARIANT range;
VariantInit ( & range);
range.vt = VT_BSTR;
_bstr_t address = _bstr_t (rangeValue.c_str ());
range.bstrVal = address;
std::string errorMsg = "Select a range in the active spreadsheet," + rangeValue + " (SetRangeWidthAlignment)";
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelSheet, L"Range", errorMsg, 1, range);
if (result1.vt == VT_DISPATCH)
{
if (columnWidthValue > 0)
{
VARIANT result2;
VariantInit ( & result2);
VARIANT columnWidth;
VariantInit ( & columnWidth);
columnWidth.vt = VT_I4;
columnWidth.intVal = columnWidthValue;
std::string errorMsg = "Set the column width of the selected cell," + rangeValue + " (SetRangeWidthAlignment)";
OLEMethod (DISPATCH_PROPERTYPUT, & result2, result1.pdispVal, L"ColumnWidth", errorMsg, 1, columnWidth);
int res2 = result2.vt;
}
if (horizontalAlignmentValue != 0)
{
// Excel Constants
// var xlCellTypeLastCell = 11;
// var xlThin = 2;
// var xlSolid = 1;
// int xlCenter = -4108;
// var xlBottom = -4107;
// var xlTop = -4160;
// var xlRight = -4152;
// var xlLeft = -4131;
// var xlNone = -4142;
// var xlContinuous = 1;
// var xlThin = 2;
// var xlAutomatic = -4105;
// var xlExpression = 2;
VARIANT result5;
VariantInit ( & result5);
VARIANT horizontalAlignment;
VariantInit ( & horizontalAlignment);
horizontalAlignment.vt = VT_I4;
horizontalAlignment.intVal = horizontalAlignmentValue;
std::string errorMsg = "Set horizontal alingment of the selected range," + rangeValue + " (SetRangeWidthAlignment)";
OLEMethod (DISPATCH_PROPERTYPUT, & result5, result1.pdispVal, L"HorizontalAlignment", errorMsg, 1, horizontalAlignment);
int res5 = result5.vt;
}
result1.pdispVal -> Release ();
}
#ifdef CLEAR_VARIANTS
VariantClear ( & range);
#endif
return true;
}
int TwoDimensionTableRowCount = 0;
int TwoDimensionTableColumnCount = 0;
int TwoDimensionTableNumberOfColumns = 0;
VARIANT TwoDimensionArray;
// I am not sure if count is a actual count of objects or a maximum number of objects
void InitTable (int numberOfRows, int numberOfColumns)
{
// put the doubles or strings in horizontally
// Create a row by column safearray of variants...
// get safe array info from http://support.microsoft.com/kb/216686
VariantInit ( & TwoDimensionArray);
TwoDimensionArray.vt = VT_ARRAY | VT_VARIANT;
{
SAFEARRAYBOUND sab[2];
sab[0].lLbound = 1;
// make the number of rows 2000 for now since we really dont know how
// many rows that there will be in the end
if (numberOfRows < 2000)
sab[0].cElements = 2000;
else
sab[0].cElements = numberOfRows;
sab[1].lLbound = 1;
sab[1].cElements = numberOfColumns;
TwoDimensionArray.parray = SafeArrayCreate (VT_VARIANT, 2, sab);
}
// init the row count at 1 for the first row
TwoDimensionTableRowCount = 1;
// init the column count at zero so can be incremented at the beginning of each element put
TwoDimensionTableColumnCount = 0;
TwoDimensionTableNumberOfColumns = numberOfColumns;
}
void PutDoubleInTable (int numberOfDoubles, double * num)
{
// Fill safearray with some values...
// Create entry value for (i,j)
for (int i = 0; i < numberOfDoubles; i++)
{
TwoDimensionTableColumnCount++;
if (TwoDimensionTableColumnCount > TwoDimensionTableNumberOfColumns)
{
TwoDimensionTableRowCount++;
TwoDimensionTableColumnCount = 1;
}
VARIANT tmp;
VariantInit ( & tmp);
if (uninitializedValue == num )
{
tmp.vt = VT_EMPTY;
}
else
{
tmp.vt = VT_R8;
tmp.dblVal = num ;
}
// Add to safearray...
long indices [] = {TwoDimensionTableRowCount, TwoDimensionTableColumnCount};
SafeArrayPutElement (TwoDimensionArray.parray, indices, (void *) & tmp);
}
}
void PutDoubleInTable (double num)
{
// Fill safearray with some values...
// Create entry value for (i,j)
TwoDimensionTableColumnCount++;
if (TwoDimensionTableColumnCount > TwoDimensionTableNumberOfColumns)
{
TwoDimensionTableRowCount++;
TwoDimensionTableColumnCount = 1;
}
VARIANT tmp;
VariantInit ( & tmp);
if (uninitializedValue == num)
{
tmp.vt = VT_EMPTY;
}
else
{
tmp.vt = VT_R8;
tmp.dblVal = num;
}
// Add to safearray...
long indices [] = {TwoDimensionTableRowCount, TwoDimensionTableColumnCount};
SafeArrayPutElement (TwoDimensionArray.parray, indices, (void *) & tmp);
}
void PutStringInTable (std::string str)
{
// get rid of the trailing blanks
str.erase (str.find_last_not_of (' ') + 1);
TwoDimensionTableColumnCount++;
if (TwoDimensionTableColumnCount > TwoDimensionTableNumberOfColumns)
{
TwoDimensionTableRowCount++;
TwoDimensionTableColumnCount = 1;
}
// Fill safearray with some values...
// Create entry value for (i,j)
VARIANT tmp;
VariantInit ( & tmp);
// if no string then punt to the blank entry
if (str.size () > 0)
{
tmp.vt = VT_BSTR;
_bstr_t valueBSTR = _bstr_t (str.c_str ());
tmp.bstrVal = valueBSTR;
}
else
tmp.vt = VT_EMPTY;
// Add to safearray...
long indices [] = {TwoDimensionTableRowCount, TwoDimensionTableColumnCount};
SafeArrayPutElement (TwoDimensionArray.parray, indices, (void *) & tmp);
// do not call variant clear on tmp because it appears that safe array
// put element does not do a deep copy on the variant bstr
// the strings will be corrupted when they get to the spreadsheet
// VariantClear ( & tmp);
}
void PutIntegerInTable (int number)
{
TwoDimensionTableColumnCount++;
if (TwoDimensionTableColumnCount > TwoDimensionTableNumberOfColumns)
{
TwoDimensionTableRowCount++;
TwoDimensionTableColumnCount = 1;
}
// Fill safearray with some values...
// Create entry value for (i,j)
VARIANT tmp;
VariantInit ( & tmp);
tmp.vt = VT_I4;
tmp.intVal = number;
// Add to safearray...
long indices [] = {TwoDimensionTableRowCount, TwoDimensionTableColumnCount};
SafeArrayPutElement (TwoDimensionArray.parray, indices, (void *) & tmp);
}
void PutBlankInTable (int numberOfBlanks)
{
// Fill safearray with some values...
// Create entry value for (i,j)
for (int i = 0; i < numberOfBlanks; i++)
{
TwoDimensionTableColumnCount++;
if (TwoDimensionTableColumnCount > TwoDimensionTableNumberOfColumns)
{
TwoDimensionTableRowCount++;
TwoDimensionTableColumnCount = 1;
}
VARIANT tmp;
VariantInit ( & tmp);
tmp.vt = VT_EMPTY;
// Add to safearray...
long indices [] = {TwoDimensionTableRowCount, TwoDimensionTableColumnCount};
SafeArrayPutElement (TwoDimensionArray.parray, indices, (void *) & tmp);
}
}
void SendTableToServer (int row1, int column1, int row2, int column2)
{
// send the array to excel
// note that InitTable probably did not get the total number of rows, only
// the total number of columns and it sets the number of rows to 2000
// so, we may be exploiting a bug in either SafeArray or in Excel and just
// selecting the actual number of rows in the range allows us to ignore
// the fact that the safearray was created with way more rows than we are
// probably using
std::string cellAddress = GetCellEquivalent (row1, column1) + ":" + GetCellEquivalent (row2, column2);
VARIANT result1;
VariantInit ( & result1);
VARIANT range;
VariantInit ( & range);
range.vt = VT_BSTR;
_bstr_t address = _bstr_t (cellAddress.c_str ());
range.bstrVal = address;
std::string errorMsg = "Select a range in the active spreadsheet ," + cellAddress + " (SendTableToServer)";
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelSheet, L"Range", errorMsg, 1, range);
if (result1.vt == VT_DISPATCH)
{
VARIANT result2;
VariantInit ( & result2);
std::string errorMsg = "Send the table to selected range, " + cellAddress + " (SendTableToServer)";
OLEMethod (DISPATCH_PROPERTYPUT, & result2, result1.pdispVal, L"Value", errorMsg, 1, TwoDimensionArray);
int res = result2.vt;
result1.pdispVal -> Release ();
}
#ifdef CLEAR_VARIANTS
VariantClear ( & TwoDimensionArray);
VariantClear ( & range);
#endif
}
double AsDouble (const char * str)
{
if (str)
return atof (str);
else
return 0.0;
}
double AsDouble (std::string string)
{
if (string.size () > 0)
return atof (string.c_str ());
else
return 0.0;
}
void PutRowInTable (std::string title, std::string number, std::string units)
{
PutStringInTable (title);
if (units.size () > 0)
PutStringInTable (SetUnitsCase (units));
else
PutBlankInTable (1);
double temp = AsDouble (number);
PutDoubleInTable (1, & temp);
}
void PutRowInTable (std::string title, std::string number, std::string units, int offset)
{
InitTable (1, 3);
PutStringInTable (title);
if (units.size () > 0)
PutStringInTable (SetUnitsCase (units));
else
PutBlankInTable (1);
double temp = AsDouble (number);
PutDoubleInTable (temp);
SendTableToServer (offset, 1, offset, 3);
}
void PutRow4InTable (std::string title, std::string units, std::string num1, std::string num2,
std::string num3, std::string num4, int offset)
{
InitTable (1, 6);
PutStringInTable (title);
if (units.size () > 0)
PutStringInTable (SetUnitsCase (units));
else
PutBlankInTable (1);
double temp [4] = {uninitializedValue, uninitializedValue,
uninitializedValue, uninitializedValue};
if (num1.size () > 0)
temp [0] = AsDouble (num1);
if (num2.size () > 0)
temp [1] = AsDouble (num2);
if (num3.size () > 0)
temp [2] = AsDouble (num3);
if (num4.size () > 0)
temp [3] = AsDouble (num4);
PutDoubleInTable (4, temp);
SendTableToServer (offset, 1, offset, 6);
}
// The OLE Automation interface to Excel is mostly written using:
// "How to automate Excel from C++ without using MFC or #import"
// http://support.microsoft.com/kb/216686
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <windows.h>
#include <comdef.h>
#include "ole2.h"
#include <ctype.h>
#include <shellapi.h>
#include "asstring.h"
#include "fm2n_old.h"
// main pointer for Excel
IDispatch * pExcelApplication = NULL;
// Workbooks collection
IDispatch * pExcelWorkbooks = NULL;
// Workbook object
IDispatch * pExcelWorkbook = NULL;
std::vector <IDispatch *> ExcelWorkbookObjects;
// Sheet object
IDispatch * pExcelSheet = NULL;
// function prototype for this private function
HRESULT OLEMethod (int autoType, VARIANT *pvResult, IDispatch *pDisp, LPOLESTR ptName, std::string errorStr, int cArgs...);
char g_Fm2nbookTitle [] = { "my application" };
std::string g_Fm2nbookStartupDir;
std::string g_notebookName;
std::string g_notebookPrefix;
std::string g_buffer;
int g_Fm2nbookNumberOfErrors = 0;
int RunInTestMode = false;
int DisconnectFromNotebook (int runInTestMode)
{
if (runInTestMode)
{
int numWorkbooks = ExcelWorkbookObjects.size ();
for (int i = 0; i < numWorkbooks; i++)
{
pExcelWorkbook = ExcelWorkbookObjects ;
if (pExcelWorkbook)
{
VARIANT result;
VariantInit ( & result);
OLEMethod (DISPATCH_METHOD, & result, pExcelWorkbook, L"Save",
"Saving the Spreadsheet (DisconnectFromNotebook)", 0);
int ret = result.vt;
}
if (pExcelWorkbook)
{
VARIANT result;
VariantInit ( & result);
OLEMethod (DISPATCH_METHOD, & result, pExcelWorkbook, L"Close",
"Closing the Spreadsheet (DisconnectFromNotebook)", 0);
int ret = result.vt;
pExcelWorkbook -> Release ();
pExcelWorkbook = NULL;
}
}
ExcelWorkbookObjects.clear ();
if (pExcelApplication)
{
VARIANT result;
VariantInit ( & result);
OLEMethod (DISPATCH_METHOD, & result, pExcelApplication, L"Quit",
"Shutting Excel down (DisconnectFromNotebook)", 0);
int ret = result.vt;
}
}
//MessageBox (0, "Point 300", Fm2nbookTitle, MB_ICONINFORMATION | MB_TASKMODAL);
if (pExcelSheet)
{
pExcelSheet -> Release ();
pExcelSheet = NULL;
}
if (pExcelWorkbook)
{
pExcelWorkbook -> Release ();
pExcelWorkbook = NULL;
}
if (pExcelWorkbooks)
{
pExcelWorkbooks -> Release ();
pExcelWorkbooks = NULL;
}
if (pExcelApplication)
{
pExcelApplication -> Release ();
pExcelApplication = NULL;
}
// MessageBox (0, "Point 302", Fm2nbookTitle, MB_ICONINFORMATION | MB_TASKMODAL);
return TRUE;
}
// stuff startup path into global
void Fm2nbookGetStartupDir (void)
{
// get the directory that the exe was started from
char directory [4096];
GetModuleFileName (NULL, directory, sizeof (directory));
// cut the exe name from string
char * p = & (directory [strlen (directory) - 1]);
while (p >= directory && *p && '\\' != *p)
p--;
*p = '\0';
g_Fm2nbookStartupDir = directory;
}
// NOTE: if this function returns FALSE then there is a major error
int MakeSureNotebookExists (std::string newNotebookName)
{
HANDLE found = NULL;
WIN32_FIND_DATA findData;
memset ( & findData, 0, sizeof (findData));
// copy from the template file if:
// a. if I could not find the file
// b. the file is there but has zero size
found = FindFirstFile (newNotebookName.c_str (), & findData);
// sprintf (buffer, "Got result %d when doing findfirst on\n%s",
// found, newNotebookName);
// MessageBox (0, buffer, Fm2nbookTitle, MB_ICONSTOP | MB_TASKMODAL);
if (INVALID_HANDLE_VALUE == found || findData.nFileSizeLow == 0)
{
// copy the standard template spreadsheet file from the
// startup directory
FILE *preExisting = NULL;
FILE *templateNotebook = NULL;
std::string path = g_Fm2nbookStartupDir + "\\template.xls";
if (strstr (newNotebookName.c_str (), ".xlsx"))
path = g_Fm2nbookStartupDir + "\\template.xlsx";
errno_t err = fopen_s ( & templateNotebook, path.c_str (), "rb");
if (err != 0)
{
g_buffer = "Could not open spreadsheet template file:\n\"" + path + "\".";
MessageBox (0, g_buffer.c_str (), g_Fm2nbookTitle, MB_ICONSTOP | MB_TASKMODAL);
return FALSE;
}
err = fopen_s ( & preExisting, newNotebookName.c_str (), "wb");
if (err == 0) // copy the template to the new file
{
int numRead = 0;
char tempBuffer [4096];
while (numRead = fread (tempBuffer, sizeof (char), sizeof (tempBuffer), templateNotebook))
{
fwrite (tempBuffer, sizeof (char), numRead, preExisting);
}
fclose (templateNotebook);
fclose (preExisting);
}
else
{
g_buffer = "Could not open new spreadsheet file:\n\"";
g_buffer += newNotebookName + ".\n"
"If this file is open by another application then\n"
"please close the file and restart the data transfer.";
MessageBox (0, g_buffer.c_str (), g_Fm2nbookTitle, MB_ICONSTOP | MB_TASKMODAL);
fclose (templateNotebook);
return FALSE; // kill the run
}
}
// make sure that we release the file handle if valid
if (found != INVALID_HANDLE_VALUE)
FindClose (found);
return TRUE;
}
// OLEMethod() - Automation helper function...
// from http://support.microsoft.com/kb/216686
HRESULT OLEMethod (int autoType, VARIANT *pvResult, IDispatch *pDisp, LPOLESTR ptName, std::string errorStr, int cArgs...)
{
// Begin variable-argument list...
va_list marker;
va_start(marker, cArgs);
char buf [2000];
char szName [2000];
// Convert down to ANSI
WideCharToMultiByte (CP_ACP, 0, ptName, -1, szName, 256, NULL, NULL);
if ( ! pDisp)
{
sprintf_s (buf, sizeof (buf), "ERROR: NULL IDispatch passed to OLEMethod() for \"%s\" (OLEMethod).", szName);
strcat_s (buf, sizeof (buf), "\n\nIf there are any warning messages in Excel then please\n"
"clear them and restart the data transfer.\n\n");
strcat_s (buf, sizeof (buf), "Activity: ");
strcat_s (buf, sizeof (buf), errorStr.c_str ());
MessageBox (NULL, buf, g_Fm2nbookTitle, 0x10010);
// _exit (0);
}
// Variables used...
DISPPARAMS dp = { NULL, NULL, 0, 0 };
DISPID dispidNamed = DISPID_PROPERTYPUT;
DISPID dispID;
HRESULT hr = -1;
// Get DISPID for name passed...
if (pDisp)
hr = pDisp -> GetIDsOfNames (IID_NULL, & ptName, 1, LOCALE_USER_DEFAULT, & dispID);
if (FAILED (hr))
{
sprintf_s (buf, sizeof (buf), "ERROR: IDispatch::GetIDsOfNames (\"%s\") failed w/err 0x%08lx (OLEMethod).",
szName, hr);
strcat_s (buf, sizeof (buf), "\n\nIf there are any warning messages in Excel then please\n"
"clear them and restart the data transfer.\n\n");
strcat_s (buf, sizeof (buf), "Activity: ");
strcat_s (buf, sizeof (buf), errorStr.c_str ());
MessageBox (NULL, buf, g_Fm2nbookTitle, 0x10010);
// _exit(0);
return hr;
}
// Allocate memory for arguments...
VARIANT * pArgs = new VARIANT [cArgs+1];
// Extract arguments...
for (int i = 0; i < cArgs; i++)
{
pArgs = va_arg (marker, VARIANT);
}
// Build DISPPARAMS
dp.cArgs = cArgs;
dp.rgvarg = pArgs;
// Handle special-case for property-puts!
if(autoType & DISPATCH_PROPERTYPUT)
{
dp.cNamedArgs = 1;
dp.rgdispidNamedArgs = &dispidNamed;
}
// Make the call! try up to 10 times and then quit after notifying user
hr = -1;
int counter = 0;
while (FAILED (hr) && counter <= 10)
{
hr = pDisp -> Invoke (dispID, IID_NULL, LOCALE_SYSTEM_DEFAULT, autoType, & dp, pvResult, NULL, NULL);
// if we failed then sleep for half a second
if (FAILED (hr))
Sleep (500);
counter++;
}
if (FAILED (hr))
{
sprintf_s (buf, sizeof (buf), "ERROR: IDispatch::Invoke (\"%s\"=%08lx) failed w/err 0x%08lx (OLEMethod).",
szName, dispID, hr);
strcat_s (buf, sizeof (buf), "\n\nIf there are any warning messages in Excel then please\n"
"clear them and restart the data transfer.\n\n");
strcat_s (buf, sizeof (buf), "Activity: ");
strcat_s (buf, sizeof (buf), errorStr.c_str ());
MessageBox (NULL, buf, g_Fm2nbookTitle, 0x10010);
// _exit(0);
return hr;
}
// End variable-argument section...
va_end (marker);
delete [] pArgs;
return hr;
}
int StartExcelServer (void)
{
// this code is somewhat from http://support.microsoft.com/kb/216686
// and from http://support.microsoft.com/kb/238610
// if there is a current server then release it
if (pExcelApplication)
{
pExcelApplication -> Release ();
pExcelApplication = NULL;
}
// Get CLSID for our server...
CLSID clsid;
HRESULT hr = CLSIDFromProgID (L"Excel.Application", & clsid);
if (FAILED (hr))
{
::MessageBox (NULL, "CLSIDFromProgID() failed", "Error", MB_ICONSTOP | MB_OK);
return false;
}
// see if we can connect to existing excel server and get idispatch
// NOTE: the process permission levels must be the same for this process
// and the excel process for GetActiveObject to work correctly.
// So, if running deswin.exe from visual studio then excel must
// be running as administrator also.
IUnknown * pIUnknown = NULL;
hr = GetActiveObject (clsid, NULL, (IUnknown**) & pIUnknown);
if (SUCCEEDED (hr))
{
// convert the iunknown pointer to an idispatch pointer
hr = pIUnknown -> QueryInterface (IID_IDispatch, (void**) & pExcelApplication);
// release the iunknown pointer since we dont need it anymore
pIUnknown -> Release ();
}
// if failed to talk to an existing excel then start server and get IDispatch...
if (FAILED (hr))
hr = CoCreateInstance (clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **) & pExcelApplication);
if (FAILED (hr))
{
::MessageBox (NULL, "Could not start Excel OLE Automation Server", "Error", MB_ICONSTOP | MB_OK);
return false;
}
// Make excel visible (i.e. app.visible = 1)
// if this fails then it is OK
if ( ! FAILED (hr))
{
VARIANT x;
x.vt = VT_I4;
x.lVal = 1;
OLEMethod (DISPATCH_PROPERTYPUT, NULL, pExcelApplication, L"Visible",
"Making Excel visible on the screen (StartExcelServer)", 1, x);
}
// if there is a current workbooks then release it
if (pExcelWorkbooks)
{
pExcelWorkbooks -> Release ();
pExcelWorkbooks = NULL;
}
// Get Workbooks collection
{
VARIANT result;
VariantInit ( & result);
OLEMethod (DISPATCH_PROPERTYGET, & result, pExcelApplication, L"Workbooks",
"Getting the Workbooks collection pointer (StartExcelServer)", 0);
pExcelWorkbooks = result.pdispVal;
}
return true;
}
// return a standard string with the file + sheet + current selected cell
// for DDE, will get something like '[EXPANDER-in.xls]Mat Bal'!R3C6
// for OLE, will get something like '[EXPANDER-in.xls]Mat Bal'!F3
std::string GetExcelCurrentSelection (void)
{
std::string selection = "";
// for OLE, will get something like '[EXPANDER-in.xls]Mat Bal'!F3
// first get the name of the spreadsheet
VARIANT result1;
VariantInit ( & result1);
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelApplication, L"ActiveWorkbook",
"Getting the pointer of the active spreadsheet (GetExcelCurrentSelection)", 0);
if (result1.vt == VT_DISPATCH)
{
IDispatch *pDisp = result1.pdispVal;
VARIANT result2;
VariantInit ( & result2);
OLEMethod (DISPATCH_PROPERTYGET, & result2, pDisp, L"Name",
"Getting the name of the active spreadsheet (GetExcelCurrentSelection)", 0);
if (result2.vt == VT_BSTR)
{
selection += "\'[";
selection += _bstr_t (result2.bstrVal);
selection += "]";
#ifdef CLEAR_VARIANTS
VariantClear ( & result2);
#endif
}
pDisp -> Release ();
}
// get the name of the sheet
VARIANT result2;
VariantInit ( & result2);
OLEMethod (DISPATCH_PROPERTYGET, & result2, pExcelApplication, L"ActiveSheet",
"Getting the pointer of the active sheet of the active spreadsheet (GetExcelCurrentSelection)", 0);
if (result2.vt == VT_DISPATCH)
{
IDispatch *pDisp = result2.pdispVal;
VARIANT result3;
VariantInit ( & result3);
OLEMethod (DISPATCH_PROPERTYGET, & result3, pDisp, L"Name",
"Getting the name of the active sheet of the active spreadsheet (GetExcelCurrentSelection)", 0);
if (result3.vt == VT_BSTR)
{
selection += _bstr_t (result3.bstrVal);
selection += "\'!";
#ifdef CLEAR_VARIANTS
VariantClear ( & result3);
#endif
}
pDisp -> Release ();
}
// get the selected cell(s) addresses
VARIANT result3;
VariantInit ( & result3);
OLEMethod (DISPATCH_PROPERTYGET, & result3, pExcelApplication, L"Selection",
"Getting the pointer of the selected cell of the active spreadsheet (GetExcelCurrentSelection)", 0);
if (result3.vt == VT_DISPATCH)
{
IDispatch *pDisp = result3.pdispVal;
// this will get the contents of the selected cell
// VariantInit ( & result);
// OLEMethod (DISPATCH_PROPERTYGET, & result, pDisp, L"Value", 0);
// int res_value = result.vt;
// this will get the address of the selected cell
VARIANT result4;
VariantInit ( & result4);
OLEMethod (DISPATCH_PROPERTYGET, & result4, pDisp, L"Address",
"Getting the address of the selected cell of the active spreadsheet (GetExcelCurrentSelection)", 0);
if (result4.vt == VT_BSTR)
{
std::string absoluteReference = _bstr_t (result4.bstrVal);
int len = absoluteReference.size ();
for (int i = 0; i < len; i++)
{
if (absoluteReference != '$')
selection += absoluteReference ;
}
#ifdef CLEAR_VARIANTS
VariantClear ( & result4);
#endif
}
pDisp -> Release ();
}
return selection;
}
int ConnectToNotebook (std::string newNotebookName, int runInTestMode, int firstTime)
{
// sprintf (buffer, "FM2NBOOK DLL built %s %s", __DATE__, __TIME__);
// MessageBox (0, buffer, Fm2nbookTitle, MB_ICONINFORMATION | MB_TASKMODAL);
// set the error counter to zero each time
g_Fm2nbookNumberOfErrors = 0;
g_buffer = newNotebookName;
int len = g_buffer.size ();
// remove the .OUT extension on the name if there is one
if (len > 4 && ! _strnicmp (& (g_buffer.c_str () [len - 4]), ".out", 4))
g_buffer.erase (len - 4);
// copy the new name in with a .XLS extension if not there already
len = g_buffer.size ();
if ((len < 4 || 0 != _strnicmp (& (g_buffer.c_str () [len - 4]), ".xls", 4)) &&
(len < 5 || 0 != _strnicmp (& (g_buffer.c_str () [len - 5]), ".xlsx", 5)))
g_notebookName = g_buffer + ".xls";
else
g_notebookName = g_buffer;
if (firstTime)
{
g_notebookPrefix = g_notebookName;
// strip the suffix from the notebook prefix, can be .xls or .xlsx
int len5 = g_notebookPrefix.size ();
g_notebookPrefix.erase (len5 - 4);
len5 = g_notebookPrefix.size ();
if (g_notebookPrefix [len5 - 1] == '.')
g_notebookPrefix.erase (len5 - 1);
}
Fm2nbookGetStartupDir ();
if ( ! MakeSureNotebookExists (g_notebookName))
return FALSE;
int ret = StartExcelServer ();
if ( ! ret)
{
g_buffer = "Can't start conversation with Excel.\n"
"Problem with either Excel or OLE automation.";
MessageBox (0, g_buffer.c_str (), g_Fm2nbookTitle, MB_ICONSTOP | MB_TASKMODAL);
return false;
}
// if we got here then Excel is alive and ready to take input
// get the names of the currently open spreadsheets and see if this one is open already
// otherwise open the spreadsheet
VARIANT result1;
VariantInit ( & result1);
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelWorkbooks, L"Count",
"Getting the number of currently open spreadsheets (ConnectToNotebook)", 0);
int numberOfWorkbooks = 0;
if (result1.vt == VT_I4)
numberOfWorkbooks = result1.intVal;
int workbookOpenAlready = false;
if (numberOfWorkbooks > 0)
{
for (int i = 0; i < numberOfWorkbooks && ! workbookOpenAlready; i++)
{
VARIANT result2;
VariantInit ( & result2);
VARIANT itemNumber;
itemNumber.vt = VT_I4;
// put the index of the workbook to get into the variant, the index starts with 1
itemNumber.intVal = i + 1;
OLEMethod (DISPATCH_PROPERTYGET, & result2, pExcelWorkbooks, L"Item",
"Getting the index of the spreadsheet (ConnectToNotebook)", 1, itemNumber);
if (result2.vt == VT_DISPATCH)
{
IDispatch *pDisp = result2.pdispVal;
VARIANT result3;
VariantInit ( & result3);
OLEMethod (DISPATCH_PROPERTYGET, & result3, pDisp, L"Name",
"Getting the name of the indexed spreadsheet (ConnectToNotebook)", 0);
if (result3.vt == VT_BSTR)
{
// this will be the workbook name without the path
std::string workbookName = _bstr_t (result3.bstrVal);
// strip the path from the current workbook name
std::string noPathNotebookName = g_notebookName;
int lastSlash = g_notebookName.size ();
while (lastSlash >= 0 && g_notebookName [lastSlash] != '\\')
lastSlash--;
if (lastSlash >= 0)
noPathNotebookName.erase (0, lastSlash + 1);
// do a case insensitive comparison of the file names
if (_stricmp (workbookName.c_str (), noPathNotebookName.c_str ()) == 0)
{
workbookOpenAlready = true;
// copy the dispatch pointer to the workbook pointer
pExcelWorkbook = pDisp;
// save the workbook pointer to close it later if needed
if (pExcelWorkbook)
ExcelWorkbookObjects.push_back (pExcelWorkbook);
// now activate the workbook
VARIANT result5;
VariantInit ( & result5);
OLEMethod (DISPATCH_PROPERTYGET, & result5, pDisp, L"Activate",
"Activating the spreadsheet that we want to use (ConnectToNotebook)", 0);
int res = result5.vt;
}
else
pDisp -> Release ();
#ifdef CLEAR_VARIANTS
VariantClear ( & result3);
#endif
}
}
}
}
// we need to open the spreadsheet file if not done already
if ( ! workbookOpenAlready)
{
VARIANT result;
VariantInit ( & result);
VARIANT fname;
fname.vt = VT_BSTR;
_bstr_t notebookNameBstr = _bstr_t (g_notebookName.c_str ());
fname.bstrVal = notebookNameBstr;
std::string errorMsg = "Opening the spreadsheet that we want to use, " + g_notebookName + " (ConnectToNotebook)";
OLEMethod (DISPATCH_METHOD, & result, pExcelWorkbooks, L"Open", errorMsg, 1, fname);
// copy the dispatch pointer to the workbook pointer
if (result.vt == VT_DISPATCH)
{
pExcelWorkbook = result.pdispVal;
// save the workbook pointer to close it later if needed
if (pExcelWorkbook)
ExcelWorkbookObjects.push_back (pExcelWorkbook);
}
else
{
VARIANT result5;
VariantInit ( & result5);
// adding a new spreadsheet
std::string errorMsg = "Creating the spreadsheet that we want to use, " + g_notebookName + " (ConnectToNotebook)";
OLEMethod (DISPATCH_METHOD, & result5, pExcelWorkbooks, L"Add", errorMsg, 0);
if (result5.vt == VT_DISPATCH)
{
pExcelWorkbook = result5.pdispVal;
// save the workbook pointer to close it later if needed
if (pExcelWorkbook)
ExcelWorkbookObjects.push_back (pExcelWorkbook);
}
else
return false;
VARIANT result6;
VariantInit ( & result6);
std::string errorMsg2 = "Saving the spreadsheet that we want to use, " + g_notebookName + " (ConnectToNotebook)";
OLEMethod (DISPATCH_METHOD, & result6, pExcelWorkbooks, L"Save", errorMsg2, 1, fname);
}
#ifdef CLEAR_VARIANTS
VariantClear ( & fname);
#endif
}
return TRUE;
}
int ConnectToNewNoteBook (int caseNumber)
{
// get the name of the new spreadsheet if this is not the base case
if (caseNumber > 0)
{
g_notebookName = g_notebookPrefix + " Case " + asString (caseNumber) + ".xls";
if ( ! MakeSureNotebookExists (g_notebookName))
return false;
if ( ! ConnectToNotebook (g_notebookName, RunInTestMode, false))
return false;
}
else
{
g_notebookName = g_notebookPrefix + ".xls";
}
return true;
}
int ConnectToNewSheet (std::string newSheetName, int createNewSheet, std::string baseSheetName, int deleteExistingSheet)
{
int sheetDeleted = FALSE;
int sheetOpenAlready = false;
IDispatch * pExcelSheets = NULL;
// get the names of the currently open sheets and see if this one is open already
// otherwise add the sheet and rename it
VARIANT result1;
VariantInit ( & result1);
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelWorkbook, L"Sheets",
"Getting the pointer to the Sheets collection in the active spreadsheet (ConnectToNewSheet)", 0);
if (result1.vt == VT_DISPATCH)
{
pExcelSheets = result1.pdispVal;
VARIANT result2;
VariantInit ( & result2);
OLEMethod (DISPATCH_PROPERTYGET, & result2, pExcelSheets, L"Count",
"Get the number of sheets in the spreadsheet (ConnectToNewSheet)", 0);
int numberOfSheets = 0;
if (result2.vt == VT_I4)
numberOfSheets = result2.intVal;
if (numberOfSheets > 0)
{
for (int i = 0; i < numberOfSheets && ! sheetOpenAlready; i++)
{
VARIANT result3;
VariantInit ( & result3);
VARIANT itemNumber;
itemNumber.vt = VT_I4;
// put the index of the workbook to get into the variant, the index starts with 1
itemNumber.intVal = i + 1;
OLEMethod (DISPATCH_PROPERTYGET, & result3, pExcelSheets, L"Item",
"Set the index of the sheet of the active spreadsheet (ConnectToNewSheet)", 1, itemNumber);
if (result3.vt == VT_DISPATCH)
{
IDispatch *pDisp = result3.pdispVal;
VARIANT result4;
VariantInit ( & result4);
OLEMethod (DISPATCH_PROPERTYGET, & result4, pDisp, L"Name",
"Get the name of the indexed sheet of the spreadsheet (ConnectToNewSheet)", 0);
if (result4.vt == VT_BSTR)
{
// this will be the sheet name
std::string sheetName = _bstr_t (result4.bstrVal);
if (sheetName == newSheetName)
{
sheetOpenAlready = true;
// if there is a current excel sheet then release it
if (pExcelSheet)
{
pExcelSheet -> Release ();
pExcelSheet = NULL;
}
// copy the dispatch pointer to the workbook pointer
pExcelSheet = pDisp;
VARIANT result5;
VariantInit ( & result5);
OLEMethod (DISPATCH_PROPERTYGET, & result5, pDisp, L"Activate",
"Activate the sheet of the spreadsheet (ConnectToNewSheet)", 0);
int res = result5.vt;
if (deleteExistingSheet)
{
sheetOpenAlready = false;
pExcelSheet = NULL;
// gotta turn off the display alerts or will get a message from excel asking if it can delete
VARIANT result6;
VariantInit ( & result6);
VARIANT displayAlerts;
VariantInit ( & displayAlerts);
displayAlerts.vt = VT_BOOL;
displayAlerts.boolVal = false;
OLEMethod (DISPATCH_PROPERTYPUT, & result6, pExcelApplication, L"DisplayAlerts",
"Turn display alerts off so we can delete a sheet in silence (ConnectToNewSheet)",
1, displayAlerts);
int res6 = result6.vt;
VARIANT result7;
VariantInit ( & result7);
OLEMethod (DISPATCH_PROPERTYGET, & result7, pDisp, L"Delete",
"Delete the current sheet in the spreadsheet (ConnectToNewSheet)", 0);
int res7 = result7.vt;
// gotta turn off the display alerts or will get a message from excel asking if it can delete
VARIANT result8;
VariantInit ( & result8);
VARIANT displayAlerts8;
VariantInit ( & displayAlerts8);
displayAlerts8.vt = VT_BOOL;
displayAlerts8.boolVal = true;
OLEMethod (DISPATCH_PROPERTYPUT, & result8, pExcelApplication, L"DisplayAlerts",
"Turn display alerts back on after deleting a sheet in the spreadsheet (ConnectToNewSheet)",
1, displayAlerts8);
int res8 = result8.vt;
pDisp -> Release ();
// get out of this for loop since we are finished with it
break;
}
}
else
pDisp -> Release ();
#ifdef CLEAR_VARIANTS
VariantClear ( & result4);
#endif
}
}
}
}
}
else
return false;
// we need to create the sheet file if not done already
if ( ! sheetOpenAlready && pExcelSheets && createNewSheet)
{
pExcelSheets = result1.pdispVal;
VARIANT result2;
VariantInit ( & result2);
OLEMethod (DISPATCH_PROPERTYGET, & result2, pExcelSheets, L"Count",
"Getting the number of sheets in the active spreadsheet (ConnectToNewSheet)", 0);
int numberOfSheets = 0;
if (result2.vt == VT_I4)
numberOfSheets = result2.intVal;
if (numberOfSheets > 0)
{
for (int i = 0; i < numberOfSheets && ! sheetOpenAlready; i++)
{
VARIANT result3;
VariantInit ( & result3);
VARIANT itemNumber;
itemNumber.vt = VT_I4;
// put the index of the workbook to get into the variant, the index starts with 1
itemNumber.intVal = i + 1;
OLEMethod (DISPATCH_PROPERTYGET, & result3, pExcelSheets, L"Item",
"Set the index of the active sheet of the spreadsheet (ConnectToNewSheet)", 1, itemNumber);
if (result3.vt == VT_DISPATCH)
{
IDispatch *pDisp = result3.pdispVal;
VARIANT result4;
VariantInit ( & result4);
OLEMethod (DISPATCH_PROPERTYGET, & result4, pDisp, L"Name",
"Get the name of the indexed sheet of the active spreadsheet (ConnectToNewSheet)", 0);
if (result4.vt == VT_BSTR)
{
// this will be the sheet name
std::string sheetName = _bstr_t (result4.bstrVal);
if (sheetName == baseSheetName)
{
sheetOpenAlready = true;
// if there is a current excel sheet then release it
if (pExcelSheet)
{
pExcelSheet -> Release ();
pExcelSheet = NULL;
}
// copy the dispatch pointer to the workbook pointer
pExcelSheet = pDisp;
VARIANT result5;
VariantInit ( & result5);
OLEMethod (DISPATCH_PROPERTYGET, & result5, pDisp, L"Activate",
"Set the spreadsheet back to the sheet in the active spreadsheet (ConnectToNewSheet)", 0);
int res = result5.vt;
}
else
pDisp -> Release ();
#ifdef CLEAR_VARIANTS
VariantClear ( & result4);
#endif
}
}
}
}
// now create the new sheet, hopefully in the proper place
VARIANT result3;
VariantInit ( & result3);
OLEMethod (DISPATCH_METHOD, & result3, pExcelSheets, L"Add",
"Add a new sheet to the active spreadsheet (ConnectToNewSheet)", 0);
// copy the dispatch pointer to the sheet pointer
if (result3.vt == VT_DISPATCH)
{
// if there is a current excel sheet then release it
if (pExcelSheet)
{
pExcelSheet -> Release ();
pExcelSheet = NULL;
}
pExcelSheet = result3.pdispVal;
// now name the sheet to the new name
VARIANT result4;
VariantInit ( & result4);
VARIANT fnameNew;
fnameNew.vt = VT_BSTR;
_bstr_t sheetNameNewBstr = _bstr_t (newSheetName.c_str ());
fnameNew.bstrVal = sheetNameNewBstr;
OLEMethod (DISPATCH_PROPERTYPUT, & result4, pExcelSheet, L"Name",
"Set the name of the new sheet in the active spreadsheet (ConnectToNewSheet)", 1, fnameNew);
int res = result4.vt;
#ifdef CLEAR_VARIANTS
VariantClear ( & fnameNew);
#endif
}
else
return false;
}
if (pExcelSheets)
pExcelSheets -> Release ();
return true;
}
void ReportError (DWORD result, std::string job, std::string msg)
{
g_Fm2nbookNumberOfErrors++;
// only report the first ten errors
if (g_Fm2nbookNumberOfErrors < 11)
{
#ifdef USE_DDE_FOR_EXCEL
char buff [10000];
char * ddeError = "unknown";
UINT lastError = DdeGetLastError (InstId);
switch (lastError)
{
case DMLERR_ADVACKTIMEOUT:
ddeError = "A request for a synchronous advise transaction has timed out.";
break;
case DMLERR_BUSY:
ddeError = "The response to the transaction caused the DDE_FBUSY flag to be set.";
break;
case DMLERR_DATAACKTIMEOUT:
ddeError = "A request for a synchronous data transaction has timed out.";
break;
case DMLERR_DLL_NOT_INITIALIZED:
ddeError = "A DDEML function was called without first calling the DdeInitialize \n function, or an invalid instance identifier was
passed to a DDEML function.";
break;
case DMLERR_DLL_USAGE:
ddeError = "An application initialized as APPCLASS_MONITOR has attempted to \n perform a Dynamic Data Exchange (DDE) transaction";
break;
case DMLERR_EXECACKTIMEOUT:
ddeError = "A request for a synchronous execute transaction has timed out.";
break;
case DMLERR_INVALIDPARAMETER:
ddeError = "A parameter failed to be validated by the DDEML.";
break;
case DMLERR_LOW_MEMORY:
ddeError = "A DDEML application has created a prolonged race condition (in \n which the server application outruns the client),
causing large amounts of \n memory to be consumed.";
break;
case DMLERR_MEMORY_ERROR:
ddeError = "A memory allocation has failed.";
break;
case DMLERR_NO_CONV_ESTABLISHED:
ddeError = "A client's attempt to establish a conversation has failed.";
break;
case DMLERR_NOTPROCESSED:
ddeError = "A transaction has failed.";
break;
case DMLERR_POKEACKTIMEOUT:
ddeError = "A request for a synchronous poke transaction has timed out.";
break;
case DMLERR_POSTMSG_FAILED:
ddeError = "An internal call to the PostMessage function has failed.";
break;
case DMLERR_REENTRANCY:
ddeError = "An application instance with a synchronous transaction already in \n progress attempted to initiate another
synchronous transaction, or the \n DdeEnableCallback function was called from within a DDEML callback function.";
break;
case DMLERR_SERVER_DIED:
ddeError = "A server-side transaction was attempted on a conversation terminated \n by the client, or the server terminated before
completing a transaction.";
break;
case DMLERR_SYS_ERROR:
ddeError = "An internal error has occurred in the DDEML.";
break;
case DMLERR_UNADVACKTIMEOUT:
ddeError = "A request to end an advise transaction has timed out.";
break;
case DMLERR_UNFOUND_QUEUE_ID:
ddeError = "An invalid transaction identifier was passed to a DDEML function.";
break;
}
sprintf_s (buff, sizeof (buff), "Spreadsheet DDE server reported error %x\n"
"(DDE error code %x) for %s\n\n"
"\"%s\"\n\n"
"DDE Error: %s\n\n"
"Number Of Rows = %d\n"
"Current Column = %d\n\n"
"The Excel Reply for the Initial Selection is:\n"
"\"%s\"\n\n"
"The Excel Row Character is '%c'\n"
"The Excel Column Character is '%c'\n",
result, lastError, job.c_str (), msg.c_str (), ddeError, numberOfRows, currentColumn,
SavedResponseFromExcel, ExcelRowCharacter, ExcelColumnCharacter);
MessageBox (0, buff, Fm2nbookTitle, MB_ICONSTOP | MB_TASKMODAL);
#endif
}
}
void PutStringInServer (int row, int column, const char * str)
{
if (str)
{
std::string newStr = str;
PutStringInServer (row, column, newStr);
}
}
void PutStringInServer (int row, int column, std::string string)
{
// get rid of the trailing blanks
string.erase (string.find_last_not_of (' ') + 1);
int len = string.size ();
if (len == 0)
return;
std::string cellAddress = GetCellEquivalent (row, column);
VARIANT result1;
VariantInit ( & result1);
VARIANT range;
VariantInit ( & range);
range.vt = VT_BSTR;
_bstr_t address = _bstr_t (cellAddress.c_str ());
range.bstrVal = address;
std::string errorMsg = "Select a range in the active spreadsheet, " + cellAddress +
" (PutStringInServer)";
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelSheet, L"Range", errorMsg, 1, range);
if (result1.vt == VT_DISPATCH)
{
VARIANT result2;
VariantInit ( & result2);
VARIANT value;
VariantInit ( & value);
value.vt = VT_BSTR;
_bstr_t valueBSTR = _bstr_t (string.c_str ());
value.bstrVal = valueBSTR;
std::string errorMsg = "Put the string into the selected cell, " + cellAddress + ", \"" + string + "\" (PutStringInServer)";
OLEMethod (DISPATCH_PROPERTYPUT, & result2, result1.pdispVal, L"Value", errorMsg, 1, value);
int res = result2.vt;
result1.pdispVal -> Release ();
#ifdef CLEAR_VARIANTS
VariantClear ( & value);
#endif
}
#ifdef CLEAR_VARIANTS
VariantClear ( & range);
#endif
}
void PutDoubleInServer (int row, int column, const double num)
{
if (uninitializedValue == num)
return;
std::string cellAddress = GetCellEquivalent (row, column);
VARIANT result1;
VariantInit ( & result1);
VARIANT range;
VariantInit ( & range);
range.vt = VT_BSTR;
_bstr_t address = _bstr_t (cellAddress.c_str ());
range.bstrVal = address;
std::string errorMsg = "Select a range in the active spreadsheet, " + cellAddress + " (PutDoubleInServer)";
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelSheet, L"Range", errorMsg, 1, range);
if (result1.vt == VT_DISPATCH)
{
VARIANT result2;
VariantInit ( & result2);
VARIANT value;
VariantInit ( & value);
value.vt = VT_R8;
value.dblVal = num;
std::string errorMsg = "Put the double into the selected cell, " + cellAddress + " (PutDoubleInServer)";
OLEMethod (DISPATCH_PROPERTYPUT, & result2, result1.pdispVal, L"Value", errorMsg, 1, value);
int res = result2.vt;
result1.pdispVal -> Release ();
#ifdef CLEAR_VARIANTS
VariantClear ( & value);
#endif
}
#ifdef CLEAR_VARIANTS
VariantClear ( & range);
#endif
}
void PutDoubleInServerVertical (int row, int column, int count, const double num [])
{
// put the doubles in vertically
// Create a 1 by count safearray of variants...
// get safe array info from http://support.microsoft.com/kb/216686
VARIANT arr;
VariantInit ( & arr);
arr.vt = VT_ARRAY | VT_VARIANT;
{
SAFEARRAYBOUND sab[2];
sab[0].lLbound = 1;
sab[0].cElements = count;
sab[1].lLbound = 1;
sab[1].cElements = 1;
arr.parray = SafeArrayCreate (VT_VARIANT, 2, sab);
}
// Fill safearray with some values...
for (int i = 1; i <= count; i++)
{
for (int j = 1; j <= 1; j++)
{
// Create entry value for (i,j)
VARIANT tmp;
VariantInit ( & tmp);
if (uninitializedValue == num [i - 1])
{
tmp.vt = VT_EMPTY;
}
else
{
tmp.vt = VT_R8;
tmp.dblVal = num [i - 1];
}
// Add to safearray...
long indices [] = {i, j};
SafeArrayPutElement (arr.parray, indices, (void *) & tmp);
}
}
// send the array to excel
std::string cellAddress = GetCellEquivalent (row, column) + ":" + GetCellEquivalent (row + count - 1, column);
VARIANT result1;
VariantInit ( & result1);
VARIANT range;
VariantInit ( & range);
range.vt = VT_BSTR;
_bstr_t address = _bstr_t (cellAddress.c_str ());
range.bstrVal = address;
std::string errorMsg = "Select a range in the active spreadsheet, " + cellAddress + " (PutDoubleInServerVertical)";
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelSheet, L"Range", errorMsg, 1, range);
if (result1.vt == VT_DISPATCH)
{
VARIANT result2;
VariantInit ( & result2);
std::string errorMsg = "Put the double into the selected cell, " + cellAddress + " (PutDoubleInServerVertical)";
OLEMethod (DISPATCH_PROPERTYPUT, & result2, result1.pdispVal, L"Value", errorMsg, 1, arr);
int res = result2.vt;
result1.pdispVal -> Release ();
}
#ifdef CLEAR_VARIANTS
VariantClear ( & arr);
VariantClear ( & range);
#endif
}
void PutDoubleInServerHorizontal (int row, int column, int count, const double num [])
{
// put the doubles in horizontally
// Create a 1 by count safearray of variants...
// get safe array info from http://support.microsoft.com/kb/216686
VARIANT arr;
VariantInit ( & arr);
arr.vt = VT_ARRAY | VT_VARIANT;
{
SAFEARRAYBOUND sab[2];
sab[0].lLbound = 1;
sab[0].cElements = 1;
sab[1].lLbound = 1;
sab[1].cElements = count;
arr.parray = SafeArrayCreate (VT_VARIANT, 2, sab);
}
// Fill safearray with some values...
for (int i = 1; i <= 1; i++)
{
for (int j = 1; j <= count; j++)
{
// Create entry value for (i,j)
VARIANT tmp;
VariantInit ( & tmp);
if (uninitializedValue == num [j - 1])
{
tmp.vt = VT_EMPTY;
}
else
{
tmp.vt = VT_R8;
tmp.dblVal = num [j - 1];
}
// Add to safearray...
long indices [] = {i, j};
SafeArrayPutElement (arr.parray, indices, (void *) & tmp);
}
}
// send the array to excel
std::string cellAddress = GetCellEquivalent (row, column) + ":" + GetCellEquivalent (row, column + count - 1);
VARIANT result1;
VariantInit ( & result1);
VARIANT range;
VariantInit ( & range);
range.vt = VT_BSTR;
_bstr_t address = _bstr_t (cellAddress.c_str ());
range.bstrVal = address;
std::string errorMsg = "Select a range in the active spreadsheet, " + cellAddress + " (PutDoubleInServerHorizontal)";
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelSheet, L"Range", errorMsg, 1, range);
if (result1.vt == VT_DISPATCH)
{
VARIANT result2;
VariantInit ( & result2);
std::string errorMsg = "Put the double into the selected cell, " + cellAddress + " (PutDoubleInServerHorizontal)";
OLEMethod (DISPATCH_PROPERTYPUT, & result2, result1.pdispVal, L"Value", errorMsg, 1, arr);
int res = result2.vt;
result1.pdispVal -> Release ();
}
#ifdef CLEAR_VARIANTS
VariantClear ( & arr);
VariantClear ( & range);
#endif
}
void PutIntegerInServer (const int row, const int column, const int num)
{
std::string cellAddress = GetCellEquivalent (row, column);
VARIANT result1;
VariantInit ( & result1);
VARIANT range;
VariantInit ( & range);
range.vt = VT_BSTR;
_bstr_t address = _bstr_t (cellAddress.c_str ());
range.bstrVal = address;
std::string errorMsg = "Select a range in the active spreadsheet, " + cellAddress + " (PutIntegerInServer)";
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelSheet, L"Range", errorMsg, 1, range);
if (result1.vt == VT_DISPATCH)
{
VARIANT result2;
VariantInit ( & result2);
VARIANT value;
VariantInit ( & value);
value.vt = VT_I4;
value.intVal = num;
std::string errorMsg = "Put the integer into the selected cell, " + cellAddress + " (PutIntegerInServer)";
OLEMethod (DISPATCH_PROPERTYPUT, & result2, result1.pdispVal, L"Value", errorMsg, 1, value);
int res = result2.vt;
result1.pdispVal -> Release ();
}
#ifdef CLEAR_VARIANTS
VariantClear ( & range);
#endif
}
std::string GetCellEquivalent (const int row, const int column)
{
// column can be A to IV or A to XFD for Excel 2007
// column basis is one
// row can be 1 to 65,536 or 1 to 1,048,576 for Excel 2007
// row basis is one
std::string ret = "";
// calculate on a zero basis so subtract one
int columnZeroBased = column - 1;
// Repeatedly divide the number by 26 and convert the
// remainder into the appropriate letter.
do
{
int remainder = columnZeroBased % 26;
ret = (char)(remainder + 'A') + ret;
columnZeroBased = columnZeroBased / 26 - 1;
}
while (columnZeroBased >= 0);
ret += asString (row);
return ret;
}
std::string GetCellRowEquivalent (const int row)
{
// column can be A to IV or A to XFD for Excel 2007
// row can be 1 to 65,536 or 1 to 1,048,576 for Excel 2007
std::string ret = "";
ret += asString (row);
return ret;
}
std::string GetCellColumnEquivalent (const int column)
{
// column can be A to IV or A to XFD for Excel 2007
// column basis is one
// row can be 1 to 65,536 or 1 to 1,048,576 for Excel 2007
// row basis is one
std::string ret = "";
// calculate on a zero basis so subtract one
int columnZeroBased = column - 1;
// Repeatedly divide the number by 26 and convert the
// remainder into the appropriate letter.
do
{
int remainder = columnZeroBased % 26;
ret = (char)(remainder + 'A') + ret;
columnZeroBased = columnZeroBased / 26 - 1;
}
while (columnZeroBased >= 0);
return ret;
}
void NameCellInServer (std::string cellName, std::string sheetName,
const int row, const int column)
{
std::string cellAddress = GetCellEquivalent (row, column);
VARIANT result1;
VariantInit ( & result1);
VARIANT range;
VariantInit ( & range);
range.vt = VT_BSTR;
_bstr_t address = _bstr_t (cellAddress.c_str ());
range.bstrVal = address;
std::string errorMsg = "Select a range in the active spreadsheet, " + cellAddress + " (NameCellInServer)";
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelSheet, L"Range", errorMsg, 1, range);
if (result1.vt == VT_DISPATCH)
{
VARIANT result2;
VariantInit ( & result2);
VARIANT name;
VariantInit ( & name);
name.vt = VT_BSTR;
_bstr_t rangeName = _bstr_t (cellName.c_str ());
name.bstrVal = rangeName;
std::string errorMsg = "Name the selected cell, " + cellAddress + " (NameCellInServer)";
OLEMethod (DISPATCH_PROPERTYPUT, & result2, result1.pdispVal, L"Name", errorMsg, 1, name);
int res = result2.vt;
result1.pdispVal -> Release ();
#ifdef CLEAR_VARIANTS
VariantClear ( & name);
#endif
}
#ifdef CLEAR_VARIANTS
VariantClear ( & range);
#endif
}
// sheetName is the Excel sheet name
// column1 is A to IV for Excel 95 to 2003, A to XFP for Excel 2007+
// column2 is probably the same as column1
// row1 is 1 to 65536 for Excel 95 to 2003, 1 to
// row2 is below row1, hopefully
std::vector <std::string> getCellInfo (std::string sheetName, std::string column1, std::string column2,
std::string row1, std::string row2)
{
std::vector <std::string> cell;
// do create the sheet if it does not exist yet but do not delete it
if (ConnectToNewSheet (sheetName, true, "", false))
{
int row1Int = atoi (row1.c_str ());
int row2Int = atoi (row2.c_str ());
for (int i = row1Int; i <= row2Int; i++)
{
int row = i;
int len = column1.size ();
int column = 0;
// convert "AC" to numeric 29 or "XFD" to 16384 or "IV" to 256
for (int j = 0; j < len; j++)
column = column * 26 + column1.c_str () [j] - 'A' + 1;
std::string cellAddress = GetCellEquivalent (row, column);
std::string reply = "";
VARIANT result1;
VariantInit ( & result1);
VARIANT range;
VariantInit ( & range);
range.vt = VT_BSTR;
_bstr_t address = _bstr_t (cellAddress.c_str ());
range.bstrVal = address;
std::string errorMsg = "Select a range in the active spreadsheet (getCellInfo)";
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelSheet, L"Range", errorMsg, 1, range);
if (result1.vt == VT_DISPATCH)
{
VARIANT result2;
VariantInit ( & result2);
std::string errorMsg = "Get the data of the selected cell, " + cellAddress + " (getCellInfo)";
OLEMethod (DISPATCH_PROPERTYGET, & result2, result1.pdispVal, L"Value", errorMsg, 0);
if (result2.vt == VT_I4)
reply = asString (result2.intVal);
else if (result2.vt == VT_R4)
reply = asString (result2.fltVal);
else if (result2.vt == VT_R8)
reply = asString (result2.dblVal);
else if (result2.vt == VT_BSTR)
reply = _bstr_t (result2.bstrVal);
result1.pdispVal -> Release ();
#ifdef CLEAR_VARIANTS
VariantClear ( & result2);
#endif
}
// MessageBox (0, reply.c_str (), cellAddress.c_str (), MB_ICONINFORMATION | MB_TASKMODAL);
cell.push_back (reply);
#ifdef CLEAR_VARIANTS
VariantClear ( & range);
#endif
}
}
return cell;
}
// sheetName is the Excel sheet name
// column1 is A to IV for Excel 95 to 2003, A to XFP for Excel 2007+
// column2 is probably the same as column1
// row1 is 1 to 65536 for Excel 95 to 2003, 1 to
// row2 is below row1, hopefully
// dimensional units placement has the following value range:
// 0 = dont transfer dimensional units, 1 = above this cell, 2 = below, 3 = left, 4 = right
int putCellInfo (std::vector <std::string> results, std::string sheetName, std::string column1,
std::string column2, std::string row1, std::string row2,
std::string dimensionalUnitsValue, int dimensionalUnitsPlacement)
{
int error = false;
// do create the sheet if it does not exist yet but do not delete it
if (ConnectToNewSheet (sheetName, true, "", false))
{
int row1Int = atoi (row1.c_str ());
int row2Int = atoi (row2.c_str ());
for (int i = row1Int; i <= row2Int; i++)
{
int row = i;
int len = column1.size ();
int column = 0;
// convert "AC" to numeric 29 or "XFD" to 16384 or "IV" to 256
for (int j = 0; j < len; j++)
column = column * 26 + column1.c_str () [j] - 'A' + 1;
std::string cellAddress = GetCellEquivalent (row, column);
VARIANT result1;
VariantInit ( & result1);
VARIANT range;
VariantInit ( & range);
range.vt = VT_BSTR;
_bstr_t address = _bstr_t (cellAddress.c_str ());
range.bstrVal = address;
std::string errorMsg = "Select a range in the active spreadsheet, " + cellAddress + " (putCellInfo)";
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelSheet, L"Range", errorMsg, 1, range);
if (result1.vt == VT_DISPATCH)
{
VARIANT result2;
VariantInit ( & result2);
VARIANT value;
VariantInit ( & value);
value.vt = VT_BSTR;
_bstr_t valueBSTR = _bstr_t (results [i - row1Int].c_str ());
value.bstrVal = valueBSTR;
std::string errorMsg = "Put the data into the selected cell, " + cellAddress + " (putCellInfo)";
OLEMethod (DISPATCH_PROPERTYPUT, & result2, result1.pdispVal, L"Value", errorMsg, 1, value);
int res = result2.vt;
result1.pdispVal -> Release ();
#ifdef CLEAR_VARIANTS
VariantClear ( & value);
#endif
}
// MessageBox (0, reply.c_str (), cellAddress.c_str (), MB_ICONINFORMATION | MB_TASKMODAL);
#ifdef CLEAR_VARIANTS
VariantClear ( & range);
#endif
}
if (dimensionalUnitsPlacement > 0)
{
int row = row1Int;
int column = 0;
int len = column1.size ();
// convert "AC" to numeric 29 or "XFD" to 16384 or "IV" to 256
for (int j = 0; j < len; j++)
column = column * 26 + column1.c_str () [j] - 'A' + 1;
// 0 = dont transfer dimensional units, 1 = above this cell, 2 = below, 3 = left, 4 = right
switch (dimensionalUnitsPlacement)
{
case 1:
row -= 1;
if (row < 1)
row = 1;
break;
case 2:
// if the user wants the units below then must be below ALL the numbers
row += row2Int - row1Int + 1;
if (row > 1048576)
row = 1048576;
break;
case 3:
column -= 1;
if (column < 1)
column = 1;
break;
case 4:
column += 1;
if (column > 16384)
column = 16384;
break;
}
std::string cellAddress = GetCellEquivalent (row, column);
VARIANT result1;
VariantInit ( & result1);
VARIANT range;
VariantInit ( & range);
range.vt = VT_BSTR;
_bstr_t address = _bstr_t (cellAddress.c_str ());
range.bstrVal = address;
std::string errorMsg = "Select a range for the dimensional units in the active spreadsheet, " + cellAddress + " (putCellInfo)";
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelSheet, L"Range", errorMsg, 1, range);
if (result1.vt == VT_DISPATCH)
{
VARIANT result2;
VariantInit ( & result2);
VARIANT value;
VariantInit ( & value);
value.vt = VT_BSTR;
_bstr_t valueBSTR = _bstr_t (dimensionalUnitsValue.c_str ());
value.bstrVal = valueBSTR;
std::string errorMsg = "Put the dimensional units into the selected cell, " + cellAddress + " (putCellInfo)";
OLEMethod (DISPATCH_PROPERTYPUT, & result2, result1.pdispVal, L"Value", errorMsg, 1, value);
int res = result2.vt;
result1.pdispVal -> Release ();
#ifdef CLEAR_VARIANTS
VariantClear ( & value);
#endif
}
#ifdef CLEAR_VARIANTS
VariantClear ( & range);
#endif
}
}
else
error = true;
return error;
}
// colorValue should be an RGB (r,g,b)
int setRangeFont (std::string rangeValue, std::string fontNameValue, int boldValue, int sizeValue, int colorValue)
{
VARIANT result1;
VariantInit ( & result1);
VARIANT range;
VariantInit ( & range);
range.vt = VT_BSTR;
_bstr_t address = _bstr_t (rangeValue.c_str ());
range.bstrVal = address;
std::string errorMsg = "Select a range in the active spreadsheet, " + rangeValue + " (setRangeFont)";
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelSheet, L"Range", errorMsg, 1, range);
if (result1.vt == VT_DISPATCH)
{
VARIANT result2;
VariantInit ( & result2);
std::string errorMsg = "Get the font type of the selected range, " + rangeValue + " (setRangeFont)";
OLEMethod (DISPATCH_PROPERTYGET, & result2, result1.pdispVal, L"Font", errorMsg, 0);
if (result2.vt == VT_DISPATCH)
{
if (fontNameValue.size () > 0)
{
VARIANT result3;
VariantInit ( & result3);
VARIANT fontName;
VariantInit ( & fontName);
fontName.vt = VT_BSTR;
_bstr_t fontNameBSTR = _bstr_t (fontNameValue.c_str ());
fontName.bstrVal = fontNameBSTR;
std::string errorMsg = "Set the current selection to a font in the active spreadsheet, " + rangeValue + " (setRangeFont)";
OLEMethod (DISPATCH_PROPERTYPUT, & result3, result2.pdispVal, L"Name", errorMsg, 1, fontName);
int res3 = result3.vt;
#ifdef CLEAR_VARIANTS
VariantClear ( & fontName);
#endif
}
if (boldValue > 0)
{
VARIANT result4;
VariantInit ( & result4);
VARIANT bold;
VariantInit ( & bold);
bold.vt = VT_BOOL;
bold.boolVal = boldValue;
std::string errorMsg = "Set the current selection to bold in the active spreadsheet, " + rangeValue + " (setRangeFont)";
OLEMethod (DISPATCH_PROPERTYPUT, & result4, result2.pdispVal, L"Bold", errorMsg, 1, bold);
int res4 = result4.vt;
}
if (sizeValue > 0)
{
VARIANT result5;
VariantInit ( & result5);
VARIANT size;
VariantInit ( & size);
size.vt = VT_I4;
size.intVal = sizeValue;
std::string errorMsg = "Set the current selection to a size in the active spreadsheet, " + rangeValue + " (setRangeFont)";
OLEMethod (DISPATCH_PROPERTYPUT, & result5, result2.pdispVal, L"Size", errorMsg, 1, size);
int res5 = result5.vt;
}
if (colorValue > 0)
{
VARIANT result6;
VariantInit ( & result6);
VARIANT color;
VariantInit ( & color);
color.vt = VT_I4;
color.intVal = colorValue;
std::string errorMsg = "Set the current selection to a color in the active spreadsheet, " + rangeValue + " (setRangeFont)";
OLEMethod (DISPATCH_PROPERTYPUT, & result6, result2.pdispVal, L"Color", errorMsg, 1, color);
int res6 = result6.vt;
}
result2.pdispVal -> Release ();
}
result1.pdispVal -> Release ();
}
#ifdef CLEAR_VARIANTS
VariantClear ( & range);
#endif
return true;
}
int SetRangeWidthAlignment (std::string rangeValue, int columnWidthValue, int horizontalAlignmentValue)
{
VARIANT result1;
VariantInit ( & result1);
VARIANT range;
VariantInit ( & range);
range.vt = VT_BSTR;
_bstr_t address = _bstr_t (rangeValue.c_str ());
range.bstrVal = address;
std::string errorMsg = "Select a range in the active spreadsheet," + rangeValue + " (SetRangeWidthAlignment)";
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelSheet, L"Range", errorMsg, 1, range);
if (result1.vt == VT_DISPATCH)
{
if (columnWidthValue > 0)
{
VARIANT result2;
VariantInit ( & result2);
VARIANT columnWidth;
VariantInit ( & columnWidth);
columnWidth.vt = VT_I4;
columnWidth.intVal = columnWidthValue;
std::string errorMsg = "Set the column width of the selected cell," + rangeValue + " (SetRangeWidthAlignment)";
OLEMethod (DISPATCH_PROPERTYPUT, & result2, result1.pdispVal, L"ColumnWidth", errorMsg, 1, columnWidth);
int res2 = result2.vt;
}
if (horizontalAlignmentValue != 0)
{
// Excel Constants
// var xlCellTypeLastCell = 11;
// var xlThin = 2;
// var xlSolid = 1;
// int xlCenter = -4108;
// var xlBottom = -4107;
// var xlTop = -4160;
// var xlRight = -4152;
// var xlLeft = -4131;
// var xlNone = -4142;
// var xlContinuous = 1;
// var xlThin = 2;
// var xlAutomatic = -4105;
// var xlExpression = 2;
VARIANT result5;
VariantInit ( & result5);
VARIANT horizontalAlignment;
VariantInit ( & horizontalAlignment);
horizontalAlignment.vt = VT_I4;
horizontalAlignment.intVal = horizontalAlignmentValue;
std::string errorMsg = "Set horizontal alingment of the selected range," + rangeValue + " (SetRangeWidthAlignment)";
OLEMethod (DISPATCH_PROPERTYPUT, & result5, result1.pdispVal, L"HorizontalAlignment", errorMsg, 1, horizontalAlignment);
int res5 = result5.vt;
}
result1.pdispVal -> Release ();
}
#ifdef CLEAR_VARIANTS
VariantClear ( & range);
#endif
return true;
}
int TwoDimensionTableRowCount = 0;
int TwoDimensionTableColumnCount = 0;
int TwoDimensionTableNumberOfColumns = 0;
VARIANT TwoDimensionArray;
// I am not sure if count is a actual count of objects or a maximum number of objects
void InitTable (int numberOfRows, int numberOfColumns)
{
// put the doubles or strings in horizontally
// Create a row by column safearray of variants...
// get safe array info from http://support.microsoft.com/kb/216686
VariantInit ( & TwoDimensionArray);
TwoDimensionArray.vt = VT_ARRAY | VT_VARIANT;
{
SAFEARRAYBOUND sab[2];
sab[0].lLbound = 1;
// make the number of rows 2000 for now since we really dont know how
// many rows that there will be in the end
if (numberOfRows < 2000)
sab[0].cElements = 2000;
else
sab[0].cElements = numberOfRows;
sab[1].lLbound = 1;
sab[1].cElements = numberOfColumns;
TwoDimensionArray.parray = SafeArrayCreate (VT_VARIANT, 2, sab);
}
// init the row count at 1 for the first row
TwoDimensionTableRowCount = 1;
// init the column count at zero so can be incremented at the beginning of each element put
TwoDimensionTableColumnCount = 0;
TwoDimensionTableNumberOfColumns = numberOfColumns;
}
void PutDoubleInTable (int numberOfDoubles, double * num)
{
// Fill safearray with some values...
// Create entry value for (i,j)
for (int i = 0; i < numberOfDoubles; i++)
{
TwoDimensionTableColumnCount++;
if (TwoDimensionTableColumnCount > TwoDimensionTableNumberOfColumns)
{
TwoDimensionTableRowCount++;
TwoDimensionTableColumnCount = 1;
}
VARIANT tmp;
VariantInit ( & tmp);
if (uninitializedValue == num )
{
tmp.vt = VT_EMPTY;
}
else
{
tmp.vt = VT_R8;
tmp.dblVal = num ;
}
// Add to safearray...
long indices [] = {TwoDimensionTableRowCount, TwoDimensionTableColumnCount};
SafeArrayPutElement (TwoDimensionArray.parray, indices, (void *) & tmp);
}
}
void PutDoubleInTable (double num)
{
// Fill safearray with some values...
// Create entry value for (i,j)
TwoDimensionTableColumnCount++;
if (TwoDimensionTableColumnCount > TwoDimensionTableNumberOfColumns)
{
TwoDimensionTableRowCount++;
TwoDimensionTableColumnCount = 1;
}
VARIANT tmp;
VariantInit ( & tmp);
if (uninitializedValue == num)
{
tmp.vt = VT_EMPTY;
}
else
{
tmp.vt = VT_R8;
tmp.dblVal = num;
}
// Add to safearray...
long indices [] = {TwoDimensionTableRowCount, TwoDimensionTableColumnCount};
SafeArrayPutElement (TwoDimensionArray.parray, indices, (void *) & tmp);
}
void PutStringInTable (std::string str)
{
// get rid of the trailing blanks
str.erase (str.find_last_not_of (' ') + 1);
TwoDimensionTableColumnCount++;
if (TwoDimensionTableColumnCount > TwoDimensionTableNumberOfColumns)
{
TwoDimensionTableRowCount++;
TwoDimensionTableColumnCount = 1;
}
// Fill safearray with some values...
// Create entry value for (i,j)
VARIANT tmp;
VariantInit ( & tmp);
// if no string then punt to the blank entry
if (str.size () > 0)
{
tmp.vt = VT_BSTR;
_bstr_t valueBSTR = _bstr_t (str.c_str ());
tmp.bstrVal = valueBSTR;
}
else
tmp.vt = VT_EMPTY;
// Add to safearray...
long indices [] = {TwoDimensionTableRowCount, TwoDimensionTableColumnCount};
SafeArrayPutElement (TwoDimensionArray.parray, indices, (void *) & tmp);
// do not call variant clear on tmp because it appears that safe array
// put element does not do a deep copy on the variant bstr
// the strings will be corrupted when they get to the spreadsheet
// VariantClear ( & tmp);
}
void PutIntegerInTable (int number)
{
TwoDimensionTableColumnCount++;
if (TwoDimensionTableColumnCount > TwoDimensionTableNumberOfColumns)
{
TwoDimensionTableRowCount++;
TwoDimensionTableColumnCount = 1;
}
// Fill safearray with some values...
// Create entry value for (i,j)
VARIANT tmp;
VariantInit ( & tmp);
tmp.vt = VT_I4;
tmp.intVal = number;
// Add to safearray...
long indices [] = {TwoDimensionTableRowCount, TwoDimensionTableColumnCount};
SafeArrayPutElement (TwoDimensionArray.parray, indices, (void *) & tmp);
}
void PutBlankInTable (int numberOfBlanks)
{
// Fill safearray with some values...
// Create entry value for (i,j)
for (int i = 0; i < numberOfBlanks; i++)
{
TwoDimensionTableColumnCount++;
if (TwoDimensionTableColumnCount > TwoDimensionTableNumberOfColumns)
{
TwoDimensionTableRowCount++;
TwoDimensionTableColumnCount = 1;
}
VARIANT tmp;
VariantInit ( & tmp);
tmp.vt = VT_EMPTY;
// Add to safearray...
long indices [] = {TwoDimensionTableRowCount, TwoDimensionTableColumnCount};
SafeArrayPutElement (TwoDimensionArray.parray, indices, (void *) & tmp);
}
}
void SendTableToServer (int row1, int column1, int row2, int column2)
{
// send the array to excel
// note that InitTable probably did not get the total number of rows, only
// the total number of columns and it sets the number of rows to 2000
// so, we may be exploiting a bug in either SafeArray or in Excel and just
// selecting the actual number of rows in the range allows us to ignore
// the fact that the safearray was created with way more rows than we are
// probably using
std::string cellAddress = GetCellEquivalent (row1, column1) + ":" + GetCellEquivalent (row2, column2);
VARIANT result1;
VariantInit ( & result1);
VARIANT range;
VariantInit ( & range);
range.vt = VT_BSTR;
_bstr_t address = _bstr_t (cellAddress.c_str ());
range.bstrVal = address;
std::string errorMsg = "Select a range in the active spreadsheet ," + cellAddress + " (SendTableToServer)";
OLEMethod (DISPATCH_PROPERTYGET, & result1, pExcelSheet, L"Range", errorMsg, 1, range);
if (result1.vt == VT_DISPATCH)
{
VARIANT result2;
VariantInit ( & result2);
std::string errorMsg = "Send the table to selected range, " + cellAddress + " (SendTableToServer)";
OLEMethod (DISPATCH_PROPERTYPUT, & result2, result1.pdispVal, L"Value", errorMsg, 1, TwoDimensionArray);
int res = result2.vt;
result1.pdispVal -> Release ();
}
#ifdef CLEAR_VARIANTS
VariantClear ( & TwoDimensionArray);
VariantClear ( & range);
#endif
}
double AsDouble (const char * str)
{
if (str)
return atof (str);
else
return 0.0;
}
double AsDouble (std::string string)
{
if (string.size () > 0)
return atof (string.c_str ());
else
return 0.0;
}
void PutRowInTable (std::string title, std::string number, std::string units)
{
PutStringInTable (title);
if (units.size () > 0)
PutStringInTable (SetUnitsCase (units));
else
PutBlankInTable (1);
double temp = AsDouble (number);
PutDoubleInTable (1, & temp);
}
void PutRowInTable (std::string title, std::string number, std::string units, int offset)
{
InitTable (1, 3);
PutStringInTable (title);
if (units.size () > 0)
PutStringInTable (SetUnitsCase (units));
else
PutBlankInTable (1);
double temp = AsDouble (number);
PutDoubleInTable (temp);
SendTableToServer (offset, 1, offset, 3);
}
void PutRow4InTable (std::string title, std::string units, std::string num1, std::string num2,
std::string num3, std::string num4, int offset)
{
InitTable (1, 6);
PutStringInTable (title);
if (units.size () > 0)
PutStringInTable (SetUnitsCase (units));
else
PutBlankInTable (1);
double temp [4] = {uninitializedValue, uninitializedValue,
uninitializedValue, uninitializedValue};
if (num1.size () > 0)
temp [0] = AsDouble (num1);
if (num2.size () > 0)
temp [1] = AsDouble (num2);
if (num3.size () > 0)
temp [2] = AsDouble (num3);
if (num4.size () > 0)
temp [3] = AsDouble (num4);
PutDoubleInTable (4, temp);
SendTableToServer (offset, 1, offset, 6);
}