Herbert Danler

In this document the extension of the excelmfv (Excel Multivalue formula) add in with a further function to retrieve data from the article table in the excelmvf.mdb database is described in detail. All header and module files that were adapted are listed here as a whole. The adapted parts are marked with bold letters.

· Step 1: derive new class TArticleRecord:

<<TArticleRecord.h>>

/***********************************************************************

* Module: TArticleRecord.h

* Author: Herbert Danler

* Created:

* Modified:

* Purpose: Declaration of the class TArticleRecord

***********************************************************************/

#if !defined(TArticleRecord_h)

#define TArticleRecord_h

class TArticleDataset;

class TFieldDescriptor;

#include <windows.h>

#include <vcl.h>

#include <string>

#include "excelmvf.h"

#include "TFieldData.h"

#include "TRecord.h"

class TArticleRecord:public excelmvf::TRecord

{

public:

static TFieldDescriptor fieldDescriptor[];

static std::string defaultFieldList;

/* returns a TFieldDescriptor struct of field i. Fields are numbered in a sequence

* purpose: to simplify implementation of < operator (which in turn is needed for sorting) */

Variant operator[](int pfieldnumber) const;

static int getfieldDescriptorArraySize();

protected:

private:

Variant articleID;

Variant description;

Variant price;

friend class TArticleDataset;

};

#endif

<<TArticleRecord.h>>

<<TArticleRecord.cpp>>

/***********************************************************************

* Module: TArticleRecord.cpp

* Author: Herbert Danler

* Modified:

* Purpose: Implementation of the class TArticleRecord

***********************************************************************/

#include "TArticleDataset.h"

#include "TArticleRecord.h"

TFieldDescriptor TArticleRecord::fieldDescriptor[]={

{"1","ARTICLEID",1},{"ARTICLEID","ARTICLEID",1},{"ID","ARTICLEID",1},

{"2","DESCRIPTION",2},{"DESCRIPTION","DESCRIPTION",2},{"BESCHREIBUNG","DESCRIPTION",2},

{"3","PRICE",3},{"PRICE","PRICE",3},{"PREIS","PRICE",3}

};

/* default field list is displayed when user enters no or invalid field list

* Extend whenever new fields are added! */

std::string TArticleRecord::defaultFieldList="articleID,description,price";

int TArticleRecord::getfieldDescriptorArraySize(){

return sizeof(fieldDescriptor);

}

////////////////////////////////////////////////////////////////////////

// Name: TArticleRecord::operator[](int pfieldnumber)

// Purpose: Implementation of TArticleRecord::operator[]()

// Comment: returns field i. Fields are numbered in a sequence

// purpose:

// Parameters:

// - pfieldnumber

// Return: Variant

////////////////////////////////////////////////////////////////////////

Variant TArticleRecord::operator[](int pfieldnumber) const

{

switch (pfieldnumber){

case 1:

return articleID;

case 2:

return description;

case 3:

return price;

default:

return Variant(0);

}

}

<<TArticleRecord.cpp>>

· Step 2: derive new class TArticleDataset:

<<TArticleDataset.h>>

/***********************************************************************

* Module: TArticleDataset.h

* Author: Herbert Danler

* Created:

* Modified:

* Purpose: Declaration of the class TArticleDataset

***********************************************************************/

#if !defined(TArticleDataset_h)

#define TArticleDataset_h

class TArticleRecord;

#include "TDataset.h"

#include "TRangeSize.h"

#include <vector>

#include <string>

#include <algorithm>

#include <values.h> //for MAXINT

#include <string_util.h>

#include "stringhelper.h"

class TArticleDataset : public excelmvf::TDataset<TArticleRecord>

{

public:

int retrieve(void);

TArticleDataset(std::string psortcriterias);

~TArticleDataset();

bool operator<(const excelmvf::TDataset<TArticleRecord>& pmvrecordset) const;

private:

static std::string SQL;

};

#endif

<<TArticleDataset.h >>

<<TArticleDataset.cpp>>

/***********************************************************************

* Module: TArticleDataset.cpp

* Author: Herbert Danler

* Modified:

* Purpose: Implementation of the class TArticleDataset

***********************************************************************/

#include "TArticleDataset.h"

#include "TArticleRecord.h"

#include "TSgADOQuery.h"

std::string TArticleDataset::SQL="Select * from Articles";

////////////////////////////////////////////////////////////////////////

// Name: TArticleDataset::retrieve()

// Purpose: Implementation of TArticleDataset::retrieve()

// Return: int

////////////////////////////////////////////////////////////////////////

int TArticleDataset::retrieve(void)

{

TADOQuery* query = TSgADOQuery::getInstance();

query->SQL->Clear();

query->Parameters->Clear();

query->SQL->Add(SQL.c_str());

query->Prepared;

query->Open();

query->Sort = verifySortingCriterias(sortCriterias);

//replace all ocurrences of i with an ordinal number

int recordcounter=0;

setTimestamp(); //unvalid values are stored in the container too, so timestamp has to be set independent from success of fetch

while (!query->Eof){

TArticleRecord record;

record.articleID= query->Fields->Fields[0]->AsVariant;

record.description = query->Fields->Fields[1]->AsVariant;

record.price = query->Fields->Fields[2]->AsVariant;

recordContainer.push_back(record);

recordcounter++;

query->Next();

}

setTimestamp();

setvalid();

return recordcounter;

}

////////////////////////////////////////////////////////////////////////

// Name: TArticleDataset::TArticleDataset()

// Purpose: Implementation of TArticleDataset::TArticleDataset()

// Return:

////////////////////////////////////////////////////////////////////////

TArticleDataset::TArticleDataset(std::string psortcriterias)

{

sortCriterias = psortcriterias;

}

////////////////////////////////////////////////////////////////////////

// Name: TArticleDataset::~TArticleDataset()

// Purpose: Implementation of TArticleDataset::~TArticleDataset()

// Return:

////////////////////////////////////////////////////////////////////////

TArticleDataset::~TArticleDataset()

{

// TODO : implement

}

////////////////////////////////////////////////////////////////////////

// Name: TArticleDataset::operator<(const excelmvf::TDataset& pmvrecordset)

// Purpose: Implementation of TArticleDataset::operator<()

// Parameters:

// - pmvrecordset

// Return: bool

////////////////////////////////////////////////////////////////////////

bool TArticleDataset::operator<(const excelmvf::TDataset<TArticleRecord>& pmvrecordset) const

{

const TArticleDataset* tempObject;

if (tempObject=dynamic_cast<const TArticleDataset*> (&pmvrecordset)){

if (sortCriterias < tempObject->sortCriterias)

return true;

}

return false;

}

<<TArticleDataset. >>

· Step 3 (if required): Adoption.

· Schritt 4: Anpassung von TfuncParams: nicht erforderlich, weil keine neuen Abfrageparameter definiert werden

· Schritt 5: Anpassung von TRequestController:

Neuen Typedef in Klasse Container.h einfügen:

<<Container.h>>

/***********************************************************************

* Module: Container.h

* Author: Herbert Danler

* Modified:

* Comment: Module contains Declaration of STL-containers for intermediate storage of data

***********************************************************************/

#if !defined(__Container_h)

#define __Container_h

#include <set>

#include "TOrderItemDataset.h"

#include "TCustomerDataset.h"

#include "TArticleDataset.h"

#include "TFuncParams.h"

using namespace std;

typedef set<TOrderItemDataset> TOrderItemDatasetContainer;

typedef set<TCustomerDataset> TCustomerDatasetContainer;

typedef set<TArticleDataset> TArticleDatasetContainer;

#endif

<<Container.h Ende>>

#include für neue Header-Files TArticleDataset und TArticleRecord einfügen, neue Methode getArticles einfügen, neuen Container articleDatasetContainer einfügen:

<<TRequestController.h>>

/***********************************************************************

* Module: TRequestController.h

* Author: Herbert Danler

* Created:

* Modified:

* Purpose: Declaration of the class TRequestController

***********************************************************************/

#if !defined(TRequestController_h)

#define TRequestController_h

#include "TOrderItemDataset.h"

#include "TCustomerDataset.h"

#include "TArticleDataset.h"

#include "TOrderItemRecord.h"

#include "TCustomerRecord.h"

#include "TArticleRecord.h"

#include "Container.h"

class TRequestController

{

public:

const TOrderItemDataset* getOrderItems(Variant pstartdate, Variant penddate, std::string psortcriterias) const;

const TCustomerDataset* getCustomers(std::string psortcriterias) const;

const TArticleDataset* getArticles(std::string psortcriterias) const;

static TFuncParams funcParams;

protected:

private:

TOrderItemDatasetContainer orderItemDatasetContainer;

TCustomerDatasetContainer customerDatasetContainer;

TArticleDatasetContainer articleDatasetContainer;

static int cachingperiod;

};

#endif

<<TRequestController.h Ende>>

<<TRequestController.cpp>>

/***********************************************************************

* Module: TRequestController.cpp

* Author: Herbert Danler

* Modified:

* Purpose: Implementation of the class TRequestController

***********************************************************************/

#include "TRequestController.h"

int TRequestController::cachingperiod=10;

TFuncParams TRequestController::funcParams;

////////////////////////////////////////////////////////////////////////

// Name: TRequestController::getOrderItems(Variant pstartdate, Variant penddate, std::string psortcriterias)

// Purpose: Implementation of TRequestController::getOrderItems()

// Parameters:

// - pstartdate

// - penddate

// - psortcriterias

// Return: const TOrderItemDataset*

////////////////////////////////////////////////////////////////////////

const TOrderItemDataset* TRequestController::getOrderItems(Variant pstartdate, Variant penddate, std::string psortcriterias) const

{

//look in the container if data is already locally stored

TOrderItemDataset temp(pstartdate, penddate,psortcriterias);

pair<set<TOrderItemDataset>::iterator,bool> pos;

pos.first = orderItemDatasetContainer.find(temp);

if (pos.first==orderItemDatasetContainer.end()) {

temp.retrieve();

pos = orderItemDatasetContainer.insert(temp);

}

else{

//check if data is out of date

if ((*pos.first).getTimestamp()<(Now()-(double(cachingperiod)/86400))){

orderItemDatasetContainer.erase(pos.first);

temp.retrieve();

pos.first = orderItemDatasetContainer.insert(temp).first;

}

}

return &(*pos.first);

}

////////////////////////////////////////////////////////////////////////

// Name: TRequestController::getCustomers()

// Purpose: Implementation of TRequestController::getCustomers()

// Return: const TCustomerDataset*

////////////////////////////////////////////////////////////////////////

const TCustomerDataset* TRequestController::getCustomers(std::string psortcriterias) const

{

//look in the container if data is already locally stored

TCustomerDataset temp(psortcriterias);

pair<set<TCustomerDataset>::iterator,bool> pos;

pos.first = customerDatasetContainer.find(temp);

if (pos.first==customerDatasetContainer.end()) {

temp.retrieve();

pos = customerDatasetContainer.insert(temp);

}

else{

//check if data is out of date

if ((*pos.first).getTimestamp()<(Now()-(double(cachingperiod)/86400))){

customerDatasetContainer.erase(pos.first);

temp.retrieve();

pos.first = customerDatasetContainer.insert(temp).first;

}

}

return &(*pos.first);

}

////////////////////////////////////////////////////////////////////////

// Name: TRequestController::getArticles()

// Purpose: Implementation of TRequestController::getArticles()

// Return: const TArticleDataset*

////////////////////////////////////////////////////////////////////////

const TArticleDataset* TRequestController::getArticles(std::string psortcriterias) const

{

//look in the container if data is already locally stored

TArticleDataset temp(psortcriterias);

pair<set<TArticleDataset>::iterator,bool> pos;

pos.first = articleDatasetContainer.find(temp);

if (pos.first==articleDatasetContainer.end()) {

temp.retrieve();

pos = articleDatasetContainer.insert(temp);

}

else{

//check if data is out of date

if ((*pos.first).getTimestamp()<(Now()-(double(cachingperiod)/86400))){

articleDatasetContainer.erase(pos.first);

temp.retrieve();

pos.first = articleDatasetContainer.insert(temp).first;

}

}

return &(*pos.first);

}

<<TRequestController.cpp Ende>>

Schritt 6: Erweiterung von TExcelEventDispatcher

Einfügen der neuen Funktion in enum (excelmvf.h)

<<ExcelmvF.h>>

/***********************************************************************

* Module: ExcelmvF.h

* Author: Herbert Danler

* Modified: Donnerstag, 28. Oktober 2004 17:03:26

* Comment: contains constants, enums etc. specific for ExcelmultiValueFormulas project

***********************************************************************/

#if !defined(__ExcelmvF_h)

#define __ExcelmvF_h

#include <limits.h>

#include <string>

//Namespace ExcelmvF (for ExcelMultivalueFormulas) contains all global variables

//used throughout the ExcelMultivalueFormulas projects

namespace excelmvf{

const std::string appName = "Excel MultiValue Formulas Add-In";

//value to be set for VARIANT.scode member to get #N/A (not available) #NV (nicht verfügbar)

const long VALNOTAVAILABLE=-2146826246;

const enum {ASC=0,DESC};

const int MAXSORTCRITERIAS = 3;

//constant for NoSorting

const int NOSORTING = -1;

const enum {getOrderItems=0,getCustomers=1,getArticles=2};

//error strings

const std::string WriteError = "Die Ergebnisse der Formel würden einen geschützten Bereich überschreiben!\nSchaffen Sie genügend Platz und wiederholen Sie die Operation!";

} //namespace end

#endif

<<ExcelmvF.h Ende>>

Erweiterung der Calculate –Methode

<<TexcelEventDispatcher.cpp>>

/***********************************************************************

* Module: TExcelEventDispatcher.cpp

* Author: Herbert Danler

* Modified: Donnerstag, 12. August 2004 10:31:30

* Purpose: Implementation of the class TExcelEventDispatcher

* Comment: Class to "catch" Excels Calculate-Event

***********************************************************************/

#include "TCOMException.h"

#include "TExcelEventDispatcher.h"

IDispatch * TExcelEventDispatcher::pExcelAppIDispatch;

bool TExcelEventDispatcher::deleting;

////////////////////////////////////////////////////////////////////////

// Name: TExcelEventDispatcher::TExcelEventDispatcher()

// Purpose: Implementation of TExcelEventDispatcher::TExcelEventDispatcher()

// Return:

////////////////////////////////////////////////////////////////////////

TExcelEventDispatcher::TExcelEventDispatcher()

{

connected=false;

deleting=false;

}

////////////////////////////////////////////////////////////////////////

// Name: TExcelEventDispatcher::~TExcelEventDispatcher()

// Purpose: Implementation of TExcelEventDispatcher::~TExcelEventDispatcher()

// Return:

////////////////////////////////////////////////////////////////////////

TExcelEventDispatcher::~TExcelEventDispatcher()

{

if (connected)

Disconnect();

}

////////////////////////////////////////////////////////////////////////

// Name: TExcelEventDispatcher::Connect(IDispatch * srv)

// Purpose: Implementation of TExcelEventDispatcher::Connect()

// Parameters:

// - srv

// Return: void

////////////////////////////////////////////////////////////////////////

void TExcelEventDispatcher::Connect(IDispatch * srv)

{

HRESULT hr=0;

pExcelAppIDispatch=srv;

IConnectionPoint *pConnectionPoint;

hr = pExcelAppIDispatch->QueryInterface(

IID_IConnectionPointContainer,

(void **)&pConnPtContainer

);

if(SUCCEEDED(hr)){

hr = pConnPtContainer->FindConnectionPoint(IID_IExcel10AppEvents,&pConnectionPoint);

}

if(SUCCEEDED(hr)){

pConnPtContainer->AddRef();

hr=ConnectEvents(pConnPtContainer);

connected=true;

}

}

////////////////////////////////////////////////////////////////////////

// Name: TExcelEventDispatcher::Disconnect()

// Purpose: Implementation of TExcelEventDispatcher::Disconnect()

// Return: void

////////////////////////////////////////////////////////////////////////

void TExcelEventDispatcher::Disconnect(void)

{

DisconnectEvents(pConnPtContainer);

pConnPtContainer->Release();

}

////////////////////////////////////////////////////////////////////////

// Name: TExcelEventDispatcher::Calculate(TVariant* params)

// Purpose: Implementation of TExcelEventDispatcher::Calculate()

// Comment: Event routine called every time the Excel Calculate Event is triggered.

// Parameters:

// - params

// Return: void

////////////////////////////////////////////////////////////////////////

void __fastcall TExcelEventDispatcher::Calculate(TVariant* params)

{

VARIANT result, retval;

HRESULT hresult;

if(!deleting){

if (TRequestController::funcParams.getState()){

try{

VARIANT worksheet = TRequestController::funcParams.getWorksheet();

VARIANT address = TRequestController::funcParams.getAddress();

VARIANT formula = TRequestController::funcParams.getFormula();

const excelmvf::TDatasetBase* dataset;

switch ( TRequestController::funcParams.getFormulaID()){

case excelmvf::getOrderItems:{

dataset = dynamic_cast<const TOrderItemDataset*>( requestController.getOrderItems(TRequestController::funcParams.getStartdate(),TRequestController::funcParams.getEnddate(),TRequestController::funcParams.getSortparams()));

break;

}

case excelmvf::getCustomers:

dataset = dynamic_cast<const TCustomerDataset*> (requestController.getCustomers(TRequestController::funcParams.getSortparams()));

break;

case excelmvf::getArticles:

dataset = dynamic_cast<const TArticleDataset*> (requestController.getArticles(TRequestController::funcParams.getSortparams()));

break;

}

// Data available

if (!(dataset==NULL || dataset->isempty() || (dataset->isvalid() == false))){

VARIANT formulaLocation, neededcols, neededrows;

VARIANT excelColRange,excelRowRange,excelrangeNumCols,excelrangeNumRows;

VARIANT emptyVariant;

emptyVariant.vt=VT_EMPTY;

VARIANT EnableCalculation;

EnableCalculation.vt=VT_BOOL;

//get range represented by column and row value

//ATTENTION: parameters must be passed to Autowrap in reverse order!

hresult = TComHelper::AutoWrap(DISPATCH_PROPERTYGET,&formulaLocation,worksheet.pdispVal,L"Cells",2,TRequestController::funcParams.getCol(),TRequestController::funcParams.getRow());

//get required number of rows and colums

TRangeSize neededRangeSize = dataset->getRangeSize(TRequestController::funcParams.getFieldlist(),TRequestController::funcParams.getHeader());

//if the current range has exactly one column and one row, it is a new user input

if (TRequestController::funcParams.getNumCols().lVal == 1 && TRequestController::funcParams.getNumRows().lVal == 1){

neededrows.vt = VT_I4;

neededcols.vt = VT_I4;

neededrows.lVal = neededRangeSize.rows;

neededcols.lVal = neededRangeSize.cols;

//extend range to needed size (attention: parameters in reverse order -> limitation of AutoWrap

hresult = TComHelper::AutoWrap(DISPATCH_PROPERTYGET,&formulaLocation,formulaLocation.pdispVal,L"Resize",2,neededcols,neededrows);

TRequestController::funcParams.setCalculating(false);

TRequestController::funcParams.setState(false);

//write Formula as Array

hresult = TComHelper::AutoWrap(DISPATCH_PROPERTYPUT,&result,formulaLocation.pdispVal,L"FormulaArray",1,formula);

}

//if it is a multicolum range then the request originates from an array formula with changed parameters

//delete Formulaarray and rewrite formula to restart the retrieval process

else{

//create a range with the first cell

hresult = TComHelper::AutoWrap(DISPATCH_PROPERTYGET,&formulaLocation,worksheet.pdispVal,L"Cells",2,TRequestController::funcParams.getCol(),TRequestController::funcParams.getRow());

//resize the range to the current size for deletion

hresult = TComHelper::AutoWrap(DISPATCH_PROPERTYGET,&formulaLocation,formulaLocation.pdispVal,L"Resize",2,TRequestController::funcParams.getNumCols(),TRequestController::funcParams.getNumRows());

//delete the current formula array

//set static variable deleting to true to avoid reentering this code due to automatic Excel recalculation after deleting the range

deleting=true;

hresult = TComHelper::AutoWrap(DISPATCH_PROPERTYPUT,NULL,formulaLocation.pdispVal,L"Value",1,emptyVariant);

deleting=false;

//create a range with the first cell

hresult = TComHelper::AutoWrap(DISPATCH_PROPERTYGET,&formulaLocation,worksheet.pdispVal,L"Cells",2,TRequestController::funcParams.getCol(),TRequestController::funcParams.getRow());

TRequestController::funcParams.setCalculating(false);

TRequestController::funcParams.setState(false);

//rewrite formula to restart the whole retrieval process

hresult = TComHelper::AutoWrap(DISPATCH_PROPERTYPUT,&result,formulaLocation.pdispVal,L"Formula",1,formula);

//select the first cell (otherwise the range from the former delete operation would persist)

hresult = TComHelper::AutoWrap(DISPATCH_METHOD,NULL,formulaLocation.pdispVal,L"Select",0);

}

}

}

catch(const TCOMException& e){

VARIANT excelMainWindowHWND;

hresult = TComHelper::AutoWrap(DISPATCH_PROPERTYGET,&excelMainWindowHWND,pExcelAppIDispatch,L"HWND",0);

int errcode = HRESULT_CODE(e.getErrCode());

if (errcode==1004) //this error occurs on writing into an protected area (e.g. into an area with another formula array)

MessageBox(excelMainWindowHWND.plVal,excelmvf::WriteError.c_str(),excelmvf::appName.c_str(),MB_ICONEXCLAMATION);

else

MessageBox(excelMainWindowHWND.plVal,e.getErrMsg().c_str(),excelmvf::appName.c_str(),MB_ICONEXCLAMATION);

TRequestController::funcParams.setCalculating(false);

}

}

}

}

////////////////////////////////////////////////////////////////////////

// Name: TExcelEventDispatcher::InvokeEvent(DISPID id, TVariant* params)

// Purpose: Implementation of TExcelEventDispatcher::InvokeEvent()

// Parameters:

// - id

// - params

// Return: HRESULT

////////////////////////////////////////////////////////////////////////

HRESULT TExcelEventDispatcher::InvokeEvent(DISPID id, TVariant* params)

{

switch(id)

{

case 0x61b: //Excel Calculate Event

Calculate(params);

break;

default:

;//MessageBox(0,"we shouldn't be here!","Oh noo!!",0);

}

}

////////////////////////////////////////////////////////////////////////

// Name: TExcelEventDispatcher::getPExcelAppIDispatch()

// Purpose: Implementation of TExcelEventDispatcher::getPExcelAppIDispatch()

// Return: IDispatch *

////////////////////////////////////////////////////////////////////////

IDispatch * TExcelEventDispatcher::getPExcelAppIDispatch(void) const

{

return pExcelAppIDispatch;

}

Schritt 7: neue Export-Funktion getArticles einfügen

<<ordermgt.cpp>>

//---------------------------------------------------------------------------

#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

//---------------------------------------------------------------------------

// Wichtiger Hinweis zur DLL-Speicherverwaltung, falls die DLL die statische

// Version der Laufzeitbibliothek (RTL) verwendet:

//

// Wenn die DLL Funktionen exportiert, die String-Objekte (oder Strukturen/

// Klassen, die verschachtelte Strings enthalten) als Parameter oder Funktionsergebnisse übergibt,

// muß die Bibliothek MEMMGR.LIB im DLL-Projekt und anderen Projekten,

// die die DLL verwenden, vorhanden sein. Sie benötigen MEMMGR.LIB auch dann,

// wenn andere Projekte, die die DLL verwenden, new- oder delete-Operationen

// auf Klassen anwenden, die nicht von TObject abgeleitet sind und die aus der DLL exportiert

// werden. Durch das Hinzufügen von MEMMGR.LIB wird die DLL und deren aufrufende EXEs

// angewiesen, BORLNDMM.DLL als Speicherverwaltung zu benutzen. In diesem Fall

// sollte die Datei BORLNDMM.DLL zusammen mit der DLL weitergegeben werden.

//

// Um die Verwendung von BORLNDMM.DLL, zu vermeiden, sollten String-Informationen als "char *" oder

// ShortString-Parameter weitergegeben werden.

//

// Falls die DLL die dynamische Version der RTL verwendet, müssen Sie

// MEMMGR.LIB nicht explizit angeben.

//---------------------------------------------------------------------------

#pragma argsused

//global constants

const char* const DBFILENAME="orderManagement.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\\Excel Multivalue Formula\\orderManagement.mdb"";Persist Security Info=False");

}

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;

}

////////////////////////////////////////////////////////////////////////

// Name: getArticles_dll()

// Purpose: Implementation of getArticles_dll()

// Return: VARIANT

////////////////////////////////////////////////////////////////////////

VARIANT __export __stdcall getArticles_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 TArticleDataset* dataset = requestController.getArticles(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::getArticles,

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"

//---------------------------------------------------------------------------

· Schritt 8: neue Funktion in Excel Add-In einbauen

Public Declare Sub setExcelApp Lib "D:\develop\Excel Multivalue Formula\ordermgt.dll" (ByVal pExcelApp As Variant)

Private Declare Function getOrderItems_dll Lib "D:\develop\Excel Multivalue Formula\ordermgt.dll" _

(ByVal pstartdate As Date, _

ByVal penddate As Date, _

ByVal fieldList As String, _

ByVal sortingCriteria As String, _

ByVal header As Boolean, _

ByVal currentCell As Variant) As Variant

Private Declare Function getCustomers_dll Lib "D:\develop\Excel Multivalue Formula\ordermgt.dll" _

(ByVal fieldList As String, _

ByVal sortingCriteria As String, _

ByVal header As Boolean, _

ByVal currentCell As Variant) As Variant

Private Declare Function getArticles_dll Lib "D:\develop\Excel Multivalue Formula\ordermgt.dll" _

(ByVal fieldList As String, _

ByVal sortingCriteria As String, _

ByVal header As Boolean, _

ByVal currentCell As Variant) As Variant

Public Function getOrderItems(Optional ByVal pstartdate As Date = 0, _

Optional ByVal penddate As Date = 2958465, _

Optional ByVal fieldList As String = "", _

Optional ByVal sortingCriteria As String = "", _

Optional ByVal header As Boolean = True) As Variant

'Aufruf der entsprechenden Funktion in der dll: als zusätzlicher Parameter wird die Zelle, aus der der Aufruf

'erfolgt, übergeben.

getOrderItems = getOrderItems_dll(pstartdate, penddate, fieldList, sortingCriteria, header, Application.Caller)

End Function

Public Function getCustomers(Optional ByVal fieldList As String = "", _

Optional ByVal sortingCriteria As String = "", _

Optional ByVal header As Boolean = True) As Variant

'Aufruf der entsprechenden Funktion in der dll: als zusätzlicher Parameter wird die Zelle, aus der der Aufruf

'erfolgt, übergeben.

getCustomers = getCustomers_dll(fieldList, sortingCriteria, header, Application.Caller)

End Function

Public Function getArticles(Optional ByVal fieldList As String = "", _

Optional ByVal sortingCriteria As String = "", _

Optional ByVal header As Boolean = True) As Variant

'Aufruf der entsprechenden Funktion in der dll: als zusätzlicher Parameter wird die Zelle, aus der der Aufruf

'erfolgt, übergeben.

getArticles = getArticles_dll(fieldList, sortingCriteria, header, Application.Caller)

End Function

Sub Auto_open()

setExcelApp Application

End Sub