第1页 / 共6页
第2页 / 共6页
第3页 / 共6页
第4页 / 共6页
第5页 / 共6页
第6页 / 共6页
c#读写excel.doc
教你用 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'";
//对象ID
private string selectStr = "select * from [Sheet1$]";
private string cmdStr = null;
private string OID = null;
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;
//打开excel文件
book = excelApp.Workbooks.Open(filePath, Missing.Value,false, Missing.Value, Missing.Value,
//若为true，删除瞬间可以看见 office excel界面
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();
}
}
}
}
相关推荐
2023年江西萍乡中考道德与法治真题及答案.doc
2012年重庆南川中考生物真题及答案.doc
2013年江西师范大学地理学综合及文艺理论基础考研真题.doc
2020年四川甘孜小升初语文真题及答案I卷.doc
2020年注册岩土工程师专业基础考试真题及答案.doc
2023-2024学年福建省厦门市九年级上学期数学月考试题及答案.doc
2021-2022学年辽宁省沈阳市大东区九年级上学期语文期末试题及答案.doc
2022-2023学年北京东城区初三第一学期物理期末试卷及答案.doc
2018上半年江西教师资格初中地理学科知识与教学能力真题及答案.doc
2012年河北国家公务员申论考试真题及答案-省级.doc
2020-2021学年江苏省扬州市江都区邵樊片九年级上学期数学第一次质量检测试题及答案.doc
2022下半年黑龙江教师资格证中学综合素质真题及答案.doc
