一起来学用CSharp-c操作excel表.doc

一起来学用CSharp-c操作excel表分享来自网络。教你用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.Tables0; 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.dataGridView10, i.Value.ToString(); if (maxOID 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 0) this.dataGridView1.RowsselRowIndex - 3.Selected = true; this.lblTip.Text=删除成功; /更新记录 private void btnUpdate_Click(object sender, EventArgs e) int selRowIndex= this.dataGridView1.CurrentRow.Index; if (selRowIndex= 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.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();