Legend Captions & User defined bar colors for every individual bars

[patrickjjs]

Hello Team,

We are using Aspose Cells for Charting. We have given the series to Aspose Cells.
Based on the series we need user defined individual color for every bar in that series. And also the legend caption should not be the series vales(Category data). Instead we need user defined Series captions.

We try out with following code.
--------------------------------
Here lstSourceValues, chartColor, are Arraylist
ChartElement object contains name & value(attributes).

chart.NSeries.Add(“C” + (headerRow + 1).ToString() + “:C” + ii.ToString(), true);
// Chart Labels
chart.NSeries.CategoryData = “B” + (headerRow + 1).ToString() + “:B” + ii.ToString();
chart.NSeries.IsColorVaried = true;

for (int k = 0; k < chart.NSeries.Count; k++)
{
System.Drawing.Color elementColor = new System.Drawing.Color();
elementColor = (System.Drawing.Color)chartColor[k];
ChartElement ceSource = (ChartElement)lstSourceValues[k];
chart.NSeries[k].Name = ceSource.Name;
}

---------------------------------------

Please advise us.

[amjad.sahi]

Hi,

Thanks for your inquiry.

If you need to apply custom colors to each bars in the chart, you need to add these colors to the MS Excel standard color palette first. Then, you will set the color of each data point in the series. And, if you want the legend entries/captions should be user specific, you need to set the category data for the data series to your desired custom string array.

I have created a sample example for your reference. Please check the following sample code for your reference. In the example I add three custom colors to the palette, specify each datapoint in the series to a color. The legend entries depends upon the category data custom string labels:

Sample code:

Workbook workbook = new Workbook();
workbook.ChangePalette(Color.Orange, 53);
workbook.ChangePalette(Color.LightBlue, 54);
workbook.ChangePalette(Color.LightCoral, 55);

Color [] colors = workbook.Colors;
//Set default font
Style style = workbook.DefaultStyle;
style.Font.Name = "Tahoma";
workbook.DefaultStyle = style;
Cells cells = workbook.Worksheets[0].Cells;

//Put a string into a cell
cells["A1"].PutValue("Region");
cells["A2"].PutValue("France");
cells["A3"].PutValue("Germany");
cells["A4"].PutValue("England");

cells["B1"].PutValue("Marketing Costs");
cells["B2"].PutValue(70000);
cells["B3"].PutValue(55000);
cells["B4"].PutValue(30000);

Worksheet sheet = workbook.Worksheets[0];
//Set the name of the worksheet
sheet.Name = "Clustered Column";
sheet.IsGridlinesVisible = false;

//Create chart
int chartIndex = sheet.Charts.Add(ChartType.Column, 5, 1, 29, 10);
Chart chart = sheet.Charts[chartIndex];
//Add the nseries collection to a chart
chart.NSeries.Add("B2:B4", true);
//Get or set the range of category axis values
//chart.NSeries.CategoryData = "A2:A4";
chart.NSeries.CategoryData = "{\"Fra\",\"Ger\",\"Eng\"}";
chart.NSeries.IsColorVaried = true;


for (int i = 0; i < chart.NSeries[0].Points.Count; i++)
{
chart.NSeries[0].Points[i].Area.ForegroundColor = colors[53 + i];
}

//Set properties of chart title
chart.Title.Text = "Marketing Costs by Region";
chart.Title.TextFont.IsBold = true;
chart.Title.TextFont.Color = Color.Black;
chart.Title.TextFont.Size = 12;

//Set properties of categoryaxis title
chart.CategoryAxis.Title.Text = "Region";
chart.CategoryAxis.Title.TextFont.Color = Color.Black;
chart.CategoryAxis.TickLabels.AutoScaleFont = true;
chart.CategoryAxis.TickLabels.Rotation = 40;
chart.CategoryAxis.TickLabels.Font.Size = 7;
chart.CategoryAxis.Title.TextFont.IsBold = true;
chart.CategoryAxis.Title.TextFont.Size = 10;

//Set properties of valueaxis title
chart.ValueAxis.Title.Text = "In Thousands";
chart.ValueAxis.Title.TextFont.Name = "Arial";
chart.ValueAxis.Title.TextFont.Color = Color.Black;
chart.ValueAxis.Title.TextFont.IsBold = true;
chart.ValueAxis.Title.TextFont.Size = 10;
chart.ValueAxis.Title.Rotation = 90;
chart.ValueAxis.MajorUnit = double.Parse("20000");
chart.ValueAxis.MaxValue = double.Parse("80000");
chart.ValueAxis.MinorUnit = double.Parse("5000");
chart.ValueAxis.MinValue = double.Parse("0");

workbook.Save("f:\\test\\output_columnchart.xls");

And for your further reference, please check the doc that explians about MS Excel color palette to apply custom colors to different objects:

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/colors-and-palette.html

Thank you.

[patrickjjs]

Thank you for your Great & Quick work.

It worked for me.