使用Open XML与C#读取Excel构建Animal字典遇断言失败问题求助

我有一个学校项目，需要用Open XML和C#读取Excel文件（如Zoo.xlsx），将每行动物信息存入Animal类，并以Name为Key、Animal实例为Value存入名为Zoo的Dictionary<string, Animal>中，同时需排除Name为“END”的行。目前运行单元测试时，Assert.IsNotNull(file.Zoo);抛出AssertFailedException异常，相关代码如下：

测试类XlsxFileTest代码 #

namespace MySchool.UnitTests
{
    [TestClass]
    public class XlsxFileTest
    {
        private XlsxFile file;

        [TestInitialize]
        public void Setup()
        {
            file = new file();
        }

        [TestCleanup]
        public void Cleanup()
        {
        }
        
        [TestMethod]
        [DeployementItem("Ressources\\Zoo.xlsx")]
        public void LoadSuccessedTest()
        {
            var fileLoad = file.Load("Zoo.xlsx");
            Assert.IsNotNull(file.Zoo);
        }
    }
}

Animal类代码 #

namespace MySchool.Library
{
    public class Animal
    {
        public string Name { get; set; }

        public string Species { get; set; }
        
        public string Family { get; set; }

        public string Sex { get; set; }

        public string DateOfBirth { get; set; }
    }
}

XlsxFile类代码 #

namespace MySchool.Library
{
    public class Xlsxfile
    {
        public dictionary<string, Animal> Zoo { get; private set; }

        public bool Load(string fileName)
        {
            bool returnValue = false;
            if (LoadAnimal(fileName))
            {
                returnValue = true;
            }
            return returnValue;
        }

        private bool LoadAnimal(fileName)
        {
            bool returnValue = false;
            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
            {
                WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
                WorksheetPart worksheetPart = ExcelHelper.GetWorksheetFromSheetname(workbookPart, "ZOO");
                sheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
                Zoo = new Dictionary<string, Animal>();
                foreach (Row row in worksheetPart.Worksheet.elements<Row>().FirstOrDefault())
                {
                   Animal animal = new Animal
                   {
                        Name = ExcelHelper.GetCellValue(workbookPart, sheetData, "A" + row.RowIndex.ToString()),
                        Species = ExcelHelper.GetCellValue(workbookPart, sheetData, "B" + row.RowIndex.ToString()),
                        Family = ExcelHelper.GetCellValue(workbookPart, sheetData, "C" + row.RowIndex.ToString()),
                        Sex = ExcelHelper.GetCellValue(workbookPart, sheetData, "D" + row.RowIndex.ToString()),
                        DateOfBirth = ExcelHelper.GetCellValue(workbookPart, sheetData, "E" + row.RowIndex.ToString())
                    };
                    Zoo.Add(animal.Name, animal);
                    returnValue = true;
                }
            }   
            return returnValue;
        }   
    }   
}

断言失败的核心原因 #

1. 类名/拼写错误：测试类中实例化的是new file()，但实际类名是Xlsxfile，同时存在Deployement→Deployment、Ressources→Resources的拼写错误，导致对象初始化或文件部署失败。
2. 方法参数缺失类型：LoadAnimal(fileName)未指定参数类型，编译不通过，直接导致Zoo字典无法初始化。
3. 行遍历逻辑错误：worksheetPart.Worksheet.elements<Row>().FirstOrDefault()中elements应为Open XML API的Elements，且FirstOrDefault()会取第一行后遍历单元格而非所有行，完全跳过了数据行的处理。
4. 异常未处理：若文件打开失败、工作表不存在，Zoo字典会保持null状态，触发断言失败。
5. 未实现“END”行排除逻辑：现有代码未判断Name字段，无法跳过目标行。

修正后的完整代码 #

测试类XlsxFileTest修正 #

namespace MySchool.UnitTests
{
    [TestClass]
    public class XlsxFileTest
    {
        private Xlsxfile file;

        [TestInitialize]
        public void Setup()
        {
            file = new Xlsxfile();
        }

        [TestCleanup]
        public void Cleanup()
        {
        }
        
        [TestMethod]
        [DeploymentItem("Resources\\Zoo.xlsx")]
        public void LoadSuccessedTest()
        {
            var fileLoad = file.Load("Zoo.xlsx");
            Assert.IsNotNull(file.Zoo);
            Assert.IsTrue(fileLoad);
        }
    }
}

XlsxFile类修正 #

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Collections.Generic;
using System;

namespace MySchool.Library
{
    public class Xlsxfile
    {
        public Dictionary<string, Animal> Zoo { get; private set; }

        public bool Load(string fileName)
        {
            bool returnValue = false;
            try
            {
                returnValue = LoadAnimal(fileName);
            }
            catch
            {
                Zoo = null;
                returnValue = false;
            }
            return returnValue;
        }

        private bool LoadAnimal(string fileName)
        {
            bool returnValue = false;
            Zoo = new Dictionary<string, Animal>();

            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
            {
                WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
                WorksheetPart worksheetPart = ExcelHelper.GetWorksheetFromSheetname(workbookPart, "ZOO");
                
                if (worksheetPart == null)
                {
                    return false;
                }

                SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
                if (sheetData == null)
                {
                    return false;
                }

                // 跳过表头行（如果有表头，可根据实际情况调整是否保留Skip(1)）
                foreach (Row row in sheetData.Elements<Row>().Skip(1))
                {
                    string name = ExcelHelper.GetCellValue(workbookPart, sheetData, $"A{row.RowIndex}");
                    
                    // 排除Name为"END"的行
                    if (string.Equals(name, "END", StringComparison.OrdinalIgnoreCase))
                    {
                        continue;
                    }

                    // 跳过空行或Name为空的行
                    if (string.IsNullOrWhiteSpace(name))
                    {
                        continue;
                    }

                    Animal animal = new Animal
                    {
                        Name = name,
                        Species = ExcelHelper.GetCellValue(workbookPart, sheetData, $"B{row.RowIndex}"),
                        Family = ExcelHelper.GetCellValue(workbookPart, sheetData, $"C{row.RowIndex}"),
                        Sex = ExcelHelper.GetCellValue(workbookPart, sheetData, $"D{row.RowIndex}"),
                        DateOfBirth = ExcelHelper.GetCellValue(workbookPart, sheetData, $"E{row.RowIndex}")
                    };

                    // 避免重复Key，可选择覆盖或抛出异常
                    if (!Zoo.ContainsKey(animal.Name))
                    {
                        Zoo.Add(animal.Name, animal);
                        returnValue = true;
                    }
                }
            }   
            return returnValue;
        }   
    }   
}

额外注意事项 #

• 确保ExcelHelper.GetWorksheetFromSheetname和ExcelHelper.GetCellValue能正确处理共享字符串表，避免读取到索引值而非实际文本。
• 若Excel无表头行，需移除Skip(1)以读取所有数据行。
• 重复Name的场景可根据需求调整：使用Zoo[animal.Name] = animal;覆盖现有值，或抛出异常提示重复。

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