What I am trying to do: .Net Core Controller -> Read file using OpenXML -> Create new spreadsheet document using OpenXML with selected sheets from the previously read file -> return the newly created file.

Some caveats: These sheets that need to be copied to the new workbook will have formulas, references, dataValidations, named ranges and other sorts of links to other sheets in the original workbook that shouldn't be copied. We only want to copy the values and styles.

What I have tried so far:

Approach 1:

using var stream = new MemoryStream();
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

workbookpart.Workbook.Save();

using var stream2 = new MemoryStream();
stream2.Write(workbook, 0, workbook.Length);
stream2.Position = 0;
var document = SpreadsheetDocument.Open(stream2, true);
Sheet sheetToCopy = document.WorkbookPart.Workbook.Sheets.Descendants<Sheet>().Where(x => x.Name == "Report Sheet").First();

sheets.Append(sheetToCopy.CloneNode(false));

workbookpart.Workbook.Save();

spreadsheetDocument.Close();

return stream.ToArray();

Approach 2: I haven't coded this yet, but the idea is to copy each cells value and style from the original sheet, to the new sheet. I am very concerned about the time complexity of this, because the sheets could get very huge.

Approach 3: I kind of have this working. The process here is to modify the originally downloaded workbook itself by removing the unnecessary worksheets, and clearing the named ranges, and data validations, but no matter how many caveats I delete from the original book, there's always another validation, or conditional formatting still lingering around.

I am really open to any ideas and would greatly appreciate some help from the community. Thank you for taking a look.