OpenXML: How to get a list of checked check-boxes in Excel sheet?

Question

I am trying to read an Excel document to get a list of checked check-boxes.

I have found a solution here for locating Checkboxes in Word documents using OpenXML, but not able to reuse this for Checkboxes in Excel. It seems that the CheckBox Class is reserved for WordProcessing documents and not Excel.

Code below shows a list of all checkboxes, but no matter what I do, I am not able to access their value. Any ideas?

C# Code:

using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(@"C:\test.xlsx", false))
{
    WorkbookPart workBookPart = spreadsheetDocument.WorkbookPart;

    foreach (Sheet s in workBookPart.Workbook.Descendants<Sheet>())
    {
        if (s.Name.ToString().Equals("Sheet1"))
        {
            WorksheetPart wsPart = workBookPart.GetPartById(s.Id) as WorksheetPart;              

            foreach (DocumentFormat.OpenXml.Spreadsheet.Control cb in wsPart.Worksheet.Descendants<DocumentFormat.OpenXml.Spreadsheet.Control>())
            {
                if (cb.Name.ToString().IndexOf("CheckBox") > -1)
                {
                    textBox1.AppendText(cb.Name + "\n");
                }
            }
        }
    }
}

UPDATE:

It turns out, we are using Active X controls. The following code is able to locate checkboxes correctly, a very very low performing solution, but not using OpenXML:

var xlApp = new Excel.Application();
var xlWorkbook = xlApp.Workbooks.Open(xlFileName);
var xlSheet = xlWorkbook.Worksheets["Sheet1"] as Excel.Worksheet;

StringBuilder x = new StringBuilder();

try
{
    Excel.OLEObjects oleObjects = xlSheet.OLEObjects() as Excel.OLEObjects;

    foreach (Excel.OLEObject item in oleObjects)
    {
        if (item.progID == "Forms.CheckBox.1")
        {
            VBE.CheckBox xlCB = item.Object as VBE.CheckBox;

            if (xlCB.get_Value())
                x.Append(item.Name + ": checked");
            else
                x.Append(item.Name + ": not checked");

            Marshal.ReleaseComObject(xlCB); xlCB = null;
        }

    }

    Marshal.ReleaseComObject(oleObjects); oleObjects = null;
}
catch (Exception ex){ }

Marshal.ReleaseComObject(xlSheet); xlSheet = null;
xlWorkbook.Close(false, Missing.Value, Missing.Value);
Marshal.ReleaseComObject(xlWorkbook); xlWorkbook = null;         

if (xlApp != null)
    xlApp.Quit();

Marshal.ReleaseComObject(xlApp);
xlApp = null;

Any further suggestions?

score 1 · Answer 1 · edited May 23 '17 at 10:31

1

Assuming you are using Form Controls CheckBox something in these lines should help:

//s is the Sheet
WorksheetPart wsPart = workBookPart.GetPartById(s.Id) as WorksheetPart;

foreach (var control in wsPart.ControlPropertiesParts)
{
    string ctrlId = wsPart.GetIdOfPart(control);
    Console.Write("Control Id: {0}", ctrlId);
    if (control.FormControlProperties.Checked != null)
        Console.Write("Checked");
    Console.WriteLine();
}

Unlike MS Word, Excel has Form Controls and Active X Controls. You can read about the difference here

edited May 23 '17 at 10:31

Community

1
1

answered May 17 '17 at 00:24

FortyTwo

2,414
3
22
33

I am using Active X Controls, above code did not locate any Controls. At this point I know I can just use MS Interop to find the checkboxes. However, could we do this via OpenXML? – lucidgold May 17 '17 at 21:05

OpenXML: How to get a list of checked check-boxes in Excel sheet?

C# Code:

UPDATE:

1 Answers1