教你用C读写删除更新excel表格记录.doc

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

2、码：using system;using system.collections.generic;using system.componentmodel;using system.data;using system.drawing;using system.text;using system.windows.forms;using system.data.oledb;using system.reflection;using excel = microsoft.office.interop.excel;namespace leation public partial class frmmain

3、: form /定义变量 private oledbconnection connection = null; private oledbcommand cmd = null; private oledbdataadapter dataadapter = null; private dataset dataset = null; private string filepath = "g:points.xls" private string connstr = "provider=microsoft.jet.oledb.4.0;data source=g:point

4、s.xls;extended properties='excel 8.0;hdr=yes;imex=2'" private string selectstr = "select * from sheet1$" private string cmdstr = null; private string oid = null; /对象id private string x = null; private string y = null; private excel.application excelapp = null; private excel.wo

5、rkbook book = null; private excel.worksheet sheet = null; private excel.range range = null; /构造函数 public frmmain() initializecomponent(); /鼠标移动事件 private void splitcontainer1_panel2_mousemove(object sender, mouseeventargs e) this.lblxy.text = "x=" + e.x.tostring() + " y=" + e.y.t

6、ostring(); /鼠标按下事件 private void splitcontainer1_panel2_mousedown(object sender, mouseeventargs e) if (e.button = mousebuttons.left) this.tbx.text = e.x.tostring(); this.tby.text = e.y.tostring(); /刷新datagridview1 private void refreshtable() connection = new oledbconnection(connstr); connection.open(

7、); dataadapter = new oledbdataadapter(selectstr, connection); dataset = new dataset(); dataadapter.fill(dataset); this.datagridview1.datasource = dataset.tables0; connection.close(); /程序加载事件，初始化datagridview1 private void frmmain_load(object sender, eventargs e) this.refreshtable(); /获取一个可以用的oid priv

8、ate string getoid() int rownum = this.datagridview1.rows.count - 1; int maxoid = 0; int temp = 0; for (int i = 0; i < rownum; i+) temp = int.parse(this.datagridview10, i.value.tostring(); if (maxoid < temp) maxoid = temp; return (maxoid+1).tostring(); /插入一条记录，即保存一个点信息 private void btnsavepnt_c

9、lick(object sender, eventargs e) oid = this.getoid(); x = this.tbx.text; y = this.tby.text; if (x = "" | y = "") messagebox.show("x,y不能为空"); lbltip.text = "保存失败" return; connection = new oledbconnection(connstr); connection.open(); cmdstr = "insert into s

10、heet1$(id,x,y) values(" + oid + "," + x + "," + y + ")" cmd = new oledbcommand(cmdstr, connection); int row=cmd.executenonquery(); if (row > 0) lbltip.text = "保存成功，插入行数：" + row.tostring(); else lbltip.text = "保存失败" connection.close(); this.re

11、freshtable(); /删除记录 private void btndelselrow_click(object sender, eventargs e) int selrowindex = this.datagridview1.currentrow.index + 2; /excel表中的行索引与datagridview不一样，这里注意 if (selrowindex<1) messagebox.show("没有选中行"); lbltip.text = "删除失败" return; excelapp = new microsoft.offic

12、e.interop.excel.application(); excelapp.visible = false; /若为true，删除瞬间可以看见 office excel界面 /打开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.valu

13、e, missing.value, missing.value); /获取sheet1 sheet = (excel.worksheet)book.worksheets1; /获取编辑范围 range = (excel.range)sheet.rowsselrowindex, missing.value; /删除整行 range.entirerow.delete(excel.xldeleteshiftdirection.xlshiftup); /保存编辑 book.save(); /关闭book book.close(missing.value, missing.value, missing.

14、value); /退出excel application，可以将前面的excelapp.visible = false改为excelapp.visible = true看看; excelapp.workbooks.close(); excelapp.quit(); /刷新datagridview1 this.refreshtable(); /选中删除行的上一行 if (selrowindex - 3) > 0) this.datagridview1.rowsselrowindex - 3.selected = true; this.lbltip.text="删除成功"

15、 /更新记录 private void btnupdate_click(object sender, eventargs e) int selrowindex= this.datagridview1.currentrow.index; if (selrowindex< 0) messagebox.show("没有选中行!"); lbltip.text = "更新失败" return; oid = this.datagridview10, selrowindex.value.tostring(); x = this.tbx.text; y = thi

16、s.tby.text; if (x = "" | y = "") messagebox.show("x,y不能为空"); lbltip.text = "更新失败" return; connection = new oledbconnection(connstr); connection.open(); cmdstr = "update sheet1$ set x="+x+",y="+y+" where id='"+oid+"'&q

17、uot; cmd = new oledbcommand(cmdstr, connection); int row = cmd.executenonquery(); if (row >= 1) lbltip.text = "更新成功，更新行数：" + row.tostring(); else lbltip.text = "更新失败" connection.close(); this.refreshtable(); /选中更新的行 this.datagridview1.rowsselrowindex.selected = true; private void btnopenfile_click(object sender, eventargs e) openfiledialog ofd = new openfiledialog(); ofd.filter = "excel文件(*.xls)|*.xls" ofd.title = "代开excel表" if (ofd