实例补充之完善Excel删除数据及数据更新功能,终于搞定了,传ID去到Excel中找值好像有点很难,于是情绪稳定后,突然想到,为啥不用索引呢,再于是乎,用索引就可以了。接着把后的更新功能也加了上去,只要功夫深,铁棒棒磨成针,还是坚持的好,终有成功日。以下是原代码,希望有抛砖引玉之效用:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Text.RegularExpressions;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;//引用才能使用Missing字段
namespace WF29
{
public partial class Form1 : Form
{
//定义一个连接字符串,sql的很常用,我就整个不常用的Excel2007
static string Dir = "D:\\ZERO";
static string fileName = "db.xlsx";
//郑重说明,HDR参数是标记是否将首行作为标题行,IMEX这个参数,
//网上大多的说法是,0是汇出,1是汇入,2是可读写,但是我被整整折磨了3天。最后确定是0值是可读可写,还是菜啊,咋就不试试呢?SHIT!
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Dir + "\\" + fileName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=0;'";
//Microsoft.Jet.OLEDB.4.0 , Microsoft.ACE.OLEDB.12.0这两个引擎都可以用在access的数据读取上。
string astr = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=D:\onedb.mdb";
public Form1()
{
//窗体初始化
InitializeComponent();
}
/// <summary>
/// 闪退
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button3_Click(object sender, EventArgs e)
{
Application.Exit();
}
/// <summary>
/// 窗体加载
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Form1_Load(object sender, EventArgs e)
{
RefreshL();
}
/// <summary>
/// 刷新ListBox对象中的数据
/// </summary>
private void RefreshL()
{
listBox1.Items.Clear();
listBox2.Items.Clear();
// 数据查寻字符串
string esql = "select * from [Sheet1$]";
string s = "select * from products";
//为ListBox控件内默认添加些内容进去
using (OleDbConnection ocon = new OleDbConnection(strConn))
{
//建立好连接对象
//接下来创建命令对象
OleDbCommand ocmd = new OleDbCommand(esql, ocon);
//定义命令对象的类型,其实这里是多次一举,只是练手的
ocmd.CommandType = CommandType.Text;
//打开连接对象
ocon.Open();
//打开数据流这个水笼头,加了个参数,意思是当关闭掉水笼头时同时关闭连接对象
OleDbDataReader odr = ocmd.ExecuteReader(CommandBehavior.CloseConnection);
//读取数据
while (odr.Read())
{
listBox1.Items.Add(odr["excelId"].ToString() + " : " + odr["excelName"].ToString());
}
odr.Close();
}
using (OleDbConnection acon = new OleDbConnection(astr))
{
OleDbCommand acmd = new OleDbCommand(s, acon);
acmd.CommandType = CommandType.Text;
acon.Open();
OleDbDataReader odr = acmd.ExecuteReader(CommandBehavior.CloseConnection);
while (odr.Read())
{
listBox2.Items.Add(
odr["ID"].ToString() + "___" +
odr["bname"].ToString() + "___" +
odr["price"].ToString() + "___" +
odr["classid"].ToString()
);
}
odr.Close();
}
}
/// <summary>
/// 添加数据到Excel表中
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
string excelId = tbId.Text.Trim();//获取要加入的项ID
string esql = "insert into [Sheet1$](excelId,excelName) values(" + excelId + "," + tbName.Text.Trim() + ")";
string esql2 = "insert into [Sheet1$](excelId,excelName) values('" + excelId + "','" + tbName.Text.ToString().Trim() + "')";
//string esql3 = "insert into [Sheet1$](excelId,excelName) Values('1234','测试')";
//string esql4 = "Insert into [Sheet1$](excelId,excelName) values('1235','Thomas')";
if (idExists(excelId, strConn, "[Sheet1$]"))
{
//先获取用户输入匹配的数据
string s = @"^\d*\b";
Regex reg = new Regex(s);
if (reg.IsMatch(tbId.Text.Trim()))
{
InsertData(esql2,strConn);
}
else
{
MessageBox.Show("请为ID输入数字!");
}
RefreshL();
}
else {
MessageBox.Show("此ID"+excelId+"已经存在!");
}
}
/// <summary>
/// 插入数据的操作---公共方法,后的更新操作也可以提取一下
/// </summary>
/// <param name="esql2"></param>
private void InsertData(string esql2,string DataConn)
{
using (OleDbConnection ocon = new OleDbConnection(DataConn))
{
OleDbCommand ocmd = new OleDbCommand(esql2, ocon);
ocmd.CommandType = CommandType.Text;
ocon.Open();
int ok =(int) ocmd.ExecuteNonQuery(); //一直在报错,"至少一个参数没有被指定值。","操作必须使用一个可更新的查询"
if (ok > 0)
{
MessageBox.Show("添加成功了!");
}
else
{
MessageBox.Show("添加失败了!");
}
}
}
/// <summary>
/// 判断一个id是否存在某个表中,这里主要是判断ACCESS和EXCLE类的表
/// 接下要判断这个ID在表中是不是已经存在呢
/// </summary>
/// <param name="id">以表中的字段名为变量的ID</param>
/// <param name="DataConn">连接字符串</param>
/// <param name="TableName">访问的表名</param>
/// <returns>返回布尔值</returns>
private bool idExists(string id, string DataConn, string TableName)
{
bool Exs = false;
if (DataConn == strConn)//通过连接字符串来判断主键ID
{
string excelId = id;
string sql = "select Count(" + excelId + ") from " + TableName + " where excelId='" + excelId+"'";
using (OleDbConnection ocon = new OleDbConnection(DataConn))
{
OleDbCommand ocmd = new OleDbCommand(sql, ocon);
ocmd.CommandType = CommandType.Text;
ocon.Open();
int ok = (int)ocmd.ExecuteScalar(); //获取首行首列的值
if (ok > 0)
{
Exs = false;
}
else
{
Exs = true;
}
}
}
else {
int ID = int.Parse(id);
string sql = "select Count(" + ID + ") from " + TableName + " where ID=" + ID;
using (OleDbConnection ocon = new OleDbConnection(DataConn))
{
OleDbCommand ocmd = new OleDbCommand(sql, ocon);
ocmd.CommandType = CommandType.Text;
ocon.Open();
int ok =(int) ocmd.ExecuteScalar(); //获取首行首列的值
if (ok > 0)
{
Exs = false;
}
else
{
Exs = true;
}
}
}
return Exs;
}
/// <summary>
/// 将选中项添加到文本框中
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
{
SyncData();
}
/// <summary>
/// 提取的选中项添加到文本框中的方法,只能满足ListBox1对象
/// 徒劳了,无法共用
/// </summary>
private void SyncData()
{
if (listBox1.SelectedItems.Count > 0)
{
int i = listBox1.Text.IndexOf(" ");
string id = listBox1.Text.Substring(0, i);
string esql = "select * from [Sheet1$] where excelId='" + id+"'";
using (OleDbConnection ocon = new OleDbConnection(strConn))
{
OleDbCommand ocmd = new OleDbCommand(esql, ocon);
ocmd.CommandType = CommandType.Text;
ocon.Open();
OleDbDataReader odr = ocmd.ExecuteReader(CommandBehavior.CloseConnection);
while (odr.Read())
{
tbId.Text = odr["excelId"].ToString();
tbName.Text = odr["excelName"].ToString();
}
odr.Close();
}
}
}
/// <summary>
/// Listbox2中将选中项数据显示到文本框中
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void listBox2_SelectedIndexChanged(object sender, EventArgs e)
{
SyncData2();
}
/// <summary>
/// 提取的选中项添加到文本框中的方法,只能满足ListBox2对象
/// </summary>
private void SyncData2()
{
if (listBox2.SelectedItems.Count > 0)
{
int i = listBox2.Text.IndexOf("___");
string id = listBox2.Text.Substring(0, i);
string esql = "select * from products where ID=" + id;
using (OleDbConnection ocon = new OleDbConnection(astr))
{
OleDbCommand ocmd = new OleDbCommand(esql, ocon);
ocmd.CommandType = CommandType.Text;
ocon.Open();
OleDbDataReader odr = ocmd.ExecuteReader(CommandBehavior.CloseConnection);
while (odr.Read())
{
tbeId.Text = odr["ID"].ToString();
tbeName.Text = odr["bname"].ToString();
tbePrice.Text = odr["price"].ToString();
tbeClass.Text = odr["classid"].ToString();
}
odr.Close();
}
}
}
/// <summary>
/// 向ListBox2中添加数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btneAdd_Click(object sender, EventArgs e)
{
string ID = tbeId.Text.Trim();
if (idExists(ID, astr, "products"))
{
string sql = "insert into products(ID,bname,price,classid) values(" + Convert.ToInt32(tbeId.Text.Trim()) + ",'" + tbeName.Text.Trim() + "'," + Convert.ToSingle(tbePrice.Text.Trim()) + "," + Convert.ToInt32(tbeClass.Text.Trim()) + ")";
//先获取用户输入匹配的数据
string s = @"^\d*\b";
Regex reg = new Regex(s);
if (reg.IsMatch(tbeId.Text.Trim()))
{
//调用的公用方法,插入数据
InsertData(sql, astr);
}
else
{
MessageBox.Show("请为ID输入数字!");
}
RefreshL();
}
else {
MessageBox.Show("此ID" + ID + "已经存在!");
}
}
//下面这几个变量还没有开始玩,先注释一下。好像更牛逼的样子,可以操作表中的单元格的
private Excel.Application excelApp = null;
private Excel.Workbook book = null;
private Excel.Worksheet sheet = null;
private Excel.Range range = null;
/// <summary>
/// Excel 表中删除选中项
/// 好似Excel不支持删除数据哦
/// 另想办法,已经解决
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button2_Click(object sender, EventArgs e)
{
#region 不可行的代码
//if (listBox1.SelectedItems.Count > 0)
//{
// //如果有选中项则删掉它
// int i = listBox1.Text.IndexOf(" ");
// string id = listBox1.Text.Substring(0, i);
// string esql = "delete from [Sheet1$] where excelId='" + id + "'";
// using (OleDbConnection ocon = new OleDbConnection(strConn))
// {
// OleDbCommand ocmd = new OleDbCommand(esql, ocon);
// ocmd.CommandType = CommandType.Text;
// ocon.Open();
// int ok = ocmd.ExecuteNonQuery();
// if (ok > 0)
// {
// MessageBox.Show("删除数据成功");
// }
// else {
// MessageBox.Show("删除数据失败");
// }
// }
// RefreshL();
//}
#endregion
int id = listBox1.SelectedIndex;//获取选中项在ListBox中的索引
int count = listBox1.Items.Count;//删除数据前项数
excelApp = new Excel.Application();//实例化一个Excel程序对象
excelApp.Visible = false;//设置程序界面为不可见
book = excelApp.Workbooks.Open("D:\\ZERO\\db.xlsx"
, 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);//打开并获取工作薄
sheet =(Excel.Worksheet) book.Worksheets.get_Item(1);//引用一个表
range =(Excel.Range) sheet.Rows[id, Missing.Value];//获取范围
range.EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);//删除一行数据
book.Save();//保存操作
book.Close(Missing.Value, Missing.Value, Missing.Value);//关闭工作薄
excelApp.Workbooks.Close();//关闭工作薄集合
excelApp.Quit();//退出程序界面
RefreshL();//刷新数据
if (count - listBox1.Items.Count == 1)
{
MessageBox.Show("删除数据成功");
}
}
/// <summary>
/// Access表中删除选中项
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btneDel_Click(object sender, EventArgs e)
{
int i = listBox2.Text.IndexOf("___");
string id = listBox2.Text.Substring(0, i);
string esql = "delete from products where ID=" + id;
if (listBox2.SelectedItems.Count > 0)
{
using (OleDbConnection ocon = new OleDbConnection(astr))
{
OleDbCommand ocmd = new OleDbCommand(esql, ocon);
ocmd.CommandType = CommandType.Text;
ocon.Open();
int ok = ocmd.ExecuteNonQuery();
if (ok > 0)
{
MessageBox.Show("删除数据成功");
}
else
{
MessageBox.Show("删除数据失败");
}
}
}
RefreshL();
}
/// <summary>
/// 更新ListBox1中的数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button4_Click(object sender, EventArgs e)
{
//获取要修改的项ID和名字
string id = tbId.Text.Trim();
string name = tbName.Text.Trim();
string sql = "update [Sheet1$] set excelName='" + name + "' where excelId='" + id + "'";
using (OleDbConnection ocon = new OleDbConnection(strConn))
{
OleDbCommand ocmd = new OleDbCommand(sql, ocon);
ocmd.CommandType = CommandType.Text;
ocon.Open();
int ok = ocmd.ExecuteNonQuery();
if (ok > 0)
{
MessageBox.Show("更新数据成功");
}
else {
MessageBox.Show("更新数据失败");
}
}
RefreshL();
}
/// <summary>
///更新ListBox2中的数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button5_Click(object sender, EventArgs e)
{
//获取用户输入
int id = Convert.ToInt32(tbeId.Text.Trim());
string name = tbeName.Text.Trim();
float price = Convert.ToSingle(tbePrice.Text.Trim());
int cid = Convert.ToInt32(tbeClass.Text.Trim());
string sql = "update products set bname='" + name + "',price=" + price + ",classid=" + cid + " where ID=" + id;
using (OleDbConnection ocon = new OleDbConnection(astr))
{
OleDbCommand ocmd = new OleDbCommand(sql, ocon);
ocmd.CommandType = CommandType.Text;
ocon.Open();
int ok = ocmd.ExecuteNonQuery();
if (ok > 0)
{
MessageBox.Show("更新数据成功");
}
else
{
MessageBox.Show("更新数据失败");
}
}
RefreshL();
}
测试结果,GIF图片太大,传不上来,各位亲拷贝代码自行测试一下。