dotnet / dotnet/Open-XML-SDK

Memory Concumption GetFirstChild is way too big

Open
#1,511 3 comments 0 reactions 1 assignee Claimed by @twsouthwick View on GitHub
Dominant language
C#
Stars
4.6k
Forks
606
PR merge metrics
No merged PRs in 30d

Description

Before Saving excel files
![image](https://github.com/dotnet/Open-XML-SDK/assets/33765542/257bd6cf-d322-4791-91fd-f4e7e774f5a9)
After Saving excel file( GC just collected all unused data)
![image](https://github.com/dotnet/Open-XML-SDK/assets/33765542/bbc4035d-71e9-4562-90c3-831a63d4066e)
And After appending DataValidation with code below
![image](https://github.com/dotnet/Open-XML-SDK/assets/33765542/bbb758a5-d38a-4d41-b0c7-b12a34d7a96e)

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
![image](https://github.com/dotnet/Open-XML-SDK/assets/33765542/8406a653-02db-4e5d-b313-2b5c11cde5d4)

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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.