Re: Converting LPXLOPER12 inputs
lab27
> Hey,
>
> In the process of developing UDFs using the Excel 2010 SDK, I've run
> into a problem trying to convert the users input (from an LPXLOPER12)
> to something that I can compare and generally use to process
> information - i.e. converting it to a built-in C type. I have been
> able to convert a numeric variable to an int using something like
> output = (int)input->val.num; (where input is an LPXLOPER12)
> ... and it works fine. The problem arises when i try to convert a text
> string to something more useful. I've found no way of doing this as of
> yet. Any suggestions as to how I might resolve this?
The val.str on an xloper is a length prefixed string - i.e. the first
byte (for excel4 api, or short for excel12) is the length of the
string, and the data PAST that is the actual content.
So you can convert with something like (you can use W2A or such if you
want a narrow string)
std::wstring xl2std(LPXLOPER12 psrc)
{
wchar_t * val = psrc->val.str;
std::wstring tmp(val+1, *(unsigned short *)val);
return tmp;
}
Suggest you get a copy of Stephen Dalton's book - it is a good
introduction for this sort of thing.
n.b. when returning strings in xlopers, you need to allocate the
buffer and do the reverse - make sure you read the documentation
around xlbitDLLFree.
Cheers,
Lee.
Ger Hobbelt
> Hey,
>
> In the process of developing UDFs using the Excel 2010 SDK, I've run
> into a problem trying to convert the users input (from an LPXLOPER12)
> to something that I can compare and generally use to process
> information - i.e. converting it to a built-in C type. I have been
> able to convert a numeric variable to an int using something like
> output = (int)input->val.num; (where input is an LPXLOPER12)
> ... and it works fine. The problem arises when i try to convert a text
> string to something more useful. I've found no way of doing this as of
> yet. Any suggestions as to how I might resolve this?
Input arguments can best be converted to the desired type using the
xlCoerce() call. Basically you let Excel do the work for you by
calling back into Excel and telling it what type you'ld like to have
this time.
When I read you incorrectly here and you've already done the above and
are merely struggling with loading the coerced-or-not values into
standard C types, then for strings you'ld need (IIRC - cave canem; I'm
a bit rusty regarding this one) to convert the BSTR delivered by Excel
into a std::wstring (or std::string, but that assumes you're never
going to have to process anything that's not ASCII, which is a mighty
risky assumption). Not sure about this, but I believe the Excel SDK
had a bit of example code which shows how one might do this. I do
recall that some bits of the SDK sample code weren't exactly up to
par, so caveat emptor and all that.
Me, when in doubt at all, always goes to find where the local copy of
Mr. Dalton's book has gone now and once found, reads up in there. The
above was typed and thought without this desirable assistance, hence
the politicians' waiver re truism of content. ;-)
Cheers,
Ger
lab27
> When I read you incorrectly here and you've already done the above and
> are merely struggling with loading the coerced-or-not values into
> standard C types, then for strings you'ld need (IIRC - cave canem; I'm
> a bit rusty regarding this one) to convert the BSTR delivered by Excel
Minor horribly over-pedantic correction ;) - as above, excel gives a
length prefixed string (as in the first byte/short depending on api
version is the length), so that the pointer is pointing to the length,
and the data follows.
A BSTR points to the contents of the string, with the size of the BSTR
resident in four bytes BEFORE the pointer. (which is one of the
reasons you have to use SysAlloc|FreeString)
Lee.
Ger Hobbelt
> Input arguments can best be converted to the desired type using the
> xlCoerce() call. Basically you let Excel do the work for you by
> calling back into Excel and telling it what type you'ld like to have
> this time.
That's
Excel(xlCoerce, ...)
for Excel 2007/2010 or
Excel4(xlCoerce, ...)
for previous versions.
> a bit rusty regarding this one) to convert the BSTR delivered by Excel
I checked. This is wrong. The BSTR has to do with interfacing with VB
(Visual Basic), which was another can of worms.
Anyway, the SDK has a few routines that showcase data extraction for
XLOPERs and XLOPER12s; see
samples/xloper2v/xloper2v.c : XLOPERToVariant()
for one and
samples/framewrk/framewrk.c : XLOper12ToXLOper() and
XLOperToXLOper12()
probably not exactly what you're looking for, but those should give
you a pretty good idea, with or without the aid of said book.
Ger Hobbelt
> Minor horribly over-pedantic correction ;) - as above, excel gives a
> length prefixed string (as in the first byte/short depending on api
> version is the length), so that the pointer is pointing to the length,
> and the data follows.
>
> A BSTR points to the contents of the string, with the size of the BSTR
> resident in four bytes BEFORE the pointer. (which is one of the
> reasons you have to use SysAlloc|FreeString)
>
> Lee.
Dang, hadn't seen your post yet. Anyway, you're spot on. BSTR joys are
to be had with variants/VB, not with XLL coding.
Cheers,
Ger