注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

天边云E家 shaoruisky

IT博文共享,知识的海洋

 
 
 

日志

 
 

Excel生成chart  

2015-02-11 08:55:21|  分类: C# |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
以下C#代码可以直接生成Excel

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;
        }
          
    }
}


  评论这张
 
阅读(276)| 评论(1)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017