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

1.  焦点CELL变化
    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);
            }
}

1.自定义Cell
            this.pivotGridControl1.CustomDrawCell += new DevExpress.XtraPivotGrid.PivotCustomDrawCellEventHandler(this.pivotGridControl1_CustomDrawCell);
{
      Color highlightColor;
        const int colorStep = 25;
        List<Point> pointDifference;
        object[,] lastCells;

        List<Point> PointDifference
        {
            get
            {
                if (pointDifference == null)
                {
                    object[,] currentState = CellsState;
                    pointDifference = new List<Point>();
                    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);
        }