Spread的常用事件

1   // 单元格编辑结束，焦点离开或者按回车键时                   比如：你要判断当前单元格输入内容是否合法。
   private void fpSpread1_EditModeOff(object sender, System.EventArgs e)                   
             

2   // 单元格得到焦点时触发                  
private void fpSpread1_EnterCell(object sender, FarPoint.Win.Spread.EnterCellEventArgs e)                   


   // 编辑单元格时触发，单元格内容发生改变
3   private void fpSpread1_EditChange(object sender, FarPoint.Win.Spread.EditorNotifyEventArgs e) 



4   // 编辑模式开始时触发
   private void fpSpread1_EditModeOn(object sender, System.EventArgs e) 

5   // 单元格失去焦点时触发
   private void fpSpread1_LeaveCell(object sender, FarPoint.Win.Spread.LeaveCellEventArgs e) 

6   // 双击Spread时触发          一般用来付值，比如把选中行内容传给其他控件
   private void fpSpread1_CellDoubleClick(object sender, FarPoint.Win.Spread.CellClickEventArgs e)          
       

7   // 单击Spread时触发          一般用来改变选中行的颜色
   private void fpSpread1_CellClick(object sender, FarPoint.Win.Spread.CellClickEventArgs e)          

8   // 一般用来检测输入内容是否合法，比如检测输入的内容是否是数字  
   private void fpSpread1_KeyPress(object sender, System.Windows.Forms.KeyPressEventArgs e)   


9   // 用来捕捉按钮列等属于按钮范畴的事件
   private void spdSetList_ButtonClicked(object sender, FarPoint.Win.Spread.EditorNotifyEventArgs e 


--------------------------------------------------------------------


                               Spread基本知识（一）

--------------------------------------------------------------------
1 取得当前行号、列号。int row=e.Row; 
int count=e.Count;
或者：
int rowindex = fpSpread1.ActiveSheet.ActiveRowIndex;
int columnindex = fpSpread1.ActiveSheet.ActiveColumnIndex;
 

2 单击一行变颜色。 

private void spdResult_CellClick(object sender, FarPoint.Win.Spread.CellClickEventArgs e)
  {
   //单击Spread列头时，什么也不处理

   if(!e.ColumnHeader)
   {
    if(spdResult.Sheets[0].Rows.Count!=0)
    {
     for(int i=0;i<spdResult.Sheets[0].Rows.Count;i++)
     {
       spdResult.Sheets[0].Rows[i].BackColor=System.Drawing.Color.White;
     }
     int row=e.Row;
      spdResult.Sheets[0].Rows[row].BackColor=System.Drawing.Color.FromArgb(((System.Byte)(192)), ((System.Byte)(255)), ((System.Byte)(255)));
    }
   }
  }
 

3 将Spread的单元格内容付值给一控件的Text

txtItemCD.Text = spdResult.Sheets[0].Cells[row,count].Text;
 

4 给Spread的指定单元格付值。

spdResult.Sheets[0].Cells[row,count].Text = txtItemCD.Text;
 

5 通过上下光标键改变选中行颜色

private void spdResult_LeaveCell(object sender, FarPoint.Win.Spread.LeaveCellEventArgs e)
  {
   //首先检查spread行数是否为0
   if(spdResult.Sheets[0].Rows.Count==0)
   {
    return;
   }
   else
   {
    for(int i=0;i<spdResult.Sheets[0].Rows.Count;i++)
    {
      spdResult.Sheets[0].Rows[i].BackColor=System.Drawing.Color.White;
    } 
    int row=e.NewRow;
     spdResult.Sheets[0].Rows[row].BackColor=System.Drawing.Color.FromArgb(((System.Byte)(192)), ((System.Byte)(255)), ((System.Byte)(255))); 
   }
  }
 

6 下拉列表加载数据（ComBobox） 
列表追加（适合于数据量少的情况）

FarPoint.Win.Spread.CellType.ComboBoxCellType cb4 = new FarPoint.Win.Spread.CellType.ComboBoxCellType();
    cb4.ListWidth = 96;
    cb4.Editable = true;
    cb4.MaxDrop = 10;
    cb4.MaxLength = 1;
   string[] priceTagList = new string[]{" 0 无"," 1 有"};
    cb4.Items = priceTagList;
   this.spdSetList.ActiveSheet.Columns[4].CellType = cb4;
 

从数据库追加

FarPoint.Win.Spread.CellType.ComboBoxCellType cb12 = new FarPoint.Win.Spread.CellType.ComboBoxCellType();
    cb12.ListWidth = 150;
    cb12.Editable = true;
    cb12.MaxDrop = 10;
    cb12.MaxLength = 8;

//dsEmployee：数据集Dataset,已经加载好数据的Dataset
   string[] employeeList = DataSetToArray(dsEmployee, 8);
    cb12.Items = employeeList;
   this.spdSetList.ActiveSheet.Columns[12].CellType = cb12;

private string[] DataSetToArray(DataSet ds, int BlankNum)
  {
   int i = 0;
   int NumLength = 0;
   string[] returnArray = new string[ds.Tables[0].Rows.Count];

   DataRow foundRows = ds.Tables[0].Rows[ds.Tables[0].Rows.Count -1];
    NumLength = foundRows[0].ToString().Length;

   foreach(DataRow dr in ds.Tables[0].Rows)
   {
     returnArray[i] = dr[0].ToString().PadLeft(BlankNum, ' ') + " " + dr[1].ToString();
     i++;
   }
   return returnArray;
  }
 

7 Focus移动（跨列）public frmProdSetDetail()
  {
    InitializeComponent();
    IsMod = flag;

    FarPoint.Win.Spread.InputMap im;
    im = spdResult.GetInputMap(InputMapMode.WhenFocused);
    im.Put(new Keystroke(Keys.Enter,Keys.None),SpreadActions.MoveToNextColumnWrap);
    im.Put(new Keystroke(Keys.Tab,Keys.None),SpreadActions.MoveToNextColumnWrap);

    im = spdResult.GetInputMap(InputMapMode.WhenAncestorOfFocused);
    im.Put(new Keystroke(Keys.Enter,Keys.None),SpreadActions.MoveToNextColumnWrap);
    im.Put(new Keystroke(Keys.Tab,Keys.None),SpreadActions.MoveToNextColumnWrap);
  }
 

指定单元格获得焦点      

this.fpSpread1.ActiveSheet.SetActiveCell(row，column);
 

8 事件触发顺序                            

_Enter     _EnterCell       _EditModeOn       _EditChange       _EditModeOff       _LeaveCell

9 用隐藏列保存原始数据  

10 设定列类型

private void SpreadSetting()
  {
   string[] ProductHandleTypeList = new string[]{" ","1 販売/仕入","2 販売","3 仕入"};
  
    FarPoint.Win.Spread.CellType.ComboBoxCellType cb3 = new FarPoint.Win.Spread.CellType.ComboBoxCellType();
    cb3.ListWidth = 100;
    cb3.Editable = true;
    cb3.MaxDrop = 5;
    cb3.MaxLength = 1;
    cb3.Items = ProductHandleTypeList;

   this.spdResult.ActiveSheet.Columns[5].CellType = cb3;

   //设置一般数据型 

    FarPoint.Win.Spread.CellType.NumberCellType nmbrcell = new FarPoint.Win.Spread.CellType.NumberCellType();
    nmbrcell.ShowSeparator = false;
    nmbrcell.DecimalPlaces = 0;
    nmbrcell.LeadingZero = FarPoint.Win.Spread.CellType.LeadingZero.UseRegional;
    nmbrcell.MaximumValue = 9999;
    nmbrcell.MinimumValue = 1;
   this.spdResult.ActiveSheet.Columns[13].CellType = nmbrcell;

   //设置JAN

    FarPoint.Win.Spread.CellType.NumberCellType nmbrcellJan = new FarPoint.Win.Spread.CellType.NumberCellType();
    nmbrcellJan.ShowSeparator = false;
    nmbrcellJan.DecimalPlaces = 0;
    nmbrcellJan.LeadingZero = FarPoint.Win.Spread.CellType.LeadingZero.UseRegional;
    nmbrcellJan.MaximumValue = 9999999999999;
    nmbrcellJan.MinimumValue = 0;
   this.fpSpread1.ActiveSheet.Columns[0].CellType = nmbrcellJan;
   this.fpSpread1.ActiveSheet.Columns[0].HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Left;

   //

    FarPoint.Win.Spread.CellType.NumberCellType numberCellType1 = new FarPoint.Win.Spread.CellType.NumberCellType();
    numberCellType1.ShowSeparator = true;
    numberCellType1.DecimalPlaces = 0;
    numberCellType1.LeadingZero = FarPoint.Win.Spread.CellType.LeadingZero.UseRegional;
    numberCellType1.MaximumValue = 99999999;
    numberCellType1.MinimumValue = 0;
   this.spdResult.ActiveSheet.Columns[20].CellType = numberCellType1;

   //%数的设定

    FarPoint.Win.Spread.CellType.PercentCellType prctcell = new FarPoint.Win.Spread.CellType.PercentCellType(); 
    prctcell.PercentSign = "%";
   this.spdResult.ActiveSheet.Columns[33].CellType = prctcell;
   
   //日期的设定

    FarPoint.Win.Spread.CellType.DateTimeCellType datecell = new FarPoint.Win.Spread.CellType.DateTimeCellType(); 
    datecell.MaximumDate = new System.DateTime(2050, 12, 31, 0, 0, 0, 0);
    datecell.MinimumDate = new System.DateTime(2001, 1, 1, 0, 0, 0, 0);

   this.spdResult.ActiveSheet.Columns[27,30].CellType = datecell;
}
 

11 列、单元格锁定

//锁定

this.fpSpread1.ActiveSheet.Columns[0,4].Locked = true;//锁定列范围

this.fpSpread1.ActiveSheet.Columns[0].Locked = true;//锁定单列


//解锁

this.fpSpread1.ActiveSheet.Columns[0,4].Locked = false;//解锁列范围

this.fpSpread1.ActiveSheet.Columns[0].Locked = false;
 

12 Spread追加行、列

//追加行

int rowindex = this.fpSpread1.ActiveSheet.Rows.Count;
   this.fpSpread1.ActiveSheet.Rows.Add(rowindex,1);

//追加列

int columnindex = this.fpSpread1.ActiveSheet.Columns.Count;
   this.fpSpread1.ActiveSheet.Columns.Add(columnindex,1);
 

13 行、列删除

//删除行

this.fpSpread1.ActiveSheet.Rows.Remove(startindex,count);

//追加列

this.fpSpread1.ActiveSheet.Columns.Remove(startindex,count);
 

14 button事件

private void spdSetList_ButtonClicked(object sender, FarPoint.Win.Spread.EditorNotifyEventArgs e)
  {
   int rowCount = e.Row;
   int columnCount = e.Column;

   if (e.Column == 16)
   {
    if (message.ShowMessage("ConfirmDelete", "選択したライン") == DialogResult.Yes)
    {
     string DeleteTag = this.spdSetList.ActiveSheet.Cells[rowCount, columnCount+1].Text.Trim();
     if (DeleteTag == "0" || DeleteTag == "2")
     {
      this.spdSetList.ActiveSheet.Cells[rowCount, columnCount+1].Text = "3";
      this.spdSetList.ActiveSheet.Cells[rowCount,columnCount+1].BackColor = clrWater;
      this.spdSetList.ActiveSheet.Rows[rowCount].Visible = false;
     }
     else if (DeleteTag == "1")
     {
      this.spdSetList.ActiveSheet.Rows[rowCount].Remove();
     }
    }
   }
  }
 
 


------------------------------------------------------------------

                        Spread 加载、插入、修改数据库-转载

---------------------------------------------------------------------

以下是代码（只有三个按钮的代码，其他代码略）：//修改

        private void btnUpdate_Click(object sender, System.EventArgs e)
        {
            //首先判断spread行数大于0

            if(fpSpread1.ActiveSheet.Rows.Count > 0)
            {
                
                 dbproc.Open();//打开数据库，这是我自己的连接数据库类，请替换你自己的连接数据库部分


                //循环更新

                for(int i=0;i<fpSpread1.ActiveSheet.Rows.Count;i++)
                {
                    string update = "update areas ";
                     update += " set areaName = '" + fpSpread1.ActiveSheet.Cells[i,2].Text.ToString().Trim() + "'";
                     update += " where areaCD = " + fpSpread1.ActiveSheet.Cells[i,0].Text.ToString().Trim();

                     dbproc.Execute(update);//更新数据库，这是我自己的更新数据库类，请替换你自己的连接数据库部分

                }
                 dbproc.Close();//关闭数据库，这是我自己的关闭数据库类，请替换你自己的连接数据库部分

            }
        }

        //加载数据

        private void btnLoad_Click(object sender, System.EventArgs e)
        {
             dbproc.Open();//打开数据库，这是我自己的连接数据库类，请替换你自己的连接数据库部分


            string select = "select * from areas where areaCD < 100";

            //定义一个Dataset

            DataSet ds= dbproc.OpenDataSet(select);//这是我自己的获取数据类，请替换你自己的获取数据库部分


            //绑定数据

             fpSpread1.ActiveSheet.DataSource = ds.Tables[0].DefaultView;

             dbproc.Close();//关闭数据库，这是我自己的关闭数据库类，请替换你自己的连接数据库部分

        }

        //插入

        private void btnInsert_Click(object sender, System.EventArgs e)
        {
            if(fpSpread1.ActiveSheet.Rows.Count > 0)
            {
                 dbproc.Open();//打开数据库，这是我自己的连接数据库类，请替换你自己的连接数据库部分

                for(int i=0;i<fpSpread1.ActiveSheet.Rows.Count;i++)
                {
                    string insert = "insert into areas ..";(具体sql语句略)
                    

                     dbproc.Execute(insert);//这是我自己的插入数据类，请替换你自己的插入数据库部分

                }
                 dbproc.Close();//关闭数据库，这是我自己的关闭数据库类，请替换你自己的连接数据库部分

            }        
        }
 


---------------------------------------------------------------------


                      解决使用spread时快捷键无反应的情况


-----------------------------------------------------------------------

如果我的画面中有快捷键，比如说“F1”、“F2”、“F3”等等，当你的焦点在spread里边时，你会发现你定义的那些快捷键有时候不反应，原因是焦点陷入spread里面，导致画面捕捉不到快捷键。FarPoint.Win.Spread.InputMap im;
            
            //spdResult是你画面中定义的spread的名字。

            //WhenFocused：焦点离开时

             im = spdResult.GetInputMap(InputMapMode.WhenFocused);

            //按回车键和TAB时，焦点移动到下一个单元格

             im.Put(new Keystroke(Keys.Enter,Keys.None),SpreadActions.MoveToNextColumnWrap);
             im.Put(new Keystroke(Keys.Tab,Keys.None),SpreadActions.MoveToNextColumnWrap);

            //按→、↓、←、↑时，焦点移动

             im.Put(new Keystroke(Keys.Left,Keys.None),SpreadActions.MoveToPreviousColumn);
             im.Put(new Keystroke(Keys.Right,Keys.None),SpreadActions.MoveToNextColumn);

            //使快捷键有效

             im.Put(new Keystroke(Keys.F1,Keys.None),SpreadActions.None);
             im.Put(new Keystroke(Keys.F2,Keys.None),SpreadActions.None);
             im.Put(new Keystroke(Keys.F3,Keys.None),SpreadActions.None);
             im.Put(new Keystroke(Keys.F8,Keys.None),SpreadActions.None);
             im.Put(new Keystroke(Keys.F10,Keys.None),SpreadActions.None);
             im.Put(new Keystroke(Keys.F12,Keys.None),SpreadActions.None);

            //WhenAncestorOfFocused：得到焦点时

             im = spdResult.GetInputMap(InputMapMode.WhenAncestorOfFocused);
             im.Put(new Keystroke(Keys.Enter,Keys.None),SpreadActions.MoveToNextColumnWrap);
             im.Put(new Keystroke(Keys.Tab,Keys.None),SpreadActions.MoveToNextColumnWrap);

             im.Put(new Keystroke(Keys.Left,Keys.None),SpreadActions.MoveToPreviousColumn);
             im.Put(new Keystroke(Keys.Right,Keys.None),SpreadActions.MoveToNextColumn);

             im.Put(new Keystroke(Keys.