通过 ComboBoxCellType 实现级联数据输入

在数据输入时，我们经常会遇到级联数据输入的问题，比如：在选择一个产品类别之后，根据类别显示该类别的全部产品。本文就结合 ComboBoxCellType 来实现级联数据输入的功能。

实现，初始化Spread表格，并设置类别列的单元格类型，代码如下：

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            FpSpread1.ClientAutoCalculation = true;
            FpSpread1.ActiveSheetView.AllowPage = false;
            FpSpread1.ActiveSheetView.RowCount = 10;
            FpSpread1.ActiveSheetView.ColumnCount = 6;
            FpSpread1.ActiveSheetView.Columns[0].Label = "类别";
            FpSpread1.ActiveSheetView.Columns[0].Width = 150;
            FpSpread1.ActiveSheetView.Columns[1].Label = "名称";
            FpSpread1.ActiveSheetView.Columns[1].Width = 300;
            FpSpread1.ActiveSheetView.Columns[2].Label = "单价";
            FpSpread1.ActiveSheetView.Columns[2].Width = 100;
            FpSpread1.ActiveSheetView.Columns[3].Label = "数量";
            FpSpread1.ActiveSheetView.Columns[3].Width = 100;
            FpSpread1.ActiveSheetView.Columns[4].Label = "折扣";
            FpSpread1.ActiveSheetView.Columns[4].Width = 100;
            FpSpread1.ActiveSheetView.Columns[5].Locked = true;
            FpSpread1.ActiveSheetView.Columns[5].Label = "小计";
            FpSpread1.ActiveSheetView.Columns[5].Width = 200;
            FpSpread1.ActiveSheetView.Columns[5].Formula = "C1 * D1 * E1";
            FpSpread1.ActiveSheetView.Columns[5].CellType = new CurrencyCellType();

            // 指定产品类别列的CellType
            DataSet ds = GetDataSource();
            FarPoint.Web.Spread.ComboBoxCellType ctCategory = new FarPoint.Web.Spread.ComboBoxCellType();
            ctCategory.DataSource = ds;
            ctCategory.DataMember = "Category";
            ctCategory.DataTextField = "Name";
            ctCategory.DataValueField = "ID";
            ctCategory.UseValue = true;
            ctCategory.OnClientChanged = "return CategoryChanged();";

            FpSpread1.ActiveSheetView.Columns[0].CellType = ctCategory;
        }
    }

前台 CategoryChanged 函数的实现代码：

<script type ="text/javascript" language="javascript">
        function CategoryChanged() {
            var row = FpSpread1.ActiveRow;
            var col = FpSpread1.ActiveCol;
            FpSpread1.EndEdit();
            FpSpread1.UpdatePostbackData();
            FpSpread1.CallBack("CategoryChanged," + row.toString() + "," + col.toString());
        }
    </script>

Spread ButtonCommand事件的后台处理代码，我们在该事件中取到选择的类别，然后显示该列的全部产品：

/// <summary>
    /// Spread ButtonCommand 事件的处理函数，根据e.CommandName的值来决定相应的处理逻辑
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void FpSpread1_ButtonCommand(object sender, FarPoint.Web.Spread.SpreadCommandEventArgs e)
    {
        switch (e.CommandName)
        {
            case "CategoryChanged":

                // 取到选择的产品类别，作为产品名称列的查询条件
                Point cell = (Point)e.CommandArgument;
                int categoryid = Convert.ToInt32(this.FpSpread1.ActiveSheetView.Cells[cell.X, cell.Y].Value.ToString());

                // 指定产品列相应单元格的CellType
                DataSet ds = GetDataSource();
                DataView product = ds.Tables["Products"].DefaultView;
                product.RowFilter = string.Format("CategoryID = {0}", categoryid);
                ComboBoxCellType ctProduct = new FarPoint.Web.Spread.ComboBoxCellType();        
                ctProduct.DataSource = product;
                ctProduct.DataTextField = "Name";
                ctProduct.DataValueField = "ID";
                ctProduct.UseValue = true;
                FpSpread1.ActiveSheetView.Cells[cell.X, cell.Y + 1].CellType = ctProduct;

                break;
            default:
                break;
        }

    }

运行截图：