CODEWITHSUNDEEP
c#excelopenxmlopenxml-sdk
lucidgold
lucidgold

Reputation: 4542

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

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

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?

Upvotes: 1

Views: 1413

Answers (1)

FortyTwo
FortyTwo

Reputation: 2639

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

Upvotes: 1

Related Questions