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

[marspdParticipant]

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

[Eric WhiteKeymaster]

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

[marspdParticipant]

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 7 years, 10 months ago by  marspd.

[bcjacobParticipant]

Did you ever find a way to link these? I am trying to get the text body of a check box and the only way to access is through the same alternate content, but I can’t then link the text back to the check box object.

[Bruce ThomsonParticipant]

I am trying to do this also. If I figure it out, I’ll post it.

• This reply was modified 5 years, 4 months ago by  Bruce Thomson. Reason: Information in post was wrong

[Bruce ThomsonParticipant]

Here are routines that access the needed relationships:

       private static void ListRelationships(WorksheetPart wsPart)
        {
            foreach(var partPair in wsPart.Parts)
            {
                if (!partPair.OpenXmlPart.RelationshipType.EndsWith("/ctrlProp"))
                    continue;
                Trace.WriteLine("id=" + partPair.RelationshipId + " checked=" + (((FormControlProperties)partPair.OpenXmlPart.RootElement).Checked != null ? "true" : "false"));
            }
        }

        private static void ListCheckedItems(WorksheetPart wsPart)
        {
            int n = 0;
            foreach(ControlPropertiesPart cpp in wsPart.ControlPropertiesParts)
            {
                if(cpp.FormControlProperties.Checked != null)
                    Trace.WriteLine("checkbox " + n.ToString() + " is checked.");
                n++;
            }
        }

        private static void ListItemNames(WorksheetPart wsPart)
        {
            foreach (var altContent in wsPart.DrawingsPart.WorksheetDrawing.OfType<AlternateContent>())
            {
                AlternateContentChoice child = altContent.OfType<AlternateContentChoice>().First();
                var loc = child.OfType<TwoCellAnchor>().First();
                var shape = loc.OfType<Shape>().First();
                var name = loc.Descendants<NonVisualDrawingProperties>().First().GetAttribute("name", null);
                Trace.WriteLine("display name=" + shape.InnerText + " internal name=" + name.Value);
            }
        }

        private static void ListSheetNames(Worksheet ws)
        {
            Controls controls = ws.Descendants<Controls>().First();
            foreach (var control in controls.Descendants<Control>())
                Trace.WriteLine("shapeId=" + control.ShapeId.ToString() + " id=" + control.Id + " name=" + control.Name);
        }

[Anonymous]

I have a solution, it contains only the logic (The property FormControlProperties is available since Office 2010:

SpreadsheetDocument document;
string sheetName = “sheetName”;
string controlName = “Option Button 5”;
…
var wbPart = document.WorkbookPart;
var theSheet = wbPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == sheetName);
var wsPart = (WorksheetPart)wbPart.GetPartById(theSheet.Id);
var control = wsPart.Worksheet.Descendants<DocumentFormat.OpenXml.Spreadsheet.Control>().FirstOrDefault(c => c.Name == controlName);
var controlProperies = (ControlPropertiesPart)wsPart.GetPartById(control.Id);
bool isChecked = controlProperies.FormControlProperties.Checked == “Checked”;

[Author]

Posts