CODEWITHSUNDEEP
chartsspreadsheetgear
CoolBreeze
CoolBreeze

Reputation: 381

How do I Create a Horizontal Bar Chart in SpreadsheetGear with Names for the Y-Axis Labels?

Suppose I have a list of planet names and the distance of the planet from the sun in my spreadsheet (column 2 is in million of miles - closest distance per UniverseToday.com):

How do I create a horizontal bar chart with the planet names on the y-axis and horizontal bars showing the distance from the sun in SpreadsheetGear.

Upvotes: 0

Views: 252

Answers (1)

Tim Andersen
Tim Andersen

Reputation: 3184

You would need to create a chart of type ChartType.BarClustered to do this. A variety of API would be involved to accomplish this if you are trying to create such a chart from scratch. If you are looking for a complete sample as a demonstration, I've provided one at the bottom of this post.

Relevant API and documentation to look over might include the following (definitely look over some of these interfaces, as you'll find many more options to customize your chart in a variety of other ways not demonstrated here):

Update: Added some code to reverse the plot order of the series, which will match the row order of the source data.

Example:

using SpreadsheetGear;
using SpreadsheetGear.Charts;
...

// Setup a workbook with desired data.
IWorkbook workbook = Factory.GetWorkbook();
IWorksheet worksheet = workbook.ActiveWorksheet;
IRange cells = worksheet.Cells;
cells["A1:B6"].Value = new object[,] {
    { "Planet", "Distance from Sun\n(millions of miles)" },
    { "Mercury", 10 },
    { "Venus", 66 },
    { "Earth", 91 },
    { "Mars", 127 },
    { "Jupiter", 460 }};

// Create a chart object.
IChart chart = worksheet.Shapes.AddChart(125, 5, 400, 200).Chart;

// Set the source data to the above range.
chart.SetSourceData(cells["A1:B6"], RowCol.Columns);

// Specify chart type.
chart.ChartType = ChartType.BarClustered;

// Add a chart title.
chart.HasTitle = true;
chart.ChartTitle.Text = "Planet Distances from Sun";

// Remove legend.
chart.HasLegend = false;

// Set some options on the series, such as adding data labels and
// specifying the position of the data labels.
ISeries series = chart.SeriesCollection[0];
series.HasDataLabels = true;
series.DataLabels.Position = DataLabelPosition.OutsideEnd;

// Access the "value" axis (the X-Axis in this case) to set the
// title to indicate values are in millions.
IAxis valueAxis = chart.Axes[AxisType.Value];
valueAxis.HasTitle = true;
valueAxis.AxisTitle.Text = "(Millions of miles)";

// Access the "category" axis (the Y-Axis in this case).
IAxis catAxis = chart.Axes[AxisType.Category];
// Reverse the plot order (top-top-bottom instead of bottom-to-top).
catAxis.ReversePlotOrder = true;
// Note that enabling ReversePlotOrder on its own will also move your
// "value" axis labels to the top of the chart since this axis will by
// default cross at the first-plotted category item.  To fix this you 
// need to tell it to cross at the maximum category item, which would
// correspond to the bottom of the chart.
catAxis.Crosses = AxisCrosses.Maximum;

// Save workbook to file.
workbook.SaveAs(@"C:\temp\BarChart.xlsx", FileFormat.OpenXMLWorkbook);

Upvotes: 1

Related Questions