C#Excel為圖表添加趨勢線、誤差線

2019-11-14 14:01:28

字體：大中小

來源：轉載

供稿：網友

Excel圖表能夠將數據可視化，在圖表中另行添加趨勢線和誤差線，可對數據進行進一步的數據分析和統計的可視化處理。Excel中的趨勢線可用于趨勢預測/回歸分析，共6中類型：指數（X），線性（L），對數（0），多項式（P），冪（W），移動平均（M）。誤差線可用于顯示潛在的誤差或相對于系列中每個數據標志的不確定程度。Excel中可設置誤差線的顯示方向：正負偏差，負偏差，正偏差；以及設置誤差類型及誤差量：固定值，百分比，標準偏差，標準誤差，自定義類型。

本篇文章主要介紹，使用免費版的Free Spire.XLS在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);
            //添加10%正偏差誤差線
            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");

        }
    }
}

上一篇：Unity封裝dll教程整理

下一篇：VisualStudio2013內置SQLServer入門（二）--增刪改查