如何用C＃在Excel中生成图表

来源：互联网发布：淘宝ifashion怎么进去编辑：程序博客网时间：2024/06/05 10:42

一　如何用C＃在Excel中生成图表C#源代码程序

exc.Charts.Add(oMissing,oMissing,1,oMissing);

exc.ActiveChart.ChartType=Excel.XlChartType.xlColumnClustered;

exc.ActiveChart.SetSourceData(worksheet.get_Range("A1","B10"),Excel.XlRowCol.xlColumns);

exc.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject,"sheet1");

exc.ActiveChart.HasTitle=true;

exc.ActiveChart.ChartTitle.Text="chatname";

exc.ActiveChart.HasDataTable=false;

二　另一个完整的

/// <summary>

///标识绘制图表类型的枚举类

/// </summary>

public enum ChartType : int

{

/// <summary>

/// 无图表类型

/// </summary>

NoneChartType = 0,

/// <summary>

/// “饼状图表”类型

/// </summary>

PieChartType = 1,

/// <summary>

/// “柱状图表”类型

/// </summary>

ColumnChartType = 2,

/// <summary>

/// “线状图表”类型

/// </summary>

LineChartType = 3

}

/// <summary>

/// 导出GridView控件中的数据，并在Excel文件中生成图表

/// </summary>

public class ExportExcelAction

{

/// <summary>

/// 私有变量，待绘制图表对象的集合

/// </summary>

private List<DataChartObject> _DataChartObjectList;

/// <summary>

/// 私有变量，输出控制

/// </summary>

private System.Web.HttpResponse _Response;

/// <summary>

/// 私有变量，用于存储生成的临时文件的完整路径

/// </summary>

private string FileName;

/// <summary>

/// 私有变量，用于存储生成的Excel文件的文件信息

/// </summary>

private FileInfo _FileInfo;

/// <summary>

/// 私有变量，系统服务工具类对象

/// </summary>

private HttpServerUtility _HttpServerUtility;

/// <summary>

///私有变量，记录当前向Excel文件写数据写到了第几数据行 ,默认：第一行

/// </summary>

private int _CurrentRowIndex = 1;

/// <summary>

/// 私有变量，用于表述生成图表的左边起始位置（单元格编号）

/// </summary>

private int _StartColumnIndex = 2;

/// <summary>

/// 私有变量，记录绘图区域跨行的行数

/// </summary>

private int _ChartAreaRowSpan = 20;

/// <summary>

/// 私有变量，记录电子表格与图表下端的距离行数

/// </summary>

private int _TableAreaRowSpan = 3;

/// <summary>

/// 私有变量，设置绘图区域的宽度

/// </summary>

private double _ChartAreaWidth = 450;

/// <summary>

/// 私有变量，获取或设置绘图区域的高度

/// </summary>

private object _ChartAreaHeigth

{

get

{

this._Range = this._Worksheet.get_Range(this._Worksheet.Cells[this._CurrentRowIndex, 1],this._Worksheet.Cells[this._CurrentRowIndex + this._ChartAreaRowSpan - 1, 1]);

this._Range.RowHeight = this._FooterRowHeigth;

return this._Range.Height;

}

/// <summary>

/// 私有变量，记录生成Excel文件的单元格的宽度

/// </summary>

private double _ColumnsWidth;

/// <summary>

/// 公有变量，记录生成Excel文件的单元格宽度

/// </summary>

public double ColumnsWidth

{

get

{

return this._ColumnsWidth;

}

set

{

this._ColumnsWidth = value;

}

/// <summary>

/// 私有变量，记录生成Excel表格时，标题栏目的行高

/// </summary>

private double _HeaderRowHeigth;

/// <summary>

/// 公有变量，记录生成Excel表格时，标题栏目的行高

/// </summary>

public double HeaderRowHeigth

{

get

{

return this._HeaderRowHeigth;

}

set

{

this._HeaderRowHeigth = value;

}

/// <summary>

/// 私有变量，记录生成Excel表格时，内容栏目的行高

/// </summary>

private double _ContentRowHeigth;

/// <summary>

/// 公有变量，记录生成Excel表格时，内容栏目的行高

/// </summary>

public double ContentRowHeigth

{

get

{

return this._ContentRowHeigth;

}

set

{

this._ContentRowHeigth = value;

}

/// <summary>

/// 私有变量，设置普通单元格的行高

/// </summary>

private double _FooterRowHeigth;

/// <summary>

/// 公有变量，设置普通单元格的行高

/// </summary>

public double FooterRowHeigth

{

get

{

return this._FooterRowHeigth;

}

set

{

this._FooterRowHeigth = value;

}

/// <summary>

/// 私有变量，记录生成Excel表格时，表格标题栏目的文字的大小

/// </summary>

private int _HeaderTitleFontSize;

/// <summary>

/// 公有变量，记录生成Excel表格时，表格标题栏目的文字的大小

/// </summary>

public int HeaderTitleFontSize

{

get

{

return this._HeaderTitleFontSize;

}

set

{

this._HeaderTitleFontSize = value;

}

/// <summary>

/// 私有变量，记录生成Excel表格时，表格内容的文字的大小

/// </summary>

private int _ContentTextFontSize;

/// <summary>

/// 公有变量，记录生成Excel表格时，表格内容的文字的大小

/// </summary>

public int ContentTextFontSize

{

get

{

return this._ContentTextFontSize;

}

set

{

this._ContentTextFontSize = value;

}

/// <summary>

/// 私有变量，用以记录该绘图区域距左边距的距离，数据类型为Object类型

/// </summary>

private object _ToLeftValue

{

get

{

return this._Worksheet.get_Range(this._Worksheet.Cells[1, 1],this._Worksheet.Cells[1, this._StartColumnIndex - 1]).Width;

}

/// <summary>

/// 私有变量，用以巨鹿该绘图区域距顶边距的距离，数据类型为Object类型

/// </summary>

private object _ToTopValue

{

get

{

return this._Worksheet.get_Range(this._Worksheet.Cells[1, 1],this._Worksheet.Cells[this._CurrentRowIndex - 1, 1]).Height;

}

/// <summary>

/// 私有变量，生成Excel文件的应用服务对象

/// </summary>

private Application _Application;

/// <summary>

/// 私有变量，生成Excel文件时，Excel文件的工作簿

/// </summary>

private Workbook _Workbook;

/// <summary>

/// 私有变量，生成Excel文件时，Excel文件的工作簿中的时间表

/// </summary>

private Worksheet _Worksheet;

/// <summary>

/// 私有变量，Excel文件的单元格序列

/// </summary>

private Range _Range;

/// <summary>

/// 私有变量，用于标识绘制图表的区域

/// </summary>

private Shape _Shape;

/// <summary>

/// 私有变量，用于标识绘制的图表对象

/// </summary>

private Chart _Chart;

/// <summary>

/// 私有变量，用于表述图表单元序列

/// </summary>

private Series _Series;

/// <summary>

/// 私有变量，用于表述图表的坐标系轴

/// </summary>

private Axis _Axis;

/// <summary>

/// 私有变量，用于表述图表单元点

/// </summary>

private Point _p;

/// <summary>

/// 构造函数，构造参数为待绘制图表对象的集合

/// </summary>

/// <param name="myDataChartObjectList">构造参数：待绘制图表对象的集合</param>

public ExportExcelAction(List<DataChartObject> myDataChartObjectList,System.Web.HttpResponse myResponse,System.Web.HttpServerUtility myHttpServerUtility)

{

this._DataChartObjectList = myDataChartObjectList;

this._Response = myResponse;

this._HttpServerUtility = myHttpServerUtility;

this.FileName = this._HttpServerUtility.MapPath("/")+DateTime.Now.ToEnCodeLongString() + ".xls";

}

/// <summary>

/// 绘制图表并将其导出到本地文件

/// </summary>

public void ExportAction()

{

//[新建Excel文件生成程序]

this._Application = new Application();

//[向实例对象中插入一个工作簿]

this._Application.Workbooks.Add(true);

//[从Excel文件中提取一个工作簿]

this._Workbook = this._Application.Workbooks[1];

//[从Excel文件中提取活动的工作表]

this._Worksheet = this._Workbook.ActiveSheet as Worksheet;

//[设置生成Excel文件后不进行预览操作]

this._Application.Visible = false;

//[开始向Excel文件中写入数据]

this._DataChartObjectList.ForEach(e =>

{

//[获取数据源]

DataSet ds = e.DataSetName;

//[添加电子表格的标题]

this._Range = this._Worksheet.get_Range(this._Worksheet.Cells[this._CurrentRowIndex, this._StartColumnIndex],this._Worksheet.Cells[this._CurrentRowIndex, 5]);

this._Range.ColumnWidth = this.ColumnsWidth;

this._Range.RowHeight = this._HeaderRowHeigth;

this._Range.Merge(null);

this._Worksheet.Cells[this._CurrentRowIndex, 2] = e.TitleName;

//[设置单元中的文字字体为“华文仿宋”]

this._Range.Font.Name = "华文仿宋";

//[设置单元格中的文字大小为12磅]

this._Range.Font.Size = this._HeaderTitleFontSize;

//[设置单元格中的文字为加粗状态]

this._Range.Font.Bold = true;

this._CurrentRowIndex++;

//[创建“电子表格”的“标题”]

for (int i = 0; i < ds.Tables[0].Columns.Count; i++)

{

this._Worksheet.Cells[this._CurrentRowIndex, i + this._StartColumnIndex]= ds.Tables[0].Columns[i].ColumnName;

}

//[设置选中单元格序列的样式：标题单元格序列]

this._Range = this._Worksheet.get_Range(this._Worksheet.Cells[this._CurrentRowIndex, this._StartColumnIndex],this._Worksheet.Cells[this._CurrentRowIndex, ds.Tables[0].Columns.Count +this._StartColumnIndex - 1]);

//[选中全部的单元格序列]

this._Range.Select();

//[设置文字在单元格中水平居中]

this._Range.HorizontalAlignment = XlHAlign.xlHAlignCenter;

//[设置文字在单元格中垂直居中]

this._Range.HorizontalAlignment = XlVAlign.xlVAlignCenter;

//[设置单元格的宽度：为20毫米]

this._Range.ColumnWidth = 20;

//[设置单元格的行高：]

this._Range.RowHeight = this._HeaderRowHeigth;

//[设置单元中的文字字体为“华文仿宋”]

this._Range.Font.Name = "华文仿宋";

//[设置单元格中的文字大小为12磅]

this._Range.Font.Size = this._HeaderTitleFontSize;

//[设置单元格中的文字为加粗状态]

this._Range.Font.Bold = true;

//[设置单元格的背景颜色]

this._Range.Borders.Value = 1;

//[设置该单元格内部的颜色]

this._Range.Interior.Color =System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(58, 196, 201));

//[数据行递增一行]

this._CurrentRowIndex++;

//[向“电子表格”中写入数据]

for (int i = 0; i < ds.Tables[0].Rows.Count; i++)

{

for (int j = 0; j < ds.Tables[0].Columns.Count; j++)

{

this._Worksheet.Cells[this._CurrentRowIndex, j + this._StartColumnIndex]= ds.Tables[0].Rows[i][j].ToString();

}

//[设置选中单元格序列的样式：内容单元格序列]

//[选中全部的单元格序列]

this._Range.Select();

//[设置单元格的宽度]

this._Range.ColumnWidth = this._ColumnsWidth;

//[设置单元格的行高]

this._Range.RowHeight = this._ContentRowHeigth;

//[设置单元格内文字的字体]

this._Range.Font.Name = "新宋体";

//[设置单元格内文字的大小]

this._Range.Font.Size = this._ContentTextFontSize;

//[设置单元格内文字的水平方位]

this._Range.HorizontalAlignment = XlHAlign.xlHAlignLeft;

//[设置单元格内文字的垂直方位]

this._Range.HorizontalAlignment = XlVAlign.xlVAlignCenter;

//[设置单元格的边框]

this._Range.Borders.Value = 1;

this._CurrentRowIndex++;

}

//[设置图表与表格之间的间隙]

this._CurrentRowIndex++;

this._Range = this._Worksheet.get_Range(this._Worksheet.Cells[this._CurrentRowIndex - 1, this._StartColumnIndex],this._Worksheet.Cells[this._CurrentRowIndex - 1,e.DataSetName.Tables[0].Columns.Count+ this._StartColumnIndex - 1]);

this._Range.RowHeight = this._FooterRowHeigth;

//[绘制“电子表格”对应的“图表”]

switch (e.ChartTypeName)

{

case ChartType.NoneChartType:

{

break;

}

case ChartType.PieChartType:

{

this.DrawPieChartAction(e);

break;

}

case ChartType.ColumnChartType:

{

this.DrawColumnChartAction(e);

break;

}

case ChartType.LineChartType:

{

this.DrawLineChartAction(e);

break;

}

default:

{

break;

}

//[设置该“电子表格”或者“图表”与下一个“电子表格”或者“图表”之间的空隙]

this._CurrentRowIndex += this._TableAreaRowSpan;

this._Range = this._Worksheet.get_Range(this._Worksheet.Cells[this._CurrentRowIndex - this._TableAreaRowSpan - 1, 1],this._Worksheet.Cells[this._CurrentRowIndex, 1]);

this._Range.RowHeight = this._FooterRowHeigth;

});

//[将生成的Excel文件保存到指定的目录中]

this._Workbook.SaveCopyAs(this.FileName);

//[将文件导入到文件流中]

this._FileInfo = File.Exists(this.FileName) ? new FileInfo(this.FileName) : null;

//[关闭工作簿]

this._Workbook.Close(false, null, null);

//[关闭Excel生成应用程序]

this._Application.Quit();

//[清理Com端口]

System.Runtime.InteropServices.Marshal.ReleaseComObject(this._Axis);

//[清理Com端口]

System.Runtime.InteropServices.Marshal.ReleaseComObject(this._p);

//[清理Com端口]

System.Runtime.InteropServices.Marshal.ReleaseComObject(this._Series);

//[清理Com端口]

System.Runtime.InteropServices.Marshal.ReleaseComObject(this._Range);

//[清理Com端口]

System.Runtime.InteropServices.Marshal.ReleaseComObject(this._Chart);

//[清理Com端口]

System.Runtime.InteropServices.Marshal.ReleaseComObject(this._Shape);

//[清理Com端口]

System.Runtime.InteropServices.Marshal.ReleaseComObject(this._Worksheet);

//[清理Com端口]

System.Runtime.InteropServices.Marshal.ReleaseComObject(this._Workbook);

//[清理Com端口]

System.Runtime.InteropServices.Marshal.ReleaseComObject(this._Application);

//[将文件从“服务端”导出到“客户端”]

this._Response.Clear();

//[设置写入流的字符编码方式为GB2312]

this._Response.Charset = "GB2312";

//[设置写入流的文字编码格式：UTF8]

this._Response.ContentEncoding = System.Text.Encoding.UTF8;

// 添加头信息，为"文件下载/另存为"对话框指定默认文件名

this._Response.AddHeader("Content-Disposition","attachment;filename=" + this._HttpServerUtility.UrlEncode(this._FileInfo.Name));

// 添加头信息，指定文件大小，让浏览器能够显示下载进度

this._Response.AddHeader("Content-Length", this._FileInfo.Length.ToString());

// 指定返回的是一个不能被客户端读取的流，必须被下载

this._Response.ContentType = "application/ms-excel";

// 把文件流发送到客户端

this._Response.WriteFile(this._FileInfo.FullName);

// 停止页面的执行

this._Response.End();

//[关闭文件流]

File.Delete(this.FileName);

}

/// <summary>

/// 根据“数据源”绘制“饼状图”

/// </summary>

/// <param name="ds">数据源</param>

private void DrawPieChartAction(DataChartObject myDataChartObject)

{

//[获取绘制饼状图的数据源]

DataSet ds = myDataChartObject.DataSetName;

//[获取数据源所持有数据行的行数]

int RowsCount = ds.Tables[0].Rows.Count;

//[获取数据源所持有数据列的列数]

int ColumnsCount = ds.Tables[0].Columns.Count;

//[获取绘图区域]

this._Shape = this._Worksheet.Shapes.AddChart(XlChartType.xl3DPie,this._ToLeftValue,this._ToTopValue,this._ChartAreaWidth,this._ChartAreaHeigth);

//[获取绘图对象]

this._Chart = this._Shape.Chart;

//[获取绘制图表的数据来源]

this._Range = this._Worksheet.get_Range(this._Worksheet.Cells[this._CurrentRowIndex - RowsCount - 1,this._StartColumnIndex + myDataChartObject.StartIndex],this._Worksheet.Cells[this._CurrentRowIndex - RowsCount - 1,myDataChartObject.StopIndex + this._StartColumnIndex]);

this._Chart.SetSourceData(this._Range, XlRowCol.xlColumns);

this._Chart.SetSourceData(this._Range, XlRowCol.xlRows);

//[更改图例文字]

this._Series = this._Chart.SeriesCollection(1) as Series;

this._Series.Name = myDataChartObject.TitleName;

//[获取图例上的文字数组]

Array myArray = this._Series.XValues as Array;

//[遍历数据源，对“图例文字”和“数据模块”进行装饰]

for (int i = myDataChartObject.StartIndex; i <= myDataChartObject.StopIndex; i++)

{

//[设置饼图每个数据块]

if (Int32.Parse(ds.Tables[0].Rows[0][i].ToString()) != 0)

{

this._p = this._Series.Points(i - myDataChartObject.StartIndex + 1) as Point;

this._p.HasDataLabel = true;

this._p.DataLabel.Text =

ds.Tables[0].Columns[i].ColumnName + "：" + ds.Tables[0].Rows[0][i].ToString();

}

//[设置每个“图例标签”]

myArray.SetValue(ds.Tables[0].Columns[i].ColumnName,i - myDataChartObject.StartIndex + 1);

}

//[将图例返回Chart对象序列]

this._Series.XValues = myArray;

//[记录当前的索引行数递增20行]

this._CurrentRowIndex += this._ChartAreaRowSpan;

//[设置生成的Excel图表的水平旋转角度，零度]

this._Chart.Rotation = 40;

//[设置生成的柱状图禁止选择]

this._Chart.ProtectSelection = false;

//[设置生成的柱状图禁止修改数据点]

this._Chart.ProtectGoalSeek = false;

//[防止修改序列公式]

this._Chart.ProtectData = false;

//[防止修改格式设置]

this._Chart.ProtectFormatting = false;

}

/// <summary>

/// 根据“数据源”绘制“柱状图”

/// </summary>

/// <param name="ds">数据源</param>

private void DrawColumnChartAction(DataChartObject myDataChartObject)

{

//[获取绘制柱状图的“数据源”]

DataSet ds = myDataChartObject.DataSetName;

//[记录该“数据源”含有数据的“行数”]

int RowsCount = ds.Tables[0].Rows.Count;

//[记录该“数据源”含有数据的“列数”]

int ColumnsCount = ds.Tables[0].Columns.Count;

//[获取绘制图表的“画板区域”]

this._Shape = this._Worksheet.Shapes.AddChart(XlChartType.xl3DColumn,this._ToLeftValue, _ToTopValue,this._ChartAreaWidth,this._ChartAreaHeigth);

//[获取绘制图表的“绘画控件”]

this._Chart = this._Shape.Chart;

//[获取绘制图表的数据来源]

this._Range = this._Worksheet.get_Range(this._Worksheet.Cells[this._CurrentRowIndex - RowsCount - 1,this._StartColumnIndex + myDataChartObject.StartIndex],this._Worksheet.Cells[this._CurrentRowIndex - RowsCount - 1,this._StartColumnIndex + myDataChartObject.StopIndex]);

this._Chart.SetSourceData(this._Range, XlRowCol.xlRows);

this._Chart.SetSourceData(this._Range, XlRowCol.xlColumns);

//[设置生成的Excel图表的X轴旋转角度，270]

this._Chart.Rotation = 270;

//[设置生成的Excel图表的Y轴旋转角度，10]

this._Chart.Elevation = 10;

//[设置生成的Excel图表的透视角度]

this._Chart.Perspective = 30;

//[设置生成的柱状图禁止选择]

this._Chart.ProtectSelection = false;

//[设置生成的柱状图禁止修改数据点]

this._Chart.ProtectGoalSeek = false;

//[防止修改序列公式]

this._Chart.ProtectData = false;

//[防止修改格式设置]

this._Chart.ProtectFormatting = false;

//[记录当前的索引行数递增20行]

this._CurrentRowIndex += this._ChartAreaRowSpan;

for (int i = myDataChartObject.StartIndex; i <= myDataChartObject.StopIndex; i++)

{

//[设置每个标签]

this._Series =this._Chart.SeriesCollection(i - myDataChartObject.StartIndex + 1) as Series;

this._Series.Name = ds.Tables[0].Columns[i].ColumnName;

this._Series.MarkerStyle = XlMarkerStyle.xlMarkerStylePicture;

//[设置每个柱状图]

this._p = this._Series.Points(1) as Point;

this._p.HasDataLabel = true;

this._p.DataLabel.Text = ds.Tables[0].Rows[0][i].ToString();

}

//[设置Y值轴]

this._Axis = this._Chart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary) as Axis;

this._Axis.HasTitle = true;

this._Axis.AxisTitle.Text = myDataChartObject.YTitleName;

this._Axis.HasDisplayUnitLabel = true;

//[设置X类别轴]

this._Axis = this._Chart.Axes(XlAxisType.xlSeriesAxis, XlAxisGroup.xlPrimary) as Axis;

this._Axis.HasTitle = true;

this._Axis.AxisTitle.Text = myDataChartObject.XTitleName;

}

/// <summary>

/// 根据“数据源”绘制“线状图”

/// </summary>

/// <param name="ds">数据源</param>

private void DrawLineChartAction(DataChartObject myDataChartObject)

{

}