C#實現Access匯入匯出Excel

一、Access從Excel中匯入資料

1.用到的Excel表的格式及內容

 

實現

 OleDbConnection con = new OleDbConnection();<br /> try<br /> {<br /> OpenFileDialog openFile = new OpenFileDialog();//開啟檔案對話方塊。<br /> openFile.Filter = ("Excel 檔案(*.xls)|*.xls");//尾碼名。<br /> if (openFile.ShowDialog() == DialogResult.OK)<br /> {<br /> string filename = openFile.FileName;<br /> int index = filename.LastIndexOf("//");//截取檔案的名字<br /> filename = filename.Substring(index + 1);<br /> conExcel.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" +<br /> Application.StartupPath + "//Appdata.mdb";<br /> //將excel匯入access<br /> //distinct :刪除excel重複的行.<br /> //[excel名].[sheet名] 已有的excel的表要加$<br /> //where not in : 插入不重複的記錄。</p><p> string sql = "insert into Users2(使用者編號,使用者姓名) select distinct * from [Excel 8.0;database=" +<br /> filename + "].[name$] where 使用者編號 not in (select 使用者編號 from Users2) ";<br /> OleDbCommand com = new OleDbCommand(sql, con);<br /> con.Open();<br /> com.ExecuteNonQuery();<br /> MessageBox.Show("匯入資料成功", "匯入資料", MessageBoxButtons.OK, MessageBoxIcon.Information);<br /> }<br /> }<br /> catch (Exception ex)<br /> {<br /> MessageBox.Show(ex.ToString());<br /> }<br /> finally<br /> {<br /> con.Close();<br /> }

 

二、Access匯出Excel

OleDbConnection con = new OleDbConnection();<br /> try<br /> {<br /> SaveFileDialog saveFile = new SaveFileDialog();<br /> saveFile.Filter = ("Excel 檔案(*.xls)|*.xls");//指定檔案尾碼名為Excel 檔案。<br /> if (saveFile.ShowDialog() == DialogResult.OK)<br /> {<br /> string filename = saveFile.FileName;<br /> if (System.IO.File.Exists(filename))<br /> {<br /> System.IO.File.Delete(filename);//如果檔案存在刪除檔案。<br /> }<br /> int index = filename.LastIndexOf("//");//擷取最後一個/的索引<br /> filename = filename.Substring(index + 1);//擷取excel名稱(建立表的路徑相對於SaveFileDialog的路徑)<br /> //select * into 建立 新的表。<br /> //[[Excel 8.0;database= excel名].[sheet名] 如果是建立sheet表不能加$,如果向sheet裡插入資料要加$.　<br /> //sheet最多儲存65535條資料。<br /> string sql = "select top 65535 * into [Excel 8.0;database=" + filename + "].[使用者資訊] from Users2";<br /> con.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + Application.StartupPath + "//Appdata.mdb";//將資料庫放到debug目錄下。<br /> OleDbCommand com = new OleDbCommand(sql, con);<br /> con.Open();<br /> com.ExecuteNonQuery();</p><p> MessageBox.Show("匯出資料成功", "匯出資料", MessageBoxButtons.OK, MessageBoxIcon.Information);<br /> }<br /> }<br /> catch (Exception ex)<br /> {<br /> MessageBox.Show(ex.ToString());<br /> }<br /> finally<br /> {<br /> con.Close();<br /> }