Merge two files with formula

I have a requirement to merge 2 excel files

1. In one excel file I have data in one sheet with sparkline and other sheet contains data used to render sparkline.
Workbook1 -
Sheet 1 - data with sparkline
Sheet 2 - data used to render sparkline in Sheet 1

2. In second file I just have data
Workbook2 -
Sheet 1 - Data
Sheet 2 - Data

Now I am using

workbook2.combine(workbook1);

to combine both the files.

Resulting file has all the sheets in it, but my formula to draw sparkline still refers to Sheet 2 whereas the actual data is moved to Sheet 4 in the merged output.

Expected output would be to have updated formula to refer sparkline data from Sheet 4, as the combine() call would have moved Workbook1 -> Sheet 2 data to Workbook2->Sheet 4.

Could you please let me know how can I resolve this?

Thanks,
Cpunji

Sample code:


//Create an Array (length=2)
String[] files = new String[2];
//Specify files with their paths to be merged
files[0] = “d:\files\MyBook1.xlsx”;
files[1] = “d:\files\MyBook2.xlsx”;
//Output File to be created
String dest = “d:\files\OutputGrandBook.xlsx”;


Workbook workbook1 = new Workbook(files[0]);
Workbook workbook2 = new Workbook(files[1]);

workbook2.combine(workbook1);

workbook2.save(dest);

Hi Cpunji,


Thank you for contacting Aspose support.

We have evaluated your presented scenario on our end while using the latest version of Aspose.Cells for Java 8.2.1, and we are able to replicate the said problem. We have noticed that worksheet reference for Spark-lines has not been updates after merging the workbooks. The problem has been logged in our bug tracking system under the ticket CELLSJAVA-41018 for further investigation & correction purposes (if applicable). In the meanwhile, you can avoid this situation by reversing the merge order, that is; merge Workbook2 in Workbook1. Doing so will keep the original order of the worksheets on which spark-line data is based.

Please check the code for better elaboration.

Java

Workbook workbook1 = new Workbook(files[0]);
Workbook workbook2 = new Workbook(files[1]);
workbook1.combine(workbook2);

Hello Raza,


Thanks for your response.

Reversing the order will work fine with this scenario but not for all.
I have a case where I need to merge more than 2 files and they have common sheet names.

Please let me know whether this case can be resolved or not. If yes by when we can expect the fix to be rolled out. I need to reply back to my clients as well.

Thanks,
Cpunji
Hi,

cpunji:
Please let me know whether this case can be resolved or not. If yes by when we can expect the fix to be rolled out. I need to reply back to my clients as well.


Well, we just logged your issue (about 2 hours before) into our database, so we cannot provide any eta or update on your issue at the moment. We have to first analyze your issue in details if this is applicable or not. If this is feasible, we may provide a fix for your issue. Please spare us little time (about 3-5 working days) to evaluate your issue.

Once we have any update on it, we will let you know here.

Thank you.


Hi Amjad,


Thanks for your response.

I hope this can be resolved at the earliest.

Thanks,
Cpunji

Hi Cpunji,

Thanks for your posting and using Aspose.Cells.

We have checked your issue status from database and we are pleased to inform you that your issue is resolved now. We will provide you a fix in next few days (2~4). Once, it is available for you, we will let you know asap.

Hi Cpunji,


Thank you for your patience with us.

We have fixed the issue logged earlier as CELLSJAVA-41018 with the latest version of Aspose.Cells for Java 8.2.1.2. Please give the latest API a try on your side, and feel free to write back in case you face any difficulty.

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


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