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

Question

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

@Paul Marsden - did you find solution for this. am looking the same. — Mukesh Arora
– Mukesh Arora, Commented Nov 11, 2020 at 11:28

Jan · Accepted Answer · 2018-06-20 15:04:11Z

1

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";

But it is simplier to map the FormControl value to a cell and read the cell value if the you can edit the excel file.

edited Jun 20, 2018 at 15:04

Jan

3332 silver badges16 bronze badges

answered Jul 15, 2016 at 13:29

dvjanm

2,3821 gold badge32 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Elmue Over a year ago

This does not work for me. wsPart.Worksheet.Descendants<Control>() is empty.

Jan Over a year ago

I am years late, but @Elmue did you try <DocumentFormat.OpenXml.Spreadsheet.Control>();?

Elmue Over a year ago

I completely abandoned OpenXML. I only wasted my time with this crap which is completely undocumented (no sample code) and does not work.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related