ExcelDataReader与OpenXML读取Excel跳过空列致数据错位问题

我尝试将Excel文件数据读取到DataTable中，但Excel里的空单元格会被ExcelDataReader和OpenXML库跳过，导致DataTable的数据顺序错乱。

Excel原始数据： #

错误读取结果： #

Name => A
Contact => M

预期正确结果： #

Name => A
Contact => 
Gender => M

使用的代码 #

ExcelDataReader #

using (var stream = new FileStream(filePath, FileMode.Open))
{
    if (extension == ".xls")
        reader = ExcelReaderFactory.CreateBinaryReader(stream);
    else
        reader = ExcelReaderFactory.CreateOpenXmlReader(stream);

    DataSet ds = new DataSet();
    ds = reader.AsDataSet();
    reader.Close();

    if (ds != null && ds.Tables.Count > 0)
    {
        return ds.Tables[0];
    }
}

OpenXML #

public static string GetCellValue(SpreadsheetDocument document, Cell cell)
{
    SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
    string value = cell.CellValue.InnerXml;

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

修复方案 #

针对ExcelDataReader的修复 #

ExcelDataReader默认跳过空单元格，需通过配置参数启用空单元格保留：

using (var stream = new FileStream(filePath, FileMode.Open))
{
    IExcelDataReader reader = null;
    if (extension == ".xls")
        reader = ExcelReaderFactory.CreateBinaryReader(stream);
    else
        reader = ExcelReaderFactory.CreateOpenXmlReader(stream);

    // 配置DataSet读取规则，强制填充空单元格
    var conf = new ExcelDataSetConfiguration()
    {
        ConfigureDataTable = _ => new ExcelDataTableConfiguration()
        {
            UseHeaderRow = true,
            FillEmptyCells = true // 关键：为空单元格填充默认空字符串
        }
    };

    DataSet ds = reader.AsDataSet(conf);
    reader.Close();

    return ds?.Tables.Count > 0 ? ds.Tables[0] : null;
}

针对OpenXML的修复 #

OpenXML仅存储有值的单元格，需手动遍历列索引补充空值：

public static DataTable ReadExcelToDataTable(string filePath)
{
    DataTable dt = new DataTable();
    using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, false))
    {
        WorkbookPart workbookPart = document.WorkbookPart;
        Sheet sheet = workbookPart.Workbook.Sheets.First();
        WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
        IEnumerable<Row> rows = worksheetPart.Worksheet.Descendants<Row>();

        // 读取表头
        Row headerRow = rows.First();
        foreach (Cell cell in headerRow.Descendants<Cell>())
        {
            dt.Columns.Add(GetCellValue(document, cell));
        }

        // 遍历数据行，按列索引匹配单元格
        foreach (Row row in rows.Skip(1))
        {
            DataRow dataRow = dt.NewRow();
            for (int colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
            {
                // 查找当前列索引对应的单元格
                Cell cell = row.Descendants<Cell>()
                    .FirstOrDefault(c => GetColumnIndex(c.CellReference) == colIndex);
                
                dataRow[colIndex] = cell != null ? GetCellValue(document, cell) : string.Empty;
            }
            dt.Rows.Add(dataRow);
        }
    }
    return dt;
}

// 辅助方法：从单元格引用（如A1、B2）解析列索引
private static int GetColumnIndex(string cellReference)
{
    int index = 0;
    foreach (char c in cellReference)
    {
        if (char.IsLetter(c))
        {
            index = index * 26 + (c - 'A' + 1);
        }
        else break;
    }
    return index - 1; // 转换为0起始索引
}

// 优化后的GetCellValue，增加空值判断
public static string GetCellValue(SpreadsheetDocument document, Cell cell)
{
    if (cell == null || cell.CellValue == null)
        return string.Empty;
        
    SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
    string value = cell.CellValue.InnerXml;

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

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