教你用C#读写、删除、更新excel表格记录

教你用C#读写、删除、更新excel表格记录如下图所示，编一个程序，鼠标单击窗体视图区（右边）时，获取一对坐标（X,Y)，点击保存将点保存到excel表记录中。此外，还实现了删除、更新功能以及打开excel表功能。插入和更新比较简单，和操作一般的数据库一样，但是删除稍微有点复杂，不能用deletefrom[Sheet1$]whereID=x的方式删除，自己可以去试，主要是excel数据之间的关系不像关系数据库那么简单，oledb不提供这种方法。所以只能用专门操作excel表的（Microsoft.Office.Interop.Excel名字空间下，先添加引用)来实现删除某条记录的功能。源代码：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;namespaceLeation{publicpartialclassFrmMain:Form{//定义变量privateOleDbConnectionconnection=null;privateOleDbCommandcmd=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';privatestringselectStr=select*from[Sheet1$];privatestringcmdStr=null;privatestringOID=null;//对象IDprivatestringx=null;privatestringy=null;privateExcel.ApplicationexcelApp=null;privateExcel.Workbookbook=null;privateExcel.Worksheetsheet=null;privateExcel.Rangerange=null;//构造函数publicFrmMain(){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.Text=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.Tables[0];connection.Close();}//程序加载事件，初始化dataGridView1privatevoidFrmMain_Load(objectsender,EventArgse){this.RefreshTable();}//获取一个可以用的OIDprivatestringGetOID(){introwNum=this.dataGridView1.Rows.Count-1;intmaxOID=0;inttemp=0;for(inti=0;irowNum;i++){temp=int.Parse(this.dataGridView1[0,i].Value.ToString());if(maxOIDtemp){maxOID=temp;}}return(maxOID+1).ToString();}//插入一条记录，即保存一个点信息privatevoidbtnSavePnt_Click(objectsender,EventArgse){OID=this.GetOID();x=this.tbX.Text;y=this.tbY.Text;if(x==||y==){MessageBox.Show(x,y不能为空);lblTip.Text=保存失败;return;}connection=newOleDbConnection(connStr);connection.Open();cmdStr=insertinto[Sheet1$](ID,X,Y)values(+OID+,+x+,+y+);cmd=newOleDbCommand(cmdStr,connection);introw=cmd.ExecuteNonQuery();if(row0){lblTip.Text=保存成功，插入行数：+row.ToString();}else{lblTip.Text=保存失败;}connection.Close();this.RefreshTable();}//删除记录privatevoidbtnDelSelRow_Click(objectsender,EventArgse){intselRowIndex=this.dataGridView1.CurrentRow.Index+2;//excel表中的行索引与dataGridView不一样，这里注意if(selRowIndex1){MessageBox.Show(没有选中行);lblTip.Text=删除失败;return;}excelApp=newMicrosoft.Office.Interop.Excel.Application();excelApp.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);//获取sheet1sheet=(Excel.Worksheet)book.Worksheets[1];//获取编辑范围range=(Excel.Range)sheet.Rows[selRowIndex,Missing.Value];//删除整行range.EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);//保存编辑book.Save();//关闭bookbook.Close(Missing.Value,Missing.Value,Missing.Value);//退出excelapplication，可以将前面的excelApp.Visible=false改为excelApp.Visible=true看看;excelApp.Workbooks.Close();excelApp.Quit();//刷新dataGridView1this.RefreshTable();//选中删除行的上一行if((selRowIndex-3)0){this.dataGridView1.Rows[selRowIndex-3].Selected=true;}this.lblTip.Text=删除成功;}//更新记录privatevoidbtnUpdate_Click(objectsender,EventArgse){intselRowIndex=this.dataGridView1.CurrentRow.Index;if(selRowIndex0){MessageBox.Show(没有选中行!);lblTip.Text=更新失败;return;}OID=this.dataGridView1[0,selRowIndex].Value.ToString();x=this.tbX.Text;y=this.tbY.Text;if(x==||y==){MessageBox.Show(x,y不能为空);lblTip.Text=更新失败;return;}connection=newOleDbConnection(connStr);connection.Open();cmdStr=update[Sheet1$]setX=+x+,Y=+y+whereID='+OID+';cmd=newOleDbCommand(cmdStr,connection);introw=cmd.ExecuteNonQuery();if(row=1){lblTip.Text=更新成功，更新行数：+row.ToString();}else{lblTip.Text=更新失败;}connection.Close();this.RefreshTable();//选中更新的行this.dataGridView1.Rows[selRowIndex].Selected=true;}privatevoidbtnOpenFile_Click(objectsender,EventArgse){OpenFileDialogofd=newOpenFileDialog();ofd.Filter=excel文件(*.xls)|*.xls;ofd.Title=代开excel表;if(ofd.ShowDialog()==DialogResult.OK){this.filePath=ofd.FileName;this.connStr=provider=microsoft.jet.oledb.4.0;datasource=+filePath+;extendedproperties='Excel8.0;HDR=yes;IMEX=2';this.RefreshTable();}}}}