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的单元格内容付值给一控件的TexttxtItemCD.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 _LeaveCell9 用隐藏列保存原始数据 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.