Multi Level Category Axis Labels lost when saving with RefreshChartCache = true

Aspose.Cells Product Family
1

Hi,

There is a problem saving charts that have Multi Level Category Axis Labels. After opening the excel and saving to stream with the RefreshChartCache = true option, that stream will no longer contain the correct xml for the chart.

Steps to reproduce:

  1. Open the excel from file
  2. Set the RefreshChartCache option to True in OoxmlSaveOptions
  3. Save the excel to stream using the OoxmlSaveOptions
  4. Save the stream to file using File.WriteAllBytes(filename, byte array)
  5. Unzip the saved file
  6. Navigate to charts and open chart1.xml

Result:
The following structure is found for category axis:
<c:cat>
<c:strRef>
<c:f>Sheet1!$B$5:$G$7</c:f>
<c:strCache>
<c:ptCount val=“6” />
<c:pt idx=“0”>
<c:v>A
Label1</c:v>
</c:pt>
…

Expected:
<c:cat>
<c:multiLvlStrRef>
<c:f>Sheet1!$B$5:$G$7</c:f>
<c:multiLvlStrCache>
<c:ptCount val=“6”/>
<c:lvl>
<c:pt idx=“0”>
<c:v>A</c:v>
</c:pt>
…
</c:lvl>
<c:lvl>
<c:pt idx=“0”>
<c:v>Label1</c:v>
</c:pt>
…
</c:lvl>
</c:multiLvlStrCache>
</c:multiLvlStrRef>
</c:cat>

Attached sample code, sample file and result file.Aspose.zip (24.5 KB)

2

@adrian.anton,

Thanks for the template file and details.

Please note the RefreshChartCache option is used to refresh the charts for specific scenarios only. For example, you need to set RefreshChartCache as true if the data source of the chart or the setting of the chart is changed as Aspose.Cells does not automatically refresh the chart by default. Since you are saving the chart in MS Excel file format(e.g XLSX), so you do not need this option to be set. Please set the option to false or do not use this option. I have tested using the following sample code and it works fine, your mentioned data structure of chart1.xml (in the output file) will be same as per the original file:
e.g
Sample code:

Workbook workbook = new Workbook("E:\\test2\\multi level category axis labels\\Aspose\\MultiLevelCategoryLabels.xlsx");

            var saveOptions = new OoxmlSaveOptions(Aspose.Cells.SaveFormat.Xlsx) { RefreshChartCache = false };
            using (var memStream = new MemoryStream())
            {
                workbook.Save(memStream, saveOptions);
                memStream.Seek(0, SeekOrigin.Begin);
                File.WriteAllBytes("e:\\test2\\out1.xlsx", memStream.ToArray());
            }

Hope, this helps a bit.

3

Thank you for the response.

We already tried it, however, we do have external data that is being updated and we do need to update the chart cache as well.

4

@adrian.anton,

We have understood the requirement but we need to look into it more. We have logged the issue in our database for investigation and for a fix(if applicable). Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-46655 - Multi Level Category Axis Labels lost when saving with RefreshChartCache = true
5

@adrian.anton,

Please try our latest version/fix: Aspose.Cells for .NET v19.3.2 (attached)

Your issue should be fixed in it.

Let us know your feedback.
Aspose.Cells19.3.2 For .Net2.Zip (4.8 MB)
Aspose.Cells19.3.2 For .Net2_AuthenticodeSigned.Zip (4.8 MB)
Aspose.Cells19.3.2 For .Net4.0.Zip (4.9 MB)

6

The issues you have found earlier (filed as CELLSNET-46655) have been fixed in Aspose.Cells for .NET v19.4. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi