How can I clone a workbook in Spire.XLS?

Question

I have a Spire.Xls.Workbook object which I would like to clone. Since the class doesn't offer a Clone method I tried to create a new workbook and copy all existing worksheets over from the existing workbook. This is my code:

public void Clone(Workbook workbook, string fileName)
{
    var clone = new Workbook();

    // copy worksheets to List to be able to use foreach
    var worksheets = workbook.Worksheets
        .Cast<Worksheet>()
        .ToList();

    foreach (Worksheet worksheet in worksheets)
    {
        var clonedSheet = worksheet.Clone(worksheet.Parent);
        clone.Worksheets.Add((IWorksheet)clonedSheet);
    }

    clone.SaveToFile(fileName, ExcelVersion.Version2007);
}

The method completes without an error, the file gets created but it doesn't contain any of the cloned worksheets. Am I doing something wrong or is it just not possible to create a clone of a Workbook object?

Answer 1

Use:

    clone.Worksheets.AddCopy(worksheets);

Answer 2

@DheerajMalik's answer works well for .xlsx files. If you have .xlsm files then the macros will get lost. To work around this limitation, here's another approach:

using (var stream = new MemoryStream())
{
    // Save the original to a memory stream
    workbook.SaveToStream(stream);

    // Create a new workbook and load the content from the memory stream
    var clone = new Workbook();
    clone.LoadFromStream(stream);
}