CODEWITHSUNDEEP
excelcheckboxopenxml-sdk
Paul Marsden
Paul Marsden

Reputation: 151

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

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

Upvotes: 1

Views: 1981

Answers (1)

dvjanm
dvjanm

Reputation: 2381

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.

Upvotes: 1

Related Questions