Retrieve a dictionary and add properties in an object with Open XML and CSharp

Question

I have this project for my school where you need to read a file.xlsx with Open XML and C#.

e.g. You have a the file named "Zoo.xlsx" :

Name	Species	Family	Sex	Date Of Birth
Zoe	Elephant	Elephantidae	F	03/19/2004
Victor	Deer	Cervidae	M
Camille	Eagle	Accipitridae	F	09/03/2108
END

Then I have "XlsxFileTest.cs"

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

I have my Animal class in "XlsxAnimal.cs"

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

And finally I have "XlsxFile.cs"

namespace MySchool.Library
{
    public class Xlsxfile
    {
        public dictionary 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();
                Zoo = new Dictionary();
                foreach (Row row in worksheetPart.Worksheet.elements().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;
        }   
    }   
}

I need to put each Name in the dictionary "Zoo" for Key and the class Animal for Value, and I need to put every information in the Class Animal. And I don't want "END" in my dictionary or as an "Animal". I have an error that say "AssertFailedException" for my Assert.IsNotNull(file.Zoo);.

Palle Due · Answer

The comment trail is getting a little long, so here is my version of what would work:

public class XlsxFile
{
    // Zoo is initialized here, no need to defer it to load
    public dictionary Zoo { get; private set; } = new dictionary();

    // Load just wraps private method LoadAnimals, no need to have it
    // public bool Load(string fileName)

    // I've changed the name to show that it loads multiple Animals and made it public. It's also part of the XlsxFile class now.
    public bool LoadAnimals(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();
            // I couldn't figure what FirstOrDefault was doing here, didn't you want all rows?
            foreach (Row row in worksheetPart.Worksheet.elements())
            {
                Animal animal = new Animal
                {
                    // I skipped all the ToString's and used interpolated strings 
                    Name = ExcelHelper.GetCellValue(workbookPart, sheetData, $"A{row.RowIndex}")
                    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}")
                }
                Zoo.Add(animal.Name, animal);
                returnValue = true; // No need to set this every time you load an animal
            }
        }
        return returnValue;
    }
}

See the comments for what I've changed. Now this should work:

var file = new XlsxFile();
bool fileLoad = file.LoadAnimals("Zoo.xlsx"); 
Assert.IsNotNull(file.Zoo);
Assert.IsTrue(file.Zoo.ContainsKey("Zoe"));
Assert.AreEqual("F", file.Zoo["Zoe"].Sex);

Retrieve a dictionary and add properties in an object with Open XML and CSharp

1 Answers