Chart value axis format and color change

Aspose.Cells Product Family
1

Hi Aspose Support team,

I have have the requirement like create the chart using the pivot table.

1. Earlier i have used the Aspose version 5.0.2.5. That time i created the chart and the value axis format set properly then i have sent few issues (by fourms) to fix. After that i have got the version 7.0.2.2. Now if i used this version, the value axis format is not set properly. For your kind reference i have attached the excel files(Before.xlsx and After.xlsx). I have used the following code to create the chart through pivot table.

obj.ExcelWorkBook.Worksheets["Summary"].Charts.Add(ChartType.BarStacked, 2, 0, 25, 10);
obj.ExcelWorkBook.Worksheets["Summary"].Charts[0].PivotSource = "Summary!PivotTable";
obj.ExcelWorkBook.Worksheets["Summary"].Charts[0].ChartArea.Area.ForegroundColor = Color.White;
obj.ExcelWorkBook.Worksheets["Summary"].Charts[0].PlotArea.Area.ForegroundColor = Color.White;
obj.ExcelWorkBook.Worksheets["Summary"].Charts[0].Title.Text = "History";
obj.ExcelWorkBook.Worksheets["Summary"].Charts[0].ShowLegend = false;
obj.ExcelWorkBook.Worksheets["Summary"].Charts[0].ValueAxis.TickLabels.Rotation = 45;
obj.ExcelWorkBook.Worksheets["Summary"].Charts[0].ValueAxis.TickLabels.Font.IsBold = false;
obj.ExcelWorkBook.Worksheets["Summary"].Charts[0].ValueAxis.TickLabelPosition = TickLabelPositionType.NextToAxis;
obj.ExcelWorkBook.Worksheets["Summary"].Charts[0].ValueAxis.MajorTickMark = TickMarkType.Outside;
obj.ExcelWorkBook.Worksheets["Summary"].Charts[0].ValueAxis.MinorTickMark = TickMarkType.None;

obj.ExcelWorkBook.Worksheets["Summary"].Charts[0].CategoryAxis.IsPlotOrderReversed = true;
obj.ExcelWorkBook.Worksheets["Summary"].Charts[0].CategoryAxis.HasMultiLevelLabels = false;
obj.ExcelWorkBook.Worksheets["Summary"].Charts[0].CategoryAxis.MajorTickMark = TickMarkType.Inside;
obj.ExcelWorkBook.Worksheets["Summary"].Charts[0].CategoryAxis.MinorTickMark = TickMarkType.None;
obj.ExcelWorkBook.Worksheets["Summary"].Charts[0].CategoryAxis.TickLabelPosition = TickLabelPositionType.NextToAxis;

2.I need to change the color of the each bar in the stacked bar chart(i am not using NSeries Properties. Series is coming through the Pivot table). In the attahced excel file, i need to change the blue color bar. How to achieve this feature. I have used the above code to create the chart?

Could you please help me how to achieve these features? and provide the sample code.

Awaiting for your response.


Regards,

Saravanan

2

Hi,


1) As an initial test, I can find the issue using your template file as a data source (pivot table) and with your sample code. The value axis and category axis does not render properly. We need to investigate it further as I am still not sure if it is an issue or something else, we will do it soon. I have logged a ticket with an id: CELLSNET-40087.

2) I think still this feature (manipulating pivot charts) is not supported. We will look into it and get back to you soon.

Thank you.
3

Hi,

Please download and try the new fix: Aspose.Cells for .NET v7.0.3.0

a) We have fixed the issue of axis number format.

b) If you want to set the format of series, please call Chart.RefreshPivotData method, please check the following code:

C#


ExcelWorkBook.Worksheets[“Summary”].Charts.Add(ChartType.BarStacked, 2, 0, 25, 10);

ExcelWorkBook.Worksheets[“Summary”].Charts[0].PivotSource = “Summary!PivotTable”;

ExcelWorkBook.Worksheets[“Summary”].Charts[0].RefreshPivotData();

ExcelWorkBook.Worksheets[“Summary”].Charts[0].NSeries[0].Area.ForegroundColor = Color.Blue;

ExcelWorkBook.Worksheets[“Summary”].Charts[0].NSeries[1].Area.ForegroundColor = Color.Yellow;



This method is only valid for the empty chart, it will remove all old setting of serieses. We are still working on this method.

4

Hi Aspose support team,

Thanks for your updation. I used RefreshPivotData method for the color change in the series of the chart and also working fine. But color is not changed in the below scenario. Please execute the attached sample project (Please put the source data ChartPlotArea1.xlsx in the C drive ) and do the following steps in the excel output.

Steps: 1.Goto “Port Summary” sheet( Now no pivot and no chart available)
2.In the Port Name Report filter Change the name from Antwerp to All

Now chart series 0 is appearing in the Blue color. But i set the color is White throuth the code(Line no 461).

Please help me to solve the issue in the particular scenario.

Thanks
Saravanan

5

Hi Support team,

Is there any update regarding this fourm?

Thanking you.

Regards,

Saravanan

6

Hi,

If there is no data in the data area of the pivot table , MS Excel will process the chart as an empty chart without any series.

So any setting of series is useless.

For example: If you create a pivot chart in MS excel and format a series’ background color, then select the filter to make no data in the data area,then save the file, close MS Excel, opening the file in MS Excel, then select the filter to show some data in the data area, the color are lost too.

We could not solve this issue. It’s the limitation of MS Excel.