//---------------------------------------------------------------------------
// Excel Multivalue Formula Add-In
// Copyright (C) <2005> <Herbert Danler>
// Contact: danler@users.sourceforge.net
// Project Home Page: http://excelmvf.sourceforge.net/
//
// This program is free software; you can redistribute it and/or modify it under
// the terms of the GNU General Public License as published by the Free Software
// Foundation; either version 2 of the License, or (at your option) any
// later version.
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
// or FITNESS FOR A PARTICULAR PURPOSE.
// See the GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License along with
// this program; if not, write to the Free Software Foundation, Inc.,
// 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
//---------------------------------------------------------------------------
/***********************************************************************
* Module: TExcelEventDispatcher.cpp
* Author: Herbert Danler
* Modified: Donnerstag, 12. August 2004 10:31:30
* Purpose: Implementation of the class TExcelEventDispatcher
* Comment: Diese Klasse ist f�r das Abfangen von Excel Events zust�ndig.
* Excel User defined functions k�nnen per Definition NUR EINEN Wert zur�ckgeben. Jeder Versuch, innerhalb eines Formelberechnungsvorganges diese Beschr�nkung zu umgehen, resultiert in COM-Fehlermeldungen.
* Um diese Beschr�nkung zu umgehen, muss das nachfolgende Calculate-Event von Excel abgefangen werden. Wenn Excel dieses Event anspringt, sind bereits alle Zellbeschr�nkungen aus der Formelberechnung aufgehoben und man kann Werte auch in mehrere Zellen schreiben.
***********************************************************************/
#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;
}
// 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;
}
Submit an article or tip