教你用C#读写、删除、更新excel表格记录
如下图所示，编一个程序，鼠标单击窗体视图区（右边）时，获取一对坐标（X,Y)，点击保存将点保存到excel表记录中。此外，还实现了删除、更新功能以及打开excel表功能。插入和更新比较简单，和操作一般的数据库一样，但是删除稍微有点复杂，不能用delete from [Sheet1$] where ID=x的方式删除，自己可以去试，主要是excel数据之间的关系不像关系数据库那么简单，oledb不提供这种方法。所以只能用专门操作excel表的（Microsoft.Office.Interop.Excel名字空间下，先添加引用)来实现删除某条记录的功能。
源代码：
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 : 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:\\points.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.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=" + e.X.ToString() + " y=" + e.Y.ToString();
}
//鼠标按下事件
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();
dataAdapter = new OleDbDataAdapter(selectStr, connection);
dataSet = new DataSet();
dataAdapter.Fill(dataSet);
this.dataGridView1.DataSource = dataSet.Tables[0];
connection.Close();
}
//程序加载事件，初始化dataGridView1
private void FrmMain_Load(object sender, EventArgs e)
{
this.RefreshTable();
}
//获取一个可以用的OID
private 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.dataGridView1[0, 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 == "" || 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 OleDbCommand(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.dataGridView1.CurrentRow.Index + 2; //excel表中的行索引与dataGridView不一样，这里注意
if (selRowIndex<1)
{
MessageBox.Show("没有选中行");
lblTip.Text = "删除失败";
return;
}
excelApp = new Microsoft.Office.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.Value, Missing.Value, Missing.Value);
//获取sheet1
sheet = (Excel.Worksheet)book.Worksheets[1];
//获取编辑范围
range = (Excel.Range)sheet.Rows[selRowIndex, Missing.Value];
//删除整行
range.EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
//保存编辑
book.Save();
//关闭book
book.Close(Missing.Value, Missing.Value, Missing.Value);
//退出excel application，可以将前面的excelApp.Visible = false改为excelApp.Visible = true看看;
excelApp.Workbooks.Close();
excelApp.Quit();
//刷新dataGridView1
this.RefreshTable();
//选中删除行的上一行
if ((selRowIndex - 3) > 0)
{
this.dataGridView1.Rows[selRowIndex - 3].Selected = true;
}
this.lblTip.Text="删除成功";
}
//更新记录
private void btnUpdate_Click(object sender, EventArgs e)
{
int selRowIndex= this.dataGridView1.CurrentRow.Index;
if (selRowIndex< 0)
{
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 = new OleDbConnection(connStr);
connection.Open();
cmdStr = "update [Sheet1$] set X="+x+",Y="+y+" where ID='"+OID+"'";
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.Rows[selRowIndex].Selected = true;
}
private void btnOpenFile_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
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;data source=" + filePath + ";extended properties='Excel 8.0;HDR=yes;IMEX=2'";
this.RefreshTable();
}
}
}
}