Jeff 隨手記

使用Visual Studio 2002/2003/2005來開發,有時有些作業必需將資料匯出為Excel檔,而匯出的方式非常的多種.

其中一種即為OLEDB.

以下即為C#程式範例 :

 try
{
    if (saveFileDialog1.ShowDialog() == DialogResult.OK)
    {
        this.Cursor = Cursors.WaitCursor;

        if (System.IO.File.Exists(saveFileDialog1.FileName))
        {
            System.IO.File.Delete(saveFileDialog1.FileName);
        }

        #region 產生測試匯出至Excel的資料
        DataTable dt = new DataTable("ListTest");
        dt.Columns.Add("TestNo");
        dt.Columns.Add("TestName");
        for (int i = 0; i < 6; i++)
        {
            DataRow dr = dt.NewRow();
            dr["TestNo"] = i;
            dr["TestName"] = "Test" + i.ToString();
            dt.Rows.Add(dr);
        }
        #endregion

        #region 產生Excel檔
        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + saveFileDialog1.FileName + ";Extended Properties=Excel 8.0;";
        OleDbConnection Conn = new OleDbConnection(strConn);
        Conn.Open();

        string strCreateTable = " CREATE TABLE ListTest ( TestNo VARCHAR, TestName VARCHAR) ";
        OleDbCommand odbcmd = new OleDbCommand(strCreateTable, Conn);

        try
        {
            odbcmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            Conn.Close();
        }
        #endregion


        #region 將資料新增至Excel檔
        string strInsert = "Insert into ListTest (TestNo,TestName) values (@TestNo,@TestName)";
        OleDbCommand olecmd = new OleDbCommand(strInsert, Conn);
        olecmd.Parameters.Add("@TestNo", OleDbType.VarChar);
        olecmd.Parameters["@TestNo"].SourceColumn = "TestNo";
        olecmd.Parameters.Add("@TestName", OleDbType.VarChar);
        olecmd.Parameters["@TestName"].SourceColumn = "TestName";
        OleDbDataAdapter da2 = new OleDbDataAdapter();
        da2.InsertCommand = olecmd;
        da2.Update(dt);
        #endregion

        MessageBox.Show("資料匯出完成完成", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}
finally
{
    this.Cursor = Cursors.Default;
}