DevExpress.XtraPivotGrid.PivotGridControl
DevExpress.XtraPivotGrid.PivotGridControl
1.简单使用：
{
string connString = "Server=.;uid=sa;pwd=123;database=bst;";
SqlDataAdapter da = new SqlDataAdapter(@"SELECT a.Customer_Id,a.Goods_No,a.Grade,a.Quantity,a.Quantity*g.unitPrice Amount
FROM cstock a INNER JOIN goods g ON a.goods_no=g.goods_no", connString);
//得到并填充数据源
DataSet ds = new DataSet();
da.Fill(ds);
this.pivotGridControl1.DataSource = ds.Tables[0];
PivotGridField field1 = new PivotGridField("customer_id", PivotArea.ColumnArea);
field1.FieldName = "Customer_Id";
PivotGridField field2 = new PivotGridField("goods_no", PivotArea.RowArea);
field2.FieldName = "Goods_No";
PivotGridField field3 = new PivotGridField("grade", PivotArea.RowArea);
field3.FieldName = "Grade";
PivotGridField field4 = new PivotGridField("quantity", PivotArea.DataArea);
field4.FieldName = "Quantity";
PivotGridField field5 = new PivotGridField("amount", PivotArea.DataArea);
field5.FieldName = "Amount";
//添加字段
this.pivotGridControl1.Fields.AddRange(new DevExpress.XtraPivotGrid.PivotGridField[]{
field1,field2,field3,field4,field5
}
);
//组设置
DevExpress.XtraPivotGrid.PivotGridGroup group1 = new PivotGridGroup("group1");
group1.Add(field1);
group1.Add(field3);
this.pivotGridControl1.Groups.AddRange(new DevExpress.XtraPivotGrid.PivotGridGroup[] { group1 });
}
2.简单使用2
{
string connString = "Server=.;uid=sa;pwd=123;database=bst;";
SqlDataAdapter da = new SqlDataAdapter(@"SELECT a.Customer_Id,a.Goods_No,a.Grade,b.ColorId,b.Long,CAST(SUBSTRING(b.Size,2,20) AS INT) Size,b.SizeName,b.Quantity,b.Quantity*g.unitPrice Amount
FROM cstock a
INNER JOIN cstockdetail2 b ON a.stockcode=b.stockcode
INNER JOIN goods g ON a.goods_no=g.goods_no
ORDER BY a.Customer_Id,a.Goods_No,a.Grade,b.ColorId,b.Long,CAST(SUBSTRING(b.Size,2,20) AS INT)", connString);
DataSet ds = new DataSet();
da.Fill(ds);
this.pivotGridControl1.DataSource = ds.Tables[0];
PivotGridField fCustomerId = new PivotGridField("customer_id", PivotArea.ColumnArea);
fCustomerId.FieldName = "Customer_Id";
PivotGridField fGoodsId = new PivotGridField("goods_no", PivotArea.RowArea);
fGoodsId.FieldName = "Goods_No";
PivotGridField fGrade = new PivotGridField("grade", PivotArea.RowArea);
fGrade.FieldName = "Grade";
PivotGridField fColorId = new PivotGridField("ColorId", PivotArea.RowArea);
fColorId.FieldName = "ColorId";
PivotGridField fLong = new PivotGridField("Long", PivotArea.RowArea);
fLong.FieldName = "Long";
PivotGridField fSize = new PivotGridField("Size", PivotArea.RowArea);
fSize.FieldName = "Size";
fSize.Caption = "-Size-";
//PivotGridField fSizeName = new PivotGridField("SizeName", PivotArea.RowArea);
//fSizeName.FieldName = "SizeName";
PivotGridField fQuantity = new PivotGridField("quantity", PivotArea.DataArea);
fQuantity.FieldName = "Quantity";
PivotGridField fAmount = new PivotGridField("amount", PivotArea.DataArea);
fAmount.FieldName = "Amount";
this.pivotGridControl1.Fields.AddRange(new DevExpress.XtraPivotGrid.PivotGridField[]{
fCustomerId,fGoodsId,fGrade,fColorId,fSize,fLong,fQuantity,fAmount
}
);
DevExpress.XtraPivotGrid.PivotGridGroup group1 = new PivotGridGroup("group1");
group1.Add(fCustomerId);
group1.Add(fSize);
this.pivotGridControl1.Groups.AddRange(new DevExpress.XtraPivotGrid.PivotGridGroup[] { group1 });
fLong.AreaIndex = fGrade.AreaIndex;
this.pivotGridControl1.Fields.Remove(fGrade);
}
this.pivotGridControl1.FocusedCellChanged += new System.EventHandler(this.pivotGridControl1_FocusedCellChanged);
{
PivotCellEventArgs c = this.pivotGridControl1.Cells.GetFocusedCellInfo();
//获取Goods_NO列的值
string ss = this.pivotGridControl1.Fields["Goods_No"].GetValueText(c.GetFieldValue(this.pivotGridControl1.Fields["Goods_No"]));
//遍历当前行ColumnField的内容
foreach (PivotGridField f in c.GetColumnFields()) {
string displayText = f.GetValueText(c.GetFieldValue(f));
int debug = 0;
}
//遍历当前行RowField的内容
foreach (PivotGridField f in c.GetRowFields())
{
string displayText = f.GetValueText(c.GetFieldValue(f));
int debug = 0;
}
DataTable dt = (this.pivotGridControl1.DataSource as DataTable);
foreach (DataRow dr in dt.Rows)
{
if(dr["Size"].ToString().Equals("1"))
dr["Size"] = "11";
else if (dr["Size"].ToString().Equals("2"))
dr["Size"] = "12";
else
dr["Size"] = "100";
}
this.pivotGridControl1.RefreshData();
}
2.FieldArea发生变化
this.pivotGridControl1.FieldAreaChanged += new DevExpress.XtraPivotGrid.PivotFieldEventHandler(this.pivotGridControl1_FieldAreaChanged);
{
if (!e.Field.FieldName.Equals("Customer_Id")) return;
PivotGridField fCustomer = this.pivotGridControl1.Fields["Customer_Id"];
if (fCustomer.Area == PivotArea.RowArea)
{
PivotGridField fSizeName = new PivotGridField("SizeName", PivotArea.RowArea);
fSizeName.FieldName = "SizeName";
PivotGridField fSize = this.pivotGridControl1.Fields["Size"];
fSizeName.AreaIndex = fSize.AreaIndex;
this.pivotGridControl1.Fields.Remove(fSize);
this.pivotGridControl1.Fields.Add(fSizeName);
this.pivotGridControl1.Groups[0].Add(fSizeName);
}
else if (fCustomer.Area == PivotArea.ColumnArea)
{
PivotGridField fSize = new PivotGridField("Size", PivotArea.RowArea);
fSize.FieldName = "Size";
PivotGridField fSizeName = this.pivotGridControl1.Fields["SizeName"];
fSize.AreaIndex = fSizeName.AreaIndex;
this.pivotGridControl1.Fields.Remove(fSizeName);
this.pivotGridControl1.Fields.Add(fSize);
this.pivotGridControl1.Groups[0].Add(fSize);
}
}
this.pivotGridControl1.CustomDrawCell += new DevExpress.XtraPivotGrid.PivotCustomDrawCellEventHandler(this.pivotGridControl1_CustomDrawCell);
{
Color highlightColor;
const int colorStep = 25;
List pointDifference;
object[,] lastCells;
List PointDifference
{
get
{
if (pointDifference == null)
{
object[,] currentState = CellsState;
pointDifference = new List();
for (int i = lastCells.GetLowerBound(0); i <= lastCells.GetUpperBound(0); i++)
for (int j = lastCells.GetLowerBound(1); j <= lastCells.GetUpperBound(1); j++)
if (!object.Equals(lastCells[i, j], currentState[i, j]))
pointDifference.Add(new Point(i, j));
}
return pointDifference;
}
}
object[,] LastCells
{
get { return lastCells; }
set
{
lastCells = value;
pointDifference = null;
}
}
object[,] CellsState
{
get
{
object[,] result = new object[pivotGridControl1.Cells.ColumnCount, pivotGridControl1.Cells.RowCount];
for (int i = 0; i < pivotGridControl1.Cells.ColumnCount; i++)
for (int j = 0; j < pivotGridControl1.Cells.RowCount; j++)
result[i, j] = pivotGridControl1.Cells.GetCellInfo(i, j).Value;
return result;
}
}
private void pivotGridControl1_CustomDrawCell(object sender, PivotCustomDrawCellEventArgs e)
{
bool highlight = highlightColor.R > 0 && PointDifference.Contains(new Point(e.ColumnIndex, e.RowIndex));
if ((e.ColumnIndex == pivotGridControl1.Cells.FocusedCell.X && e.RowIndex == pivotGridControl1.Cells.FocusedCell.Y) || highlight)
e.Appearance.Font = new Font(e.Appearance.Font, FontStyle.Bold);
else
e.Appearance.Font = new Font(e.Appearance.Font, FontStyle.Regular);
e.Appearance.ForeColor = highlight ? highlightColor : Color.Black;
if (e.ColumnIndex == pivotGridControl1.Cells.FocusedCell.X || e.RowIndex == pivotGridControl1.Cells.FocusedCell.Y)
e.Appearance.BackColor = Color.FromArgb(e.Appearance.BackColor.R - 10, e.Appearance.BackColor.G - 10, e.Appearance.BackColor.B - 10);
}
}
2.自定义ColumnField
this.pivotGridControl1.CustomDrawFieldValue += new DevExpress.XtraPivotGrid.PivotCustomDrawFieldValueEventHandler(this.pivotGridControl1_CustomDrawFieldValue);
private void pivotGridControl1_CustomDrawFieldValue(object sender, PivotCustomDrawFieldValueEventArgs e)
{
int index = e.Area == PivotArea.ColumnArea ? pivotGridControl1.Cells.FocusedCell.X : pivotGridControl1.Cells.FocusedCell.Y;
if (e.MinIndex <= index && index <= e.MaxIndex)
e.Appearance.Font = new Font(e.Appearance.Font, FontStyle.Bold);
else
e.Appearance.Font = new Font(e.Appearance.Font, FontStyle.Regular);
}
