[ADO.NET] 如何 使用 OLE DB 讀寫 Excel / 建立 Excel 檔案 (二)
續上篇[ADO.NET] 如何 使用 OLE DB 讀寫 Excel / 建立 Excel 檔案 (一)
1.本篇主要是使用Adapter與Parameter異動資料
2.Excel儲存格的欄位長這樣，英文代表Column，數字代表Row
![untitled1_thumb[16]](file:///C:/Documents and Settings/ggyy/Local Settings/Temp/WindowsLiveWriter1352241347/supfiles592346/untitled1[24].png "untitled1_thumb[16]")
ds.Tables[0].Rows[0][1] = textBox2.Text;
[0]→指的是Row
[1]→指的是Column
當然也可以醬用
ds.Tables[0].Rows[0]["Name"] = textBox2.Text;
["Name"]→代表的是欄位名稱，但不可缺少 "HDR=YES" 屬性。
3.其中Row會因為你下的SQL關係而影響
若是"HDR=YES"，資料的第一格就是 "A2"。
若是"HDR=NO"，資料的第一格就是"A1"
4.為了要使用問號的 Where 條件：
string qs1 = "Update [" + "EmployeeData" + "$] set Id=? , Name=? where Id=? ";
這問題困擾了我很久，直到比爾叔出面幫忙，使用了下列參數才得已成功。
//3.建立更新定義
OleDbParameter parameter1 = cm.Parameters.Add("@id", OleDbType.VarChar, 50, "Id");
parameter1.SourceVersion = DataRowVersion.Current;
OleDbParameter parameter2 = cm.Parameters.Add("@name", OleDbType.VarChar, 50, "Name");
parameter2.SourceVersion = DataRowVersion.Current;
OleDbParameter parameter = cm.Parameters.Add("@oldID", OleDbType.VarChar, 50, "Id");
parameter.SourceVersion = DataRowVersion.Original;
da.UpdateCommand = cm;
5.範例說明
如何插入一行資料
private void button1_Click(object sender, EventArgs e)
{
dataGridView1.DataSource = null;
//連線字串
cs =
"Data Source=" + FileName + ";" +
"Provider=" + ProviderName +
"Extended Properties=" + ExtendedString +
"HDR=" + Hdr +
"IMEX=" + IMEX;
//1.建立連線
using (OleDbConnection cn = new OleDbConnection(cs))
{
cn.Open();
string qs = "INSERT INTO [EmployeeData$] VALUES(?, ?)";
//2.建立OleDbCommand物件
using (OleDbCommand cm = new OleDbCommand(qs, cn))
{
//3.建立OleDbDataAdapter物件
using (OleDbDataAdapter da = new OleDbDataAdapter("Select id,name From [EmployeeData$] ", cn))
{
//4.引用DataSet
DataSet ds = new DataSet();
//5.Fill到inputTable變數
da.Fill(ds, "inputTable");
//6.建立插入定義
da.InsertCommand = cm;
da.InsertCommand.Parameters.Add("@Id", OleDbType.VarChar).SourceColumn = "Id";
da.InsertCommand.Parameters.Add("@Name", OleDbType.VarChar).SourceColumn = "Name";
//7.在DataTable加入新的列
DataRow dr = ds.Tables[0].NewRow();
ds.Tables[0].Rows[0][0] = textBox1.Text;
ds.Tables[0].Rows[0][1] = textBox2.Text;
//8.加入到DataTable裡
ds.Tables[0].Rows.Add(dr);
//9.更新檔案
da.UpdateCommand = cm;
da.Update(ds, "inputTable");
}
}
}
ShowList();
ShowGrid();
}
如何只更新一欄的資料
private void button4_Click(object sender, EventArgs e)
{
this.button4.Enabled = false;
string qs1 = "Update [" + "EmployeeData" + "$] set Id=? , Name=? where Id=? ";
string qs2 = "Select id,name From [EmployeeData$]";
//1.建立連線
using (OleDbConnection cn = new OleDbConnection(cs))
{
cn.Open();
//2.建立OleDbCommand物件
using (OleDbCommand cm = new OleDbCommand(qs1, cn))
{
using (OleDbDataAdapter da = new OleDbDataAdapter(qs2, cn))
{
//3.建立更新定義
OleDbParameter parameter1 = cm.Parameters.Add("@id", OleDbType.VarChar, 50, "Id");
parameter1.SourceVersion = DataRowVersion.Current;
OleDbParameter parameter2 = cm.Parameters.Add("@name", OleDbType.VarChar, 50, "Name");
parameter2.SourceVersion = DataRowVersion.Current;
OleDbParameter parameter = cm.Parameters.Add("@oldID", OleDbType.VarChar, 50, "Id");
parameter.SourceVersion = DataRowVersion.Original;
da.UpdateCommand = cm;
//4.引用DataSet
DataSet ds = new DataSet();
//Fill到inputTable變數
da.Fill(ds, "inputTable");
//5.更新特定位置的值
ds.Tables[0].Rows[Int32.Parse(textBox1.Text) - 1]["Name"] = textBox2.Text;
//ds.Tables[0].Rows[1][1] = (string)this.textBox2.Text;
//6.更新檔案
da.UpdateCommand = cm;
da.Update(ds, "inputTable");
//da.Update(ds.Tables[0]);
}
}
}
ShowList();
ShowGrid();
this.button4.Enabled = true;
}
如何用 dataGridView 轉成 Excel
//比爾叔友情贊助的CODE
DataSet myDataset = new DataSet();
BindingSource sBindingSource = new BindingSource();
private void ShowGrid()
{
//清空資料繫結
myDataset.Reset();
OleDbConnection cn = new OleDbConnection(cs);
CreatDataAdapter(cn).Fill(myDataset);
cn.Close();
cn.Dispose();
sBindingSource.DataSource = myDataset.Tables[0];
dataGridView1.DataSource = sBindingSource;
}
private OleDbDataAdapter CreatDataAdapter(OleDbConnection MyConn)
{
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand myCmd;
string SQLstr;
SQLstr = "select * from[" + SheetName + "$]";
myCmd = new OleDbCommand(SQLstr, MyConn);
adapter.SelectCommand = myCmd;
SQLstr = "Update [" + SheetName + "$] set Id=? , Name=? where Id=? ";
OleDbCommand myCmd1;
myCmd1 = new OleDbCommand(SQLstr, MyConn);
OleDbParameter parameter1 = myCmd1.Parameters.Add("@id", OleDbType.VarChar, 50, "Id");
parameter1.SourceVersion = DataRowVersion.Current;
OleDbParameter parameter2 = myCmd1.Parameters.Add("@name", OleDbType.VarChar, 50, "Name");
parameter2.SourceVersion = DataRowVersion.Current;
OleDbParameter parameter = myCmd1.Parameters.Add("@oldID", OleDbType.VarChar, 50, "Id");
parameter.SourceVersion = DataRowVersion.Original;
adapter.UpdateCommand = myCmd1;
// adapter.ContinueUpdateOnError=true ;
return adapter;
}
private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
OleDbConnection cn = new OleDbConnection(cs);
CreatDataAdapter(cn).Update(myDataset.Tables[0]);
cn.Close();
cn.Dispose();
ShowList();
ShowGrid();
}
![快照-2009711172330_thumb[1]](file:///C:/Documents and Settings/ggyy/Local Settings/Temp/WindowsLiveWriter1352241347/supfiles592346/快照-2009711172330[3].jpg "快照-2009711172330_thumb[1]")
6.範例下載：
PS.寫這篇範例花了我兩天的時間，感謝熱心的朋友們幫忙解決，特此感謝：比爾叔、小朱、小歐、Willams，有你們在真好!!!
若有謬誤，煩請告知，新手發帖請多包涵
Microsoft MVP Award 2010~2017 C# 第四季
Microsoft MVP Award 2018~2022 .NET
