I need to load a file .xlsx who have multiple worksheet.

It look like this :

First Worksheet : Zoo

Name	Location
Zoo	Paris
END

Second Worksheet : Animals

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

Afterwards I have worksheet for each Animal :

Zoe

Name	Unit	Value
Height	cm	280
Weight	kg	4 000
END

Victor

Name	Unit	Value
Height	cm	150
Weight	kg	75
END

ETC

I have a function Load() who call all the function to load the information I need.

public bool Load(string fileName)
{
    bool returnValue = false;
    if (File.Exists(fileName))
    {
        SpreadsheetDocument spreadsheetDocument = spreadsheetDocument.Open(fileName, false);
        if (LoadZoo(spreadsheetDocument))
        {
            returnValue = true;
        }
        if (LoadAnimals(spreadsheetDocument))
        {
            returnValue = true;
        }
        spreadsheetDocument.Close();
    }
    return returnValue;
}

In LoadAnimals I retrieve Information from the second worksheet and I call my function to retrieve information from all Animal Worksheet :

public Dictionary<string, IXlsxAnimals> Animals { get; private set; } = new Dictionary<string, IXlsxAnimals>();

private bool LoadAnimals(SpreadsheetDocument spreadsheetDocuement)
    {
        bool returnValue = false;
        WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
        WorksheetPart worksheetPart = ExcelHelper.GetWorksheetFromSheetname(workbookPart, "Animals");
        if (worksheetPart != null)
        {
            SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
            Sheet sheet = workbookPart.workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == "Animals");
            if (sheet.Name == "Animals")
            {
                Cell cell = worksheetPart.Descendants<Cell>().FirstOrDefault();
                string endingCell = "";
                while (endingCell != "END")
                {
                    foreach (Row row in sheetData.Descendants<Row>())
                    {
                         endingCell = ExcelHelper.GetCellValue(worbookPart, sheetData, $"A{row.RowIndex}");
                         if (endingCell == null)
                         {
                             break;
                         }
                         XlsxAnimals xlsxAnimals = new XlsxAnimals()
                         {
                             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}"),
                             Animal = new Dictionary<string, IXlsxAnimal>();
                         };
                         if (!Animals.ContainsKey(xlsxAnimals.Name))
                         {
                             if(xlsxAnimals.Name != "Name")
                             {
                                 Animals.Add(xlsxAnimals.Name, xlsxAnimals);
                                 LoadAnimal(spreadsheetDocument, xlsxAnimals.Animal, xlsxAnimals);
                             }
                         }
                         returnValue = true;
                     }
                 }
            }
        }
        return returnValue;
    }

And LoadAnimal look like this :

private bool LoadAnimal (SpreadsheetDocument spreadsheetDocument, Dictionary<string, IXlsxAnimal> Animal, XlsxAnimals xlsxAnimals)
{
    bool returnValue = false;
    WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
    WorksheetPart worksheetPart = ExcelHelper.GetworksheetFromSheetName(workbookPart, xlsxAnimals.Name);
    if (worksheetPart != null)
    {
        SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
        Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrdefault(s => s.Name == xlsxAnimals.Name);
        if (sheet.Name == xlsxAnimals.Name)
        {
            Cell cell = worksheetPart.Wroksheet.Descendants<Cell>().FirstOrDefault();
            string endingCell = "";
            while (endingCell != "End")
            {
                foreach (Row row in sheetData.Descendants<Row>())
                {
                    endingCell = ExcelHelper.GetCellValue(workbookPart, sheetData, $"A{row.RowIndex}");
                    if (endingCell == null)
                    {
                        break;
                    }
                    XlsxAnimal xlsxAnimal = new XlsxAnimal()
                    {
                        Name = ExcelHelper.GetCellValue(workbookPart, sheetData, $"A{row.RowIndex}"),
                        Unit = ExcelHelper.GetCellValue(workbookPart, sheetData, $"B{row.RowIndex}"),
                        Value = ExcelHelper.GetCellValue(workbookPart, sheetData, $"C{row.RowIndex}")
                    };
                    if (!Animal.ContainKey(XlsxAnimal.Name))
                    {
                        if (XlsxAnimal.Name != "Name")
                        {
                            Animal.Add(xlsxAnimal.Name, xlsxAnimal);
                        }
                    }
                    returnValue = true;
                }
            }
        }
    }
    return returnValue; 
}

All of my code run, I can retrieve all the information I want.

I want to have my LoadAnimal() function in my function Load() not in LoadAnimals()

public bool Load(string fileName)
{
    bool returnValue = false;
    if (File.Exists(fileName))
    {
        SpreadsheetDocument spreadsheetDocument = spreadsheetDocument.Open(fileName, false);
        if (LoadZoo(spreadsheetDocument))
        {
            returnValue = true;
        }
        if (LoadAnimals(spreadsheetDocument))
        {
            returnValue = true;
        }
        if (LoadAnimal(spreadsheetDocument))
        {
            returnValue = true;
        }
        spreadsheetDocument.Close();
    }
    return returnValue;
}

Something like that, but I dont know what to use as parameters or how to move the function for it to works.

CodePudding user response：

First, comment the following line in your LoadAnimals:

if (!Animals.ContainsKey(xlsxAnimals.Name))
{
    if(xlsxAnimals.Name != "Name")
    {
        Animals.Add(xlsxAnimals.Name, xlsxAnimals);
        //LoadAnimal(spreadsheetDocument, xlsxAnimals.Animal, xlsxAnimals);
    }
}

Secondly, change your Load method as follows:

public bool Load(string fileName)
{
    bool returnValue = false;
    if (File.Exists(fileName))
    {
        SpreadsheetDocument spreadsheetDocument = spreadsheetDocument.Open(fileName, false);
        if (LoadZoo(spreadsheetDocument))
        {
            returnValue = true;
        }
        if (LoadAnimals(spreadsheetDocument))
        {
            returnValue = true;
        }

        // Load the details of each animal here:
        foreach (var animal in Animals) 
        {
            if (LoadAnimal(spreadsheetDocument, animal.Value, animal))
            {
                returnValue = true;
            }
        }
        spreadsheetDocument.Close();
    }
    return returnValue;
}

CodePudding user response：

As @Sergey answered me, I did comment my line in LoadAnimals()

Then I wrote my Load() method like this :

public bool Load(string fileName)
{
    bool returnValue = false;
    if (File.Exists(fileName))
    {
        SpreadsheetDocument spreadsheetDocument = spreadsheetDocument.Open(fileName, false);
        if (LoadZoo(spreadsheetDocument))
        {
            returnValue = true;
        }
        if (LoadAnimals(spreadsheetDocument))
        {
            returnValue = true;
        }

        foreach (XlsxAnimals xlsxAnimals in Animals.Values) 
        {
            if (LoadAnimal(spreadsheetDocument, xlsxAnimals.Animal, xlsxAnimals))
            {
                returnValue = true;
            }
        }
        spreadsheetDocument.Close();
    }
    return returnValue;
}

And everything run perfectly, thank you.