Excel 操作3

 OleDbConnection connection;//打开数据库连接
        public void OpenConnection(string xlsFils) {if (!File.Exists(xlsFils)){MessageBox.Show("文件\"" + xlsFils + "\"不存在", "提示");return;}string conn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + xlsFils + ";Extended Properties=Excel 8.0";connection = new OleDbConnection(conn);connection.Open();}//查询数据
        public DataTable Select(){DataTable dt = new DataTable();string Sql = "select * from [Sheet1$]";OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, connection);mycommand.Fill(dt);return dt;}private void Form1_Load(object sender, EventArgs e){string xlsFile = System.Windows.Forms.Application.StartupPath + "/" + "ExcelFiles/test.xls";OpenConnection(xlsFile);}//插入数据
        public void Insert(){string sql = string.Format("insert into [Sheet1$] values('{0}','{1}','{2}')", "陈太汉", "陈晓玲", "520");OleDbCommand myCommand = new OleDbCommand(sql, connection);myCommand.ExecuteNonQuery();Select();}private void btAdd_Click(object sender, EventArgs e){Insert();}//Excel不支持SQl语句的方式进行删除，可以用把每个字段的值设为空的方式进行删除
        public void Delete(){string sql = string.Format("Update [Sheet1$] set col1=NULL,col2=NULL,col3=NULL where col1='{0}'", "陈太汉");OleDbCommand myCommand = new OleDbCommand(sql, connection);myCommand.ExecuteNonQuery();Select();}private void btDelete_Click(object sender, EventArgs e){Delete();}//更新数据
        private new void Update() {string sql = string.Format("update  [Sheet1$] set col1='{0}' where col1='{1}'", "陈晓玲","陈太汉");OleDbCommand myCommand = new OleDbCommand(sql, connection);myCommand.ExecuteNonQuery();Select();}private void btUpdate_Click(object sender, EventArgs e){Update();}private void btSelect_Click(object sender, EventArgs e){Select();}