Copy excel is not working properly when we try to copy multiple source excel which have one or more pivots to Destination Excel

Aspose.Cells Product Family
,
1

Hi Team,

Good Morning!!!

I am encountering an issue while copying multiple source workbooks containing PivotTables into a destination Excel workbook. The destination workbook is created, but it gets corrupted. When I try to open it, MS Excel displays a message stating, “Found a problem with content and will attempt to recover.” After clicking “Yes,” the file opens, but a pop-up appears indicating that some PivotTables have issues and were discarded.

This issue specifically occurs when the source workbooks contain more than one PivotTable sheet. If a source workbook has only one PivotTable sheet, the process works fine.

I have attached the sample code, source Excel files, and the generated destination Excel file. Please help me resolve this issue as soon as possible.

We are using latest Aspose license (24.10)

FYi…
Attached error screenshot, sample java file, input and output excel files.
Aspose.zip (2.2 MB)

2

@koteswaragunda

It seems you are experiencing issues when copying multiple source Excel workbooks that contain PivotTables into a destination workbook, resulting in a corrupted file. This problem appears to occur specifically when the source workbooks have more than one PivotTable sheet.

To address this issue, I recommend the following steps:

  1. Check for Compatibility: Ensure that the version of Aspose.Cells you are using (24.10) is compatible with the features you are trying to implement, especially with PivotTables.

  2. Copying Worksheets: When copying worksheets that contain PivotTables, it is essential to ensure that all dependencies and references are correctly handled. You can refer to the example code provided in the Aspose.Cells documentation, which demonstrates how to copy worksheets from one workbook to another. Here’s a simplified version of how you might implement this:

    import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;

public class CopyWorksheets {
    public static void main(String[] args) throws Exception {
        // Create a new destination workbook
        Workbook destinationWorkbook = new Workbook();

        // Load source workbooks
        Workbook sourceWorkbook1 = new Workbook("source1.xlsx");
        Workbook sourceWorkbook2 = new Workbook("source2.xlsx");

        // Copy worksheets from source workbooks to destination workbook
        for (int i = 0; i < sourceWorkbook1.getWorksheets().getCount(); i++) {
            Worksheet sourceSheet = sourceWorkbook1.getWorksheets().get(i);
            Worksheet destSheet = destinationWorkbook.getWorksheets().add(sourceSheet.getName());
            destSheet.copy(sourceSheet);
        }

        for (int i = 0; i < sourceWorkbook2.getWorksheets().getCount(); i++) {
            Worksheet sourceSheet = sourceWorkbook2.getWorksheets().get(i);
            Worksheet destSheet = destinationWorkbook.getWorksheets().add(sourceSheet.getName());
            destSheet.copy(sourceSheet);
        }

        // Save the destination workbook
        destinationWorkbook.save("destination.xlsx");
    }
}

  3. Error Handling: Implement error handling to catch any exceptions that may arise during the copying process. This can help identify specific issues related to PivotTables.

  4. Testing with Fewer PivotTables: Since you mentioned that the process works fine with a single PivotTable, try incrementally adding more PivotTables to identify if a specific one is causing the issue.

  5. Contact Support: If the problem persists, consider reaching out to Aspose support with detailed information about the issue, including the sample files and the error messages you are encountering.

By following these steps, you should be able to troubleshoot the issue with copying multiple source workbooks containing PivotTables. If you have any further questions or need additional assistance, feel free to ask!

3

@koteswaragunda
We have reproduced this issue and have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSJAVA-46315

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

4

@koteswaragunda
We have fixed this issue. We will include the fix into the next version 25.4.
And you can try the following codes to merge files :

public static void main(String[] args) throws Throwable {
		  String[] sourceFiles = {
				  dir +"51973.xlsx",

				  dir +"31884.xls",
				  dir +"51972.xlsx"
	        };

	        Workbook distWb = new Workbook();
	        distWb.getWorksheets().removeAt(0); // Remove default sheet

	        // Iterate over source files and copy sheets
	        for (String filePath : sourceFiles) {
	            try {
	                Workbook sourceWb = new Workbook(filePath);
	                distWb.combine(sourceWb);
	                //copySheetsFromWorkbook(distWb, sourceWb);
	            } catch (Exception e) {
	                System.err.println("Error processing file: " + filePath);
	                e.printStackTrace();
	            }
	        }

	        distWb.calculateFormula(true);
	        try {
	            distWb.save(dir +"dest.xls");
	            System.out.println("Process completed successfully");
	        } catch (Exception e) {
	            e.printStackTrace();
	        }
	    }