Search code examples
c#.netasp.net-coreopenxml

OpenXml unable to read integer with percentage symbol

Net core with Open XML. In my excel there is value 100%. I am trying to read as below

GetCellValue method

public static string GetCellValue(SpreadsheetDocument doc, Cell cell)
{
    if (cell.CellValue != null)
    {
        string value = cell.CellValue.InnerText;
        if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
        {
            return doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.ElementAt(int.Parse(value)).InnerText;
        }
        else
        {
            return value;
        }
    }
    return string.Empty;
}

GetCell method

 public static Cell GetCell(Worksheet worksheet, string columnName, uint rowIndex)
 {
     Row row = worksheet.GetFirstChild<SheetData>().Elements<Row>().FirstOrDefault(r => r.RowIndex == rowIndex);

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

I am reading as below.

 var data = OpenXmlReader.GetCellValue(spreadsheetDocument, OpenXmlReader.GetCell(workSheet, "E", 11));

The data is coming as 1 whereas actual value in my excel is 100%. I am not sure why this is not properly reading. Can someone please help me? Any help would be appreciated. Thanks

Solution

  • In Excel, the display format of cells and the actual stored data format can be different. When you enter 75% into a cell formatted as a percentage, Excel stores it as a decimal, but when displayed, it still appears as a percentage.

    enter image description here enter image description here

    We can read the format configured in this way from Open XML,and we can see that the read NumberFormatId is 43, and the FormatCode is "_ * #,##0.00_ ;_ * \\-#,##0.00_ ;_ * \"-\"??_ ;_ @_ "

    enter image description here enter image description here

    However, this format is not found in the predefined in NumberFormatId list. So we can set a corresponding format for the return value use OpenXml . Here is an example for you can be used as reference：

    public static string GetCellValue(SpreadsheetDocument doc, Cell cell)
{
     if (cell.CellValue != null)
     {
         string value = cell.CellValue.InnerText;
         if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
         {
             return doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.ElementAt(int.Parse(value)).InnerText;
         }
         else if (cell.StyleIndex != null)
         {
             var styleIndex = (int)cell.StyleIndex.Value;
             var cellFormat = (NumberingFormat)doc.WorkbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats.ElementAt(styleIndex);
             var numberFormat = doc.WorkbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats.Elements<NumberingFormat>()
                 .Where(i => i.NumberFormatId.ToString() == cellFormat.NumberFormatId.ToString()).FirstOrDefault().FormatCode;
             if (numberFormat != null )
             {
                 var cellValue = double.Parse(value);
                 value = (cellValue * 100).ToString("0.00") + "%";
             }
         }
         return value;
     }
     return string.Empty;
}

    Return data:

    enter image description here