none
Using C#, Openxml and Excel, how can I get the currently selected value from a Selection list? RRS feed

  • Question

  • I've been attempting to extract the value of a Selection list from within an excel spreadsheet. Retrieving the cell value of a cell with a dropdown simply returns '0'. Surrounding properties and/or validation objects don't seem to hold the answer either. This method works fine for other cells with different data types.

    Any ideas anyone?

    Thanks

    Code I'm using here;

    // Retrieve the value of a cell, given a file name, sheet name, 
        // and address name.
        public static string GetCellValue(string fileName,
            string sheetName,
            string addressName)
        {
            string value = null;
    
            // Open the spreadsheet document for read-only access.
            using (SpreadsheetDocument document =
                SpreadsheetDocument.Open(fileName, false))
            {
                document.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
                document.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;
    
                // Retrieve a reference to the workbook part.
                WorkbookPart workbookPart = document.WorkbookPart;
    
    
    
                // Find the sheet with the supplied name, and then use that 
                // Sheet object to retrieve a reference to the first worksheet.
                Sheet theSheet = workbookPart.Workbook.Descendants<Sheet>().
                  Where(s => s.Name == sheetName).FirstOrDefault();
    
                // Throw an exception if there is no sheet.
                if (theSheet == null)
                {
                    throw new ArgumentException("sheetName");
                }
    
                // Retrieve a reference to the worksheet part.
                WorksheetPart wsPart =
                    (WorksheetPart)(workbookPart.GetPartById(theSheet.Id));
    
                // Use its Worksheet property to get a reference to the cell 
                // whose address matches the address you supplied.
                Cell theCell = wsPart.Worksheet.Descendants<Cell>().
                  Where(c => c.CellReference == addressName).FirstOrDefault();
    
                // If the cell does not exist, return an empty string.
                if (theCell != null)
                {
                    value = theCell.InnerText;
    
                    if (theCell.CellFormula !=null)
                    {
                        value = theCell.CellValue.InnerText;
                    }
    
    
                    // If the cell represents an integer number, you are done. 
                    // For dates, this code returns the serialized value that 
                    // represents the date. The code handles strings and 
                    // Booleans individually. For shared strings, the code 
                    // looks up the corresponding value in the shared string 
                    // table. For Booleans, the code converts the value into 
                    // the words TRUE or FALSE.
                    if (theCell.DataType != null)
                    {
                        switch (theCell.DataType.Value)
                        {
                            case CellValues.SharedString:
    
                                // For shared strings, look up the value in the
                                // shared strings table.
                                var stringTable =
                                    workbookPart.GetPartsOfType<SharedStringTablePart>()
                                    .FirstOrDefault();
    
                                // If the shared string table is missing, something 
                                // is wrong. Return the index that is in
                                // the cell. Otherwise, look up the correct text in 
                                // the table.
                                if (stringTable != null)
                                {
                                    value =
                                        stringTable.SharedStringTable
                                        .ElementAt(int.Parse(value)).InnerText;
                                }
                                break;
    
                            case CellValues.Boolean:
                                switch (value)
                                {
                                    case "0":
                                        value = "FALSE";
                                        break;
                                    default:
                                        value = "TRUE";
                                        break;
                                }
                                break;
                        }
                    }
                }
            }
            return value;
        }

    Saturday, July 6, 2013 8:54 PM

Answers

All replies

  • Please show us the XML that represents this thing you want to access. And tell us in which file in the Zip package it's in.

    Cindy Meister, VSTO/Word MVP, my blog

    Sunday, July 7, 2013 10:18 AM
    Moderator
  • I can't access the xml, I'm using openoffice and it can't save the xlsx format into 2003 xml. Very annoying!

    As for the structure, I imagine it would like any standard dropdown would look like. I'm not doing anything fancy here, it's a dropdown with three values you can select. I've read in a few places this is a flaw in the sdk so I'm wondering if this is even possible(bizarrely).

    Sunday, July 7, 2013 10:23 AM
  • A xlsx file is a Zip package of XML files. All you need to do is put the extension .zip after the .xlsx and Windows should treat it like a Zip archive. (It will prompt you whether you really want to change the extension - just answer "Yes".) Once Windows sees it as a Zip archive you should be able to look at each and every XML file in the archive in order to locate the dropdown you're using. Maybe.

    There's more than one kind of dropdown you can use on an Excel sheet, and it's going to depend on exactly what you're using. It would also help if you can tell us what command you used to create the dropdown?


    Cindy Meister, VSTO/Word MVP, my blog

    Sunday, July 7, 2013 10:44 AM
    Moderator
  • There's alot of xml...

    But I think this is it...

    <dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="B59 B51"><formula1>"Fixed,RPI,CPI"</formula1></dataValidation>

    Sunday, July 7, 2013 10:56 AM
  • I'm not sure...

    Do you find the three values anywhere?

    Can you tell me how you created the dropdown?


    Cindy Meister, VSTO/Word MVP, my blog

    Sunday, July 7, 2013 11:54 AM
    Moderator
  • I didn't create the dropdown or the sheet, so I'm not sure how. 

    If I go to Data -> Validity(Validation in Office) I can change the options available, which makes it a DataValidationList I guess?

    Sunday, July 7, 2013 12:01 PM
  • OK...

    The attribute sqref appears to give you the cell reference of the data validation list. I'm not sure why your is B59 B51; mine is a single cell B1. (Is it possible this list appears in column B, rows 51 through 59?)

    When I look in sheetData for the cell B1 it tells me cell content is of type "s" (string) and the element <v> contains 0 (zero), meaning it's the first entry in the sharedString.xml file.

    From the code you posted, I see that you're familiar with looking up the value of a string in that file, so the "tricky part" is to figure out how to work with the sqref information. See the information on this page, for starters

    http://msdn.microsoft.com/en-us/library/documentformat.openxml.office2010.excel.datavalidation(v=office.14).aspx

    And then here

    http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.datavalidation.sequenceofreferences.aspx


    Cindy Meister, VSTO/Word MVP, my blog

    Monday, July 8, 2013 10:13 AM
    Moderator