Jack Miller Jack Miller - 3 months ago 18
C# Question

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.

sly sly
Answer

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;
    }
}