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