教你用C讀寫(xiě)、刪除、更新excel表格記錄

1、教你用C獻(xiàn)寫(xiě)、刪除、更新excel表格記錄如下圖所示，編一個(gè)程序，鼠標(biāo)單擊窗體視圖區(qū)（右邊）時(shí)，獲取一對(duì)坐標(biāo)（X,Y）,點(diǎn)擊保存將點(diǎn)保存到excel表記錄中。此外，還實(shí)現(xiàn)了刪除、更新功能以及打開(kāi)excel表功能。插入和更新比較簡(jiǎn)單，和操作一般的數(shù)據(jù)庫(kù)一樣，但是刪除稍微有點(diǎn)復(fù)雜，不能用deletefromSheet1$whereID=x的方式刪除，自己可以去試，主要是excel數(shù)據(jù)之間的關(guān)系不像關(guān)系數(shù)據(jù)庫(kù)那么簡(jiǎn)單，oledb不提供這種方法。所以只能用專(zhuān)門(mén)操作excel表的（Microsoft.Ofice.Interop.Excel名字空間下，先添加引用）來(lái)實(shí)現(xiàn)刪除某條記錄的功能?；⒆x寫(xiě)陛雄匚叵I

2、X打開(kāi)丈件t-.劇除讓中行更新便理士,技弓片示Lj315源代碼：usingSystem;usingSystem.Collections.Generic;usingSystem.ComponentModel;usingSystem.Data;usingSystem.Drawing;usingSystem.Text;usingSystem.Windows.Forms;usingSystem.Data.OleDb;usingSystem.Reflection;usingExcel=Microsoft.Office.Interop.Excel;namespaceLeationpublicpartial

3、classFrmMain:Form/定義變量privateOleDbConnectionconnection=null;privateOleDbCommandmd=null;privateOleDbDataAdapterdataAdapter=nullprivateDataSetdataSet=null;privatestringfilePath=G:points.xls;privatestringconnStr=provider=microsoft.jet.oledb.4.0;datasource=G:points.xls;extendedproperties=Excel8.0;HDR=ye

4、s;IMEX=2;privatestringselectStr=select*fromSheet1$privatestringcmdStr=null;privatestringOID=null;/對(duì)象IDprivatestringx=null;privatestringy=null;privateExcel.ApplicationexcelApp=null;privateExcel.Workbookbook=null;privateExcel.Worksheetsheet=null;privateExcel.Rangerange=null;/構(gòu)造函數(shù)publicFrmMain()Initial

5、izeComponent();/鼠標(biāo)移動(dòng)事件privatevoidsplitContainer1_Panel2_MouseMove(objectsender,MouseEventArgse)this.lblxy.Text=x=+e.X.ToString()+y=+e.Y.ToString();/鼠標(biāo)按下事件privatevoidsplitContainer1_Panel2_MouseDown(objectsender,MouseEventArgse)if(e.Button=MouseButtons.Left)this.tbX.Text=e.X.ToString();this.tbY.Text=

6、e.Y.ToString();/刷新dataGridView1privatevoidRefreshTable()connection=newOleDbConnection(connStr);connection.Open();dataAdapter=newOleDbDataAdapter(selectStr,connection);dataSet=newDataSet();dataAdapter.Fill(dataSet);this.dataGridView1.DataSource=dataSet.Tables0;connection.Close();/程序加載事件，初始化dataGridVi

7、ew1privatevoidFrmMain_Load(objectsender,EventArgse)this.RefreshTable();/獲取一個(gè)可以用的OIDprivatestringGetOID()introwNum=this.dataGridView1.Rows.Count-1;intmaxOID=0;inttemp=0;for(inti=0;irowNum;i+)temp=int.Parse(this.dataGridView10,i.Value.ToString();if(maxOID0)lblTip.Text=保存成功，插入行數(shù)：+row.ToString();elselbl

8、Tip.Text=保存失敗;connection.Close();this.RefreshTable();/刪除記錄privatevoidbtnDelSelRow_Click(objectsender,EventArgse)intselRowIndex=this.dataGridView1.CurrentRow.Index+2;/excel表中的行索引與dataGridView不一樣，這里注意if(selRowIndex0)this.dataGridView1.RowsselRowIndex-3.Selected=true;this.lblTip.Text=刪除成功;/更新記錄privatev

9、oidbtnUpdate_Click(objectsender,EventArgse)intselRowIndex=this.dataGridView1.CurrentRow.Index;if(selRowIndex=1)lblTip.Text=更新成功，更新行數(shù)：+row.ToString();elselblTip.Text=更新失敗;connection.Close();this.RefreshTable();/選中更新的行this.dataGridView1.RowsselRowIndex.Selected=true;privatevoidbtnOpenFile_Click(object

10、sender,EventArgse)OpenFileDialogofd=newOpenFileDialog();ofd.Filter=excel文件(*.xls)|*.xls;ofd.Title=代開(kāi)excel表;if(ofd.ShowDialog()=DialogResult.OK)this.filePath=ofd.FileName;this.connStr=provider=microsoft.jet.oledb.4.0;datasource=+filePath+extendedproperties=Excel8.0;HDR=yes;IMEX=2;this.RefreshTable();程與5Ml表二J叵區(qū)打開(kāi)支仲加打T。302曇存原或皿除選中行更新修改保存成功，任入行裝：.7323B356同標(biāo)位置二步如：T