Access Cell Properties ?

For discussion of the xlw project for producing xlls.

Access Cell Properties ?

Postby AlexBW » Sat Sep 07, 2013 6:17 am

Here is the synopsis. I am creating an Add-In that manipulates text on the worksheet. One of the commands (Ribbon button) deals with a range of cells skipping those that are hidden. So, the command asks for source and destination ranges, manipulates data in the cells of the source range and writes to the destination range skipping the hidden cells in the destination range, thus writing to only the visible cells.

I have tried to achieve this with Excel-DNA, but got lost. Now, I am trying to do this with XLW and hope to find a way.

Is there any way I can access the properties of a cell ?

Assuming there isn't a straight forward way to do this, I wanted to work around it. I tried to use C++/CLI approach, by using the Interop::Excel PIA, by using Workbook^, WorkSheet^ and Range^ objects and referring to the Hidden property of the Range class. Here is the code snippet:

Code: Select all
xlw::XlfOper  rngSourceRef = xlw::XlfServices.Commands.InputReference("Select the source range", "Select Source Range");

xlw::XlfOper  rngDestCell = xlw::XlfServices.Commands.InputReference("Select the destination range", "Select Destination Range");

To do this I should be able to extract the workbook name and worksheet name from rngDestCell as this may be on a different workbook/worksheet. Therefore I need to be sure that I would be writing to the correct workbook/worksheet. I am not sure how I should go about obtaining the workbook and worksheet names and also the range address. In C# and VB I used the Range.Address(External:=true) property which returns the address along with workbook/worksheet names.

So is there a way to get the hidden property of cells from the rngDestCell ? If not is there a way I can get the workbook/worksheet names and the range address in "A1:A10" style ?

Posts: 1
Joined: Sat Sep 07, 2013 4:43 am

Re: Access Cell Properties ?

Postby fKaria » Sat Oct 26, 2013 3:13 am

Hi. Take a look at Information_t in header XlfServices.h

Code: Select all
struct Information_t
        //! gets the reference of the calling cell
        XlfOper GetCallingCell();
        //! gets the reference of the active cell
        XlfOper GetActiveCell();
        //! sets the reference of the active cell
        void SetActiveCell(const XlfOper& ref);
        //! gets the formula in the supplied cell ref in R1C1 notation
        std::string GetFormula(const XlfOper& cellRef);
        //! convert a formula from A1 to R1C1 notation
        std::string ConvertA1FormulaToR1C1(std::string a1Formula);
        //! convert a formula from R1C1 to A1 notation
        std::string ConvertR1C1FormulaToA1(std::string r1c1Formula, bool fixRows = false, bool fixColums = false);
        //! convert A1 style string to a reference
        XlfOper GetCellRefA1(std::string a1Location);
        //! convert R1C1 string to a reference
        XlfOper GetCellRefR1C1(std::string r1c1Location);
        //! convert R[-1]C[1] string to a reference given another cell
        XlfOper GetCellRefR1C1(XlfOper referenceCell, std::string r1c1RelativeLocation);
        //! convert a reference to A1 style text
        std::string GetRefTextA1(const XlfOper& ref);
        //! convert a reference to R1C1 style text
        std::string GetRefTextR1C1(const XlfOper& ref);
        //! get sheet name for a reference
        std::string GetSheetName(const XlfOper& ref);
        //! get current sheet id
        int GetCurrentSheetId();
Posts: 2
Joined: Sat Oct 26, 2013 3:09 am

Return to xlw

Who is online

Users browsing this forum: No registered users and 1 guest