C# Excel 为图表添加趋势线、误差线
Excel 图表能够将数据可视化，在图表中另行添加趋势线和误差线，可对数据进行进一步的数据分析和统计的可视化处理。 Excel 中的趋势线可用于趋势预测 / 回归分析，共 6 中类型：指数（ X ），线性（ L ），对数（ 0 ），多项式（ P ），幂（ W ），移动平均（ M ）。误差线可用于显示潜在的误差或相对于系列中每个数据标志的不确定程度。 Excel 中可设置误差线的显示方向：正负偏差，负偏差，正偏差；以及设置误差类型及误差量：固定值，百分比，标准偏差，标准误差，自定义类型。
本篇文章主要介绍，使用免费版的 在 C# 中独立创建 Excel 文档，生成折线图、柱状图，并添加趋势线和误差线。
需添加的命名空间
using Spire.Xls;
using
System.Drawing;
步骤详解：
步骤一：独立创建Excel文件和表单。
Workbook workbook = new Workbook();
workbook.CreateEmptySheets( 1 );
Worksheet sheet = workbook.Worksheets[
0
];
步骤二：为Excel单元格添加示例数据。
sheet.Name = " 误差线和趋势线演示 " ;
sheet.Range[ " A1 " ].Value = " 月份 " ;
sheet.Range[ " A2 " ].Value = " 一月 " ;
sheet.Range[ " A3 " ].Value = " 二月 " ;
sheet.Range[ " A4 " ].Value = " 三月 " ;
sheet.Range[ " A5 " ].Value = " 四月 " ;
sheet.Range[ " A6 " ].Value = " 五月 " ;
sheet.Range[ " A7 " ].Value = " 六月 " ;
sheet.Range[ " B1 " ].Value = " 计划量 " ;
sheet.Range[ " B2 " ].NumberValue = 3.3 ;
sheet.Range[ " B3 " ].NumberValue = 2.5 ;
sheet.Range[ " B4 " ].NumberValue = 2.0 ;
sheet.Range[ " B5 " ].NumberValue = 3.7 ;
sheet.Range[ " B6 " ].NumberValue = 4.5 ;
sheet.Range[ " B7 " ].NumberValue = 4.0 ;
sheet.Range[ " C1 " ].Value = " 实际量 " ;
sheet.Range[ " C2 " ].NumberValue = 3.8 ;
sheet.Range[ " C3 " ].NumberValue = 3.2 ;
sheet.Range[ " C4 " ].NumberValue = 1.7 ;
sheet.Range[ " C5 " ].NumberValue = 3.5 ;
sheet.Range[ " C6 " ].NumberValue = 4.5 ;
sheet.Range[ " C7 "].NumberValue =
4.3
;
步骤三：生成折线图，为其添加趋势线和误差线。
// 生成折线图，设置位置
Chart chart = sheet.Charts.Add(ExcelChartType.Line);
chart.DataRange = sheet.Range[ " B1:B7 " ];
chart.SeriesDataFromRange = false ;
chart.TopRow = 6 ;
chart.BottomRow = 25 ;
chart.LeftColumn = 2 ;
chart.RightColumn = 9 ;
chart.ChartTitle = " 百分比正偏差误差线和对数趋势线示例 " ;
chart.ChartTitleArea.IsBold = true ;
chart.ChartTitleArea.Size = 12 ;
Spire.Xls.Charts.ChartSerie cs1 = chart.Series[ 0 ];
cs1.CategoryLabels = sheet.Range[ " A2:A7 " ];
// 添加对数趋势线
cs1.TrendLines.Add(TrendLineType.Logarithmic);
cs1.ErrorBar( true
, ErrorBarIncludeType.Plus, ErrorBarType.Percentage,
10
);
步骤四：生成柱状图，并为其添加趋势线和误差线。
// 生成柱状图作为对照组
Chart chart2 = sheet.Charts.Add(ExcelChartType.ColumnClustered);
chart2.DataRange = sheet.Range[ " B1:C7 " ];
chart2.SeriesDataFromRange = false ;
chart2.TopRow = 6 ;
chart2.BottomRow = 25 ;
chart2.LeftColumn = 10 ;
chart2.RightColumn = 17 ;
chart2.ChartTitle = " 正负标准误差误差线和指数趋势线示例 " ;
chart2.ChartTitleArea.IsBold = true ;
chart2.ChartTitleArea.Size = 12 ;
Spire.Xls.Charts.ChartSerie cs2 = chart2.Series[ 0 ];
cs2.CategoryLabels = sheet.Range[ " A2:A7 " ];
// 添加标准误差负误差0.3的误差线
cs2.ErrorBar( true , ErrorBarIncludeType.Minus, ErrorBarType.StandardError, 0.3 );
// 添加指数趋势线
cs2.TrendLines.Add(TrendLineType.Exponential);
Spire.Xls.Charts.ChartSerie cs3 = chart2.Series[ 1 ];
// 添加标准误差正负误差0.5的误差线cs3.ErrorBar( true
, ErrorBarIncludeType.Both, ErrorBarType.StandardError,
0.5
);
步骤五：保存Excel文档，启动查看效果。
workbook.SaveToFile( " S3.xlsx " , ExcelVersion.Version2010);
System.Diagnostics.Process.Start( " S3.xlsx"
);
效果图：
纯图表：
完整代码：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Spire.Xls;
using System.Drawing;
namespace ConsoleApplication2
{
class Program
{
static void Main( string [] args)
{
Workbook workbook = new Workbook();
workbook.CreateEmptySheets( 1 );
Worksheet sheet = workbook.Worksheets[ 0 ];
sheet.Name = " 误差线和趋势线演示 " ;
sheet.Range[ " A1 " ].Value = " 月份 " ;
sheet.Range[ " A2 " ].Value = " 一月 " ;
sheet.Range[ " A3 " ].Value = " 二月 " ;
sheet.Range[ " A4 " ].Value = " 三月 " ;
sheet.Range[ " A5 " ].Value = " 四月 " ;
sheet.Range[ " A6 " ].Value = " 五月 " ;
sheet.Range[ " A7 " ].Value = " 六月 " ;
sheet.Range[ " B1 " ].Value = " 计划量 " ;
sheet.Range[ " B2 " ].NumberValue = 3.3 ;
sheet.Range[ " B3 " ].NumberValue = 2.5 ;
sheet.Range[ " B4 " ].NumberValue = 2.0 ;
sheet.Range[ " B5 " ].NumberValue = 3.7 ;
sheet.Range[ " B6 " ].NumberValue = 4.5 ;
sheet.Range[ " B7 " ].NumberValue = 4.0 ;
sheet.Range[ " C1 " ].Value = " 实际量 " ;
sheet.Range[ " C2 " ].NumberValue = 3.8 ;
sheet.Range[ " C3 " ].NumberValue = 3.2 ;
sheet.Range[ " C4 " ].NumberValue = 1.7 ;
sheet.Range[ " C5 " ].NumberValue = 3.5 ;
sheet.Range[ " C6 " ].NumberValue = 4.5 ;
sheet.Range[ " C7 " ].NumberValue = 4.3 ;
Chart chart = sheet.Charts.Add(ExcelChartType.Line);
chart.DataRange = sheet.Range[ " B1:B7 " ];
chart.SeriesDataFromRange = false ;
chart.TopRow = 6 ;
chart.BottomRow = 25 ;
chart.LeftColumn = 2 ;
chart.RightColumn = 9 ;
chart.ChartTitle = " 百分比正偏差误差线和对数趋势线示例 " ;
chart.ChartTitleArea.IsBold = true ;
chart.ChartTitleArea.Size = 12 ;
Spire.Xls.Charts.ChartSerie cs1 = chart.Series[ 0 ];
cs1.CategoryLabels = sheet.Range[ " A2:A7 " ];
cs1.TrendLines.Add(TrendLineType.Logarithmic);
cs1.ErrorBar( true , ErrorBarIncludeType.Plus, ErrorBarType.Percentage, 10 );
Chart chart2 = sheet.Charts.Add(ExcelChartType.ColumnClustered);
chart2.DataRange = sheet.Range[ " B1:C7 " ];
chart2.SeriesDataFromRange = false ;
chart2.TopRow = 6 ;
chart2.BottomRow = 25 ;
chart2.LeftColumn = 10 ;
chart2.RightColumn = 17 ;
chart2.ChartTitle = " 正负标准误差误差线和指数趋势线示例 " ;
chart2.ChartTitleArea.IsBold = true ;
chart2.ChartTitleArea.Size = 12 ;
Spire.Xls.Charts.ChartSerie cs2 = chart2.Series[ 0 ];
cs2.CategoryLabels = sheet.Range[ " A2:A7 " ];
cs2.ErrorBar( true , ErrorBarIncludeType.Minus, ErrorBarType.StandardError, 0.3 );
cs2.TrendLines.Add(TrendLineType.Exponential);
Spire.Xls.Charts.ChartSerie cs3 = chart2.Series[ 1 ];
cs3.ErrorBar( true , ErrorBarIncludeType.Both, ErrorBarType.StandardError, 0.5 );
workbook.SaveToFile( " S3.xlsx " , ExcelVersion.Version2010);
System.Diagnostics.Process.Start( " S3.xlsx " );
}
}
}
正文到此结束
热门推荐
相关文章
近期评论
-
文章和留言都翻到11页了 没有OOM
-
-
朋友，翻页到11页，及以后，会出现OOM，无法访问
-
-
搞个gitee的项目
-
-
版本号是多少，你可以下载哪个代码仓库，jdk选1.8 直接跑就行
-
-
-
Loading...
![[HBLOG]公众号](https://www.liuhaihua.cn/img/qrcode_gzh.jpg)
