更多文章参见:
http://hi.csdn.net/xjzdr/profile
http://blog.csdn.net/hicsdn.aspx?username=xjzdr
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;
namespace 操作EXCEL
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void btnQuery_Click(object sender, EventArgs e)
{
System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/a.xls;Extended Properties=Excel 8.0;");
DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", Conn);//删除语句没搞定,有时间了继续测试。。。
try
{
da.Fill(ds);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
Conn.Close();
return;
}
dataGridView1.DataSource = ds.Tables[0];
Conn.Close();
}
private void Form1_Load(object sender, EventArgs e)
{
}
private void btnUpdate_Click(object sender, EventArgs e)
{
System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/a.xls;Extended Properties=Excel 8.0;");
DataSet ds = new DataSet();
string strSql = "update [sheet1$] set b=111";//将写入所有有数据的行,b是列标题名(测试通过)
strSql = "update [sheet1$] set b=123 where a=111";//只写入影响一行数据。(测试通过)
Conn.Open();
OleDbCommand cmd = new OleDbCommand(strSql, Conn);
cmd.ExecuteNonQuery();
Conn.Close();
}
private void btnInsert_Click(object sender, EventArgs e)
{
System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/a.xls;Extended Properties=Excel 8.0;");
DataSet ds = new DataSet();
string strSql = "insert into [sheet1$](a,b) values(123,456)";//写入新的一行数据,a,b是列名(测试通过)
strSql = "insert into [sheet1$] values(123,456,765,890)";//本句中值的个数必须和EXCEL表格中的列数相同,否则报错。(测试通过,本例中EXCEL共有4列)
strSql = "insert into [sheet1$](b,c) values(123,456)";//只插第二三列(测试通过)
// strSql = "insert into [sheet1$B3:C8] VALUES(1,2)"; //测试时可能会报错,超出界限
Conn.Open();
OleDbCommand cmd = new OleDbCommand(strSql, Conn);
cmd.ExecuteNonQuery();
Conn.Close();
}
//删除的没搞定,有时间再继续弄
private void btnDel_Click(object sender, EventArgs e)
{
System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/a.xls;Extended Properties=Excel 8.0;");
DataSet ds = new DataSet();
string strSql = "delete * from [sheet1$]";//写入新的一行数据,a,b是列名(测试通过)
//DELETE Sheet1.编号, Sheet1.姓名 FROM Sheet1 WHERE Sheet1.编号 in (select sheet2.编号 from sheet2)
Conn.Open();
OleDbCommand cmd = new OleDbCommand(strSql, Conn);
cmd.ExecuteNonQuery();
Conn.Close();
}
}
}