C# Excel 为图表添加模拟运算表

  Excel中的图表能够将数据可视化，方便我们比较分析数据。但也有一定的局限，例如：不能够直接从图表中读出原来数据的准确值。Excel提供的解决方案是，在图表下方添加一个模拟运算表，即在坐标轴下方添加包含建表时所依照的数据的表格。本文旨在介绍通过免费的Free Spire.XLS在C#独立创建Excel文件，添加数据，生成图表，并添加模拟运算表。

需要使用的命名空间：

using Spire.Xls; using System.Drawing;

 

步骤详解：

 

步骤1：创建Excel文件，添加表单。

            Workbook workbook =  new Workbook();             workbook.CreateEmptySheets( 1);             Worksheet sheet = workbook.Worksheets[ 0];

 

步骤二：为单元格添加样本数据，用于一下不生成图表。

 

            sheet.Name =  " Demo ";             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;             sheet.Range[ " D1 "].Value =  " 小白 ";             sheet.Range[ " D2 "].NumberValue =  3.0;             sheet.Range[ " D3 "].NumberValue =  2.8;             sheet.Range[ " D4 "].NumberValue =  3.5;             sheet.Range[ " D5 "].NumberValue =  2.3;             sheet.Range[ " D6 "].NumberValue =  3.3;             sheet.Range[ " D7 "].NumberValue =  3.8;

步骤三：利用步骤二填充的数据，生成柱形图，设置其在Excel中的位置，图表名称等。

            Chart chart = sheet.Charts.Add(ExcelChartType.Column3DClustered);             chart.DataRange = sheet.Range[ " B1:D7 "];             chart.SeriesDataFromRange =  false;             chart.TopRow =  7;             chart.BottomRow =  28;             chart.LeftColumn =  3;             chart.RightColumn = 11;             chart.ChartTitle =  " 带模拟数据表的柱状图示例 ";             chart.ChartTitleArea.IsBold =  true;             chart.ChartTitleArea.Size =  12;             Spire.Xls.Charts.ChartSerie cs1 = chart.Series[ 0];                   cs1.CategoryLabels = sheet.Range[ " A2:A7 "];  

步骤四：为柱状图添加模拟数据表。

           chart.HasDataTable =  true;

 

步骤五：保存文档为2010版Excel。

           workbook.SaveToFile( " S3.xlsx ", ExcelVersion.Version2010);

 

代码运行后效果截图：

 

 

 完整代码：

 

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 =  " Demo ";             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;             sheet.Range[ " D1 "].Value =  " 小白 ";             sheet.Range[ " D2 "].NumberValue =  3.0;             sheet.Range[ " D3 "].NumberValue =  2.8;             sheet.Range[ " D4 "].NumberValue =  3.5;             sheet.Range[ " D5 "].NumberValue =  2.3;             sheet.Range[ " D6 "].NumberValue =  3.3;             sheet.Range[ " D7 "].NumberValue =  3.8;                          Chart chart = sheet.Charts.Add(ExcelChartType.Column3DClustered);             chart.DataRange = sheet.Range[ " B1:D7 "];             chart.SeriesDataFromRange =  false;             chart.TopRow =  7;             chart.BottomRow =  28;             chart.LeftColumn =  3;             chart.RightColumn = 11;             chart.ChartTitle =  " 带模拟数据表的柱状图示例 ";             chart.ChartTitleArea.IsBold =  true;             chart.ChartTitleArea.Size =  12;             Spire.Xls.Charts.ChartSerie cs1 = chart.Series[ 0];                   cs1.CategoryLabels = sheet.Range[ " A2:A7 "];             chart.HasDataTable =  true;                      workbook.SaveToFile( " S3.xlsx ", ExcelVersion.Version2010);             System.Diagnostics.Process.Start( " S3.xlsx ");         }     } }

 

 

 

 

转载于:https://www.cnblogs.com/Yesi/p/4798127.html

相关资源：C#开发实战1200例（第一卷 第二卷） 源码下载地址.txt