Free Support Forum - aspose.com

Format stacked column charts bars

Hi,

I need to create a chart as per the attached template. Please help me. I am specifically looking for the formatting of chart. I was able to generate the chart by adding NSeries and CategoryData properties. But I was unable to achieve the same formatting.

Data sheet and Chart Corelation: The bars are visible only for column C. The bars are transparent for colum B. Column A is category data whereas for Column D only data labels are shown.

Requirement is: In data sheet every product data is differentiated
using a different color row. The same behavior is required in case of
chart. i.e. The bars visible on the chart for column C should be of different colors. Their color should match with the color of every product in data sheet.

I am using designer template for the purpose. So I can set chart formatting as per the template and can access the chart using sheet.Chart object.

I need to set the bars formatting dynamically as this varies with the data.

Thanks for the help in advance,
Deepali





Hi Deepali,

Thanks for sharing your template file containing your desired chart.

Aspose.Cells for .NET allows you to modify your designer charts as per your requirements. I have modified your template file’s chart in the “MWell $ft Vs Interval by Well " sheet and saved it as Chart1 in the attached “Aspose+Template2.xls” template file. Please see the following sample code that will help you how you can set the colors of the data labels of “D” series according to Series “C” bar colors. I think you can refer to my sample code and create your own code for your need. The output file is also attached containing your desired chart. The chart may not be 100% identical but the code will help you and you can add/amend my code accordingly.

Sample code:

Workbook workbook = new Workbook();
workbook.Open(“f:\test\Aspose+Template2.xls”);
Cells cells = workbook.Worksheets[“DATA”].Cells;
Worksheet csheet = workbook.Worksheets[“Chart1”];
Chart chart = csheet.Charts[0];
NSeries nseries = chart.NSeries;
MessageBox.Show(nseries.Count.ToString());

nseries.Add(”=DATA!F3:F60", true);

ASeries aseries;
chart.NSeries.CategoryData = “DATA!A3:A60”;
MessageBox.Show(nseries.Count.ToString());
aseries = nseries[0];
aseries.Name = cells[0, 1].Value.ToString();
aseries = nseries[1];
aseries.Name = cells[0, 3].Value.ToString();
aseries = nseries[2];
aseries.Name = cells[0, 5].Value.ToString();
aseries.DataLabels.IsValueShown = true;
aseries.DataLabels.TextFont.Name = “Britannic Bold”;
aseries.DataLabels.TextFont.Size = 10;
ChartPoints pdatapoints = chart.NSeries[1].Points;
ChartPoints datapoints = chart.NSeries[2].Points;
for (int i = 0; i < datapoints.Count; i++)
{

datapoints[i].DataLabels.TextFont.Color = pdatapoints[i].Area.ForegroundColor;



}


chart.CategoryAxis.TickLabelSpacing = 4;
chart.CategoryAxis.TickMarkSpacing = 1;
chart.CategoryAxis.TickLabels.Rotation = 59;

chart.ValueAxis.MinValue = 0;
chart.ValueAxis.MaxValue = 11000;
chart.ValueAxis.MinorUnit = 2000;
chart.ValueAxis.MinorUnit = 400;
chart.ValueAxis.CrossAt = 11000;
chart.ValueAxis.Crosses = CrossType.Maximum;

workbook.Save(“f:\test\1outputtemplatechart_2.xls”);

Thank you.

Thanks for the response. But there is some gap in understanding.
My foremost requirement is to set the bars area foreground color same as respective category data value color in data sheet.
Then I need to set the Datalabels colors similar bar area foreground color.

You have given the code to set the color of Datalabels but nowhere mentioned how to set the foreground color of bars.

Please refer the jpeg attachment for clarification.

I have also attached the template that I have created with the below code

Charts[0].NSeries[0].Values = “=DATA!$B$3:$B$39”;

Charts[0].NSeries[1].Values
= “=DATA!$D$3:$D$39”;

Charts[0].NSeries[2].Values = “=DATA!$F$3:$F$39”;

Charts[0].NSeries.CategoryData = “=DATA!$A$3:$A$39”;

Charts[0].Legend.Position = LegendPositionType.Top;

ChartPoints barPoints = Charts[0].NSeries[1].Points;

//// In this case I am getting only one point. I don’t know why. I should get all the points related to Product 1

for (int pointIndex = 0; pointIndex < barPoints.Count; pointIndex++)

{

barPoints[pointIndex].Area.ForegroundColor = WellColors[pointIndex];

//// WellColors is the array where I have stored all the colors written in data sheet

}

//// Show Data label for the third series only

DataLabels dataLabels = Charts[0].NSeries[2].DataLabels;

dataLabels.IsValueShown = true;

dataLabels.TextFont.Color = Color.Red;

dataLabels.TextFont.Name = "Britannic Bold";

dataLabels.TextFont.Size = 10;



Hi,

Thanks for providing the template excel file.

Yes, we found it iterates all the 37 data points but only set foreground color for the first data point in the second series after an initial test. We will look into it and get back to you soon.

Thank you.

Hi,

Thank you for considering Aspose.

Please try the attached latest version of Aspose.Cells for .NET. We have fixed your mentioned issue regarding setting of foreground color of bars.

Thank You & Best Regards,

Hi Aslam,

I have tried with the latest version. Result is same. You mentioned in your post you are getting 37 points but in my case I am getting only one datapoint from the below code. i.e. barPoints.Count is coming 1.

ChartPoints barPoints = Charts[0].NSeries[1].Points;

////
In this case I am getting only one point. I don’t know why. I should get all the points related to Product 1

for (int pointIndex = 0; pointIndex < barPoints.Count; pointIndex++)

{

barPoints[pointIndex].Area.ForegroundColor = WellColors[pointIndex];

//// WellColors is the array where I have stored all the colors written in data sheet

}

And i need to set different colors in bars as per the value in WellColors array. I need to set the bars or datapoints of same product of 1 color. Please refer the explaination.jpg attached in the earlier post.

Thanks,

Deepali.

Hi Deepali,

Well, I tested using your template file using the code below and it indeed returns 37 data points. I have tried to set all 37 data points to some background color and it works fine.

Sample code:

Workbook workbook = new Workbook();
workbook.Open(“f:\test\Generated+File.xls”);
Worksheet csheet = workbook.Worksheets[“Chart”];
Chart chart = csheet.Charts[0];
NSeries nseries = chart.NSeries;
MessageBox.Show(nseries.Count.ToString()); //3 series
ASeries aseries;
ChartPoints barPoints = chart.NSeries[1].Points;
MessageBox.Show(barPoints.Count.ToString()); // 37 data points

for (int pointIndex = 0; pointIndex < barPoints.Count; pointIndex++)
{


barPoints[1].Area.ForegroundColor = Color.Blue; // it sets all the data points to blue color fine.

}


Could you create a simple console application using the latest fix we posted, zip it and post it here to simulate the issue, we will check it soon.

Thank you.

Hi Amjad,

Thanks for the reply. Please find the attached console application. I re-tested again and it returns 1 datapoint only.


Thanks,
Deepali

Hi Deepali,

We found the issue and will fix it soon.


Thank you.


Hi,

Please try the attached version, We have fixed this issue, if you update the data source of the series, ASeries.Points will be updated too.

Thank you.

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


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