Is it possible to use C++/CLI with xll library ?

Sep 11, 2013 at 10:43 PM
First of all, thanks for the great work. Keep it up.

I am tryng to port my vb dll to c++ and convert it into an add-in using your xll library. I need to use CLI as I intedn to use .Net library objects like DateTime and to use the properties of the cell which are not exposed by the C CPI (I think).

When I create a new project by means of the your provided XLL Addin template and add CLR support to it, I cannot compile the project as I end up getting linker error as follows:
Functions.obj: fatal error LNK1242: '?xll_function@@$$FYGPAV?$XOPER@Uxloper@@@@N@Z' is an invalid export symbol name
So, would it be possible at all, to include CLR support ?

Thanks for any help.
Coordinator
Sep 11, 2013 at 11:03 PM
Hi Alex. Thanks for the kind words.
I can highly recommend https://exceldna.codeplex.com/ by my buddy Govert for creating add-ins that need access to the .Net world.
The xll library sticks to the unmanaged world by design. Just hand someone an xll and it works on all versions of Excel and Windows. (For some value of any.)
If you need a portable datetime library see https://fmsdatetime.codeplex.com, and https://xlldatetime.codeplex.com for the Excel wrapper.
Let me know if you find any cell properties that you think are not available from the C SDK. They are usually there, just difficult to find.
Sep 12, 2013 at 1:54 AM
Edited Sep 12, 2013 at 1:56 AM
Hi Keith, Thanks very much for the reply.

I have tried Excel-DNA and encountered a bunch of problems and had to give up on that.

I need to have access to cell properties like 'Hidden' (to check if the cell is hidden or visible) and I tried asking Govert about it but, unfortunately did not get any answer. So I decided to try XLW and XLL library.

I almost succeeded with XLW library but not really. What I was trying to do was use C++/CLI to obtain two ranges of cells, copy the source range over to the destination range, skipping the hidden cells in the destination range (PasteToVisibleCells was the name of my add-in function). To make that possible, I had to obtain a handle to the excel instance from which the function gets called and convert it into .NET Excel::Application object. I had to do that using Andrew Whitechapel's methods of making dll callls to oleacc.dll and convert the handle to an application object. Some of It worked but not all of it and when I started to debug it, the part that worked initially stopped working as well. Now, when I launch the xll, I can see the menu and click on it but, nothing happens when I click on it. The macro behind it simply doesn't get called. It is very difficult to debug as I need to step into the code right from the start where AutoOpen gets called. Then it displays unhandled exceptions and such things from Excel.exe, 'step into' becomes useless as I will have to keep hitting F11 forever. I guess I will have to delve deeper into XLW library to be able to debug my code successfully !

I had tried to use your xll library in the past, so I was aware of it not supporting the CLI. So, I thought I should confirm that with you.

The next thing I am going to try is, use the hybrid template from XLW and see if that works. One of the XLW examples actually demonstrates how to get the Excel.Application obect in C#. I am going to try and convert it into C++ and see if it works.

Thanks for the DateTime library references. Those seem useful for financial purposes. I need to use the .NET DateTime's parse methods to extract dates from text strings. I don't think I would be able to do that using your datetime libraries, correct me if I am wrong.

Thanks again.
Coordinator
Sep 12, 2013 at 5:03 AM
Don't give up on Excel-DNA. Govert is as passionate about his open source project as I am. Just give him a reproducible bug and he will get to the bottom of it.
XLW is a waste of time. It was pretty good back in the day, but seems to be in maintenance mode now.
No need to cross the street to turn the corner. ExcelX(xlfGetCell, OPERX(15), Cell) returns true if Cell is hidden. macrofun.hlp is the Rosetta Stone for this.

My dirty secret is that I am a unix guy, born and bred. strptime is your solution, but that does not seem to be something Windows supports.
I also use vi to write code. Bill Joy wrote that when he was a grad student at Berkeley. It is much better than ed. [1]
The world has come a long way since then.
Bill Joy is a pretty switched on guy: http://www.wired.com/wired/archive/8.04/joy.html

[1] The Unix Programming Environment (Prentice-Hall Software Series) Paperback by Brian W. Kernighan (Author) , Rob Pike (Author)
Sep 16, 2013 at 1:30 AM
Edited Sep 16, 2013 at 1:31 AM
Thanks for the response Keith.

I did give up on XLW and I am not sure if I should go back to Excel-DNA yet. I am trying to use your library and get things done. I am having a problem with fetching the values from a range.

ExcelX(xlfGetCell, OPERX(15), Cell) actually refers to the cell property IsFromulaHidden which is not the same as Cell.Hidden. This returns false even if the cell is hidden and returns true only if the formula is hidden. I googled a bit and found a workaround. If you use SubTotal function (xlfSubTotal) with the type number greater than 100, it returns a value only if the cell is visible and returns false if not. So, I could use that and get around the problem.

I am a little perplexed over how to get the values from a range. Given the following code, I am unable to get it working, unless I am doing something wrong and don't know how to fetch values from the range.
void WINAPI
xll_PasteToVisCells(void)
{
#pragma XLLEXPORT

    try
    {
               OPERX oxSourceRange = ExcelX(xlfInput, OPERX(_T("Source Range")), OPERX(8), OPERX(_T("Select Source Range")));

        if (oxSourceRange.xltype == xltypeBool && !oxSourceRange)
        {
               return;
        }
        
        if (((oxSourceRange.xltype &  xltypeSRef) != 0 && (oxSourceRange.xltype &  xltypeRef) != 0) || (oxSourceRange.columns() != 1))
        {
            std::stringstream str;
            str << "Source Range was not selected or invalid reference !! " << oxSourceRange.xltype;
            XLL_ERROR(str.str().c_str());                       
            return;
        }
                
        //Get the first cell in Destination Range
        OPERX oxDestCell = ExcelX(xlfInput, OPERX(_T("Destination Range")), OPERX(8), OPERX(_T("Select The First Cell In Destination Range To Copy To:")));
        if (oxDestCell.xltype == xltypeBool && !oxDestCell)
        {
            return;
        }
        
        if (((oxDestCell.xltype &  xltypeSRef) != 0 && (oxDestCell.xltype &  xltypeRef) != 0) || (oxDestCell.rows() != 1 || oxDestCell.columns() != 1))
        {
            std::stringstream str;
            str << "Source Range was not selected or invalid reference !! " << oxDestCell.xltype;
            XLL_ERROR(str.str().c_str());                       
            return;
        }
                
                ExcelX(xlcMessage, OPERX(true), OPERX(_T("Processing... Please Wait...")));

        OPERX oxSourceValues = ExcelX(xlCoerce, oxSourceRange);
        OPERX oxRetVal;
        for (xword iSource = 0; iSource < oxSourceValues.rows(); iSource++)
        {
            for(;;)
            {
                oxRetVal = ExcelX(xlfSubtotal, OPERX(103), oxDestCell);
                if (oxRetVal.val.xbool != 0)
                {
                    ExcelX(xlSet, oxDestCell, oxSourceValues(iSource, 0));
                    ExcelX(xlfOffset, oxDestCell, OPERX(1), OPERX(0));
                    break;
                }
                ExcelX(xlfOffset, oxDestCell, OPERX(1), OPERX(0));
            };
        }
    }
    catch (const std::exception &ex){
        XLL_WARNING(ex.what());
    }   
    ExcelX(xlcMessage, OPERX(false), OPERX(_T("")));
}
The xlType for both oxDestCell and oxSourceRange is xlTypeSRef and for oxSourceValues it is xlTypeMulti. oxSourceValues does have the correct number of rows and columns but fails to fetch the correct values from the SRef. What am I doing wrong here ? Maybe I am just tired. I'd appreciate any pointers in this direction. My intention is to copy the source range over to the destination skipping the hidden cells in the destination range.

I am myself a Unix/Linux guy. I started using linux back in early 1990's with Red Hat version prior to v2.0. Yes, Vim is a much editor that Ed or Emacs, no doubt, at least in my opinion !

I wonder if I can use the source code of strptime and create an equivalent .Net DateTime library !

Thanks again.
Coordinator
Sep 16, 2013 at 2:40 AM
I used Slackware back then. Also had an IBM RT running AIX that I picked up for $300 when I left Brown. Wish I had saved that keyboard.

Your code looks like something I would write up until your for loop with oxSourceValues. Why not use xlSet to paste the range in? If a cell is hidden, it will stay hidden. Or maybe I'm missing something. If you need to find out if it is not visible, maybe ExcelX(xlfGetCell, OPERX(16), ref)[0] == 0 is what you need.

I think strptime is an accident waiting to happen. If you know the format, use <regex> to pull out the pieces. There is no shame in using atoi to get your immediate job done.

Also, macros should return an int 1 on success and 0 on failure.
Sep 16, 2013 at 4:44 AM
Ah ! I remember slackware, used it myself in the 1990s and in mid 2000's but, I guess I preferred BLFS. The first time I compiled BLFS with KDE, it took me about a month ! I had to spend about 6 hours daily on an average and let my laptop keep running day and night compiling ! Yeah, I can understand how you feel about the keyboard, I wish I had saved my 386 ! I would have put it in a glass cage in my living room ! Lol.

My apologies, I did not make my intention very clear. Like the function name suggests, I intend to copy the entire source range from one work(sheet/book) to another work(sheet/book), with the destination sheet having a filter active which would have some rows hidden. When the source range is pasted to the destination, it would be pasted only in the visible cells. I frequently come across workbooks that have same columns but with different contents within those columns and a few Primary Key Columns with the same values in both sheets. So, I usually have a need to filter both worksheets using one or more of those primary key columns and copy data from one or more columns from one sheet over to the other both filtered in the exact same way. Therefore, the pasting operation must skip the hidden cells so, the data gets pasted across the rows having the same primary key.

ExcelX(xlfGetCell, OPERX(16), Cell) return the cell width and returns the same value regardless of whether the cell is hidden or visible. When you hide a column, its width is not set to zero but is maintained at the same value. I have spent quite some time looking into the mcarofun and xlcall.h files to see if there is anything that can help me with this, but couldn't find anything that is obvious. I believe the xlfSubTotal trick as I depicted in my code should do it. I have tested it using VBA and it works.

A couple of months ago I gave up on ExcelDNA as I couldn't find a way around this hidden property of cell. Now that I found a way, I am stuck at yet another thing. I must be missing something very trivial here. I would appreciate any help you may provide with this.

Thanks.
Sep 16, 2013 at 9:37 PM
What is the right way to obtain an xlTypeMulti reference from an xlTypeSRef object and how do you navigate through the xlTypeMulti object ?

Thanks.
Coordinator
Sep 16, 2013 at 9:42 PM
xMulti = ExcelX(xlCoerce, xSRef) Multi's use operator[] for 1-d indexing and operator() for 2-d indexing. Data is row-major.