Using the OpenXmlSDK, how can I select/update the value in a cell that is a range-control?

Question

I'm not even sure if I'm using the correct terminology; I will update the question and title as required.

I'm using the OpenXmlSDK to populate a pre-existing Excel 2010 .xlsm file - a macro-enabled worksheet.

I can access worksheets and cells fairly well.

However, I can't figure out how to either access or update the data in a cell that is a dropdown-control that takes its values from a range on another worksheet.

It's labelled as "H13" on the worksheet, and right-click >> format control shows

Input range: 'anotherWorksheet'!$N$3:$N$54
Cell link: 'anotherWorksheet'!$M$3

Whenever I try to get a reference to this cell, I can't find it -- I get a null value

I've tried two access methods:

I'm not even sure if I'm using the correct terminology; I will update the question and title as required.

I'm using the OpenXmlSDK to populate a pre-existing Excel 2010 .xlsm file - a macro-enabled worksheet.

I can access worksheets and cells fairly well.

However, I can't figure out how to either access or update the data in a cell that is a dropdown-control that takes its values from a range on another worksheet.

It's labelled as "H13" on the worksheet, and right-click >> format control shows

Input range: 'anotherWorksheet'!$N$3:$N$54
Cell link: 'anotherWorksheet'!$M$3

Whenever I try to get a reference to this cell, I can't find it -- I get a null value

I've tried two access methods:

    // http://msdn.microsoft.com/en-us/library/ff921204.aspx
    private static Cell GetCell(Worksheet worksheet, string addressName)
    {
        return worksheet.Descendants<Cell>().Where(
          c => c.CellReference == addressName).FirstOrDefault();
    }

and:

    // Given a worksheet, a column name, and a row index,
    // gets the cell at the specified column and
    // http://stackoverflow.com/questions/527028/open-xml-sdk-2-0-how-to-update-a-cell-in-a-spreadsheet
    private static Cell GetCell(Worksheet worksheet, string columnName, uint rowIndex)
    {
        Row row = GetRow(worksheet, rowIndex);

        if (row == null)
            return null;

        return row.Elements<Cell>().Where(c => string.Compare
               (c.CellReference.Value, columnName +
               rowIndex, true) == 0).FirstOrDefault();
    }

    // Given a worksheet and a row index, return the row.
    private static Row GetRow(Worksheet worksheet, uint rowIndex)
    {
        return worksheet.GetFirstChild<SheetData>().
          Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
    }

Both yield null for the target cell H13, but provide references to surrounding cells (ie, `H12, H14, G13'

Actually, I13 also yields null, but that cell is not populated with anything. However, if I can't get a reference, how could I populate it with the SDK? Not my main point, here.

I will be receiving data that will match one of the entries in the dropdown; I just need to actually populate/select that particular entry in the target spreadsheet.

How can I do this with the OpenXmlSDK? I've tried using various open-source libraries, but none seem to support .xslm files (file provided by the client, and cannot be used in another format; macros must execute on launch, etc.).

Although I'm using C#, since my question is about the OpenXmlSDK, I would accept answers in other languages using that framework.

Answer 1

Short Answer: The cell does not exist, thus the null reference.

I created a small worksheet with a list (dropdown) DataValidation pointing to a range of cells in another worksheet.

Reflecting the file using the Open XML SDK 2.0 Productivity Tool I saw that instead of a cell being created and appended to the worksheet, a DataValidation (with a CellReference equating to the target) was created, instead.

    using X14 = DocumentFormat.OpenXml.Office2010.Excel;

    [....]

    X14.DataValidations dataValidations1 = new X14.DataValidations() { Count = (UInt32Value)1U };
    dataValidations1.AddNamespaceDeclaration("xm", "http://schemas.microsoft.com/office/excel/2006/main");

    X14.DataValidation dataValidation1 = new X14.DataValidation() { Type = DataValidationValues.List, AllowBlank = true, ShowInputMessage = true, ShowErrorMessage = true };

    X14.DataValidationForumla1 dataValidationForumla11 = new X14.DataValidationForumla1();
    Excel.Formula formula1 = new Excel.Formula();
    formula1.Text = "Lists!$A$1:$A$51";

    dataValidationForumla11.Append(formula1);
    Excel.ReferenceSequence referenceSequence1 = new Excel.ReferenceSequence();
    referenceSequence1.Text = "A1";

    dataValidation1.Append(dataValidationForumla11);
    dataValidation1.Append(referenceSequence1);

    dataValidations1.Append(dataValidation1);

Unless the worksheet location has a preset value, it will not actually "be" a "cell" when accessed at run-time.

In retrospect, it makes sense. But visually, it looks like a cell, so it's not obvious....

NOT A CELL:

NOTE: if a selection is made from the DataValidation, and saved, the cell now has a value, and so it exists:

IS A CELL:

This can be worked-around by creating and appending a new cell to the target row when a null reference is returned.

The difficulty now lies in the validation requiring a reference to the shared strings table, and will not accept raw-text as the cell value....

UPDATE: I was able to find the DataValidation associated with the cell, find the target worksheet and range, find the target value within that range, and get the SharedStringsTable reference associated with that value. But no matter value I plugged into the target cell, it was all considered bad data by Excel upon opening.

In the end, I gave up, went slinking back to Excel Interop, and was find a method to select a dropdown field from within (ugh) Interop.