We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Copying Named Ranges from one worksheet to another

Hi

I have some named ranges defined in one aspose worksheet and I am copying it to another aspose worksheet. I am observing that after copying, the named ranges from the first sheet are not copied to the second sheet.

Please help us in providing with the correct way to copy named ranges using Aspose cells.

@PramodHegde,

How do you copy named ranges from one worksheet to other. Could you please provide your sample code (runnable) that you are using and template file(s) to reproduce the issue, we will check it soon.

We are using Range.Copy() method to copy from one worksheet to another

            IList<ICell> l = FindCells(template3.Workbook, "1,000.00");
            CreateRange(template3.Workbook, l[0].Row, l[0].Column, 1, 1, "SecTotalDeal");
            //SetFormulas(template3.Workbook);
            foreach (var workSheet in template3.Workbook.Worksheets)
            {
                Range sourceRange = workSheet.Cells.MaxDisplayRange;
                Range destinationRange = newWorkbook.Worksheets["QuoteSummary"].Cells.CreateRange(sourceRange.FirstRow + totalRowCount, sourceRange.FirstColumn, sourceRange.RowCount, sourceRange.ColumnCount);
                destinationRange.Copy(sourceRange, new PasteOptions
                {
                    PasteType = PasteType.All
                });
                destinationRange.CopyStyle(sourceRange);
                //totalRowCount = sourceRange.RowCount + totalRowCount + 1;
            }

I am also attaching a console application for further analysis, you can check from line no. 170 onwards in Program.cs file where I am creating a named range and then copying it to another workbook.

AsposeTesting.zip (5.6 MB)

Also note that we are using Range.Copy() since there are cases where we need to copy multiple worksheets into a single worksheet.

@PramodHegde,

I evaluated your sample project and issue in details. This is not an issue with Aspose.Cells APIs. Please note, since you are copying cells ranges b/w the worksheets in multiple workbooks, so the named ranges won’t be copied. This behavior is same with MS Excel. For confirmation, you may open a template file into MS Excel manually and select range of cells then right click and select “Copy”. Now in other workbook sheet, click “Paste” or “Paste Special” command/option. You will also notice, named ranges (references) are not copied in the destination sheet(s). Moreover, named ranges (references) are stored on Workbook/Worksheet level, so you got to copy the workbook/worksheet for it.

Hope, this helps a bit.

@Amjad_Sahi

Thanks for the update. Is there an API in Aspose.Cells which can be used to copy only named ranges from one worksheet to another?

@PramodHegde,

There is no such an API to copy only named ranges b/w worksheets or workbooks. This restriction is same with MS Excel. Also, as we suggested you in the previous post, you should use Workbook.Combine or Worksheet.Copy API. This will copy named ranges in the destination sheets. Moreover, Workbook.Combine can reduce your code for your project. So, you should update your sample code accordingly and use the above mentioned API for the task.

@Amjad_Sahi

Will Workbook.Combine() or Worksheet.Copy() allow us to merge multiple worksheets into a single worksheet? I am asking this because we have use case where we need to merge multiple worksheets into a single worksheet one below another as per this example.

No. it won’t merge multiple sheets into single sheet. The Workbook.Combine method copies/adds all the worksheets from source workbook to destination workbook. If the destination workbook has existing worksheets, it will retain existing ones also. The Worksheet.Copy method will copy the whole worksheet (including its data and other objects) to destination worksheet.