Stacked bar chart multilevel category axis label alignment issue

RaviPuvvadi · July 10, 2014, 12:17am

I have attached here sample chart built in Excel, horizontal axis, was aligned well and excess data use to come in next line, but using appose its not aligning text properly and always second and third, level label shows in single line and over lap each other default, if i just click to edit it and come out looks fine. but we want in default it should align good.

Error image attached please find attachment.

babar.raza · July 11, 2014, 12:35am

Hi Ravi,

First of all, please accept our apologies for a bit delayed response.

We have evaluated your presented scenario on our end while using the latest version of Aspose.Cells for .NET 8.1.1.1 by loading the provided spreadsheet in an instance of Workbook and saving it back to disc. Unfortunately, we are unable to spot the problem as shown in your snapshot, when the resultant spreadsheet (attached) is viewed with Excel 2007 & 2010.

In case you are working on a different platform (Java/Cloud/Android) then please mention the platform. Please also provide your exact source code lines as well as MS Excel version to reproduce the problem on our side.

Thank you for your cooperation.

RaviPuvvadi · July 11, 2014, 1:40am

the code used is as follows:

Workbook wb = new Workbook();

int chartRows = 40;

int chartCols =20;

int dataSheetIdx = wb.Worksheets.Add();

Worksheet dataSheet = wb.Worksheets[dataSheetIdx];

string sheetName = “ChartData”;

dataSheet.Name = sheetName;

dataSheet.Cells[0, 0].PutValue(@“X\Y”);

int dataSheetIndex = 1;

dataSheet.Cells[0, dataSheetIndex].PutValue(“Included in: Group 1, Group 2,Group 3”);

dataSheet.Cells[1, dataSheetIndex].PutValue(“Category 1”);

dataSheet.Cells[2, dataSheetIndex].PutValue(“Label 1”);

dataSheet.Cells[3, dataSheetIndex].PutValue(2310);

dataSheet.Cells[4, dataSheetIndex].PutValue(1508);

dataSheet.Cells[5, dataSheetIndex].PutValue(0);

dataSheet.Cells[6, dataSheetIndex].PutValue(3818);

dataSheetIndex++;

dataSheet.Cells[2, dataSheetIndex].PutValue(“Label 2”);

dataSheet.Cells[3, dataSheetIndex].PutValue(0);

dataSheet.Cells[4, dataSheetIndex].PutValue(0);

dataSheet.Cells[5, dataSheetIndex].PutValue(629);

dataSheet.Cells[6, dataSheetIndex].PutValue(629);

dataSheetIndex++;

dataSheet.Cells[1, dataSheetIndex].PutValue(“Category Category 2”);

dataSheet.Cells[2, dataSheetIndex].PutValue(“Label 3”);

dataSheet.Cells[3, dataSheetIndex].PutValue(0);

dataSheet.Cells[4, dataSheetIndex].PutValue(293);

dataSheet.Cells[5, dataSheetIndex].PutValue(0);

dataSheet.Cells[6, dataSheetIndex].PutValue(293);

dataSheetIndex++;

dataSheet.Cells[1, dataSheetIndex].PutValue(“Category Category 3”);

dataSheet.Cells[2, dataSheetIndex].PutValue(“Label 4”);

dataSheet.Cells[3, dataSheetIndex].PutValue(0);

dataSheet.Cells[4, dataSheetIndex].PutValue(233);

dataSheet.Cells[5, dataSheetIndex].PutValue(11);

dataSheet.Cells[6, dataSheetIndex].PutValue(244);

dataSheetIndex++;

dataSheet.Cells[0, dataSheetIndex].PutValue(“Included in: Group 1, Group 2”);

dataSheet.Cells[1, dataSheetIndex].PutValue(“All”);

dataSheet.Cells[2, dataSheetIndex].PutValue(“Label 5”);

dataSheet.Cells[3, dataSheetIndex].PutValue(84);

dataSheet.Cells[4, dataSheetIndex].PutValue(796);

dataSheet.Cells[5, dataSheetIndex].PutValue(0);

dataSheet.Cells[6, dataSheetIndex].PutValue(880);

dataSheetIndex++;

dataSheet.Cells[0, dataSheetIndex].PutValue(“Included in: Group 1”);

dataSheet.Cells[1, dataSheetIndex].PutValue(“Category 4”);

dataSheet.Cells[2, dataSheetIndex].PutValue(“Label 6”);

dataSheet.Cells[3, dataSheetIndex].PutValue(210);

dataSheet.Cells[4, dataSheetIndex].PutValue(38);

dataSheet.Cells[5, dataSheetIndex].PutValue(0);

dataSheet.Cells[6, dataSheetIndex].PutValue(248);

dataSheetIndex++;

dataSheet.Cells[2, dataSheetIndex].PutValue(“Label 7”);

dataSheet.Cells[3, dataSheetIndex].PutValue(26);

dataSheet.Cells[4, dataSheetIndex].PutValue(628);

dataSheet.Cells[5, dataSheetIndex].PutValue(0);

dataSheet.Cells[6, dataSheetIndex].PutValue(655);

dataSheetIndex++;

dataSheet.Cells[1, dataSheetIndex].PutValue(“Category 5”);

dataSheet.Cells[2, dataSheetIndex].PutValue(“Label 8”);

dataSheet.Cells[3, dataSheetIndex].PutValue(0);

dataSheet.Cells[4, dataSheetIndex].PutValue(545);

dataSheet.Cells[5, dataSheetIndex].PutValue(0);

dataSheet.Cells[6, dataSheetIndex].PutValue(545);

dataSheetIndex++;

dataSheet.Cells[2, dataSheetIndex].PutValue(“Label 9”);

dataSheet.Cells[3, dataSheetIndex].PutValue(“0”);

dataSheet.Cells[4, dataSheetIndex].PutValue(“55”);

dataSheet.Cells[5, dataSheetIndex].PutValue(“0”);

dataSheet.Cells[6, dataSheetIndex].PutValue(“55”);

dataSheetIndex++;

dataSheet.Cells[1, dataSheetIndex].PutValue(“Category 6”);

dataSheet.Cells[2, dataSheetIndex].PutValue(“Label 10”);

dataSheet.Cells[3, dataSheetIndex].PutValue(0);

dataSheet.Cells[4, dataSheetIndex].PutValue(727);

dataSheet.Cells[5, dataSheetIndex].PutValue(0);

dataSheet.Cells[6, dataSheetIndex].PutValue(727);

dataSheetIndex++;

dataSheet.Cells[2, dataSheetIndex].PutValue(“Label 11”);

dataSheet.Cells[3, dataSheetIndex].PutValue(0);

dataSheet.Cells[4, dataSheetIndex].PutValue(364);

dataSheet.Cells[5, dataSheetIndex].PutValue(0);

dataSheet.Cells[6, dataSheetIndex].PutValue(364);

dataSheetIndex++;

dataSheet.Cells[2, dataSheetIndex].PutValue(“Label 12”);

dataSheet.Cells[3, dataSheetIndex].PutValue(259);

dataSheet.Cells[4, dataSheetIndex].PutValue(461);

dataSheet.Cells[5, dataSheetIndex].PutValue(361);

dataSheet.Cells[6, dataSheetIndex].PutValue(1081);

dataSheet.Cells[3, 0].PutValue(“Series 1”);

dataSheet.Cells[4, 0].PutValue(“Series 2”);

dataSheet.Cells[5, 0].PutValue(“Series 3”);

//Formatting Chart Data

Aspose.Cells.Style style = new Aspose.Cells.Style();

Cells cells = dataSheet.Cells;

Range range = cells.CreateRange(0, 0, 1, 12);

StyleFlag flag = new StyleFlag();

flag.All = true;

style.Font.IsBold = true;

style.IsTextWrapped = true;

range.ApplyStyle(style, flag);

Aspose.Cells.Style style1 = new Aspose.Cells.Style();

style1.Font.Color = Color.FromArgb(99, 37, 35);

style1.IsTextWrapped = true;

cells[1, 0].SetStyle(style1);

cells[1, 1].SetStyle(style1);

cells[1, 2].SetStyle(style1);

cells[1, 3].SetStyle(style1);

cells[1, 4].SetStyle(style1);

cells[1, 5].SetStyle(style1);

cells[1, 6].SetStyle(style1);

cells[1, 7].SetStyle(style1);

cells[1, 8].SetStyle(style1);

cells[1, 9].SetStyle(style1);

cells[1, 10].SetStyle(style1);

cells[1, 11].SetStyle(style1);

cells[1, 12].SetStyle(style1);

Aspose.Cells.Style style2 = new Aspose.Cells.Style();

style2.Font.Color = Color.FromArgb(149, 55, 53);

style2.IsTextWrapped = true;

cells[2, 0].SetStyle(style2);

cells[2, 1].SetStyle(style2);

cells[2, 2].SetStyle(style2);

cells[2, 3].SetStyle(style2);

cells[2, 4].SetStyle(style2);

cells[2, 5].SetStyle(style2);

cells[2, 6].SetStyle(style2);

cells[2, 7].SetStyle(style2);

cells[2, 8].SetStyle(style2);

cells[2, 9].SetStyle(style2);

cells[2, 10].SetStyle(style2);

cells[2, 11].SetStyle(style2);

cells[2, 12].SetStyle(style2);

Aspose.Cells.Style style3 = new Aspose.Cells.Style();

style3.Font.Color = Color.FromArgb(217, 150, 148);

cells[3, 0].SetStyle(style3);

style3.Custom = “($* #,##0 “K”);($* (#,##0) “K”;($* “-”);(@)";

cells[3, 1].SetStyle(style3);

cells[3, 2].SetStyle(style3);

cells[3, 3].SetStyle(style3);

cells[3, 4].SetStyle(style3);

cells[3, 5].SetStyle(style3);

cells[3, 6].SetStyle(style3);

cells[3, 7].SetStyle(style3);

cells[3, 8].SetStyle(style3);

cells[3, 9].SetStyle(style3);

cells[3, 10].SetStyle(style3);

cells[3, 11].SetStyle(style3);

cells[3, 12].SetStyle(style3);

Aspose.Cells.Style style4 = new Aspose.Cells.Style();

style4.Font.Color = Color.FromArgb(217, 150, 148);

cells[4, 0].SetStyle(style3);

style4.Custom = "($* #,##0 “K”);($* (#,##0) “K”;($* “-”);(@)”;

cells[4, 1].SetStyle(style3);

cells[4, 2].SetStyle(style3);

cells[4, 3].SetStyle(style3);

cells[4, 4].SetStyle(style3);

cells[4, 5].SetStyle(style3);

cells[4, 6].SetStyle(style3);

cells[4, 7].SetStyle(style3);

cells[4, 8].SetStyle(style3);

cells[4, 9].SetStyle(style3);

cells[4, 10].SetStyle(style3);

cells[4, 11].SetStyle(style3);

cells[4, 12].SetStyle(style3);

Aspose.Cells.Style style5 = new Aspose.Cells.Style();

style3.Font.Color = Color.FromArgb(217, 150, 148);

cells[5, 0].SetStyle(style3);

style5.Custom = “($* #,##0 “K”);($* (#,##0) “K”;($* “-”);(@)";

cells[5, 1].SetStyle(style3);

cells[5, 2].SetStyle(style3);

cells[5, 3].SetStyle(style3);

cells[5, 4].SetStyle(style3);

cells[5, 5].SetStyle(style3);

cells[5, 6].SetStyle(style3);

cells[5, 7].SetStyle(style3);

cells[5, 8].SetStyle(style3);

cells[5, 9].SetStyle(style3);

cells[5, 10].SetStyle(style3);

cells[5, 11].SetStyle(style3);

cells[5, 12].SetStyle(style3);

Aspose.Cells.Style style6 = new Aspose.Cells.Style();

style3.Font.Color = Color.FromArgb(217, 150, 148);

cells[6, 0].SetStyle(style3);

style6.Custom = "($* #,##0 “K”);($* (#,##0) “K”;($* “-”);(@)”;

cells[6, 1].SetStyle(style3);

cells[6, 2].SetStyle(style3);

cells[6, 3].SetStyle(style3);

cells[6, 4].SetStyle(style3);

cells[6, 5].SetStyle(style3);

cells[6, 6].SetStyle(style3);

cells[6, 7].SetStyle(style3);

cells[6, 8].SetStyle(style3);

cells[6, 9].SetStyle(style3);

cells[6, 10].SetStyle(style3);

cells[6, 11].SetStyle(style3);

cells[6, 12].SetStyle(style3);

//Add a chart sheet

int chartSheetIdx = wb.Worksheets.Add(SheetType.Chart);

Worksheet chartSheet = wb.Worksheets[chartSheetIdx];

chartSheet.Name = “ChartSheet”;

//Add a chart in ChartSheet with data series from DataSheet

int chartIdx = chartSheet.Charts.Add(Aspose.Cells.Charts.ChartType.ColumnStacked, 0, 0, chartRows, chartCols);

Chart chart = chartSheet.Charts[chartIdx];

chart.SizeWithWindow = true;

for (int rows = 4; rows <= 7; rows++)

chart.NSeries.Add(sheetName + “!B” + rows.ToString() + “:M” + rows.ToString(), false);

Aspose.Cells.Charts.Legend legend = chart.Legend;

legend.Position = Aspose.Cells.Charts.LegendPositionType.Bottom;

chart.PlotArea.Area.Formatting = FormattingType.None;

chart.PlotArea.BackgroundMode = BackgroundMode.Transparent;

legend.Width = 400;

legend.Height = 18;

legend.Border.Style = LineType.Solid;

legend.TextFont.Size = 12;

chart.PlotArea.Border.IsVisible = false;

//Set a text

// chart.ValueAxis.DisplayUnit = DisplayUnitType.Thousands;

chart.ValueAxis.IsDisplayUnitLabelShown = false;

chart.ValueAxis.TickLabels.NumberFormat = “$###,### K”;

chart.SecondValueAxis.TickLabels.NumberFormat = “$###,### K”;

chart.PlotArea.Area.BackgroundColor = Color.White;

chart.NSeries[0].Area.ForegroundColor = ColorTranslator.FromHtml("#4f81bd");

chart.NSeries[1].Area.ForegroundColor = ColorTranslator.FromHtml("#c0504d");

chart.NSeries[2].Area.ForegroundColor = ColorTranslator.FromHtml("#9bbb59");

chart.NSeries[0].Points[1].Area.ForegroundColor = ColorTranslator.FromHtml("#4f81bd");

chart.NSeries[1].Points[1].Area.ForegroundColor = ColorTranslator.FromHtml("#c0504d");

chart.NSeries[2].Points[1].Area.ForegroundColor = ColorTranslator.FromHtml("#9bbb59");

chart.NSeries.CategoryData = “=‘ChartData’!$B$1:$M$3”;

chart.ValueAxis.MajorGridLines.Transparency = .60;

//Set properties of chart title

chart.Title.Text = “Test Chart”;

chart.Title.TextFont.Color = Color.Black;

chart.Title.TextFont.IsBold = true;

chart.Title.TextFont.Size = 12;

chart.Title.TextFont.Name = “Arial”;

chart.ValueAxis.TickLabels.Font.Name = “Arial”;

chart.CategoryAxis.TickLabels.Font.Name = “Arial”;

chart.PlotArea.TextFont.Name = “Arial”;

//Set properties of valueaxis title

Axis valueAxis = chart.ValueAxis;

valueAxis.TickLabels.Font.Size = 10;

valueAxis.TickLabels.Font.Name = “Arial”;

//Set properties of Axis(categoryaxis) title

Axis categoryAxis = chart.CategoryAxis;

categoryAxis.TickLabels.Font.Size = 10;

categoryAxis.TickLabels.Font.Name = “Arial”;

legend.Position = LegendPositionType.Right;

Aspose.Cells.Charts.Series ser1 = chart.NSeries[0];

Aspose.Cells.Charts.Series ser2 = chart.NSeries[1];

Aspose.Cells.Charts.Series ser3 = chart.NSeries[2];

Aspose.Cells.Charts.Series ser4 = chart.NSeries[3];

categoryAxis.TickLabelPosition = TickLabelPositionType.NextToAxis;

categoryAxis.CategoryType = CategoryType.AutomaticScale;

categoryAxis.MajorTickMark = TickMarkType.Outside;

categoryAxis.TickLabels.RotationAngle = 90;

categoryAxis.TickLabels.TextDirection = TextDirectionType.LeftToRight;

ser4.PlotOnSecondAxis = true;

ser4.Type = ChartType.Column;

ser1.GapWidth = 55;

ser2.GapWidth = 55;

ser3.GapWidth = 55;

ser4.GapWidth = 55;

ser4.LegendEntry.IsDeleted = true;

DataLabels datalabels;

for (int i = 3; i < chart.NSeries.Count; i++)

{

datalabels = chart.NSeries[i].DataLabels;

//Show the value in the DataLabels

datalabels.ShowValue = true;

datalabels.NumberFormat = “$###,### K”;

//Show the category name in the DataLabels

datalabels.ShowCategoryName = false;

//Not show the percentage in the DataLabels

datalabels.ShowPercentage = false;

//Not show the legend key.

datalabels.ShowLegendKey = false;

datalabels.Area.ForegroundColor = Color.White;

datalabels.Border.IsVisible = true;

datalabels.Border.Style = LineType.Solid;

datalabels.Border.Color = ColorTranslator.FromHtml("#4f81bd");

datalabels.Border.Weight = WeightType.SingleLine;

datalabels.Separator = DataLablesSeparatorType.Comma;

}

ser1.Name = “Series 1”;

ser2.Name = “Series 2”;

ser3.Name = “Series 3”;

ser4.DataLabels.Position = LabelPositionType.OutsideEnd;

ser4.Area.Formatting = FormattingType.None;

chart.SecondValueAxis.IsVisible = false;

chart.PlotArea.Border.IsVisible = false;

chart.PlotArea.Area.Formatting = FormattingType.None;

chart.PlotArea.BackgroundMode = BackgroundMode.Transparent;

chart.PlotArea.TextFont.Size = 10;

chart.PlotArea.TextFont.Name = “Arial”;

chart.PlotArea.Border.Transparency = 1.0;

// Set ChartSheet an active sheet

wb.Worksheets.ActiveSheetIndex = chartSheetIdx;

Once generated we add it to PPT,

please check and update me soon.

RaviPuvvadi · July 11, 2014, 2:04am

Technology .NET.

shakeel.faiz · July 11, 2014, 2:13am

Hi Ravi,

Thanks for your posting and using Aspose.Cells.

We have generated the Excel chart and its image using your code with the latest version and found the issues in the chart image. Excel chart itself is correct. However, there are category axis label alignment issues in the output chart image.

I have attached the output Excel file and chart image for a reference.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-42819.

I added the following two lines at the end of your code.

C#
chart.ToImage(“output.png”);

wb.Save(“output.xlsx”);

RaviPuvvadi · July 11, 2014, 2:31am

Hi,

Thanks for your support, we are using Aspose cells 5.2.2.0, once you fixed the bug please send me fix in 5.2.2.0 version of aspose cells.

Thanks & Regards

Ravi

babar.raza · July 11, 2014, 8:32am

Hi Ravi,

As per company policy, we have to provide the fix with the latest version of the API. Therefore we will not be able to honor your request of providing the fix with some previous version.

Thank you for your understanding.

amjad.sahi · September 28, 2014, 4:13am

Hi,

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

We have fixed the issue now. We fixed the multi-category labels overlap issues.

Let us know your feedback.

Thank you.

aspose.notifier · October 16, 2014, 6:08am

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

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