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

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

2、stem.Data;using System.Drawing;using System.Text;usingusingusing System.Reflection;usingnamespace Leation public partial class FrmMain : Form /定义变量 private OleDbConnection connection = null; private OleDbCommand cmd = null; private OleDbDataAdapter dataAdapter = null; private DataSet dataSet = null;

3、 private string filePath = "G:points.xls" private string connStr = ; 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; priv

4、ate Excel.Workbook 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="" y=" /鼠标按下事件 private

5、 void splitContainer1_Panel2_MouseDown(object sender, MouseEventArgs e) if (e.Button = MouseButtons.Left) this this /刷新dataGridView1 private void RefreshTable() connection = new OleDbConnection(connStr); connection.Open(); dataAdapter = new OleDbDataAdapter(selectStr, connection); dataSet = new Data

6、Set(); dataAdapter.Fill(dataSet); this.dataGridView1.DataSource = dataSet.Tables0; connection.Close(); /程序加载事件，初始化dataGridView1 private void FrmMain_Load(object sender, EventArgs e) this.RefreshTable(); /获取一个可以用的OID private string GetOID() int rowNum = this int maxOID = 0; int temp = 0; for (int i =

7、 0; i < rowNum; i+) temp = int.Parse(this.dataGridView10, i.Value.ToString(); if (maxOID < temp) maxOID = temp; return (maxOID+1).ToString(); /插入一条记录，即保存一个点信息 private void btnSavePnt_Click(object sender, EventArgs e) OID = this.GetOID(); x = this.tbX.Text; y = this.tbY.Text; if (x = "&quo

8、t; | y = "") MessageBox.Show("x,y不能为空"); lblTip.Text = "保存失败" return; connection = new OleDbConnection(connStr); connection.Open(); cmdStr = "insert into Sheet1$(ID,X,Y) values(" + OID + "," + x + "," + y + ")" cmd = new OleDbComm

9、and(cmdStr, connection); int row=cmd.ExecuteNonQuery(); if (row > 0) lblTip.Text = "保存成功，插入行数：" + row.ToString(); else lblTip.Text = "保存失败" connection.Close(); this.RefreshTable(); /删除记录 private void btnDelSelRow_Click(object sender, EventArgs e) int selRowIndex = this/excel表中

10、的行索引与dataGridView不一样，这里注意 if (selRowIndex<1) MessageBox.Show("没有选中行"); lblTip.Text = "删除失败" return; excelApp = newApplication(); excelApp.Visible = false; /若为true，删除瞬间可以看见 office excel界面 /打开excel文件Missing.Value,false, Missing.Value, Missing.Value, Missing.Value, true, Missing.

11、Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); /获取sheet1 sheet = (Excel.Worksheet)book.Worksheets1; /获取编辑范围 range = (Excel.Range)sheet.RowsselRowIndex, Missing.Value; /删除整行 XlDeleteShiftDirection.xlShiftUp); /保存编辑 book.Save(); /关闭book

12、 book.Close(Missing.Value, Missing.Value, Missing.Value); /退出excel application，可以将前面的excelApp.Visible = false改为excelApp.Visible = true看看; excelApp.Quit(); /刷新dataGridView1 this.RefreshTable(); /选中删除行的上一行 if (selRowIndex - 3) > 0) this.dataGridView1.RowsselRowIndex - 3.Selected = true; this.lblTip

13、.Text="删除成功" /更新记录 private void btnUpdate_Click(object sender, EventArgs e) int selRowIndex= this if (selRowIndex< 0) MessageBox.Show("没有选中行!"); lblTip.Text = "更新失败" return; OID = this.dataGridView10, selRowIndex.Value.ToString(); x = this.tbX.Text; y = this.tbY.Text

14、; 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+"'" cmd =

15、 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 = "exce