CODEWITHSUNDEEP
c#excelopenxml
Jack Miller
Jack Miller

Reputation: 325

C# OpenXML determining if a value is a number or in sharedstringtable

My program needs to scan through an excel document and take the values in particular cells and put them in a list.

However the data in the spreadsheet is all formatted as general and always looks up the value in the SST, I think anyway, and doesn't put any of the number values in the spreadsheet in the list.

How do I tell my program that the data is a number and not a reference to the SST?

foreach (Cell cell in row.Elements<Cell>())
{
    try
    {
        cellvalue1 = cell.CellValue.InnerText;
        if (cell.DataType == CellValues.SharedString && cellvalue2.Any(char.IsDigit))
        {
            cellvalue2 = ssT.ElementAt(Int32.Parse(cellvalue1)).InnerText;
        }
        else 
        {
            cellvalue2 = cell.CellValue.ToString();
        }
    }
    catch (Exception)
    {
        cellvalue2 = " ";
    }                                    

    switch (cellvalue2)
    {
        case ("WELL NAME and NUMBER"):
            WellnameCol = GetColumnName(cell.CellReference);                                                                                                           
            break;
        case ("FLOWING PRESSURE"):
            FlowpCol = GetColumnName(cell.CellReference);
            break;
        case ("SHUT-IN PRESSURE"):
            ShutpCol = GetColumnName(cell.CellReference);
            break;
        default:
            if (GetColumnName(cell.CellReference) == WellnameCol)
            {
                if (cellvalue2.Contains("#"))
                {
                    Wellname.Add(cellvalue2);
                    inRow = true;
                }
                else
                {
                    inRow = false;
                }                                                                                                
            }    
            else if (GetColumnName(cell.CellReference) == FlowpCol)
            {   
                 if (!cellvalue2.Contains("#") && inRow)
                     Flowp.Add(cellvalue2);
            }
            else if (GetColumnName(cell.CellReference) == ShutpCol)
            {   
                 if (inRow)
                 {
                     ShutP.Add(cellvalue2);
                 }
            }
            break;                                           
    }                                                                       
}

The Try Catch statement is to determine whether a cell is empty or not and then returns the cell as a empty string if so.

All help appreciated.

Upvotes: 0

Views: 2530

Answers (2)

sly
sly

Reputation: 318

I've done something similar in the past to extract data from excel spreadsheets below is the specific code I used to retrieve a cell value:

public static string GetCellValue(SharedStringTable sharedStringTable, Cell cell)
{
    string value = cell.CellValue.InnerText;

    if (cell.DataType != null
        && cell.DataType.Value == CellValues.SharedString)
    {
        return sharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
    }
    else
    {
        return value;
    }
}

Upvotes: 0

Ralf B&#246;nning
Ralf B&#246;nning

Reputation: 15415

I have create a small Excel file with a cell formatted as string with text "1234" and a second cell with text content "abcd".

By inspecting the content of the xlsx file I saw their xml codes.

<c r="A1" s="1"><v>1234</v></c>
<c r="A2" t="s"><v>0</v></c>

In the second cell t="s"indicates a shared string. For the first one no explicit data type (t attribute) is given. This differentiation is used in this MSDN sample as well. https://msdn.microsoft.com/de-de/library/office/hh298534.aspx Here is the interesting part for your problem:

        value = theCell.InnerText;
        if (theCell.DataType != null)
        {
            switch (theCell.DataType.Value)
            {
                case CellValues.SharedString:
                    var stringTable = 
                        wbPart.GetPartsOfType<SharedStringTablePart>()
                        .FirstOrDefault();                        
                    if (stringTable != null)
                    {
                        value = 
                            stringTable.SharedStringTable
                            .ElementAt(int.Parse(value)).InnerText;
                    }
                    break;
            }
        }

Hope this helps.

Upvotes: 1

Related Questions