none
OpenXML return cell value without formula RRS feed

  • Question

  • Hi All, I am using the OpenXML api to retrieve cell values as per the MSDN sample GetCellValue (http://msdn.microsoft.com/en-us/library/hh298534) It all works very well. However cells which use a formula return the actual formula as a prefix to the results. Example:

    CONCATENATE(J11,K11)LU-003

    Same applies to numeric formula results. Unfortunately there is no separator between the formula and the result so it not easy to parse the returned value.

    I have searched extensively with no luck. Am I overlooking something simple?

    Regards, Dale 

    • Moved by Bob Shen Tuesday, October 9, 2012 5:17 AM (From:Visual C# General)
    Monday, October 8, 2012 11:51 AM

Answers

  • Hi Dale,

    Thanks for posting in the MSDN Forum.

    // 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))
        {
            // Retrieve a reference to the workbook part.
            WorkbookPart wbPart = 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 = wbPart.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)(wbPart.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.Descendants<CellValue>().FirstOrDefault().Text;
    
                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 = 
                                wbPart.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;
    }

    Please try it.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, October 9, 2012 7:08 AM
    Moderator

All replies

  • You should post this question to Open XML Format SDK forum. You might get more answers there.
    Monday, October 8, 2012 12:31 PM
  • Hi Dale,

    Thanks for posting in the MSDN Forum.

    // 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))
        {
            // Retrieve a reference to the workbook part.
            WorkbookPart wbPart = 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 = wbPart.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)(wbPart.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.Descendants<CellValue>().FirstOrDefault().Text;
    
                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 = 
                                wbPart.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;
    }

    Please try it.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, October 9, 2012 7:08 AM
    Moderator