Excel user defined functions unlimited

//---------------------------------------------------------------------------
//  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:  TDataset.cpp
 * Author:  Herbert Danler
 * Modified: Montag, 8. November 2004 15:21:10
 * Purpose: Implementation of the class TDataset
 * Comment: base class for all classes that return tables as multivalue arrays
 ***********************************************************************/
#include "stdafx.h"
#include "TRangeSize.h"
#include "stringhelper.h"
#include "TFieldDescriptor.h"
#include "TDataset.h"

using namespace excelmvf;

////////////////////////////////////////////////////////////////////////
// Name:       excelmvf::TDataset::setTimestamp()
// Purpose:    Implementation of TDataset::setTimestamp()
// Return:     void
////////////////////////////////////////////////////////////////////////

void excelmvf::TDataset::setTimestamp(void)
{
   timestamp=Now();
}

////////////////////////////////////////////////////////////////////////
// Name:       excelmvf::TDataset::isvalid()
// Purpose:    Implementation of TDataset::isvalid()
// Return:     bool
////////////////////////////////////////////////////////////////////////

bool excelmvf::TDataset::isvalid(void) const
{
   return valid;
}

////////////////////////////////////////////////////////////////////////
// Name:       excelmvf::TDataset::setvalid()
// Purpose:    Implementation of TDataset::setvalid()
// Return:     void
////////////////////////////////////////////////////////////////////////

void excelmvf::TDataset::setvalid(void)
{
   valid=true;
}

////////////////////////////////////////////////////////////////////////
// Name:       excelmvf::TDataset::processFieldList(std::string pfieldList, std::string pdefaultFieldList, TFieldDescriptor ptableFields[], int pnumTableFields)
// Purpose:    Implementation of TDataset::processFieldList()
// Comment:    takes the field list string entered by the user as formula parameter in Excel and converts it to FieldDescriptor, which contains name and number (index) of the field
//             result: the fieldList container contains all Fields to be shown by the current call.
// Parameters:
// - pfieldList
// - pdefaultFieldList
// - ptableFields[]
// - pnumTableFields
// Return:     void
////////////////////////////////////////////////////////////////////////

void excelmvf::TDataset::processFieldList(std::string pfieldList, std::string pdefaultFieldList, TFieldDescriptor ptableFields[], int pnumTableFields)
{
   //if user did not enter a field list, all fields are displayed
   if (pfieldList.empty()){
      pfieldList = pdefaultFieldList;
   }
   int currentpos=0;
   int startpos=0;
   fieldList.clear();
   std::vector<std::string> strFields;
   //Step 1: scan input string for dividers, store strings between dividers in array (vector)
   for (unsigned int i=0;i<pfieldList.length();i++){
      switch (pfieldList[i]){
         case ',':
         case ';':
            currentpos=i;
      }
      if (currentpos>startpos){                            
         strFields.push_back(stringhelper::trim(pfieldList.substr(startpos,currentpos-startpos)));
         startpos=currentpos+1;
      }
   }
   
   //insert last value 
   strFields.push_back(stringhelper::trim(pfieldList.substr(startpos,pfieldList.length()-startpos)));
       
   //Step 2: search for numerical representation of the field names
   for (unsigned int i=0; i<strFields.size();i++){
      for (int j=0;j<pnumTableFields;j++){
         if (stringhelper::makeUpper(strFields[i]) == ptableFields[j].fieldName){
            //if field description is numeric (column number), store the first tableFields descriptor with alphanumeric field description
            if (strcmp(ptableFields[j].fieldName,"0")==0 || atoi(ptableFields[j].fieldName)){
               fieldList.push_back(ptableFields[j + 1]);
            }
            else{
               fieldList.push_back(ptableFields[j]);
            }
            break;
         }
      }
   }
        
}

////////////////////////////////////////////////////////////////////////
// Name:       excelmvf::TDataset::getResultArray(std::string pfieldList, std::string pdefaultFieldList, TFieldDescriptor ptableFields[], int pnumTableFields, bool pheader)
// Purpose:    Implementation of TDataset::getResultArray()
// Comment:    returns an appropriately sized array of Variants containing the requested Data
// Parameters:
// - pfieldList
// - pdefaultFieldList
// - ptableFields[]
// - pnumTableFields
// - pheader
// Return:     VARIANT
////////////////////////////////////////////////////////////////////////

VARIANT excelmvf::TDataset::getResultArray(std::string pfieldList, std::string pdefaultFieldList, TFieldDescriptor ptableFields[], int pnumTableFields, bool pheader)
{
   //convert String field list to numeric list
   processFieldList(pfieldList,pdefaultFieldList,ptableFields,pnumTableFields);
   
      
   //Attention: The caller of this function has to release the SAFEARRAY by calling VariantClear with the returned Variant!!
   VARIANT v;
   v.vt = VT_ARRAY | VT_VARIANT;
   SAFEARRAYBOUND sab[2];
   int rowsrequired = 0;
   
   rowsrequired =getRecordContainer().size();
   
   //add 1 to the required rows if headers are wanted
   if (pheader) rowsrequired++;

   //size Array
   sab[0].lLbound = 0; sab[0].cElements = rowsrequired;
   sab[1].lLbound = 0; sab[1].cElements = fieldList.size();

   //create array
   v.parray = SafeArrayCreate(VT_VARIANT, 2, sab);
   
   //add headers
   if (pheader){
      for (unsigned int j=0;j<fieldList.size();j++){
         VARIANT tmp;
         int reqbufsize;
         LPWSTR p;
         tmp.vt=VT_BSTR;
         reqbufsize = MultiByteToWideChar(CP_ACP, 0, fieldList[j].fieldName, -1, NULL, 0);
         p= new WCHAR [ reqbufsize ];
         MultiByteToWideChar(CP_ACP, 0, fieldList[j].fieldName, -1, p, reqbufsize);
         tmp.bstrVal= SysAllocString(p);
         delete[] p;
         long indices[] = {0,j};
         SafeArrayPutElement(v.parray, indices, (void *)&tmp);
         
      }
   }

   //loop rows
   int rowcounter = 0;
   for (unsigned int i=0;i<getRecordContainer().size();i++){
      //loop cols
      for (unsigned int j=0;j<fieldList.size();j++){
         VARIANT tmp;
         tmp = getRecordContainer()[i].fieldAsVARIANT(fieldList[j].fieldNumber);
         long indices[] = {pheader ? rowcounter + 1 : rowcounter,j};
         SafeArrayPutElement(v.parray, indices, (void *)&tmp);
      }
      rowcounter++;
   }
   return v;
}

////////////////////////////////////////////////////////////////////////
// Name:       excelmvf::TDataset::getTimestamp()
// Purpose:    Implementation of TDataset::getTimestamp()
// Return:     TDate
////////////////////////////////////////////////////////////////////////

TDate excelmvf::TDataset::getTimestamp(void) const
{
   return timestamp;
}

////////////////////////////////////////////////////////////////////////
// Name:       excelmvf::TDataset::setSortCriterias(std::string newSortCriterias)
// Purpose:    Implementation of TDataset::setSortCriterias()
// Parameters:
// - newSortCriterias
// Return:     void
////////////////////////////////////////////////////////////////////////

void excelmvf::TDataset::setSortCriterias(std::string newSortCriterias)
{
   sortCriterias = newSortCriterias;
}