如何用C#+OpenXML导入Excel数据时获取单元格显示值而非精确值

我的Excel单元格显示内容如下：

B
0.23
0.356

但B列单元格实际存储的精确十进制值为0.234567和0.35689，我不需要这些精确值，只需要获取单元格显示的内容。但当前使用的代码获取到的是精确值：

B
0.234567
0.35689

当前使用的导入Excel数据的C#代码如下：

public DataSet GetMigrationExcelData(string fileName, string folderPath)
{
    DataSet dsData = new DataSet();
    try
    {
        using (SpreadsheetDocument doc = SpreadsheetDocument.Open(folderPath + fileName, false))
        {
            foreach (Sheet sh in doc.WorkbookPart.Workbook.Sheets)
            {
                DataTable dtRecords = new DataTable();
              
                Worksheet worksheet = (doc.WorkbookPart.GetPartById(sh.Id.Value) as WorksheetPart).Worksheet;

                IEnumerable<Row> rows = worksheet.Descendants<Row>();

                foreach (Row row in rows)
                {
                    IEnumerable<Cell> cells = GetRowCells(row);

                    if (row.RowIndex.Value == 1)
                    {
                        foreach (Cell cell in row.Descendants<Cell>())
                        {
                            string columnName = GetValue(doc, cell).Trim();
                            dtRecords.Columns.Add(columnName);
                        }
                    }
                    else
                    {
                        int i = 0;
                        dtRecords.Rows.Add();
                        foreach (Cell cell in cells)
                        {
                            if (i < dtRecords.Columns.Count)
                                dtRecords.Rows[dtRecords.Rows.Count - 1][i] = Convert.ToString(GetValue(doc, cell));                                  
                            i++;
                        }
                    }
                }
                dtRecords.TableName = sh.Name;
                dsData.Tables.Add(dtRecords);
            }
        }
    }
    catch (Exception ex)
    {

    }
     return dsData;
}

public static string GetValue(SpreadsheetDocument doc, Cell cell)
{
    string value = string.Empty;
    try
    {
        value = cell.CellValue.Text;

        if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
        {
            return doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.GetItem(int.Parse(value)).InnerText.ToString();
        }
    }
    catch { }
    return value;
}

public static IEnumerable<Cell> GetRowCells(Row row)
{
    int currentCount = 0;

    foreach (DocumentFormat.OpenXml.Spreadsheet.Cell cell in
        row.Descendants<DocumentFormat.OpenXml.Spreadsheet.Cell>())
    {
        string columnName = GetColumnName(cell.CellReference);

        int currentColumnIndex = ConvertColumnNameToNumber(columnName);

        for (; currentCount < currentColumnIndex; currentCount++)
        {
            yield return new DocumentFormat.OpenXml.Spreadsheet.Cell();
        }

        yield return cell;
        currentCount++;
    }
}

要获取单元格的显示值，需要读取Excel的样式信息，根据单元格设置的数字格式对精确值进行格式化。以下是修改后的代码：

修改要点 #

1. 新增获取单元格格式字符串的方法GetCellFormatString，从Workbook的StylesPart中读取格式信息
2. 修改GetValue方法，判断单元格是否为数字类型，应用对应的格式字符串格式化数值

public DataSet GetMigrationExcelData(string fileName, string folderPath)
{
    DataSet dsData = new DataSet();
    try
    {
        using (SpreadsheetDocument doc = SpreadsheetDocument.Open(folderPath + fileName, false))
        {
            // 提前获取样式信息，避免重复读取
            StylesPart stylesPart = doc.WorkbookPart.StylesPart;

            foreach (Sheet sh in doc.WorkbookPart.Workbook.Sheets)
            {
                DataTable dtRecords = new DataTable();
              
                Worksheet worksheet = (doc.WorkbookPart.GetPartById(sh.Id.Value) as WorksheetPart).Worksheet;

                IEnumerable<Row> rows = worksheet.Descendants<Row>();

                foreach (Row row in rows)
                {
                    IEnumerable<Cell> cells = GetRowCells(row);

                    if (row.RowIndex.Value == 1)
                    {
                        foreach (Cell cell in row.Descendants<Cell>())
                        {
                            string columnName = GetValue(doc, cell, stylesPart).Trim();
                            dtRecords.Columns.Add(columnName);
                        }
                    }
                    else
                    {
                        int i = 0;
                        dtRecords.Rows.Add();
                        foreach (Cell cell in cells)
                        {
                            if (i < dtRecords.Columns.Count)
                                dtRecords.Rows[dtRecords.Rows.Count - 1][i] = Convert.ToString(GetValue(doc, cell, stylesPart));                                  
                            i++;
                        }
                    }
                }
                dtRecords.TableName = sh.Name;
                dsData.Tables.Add(dtRecords);
            }
        }
    }
    catch (Exception ex)
    {
        // 建议添加日志记录，便于排查问题
        // Logger.Error(ex, "读取Excel数据失败");
    }
     return dsData;
}

public static string GetValue(SpreadsheetDocument doc, Cell cell, StylesPart stylesPart)
{
    if (cell == null || cell.CellValue == null)
        return string.Empty;

    string cellValue = cell.CellValue.Text;

    // 处理共享字符串
    if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
    {
        return doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.GetItem(int.Parse(cellValue)).InnerText;
    }

    // 处理数字类型，应用显示格式
    if (cell.DataType == null || cell.DataType.Value == CellValues.Number)
    {
        string formatString = GetCellFormatString(cell, stylesPart);
        if (!string.IsNullOrEmpty(formatString) && double.TryParse(cellValue, out double numericValue))
        {
            return numericValue.ToString(formatString);
        }
    }

    return cellValue;
}

// 获取单元格对应的格式字符串
private static string GetCellFormatString(Cell cell, StylesPart stylesPart)
{
    if (cell.StyleIndex == null)
        return string.Empty;

    CellFormats cellFormats = stylesPart.Stylesheet.CellFormats;
    NumberingFormats numberingFormats = stylesPart.Stylesheet.NumberingFormats;

    // 获取单元格格式索引
    uint styleIndex = cell.StyleIndex.Value;
    CellFormat cellFormat = cellFormats.ElementAt((int)styleIndex) as CellFormat;

    if (cellFormat == null || cellFormat.NumberFormatId == null)
        return string.Empty;

    uint numFormatId = cellFormat.NumberFormatId.Value;

    // 查找自定义格式（ID >= 164）
    NumberingFormat numFormat = numberingFormats.Cast<NumberingFormat>()
        .FirstOrDefault(nf => nf.NumberFormatId.Value == numFormatId);

    if (numFormat != null)
        return numFormat.FormatCode;

    // 处理常用内置格式
    switch (numFormatId)
    {
        case 2: return "0.00";
        case 3: return "0.000";
        case 4: return "#,##0.00";
        case 5: return "#,##0.000";
        case 9: return "0%";
        case 10: return "0.00%";
        // 可根据需求添加更多内置格式映射
        default: return string.Empty;
    }
}

public static IEnumerable<Cell> GetRowCells(Row row)
{
    int currentCount = 0;

    foreach (DocumentFormat.OpenXml.Spreadsheet.Cell cell in
        row.Descendants<DocumentFormat.OpenXml.Spreadsheet.Cell>())
    {
        string columnName = GetColumnName(cell.CellReference);

        int currentColumnIndex = ConvertColumnNameToNumber(columnName);

        for (; currentCount < currentColumnIndex; currentCount++)
        {
            yield return new DocumentFormat.OpenXml.Spreadsheet.Cell();
        }

        yield return cell;
        currentCount++;
    }
}

// 补充原代码依赖的列名转换方法
public static string GetColumnName(string cellReference)
{
    return new string(cellReference.TakeWhile(char.IsLetter).ToArray());
}

public static int ConvertColumnNameToNumber(string columnName)
{
    int number = 0;
    foreach (char c in columnName.ToUpper())
    {
        number = number * 26 + (c - 'A' + 1);
    }
    return number - 1; // 转为0-based索引
}

说明 #

• GetCellFormatString方法负责读取单元格的样式配置，匹配对应的数字格式字符串
• 修改后的GetValue方法会自动识别数字类型单元格，用格式字符串对精确值进行格式化，输出和Excel显示一致的内容
• 内置格式映射可根据你实际使用的Excel格式进行扩展
• 建议在异常捕获块中添加日志记录，方便后续排查读取失败问题

内容的提问来源于stack exchange，提问作者shafi7468