Combine multiple workbooks with charts

Aspose.Cells Product Family
1

Hi,

I'm trying to combine multiple workbooks, generated by an external application, to a new workbook.

The source workbooks including some table data and different charts. The charts are referencing to data, stored in a hidden worksheet with the same name ("_hidden2") in each of the source workbooks.

I use the method workbook#combine() to solve this but I'm struggling with the following problems:

1. The format of the charts changes (colors, format of labels, legend ...)

2. The hidden sheets are getting combined to new sheets "_hidden3", "_hidden4" and so on, but the references of the chart series doesn't change to the new destination names

Is there an easy way to solve this? I'm new to Aspose Cells, evaluating it to use it within a new application.

Thanks in advance,
Andreas

2

Hi,


Could you please attach your sample file(s) which you are combining, also paste your sample code and output file to reproduce the issue on our end. We will check your issue soon.

Thank you.
3

Hi,

thanks for your fast reply!

Attached you'll find an example for the second problem. Two source workbooks, including a chart with data referencing to a worksheet "_hidden2" inside each of them.

The merged result includes (of course) only a single worksheet "_hidden2". The other worksheet that was orgininally called "_hidden2" was added as "Sheet5". So inside the merged workbook both diagramms are referencing to the same sheet and displaying the same data, which is wrong.

My code simply looks like this:

private byte[] concatenateExcelFilesAspose(final File aDirectory) throws Exception {
final ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
final com.aspose.cells.Workbook newWorkbookTest = new com.aspose.cells.Workbook();

final com.aspose.cells.Workbook workbook1 = new com.aspose.cells.Workbook("C:/temp/xls_test/source1.xls");
final com.aspose.cells.Workbook workbook2 = new com.aspose.cells.Workbook("C:/temp/xls_test/source2.xls");

newWorkbookTest.combine(workbook1);
newWorkbookTest.combine(workbook2);

final SaveOptions saveOptions = new XlsSaveOptions();
newWorkbookTest.save(outputStream, saveOptions);

return outputStream.toByteArray();
}

Thanks for your help.

Best Regards

4
Hi,

Thanks for the sample files (input files + output) and code snippet.

I can notice the issue as you mentioned. But, I think the strange thing is that it might be difficult to build a valid logic (for internal codes/modules behind) for merging two workbooks where each workbook has a sheet with the same/identical name that is referencing to the chart or other objects. Our merger process currently do:
1) It will first add all the worksheets with their objects in the workbook to the merger workbook.
2) If it found there is an existing sheet with the same name, it would rename that sheet and copies to the merger workbook. It does not update/modify any internal object (e.g chart) or change its properties/options which is referencing to a worksheet (for its source data).

Anyways, we need to analyse it further. I have logged a ticket with an id "CELLSJAVA-40414". We will look into it soon.

Thank you.
5

Hi Amjad,

i agree that it might be difficult to handle this!

I already tried to solve it manually by iterating over all worksheets, rename them if necessary and add them to my new workbook. But i'm not really sure how to update the referencing charts to the new sheet name. Are there any examples available for a use case like this?

In our application we have to merge more than 200 workbooks in some cases. Most of them include charts. Because each workbook has been generated by an external reporting software in the same way, the charts always references to hidden sheets called "_hiddenX".

Thank you

6

Hi,


Well, as we have logged a ticket into our database, our concerned developer will look into it soon. Please spare us little time (may be 2/3 working days). We will get back to you soon.

Regarding check charts by their data sources (set/get) for the worksheets. one can use the following APIs:

e.g
SeriesCollection.getCategoryData()/setCategoryData()
Series.getXValues()/setXValues, Series.getValues()/setValues()
etc.

Thank you.
7

Hi,

as I already mentioned there is a second problem when I try to merge multiple files via Workbook.combine(). The style and color of charts are lost after merging.

In the enclosed example you'll find source.xls which has been generated via our external reporting software; result.xls is the result after merge. Colors and style of the chart are lost, also the content of the sheets is not correct!

Code-Snippet:

final Workbook newWorkbookTest = new Workbook();
final workbook1 = new Workbook("C:/temp/xls_test/source.xls");

newWorkbookTest.combine(workbook1);

final SaveOptions saveOptions = new XlsSaveOptions();
newWorkbookTest.save(new FileOutputStream(new File("c:/temp/result.xls")), saveOptions);

Could you please check this issue?

Thanks in advance

8

Hi,


I did test your issue using your code and template file, I have found the issue. The chart’s formatting is lost with data as you pointed out as per your output file.

I have logged a ticket with an id “CELLSJAVA-40416” for your issue into our database. We will look into your issue soon.

Thank you.
9

Hi,

Thanks for your posting and using Aspose.Cells for Java.

We have fixed the issue logged as CELLSJAVA-40414. For the issue of chart formatting, we are still working on it.

Please download and try this fix: Aspose.Cells for Java v7.3.5.2 and let us know your feedback.

10

Hi,

thank you, the fix works great!

If you could solve the issue of chart formatting as well, we would be very happy to use Aspose Cells for our project!

Best regards

11
AndreasvN:

Hi,

thank you, the fix works great!

If you could solve the issue of chart formatting as well, we would be very happy to use Aspose Cells for our project!

Best regards

Hi,

Thanks for your comments and good wishes for Aspose. We also wish you a great 2013 year.

We will try our best to fix the other logged issue and keep you updated with any progress.

However, we are afraid to inform you that your issue is logged as a Normal User. If you want us to log your issue as Priority or Enterprise User, you might like to contact Aspose.Purchase department for enhanced logging and support.

Besides, we have also logged your comments in our database against chart formatting issue. Hopefully, your issue will be fixed soon.
12

Hi,

is there any progress regarding CELLSJAVA-40416?

We need to be sure that this issue is fixed before we're able to purchase Aspose Cells.

By the way, there is an other issue with the example delivered to you for CELLSJAVA-40416: If I open the combined new workbook with Excel 2003 all the colors of the table are lost as well, respectively has been changed! In Excel 2010 the table colors are still correct, but we need to be sure that it works for both versions!

Thanks in advance,
Andreas

13

Hi,


I checked your issue’s status, it is not resolved yet. I have also asked the concerned developer()s to update on it or share any eta if we have any. Also I did test and found that MS Excel 2003 displays the table colors differently than MS Excel 2007/2010 (which shows the table fine). We will also look into it as well.

Moreover, we are aware of your concerns. But, sometimes you now we have a loaded/busy schedule to include new enhancements, features and fixes into the product for the users, so we may not find enough time for some specific features or fixes of the users to be sorted out quickly. Anyways, we will surely check if we could prioritize your issue a bit.

Thanks for your understanding!

14

Hi,

We have evaluated your issue “CELLSJAVA-40416” further. Could you tell us what tools do you use to create the source Excel (source.xls in example.zip that you posted) file? We feel the file is strange and is not a standard file, even we can’t open with lower version of MS Excel 2003.

Thank you.

15

Hi,

the file has been generated by SAP Business Objects.

Best regards

16

Hi,


Thanks for providing the information.

We have logged it against your issue “CELLSJAVA-40416” into our database. Our concerned developer will look into it and once we have any update on it, we will let you know here.

Thank you.
17

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

18

Hi,


For issue logged as “CELLSJAVA-40416”:
Please try the latest release v7.4.0 of the product. The generated file is better
than the previous. But it seems not to be completely fine. By analyzing the source file,
we found it is not fit to be a standard document of MS Excel. We only ignored
some errors of them.

19

@AndreasvN,

Please try our latest version/fix: Aspose.Cells for Java v17.9.1.

Your issue “CELLSJAVA-40416” should be fixed in it.

Let us know your feedback.

20

The issues you have found earlier (filed as CELLSJAVA-40416) have been fixed in Aspose.Cells for Java 17.10.