Microsoft Excel has the ability to copy a group of worksheets in a single operation. The benefit of this feature is if there are cell references between the group of worksheets, when those worksheets are copied the dependent cell references are updated to the new dependent worksheet.
For example if you have a worksheet called Source and another called Dependent. In the Dependent worksheet you have a cell with the formula =Source!A1. When you copy both of these worksheets as a group you’ll have 2 new sheets called Source (2) and Dependent (2). The Dependent (2) worksheet formula will have been automatically updated to =‘Source (2)!A1’.
This automatic updating of dependent formulas behavior does not occur if you were to copy the worksheets one at a time.
My question is, is there a facility in Aspose to copy a group of worksheets as one can do in Microsoft Excel, and if so does that facility provide that formula updating behavior?
@weissa
Currently, Aspose.Cells does not support copying multiple worksheets simultaneously and preserve dependencies.
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSNET-54328
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
The issues you have found earlier (filed as CELLSNET-54328) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi
The support for copying worksheets as a group is incredibly helpful, we really appreciate the quick turnaround on this enhancement. The AddCopy method takes an array of sheets to copy from and an array of new sheet names. I noticed that if you pass null for the new sheet name array then the sheets are named using Aspose’s default sheet name algorithm and that the worksheets are always appended to the end of the book. This is ideal for me and I just wanted to verify that passing null for the second argument and then taking the last n worksheets from the worksheets collection would be a supported approach.
For example:
var sourceSheets = new[]{sheet1,sheet2, sheet3};
workbook.Worksheets.AddCopy(sourceSheets ,null);
var newSheets = workbook.Worksheets.Skip(workbook.Worksheets.Count - sourceSheets.Count).ToList();
You’re welcome. It’s nice to know that the supported feature is acceptable to you. And yes, if you put null for the second argument of the AddCopy() method, Aspose.Cells will set worksheet names in n+1, n+2, n+3, and so on… format. It seems you like this format, so we will retain it.