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)
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.
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.
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.
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!
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.
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?
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 }