C#开发Excel生成统计图

想看更多的C#操作Excel开发报表的文章,点击这里

C#调用Excel来生成统计图（Chart）的以及Chart图中各部分控制的一些方法

添加引用：在“com选项卡”中选择“misrosoft office 12.0 object library”

先添加Excel的引用
using Microsoft.Office.Interop.Excel;
//定义所要使用的Excel对象
    Application ThisApplication = null;
    Workbooks m_objBooks = null;
    _Workbook ThisWorkbook = null;
    Worksheet xlSheet = null;

    /// <summary>
    /// 删除多余的Sheet
    /// </summary>
    private void DeleteSheet()
    {
        foreach (Worksheet ws in ThisWorkbook.Worksheets)
            if (ws != ThisApplication.ActiveSheet)
            {
                ws.Delete();
            }
        foreach (Chart cht in ThisWorkbook.Charts)
            cht.Delete();
    }

    /// <summary>
    /// 创建一个Sheet，用来存数据
    /// </summary>
    private void AddDatasheet()
    {
        xlSheet = (Worksheet)ThisWorkbook.
            Worksheets.Add(Type.Missing, ThisWorkbook.ActiveSheet,
            Type.Missing, Type.Missing);

        xlSheet.Name = "数据";
    }

    /// <summary>
    /// 用生成的随机数作数据,实际情况数据可以从数据库中取出
    /// </summary>
    private void LoadData()
    {
        Random ran = new Random();
        for (int i = 1; i <= 12; i++)
        {
            xlSheet.Cells[i, 1] = i.ToString() + "月";
            xlSheet.Cells[i, 2] = ran.Next(2000).ToString();
        }
    }

    /// <summary>
    /// 创建统计图        
    /// </summary>
    private void CreateChart()
    {
        // TODO: 生成一个统计图对象：
        Chart xlChart = (Chart)ThisWorkbook.Charts.
            Add(Type.Missing, xlSheet, Type.Missing, Type.Missing);

        // TODO: 设定数据来源
        Range cellRange = (Range)xlSheet.Cells[1, 1];

        // TODO: 通过向导生成Chart
        xlChart.ChartWizard(cellRange.CurrentRegion,
            XlChartType.xl3DColumn, Type.Missing,
            XlRowCol.xlColumns, 1, 0, true,
            "访问量比较", "月份", "访问量",
            "");

        // TODO: 设置统计图Sheet的名称
        xlChart.Name = "统计";

        // TODO: 让12个Bar都显示不同的颜色
        ChartGroup grp = (ChartGroup)xlChart.ChartGroups(1);
        grp.GapWidth = 20;
        grp.VaryByCategories = true;

        // TODO: 让Chart的条目的显示形状变成圆柱形，并给它们显示加上数据标签
        Series s = (Series)grp.SeriesCollection(1);
        s.BarShape = XlBarShape.xlCylinder;
        s.HasDataLabels = true;

        // TODO: 设置统计图的标题和图例的显示
        xlChart.Legend.Position = XlLegendPosition.xlLegendPositionTop;
        xlChart.ChartTitle.Font.Size = 24;
        xlChart.ChartTitle.Shadow = true;
        xlChart.ChartTitle.Border.LineStyle = XlLineStyle.xlContinuous;

        // TODO: 设置两个轴的属性，Excel.XlAxisType.xlValue对应的是Y轴，Excel.XlAxisType.xlCategory对应的是X轴
        Axis valueAxis = (Axis)xlChart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary);
        valueAxis.AxisTitle.Orientation = -90;

        Axis categoryAxis = (Axis)xlChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
        categoryAxis.AxisTitle.Font.Name = "MS UI Gothic";
    }

最后,生成Excel
    protected void btnExcel_Click(object sender, EventArgs e)
    {
        string sPath = Server.MapPath("~/Files/TestExcel1.xlsx");
        try
        {
            ThisApplication = new Application();
            m_objBooks = (Workbooks)ThisApplication.Workbooks;
            ThisWorkbook = (_Workbook)(m_objBooks.Add(Type.Missing));

            ThisApplication.DisplayAlerts = false;

            DeleteSheet();
            AddDatasheet();
            LoadData();
            CreateChart();

            ThisWorkbook.SaveAs(sPath, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing);

        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
        finally
        {
            ThisWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
            ThisApplication.Workbooks.Close();

            ThisApplication.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(ThisWorkbook);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(ThisApplication);
            ThisWorkbook = null;
            ThisApplication = null;
            GC.Collect();
        }
    }

 

引用自:http://www.cnblogs.com/dahuzizyd/archive/2006/12/20/Excel_Chart_3.html

另：http://www.cnblogs.com/ziyiFly/archive/2008/09/24/1297841.html

http://www.51aspx.com/S/chart.html

OpenFlashChart的用法：

http://www.cnblogs.com/yaunion/archive/2009/03/12/1409321.html