Hi Team,
Good Morning!!!
I am copying an excel file having multiple pivot sheets, total sheets and other data sheets into another destination excel. Observed that the destination excel is created but it got corrupted. Because when try to open MS Excel was saying “found problem with content and try to recover” kind of message. And i clicked on “yes” it opened and there was a pop up saying that some issue with the PivotTable and was discarded. Can you please help me to resolve this issue. And one more thing was, its been observed if the source excel have more than one pivot sheet. In case if the source excel have only one pivot sheet then it was fine. I am enclosing the sample code, source excel and generated destination excel please help me as early as possible.
Sample code:
package com.example.demo.totals;
import java.io.IOException;
import java.io.InputStream;
import com.aspose.cells.License;
import com.aspose.cells.PivotField;
import com.aspose.cells.PivotFieldCollection;
import com.aspose.cells.PivotTable;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;
public class CopyExcel {
public static void main(String[] args) {
setAsposeExcelLicense();
// Instantiate a new workbook
Workbook sourceWb = null;
Workbook distWb = null;
try {
sourceWb = new Workbook("31884.xls");
distWb = new Workbook();
distWb.getWorksheets().removeAt(0);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
for (int i = 0; i < sourceWb.getWorksheets().getCount(); i++) {
Worksheet sourceSheet = sourceWb.getWorksheets().get(i);
String sheetName = sourceSheet.getName();
copySheet(distWb, sourceSheet, true);
}
distWb.calculateFormula(true);
try {
distWb.save("31884-dist.xls");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// Print message
System.out.println("Process completed successfully");
}
private static License setAsposeExcelLicense() {
License license = new License();
try (InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("Aspose_Total.lic")) {
license.setLicense(is);
} catch (IOException e) {
e.printStackTrace();
}
return license;
}
private static Worksheet copySheet(Workbook destinationWb, Worksheet sourceSheet, boolean hide) {
Worksheet destinationSheet = destinationWb.getWorksheets().add(sourceSheet.getName());
try {
destinationSheet.copy(sourceSheet);
destinationSheet.refreshPivotTables();
} catch (Exception e) {
e.printStackTrace();
}
destinationSheet.setVisible(hide);
return destinationSheet;
}
}
excel copy pivottable issue.zip (240.8 KB)