Excel生成chart

浏览:2390

Excel生成chart

2009-04-10 10:04 来自 xihongshibeibei

以下C#代码可以直接生成Excel，我根据一个VB的写法进行了修改，直接生成带有图表的Excel,VB的网址为：http://www.beansoftware.com/ASP.NET-Tutorials/Generating-Excel-Reports.aspx

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.Vbe.Interop;using System.Runtime.InteropServices;
using System.IO;
using Microsoft.Office;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        try
        {

            Excel._Workbook ThisWorkbook = null;

            //下面是数据sheet

　　　　Excel.Worksheet xlSheet = null;
            ThisWorkbook = new Excel.Application().Workbooks.Add(Type.Missing);
            ThisWorkbook.Application.Visible = true;
            xlSheet = (Excel.Worksheet)ThisWorkbook.ActiveSheet;
            xlSheet.Name = "数据";

　　　　//这里用了个数据集加载数据，可以自己改成sql语句
            DataSet1TableAdapters.DataTable1TableAdapter adapter = new DataSet1TableAdapters.DataTable1TableAdapter();

            DataSet1 ds = new DataSet1();
            adapter.Fill(ds.DataTable1);
            System.Data.DataTable dt = ds.Tables[0];

            for (int i = 0; i < dt.Rows.Count; i++)
            {

　　　　　　//这里的datatable是一个有12列的table，有4个类型，类型1名称，月份1，金额1以此类推.....可　　　　以根据自己需要修改
                if (Int32.Parse(dt.Rows[i][1].ToString()) == 0)
                {
                    for (int j = 1; j < dt.Columns.Count; )
                    {
                        if (Int32.Parse(dt.Rows[i][j].ToString()) != 0)//如果没有出现不休要的数据，可以不要这个
                        {

                            xlSheet.Cells[i + 1, 1] = dt.Rows[i][j].ToString() + "月";//这里是chart横坐标

                            xlSheet.Cells[i + 1, 2] = dt.Rows[i][2].ToString();

　　　　　　　　　　//下面这些是将在一个月中同时出现的多个列，如果只有1列就用不用上面的东西了
                            xlSheet.Cells[i + 1, 3] = dt.Rows[i][5].ToString();
                            xlSheet.Cells[i + 1, 4] = dt.Rows[i][8].ToString();
                            xlSheet.Cells[i + 1, 5] = dt.Rows[i][11].ToString();
                          

                            break;
                        }
                        else
                        {
                            j += 3;//因为取的列不同，所以是隔三个取得

                        }
                    }
                }
                else
                {
                    xlSheet.Cells[i + 1, 1] = dt.Rows[i][1].ToString() + "月";
                    xlSheet.Cells[i + 1, 2] = dt.Rows[i][2].ToString();
                    xlSheet.Cells[i + 1, 3] = dt.Rows[i][5].ToString();
                    xlSheet.Cells[i + 1, 4] = dt.Rows[i][8].ToString();
                    xlSheet.Cells[i + 1, 5] = dt.Rows[i][11].ToString();
                   

                }

            }

　　　　//用向导生成图形

            Excel.Chart xlChart = (Excel.Chart)ThisWorkbook.Charts.Add(Type.Missing, xlSheet, Type.Missing, Type.Missing);
            Excel.Range cellRange = (Excel.Range)xlSheet.Cells[1, 1];
            xlChart.SetSourceData(cellRange, 2);
            xlChart.ChartWizard(cellRange.CurrentRegion,

                    Excel.XlChartType.xl3DColumn, Type.Missing,

                    Excel.XlRowCol.xlColumns, 1, 0, true,

                    "统计", "月份", "金额",

                    "");

 

            xlChart.Name = "统计";

            Excel.ChartGroup grp = (Excel.ChartGroup)xlChart.ChartGroups(1);

            grp.GapWidth = 20;

            grp.VaryByCategories = true;

            Excel.Series s = (Excel.Series)grp.SeriesCollection(1);

　　　　//这个是每个月里不同列
            Excel.Series s2 = (Excel.Series)grp.SeriesCollection(2);
            Excel.Series s3 = (Excel.Series)grp.SeriesCollection(3);
            Excel.Series s4 = (Excel.Series)grp.SeriesCollection(4);

            s.BarShape = Excel.XlBarShape.xlCylinder;
            s.Name = "系列1";
            s2.Name = "2";
            s3.Name = "3";
            s4.Name = "4";

           //图形上面有数据的值

            s.HasDataLabels = true;
            s2.HasDataLabels = true;
            s3.HasDataLabels = true;
            s4.HasDataLabels = true;

　　　　//图例的位置

            xlChart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionTop;
            xlChart.HasLegend = true;

 

            xlChart.ChartTitle.Font.Size = 24;

 

            xlChart.ChartTitle.Shadow = true;

            xlChart.ChartTitle.Border.LineStyle = Excel.XlLineStyle.xlContinuous;

          

 

            //最后设置两个轴的属性，Excel.XlAxisType.xlValue对应的是Y轴，Excel.XlAxisType.xlCategory对应的是X轴：

            Excel.Axis valueAxis = (Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);

            valueAxis.AxisTitle.Orientation = -90;

 

            Excel.Axis categoryAxis = (Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);

            categoryAxis.AxisTitle.Font.Name = "MS UI Gothic";
        }
        catch (Exception ex)
        {
            throw ex;
        }
         
    }
}