Combining Multiple workbooks keeping the tab/worhsheet name same

Aspose.Cells Product Family
1

My requirement: Combining 2 or more workbooks into a single workBook keeping the name of the worsheets as is. Also please note that here there are two dynamic pieces.

1) Not sure of how many workbooks we need to combine

2) Not sure of how many worksheets will be there in each workbook

Explaination of the attachments:

WorkBook1, WorkBook2 and WorkBook3 are the ones to be combined to a single one

FinalWorkBook - Is the one that need to be produced

Coding Done

I have done the following coding and created the FinalWorkBook, but the problem I am facing is worksheet names are not gatting retained

Within loop I have used the following line of code

Workbook excelWorkbook1 = new Workbook(inputStream1);
excelWorkbook1.save(stream, FileFormatType.XLSX);

Please find attached the code for you referrence

Please help

2

Hi Shubhabrata,

Thank you for considering Aspose products.

Please check the below provided simplest source code snippet that merges multiple worksheets from several workbook into one workbook while maintaining the original worksheet names. Please note, I have used the latest version of Aspose.Cells for Java (Latest Version) for my testing. Give it a try at your end, and feed us back with your results.

Java


//Create an ArrayList of files to be merged
ArrayList list = new ArrayList();
list.add(myDir + “WorkBook1.xlsx”);
list.add(myDir + “WorkBook2.xlsx”);
list.add(myDir + “WorkBook3.xlsx”);
//Create an instance of Workbook for merged worksheets
Workbook outputWorkbook = new Workbook();
//Clear the worksheet collection, as by default there is one empty worksheet in every new workbook
outputWorkbook.getWorksheets().clear();

//Loop over the input files
for(int fileIndex = 0; fileIndex < list.size(); fileIndex++)
{
//Load the indexed input workbook
Workbook inputWorkook = new Workbook(list.get(fileIndex));
//Loop over the worksheets in the loaded workbook
for(int worksheetIndex = 0; worksheetIndex<inputWorkook.getWorksheets().getCount(); worksheetIndex++)
{
//Get the indexed worksheet name
String worksheetName = inputWorkook.getWorksheets().get(worksheetIndex).getName();
System.out.println("worksheetName " + worksheetName);
//Add a new worksheet in the collection and get the index
int index = outputWorkbook.getWorksheets().add();
//Copy the worksheet into output workbook
outputWorkbook.getWorksheets().get(index).copy(inputWorkook.getWorksheets().get(worksheetIndex));
//Rename the worksheet in output workbook as it was in original spreadsheet
outputWorkbook.getWorksheets().get(index).setName(worksheetName);
}

}
//Save output
outputWorkbook.save(myDir+“output.xlsx”);

3

Hi Raza,

Thank you very much for the quick response. It is working as expected. Thank you again

4

Hi Raza,

As mentioned to you earlier, i tried the above code and it works fine. Also I tried the below code and it behaves the same

//Create an ArrayList of files to be merged ArrayList list = new ArrayList();
list.add(myDir + "WorkBook1.xlsx");
list.add(myDir + "WorkBook2.xlsx");
list.add(myDir + "WorkBook3.xlsx");
//Create an instance of Workbook for merged worksheets
Workbook outputWorkbook = new Workbook();
//Clear the worksheet collection, as by default there is one empty worksheet in every new workbook
outputWorkbook.getWorksheets().clear();
//Loop over the input files
for(int fileIndex = 0; fileIndex < list.size(); fileIndex++)
{
//Load the indexed input workbook
Workbook inputWorkook = new Workbook(list.get(fileIndex));
//Loop over the worksheets in the loaded workbook
outputWorkbook.combine(inputWorkook);
}


//Save output
outputWorkbook.save(myDir+"output.xlsx");

In the actual Code I have used "mergedWorkbook.save(stream, FileFormatType.XLSX);" to save the final workbook (attached the actual Code)

Now the new problem I am facing is with the format of the saved outputWorkBook. Please find attached the inputWorkBook and the corresponding outputWorkBook.

You will see that the width of the merged cells etc are all gone and the corresponding texts are not readable. Is there any way this can be resolved using ASPOSE? Below is the problem statement in details

Problem Statement: Merged WorkBook Format is getting lost

Attachment List

1. CoverageSpecMerge.txt - Actual JAVA Code

2. CoverageSpec2007_obfuscated.xlsx - InputWorkBook

3. CoverageSpec2007_obfuscated_afteraspose.xlsx - Merged workbook and format lost

5

Hi Shubhabrata,

We are sorry for your inconvenience.

Thanks to your provided sample, we are able to observe the presented issue while using the latest version of Aspose.Cells for Java 7.6.1. We have noticed that after merging multiple workbooks, the column width settings are lost, due to which the text in the merged cells become unreadable. A ticket (CELLSJAVA-40679) has been logged in our bug tracking system to look further into this matter. Please spare us little time to properly analyze the problem cause on our end, and to provide a fix at earliest. In the meanwhile, we will keep you posted with updates in this regard.

6

Hi Raza,

Thank you very much for the response and looking into it. We are at a client engagement using this specific code. Now this formatting issue is a showstopper for the project. Can you please take a look at this bug as a high priority item and let us know at the earliest?

Thanks

Subho

7

Hi Subho,

Thank you for your response.

We are pleased to inform you that the ticket logged earlier as CELLSJAVA-40679, has been marked Resolved from the development end. The fix will be available with the next major release of Aspose.Cells for Java 7.7.0. As soon as we schedule the said release, we will inform you here.

8

Hi,

Please try the latest version/fix: Aspose.Cells for Java (Latest Version). We have fixed your issue now.

Let us know your feedback.

Thank you.

9

Hi Raza,

This works fantastic !!!! Thank you very much

Thanks

Subho

10

Hi,


Good to know that your issue is resolved by the new fix/version. I have closed your ticket now. 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.
11

Hi Raza,

On the same topic - Merging multiple excel work books.

Problem Statement - While we are merging multiple workbooks (as per below). WorkBook1.xlsx and WorkBook2.xlsx are protected (password protection) at the workbook level and also in the worksheet level. Now what we are seeing is MergedWorkBook.xlsx is loosing the protection at workbook level but retaining the worksheet level protection

e.g Merge WorkBook1.xlsx and WorkBook2.xlsx to MergedWorkBook.xlsx.

Please let us know how we can resolve the above problem

Not attaching anything. Please let me know if you need any additional details

12

Hi Subho,

Thank you for writing back.

I was able to replicate the said problem while using the latest build of Aspose.Cells for Java 7.6.1.4. A ticket with Id CELLSJAVA-40691 has been logged in our bug tracking system to further investigate the presented behavior on our end. We will keep you posted with updates in this regard.

We are sorry for your inconvenience.

13

Hi,

We have evaluated your issue further. Well, it’s global setting regarding Protected Workbooks. So, we cannot protect the workbook when combining workbooks which are protected because we do not know which workbook’s setting should be applied to be prevailed.

So, it is requested that you should protect the workbook by yourself.

Thank you.

14

The issues you have found earlier (filed as CELLSJAVA-40679) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.