CopyAfter() is meant to return the newly created sheet, but doesn't seem to

Question

Update: it turns out it was the SaveToStream() method which saves all sheets instead of the the one, that was the confusing part which lead me to believe the sheet being returned wasn't right

Given the documentation on the CopyAfter() method, the return value is the newly copied sheet

So I wrote the first line of code, now commented out (see below).

The problem is, when I use the sheetWithValues variable from the return of the method, the code below it, does not work (ie The formulas are not replaced with values)

When I comment out the use of the returned value from CopyAfter() and substitute it with workbook.Worksheets[0] as done in the code below, it does work (ie the formulas in the sheet are replaced with values)

Given that experiment, it doesn't seem possible that the return value of CopyAfter() is actually returning the newly created sheet, it's returning something else.

So why does the return value of CopyAfter() not return what is obviously expected here? I really want to use the return value, because the alternative code below seems brittle and is unlikely to continue working if sheets are re-arranged.

The goal of the code BTW is simply to copy the entire sheet, with values, before exporting... shortly after this code, I take the copied sheet and stream it to PDF.

//var sheetWithValues = workbook.ActiveWorksheet.CopyAfter(workbook.ActiveWorksheet);
workbook.ActiveWorksheet.CopyAfter(workbook.ActiveWorksheet);
var sheetWithValues = workbook.Worksheets[0];  // no idea why this works and the return value does not
sheetWithValues.UsedRange.Value = sheetWithValues.UsedRange.Value

Answer 1

Based on your follow-up comments, it sounds like the problem is with the expectation of IWorksheet.SaveToStream(...), or any of the other IWorksheet.Save*() methods.

These methods will only ever save just the specified IWorksheet when you are saving to a text-based data file format like FileFormat.CSV or UnicodeText (tab-delimited / *.txt). Saving to an Excel file format like FileFormat.OpenXMLWorkbook will result in the entire workbook (so all sheets) being saved, as there simply are too many potential interdependencies on other parts of a workbook to extract and save only one sheet (even if in your case you expect to only have simple values, and not any formulas referring to other sheets, defined names, etc.)

Right now, it looks like you are making a copy of your sheet in the same workbook as the source. In this case you should delete the source sheet prior to saving, which will leave the copied sheet. Do note that if this workbook has any other sheets, those will be persisted as well. You can delete a sheet via the ISheet.Delete() method.

If your source workbook does have a bunch of other sheets that you don't want persisted in your destination workbook, an alternative to copying in the same workbook would be to create a new workbook (Spreadsheet.Factory.GetWorkbook() method) and copy the source sheet into it, after or before the default "Sheet1" worksheet. You'd still need to delete the default "Sheet1" via the ISheet.Delete() method so that only your copied sheet remains.