在ASP.NET中使用Office Web Components (OWC)创建统计图
图形和图表是Web上数据表现的很好的形式，在ASP.NET，可以使用Office Web Components (OWC)来创建统计图。Office Web Component (OWC)是包含在Microsoft Office 2000中的一套组件，利用这些组件，我们可以很方便地在浏览器中或者传统的编程环境中进行数据分析和报表。比如：电子报表，图表，数据透视表等。
要在浏览器中显示图表，可以按下面的步骤进行：
- 从数据库中读取要生成图表的数据；
- 创建OWC图表；
- 添加必要的数据系列；
- 为个数据列赋数据；
- 定义外观；
- 创建GIF图形；
- 用IMG标记显示图形。
要生成图表的数据称为数据原，Chart Component组件支持的数据源有：实现IDataSource接口的任何数据源；ADO Recordset对象；XML文件；数组或者一定格式的文本字符串。在ASP中，我们可以用ADO Recordset对象；在.NET的ADO.NET中，由于ADO.NET没有实现IDataSource，.NET也没有提供ADO.NET DataSet对象向ADO Recordset对象的直接转换，如果你有一个 DataSet对象，你要么转换成XML文件，要么生成特殊格式的字符串才可以使用。下面就是本例子的结果：
aspx:
<%@ Page language="c#" Codebehind="owcsr.aspx.cs" AutoEventWireup="false" Inherits="AAS2.finance.owcsr" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" ><HTML> <HEAD> <title>owcsr</title> <meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR"> <meta content="C#" name="CODE_LANGUAGE"> <meta content="JavaScript" name="vs_defaultClientScript"> <meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema"> <LINK href="../style/lynn.css" rel="stylesheet"> </HEAD> <body MS_POSITIONING="GridLayout"> <form id="Form1" method="post" runat="server"> <TABLE cellSpacing="0" cellPadding="0" width="100%" border="0"> <tr> <td>选择年份： <asp:dropdownlist id="dropyear" runat="server"></asp:dropdownlist><asp:button id="Button2" runat="server" Text="查询"></asp:button></td> </tr> <tr> <td><asp:placeholder id="ChartHolder" runat="server"></asp:placeholder></td> </tr> </TABLE> </form> </body></HTML>cs:
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
namespace AAS2.finance
{
/// <summary>
/// owcsr 的摘要说明。
/// </summary>
public class owcsr : System.Web.UI.Page
{
protected System.Web.UI.WebControls.PlaceHolder ChartHolder;
protected System.Web.UI.WebControls.Button Button1;
protected System.Web.UI.WebControls.DropDownList dropyear;
protected System.Web.UI.WebControls.Button Button2;
private string _YEARID{get{if (this.Request["year"]!=null) return Convert.ToString(this.Request["year"].ToString()); else return "";}}
#region SQL String
private const string sqlsr=@"select * from (
select case when sum(入帐金额) is null then 0 else sum(入帐金额) end as 金额,1 as 月份 from 收入帐 where year(入帐时间)=year(getdate()) and month(入帐时间)=1
union
select case when sum(入帐金额) is null then 0 else sum(入帐金额) end as 金额,2 as 月份 from 收入帐 where year(入帐时间)=year(getdate()) and month(入帐时间)=2
union
select case when sum(入帐金额) is null then 0 else sum(入帐金额) end as 金额,3 as 月份 from 收入帐 where year(入帐时间)=year(getdate()) and month(入帐时间)=3
union
select case when sum(入帐金额) is null then 0 else sum(入帐金额) end as 金额,4 as 月份 from 收入帐 where year(入帐时间)=year(getdate()) and month(入帐时间)=4
union
select case when sum(入帐金额) is null then 0 else sum(入帐金额) end as 金额,5 as 月份 from 收入帐 where year(入帐时间)=year(getdate()) and month(入帐时间)=5
union
select case when sum(入帐金额) is null then 0 else sum(入帐金额) end as 金额,6 as 月份 from 收入帐 where year(入帐时间)=year(getdate()) and month(入帐时间)=6
union
select case when sum(入帐金额) is null then 0 else sum(入帐金额) end as 金额,7 as 月份 from 收入帐 where year(入帐时间)=year(getdate()) and month(入帐时间)=7
union
select case when sum(入帐金额) is null then 0 else sum(入帐金额) end as 金额,8 as 月份 from 收入帐 where year(入帐时间)=year(getdate()) and month(入帐时间)=8
union
select case when sum(入帐金额) is null then 0 else sum(入帐金额) end as 金额,9 as 月份 from 收入帐 where year(入帐时间)=year(getdate()) and month(入帐时间)=9
union
select case when sum(入帐金额) is null then 0 else sum(入帐金额) end as 金额,10 as 月份 from 收入帐 where year(入帐时间)=year(getdate()) and month(入帐时间)=10
union
select case when sum(入帐金额) is null then 0 else sum(入帐金额) end as 金额,11 as 月份 from 收入帐 where year(入帐时间)=year(getdate()) and month(入帐时间)=11
union
select case when sum(入帐金额) is null then 0 else sum(入帐金额) end as 金额,12 as 月份 from 收入帐 where year(入帐时间)=year(getdate()) and month(入帐时间)=12
) as tempowc order by tempowc.月份";
private const string sqlzc=@"select * from (
select case when sum(支付金额) is null then 0 else sum(支付金额) end as 金额,1 as 月份 from 支出帐 where year(支付时间)=year(getdate()) and month(支付时间)=1
union
select case when sum(支付金额) is null then 0 else sum(支付金额) end as 金额,2 as 月份 from 支出帐 where year(支付时间)=year(getdate()) and month(支付时间)=2
union
select case when sum(支付金额) is null then 0 else sum(支付金额) end as 金额,3 as 月份 from 支出帐 where year(支付时间)=year(getdate()) and month(支付时间)=3
union
select case when sum(支付金额) is null then 0 else sum(支付金额) end as 金额,4 as 月份 from 支出帐 where year(支付时间)=year(getdate()) and month(支付时间)=4
union
select case when sum(支付金额) is null then 0 else sum(支付金额) end as 金额,5 as 月份 from 支出帐 where year(支付时间)=year(getdate()) and month(支付时间)=5
union
select case when sum(支付金额) is null then 0 else sum(支付金额) end as 金额,6 as 月份 from 支出帐 where year(支付时间)=year(getdate()) and month(支付时间)=6
union
select case when sum(支付金额) is null then 0 else sum(支付金额) end as 金额,7 as 月份 from 支出帐 where year(支付时间)=year(getdate()) and month(支付时间)=7
union
select case when sum(支付金额) is null then 0 else sum(支付金额) end as 金额,8 as 月份 from 支出帐 where year(支付时间)=year(getdate()) and month(支付时间)=8
union
select case when sum(支付金额) is null then 0 else sum(支付金额) end as 金额,9 as 月份 from 支出帐 where year(支付时间)=year(getdate()) and month(支付时间)=9
union
select case when sum(支付金额) is null then 0 else sum(支付金额) end as 金额,10 as 月份 from 支出帐 where year(支付时间)=year(getdate()) and month(支付时间)=10
union
select case when sum(支付金额) is null then 0 else sum(支付金额) end as 金额,11 as 月份 from 支出帐 where year(支付时间)=year(getdate()) and month(支付时间)=11
union
select case when sum(支付金额) is null then 0 else sum(支付金额) end as 金额,12 as 月份 from 支出帐 where year(支付时间)=year(getdate()) and month(支付时间)=12
) as tempowc order by tempowc.月份";
private const string sqlys=@"select * from (
select case when sum(应收金额) is null then 0 else sum(应收金额) end as 金额,1 as 月份 from 应收帐 where year(预收款时间)=year(getdate()) and month(预收款时间)=1
union
select case when sum(应收金额) is null then 0 else sum(应收金额) end as 金额,2 as 月份 from 应收帐 where year(预收款时间)=year(getdate()) and month(预收款时间)=2
union
select case when sum(应收金额) is null then 0 else sum(应收金额) end as 金额,3 as 月份 from 应收帐 where year(预收款时间)=year(getdate()) and month(预收款时间)=3
union
select case when sum(应收金额) is null then 0 else sum(应收金额) end as 金额,4 as 月份 from 应收帐 where year(预收款时间)=year(getdate()) and month(预收款时间)=4
union
select case when sum(应收金额) is null then 0 else sum(应收金额) end as 金额,5 as 月份 from 应收帐 where year(预收款时间)=year(getdate()) and month(预收款时间)=5
union
select case when sum(应收金额) is null then 0 else sum(应收金额) end as 金额,6 as 月份 from 应收帐 where year(预收款时间)=year(getdate()) and month(预收款时间)=6
union
select case when sum(应收金额) is null then 0 else sum(应收金额) end as 金额,7 as 月份 from 应收帐 where year(预收款时间)=year(getdate()) and month(预收款时间)=7
union
select case when sum(应收金额) is null then 0 else sum(应收金额) end as 金额,8 as 月份 from 应收帐 where year(预收款时间)=year(getdate()) and month(预收款时间)=8
union
select case when sum(应收金额) is null then 0 else sum(应收金额) end as 金额,9 as 月份 from 应收帐 where year(预收款时间)=year(getdate()) and month(预收款时间)=9
union
select case when sum(应收金额) is null then 0 else sum(应收金额) end as 金额,10 as 月份 from 应收帐 where year(预收款时间)=year(getdate()) and month(预收款时间)=10
union
select case when sum(应收金额) is null then 0 else sum(应收金额) end as 金额,11 as 月份 from 应收帐 where year(预收款时间)=year(getdate()) and month(预收款时间)=11
union
select case when sum(应收金额) is null then 0 else sum(应收金额) end as 金额,12 as 月份 from 应收帐 where year(预收款时间)=year(getdate()) and month(预收款时间)=12
) as tempowc order by tempowc.月份";
private const string sqlyz=@"select * from (
select case when sum(应付款金额) is null then 0 else sum(应付款金额) end as 金额,1 as 月份 from 应支帐 where year(应付款时间)=year(getdate()) and month(应付款时间)=1
union
select case when sum(应付款金额) is null then 0 else sum(应付款金额) end as 金额,2 as 月份 from 应支帐 where year(应付款时间)=year(getdate()) and month(应付款时间)=2
union
select case when sum(应付款金额) is null then 0 else sum(应付款金额) end as 金额,3 as 月份 from 应支帐 where year(应付款时间)=year(getdate()) and month(应付款时间)=3
union
select case when sum(应付款金额) is null then 0 else sum(应付款金额) end as 金额,4 as 月份 from 应支帐 where year(应付款时间)=year(getdate()) and month(应付款时间)=4
union
select case when sum(应付款金额) is null then 0 else sum(应付款金额) end as 金额,5 as 月份 from 应支帐 where year(应付款时间)=year(getdate()) and month(应付款时间)=5
union
select case when sum(应付款金额) is null then 0 else sum(应付款金额) end as 金额,6 as 月份 from 应支帐 where year(应付款时间)=year(getdate()) and month(应付款时间)=6
union
select case when sum(应付款金额) is null then 0 else sum(应付款金额) end as 金额,7 as 月份 from 应支帐 where year(应付款时间)=year(getdate()) and month(应付款时间)=7
union
select case when sum(应付款金额) is null then 0 else sum(应付款金额) end as 金额,8 as 月份 from 应支帐 where year(应付款时间)=year(getdate()) and month(应付款时间)=8
union
select case when sum(应付款金额) is null then 0 else sum(应付款金额) end as 金额,9 as 月份 from 应支帐 where year(应付款时间)=year(getdate()) and month(应付款时间)=9
union
select case when sum(应付款金额) is null then 0 else sum(应付款金额) end as 金额,10 as 月份 from 应支帐 where year(应付款时间)=year(getdate()) and month(应付款时间)=10
union
select case when sum(应付款金额) is null then 0 else sum(应付款金额) end as 金额,11 as 月份 from 应支帐 where year(应付款时间)=year(getdate()) and month(应付款时间)=11
union
select case when sum(应付款金额) is null then 0 else sum(应付款金额) end as 金额,12 as 月份 from 应支帐 where year(应付款时间)=year(getdate()) and month(应付款时间)=12
) as tempowc order by tempowc.月份";
#endregion
private void bindyear()
{
int j=0;
for(int i=1990;i<2091;i++)
{
dropyear.Items.Insert(j,new ListItem(i.ToString(),i.ToString()));
j++;
}
dropyear.SelectedIndex=dropyear.Items.IndexOf(dropyear.Items.FindByValue(System.DateTime.Now.Year.ToString()));
}
private string GetValue(string type,string year)
{
string vv="",sql="";
switch(type)
{
case "收入":
sql=sqlsr.Replace("getdate()","'"+year+"'");
break;
case "支出":
sql=sqlsr.Replace("getdate()","'"+year+"'");
break;
case "应收":
sql=sqlys.Replace("getdate()","'"+year+"'");
break;
case "应支":
sql=sqlyz.Replace("getdate()","'"+year+"'");
break;
}
SqlDataReader dr=SqlHelper.ExecuteReader(SqlHelper.CONN_STRING,CommandType.Text,sql);
while(dr.Read())
{
vv+=dr["金额"].ToString()+ '\t';
}
return vv;
}
private void Page_Load(object sender, System.EventArgs e)
{
if(!this.IsPostBack)
{
bindyear();
if(this._YEARID!=String.Empty)
{
ChartHolder.Controls.Add(new LiteralControl("<IMG SRC='./test.gif' height=550 width=800/>"));
dropyear.SelectedIndex=dropyear.Items.IndexOf(dropyear.Items.FindByValue(_YEARID));
}
}
}
#region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.Button2.Click += new System.EventHandler(this.Button2_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
private void Button2_Click(object sender, System.EventArgs e)
{
try
{
//创建ChartSpace对象来放置图表
OWC.ChartSpace objCSpace = new OWC.ChartSpaceClass ();
//在ChartSpace对象中添加图表，Add方法返回chart对象
OWC.WCChart objChart = objCSpace.Charts.Add (0);
//指定图表的类型。类型由OWC.ChartChartTypeEnum枚举值得到
objChart.Type = OWC.ChartChartTypeEnum.chChartTypeColumnClustered;
//指定图表是否需要图例
objChart.HasLegend = true;
//给定标题
objChart.HasTitle = true;
objChart.Title.Caption= "AAS"+dropyear.SelectedItem.Text.ToString()+"财务分布图";
//给定x,y轴的图示说明
objChart.Axes[0].HasTitle = true;
objChart.Axes[0].Title.Caption = "Y ： 金额";
objChart.Axes[1].HasTitle = true;
objChart.Axes[1].Title.Caption = "X ： 月份";
//计算数据
/*categories 和 values 可以用tab分割的字符串来表示*/
string strSeriesName = "收入";
string strCategory = "1" + '\t' + "2" + '\t' + "3" + '\t'+"4" + '\t' + "5" + '\t' + "6" + '\t'+"7" + '\t' + "8" + '\t' + "9" + '\t'+"10" + '\t' + "11" + '\t' + "12" + '\t';
string strValue = GetValue("收入",dropyear.SelectedItem.Text.ToString());
//"100" + '\t' + "1000" + '\t' + "10000" + '\t'+"100000" + '\t' + "1000000" + '\t' + "150000" + '\t'+"100" + '\t' + "1000" + '\t' + "10000" + '\t'+"100000" + '\t' + "1000000" + '\t' + "150000" + '\t';
//添加一个series
objChart.SeriesCollection.Add(0);
//给定series的名字
objChart.SeriesCollection[0].SetData (OWC.ChartDimensionsEnum.chDimSeriesNames,
+ (int)OWC.ChartSpecialDataSourcesEnum.chDataLiteral, strSeriesName);
//给定分类
objChart.SeriesCollection[0].SetData (OWC.ChartDimensionsEnum.chDimCategories,
+ (int)OWC.ChartSpecialDataSourcesEnum.chDataLiteral, strCategory);
//给定值
objChart.SeriesCollection[0].SetData
(OWC.ChartDimensionsEnum.chDimValues,
(int)OWC.ChartSpecialDataSourcesEnum.chDataLiteral, strValue);
//***************************************************************
string strSeriesName2 = "支出";
string strCategory2 = "1" + '\t' + "2" + '\t' + "3" + '\t'+"4" + '\t' + "5" + '\t' + "6" + '\t'+"7" + '\t' + "8" + '\t' + "9" + '\t'+"10" + '\t' + "11" + '\t' + "12" + '\t';
string strValue2 = GetValue("支出",dropyear.SelectedItem.Text.ToString());
//"100" + '\t' + "1000" + '\t' + "10000" + '\t'+"100000" + '\t' + "1000000" + '\t' + "150000" + '\t'+"100" + '\t' + "1000" + '\t' + "10000" + '\t'+"100000" + '\t' + "1000000" + '\t' + "150000" + '\t';
//添加一个series
objChart.SeriesCollection.Add(1);
//objChart.SeriesCollection[1].Interior.Color="red";
//给定series的名字
objChart.SeriesCollection[1].SetData (OWC.ChartDimensionsEnum.chDimSeriesNames,
+ (int)OWC.ChartSpecialDataSourcesEnum.chDataLiteral, strSeriesName2);
//给定分类
objChart.SeriesCollection[1].SetData (OWC.ChartDimensionsEnum.chDimCategories,
+ (int)OWC.ChartSpecialDataSourcesEnum.chDataLiteral, strCategory);
//给定值
objChart.SeriesCollection[1].SetData
(OWC.ChartDimensionsEnum.chDimValues,
(int)OWC.ChartSpecialDataSourcesEnum.chDataLiteral, strValue2);
//*************************************************************
//***************************************************************
string strSeriesName3 = "应收";
string strCategory3 = "1" + '\t' + "2" + '\t' + "3" + '\t'+"4" + '\t' + "5" + '\t' + "6" + '\t'+"7" + '\t' + "8" + '\t' + "9" + '\t'+"10" + '\t' + "11" + '\t' + "12" + '\t';
string strValue3 = GetValue("应收",dropyear.SelectedItem.Text.ToString());
//"100" + '\t' + "1000" + '\t' + "10000" + '\t'+"100000" + '\t' + "1000000" + '\t' + "150000" + '\t'+"100" + '\t' + "1000" + '\t' + "10000" + '\t'+"100000" + '\t' + "1000000" + '\t' + "150000" + '\t';
//添加一个series
objChart.SeriesCollection.Add(2);
//objChart.SeriesCollection[1].Interior.Color="red";
//给定series的名字
objChart.SeriesCollection[2].SetData (OWC.ChartDimensionsEnum.chDimSeriesNames,
+ (int)OWC.ChartSpecialDataSourcesEnum.chDataLiteral, strSeriesName3);
//给定分类
objChart.SeriesCollection[2].SetData (OWC.ChartDimensionsEnum.chDimCategories,
+ (int)OWC.ChartSpecialDataSourcesEnum.chDataLiteral, strCategory);
//给定值
objChart.SeriesCollection[2].SetData
(OWC.ChartDimensionsEnum.chDimValues,
(int)OWC.ChartSpecialDataSourcesEnum.chDataLiteral, strValue3);
//*************************************************************
//***************************************************************
string strSeriesName4 = "应支";
string strCategory4 = "1" + '\t' + "2" + '\t' + "3" + '\t'+"4" + '\t' + "5" + '\t' + "6" + '\t'+"7" + '\t' + "8" + '\t' + "9" + '\t'+"10" + '\t' + "11" + '\t' + "12" + '\t';
string strValue4 = GetValue("应支",dropyear.SelectedItem.Text.ToString());
//"100" + '\t' + "1000" + '\t' + "10000" + '\t'+"100000" + '\t' + "1000000" + '\t' + "150000" + '\t'+"100" + '\t' + "1000" + '\t' + "10000" + '\t'+"100000" + '\t' + "1000000" + '\t' + "150000" + '\t';
//添加一个series
objChart.SeriesCollection.Add(3);
//objChart.SeriesCollection[1].Interior.Color="red";
//给定series的名字
objChart.SeriesCollection[3].SetData (OWC.ChartDimensionsEnum.chDimSeriesNames,
+ (int)OWC.ChartSpecialDataSourcesEnum.chDataLiteral, strSeriesName4);
//给定分类
objChart.SeriesCollection[3].SetData (OWC.ChartDimensionsEnum.chDimCategories,
+ (int)OWC.ChartSpecialDataSourcesEnum.chDataLiteral, strCategory);
//给定值
objChart.SeriesCollection[3].SetData
(OWC.ChartDimensionsEnum.chDimValues,
(int)OWC.ChartSpecialDataSourcesEnum.chDataLiteral, strValue4);
//*************************************************************
//输出成GIF文件.
string strAbsolutePath = (Server.MapPath(".")) + "\\test.gif";
objCSpace.ExportPicture(strAbsolutePath, "GIF", 800, 550);
//创建GIF文件的相对路径.
//string strRelativePath = "./test.gif";
//把图片添加到placeholder.
//string strATag = "<a href='" + strRelativePath + "'>AAS"+dropyear.SelectedItem.Text.ToString()+"财务分布图</a>";
//string strImageTag = "<IMG SRC='" + strRelativePath + "'/>";
//string strIfTag="<iframe name=\"MyIframeName\" id=\"211\" marginWidth=\"0\" src=" + strRelativePath + " frameBorder=\"0\" width=\"800\" height=\"600\"></iframe>";
//ChartHolder.Controls.Add(new LiteralControl(strImageTag));
//ChartHolder.Controls.Add(new LiteralControl(strImageTag));
string url="owcsr.aspx?year="+dropyear.SelectedItem.Text.ToString();
Response.Write("<script language='javascript'>window.open('"+url+@"','','height=600,width=850,top=0,left=0,location=no,menubar=no,resizable=no,scrollbars=no,status=no,titlebar=no,toolbar=no,directories=no');window.opener=null;window.close();</script>");
}
catch(Exception sqlee)
{
JScript.Alert("服务器错误！请稍后再试，"+sqlee.Message);
}
}
}
}
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
namespace AAS2.finance
{
/// <summary>
/// owcsr 的摘要说明。
/// </summary>
public class owcsr : System.Web.UI.Page
{
protected System.Web.UI.WebControls.PlaceHolder ChartHolder;
protected System.Web.UI.WebControls.Button Button1;
protected System.Web.UI.WebControls.DropDownList dropyear;
protected System.Web.UI.WebControls.Button Button2;
private string _YEARID{get{if (this.Request["year"]!=null) return Convert.ToString(this.Request["year"].ToString()); else return "";}}
#region SQL String
private const string sqlsr=@"select * from (
select case when sum(入帐金额) is null then 0 else sum(入帐金额) end as 金额,1 as 月份 from 收入帐 where year(入帐时间)=year(getdate()) and month(入帐时间)=1
union
select case when sum(入帐金额) is null then 0 else sum(入帐金额) end as 金额,2 as 月份 from 收入帐 where year(入帐时间)=year(getdate()) and month(入帐时间)=2
union
select case when sum(入帐金额) is null then 0 else sum(入帐金额) end as 金额,3 as 月份 from 收入帐 where year(入帐时间)=year(getdate()) and month(入帐时间)=3
union
select case when sum(入帐金额) is null then 0 else sum(入帐金额) end as 金额,4 as 月份 from 收入帐 where year(入帐时间)=year(getdate()) and month(入帐时间)=4
union
select case when sum(入帐金额) is null then 0 else sum(入帐金额) end as 金额,5 as 月份 from 收入帐 where year(入帐时间)=year(getdate()) and month(入帐时间)=5
union
select case when sum(入帐金额) is null then 0 else sum(入帐金额) end as 金额,6 as 月份 from 收入帐 where year(入帐时间)=year(getdate()) and month(入帐时间)=6
union
select case when sum(入帐金额) is null then 0 else sum(入帐金额) end as 金额,7 as 月份 from 收入帐 where year(入帐时间)=year(getdate()) and month(入帐时间)=7
union
select case when sum(入帐金额) is null then 0 else sum(入帐金额) end as 金额,8 as 月份 from 收入帐 where year(入帐时间)=year(getdate()) and month(入帐时间)=8
union
select case when sum(入帐金额) is null then 0 else sum(入帐金额) end as 金额,9 as 月份 from 收入帐 where year(入帐时间)=year(getdate()) and month(入帐时间)=9
union
select case when sum(入帐金额) is null then 0 else sum(入帐金额) end as 金额,10 as 月份 from 收入帐 where year(入帐时间)=year(getdate()) and month(入帐时间)=10
union
select case when sum(入帐金额) is null then 0 else sum(入帐金额) end as 金额,11 as 月份 from 收入帐 where year(入帐时间)=year(getdate()) and month(入帐时间)=11
union
select case when sum(入帐金额) is null then 0 else sum(入帐金额) end as 金额,12 as 月份 from 收入帐 where year(入帐时间)=year(getdate()) and month(入帐时间)=12
) as tempowc order by tempowc.月份";
private const string sqlzc=@"select * from (
select case when sum(支付金额) is null then 0 else sum(支付金额) end as 金额,1 as 月份 from 支出帐 where year(支付时间)=year(getdate()) and month(支付时间)=1
union
select case when sum(支付金额) is null then 0 else sum(支付金额) end as 金额,2 as 月份 from 支出帐 where year(支付时间)=year(getdate()) and month(支付时间)=2
union
select case when sum(支付金额) is null then 0 else sum(支付金额) end as 金额,3 as 月份 from 支出帐 where year(支付时间)=year(getdate()) and month(支付时间)=3
union
select case when sum(支付金额) is null then 0 else sum(支付金额) end as 金额,4 as 月份 from 支出帐 where year(支付时间)=year(getdate()) and month(支付时间)=4
union
select case when sum(支付金额) is null then 0 else sum(支付金额) end as 金额,5 as 月份 from 支出帐 where year(支付时间)=year(getdate()) and month(支付时间)=5
union
select case when sum(支付金额) is null then 0 else sum(支付金额) end as 金额,6 as 月份 from 支出帐 where year(支付时间)=year(getdate()) and month(支付时间)=6
union
select case when sum(支付金额) is null then 0 else sum(支付金额) end as 金额,7 as 月份 from 支出帐 where year(支付时间)=year(getdate()) and month(支付时间)=7
union
select case when sum(支付金额) is null then 0 else sum(支付金额) end as 金额,8 as 月份 from 支出帐 where year(支付时间)=year(getdate()) and month(支付时间)=8
union
select case when sum(支付金额) is null then 0 else sum(支付金额) end as 金额,9 as 月份 from 支出帐 where year(支付时间)=year(getdate()) and month(支付时间)=9
union
select case when sum(支付金额) is null then 0 else sum(支付金额) end as 金额,10 as 月份 from 支出帐 where year(支付时间)=year(getdate()) and month(支付时间)=10
union
select case when sum(支付金额) is null then 0 else sum(支付金额) end as 金额,11 as 月份 from 支出帐 where year(支付时间)=year(getdate()) and month(支付时间)=11
union
select case when sum(支付金额) is null then 0 else sum(支付金额) end as 金额,12 as 月份 from 支出帐 where year(支付时间)=year(getdate()) and month(支付时间)=12
) as tempowc order by tempowc.月份";
private const string sqlys=@"select * from (
select case when sum(应收金额) is null then 0 else sum(应收金额) end as 金额,1 as 月份 from 应收帐 where year(预收款时间)=year(getdate()) and month(预收款时间)=1
union
select case when sum(应收金额) is null then 0 else sum(应收金额) end as 金额,2 as 月份 from 应收帐 where year(预收款时间)=year(getdate()) and month(预收款时间)=2
union
select case when sum(应收金额) is null then 0 else sum(应收金额) end as 金额,3 as 月份 from 应收帐 where year(预收款时间)=year(getdate()) and month(预收款时间)=3
union
select case when sum(应收金额) is null then 0 else sum(应收金额) end as 金额,4 as 月份 from 应收帐 where year(预收款时间)=year(getdate()) and month(预收款时间)=4
union
select case when sum(应收金额) is null then 0 else sum(应收金额) end as 金额,5 as 月份 from 应收帐 where year(预收款时间)=year(getdate()) and month(预收款时间)=5
union
select case when sum(应收金额) is null then 0 else sum(应收金额) end as 金额,6 as 月份 from 应收帐 where year(预收款时间)=year(getdate()) and month(预收款时间)=6
union
select case when sum(应收金额) is null then 0 else sum(应收金额) end as 金额,7 as 月份 from 应收帐 where year(预收款时间)=year(getdate()) and month(预收款时间)=7
union
select case when sum(应收金额) is null then 0 else sum(应收金额) end as 金额,8 as 月份 from 应收帐 where year(预收款时间)=year(getdate()) and month(预收款时间)=8
union
select case when sum(应收金额) is null then 0 else sum(应收金额) end as 金额,9 as 月份 from 应收帐 where year(预收款时间)=year(getdate()) and month(预收款时间)=9
union
select case when sum(应收金额) is null then 0 else sum(应收金额) end as 金额,10 as 月份 from 应收帐 where year(预收款时间)=year(getdate()) and month(预收款时间)=10
union
select case when sum(应收金额) is null then 0 else sum(应收金额) end as 金额,11 as 月份 from 应收帐 where year(预收款时间)=year(getdate()) and month(预收款时间)=11
union
select case when sum(应收金额) is null then 0 else sum(应收金额) end as 金额,12 as 月份 from 应收帐 where year(预收款时间)=year(getdate()) and month(预收款时间)=12
) as tempowc order by tempowc.月份";
private const string sqlyz=@"select * from (
select case when sum(应付款金额) is null then 0 else sum(应付款金额) end as 金额,1 as 月份 from 应支帐 where year(应付款时间)=year(getdate()) and month(应付款时间)=1
union
select case when sum(应付款金额) is null then 0 else sum(应付款金额) end as 金额,2 as 月份 from 应支帐 where year(应付款时间)=year(getdate()) and month(应付款时间)=2
union
select case when sum(应付款金额) is null then 0 else sum(应付款金额) end as 金额,3 as 月份 from 应支帐 where year(应付款时间)=year(getdate()) and month(应付款时间)=3
union
select case when sum(应付款金额) is null then 0 else sum(应付款金额) end as 金额,4 as 月份 from 应支帐 where year(应付款时间)=year(getdate()) and month(应付款时间)=4
union
select case when sum(应付款金额) is null then 0 else sum(应付款金额) end as 金额,5 as 月份 from 应支帐 where year(应付款时间)=year(getdate()) and month(应付款时间)=5
union
select case when sum(应付款金额) is null then 0 else sum(应付款金额) end as 金额,6 as 月份 from 应支帐 where year(应付款时间)=year(getdate()) and month(应付款时间)=6
union
select case when sum(应付款金额) is null then 0 else sum(应付款金额) end as 金额,7 as 月份 from 应支帐 where year(应付款时间)=year(getdate()) and month(应付款时间)=7
union
select case when sum(应付款金额) is null then 0 else sum(应付款金额) end as 金额,8 as 月份 from 应支帐 where year(应付款时间)=year(getdate()) and month(应付款时间)=8
union
select case when sum(应付款金额) is null then 0 else sum(应付款金额) end as 金额,9 as 月份 from 应支帐 where year(应付款时间)=year(getdate()) and month(应付款时间)=9
union
select case when sum(应付款金额) is null then 0 else sum(应付款金额) end as 金额,10 as 月份 from 应支帐 where year(应付款时间)=year(getdate()) and month(应付款时间)=10
union
select case when sum(应付款金额) is null then 0 else sum(应付款金额) end as 金额,11 as 月份 from 应支帐 where year(应付款时间)=year(getdate()) and month(应付款时间)=11
union
select case when sum(应付款金额) is null then 0 else sum(应付款金额) end as 金额,12 as 月份 from 应支帐 where year(应付款时间)=year(getdate()) and month(应付款时间)=12
) as tempowc order by tempowc.月份";
#endregion
private void bindyear()
{
int j=0;
for(int i=1990;i<2091;i++)
{
dropyear.Items.Insert(j,new ListItem(i.ToString(),i.ToString()));
j++;
}
dropyear.SelectedIndex=dropyear.Items.IndexOf(dropyear.Items.FindByValue(System.DateTime.Now.Year.ToString()));
}
private string GetValue(string type,string year)
{
string vv="",sql="";
switch(type)
{
case "收入":
sql=sqlsr.Replace("getdate()","'"+year+"'");
break;
case "支出":
sql=sqlsr.Replace("getdate()","'"+year+"'");
break;
case "应收":
sql=sqlys.Replace("getdate()","'"+year+"'");
break;
case "应支":
sql=sqlyz.Replace("getdate()","'"+year+"'");
break;
}
SqlDataReader dr=SqlHelper.ExecuteReader(SqlHelper.CONN_STRING,CommandType.Text,sql);
while(dr.Read())
{
vv+=dr["金额"].ToString()+ '\t';
}
return vv;
}
private void Page_Load(object sender, System.EventArgs e)
{
if(!this.IsPostBack)
{
bindyear();
if(this._YEARID!=String.Empty)
{
ChartHolder.Controls.Add(new LiteralControl("<IMG SRC='./test.gif' height=550 width=800/>"));
dropyear.SelectedIndex=dropyear.Items.IndexOf(dropyear.Items.FindByValue(_YEARID));
}
}
}
#region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.Button2.Click += new System.EventHandler(this.Button2_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
private void Button2_Click(object sender, System.EventArgs e)
{
try
{
//创建ChartSpace对象来放置图表
OWC.ChartSpace objCSpace = new OWC.ChartSpaceClass ();
//在ChartSpace对象中添加图表，Add方法返回chart对象
OWC.WCChart objChart = objCSpace.Charts.Add (0);
//指定图表的类型。类型由OWC.ChartChartTypeEnum枚举值得到
objChart.Type = OWC.ChartChartTypeEnum.chChartTypeColumnClustered;
//指定图表是否需要图例
objChart.HasLegend = true;
//给定标题
objChart.HasTitle = true;
objChart.Title.Caption= "AAS"+dropyear.SelectedItem.Text.ToString()+"财务分布图";
//给定x,y轴的图示说明
objChart.Axes[0].HasTitle = true;
objChart.Axes[0].Title.Caption = "Y ： 金额";
objChart.Axes[1].HasTitle = true;
objChart.Axes[1].Title.Caption = "X ： 月份";
//计算数据
/*categories 和 values 可以用tab分割的字符串来表示*/
string strSeriesName = "收入";
string strCategory = "1" + '\t' + "2" + '\t' + "3" + '\t'+"4" + '\t' + "5" + '\t' + "6" + '\t'+"7" + '\t' + "8" + '\t' + "9" + '\t'+"10" + '\t' + "11" + '\t' + "12" + '\t';
string strValue = GetValue("收入",dropyear.SelectedItem.Text.ToString());
//"100" + '\t' + "1000" + '\t' + "10000" + '\t'+"100000" + '\t' + "1000000" + '\t' + "150000" + '\t'+"100" + '\t' + "1000" + '\t' + "10000" + '\t'+"100000" + '\t' + "1000000" + '\t' + "150000" + '\t';
//添加一个series
objChart.SeriesCollection.Add(0);
//给定series的名字
objChart.SeriesCollection[0].SetData (OWC.ChartDimensionsEnum.chDimSeriesNames,
+ (int)OWC.ChartSpecialDataSourcesEnum.chDataLiteral, strSeriesName);
//给定分类
objChart.SeriesCollection[0].SetData (OWC.ChartDimensionsEnum.chDimCategories,
+ (int)OWC.ChartSpecialDataSourcesEnum.chDataLiteral, strCategory);
//给定值
objChart.SeriesCollection[0].SetData
(OWC.ChartDimensionsEnum.chDimValues,
(int)OWC.ChartSpecialDataSourcesEnum.chDataLiteral, strValue);
//***************************************************************
string strSeriesName2 = "支出";
string strCategory2 = "1" + '\t' + "2" + '\t' + "3" + '\t'+"4" + '\t' + "5" + '\t' + "6" + '\t'+"7" + '\t' + "8" + '\t' + "9" + '\t'+"10" + '\t' + "11" + '\t' + "12" + '\t';
string strValue2 = GetValue("支出",dropyear.SelectedItem.Text.ToString());
//"100" + '\t' + "1000" + '\t' + "10000" + '\t'+"100000" + '\t' + "1000000" + '\t' + "150000" + '\t'+"100" + '\t' + "1000" + '\t' + "10000" + '\t'+"100000" + '\t' + "1000000" + '\t' + "150000" + '\t';
//添加一个series
objChart.SeriesCollection.Add(1);
//objChart.SeriesCollection[1].Interior.Color="red";
//给定series的名字
objChart.SeriesCollection[1].SetData (OWC.ChartDimensionsEnum.chDimSeriesNames,
+ (int)OWC.ChartSpecialDataSourcesEnum.chDataLiteral, strSeriesName2);
//给定分类
objChart.SeriesCollection[1].SetData (OWC.ChartDimensionsEnum.chDimCategories,
+ (int)OWC.ChartSpecialDataSourcesEnum.chDataLiteral, strCategory);
//给定值
objChart.SeriesCollection[1].SetData
(OWC.ChartDimensionsEnum.chDimValues,
(int)OWC.ChartSpecialDataSourcesEnum.chDataLiteral, strValue2);
//*************************************************************
//***************************************************************
string strSeriesName3 = "应收";
string strCategory3 = "1" + '\t' + "2" + '\t' + "3" + '\t'+"4" + '\t' + "5" + '\t' + "6" + '\t'+"7" + '\t' + "8" + '\t' + "9" + '\t'+"10" + '\t' + "11" + '\t' + "12" + '\t';
string strValue3 = GetValue("应收",dropyear.SelectedItem.Text.ToString());
//"100" + '\t' + "1000" + '\t' + "10000" + '\t'+"100000" + '\t' + "1000000" + '\t' + "150000" + '\t'+"100" + '\t' + "1000" + '\t' + "10000" + '\t'+"100000" + '\t' + "1000000" + '\t' + "150000" + '\t';
//添加一个series
objChart.SeriesCollection.Add(2);
//objChart.SeriesCollection[1].Interior.Color="red";
//给定series的名字
objChart.SeriesCollection[2].SetData (OWC.ChartDimensionsEnum.chDimSeriesNames,
+ (int)OWC.ChartSpecialDataSourcesEnum.chDataLiteral, strSeriesName3);
//给定分类
objChart.SeriesCollection[2].SetData (OWC.ChartDimensionsEnum.chDimCategories,
+ (int)OWC.ChartSpecialDataSourcesEnum.chDataLiteral, strCategory);
//给定值
objChart.SeriesCollection[2].SetData
(OWC.ChartDimensionsEnum.chDimValues,
(int)OWC.ChartSpecialDataSourcesEnum.chDataLiteral, strValue3);
//*************************************************************
//***************************************************************
string strSeriesName4 = "应支";
string strCategory4 = "1" + '\t' + "2" + '\t' + "3" + '\t'+"4" + '\t' + "5" + '\t' + "6" + '\t'+"7" + '\t' + "8" + '\t' + "9" + '\t'+"10" + '\t' + "11" + '\t' + "12" + '\t';
string strValue4 = GetValue("应支",dropyear.SelectedItem.Text.ToString());
//"100" + '\t' + "1000" + '\t' + "10000" + '\t'+"100000" + '\t' + "1000000" + '\t' + "150000" + '\t'+"100" + '\t' + "1000" + '\t' + "10000" + '\t'+"100000" + '\t' + "1000000" + '\t' + "150000" + '\t';
//添加一个series
objChart.SeriesCollection.Add(3);
//objChart.SeriesCollection[1].Interior.Color="red";
//给定series的名字
objChart.SeriesCollection[3].SetData (OWC.ChartDimensionsEnum.chDimSeriesNames,
+ (int)OWC.ChartSpecialDataSourcesEnum.chDataLiteral, strSeriesName4);
//给定分类
objChart.SeriesCollection[3].SetData (OWC.ChartDimensionsEnum.chDimCategories,
+ (int)OWC.ChartSpecialDataSourcesEnum.chDataLiteral, strCategory);
//给定值
objChart.SeriesCollection[3].SetData
(OWC.ChartDimensionsEnum.chDimValues,
(int)OWC.ChartSpecialDataSourcesEnum.chDataLiteral, strValue4);
//*************************************************************
//输出成GIF文件.
string strAbsolutePath = (Server.MapPath(".")) + "\\test.gif";
objCSpace.ExportPicture(strAbsolutePath, "GIF", 800, 550);
//创建GIF文件的相对路径.
//string strRelativePath = "./test.gif";
//把图片添加到placeholder.
//string strATag = "<a href='" + strRelativePath + "'>AAS"+dropyear.SelectedItem.Text.ToString()+"财务分布图</a>";
//string strImageTag = "<IMG SRC='" + strRelativePath + "'/>";
//string strIfTag="<iframe name=\"MyIframeName\" id=\"211\" marginWidth=\"0\" src=" + strRelativePath + " frameBorder=\"0\" width=\"800\" height=\"600\"></iframe>";
//ChartHolder.Controls.Add(new LiteralControl(strImageTag));
//ChartHolder.Controls.Add(new LiteralControl(strImageTag));
string url="owcsr.aspx?year="+dropyear.SelectedItem.Text.ToString();
Response.Write("<script language='javascript'>window.open('"+url+@"','','height=600,width=850,top=0,left=0,location=no,menubar=no,resizable=no,scrollbars=no,status=no,titlebar=no,toolbar=no,directories=no');window.opener=null;window.close();</script>");
}
catch(Exception sqlee)
{
JScript.Alert("服务器错误！请稍后再试，"+sqlee.Message);
}
}
}
}
