Free Support Forum - aspose.com

Unable to open Excel after flattening Waterfall charts and saving

#1

Hi all,

After flattening labels on a XLSX with a Waterfall chart and saving it once, Aspose throws an exception when trying to open the file again. In addition, it appears as though the Waterfall chart itself stops rendering in the Excel after the initial save (maybe the chart is being corrupted?).

I have attached a sample file and provided some reproduction code below. Thanks!

var format = FileFormatUtil.DetectFileFormat("BrokenWaterfallChart.xlsx");

var asposeLoadOptions = new LoadOptions(format.LoadFormat)
{
	LoadFilter = new LoadFilter(LoadDataFilterOptions.All)
};

using (var workbook = new Workbook("BrokenWaterfallChart.xlsx", asposeLoadOptions))
{
	foreach (var workbookWorksheet in workbook.Worksheets)
	{
		foreach (var chart in workbookWorksheet.Charts)
		{
			chart.Calculate();

			foreach (var nSeries in chart.NSeries)
			{
				nSeries.Name = nSeries.DisplayName;
			}
		}
	}

	workbook.Save("BrokenWaterfallChart.xlsx");
}

// Throws Aspose exception
using (var workbook = new Workbook("BrokenWaterfallChart.xlsx", asposeLoadOptions))
{
}

BrokenWaterfallChart.zip (10.3 KB)

#2

@bvk,

I have tried to compile your code but could not resolve the following call:

chart.FlattenLabels();

Could you please share which version of the product are you using as it is not resolved with the latest version Aspose.Cells for .NET 19.2.x.? Please provide us some sample code which can be compiled with the latest version for our testing and analysis.

#3

Apologies I forgot that was one of our extension methods that implements varying other aspose methods. I edited/expanded the example out

#4

@bvk

The code snippet still includes undefined method,. Flatten. So please share sample application instead so that we can compile and help you accordingly.

#5

@ahsaniqbalsidiqui Sorry once again, I was quickly copy/pasting that. I narrowed down the error further today and edited my post again–there are 5 chart types that seem to have an issue with a snippet of our code: Waterfall, Box & Whisker, Treemap, Sunburst, and Histogram. All other charts work fine. The code that appears to be causing an issue on these chart types specifically is:

foreach (var nSeries in chart.NSeries)
{
	nSeries.Name = nSeries.DisplayName;
}

This is a part of our flattening process for charts which should flatten all the data on the chart.

I have attached a zip of xlsx files that have the chart types in them.

AllBrokenChartTypes.zip (71.8 KB)

#6

@bvk,

I have tested your scenario using updated code in the first post, however no issue is observed while using all the files in AllBrokenChartTypes.zip. No exception is raised by Aspose.Cells while loading all the saved files after flattening process code snippet. Similarly when we open these file in Excel, charts are also displayed. Please try this scenario using latest version Aspose.Cells for .NET 19.2.x and if still issue is there, please provide us issue details along with the images showing the exact problem.

#7

@ahsaniqbalsidiqui Ahhhh, that specific issue was fixed sometime in the last few minor builds. I tried with 19.2.X and it worked fine Our core application was a few minor versions behind.

However after updating I did spot a different issue in our code with Histogram and Waterfall charts (in HistogramChart.xlsx and WaterfallChart.xlsx). Here is the repro code for where we are seeing an exception:

var fileName = "HistogramChart.xlsx";
var format = FileFormatUtil.DetectFileFormat(fileName);

var asposeLoadOptions = new LoadOptions(format.LoadFormat)
{
	LoadFilter = new LoadFilter(LoadDataFilterOptions.All)
};

using (var workbook = new Workbook(fileName, asposeLoadOptions))
{
	foreach (var workbookWorksheet in workbook.Worksheets)
	{
		foreach (var chart in workbookWorksheet.Charts)
		{
			chart.Calculate();

			foreach (var nSeries in chart.NSeries)
			{
				if (nSeries.DataLabels != null)
				{
					nSeries.DataLabels.IsAutoText = false;
				}
			}
		}
	}

	workbook.Save(fileName);
}

using (var workbook = new Workbook(fileName, asposeLoadOptions))
{
}

The exception we’re seeing:
Aspose.Cells.CellsException : Invalid ChartDLblPos string val

#8

@bvk,
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-46646 - Exception raised by opening the Excel file after updating the chart
#9

@bvk,

This is to inform you that we have fixed your issue (logged earlier as “CELLSNET-46646”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

#10

@ahsaniqbalsidiqui,

Thanks for the update! I’m looking forward to it :slight_smile:

#11

@bvk,

You are welcome.

#12

@bvk,

Please try our latest version/fix: Aspose.Cells for .NET v19.3.1:

Aspose.Cells19.3.1 For .NetStandard20.Zip (4.0 MB)
Aspose.Cells19.3.1 For .Net2_AuthenticodeSigned.Zip (4.8 MB)
Aspose.Cells19.3.1 For .Net4.0.Zip (4.8 MB)

Your issue should be fixed in it.

Let us know your feedback.

#13

@Amjad_Sahi,

That works great, thanks! Tested our chart flattening method against all chart types and it didn’t have an issue with any :slight_smile: