Copying worksheets from one workbook to another

Aspose.Cells Product Family
1

Hi.


I want to copy a several sheets from one workbook to another.
Each worksheet contains formulas, that used values from another worksheet in same workbook.
Now i have received broken formulas after copying.

Is there exists a way to save a dependencies of worksheets ?

public void copyWorksheets() {
try {
Workbook sourceWb = new Workbook(“D:\book2.xls”);

Workbook targetWb = new Workbook(“D:\book1.xls”);

copyWorksheet(targetWb, sourceWb, 0);
copyWorksheet(targetWb, sourceWb, 1);

targetWb.save(“D:\out.xls”);

} catch (Exception e) {
e.printStackTrace();
assertNull(e);
}
}

private void copyWorksheet(Workbook targetWb, Workbook sourceWb, int i) throws Exception {
Worksheet sourceWs = sourceWb.getWorksheets().get(i);
Worksheet targetWs = targetWb.getWorksheets().add(sourceWs.getName());
targetWs.copy(sourceWs);
}

2

Hi Nikolaj,

Thank you for using Aspose products.

We are able to observe the problem of “Invalid Cell Reference Error” while copying the worksheets containing the formulas. A ticket (CELLSJAVA-40752) has been logged in our bug tracking system for further investigation and correction purposes. In the meanwhile, we will try to provide you with a workaround for the said situation.

Please accept our apologies for your inconvenience.

3

I not sure that it is bug. If i will create all sheets to target book first, all works perfectly


public void copyWorksheets() {
try {
Workbook sourceWb = new Workbook(“D:\book2.xls”);

Workbook targetWb = new Workbook(“D:\book1.xls”);

copyWorksheet(targetWb, sourceWb, 0, 1);

targetWb.save(“D:\out.xls”);

} catch (Exception e) {
e.printStackTrace();
assertNull(e);
}
}

private void copyWorksheet(Workbook targetWb, Workbook sourceWb, int startIdx, int endIdx) throws Exception {
for (int i = startIdx; i <= endIdx; i++) {
Worksheet sourceWs = sourceWb.getWorksheets().get(i);
targetWb.getWorksheets().add(sourceWs.getName());
}

for (int i = startIdx; i <= endIdx; i++) {
Worksheet sourceWs = sourceWb.getWorksheets().get(i);
Worksheet targetWs = targetWb.getWorksheets().get(sourceWs.getName());
targetWs.copy(sourceWs);
}
}
4

Hi Nikolaj,

Thank you for using Aspose.Cells.

Please use Workbook.combine() method instead of Worksheet.copy() method as you want to merge other workbooks.

5

Many thanks!

6

Hi,


Good to know that Workbook.combine() method sorts out your issue for your needs. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.