c#处理EXCEL操作包括查询以及增改

1、c#处理EXCEL操作包括查询以及增改usingSystem;usingSystem.Collections.Generic;usingSystem.ComponentModel;usingSystem.Data;usingSystem.Drawing;usingSystem.Text;usingSystem.Windows.Forms;usingSystem.Data.OleDb;namespace操彳EXCELpublicpartialclassForm1:FormpublicForm1()InitializeComponent();privatevoidbtnQuery_Click(ob

2、jectsender,EventArgse)System.Data.OleDb.OleDbConnection(Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:a.xls;ExtendedProperties=Excel8.0;);DataSetds=newDataSet();OleDbDataAdapterda=newOleDbDataAdapter(SELECT*FROMSheet1$,Conn);/删除语句没搞定，有时间了继续测试。tryda.Fill(ds);catch(Exceptionex)MessageBox.Show(ex.Messa

3、ge);Conn.Close();return;dataGridViewl.DataSource=ds.Tables0;Conn.Close();privatevoidForm1_Load(objectsender,EventArgse)(privatevoidbtnUpdate_Click(objectsender,EventArgse)(System.Data.OleDb.OleDbConnectionConn=newSystem.Data.OleDb.OleDbConnection(Provider=Microsoft.JetQLEDB.4.0;DataSource=C:a.xls;Ex

4、tendedProperties=Excel8.0;);System.Data.OleDb.OleDbConnectionConnnewDataSetds=newDataSet();stringstrSql=updatesheet1$setb=111;/将写入所有有数据的行，b是列标题名(测试通过)strSql=updatesheet1$setb=123wherea=111;/只写入影响一行数据。(测试通过)Conn.Open();OleDbCommandcmd=newOleDbCommand(strSql,Conn);cmd.ExecuteNonQuery();Conn.Close();pr

5、ivatevoidbtnInsert_Click(objectsender,EventArgse)(System.Data.OleDb.OleDbConnectionConn=newSystem.Data.OleDb.OleDbConnection(Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:a.xls;ExtendedProperties=Excel8.0;);DataSetds=newDataSet();stringstrSql=insertintosheet1$(a3,b3)values(123,456);写入新的一行数据,a3,b3是第一

6、行单元格的值(测试通过)strSql=insertintosheet1$values(123,456,765,890);本句中值的个数必须和EXCEL表格中的列数相同，否则报错。(测试通过，本例中EXCEL共有4歹U)strSql=insertintosheet1$(b3,c3)values(123,456);只插第二三列(测试通过)/strSql=insertintosheet1$B3:C8VALUES(1,2);/测试时可能会报错，超出界限Conn.Open();OleDbCommandcmd=newOleDbCommand(strSql,Conn);cmd.ExecuteNonQuery

7、();Conn.Close();删除的没搞定，有时间再继续弄privatevoidbtnDel_Click(objectsender,EventArgse)(System.Data.OleDb.OleDbConnectionConn=newSystem.Data.OleDb.OleDbConnection(Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:a.xls;ExtendedProperties=Excel8.0;);DataSetds=newDataSet();stringstrSql=delete*fromsheet1$;/写入新的一行数据,a,b是列名(测试通过)/DELETESheetl.编号,Sheetl.姓名FROMSheetlWHERESheetl.编号in(sele