一起来学用CSharp

1、分享来自网络教你用C敝写、删除、更新excel表格记录如下列图所示，编一个程序，鼠标单击窗体视图区右边时，获取一对坐标X,Y),点击保存将点保存到excel表记录中。此外，还实现了删除、更新功能以及打开excel表功能。插入和更新比较简单，和操作一般的数据库一样，但是删除稍微有点复杂，不能用deletefromSheet1$whereID=x的方式删除，自己可以去试，主要是excel数据之间的关系不像关系数据库那么简单，oledb不提供这种方法。所以只能用专门操作excel表的Microsoft.Office.Interop.Excel名字空间下，先添加引用)来实现删除某条记录的功能。源代码：

2、usingSystem;usingSystem.Collections.Generic;usingSystem.ComponentModel;usingSystem.Data;usingSystem.Drawing;usingSystem.Text;usingSystem.Windows.Forms;usingSystem.Data.OleDb;usingSystem.Reflection;usingExcel=Microsoft.Office.Interop.Excel;namespaceLeationpublicpartialclassFrmMain:Form/定义变量privateOle

3、DbConnectionconnection=nullprivateOleDbCommancdmd=null;privateOleDbDataAdapterdataAdapter=null;privateDataSetdataSet=null;privatestringfilePath="G:points.xls”;privatestringconnStr="provider=microsoft.jet.oledb.4.0;datasource=G:points.xls;extendedproperties='Excel8.0;HDR=yes;IMEX=2'

4、"privatestringselectStr="select*fromSheet1$"privatestringcmdStr=null;privatestringOID=null;/对象IDprivatestringx=null;privatestringy=null;privateExcel.ApplicationexcelApp=null;privateExcel.Workbookbook=null;privateExcel.Worksheetsheet=null;privateExcel.Rangerange=null;/构造函数publicFrmMain

5、()InitializeComponent();/鼠标移动事件privatevoidsplitContainer1_Panel2_MouseMove(objectsender,MouseEventArgse)this.lblxy.Text="x="+e.X.ToString()+"y="+e.Y.ToString();/鼠标按下事件privatevoidsplitContainer1_Panel2_MouseDown(objectsender,MouseEventArgse)if(e.Button=MouseButtons.Left)this.tbX.T

6、ext=e.X.ToString();this.tbY.Text=e.Y.ToString();/刷新dataGridView1privatevoidRefreshTable()connection=newOleDbConnection(connStr);connection.Open();dataAdapter=newOleDbDataAdapter(selectStr,connection);dataSet=newDataSet();dataAdapter.Fill(dataSet);this.dataGridView1.DataSource=dataSet.Tables0;connect

7、ion.Close();/程序加载事件，初始化dataGridView1privatevoidFrmMain_Load(objectsender,EventArgse)this.RefreshTable();/获取一个可以用的OIDprivatestringGetOID()introwNum=this.dataGridView1.Rows.Count-1;intmaxOID=0;inttemp=0;for(inti=0;i<rowNum;i+)(temp=int.Parse(this.dataGridView10,i.Value.ToString();if(maxOID<temp)

8、(maxOID=temp;return(maxOID+1).ToString();/插入一条记录，即保存一个点信息privatevoidbtnSavePnt_Click(objectsender,EventArgse)OID=this.GetOID();x=this.tbX.Text;""y=this.tbY.Text;if(x=""|y=MessageBoxShow("x,y不能为空”);IblTip.Text="保存失败”;returnconnection=newOleDbConnection(connStr);+OID+&quo

9、t;,"+x+","+y+")"connection.Open();cmdStr="insertintoSheet1$(ID,X,Y)values("cmd=newOleDbComman(dcmdStr,connection);introw=cmd.ExecuteNonQuery();if(row>0)(lblTip.Text="保存成功，插入行数："+row.ToString();else(lblTip.Text="保存失败”；connection.Close();this.Refres

10、hTable();/删除记录privatevoidbtnDelSelRow_Click(objectsender,EventArgse)(intselRowIndex=this.dataGridViewl.CurrentRow.Index+2;/excel表中的行索引与dataGridView不一样，这里注意if(selRowIndex<1)(MessageBoxShow("没有选中行”);lblTip.Text="删除失败”；return;excelApp=newMicrosoft.Office.Interop.Excel.Application();excelAp

11、p.Visible=false;/假设为true，删除瞬间可以看见officeexcel界面/打开excel文件book=excelApp.Workbooks.Open(filePath,Missing.Value,false,Missing.Value,Missing.Value,Missing.Value,true,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);/获取sheetlsheet=(Excel.Work

12、sheet)book.Worksheets1;/获取编辑范围range=(Excel.Range)sheet.RowsselRowIndex,Missing.Value;/删除整行range.EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);/保存编辑book.Save();/关闭bookbook.Close(Missing.Value,Missing.Value,Missing.Value);/退出excelapplication，可以将前面的excelApp.Visible=false改为excelApp.Visible=tr

13、ue看看;excelApp.Workbooks.Close();excelApp.Quit();/刷新dataGridView1this.RefreshTable();/选中删除行的上一行if(selRowIndex-3)>0)this.dataGridView1.RowsselRowIndex-3.Selected=true;this.lblTip.Text="删除成功”；/更新记录privatevoidbtnUpdate_Click(objectsender,EventArgse)intselRowIndex=this.dataGridView1.CurrentRow.In

14、dex;if(selRowIndex<0)MessageBoxShow("没有选中行!");lblTip.Text="更新失败”；return;OID=this.dataGridView10,selRowIndex.Value.ToString();x=this.tbX.Text;y=this.tbY.Text;if(x=""|y="")MessageBoxShow("x,y不能为空”);lblTip.Text="更新失败”；return;connection=newOleDbConnection(

15、connStr);connection.Open();cmdStr="updateSheet1$setX="+x+",Y="+y+"whereID='"+OID+"'"cmd=newOleDbComman(dmdStr,connection);introw=cmd.ExecuteNonQuery();if(row>=1)lblTip.Text="更新成功，更新行数："+row.ToString();elselblTip.Text="更新失败"；conne

16、ction.Close();this.RefreshTable();/选中更新的行this.dataGridView1.RowsselRowIndex.Selected=true;privatevoidbtnOpenFile_Click(objectsender,EventArgse)OpenFileDialogofd=newOpenFileDialog();ofd.Filter="excel文件(*.xls)|*.xls"ofd.Title="代开excel表"；if(ofd.ShowDialog()=DialogResult.OK)(+filePath+"extendedthis.filePath=ofd.FileName;this.connStr="