How to access a FormControl checkbox in an Excel sheet using OpenXML SDK

Home Forums SpreadsheetML How to access a FormControl checkbox in an Excel sheet using OpenXML SDK

This topic contains 2 replies, has 2 voices, and was last updated by  marspd 10 months ago.

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
    Posts
  • #3492

    marspd
    Participant

    I have a spreadsheet that has a number of check boxes in various cells on the sheet that I need to get the value of (checked/unchecked) from within a c# program.

    I’m using the OpenXML SDK v2.5 and the associated toolbox.

    Using the toolbox I can see the check box controls as part of the AlternateControlParts collection. These are not ActiveX checkboxes but are form controls added via the developer tab in Excel.

    When I use the SDK I can also see the WorkSheetPart which has a ControlPropertiesParts collection on it which lists all the checkboxes.

    My problem is, how do I find which checkbox is in which cell or at least related to which cell?

    I have also found the collection
    wsPart.ControlPropertiesParts.First().DrawingsPart
    .WorkSheetDrawing.DrawingsPart.WorkSheetDrawing

    This collection appears to have the alternate content of each of the checkboxes and if I drill down further I can find the anchor points which appear to give the location of the checkboxes relative to the cells on the sheet. However, the col and row Id’s don’t appear to exactly match up and I suspect that the Offset values may also have something to do with it.

    If someone can point me in the right direction on how to map the checkboxes to the correct row/cells I would be very grateful.

    Thank you for any help.

    Regards Paul

    #3511

    Eric White
    Keymaster

    Hi Paul,

    Yes, this is a peculiarity of having ActiveX controls, (also images) on a worksheet. Technically, they are not in a cell – they are on the sheet. If you need to know what cell the ActiveX control is ‘in’, then you need to calculate it based on the position of the ActiveX control.

    You will find the following screen-cast interesting:

    Precisely Placing Images in an Open XML Spreadsheet

    The key to your problem is to understand the metrics and positioning of cells, and to be able to find the location of the ActiveX control via its position.

    Cheers, Eric

    #3515

    marspd
    Participant

    Hi Eric

    Thank you so much for the reply.

    I did indeed find those screen casts of yours (Excellent by the way) and realised what I needed to do as you have pointed out above. I’m currently doing that at the moment.

    If I can possibly ask you another question on the same topic though I would very much appreciate it?

    Using OpenXML I have this bit of code to get a list of all the form controls and whether they are checked or not

    
    foreach (var ctrl in sheetPart.ControlPropertiesParts)
    {
        string ctrlId = sheetPart.GetIdOfPart(ctrl);
        Console.Write($"Ctrl Id:{ctrlId} ");
        if (ctrl.FormControlProperties.Checked != null)
            Console.Write("Checked");
        Console.WriteLine();
    }
    

    and this bit of code to get all the AlternateContent which gives me the locations of all the form controls.

    
    foreach (var altContent in sheetPart.DrawingsPart.WorksheetDrawing.OfType<DocumentFormat.OpenXml.AlternateContent>())
    {
        DocumentFormat.OpenXml.AlternateContentChoice child = altContent.OfType<DocumentFormat.OpenXml.AlternateContentChoice>().FirstOrDefault();
        if (child != null)
        {
            var loc = child.OfType<TwoCellAnchor>().FirstOrDefault();
            if (loc != null)
            {
                var shp = loc.OfType<Shape>().FirstOrDefault();
                if (shp != null)
                {
                    var shpProp = shp.OfType<NonVisualShapeProperties>().FirstOrDefault();
                    var dwgProp = shpProp.OfType<NonVisualDrawingProperties>().FirstOrDefault();
                    Console.WriteLine($"Shape Id:{dwgProp.Id} Name:{dwgProp.Name}");
                }
                var frm = loc.OfType<FromMarker>().FirstOrDefault();
                var to = loc.OfType<ToMarker>().FirstOrDefault();
                if (frm != null && to != null)
                {
                    int frmCol = int.Parse(frm.ColumnId.Text);
                    int frmColOff = int.Parse(frm.ColumnOffset.Text);
                    int frmRow = int.Parse(frm.RowId.Text);
                    int frmRowOff = int.Parse(frm.RowOffset.Text);
    
                    int toCol = int.Parse(to.ColumnId.Text);
                    int toColOff = int.Parse(to.ColumnOffset.Text);
                    int toRow = int.Parse(to.RowId.Text);
                    int toRowOff = int.Parse(to.RowOffset.Text);
                    Console.WriteLine($"From Col:{frmCol}:{frmColOff}  Row:{frmRow}:{frmRowOff}");
                    Console.WriteLine($"To   Col:{toCol}:{toColOff}  Row:{toRow}:{toRowOff}");
                }
            }
        }
    }
    

    However, I can’t see anywhere that links the two lists together. I don’t know which form control relates to which AlternateContent control.

    If I look at the raw Xml I can see the relationship Id’s that link them but I can’t see these relationship Id’s in any of the properties of any of the objects in OpenXML that I am dealing with.

    Can you point me in the right direction by any chance?

    Regards
    Paul

    • This reply was modified 10 months ago by  marspd.
Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.