文章出处：http://blog.csdn.net/kuangshazi515/article/details/6585118                                                                                                                            

如下图所示，编一个程序，鼠标单击窗体视图区（右边）时，获取一对坐标（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();

            }

        }

    }

}