[ADO.NET] 如何 使用 OLE DB 讀寫 Excel / 建立 Excel 檔案 (二)

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;

}

//比爾叔友情贊助的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();

}