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

1、分享來自網(wǎng)絡(luò)。教你用C#讀寫、刪除、更新excel表格記錄如下列圖所示，編一個(gè)程序，鼠標(biāo)單擊窗體視圖區(qū)右邊時(shí)，獲取一對(duì)坐標(biāo)X,Y)，點(diǎn)擊保存將點(diǎn)保存到excel表記錄中。此外，還實(shí)現(xiàn)了刪除、更新功能以及打開excel表功能。插入和更新比較簡(jiǎn)單，和操作一般的數(shù)據(jù)庫一樣，但是刪除稍微有點(diǎn)復(fù)雜，不能用delete from Sheet1$ where ID=x的方式刪除，自己可以去試，主要是excel數(shù)據(jù)之間的關(guān)系不像關(guān)系數(shù)據(jù)庫那么簡(jiǎn)單，oledb不提供這種方法。所以只能用專門操作excel表的Microsoft.Office.Interop.Excel名字空間下，先添加引用)來實(shí)現(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 Leation public partial class FrmM

3、ain : Form /定義變量 private OleDbConnection connection = null; private OleDbCommand cmd = null; private OleDbDataAdapter dataAdapter = null; private DataSet dataSet = null; private string filePath = "G:points.xls" private string connStr = "provider=microsoft.jet.oledb.4.0;data source=G:p

4、oints.xls;extended properties='Excel 8.0;HDR=yes;IMEX=2'" private string selectStr = "select * from Sheet1$" private string cmdStr = null; private string OID = null; /對(duì)象ID private string x = null; private string y = null; private Excel.Application excelApp = null; private Exce

5、l.Workbook book = null; private Excel.Worksheet sheet = null; private Excel.Range range = null; /構(gòu)造函數(shù) public FrmMain() InitializeComponent(); /鼠標(biāo)移動(dòng)事件 private void splitContainer1_Panel2_MouseMove(object sender, MouseEventArgs e) this.lblxy.Text = "x=" + e.X.ToString() + " y=" + e

6、.Y.ToString(); /鼠標(biāo)按下事件 private void splitContainer1_Panel2_MouseDown(object sender, MouseEventArgs e) if (e.Button = MouseButtons.Left) this.tbX.Text = e.X.ToString(); this.tbY.Text = e.Y.ToString(); /刷新dataGridView1 private void RefreshTable() connection = new OleDbConnection(connStr); connection.O

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

8、private string GetOID() int rowNum = this.dataGridView1.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(); /插入一條記錄，即保存一個(gè)點(diǎn)信息 private void btnSaveP

9、nt_Click(object sender, EventArgs e) OID = this.GetOID(); 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 in

10、to Sheet1$(ID,X,Y) values(" + OID + "," + x + "," + y + ")" cmd = new OleDbCommand(cmdStr, connection); int row=cmd.ExecuteNonQuery(); if (row > 0) lblTip.Text = "保存成功，插入行數(shù)：" + row.ToString(); else lblTip.Text = "保存失敗" connection.Close(); thi

11、s.RefreshTable(); /刪除記錄 private void btnDelSelRow_Click(object sender, EventArgs e) int selRowIndex = this.dataGridView1.CurrentRow.Index + 2; /excel表中的行索引與dataGridView不一樣，這里注意 if (selRowIndex<1) MessageBox.Show("沒有選中行"); lblTip.Text = "刪除失敗" return; excelApp = new Microsoft.O

12、ffice.Interop.Excel.Application(); excelApp.Visible = false; /假設(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

13、.Value, Missing.Value, Missing.Value); /獲取sheet1 sheet = (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, Mis

14、sing.Value); /退出excel application，可以將前面的excelApp.Visible = false改為excelApp.Visible = true看看; excelApp.Workbooks.Close(); excelApp.Quit(); /刷新dataGridView1 this.RefreshTable(); /選中刪除行的上一行 if (selRowIndex - 3) > 0) this.dataGridView1.RowsselRowIndex - 3.Selected = true; this.lblTip.Text="刪除成功&

15、quot; /更新記錄 private void btnUpdate_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

16、= this.tbY.Text; if (x = "" | 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+"&#

17、39;" cmd = new OleDbCommand(cmdStr, connection); int row = cmd.ExecuteNonQuery(); if (row >= 1) lblTip.Text = "更新成功，更新行數(shù)：" + row.ToString(); else lblTip.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.Title