请教Spread for ASP.NET 5.0中数据的分组统计功能怎么用？

收藏

如题！

iceman · iceman

回复 1楼xxwood的帖子

您好，
请查看该贴 9# 中的代码：http://gcdn.grapecity.com/showtopic-2309.html

iceman · iceman

回复 1楼xxwood的帖子

代码精简结果：

public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (IsPostBack)
{
return;
}
FpSpread1.Sheets[0].GroupBarVisible = true;
//设置 group 信息
FarPoint.Web.Spread.SheetView sv = this.FpSpread1.ActiveSheetView;
sv.AllowGroup = true;
GroupDataModel gdm = new GroupDataModel(sv.DataModel);
sv.DataModel = gdm;
FarPoint.Web.Spread.SortInfo[] sort = new FarPoint.Web.Spread.SortInfo[1];
//设置分组列和排序方式
sort[0] = new FarPoint.Web.Spread.SortInfo(1, true);
gdm.Group(sort);
FarPoint.Web.Spread.Model.Group group = new Group(gdm, (FarPoint.Web.Spread.Model.Group)gdm.Groups[0], 0, false);
GroupFooter groupfooter = new GroupFooter(group);
FpSpread1.Sheets[0].GroupFooterVisible = true;
//设置 ColumnFooter,GroupFooter 第1列公式为 Sum ,用于计算第5列所有单元格加和.
this.FpSpread1.ActiveSheetView.Columns[0].AggregationType = FarPoint.Web.Spread.Model.AggregationType.Sum;
}
}

复制代码

iceman · iceman

回复 1楼xxwood的帖子

xxwood 你好，

Excel 分组如图：

Spread 分组如图：

Excel 无法支持 Spread 中的分组样式。

我们可以通过自定义实现，代码有不完善之处，还需要你继续完善，以下为 Demo ：

grouping.zip (27.9 KB, 下载次数: 98)

iceman · iceman

回复 1楼xxwood的帖子

公式的应用可以参考以下代码：

protected void Page_Load(object sender, EventArgs e)
{
if (IsPostBack)
{
return;
}
this.FpSpread1.ClientAutoCalculation = true;
this.FpSpread1.Sheets[0].Columns[3].Formula = "C1/B1";
FarPoint.Web.Spread.DoubleCellType dblc = new FarPoint.Web.Spread.DoubleCellType();
dblc.DecimalDigits = 2;
dblc.FixedPoint = true;
FpSpread1.ActiveSheetView.Columns[3].CellType = dblc;
}
protected void Button1_Click(object sender, EventArgs e)
{
this.FpSpread1.SaveExcelToResponse();
}

复制代码

xxwood · xxwood

自动分组统计，第一次查询的时候表格正常，第二次查询后表格后面就出现一些乱七八糟的数据，什么原因？代码如下：

FpSpread1.DataSource = ds;
FarPoint.Web.Spread.Model.GroupDataModel gdm = new FarPoint.Web.Spread.Model.GroupDataModel(FpSpread1.ActiveSheetView.DataModel);
FarPoint.Web.Spread.SortInfo[] st = new FarPoint.Web.Spread.SortInfo[2];
st[0] = new FarPoint.Web.Spread.SortInfo(0, true);
st[1] = new FarPoint.Web.Spread.SortInfo(1, true);
gdm.Group(st);
FpSpread1.ActiveSheetView.DataModel = gdm;
FpSpread1.ActiveSheetView.GroupFooterVisible = true;
FpSpread1.ActiveSheetView.ColumnFooter.RowCount = 1;
FpSpread1.ActiveSheetView.Columns[3].AggregationType = FarPoint.Web.Spread.Model.AggregationType.Sum;

复制代码

iceman · iceman

回复 6楼xxwood的帖子

请问查询指的是什么操作？能否发个demo到论坛调试？

xxwood · xxwood

还要什么demo，反正代码页很少，我在这发吧。
页面上就3个文本框，一个按钮，一个FpSpread，没别的控件。后台代码如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
namespace ysjyb
{
public partial class zdgz : System.Web.UI.Page
{
String ConnStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
TextBox_ny1.Text = “201303”;
TextBox_ny2.Text = “201203”;
TextBox_nlc.Text = "3";
fp_bind();
}
}
void fp_bind()
{
OleDbConnection conn = new OleDbConnection(ConnStr);
conn.Open();
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "select c.km,c.dm,a.* from (select a.jh,a.rynl nl1,b.rynl nl2,a.rynl-b.rynl nlc from (select * from yd_bb_yjdjsjb where ny='"+TextBox_ny2.Text+"') a,(select * from yd_bb_yjdjsjb where ny='"+TextBox_ny1.Text+"') b where a.jh=b.jh) a,ys_daa01 b,dwdzb c where a.jh=b.jh and b.dwdm=c.dwdm and nlc>"+TextBox_nlc.Text+" order by c.km,c.dm,nlc desc";
OleDbDataAdapter da = new OleDbDataAdapter();
Response.Write(cmd.CommandText);
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds);
FpSpread1.ActiveSheetView.DataSource = ds;
ds.Dispose();
da.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
FarPoint.Web.Spread.Model.GroupDataModel gdm = new FarPoint.Web.Spread.Model.GroupDataModel(FpSpread1.ActiveSheetView.DataModel);
FarPoint.Web.Spread.SortInfo[] st = new FarPoint.Web.Spread.SortInfo[2];
st[0] = new FarPoint.Web.Spread.SortInfo(0, true);
st[1] = new FarPoint.Web.Spread.SortInfo(1, true);
gdm.Group(st);
FpSpread1.ActiveSheetView.DataModel = gdm;
FpSpread1.ActiveSheetView.GroupFooterVisible = true;
FpSpread1.ActiveSheetView.ColumnFooter.RowCount = 1;
FpSpread1.ActiveSheetView.Columns[3].AggregationType = FarPoint.Web.Spread.Model.AggregationType.Sum;
FpSpread1.ActiveSheetView.Columns[4].AggregationType = FarPoint.Web.Spread.Model.AggregationType.Sum;
FpSpread1.ActiveSheetView.Columns[5].AggregationType = FarPoint.Web.Spread.Model.AggregationType.Sum;
}
protected void Button1_Click(object sender, EventArgs e)
{
fp_bind();
}
}
}

复制代码

xxwood · xxwood

不但出现乱数据，而且按钮似乎也失效了。
逐句调试了下，发现 FpSpread1.ActiveSheetView.DataModel = gdm; 这一句的问题。只要有这一句，按钮就失效。
是不是在分组完成后，还要做什么类似于close、dispose之类的处理啊？

iceman · iceman

回复 9楼xxwood的帖子

您的看法是正确的,似乎是分组有个累计效果导致的。请在 fp_bind() 方法的开始处添加：

this.FpSpread1.Sheets[0].Reset();

复制代码

		自动登录	找回密码
密码			立即注册

请教Spread for ASP.NET 5.0中数据的分组统计功能怎么用？

9 个回复

活字格认证

微信认证勋章

元老葡萄