默认情况下,创建图表时,Excel会自动设置其坐标轴属性。这些属性包括坐标轴选项,例如边界最大值,边界最小值,主要单位,次要单位;刻度线标记的主要类型,次要类型;标签位置;横坐标轴交叉设置;是否逆序刻度值等。但是有时我们需要手动设置这些属性来完善和美化图表。本文将介绍如何使用Spire.XLS自定义设置Excel图表坐标轴格式。
C#
//创建Workbook对象,获取第一个工作表
Workbook workbook = new Workbook();
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
//添加示例数据
sheet.Name = "Demo";
sheet.Range["A1"].Value = "Month";
sheet.Range["A2"].Value = "Jan";
sheet.Range["A3"].Value = "Feb";
sheet.Range["A4"].Value = "Mar";
sheet.Range["A5"].Value = "Apr";
sheet.Range["A6"].Value = "May";
sheet.Range["A7"].Value = "Jun";
sheet.Range["A8"].Value = "Jul";
sheet.Range["A9"].Value = "Aug";
sheet.Range["B1"].Value = "Planned";
sheet.Range["B2"].NumberValue = 38;
sheet.Range["B3"].NumberValue = 47;
sheet.Range["B4"].NumberValue = 39;
sheet.Range["B5"].NumberValue = 36;
sheet.Range["B6"].NumberValue = 27;
sheet.Range["B7"].NumberValue = 25;
sheet.Range["B8"].NumberValue = 36;
sheet.Range["B9"].NumberValue = 48;
//根据示例数据创建柱形图
Chart chart = sheet.Charts.Add(ExcelChartType.ColumnClustered);
chart.DataRange = sheet.Range["B1:B9"];
chart.SeriesDataFromRange = false;
chart.PlotArea.Visible = false;
chart.TopRow = 6;
chart.BottomRow = 25;
chart.LeftColumn = 2;
chart.RightColumn = 9;
chart.ChartTitle = "Chart with Customized Axis";
chart.ChartTitleArea.IsBold = true;
chart.ChartTitleArea.Size = 12;
Spire.Xls.Charts.ChartSerie cs1 = chart.Series[0];
cs1.CategoryLabels = sheet.Range["A2:A9"];
//自定义设置图表坐标轴属性
chart.PrimaryValueAxis.MajorUnit = 8;
chart.PrimaryValueAxis.MinorUnit = 2;
chart.PrimaryValueAxis.MaxValue = 50;
chart.PrimaryValueAxis.MinValue = 0;
chart.PrimaryValueAxis.IsReverseOrder = false;
chart.PrimaryValueAxis.MajorTickMark = TickMarkType.TickMarkOutside;
chart.PrimaryValueAxis.MinorTickMark = TickMarkType.TickMarkInside;
chart.PrimaryValueAxis.TickLabelPosition = TickLabelPositionType.TickLabelPositionNextToAxis;
chart.PrimaryValueAxis.CrossesAt = 0;
//保存文档
workbook.SaveToFile("Result.xlsx", ExcelVersion.Version2010);
VB.NET
'创建Workbook对象,获取第一个工作表
Dim workbook As New Workbook()
workbook.CreateEmptySheets(1)
Dim sheet As Worksheet = workbook.Worksheets(0)
'添加示例数据
sheet.Name = "Demo"
sheet.Range("A1").Value = "Month"
sheet.Range("A2").Value = "Jan"
sheet.Range("A3").Value = "Feb"
sheet.Range("A4").Value = "Mar"
sheet.Range("A5").Value = "Apr"
sheet.Range("A6").Value = "May"
sheet.Range("A7").Value = "Jun"
sheet.Range("A8").Value = "Jul"
sheet.Range("A9").Value = "Aug"
sheet.Range("B1").Value = "Planned"
sheet.Range("B2").NumberValue = 38
sheet.Range("B3").NumberValue = 47
sheet.Range("B4").NumberValue = 39
sheet.Range("B5").NumberValue = 36
sheet.Range("B6").NumberValue = 27
sheet.Range("B7").NumberValue = 25
sheet.Range("B8").NumberValue = 36
sheet.Range("B9").NumberValue = 48
'根据示例数据创建柱形图
Dim chart As Chart = sheet.Charts.Add(ExcelChartType.ColumnClustered)
chart.DataRange = sheet.Range("B1:B9")
chart.SeriesDataFromRange = False
chart.PlotArea.Visible = False
chart.TopRow = 6
chart.BottomRow = 25
chart.LeftColumn = 2
chart.RightColumn = 9
chart.ChartTitle = "Chart with Customized Axis"
chart.ChartTitleArea.IsBold = True
chart.ChartTitleArea.Size = 12
Dim cs1 As Spire.Xls.Charts.ChartSerie = chart.Series(0)
cs1.CategoryLabels = sheet.Range("A2:A9")
'自定义设置图表坐标轴属性
chart.PrimaryValueAxis.MajorUnit = 8
chart.PrimaryValueAxis.MinorUnit = 2
chart.PrimaryValueAxis.MaxValue = 50
chart.PrimaryValueAxis.MinValue = 0
chart.PrimaryValueAxis.IsReverseOrder = False
chart.PrimaryValueAxis.MajorTickMark = TickMarkType.TickMarkOutside
chart.PrimaryValueAxis.MinorTickMark = TickMarkType.TickMarkInside
chart.PrimaryValueAxis.TickLabelPosition = TickLabelPositionType.TickLabelPositionNextToAxis
chart.PrimaryValueAxis.CrossesAt = 0
'保存文档
workbook.SaveToFile("Result.xlsx", ExcelVersion.Version2010)
截图: