Issue with copy worksheets and renaming of Named Ranges

srsuvarna · July 3, 2017, 5:25am

1. When Copying the worksheet from one workbook to another workbook the named ranges present in the worksheet are not copied:

We have AsposeTest_Original.xls which has about 10+ ranges defined. When I copy a worksheet into another workbook the corresponding, the worksheet is copied into the new workbook. But the defined ranges are not present. The copied workbook which is saved as AsposeTest_Original_workbook_copy.xls does not have the named range definitions.

Attachment with code: worksheet_copy_workbook.zip (inside reference_code.zip)

2. Issue with NamedRange.setText method

There were a set of named ranges defined in a workbook. We did a mass rename of all the named range names (by appending it with a string constant) using the NamedRange.setText() method. After renaming the ranges, when we tried to lookup for the range by range name, it does not fetch the range object.

Attachment with code: rename_range_issue.zip (inside reference_code.zip)

Thanks!

Shashi

Amjad_Sahi · July 3, 2017, 5:25am

Hi Shashi,

Sorry, we could not get your attachments.

Please do post them again, so that we may check and figure out your issue soon.

Thank you.

srsuvarna · July 3, 2017, 5:25am

Amjad,

Please find attached!

Thanks,
Shashi

Amjad_Sahi · July 3, 2017, 5:25am

Hi Shashi,

Thanks for the template file and sample codes.

1. When Copying the worksheet from one workbook to another workbook the named ranges present in the worksheet are not copied:

Yes, we have found the problem as the named ranges are not copied with the worksheet. We will fix it soon.

2. Issue with NamedRange.setText method

Well, currently Aspose.Cells for java does not completely support to manipulate a named range in the designer file. We will add this feature soon.

Thank you.

srsuvarna · July 3, 2017, 5:25am

Amjad,

For 2, the file that I provided was not a Designer file. It was a normal excel file (though it looked like a designer file).

Thanks,
Shashi

Warren · July 3, 2017, 5:26am

Dear Shashi,

Please try this fix. And it has fixed the bug of calcluating formulas in the worksheet.

srsuvarna · July 3, 2017, 5:26am

Hi Warren,

The issue with Copying the worksheet from one workbook to another workbook is still not fixed completely. After your suggested fix the ranges are copied properly. But the formulas which are using ranges are not proper in the copied worksheet.

In the original sheet - AsposeTest_Original.xls (Attached)

The formula in the cell C2 is "=SUM(colsum.seasonality)".

In the copied sheet - AsposeTest_Copied.xls(Attached)

The formula in the cell C2 is "=SUM(colsum.tempsummary)". This formula should have been in C4.

If you compare the cells C3, C4, and C5 in both original and copied excel, the formulas are not copied properly.

I have attached excel files and sample code that I am using for testing.

Thanks,
Shashi

Amjad_Sahi · July 3, 2017, 5:26am

Hi Shashi,

Thanks for the sample files with code,

We have found the problem, We will figure it our soon.

Thank you.

Warren · July 3, 2017, 5:25am

Hi,

Thanks for your info.

Please try this fix.

srsuvarna · July 3, 2017, 5:26am

Thanks Warren. It is fixed!!

-Shashi

rfcExcel · July 3, 2017, 5:26am

HI,

Has there been any fixes for this one? I also need to copy NamedRange to a new worksheet but it is not getting copied.

Regards,

rfcExcel!

Amjad_Sahi · July 3, 2017, 5:26am

Hi,

Do you mean you want to copy a named range's data to another named range, I think you may use NamedRange.getCells() method to obtain the cells and paste its data into other named range. And we may consider adding NamedRange.copy() method soon.

Thank you.

johnson.shi · July 3, 2017, 5:26am

Hi,

Which version are you using? Please try version <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" />1.9.1, this version copies NamedRange correctly when copying worksheet.

rfcExcel · July 3, 2017, 5:26am

Hi,

I will not be copying a complete worksheet.

Currently I have a worksheet with a few cells which have a NamedRange associated. I am copying these cells onto another worksheet which acts as a 'frame', holding other cells as well coming from various other worksheets. But while copying the cells, the NamedRange specified for the cells does not get copied to the new worksheet.

I am planning to refer to these copied cells using their NamedRange in the new worksheet, but I'm unable to do so currently!

Regards,

rfcExcel!

Amjad_Sahi · July 3, 2017, 5:26am

Hi rfcExcel,

Well, when you copy a whole worksheet to somewhere (in the same workbook or other workbook), the named ranges also get copied, but copy a few cells will not copy the named range to the target sheet. I think you have to manually do it. You can also use NamedRange.getCells() method to obtain the source cells and paste its data into other named range. And we may consider adding NamedRange.copy() method soon.

Thank you.

rfcExcel · July 3, 2017, 5:26am

Hi,

The issue is: data that needs to be copied to another spreadsheet does not have a fixed length. As such, I cannot pre-define a NamedRange in the template.

However, I could use a temporary excel sheet and programmaticaly set a NamedRange to the group of cells in that.Then I could copy these NamedRanged cells to the final excel sheet.

But since the NamedRange is not getting copied on the final sheet as discussed above, I cannot refer to the group of cells.

Is there any other possibilty you think could come of use to solve this problem?

Regards,

rfcExcel!

Amjad_Sahi · July 3, 2017, 5:26am

Hi rfcExcel,

We will get back to you for your issue soon.

Thank you.

johnson.shi · July 3, 2017, 5:26am

Hi rfcExcel,

Please try this attached patch. There is a new method “copyCellRange()” added on Cells object:

/**
* Copys data and formattings of cells in given range.
* @param sourceCells Source Cells object contains data and formattings to copy.
* @param srcRowIndex the start row index of given range in sourceCells to be copied
* @param srcColIndex the start column index of given range in sourceCells to be copied
* @param destRowIndex the destination row index where the start row of source to be copied into.
* @param destColIndex the destination column index where the start column of to be source copied into.
* @param rowCount count of rows to be copied
* @param colCount count of columns to be copied
*/
public void copyCellRange(Cells sourceCells, int srcRowIndex, int srcColIndex, int destRowIndex, int destColIndex, int rowCount, int colCount);

For copying NamedRange between worksheets, you can try code like following:

Worksheets wss = wb.getWorksheets();
NamedRange nr = wss.getRangeByName("testname"); //NamedRange refers to the first sheet
if(nr != null)
{
Cells cellsSrc = wss.getSheet(0).getCells(); //the NamedRange nr refers to this Cells
Cells cellsDest = wss.getSheet(1).getCells();
int rowDest = 20;
int colDest = 15;
int rowCount = nr.getEndRow()-nr.getStartRow()+1;
int colCount = nr.getEndColumn()-nr.getStartColumn()+1;
cellsDest.copyCellRange(cellsSrc, nr.getStartRow(), nr.getStartColumn(), rowDest, colDest, rowCount, colCount); //copy cells to the second sheet
cellsDest.createNamedRange("Sheet2!testname1", rowDest, colDest, nr.getEndRow()+rowDest-nr.getStartRow(), nr.getEndColumn()+colDest-nr.getStartColumn());//create new NamedRange for the copied area
}