一起來學(xué)用CSharp-c操作excel表(102629)

1、教你用C#賣寫、刪除、更新excel表格記錄如以下圖所示，編一個程序，鼠標(biāo)單擊窗體視圖區(qū)右邊時，獲取一對坐標(biāo)X,Y，點擊保存將點保存到excel表記錄中。此外，還實現(xiàn)了刪除、更新功能以及翻開excel表功能。插入和更新比擬簡單，和操作一般的數(shù)據(jù)庫一樣，但是刪除稍微有點復(fù)雜，不能用 delete from Sheet1$ where ID=x 的方式刪除，自己可以去試，主要是 excel數(shù)據(jù)之間的關(guān)系不像關(guān)系數(shù) 據(jù)庫那么簡單，oledb不提供這種方法。所以只能用專門操作excel表的Microsoft.Office.lnterop.Excel 名字空間下，先添加引用來實現(xiàn)刪除某條記錄的功能。源代

2、碼：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;using System.Reflection;using Excel = Microsoft.Office.Interop.Excel;namespace Leationpublic partial class FrmMain :

3、 Form/定義變量private OleDbConnection connection = nullprivate private private private privateOleDbCommancdmd = null ;OleDbDataAdapterDataSet dataSet = string filePath = string connStr = properties='Excel 8.0;HDR=yes;IMEX=2dataAdapter = null ;null ; "G:points.xls" ;"provider=microsoft

4、.jet.oledb.4.0;data source=G:points.xls;extendedII!privatestringselectStr = "select * from Sheet1$privatestringcmdStr = null ;privatestringOID = null ; / 對象 IDprivatestringx = null ;privatestringy = null ;privateExcel.Application excelApp = null ;privateExcel.Workbook book = null ;privateExcel.

5、Worksheet sheet = null ;privateExcel.Range range = null ;/ 構(gòu)造函數(shù)publicFrmMain()InitializeComponent();/ 鼠標(biāo)移動事件private void splitContainer1_Panel2_MouseMove(objectsender, MouseEventArgs e)this .lblxy.Text = "x=" + e.X.ToString() +y=+ e.Y.ToString();/ 鼠標(biāo)按下事件private void splitContainer1_Panel2_

6、MouseDown(objectsender, MouseEventArgs e)if (e.Button = MouseButtons .Left)this .tbX.Text = e.X.ToString();this .tbY.Text = e.Y.ToString();/ 刷新 dataGridView1 private void RefreshTable()new OleDbConnection (connStr);connection = connection.Open(); dataAdapter = dataSet = dataAdapter.Fill(dataSet);new

7、 OleDbDataAdapter (selectStr, connection); new DataSet ();this .dataGridView1.DataSource = dataSet.Tables0; connection.Close();/ 程序加載事件，初始化 dataGridView1private void FrmMain_Load( object sender, EventArgs e) this .RefreshTable();/ 獲取一個可以用的 OID private string GetOID() int rowNum = this .dataGridView1

8、.Rows.Count - 1; int maxOID = 0;int temp = 0;for ( int i = 0; i < rowNum; i+)temp = int .Parse( this .dataGridView10, i.Value.ToString();if (maxOID < temp)maxOID = temp;return (maxOID+1).ToString();/ 插入一條記錄，即保存一個點信息private void btnSavePnt_Click( object sender, EventArgs e)OID = this .GetOID();

9、x =this .tbX.Text;y =this .tbY.Text;if (x = "" | y = "" )MessageBox.Show( "x,y 不能為空 " ); lblTip.Text =" 保存失敗 "return ;connection = new OleDbConnection (connStr);connection.Open();cmdStr ="insert into Sheet1$(ID,X,Y) values("+ OID + "," + x

10、+ "," + y + ")" ;cmd =new OleDbComman(dcmdStr, connection);int row=cmd.ExecuteNonQuery();if (row > 0)lblTip.Text =" 保存成功，插入行數(shù)： " + row.ToString();elselblTip.Text =" 保存失敗 "connection.Close();this .RefreshTable();/ 刪除記錄private void btnDelSelRow_Click( object

11、sender, EventArgs e)int selRowIndex = this .dataGridView1.CurrentRow.Index + 2;/excel 表中的行索引與 dataGridView 不一樣，這里注意if (selRowIndex<1)MessageBox.Show( " 沒有選中行 ");lblTip.Text ="刪除失敗 "return ;excelApp = newMicrosoft.Office.Interop.Excel. Application ();excelApp.Visible = false ;

12、/ 假設(shè)為 true ，刪除瞬間可以看見 office excel 界面/ 翻開 excel 文件book = excelApp.Workbooks.Open(filePath, 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);/ 獲

13、取 sheet1sheet = (Excel.Worksheet )book.Worksheets1;/ 獲取編輯范圍range = (Excel. Range)sheet.RowsselRowIndex, Missing .Value;/ 刪除整行range.EntireRow.Delete(Excel. XlDeleteShiftDirection .xlShiftUp);/ 保存編輯 book.Save();看看 ;/ 關(guān)閉 book book.Close( Missing .Value, Missing .Value, Missing .Value);/ 退出 excel applic

14、ation，可以將前面的 excelApp.Visible = false 改為 excelApp.Visible = trueexcelApp.Workbooks.Close();excelApp.Quit();/ 刷新 dataGridView1this .RefreshTable();/ 選中刪除行的上一行if (selRowIndex - 3) > 0)this .dataGridView1.RowsselRowIndex - 3.Selected =true ;this .lblTip.Text="刪除成功 "/ 更新記錄private void btnUp

15、date_Click( object sender, EventArgs e)int selRowIndex= this .dataGridView1.CurrentRow.Index;if (selRowIndex< 0)MessageBox.Show( "沒有選中行 !" );lblTip.Text ="更新失敗 "return ;OID =this .dataGridView10, selRowIndex.Value.ToString();x =this .tbX.Text;y =this .tbY.Text;if (x ="&qu

16、ot; | y ="" )MessageBox.Show( "x,y 不能為空 " );lblTip.Text ="更新失敗 "return ;connection =new OleDbConnection (connStr);connection.Open();cmdStr ="update Sheet1$ set X="+x+",Y=" +y+" where ID='"+OID+"'" ;cmd =new OleDbComman(dcm

17、dStr, connection);int row = cmd.ExecuteNonQuery();if (row >= 1)lblTip.Text ="更新成功，更新行數(shù)： " + row.ToString();elselblTip.Text ="更新失敗 "connection.Close();this .RefreshTable();/ 選中更新的行this .dataGridView1.RowsselRowIndex.Selected = true ;private void btnOpenFile_Click( object sender, EventArgs e)OpenFileDialog ofd = new OpenFileDialog ();ofd.Filter ="excel 文件(*.xls)|*.xls"ofd.T