Memory Concumption GetFirstChild is way too big
- Dominant language
- C#
- Stars
- 4.6k
- Forks
- 606
- PR merge metrics
- No merged PRs in 30d
Description
Before Saving excel files

After Saving excel file( GC just collected all unused data)

And After appending DataValidation with code below

That memory usage after Validation look very awful, when output excel file has only 20mb size, and if i need add another validation to another list, memory just going higher like that

i'm using that code to perform adding Validation
using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
{
IEnumerable sheets = document.WorkbookPart.Workbook.Descendants().Where(s => s.Name == worksheetName);
if (sheets.Count() == 0)
{
return;
}
WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
WorksheetExtensionList worksheetExtensionList = new WorksheetExtensionList();
WorksheetExtension worksheetExtension = new WorksheetExtension() { Uri = "{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}" };
worksheetExtension.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
DataValidations dataValidations = new DataValidations();
DataValidation dataValidation = null;
if (!isRange)
{
dataValidation = new DataValidation
{
Type = DataValidationValues.List,
AllowBlank = true,
SequenceOfReferences = new ListValue { InnerText = string.Format("{0}1:{0}1048576", column) }
};
dataValidation.Append(
new Formula1 { Text = dataContainingSheet }
);
}
else
{
X14.DataValidations dataValidations14 = new X14.DataValidations() { Count = (UInt32Value)1U };
dataValidations14.AddNamespaceDeclaration("xm", "http://schemas.microsoft.com/office/excel/2006/main");
dataValidations14.Append(new X14.DataValidation()
{
Type = DataValidationValues.List,
AllowBlank = true,
ReferenceSequence = new DocumentFormat.OpenXml.Office.Excel.ReferenceSequence(string.Format("{0}1:{0}1048576", column)),
DataValidationForumla1 = new X14.DataValidationForumla1() { Formula = new DocumentFormat.OpenXml.Office.Excel.Formula(dataContainingSheet) }
});
worksheetExtension.Append(dataValidations14);
worksheetExtensionList.Append(worksheetExtension);
worksheetPart.Worksheet.Append(worksheetExtensionList);
worksheetPart.Worksheet.Save();
return;
}
var oldDataValidations = worksheetPart.Worksheet.GetFirstChild();
if (oldDataValidations != null)
{
if (dataValidation != null)
oldDataValidations.Append(dataValidation);
}
else
{
dataValidations.Append(dataValidation);
worksheetPart.Worksheet.AppendChild(dataValidations);
}
on worksheetPart.Worksheet.GetFirstChild it's just taking a lot of memory for that action.
If it was only on my PC, there is no problem, but i have to run that on PC with only 8Gb RAM and it throws OutOfMemory exception sometimes
**Desktop (please complete the following information):**
- OS: Windows 10
- .NET Target: tried migrate to .net 6, but on .net 6 there is always that error, on .net framework 4.7.2 it happens periodically
- DocumentFormat.OpenXml Version: used 2.20.2, 2.11.3 and Open-XML-SDK 2.9.1,2.5.0 on any of them same
**Additional context**
Just don't know how to solve that problem, or there is some another way to add DataValidations to excel files without that memory concumption
Contributor guide
Assessment
This issue has not been assessed yet.