Open-XML-SDK

Open-XML-SDK copied to clipboard

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<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().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<StringValue> { 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<DataValidations>();
            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

Is there still no help or solution for this issue?

@Berethor have you tried this with the most recent 3.0.1?