We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Stacked bar chart multilevel category axis label alignment issue

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.

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.

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.

Technology .NET.

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”);

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

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.
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.

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.