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

 

一 如何用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 = 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.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)

      {

             }

 }

 

  • 0
    点赞
  • 4
    收藏
  • 3
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值