CODEWITHSUNDEEP
c#excel
user236215
user236215

Reputation: 7546

set lower and upper bound of axes in excel chart from C# API to min/max values of data set

I am using C# Excel API to generate some reports. However, Excel leaves gaps between the first axis point and minimum value in data set, and between last axis point and maximum value in data set. My data set is sorted by datetime. How do I force excel to set the lower and upper bounds of axes precisely to minimum and maximum values in my data set so that I don't see any gaps? I can do that in excel by manually setting min/max axis points in excel chart.

But is there a way to have excel do this automatically, or otherwise set min/max points from my C# application using the data set?

Example (marked gaps) enter image description here

Hope it makes sense.

thanks

Upvotes: 4

Views: 13223

Answers (1)

thinkOfaNumber
thinkOfaNumber

Reputation: 2883

[Edited] OK, I did some playing, and I've figured out how to set the vertical and horizontal axis range. This is working with Excel 2010.

Here, I clear all charts on the page and create a new one (_resultsSheet is an Excel.Worksheet):

var resultCharts = (ChartObjects)_resultsSheet.ChartObjects();
foreach (ChartObject ch in resultCharts)
{
    ch.Delete();
}
ChartObject resultChart = resultCharts.Add(150, 40, 300, 200);
_resultChartPage = resultChart.Chart;

Now set up the source - I've just used a predefined range of fixed values. You could scan your source to find the actual min and max values:

_resultChartRange = _resultsSheet.get_Range("J5", "K15");
_resultChartPage.SetSourceData(_resultChartRange);
_resultChartPage.ChartType = Excel.XlChartType.xlXYScatterLines;
_resultChartPage.HasLegend = false;

Now for the vertical axis setup:

Axis vertAxis = (Axis)resultChart.Chart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary);
vertAxis.HasMajorGridlines = true; // change this to whatever you wish
vertAxis.HasTitle = true;
vertAxis.AxisTitle.Text = "up the side";
vertAxis.MaximumScaleIsAuto = false;
vertAxis.MaximumScale = 500; // you can pick this based on your input
vertAxis.MinimumScaleIsAuto = false;
vertAxis.MinimumScale = 5;

now for the other axis. Note here I've used fixed times. To convert a time to an axis scale, just use the 24 hour time in decimal, divided by 24. Eg. 9:30pm is 21:30 which is 21.5 hours. Don't forget the (double) cast just in case you use to ints.

Axis horizAxis = resultChart.Chart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
horizAxis.MaximumScaleIsAuto = false;
horizAxis.MaximumScale = (double)21.5 / 24; // 9:30 pm
horizAxis.MinimumScaleIsAuto = false;
horizAxis.MinimumScale = (double)13 / 24; // 1:00 pm
horizAxis.HasTitle = true;
horizAxis.AxisTitle.Text = "across the bottom";

and for those who "like to watch":

_resultsSheet.Activate();
_workBook.Application.Visible = true;

Upvotes: 4

Related Questions