jeffyeh

之前有大概Post了一篇怎麼將資料匯出為Excel檔,同樣的,也會有可能需要讀取Excel內的資料到程式內.

尤其是部份的資料交換格式仍未使用XML,有的還是使用Excel檔在做資料交換.

那麼就需要用程式碼去讀取檔案,將資料讀進來做運用.

以下的程式碼可以適用在Visual Studio 2002/2003/2005

採的用語言是C#

using System.Data.OleDb;
using System.IO;

 if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                FileInfo fi = new FileInfo(openFileDialog1.FileName);
                if (!fi.Exists)
                {
                    MessageBox.Show("匯入檔案並不存在", "注意", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }

                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + openFileDialog1.FileName + ";Extended Properties='Excel 8.0;IMEX=1;'";
                OleDbConnection Conn = new OleDbConnection(strConn);
                try
                {
                    Conn.Open();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    return;
                }

                try
                {
                    DataSet ds = new DataSet();

                    DataTable dt = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                    if (dt.Rows[0]["TABLE_Name"].ToString().IndexOf("$") < 0)
                    {
                        dt.Rows[0]["TABLE_Name"] += "$";
                    }
                    string strSelect = "Select * From [" + dt.Rows[0]["TABLE_Name"].ToString() + "] ";
                    OleDbDataAdapter da = new OleDbDataAdapter(strSelect, strConn);

                    da.Fill(ds, dt.Rows[0]["TABLE_Name"].ToString());
                    da.Dispose();

                    ds.Tables[dt.Rows[0]["TABLE_Name"].ToString()].Rows.RemoveAt(0);

                    dataGridView1.DataSource = ds.Tables[dt.Rows[0]["TABLE_Name"].ToString()];
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    Conn.Close();
                    Conn.Dispose();
                }
            }