设置excel图表中从C＃API到数据集的最小/最大值的轴的下限和上限

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.

我正在使用C＃Excel API生成一些报告。但是，Excel会在数据集中的第一个轴点和最小值之间以及数据集中的最后一个轴点和最大值之间留下间隙。我的数据集按日期时间排序。如何强制excel将轴的下限和上限精确设置为我的数据集中的最小值和最大值，以便我看不到任何间隙？我可以通过在excel图表中手动设置最小/最大轴点来实现excel。

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?

但有没有办法让excel自动执行此操作，或者使用数据集从我的C＃应用程序设置最小/最大点数？

Example (marked gaps) 设置excel图表中从C＃API到数据集的最小/最大值的轴的下限和上限

示例（标记的间隙）

Hope it makes sense.

希望它有意义。

thanks

谢谢

1 个解决方案

#1

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

[编辑]好的，我做了一些演奏，我已经想出如何设置垂直和水平轴范围。这适用于Excel 2010。

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

在这里，我清除页面上的所有图表并创建一个新图表（_resultsSheet是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.

现在是另一个轴。请注意，我使用了固定时间。要将时间转换为轴刻度，只需使用十进制的24小时时间除以24。晚上9:30是21:30，这是21.5小时。不要忘记（双）施法，以防你使用int。

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;

#1