jeffyeh

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

其中一種即為OLEDB.

以下即為C#程式範例 :

using System.Data.OleDb;

 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;
            }