//---------------------------------------------------------------------------
// Excel Multivalue Formula Add-In
// Copyright (C) <2005> <Herbert Danler>
// Contact: danler@users.sourceforge.net
// Project Home Page: http://excelmvf.sourceforge.net/
//
// This program is free software; you can redistribute it and/or modify it under
// the terms of the GNU General Public License as published by the Free Software
// Foundation; either version 2 of the License, or (at your option) any
// later version.
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
// or FITNESS FOR A PARTICULAR PURPOSE.
// See the GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License along with
// this program; if not, write to the Free Software Foundation, Inc.,
// 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
//---------------------------------------------------------------------------
#include <vcl.h>
#include <windows.h>
#include <string>
#include "excelmvf.h"
#include "TRequestController.h"
#include "TExcelEventDispatcher.h"
#include "TSgADOConnection.h"
#include "TRangeSize.h"
#pragma hdrstop
#pragma argsused
//global constants
const char* const DBFILENAME="excelmvf.mdb";
// global variables
TRequestController requestController;
TExcelEventDispatcher ExcelEventDispatcher;
int WINAPI DllEntryPoint(HINSTANCE hinst, unsigned long reason, void* lpReserved)
{
switch(reason){
case DLL_PROCESS_ATTACH:{
// We don't need thread notifications for what we're doing. Thus, get
// rid of them, thereby eliminating some of the overhead of this DLL
DisableThreadLibraryCalls( hinst );
//this add-in assumes that the DB-File is in the same directory as the dll
//build connection string for ADO-datasource
char dllfilename[_MAX_DRIVE + _MAX_DIR +_MAX_FNAME +_MAX_EXT], drive[_MAX_DRIVE],dir[_MAX_DIR];
char mbsdatasoure[_MAX_DRIVE + _MAX_DIR +_MAX_FNAME +_MAX_EXT];
wchar_t wcsdatasource[_MAX_DRIVE + _MAX_DIR +_MAX_FNAME +_MAX_EXT];
std::wstring connectionString =L"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
GetModuleFileName(hinst,dllfilename,_MAX_DRIVE + _MAX_DIR +_MAX_FNAME +_MAX_EXT);
_splitpath(dllfilename,drive,dir,NULL,NULL);
_makepath(mbsdatasoure,drive,dir,DBFILENAME,NULL);
mbstowcs(wcsdatasource, mbsdatasoure,strlen(mbsdatasoure)+1);
connectionString.append(wcsdatasource);
connectionString.append(L";Persist Security Info=False");
//set the connection string for the datasource
TSgADOConnection::setConnectionString(connectionString.c_str());
//for hard coded datasource all building stuff above can be eliminated
//and the following used instead
//TSgADOConnection::setConnectionString(L"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""D:\\develop\\Experimente\\Artikel Exel Multivalue Formula\\orderManagement.mdb"";Persist Security Info=False");
//the following Connection string would fit for a OLE-ODBC-Bridge
//TSgADOConnection::setConnectionString(L"Provider=MSDASQL.1;Password=excelmvf;Persist Security Info=True;User ID=excelmvf;Data Source=excelmvf");
}
break;
case DLL_PROCESS_DETACH:
break;
default:
NULL;
}
return 1;
}
extern "C" {
void __export __stdcall setExcelApp(VARIANT pExcelApp){
ExcelEventDispatcher.Connect(pExcelApp.pdispVal);
}
VARIANT __export __stdcall getOrderItems_dll(TDateTime pstartdate, TDateTime penddate, char* pfieldlist, char* psortcriterias, bool pheader, VARIANT pcell)
{
bool rangeSizechanged=false;
VARIANT retval,result;
VARIANT callingCellAddress,callingCellWorksheet,callingCellFormula,callingCellRow,callingCellCol;
VARIANT excelColRange,excelrangeNumCols, excelRowRange, excelrangeNumRows;
HRESULT hr;
HWND funcparamWindow = 0;
//the function argument window is of class bosa_sdm_XL9. If a window of this class is found the user enters
//arguments in this window. In this case we always return #NA
funcparamWindow = FindWindow("bosa_sdm_XL9",NULL);
if (funcparamWindow){
retval.vt = VT_ERROR;
retval.scode=excelmvf::VALNOTAVAILABLE;
return retval;
}
//read values we need to know for storage in the FuncParam variable to later access this information
//in the ExcelEventHandler
TComHelper::AutoWrap(DISPATCH_PROPERTYGET,&callingCellAddress,pcell.pdispVal,L"Address",0);
TComHelper::AutoWrap(DISPATCH_PROPERTYGET,&callingCellFormula,pcell.pdispVal,L"Formula",0);
TComHelper::AutoWrap(DISPATCH_PROPERTYGET,&callingCellCol,pcell.pdispVal,L"Column",0);
TComHelper::AutoWrap(DISPATCH_PROPERTYGET,&callingCellRow,pcell.pdispVal,L"Row",0);
TComHelper::AutoWrap(DISPATCH_PROPERTYGET,&callingCellWorksheet,pcell.pdispVal,L"Worksheet",0);
//query current range and determine number of cols and rows
//if pcell is part of an array formula, Count delivers size of array
TComHelper::AutoWrap(DISPATCH_PROPERTYGET,&excelColRange,pcell.pdispVal,L"Columns",0);
TComHelper::AutoWrap(DISPATCH_PROPERTYGET,&excelrangeNumCols,excelColRange.pdispVal,L"Count",0);
TComHelper::AutoWrap(DISPATCH_PROPERTYGET,&excelRowRange,pcell.pdispVal,L"Rows",0);
TComHelper::AutoWrap(DISPATCH_PROPERTYGET,&excelrangeNumRows,excelRowRange.pdispVal,L"Count",0);
//fetch data
const TOrderItemDataset* dataset = requestController.getOrderItems(pstartdate,penddate,std::string(psortcriterias ? psortcriterias :""));
//if the formula is returned in an Array of Variants (MultiArrayFormula) then read the formula from the first element of the array
if (callingCellFormula.vt == (VT_ARRAY | VT_VARIANT)){
//it's an 2-dimensional array of Variants (dim1=rows, dim2 cols)
//Lbounds of Dimensions start with 1
long indices[] = {1,1};
hr = SafeArrayGetElement(callingCellFormula.parray,indices ,&callingCellFormula);
}
//compare the size of the range with the required size
if (dataset!=NULL && !dataset->isempty() && dataset->isvalid() ){
TRangeSize neededRangeSize = dataset->getRangeSize(std::string(pfieldlist ? pfieldlist:""),pheader);
if(neededRangeSize.rows != excelrangeNumRows.lVal || neededRangeSize.cols != excelrangeNumCols.lVal)
rangeSizechanged=true;
}
else
rangeSizechanged = false;
//if the function call was not invoked by the ExcelEventDispatchers calculate-event
//and we are working on the active cell, store all function parameters for later
//processing in ExcelEventDispatchers calculate event
if (TRequestController::funcParams.getCalculating()==false && rangeSizechanged == true){
//store parameters for later processing in the Calculate Event
TRequestController::funcParams.setFuncParams(pstartdate,
penddate,
std::string(pfieldlist ? pfieldlist : ""),
std::string(psortcriterias ? psortcriterias :""),
pheader,
excelmvf::getOrderItems,
callingCellFormula,
callingCellAddress,
callingCellCol,
callingCellRow,
excelrangeNumCols,
excelrangeNumRows,
callingCellWorksheet
);
TRequestController::funcParams.setState(true);
//the correct values are retrieved by a second function call triggered by the Calculate Event
//in the meantime we return N/A
retval.vt = VT_ERROR;
retval.scode=excelmvf::VALNOTAVAILABLE;
return retval;
}
//the function was either called by automatic calculation or after rewriting the formula in the Calculate-Event
//which triggers a further function call. Return the results in an Array
TRequestController::funcParams.setCalculating(false);
if (dataset==NULL || dataset->isempty() || (dataset->isvalid() == false)){
retval.vt=VT_ERROR;
retval.scode=excelmvf::VALNOTAVAILABLE;
return retval;
}
retval = dataset->getResultArray(std::string(pfieldlist ? pfieldlist : ""),
pheader
);
return retval;
}
////////////////////////////////////////////////////////////////////////
// Name: getCustomers_dll()
// Purpose: Implementation of getCustomers_dll()
// Return: VARIANT
////////////////////////////////////////////////////////////////////////
VARIANT __export __stdcall getCustomers_dll( char* pfieldlist, char* psortcriterias, bool pheader, VARIANT pcell)
{
bool rangeSizechanged=false;
VARIANT retval,result;
VARIANT callingCellAddress,callingCellWorksheet,callingCellFormula,callingCellRow,callingCellCol;
VARIANT excelColRange,excelrangeNumCols, excelRowRange, excelrangeNumRows;
HRESULT hr;
HWND funcparamWindow = 0;
//the function argument window is of class bosa_sdm_XL9. If a window of this class is found the user enters
//arguments in this window. In this case we always return #NA
funcparamWindow = FindWindow("bosa_sdm_XL9",NULL);
if (funcparamWindow){
retval.vt = VT_ERROR;
retval.scode=excelmvf::VALNOTAVAILABLE;
return retval;
}
//read values we need to know for storage in the FuncParam variable to later access this information
//in the ExcelEventHandler
TComHelper::AutoWrap(DISPATCH_PROPERTYGET,&callingCellAddress,pcell.pdispVal,L"Address",0);
TComHelper::AutoWrap(DISPATCH_PROPERTYGET,&callingCellFormula,pcell.pdispVal,L"Formula",0);
TComHelper::AutoWrap(DISPATCH_PROPERTYGET,&callingCellCol,pcell.pdispVal,L"Column",0);
TComHelper::AutoWrap(DISPATCH_PROPERTYGET,&callingCellRow,pcell.pdispVal,L"Row",0);
TComHelper::AutoWrap(DISPATCH_PROPERTYGET,&callingCellWorksheet,pcell.pdispVal,L"Worksheet",0);
//query current range and determine number of cols and rows
//if pcell is part of an array formula, Count delivers size of array
TComHelper::AutoWrap(DISPATCH_PROPERTYGET,&excelColRange,pcell.pdispVal,L"Columns",0);
TComHelper::AutoWrap(DISPATCH_PROPERTYGET,&excelrangeNumCols,excelColRange.pdispVal,L"Count",0);
TComHelper::AutoWrap(DISPATCH_PROPERTYGET,&excelRowRange,pcell.pdispVal,L"Rows",0);
TComHelper::AutoWrap(DISPATCH_PROPERTYGET,&excelrangeNumRows,excelRowRange.pdispVal,L"Count",0);
//fetch data
const TCustomerDataset* dataset = requestController.getCustomers(std::string(psortcriterias ? psortcriterias :""));
//if the formula is returned in an Array of Variants (MultiArrayFormula) then read the formula from the first element of the array
if (callingCellFormula.vt == (VT_ARRAY | VT_VARIANT)){
//it's an 2-dimensional array of Variants (dim1=rows, dim2 cols)
//Lbounds of Dimensions start with 1
long indices[] = {1,1};
hr = SafeArrayGetElement(callingCellFormula.parray,indices ,&callingCellFormula);
}
//compare the size of the range with the required size
if (dataset!=NULL && !dataset->isempty() && dataset->isvalid() ){
TRangeSize neededRangeSize = dataset->getRangeSize(std::string(pfieldlist ? pfieldlist:""),pheader);
if(neededRangeSize.rows != excelrangeNumRows.lVal || neededRangeSize.cols != excelrangeNumCols.lVal)
rangeSizechanged=true;
}
else
rangeSizechanged = false;
//if the function call was not invoked by the ExcelEventDispatchers calculate-event
//and we are working on the active cell, store all function parameters for later
//processing in ExcelEventDispatchers calculate event
if (TRequestController::funcParams.getCalculating()==false && rangeSizechanged == true){
//store parameters for later processing in the Calculate Event
TRequestController::funcParams.setFuncParams(NULL,
NULL,
std::string(pfieldlist ? pfieldlist : ""),
std::string(psortcriterias ? psortcriterias :""),
pheader,
excelmvf::getCustomers,
callingCellFormula,
callingCellAddress,
callingCellCol,
callingCellRow,
excelrangeNumCols,
excelrangeNumRows,
callingCellWorksheet
);
TRequestController::funcParams.setState(true);
//the correct values are retrieved by a second function call triggered by the Calculate Event
//in the meantime we return N/A
retval.vt = VT_ERROR;
retval.scode=excelmvf::VALNOTAVAILABLE;
return retval;
}
//the function was either called by automatic calculation or after rewriting the formula in the Calculate-Event
//which triggers a further function call. Return the results in an Array
TRequestController::funcParams.setCalculating(false);
if (dataset==NULL || dataset->isempty() || (dataset->isvalid() == false)){
retval.vt=VT_ERROR;
retval.scode=excelmvf::VALNOTAVAILABLE;
return retval;
}
retval = dataset->getResultArray(std::string(pfieldlist ? pfieldlist : ""),
pheader
);
return retval;
}
} //extern "C"
//---------------------------------------------------------------------------
Submit an article or tip