O% Data Type Problem in C API and Excel12

W

William C Bonner

I'm trying to update a set of routines that I had working in older excel to
allowing them to work with larger arrays in the new excel.

I've updated my routines to work with XLOPER12 instead of the older XLOPER
formats. In the past my function was registered with a set of parameters that
looked like "ROO" and I'm trying to use the new parameters of "UO%O%$".

In C, the old and new procedure definitions looked like:

LPXLOPER __stdcall cplxSum(unsigned short int *iNumXLRowsA,unsigned short
int *iNumXLColumnsA, double A[],unsigned short int *iNumXLRowsB,unsigned
short int *iNumXLColumnsB, double B[])

LPXLOPER12 __stdcall cplx12Sum(signed int *iNumXLRowsA,signed int
*iNumXLColumnsA, double A[],signed int *iNumXLRowsB,signed int
*iNumXLColumnsB, double B[])

I've been looking at http://msdn.microsoft.com/en-us/library/bb687900.aspx
as to the differences in the new SDK.

The values getting passed in the array A[] or B[] were incorrect when the
second version of the function was registered. In the debugger, I was able to
figure out that the address of those arrays was off by 4 bytes. If I
manually insert this hack, things seem to be working.

// HACK: Total HACK!
char * foo = (char *) A;
foo -= 4;
A = (double *) foo;
foo = (char *) B;
foo -= 4;
B = (double *) foo;
// HACK: End of total hack.

Has anyone else run into problems with the O% Data Type? Is there something
inherently wrong that I'm doing that's causing this behavior?
 
W

William C Bonner

No, the machine in question is still running XP in 32 bit. Also, I believe
that Excel isn't available in 64 bit yet. At least on my other machine that
is 64 bit, it gets installed under the "Program Files (x86)" heirarchy.

Thanks for the suggestion, I just don't think it fixes my problem.
 
W

William C Bonner

I've now created a sample code set.

/////////////////////////////////////////////////////////////////////////////
/*
The problem I am trying to demonstrate here is that when using the O% data
type introduced with
Excel 2007 the pointer to the array of doubles seems to be offset from the
correct location by 4 bytes,
or half the size of a double. The problem does not happen when using the O
data type, but the O data
type will not work with larger array sizes that are possible with Excel 2007
(Excel12)

I have written two worksheet functions to demonstrate the problem. Both
functions do the same
simple task of copying an input array of numbers to the output array. The
first is declared using the
O% data type plus a boolean to disable or enable my hacked solution. The
second simply uses the O
data type and requires no hacking.

I have followed the two worksheet functions with three helper functions to
make the DLL work
correctly as and XLL. First I have my own XLRegister12 function that takes a
series of strings as
parameters and registers a function with Excel. xlAutoOpen is en exported
function of the DLL that
Excel calls when it opens an XLL file. It uses the first function to
register the worksheet functions.
xlAutoFree12 is an exported function to make sure that memory I allocate in
the DLL is freed
properly in the DLL.

I have isolated this bit of code so that a project can be built simply to
demonstrate the problem. In
Visual Studio, create a MFC DLL project. Then add the code from here to the
end of this file to the
main CPP file. You will need to include the xlcall.cpp and xlcall.h and
xlcall32.lib files from the Excel
2007 SDK, which is available at (Excel 2007 SDK: Excel 2007 XLL Software
Development Kit)
http://www.microsoft.com/downloads/...D1-93AB-4BD4-AF18-CB6BB487E1C4&displaylang=en
*/
#include "xlcall.h"
/////////////////////////////////////////////////////////////////////////////
extern CString rgFuncs12[][9] =
{
// Worksheet Functions
{_T("Excel12CopyError"),_T("UO%A$"),_T("Excel12CopyError"),_T("Source
Array, Use Hack(true/false)"),_T("1"),_T("Error Demo"),_T(""),_T(""),_T("Copy
Numbers to demonstrate error")},
{_T("Excel12CopyFine"),_T("UO$"),_T("Excel12CopyFine"),_T("Source
Array"),_T("1"),_T("Error Demo"),_T(""),_T(""),_T("Copy Numbers to
demonstrate error")},
};
extern int rgFuncs12Rows = sizeof(rgFuncs12) / (sizeof(CString) * 9);
LPXLOPER12 __stdcall Excel12CopyError(signed int *iNumXLRowsA,signed int
*iNumXLColumnsA, double A[], short int UseHack)
{
#pragma comment(linker, "/EXPORT:"__FUNCTION__"="__FUNCDNAME__)
AFX_MANAGE_STATE(AfxGetStaticModuleState( )); // Very first thing I should
do on entering an MFC DLL.
XLOPER12 * rval = new XLOPER12;
rval->xltype = xltypeErr | xlbitDLLFree;// Do this now, in case we don't
produce any results.
rval->val.err = xlerrRef;
if ((*iNumXLRowsA > 0) && (*iNumXLColumnsA > 0))
{
// HACK: Total HACK!
// This is where I'm offsetting the pointer by four bytes.
if (UseHack == 1)
{
char * foo = (char *) A;
foo -= 4;
A = (double *) foo;
}
// HACK: End of total hack.
int NumRows = *iNumXLRowsA;
int NumCols = *iNumXLColumnsA;
int OutSize = NumRows * NumCols;
XLOPER12 * lpxData = new XLOPER12[OutSize];
if (lpxData != 0)
{
rval->xltype = xltypeMulti | xlbitDLLFree;
rval->val.array.rows = NumRows;
rval->val.array.columns = NumCols;
rval->val.array.lparray = lpxData;
for (int index = 0; index < OutSize; index++)
{
lpxData[index].xltype = xltypeNum;
lpxData[index].val.num = A[index];
}
}
}
return(rval);
}
/////////////////////////////////////////////////////////////////////////////
LPXLOPER12 __stdcall Excel12CopyFine(unsigned short int
*iNumXLRowsA,unsigned short int *iNumXLColumnsA, double A[])
{
#pragma comment(linker, "/EXPORT:"__FUNCTION__"="__FUNCDNAME__)
AFX_MANAGE_STATE(AfxGetStaticModuleState( )); // Very first thing I should
do on entering an MFC DLL.
XLOPER12 * rval = new XLOPER12;
rval->xltype = xltypeErr | xlbitDLLFree;// Do this now, in case we don't
produce any results.
rval->val.err = xlerrRef;
if ((*iNumXLRowsA > 0) && (*iNumXLColumnsA > 0))
{
int NumRows = *iNumXLRowsA;
int NumCols = *iNumXLColumnsA;
int OutSize = NumRows * NumCols;
XLOPER12 * lpxData = new XLOPER12[OutSize];
if (lpxData != 0)
{
rval->xltype = xltypeMulti | xlbitDLLFree;
rval->val.array.rows = NumRows;
rval->val.array.columns = NumCols;
rval->val.array.lparray = lpxData;
for (int index = 0; index < OutSize; index++)
{
lpxData[index].xltype = xltypeNum;
lpxData[index].val.num = A[index];
}
}
}
return(rval);
}
/////////////////////////////////////////////////////////////////////////////
LPXLOPER12 XLRegister12(const CString Procedure,const CString TypeText,const
CString FunctionName,const CString FunctionArgs,const CString MacroType,const
CString Category,const CString Shortcut,const CString help_topic = _T(""),
const CString function_help = _T(""))
{
static XLOPER12 Result;

Excel12(xlGetName, (LPXLOPER12)&Result, 0);
CStringW csModuleText(Result.val.str, Result.val.str[0] + 1);
Excel12(xlFree, 0, 1, (LPXLOPER12)&Result);
XLOPER12 xModuleText;
xModuleText.xltype = xltypeStr;
xModuleText.val.str = (XCHAR *) csModuleText.GetString();

CStringW csProcedure(Procedure);
csProcedure.Insert( 0, csProcedure.GetLength());
XLOPER12 xProcedure;
xProcedure.xltype = xltypeStr;
xProcedure.val.str = (XCHAR *) csProcedure.GetString();

CStringW csTypeText(TypeText);
csTypeText.Insert( 0, csTypeText.GetLength());
XLOPER12 xTypeText;
xTypeText.xltype = xltypeStr;
xTypeText.val.str = (XCHAR *) csTypeText.GetString();

CStringW csFunctionText(FunctionName);
csFunctionText.Insert( 0, csFunctionText.GetLength());
XLOPER12 xFunctionText;
xFunctionText.xltype = xltypeStr;
xFunctionText.val.str = (XCHAR *) csFunctionText.GetString();

XLOPER12 xArgumentText;
xArgumentText.xltype = xltypeMissing;
CStringW csArgumentText(FunctionArgs);
if (!FunctionArgs.IsEmpty())
{
csArgumentText.Insert( 0, csArgumentText.GetLength());
xArgumentText.xltype = xltypeStr;
xArgumentText.val.str = (XCHAR *) csArgumentText.GetString();
}

XLOPER12 xMacroType;
xMacroType.xltype = xltypeMissing;
CStringW csMacroType(MacroType);
if (!MacroType.IsEmpty())
{
csMacroType.Insert( 0, csArgumentText.GetLength());
xMacroType.xltype = xltypeStr;
xMacroType.val.str = (XCHAR *) csMacroType.GetString();
}

XLOPER12 xCategory;
xCategory.xltype = xltypeMissing;
CStringW csCategory(Category);
if (!Category.IsEmpty())
{
csCategory.Insert( 0, csCategory.GetLength());
xCategory.xltype = xltypeStr;
xCategory.val.str = (XCHAR *) csCategory.GetString();
}

XLOPER12 xShortcutText;
xShortcutText.xltype = xltypeMissing;
CStringW csShortcutText(Shortcut);
if (!Shortcut.IsEmpty())
{
csShortcutText.Insert( 0, csShortcutText.GetLength());
xShortcutText.xltype = xltypeStr;
xShortcutText.val.str = (XCHAR *) csShortcutText.GetString();
}

XLOPER12 x_help_topic;
x_help_topic.xltype = xltypeMissing;
CStringW cs_help_topic(help_topic);
if (!help_topic.IsEmpty())
{
cs_help_topic.Insert( 0, cs_help_topic.GetLength());
x_help_topic.xltype = xltypeStr;
x_help_topic.val.str = (XCHAR *) cs_help_topic.GetString();
}

XLOPER12 x_function_help;
x_function_help.xltype = xltypeMissing;
CStringW cs_function_help(function_help);
if (!function_help.IsEmpty())
{
cs_function_help.Insert( 0, cs_function_help.GetLength());
x_function_help.xltype = xltypeStr;
x_function_help.val.str = (XCHAR *) cs_function_help.GetString();
}

Excel12(xlfRegister, &Result,10,
&xModuleText, &xProcedure,
&xTypeText, &xFunctionText,
&xArgumentText,&xMacroType,
&xCategory, &xShortcutText,
&x_help_topic, &x_function_help);
return(&Result);
}
/////////////////////////////////////////////////////////////////////////////
int __stdcall xlAutoOpen(void)
{
#pragma comment(linker, "/EXPORT:"__FUNCTION__"="__FUNCDNAME__)
AFX_MANAGE_STATE(AfxGetStaticModuleState());
int version = XLCallVer(); // This returns 0x0c00 in Excel version 12
if (version >= 0x0c00)
{
LPXLOPER12 rval;
int count = 0;
for (int i=0; i<rgFuncs12Rows; i++)
{
rval = XLRegister12(rgFuncs12[0], rgFuncs12[1], rgFuncs12[2],
rgFuncs12[3], rgFuncs12[4], rgFuncs12[5], rgFuncs12[6],
rgFuncs12[7], rgFuncs12[8]);
if (rval->xltype != 1) count++;
}
}
return(1);
}
/////////////////////////////////////////////////////////////////////////////
void WINAPI xlAutoFree12(LPXLOPER12 p_oper)
{
#pragma comment(linker, "/EXPORT:"__FUNCTION__"="__FUNCDNAME__)
AFX_MANAGE_STATE(AfxGetStaticModuleState());
switch(p_oper->xltype)
{
case xltypeStr:
case xltypeStr | xlbitDLLFree:
delete[] p_oper->val.str;
break;
case xltypeRef:
case xltypeRef | xlbitDLLFree:
delete[] p_oper->val.mref.lpmref;
break;
case xltypeMulti:
case xltypeMulti | xlbitDLLFree:
// TODO: This should be modified to loop and check all of the individual
values
for (int index = 0; index < (p_oper->val.array.rows *
p_oper->val.array.columns); index++)
if (p_oper->val.array.lparray[index].xltype == xltypeStr)
delete[] p_oper->val.array.lparray[index].val.str;
delete[] p_oper->val.array.lparray; // for allocated memory.
break;
case xltypeBigData:
case xltypeBigData | xlbitDLLFree:
delete[] p_oper->val.bigdata.h.lpbData;
break;
}
delete p_oper;
}
/////////////////////////////////////////////////////////////////////////////
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top