通常、ADO.NETで処理を書く場合はSQLServerなりOracleなりのデータベースを
対象として処理を行うのですが、Excelにも接続することが出来ます。
Excelの場合、実際は他のライブラリなどを利用して処理をするのが多いですが
知っておくと便利です。
利用するプロバイダーは以下のものとなります。
System.Data.OleDb
接続文字列は以下のようなパターンをとります。
Provider=Microsoft.ACE.OLEDB.12.0;Data Source={ファイルパス};Extended Properties="Excel {ISAMバージョン};HDR={YES/NO}";
ISAMバージョンには、Excel 2003までの形式には8.0を、Excel2007形式には12.0を指定します。
HDRは先頭行をヘッダとして扱うか否かの設定です。
Providerには従来までのJETドライバも使用できますが、その場合2007形式を読むことが出来ません。
Microsoft.ACE.OLEDB.12.0を利用する方が無難です。
以下、サンプルです。
サンプルデータとして以下のようなエクセルファイルを用意しました。
データは以下のようになっています。
ID | NAME | AGE |
1 | gsf_zero1 | 30 |
2 | gsf_zero2 | 31 |
3 | gsf_zero3 | 32 |
4 | gsf_zero4 | 33 |
5 | gsf_zero5 | 34 |
6 | gsf_zero6 | 35 |
で、実際に接続してデータの取得・追加・更新を行っているソースです。
#region ExcelConnectSamples-01 public class ExcelConnectSamples01 : IExecutable { public void Execute() { DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb"); using(DbConnection conn = factory.CreateConnection()) { // // Excel用の接続文字列を構築. // // Providerは、Microsoft.ACE.OLEDB.12.0を使用する事。 // (JETドライバを利用するとxlsxを読み込む事が出来ない。) // // Extended Propertiesには、ISAMのバージョン(Excel 12.0)とHDRを指定する。 // (2003までのxlsの場合はExcel 8.0でISAMバージョンを指定する。) // HDRは先頭行をヘッダ情報としてみなすか否かを指定する。 // 先頭行をヘッダ情報としてみなす場合はYESを、そうでない場合はNOを設定。 // // HDR=NOと指定した場合、カラム名はシステム側で自動的に割り振られる。 // (F1, F2, F3.....となる) // DbConnectionStringBuilder builder = factory.CreateConnectionStringBuilder(); builder["Provider"] = "Microsoft.ACE.OLEDB.12.0"; builder["Data Source"] = @"C:\Users\gsf\Tmp\Sample.xlsx"; builder["Extended Properties"] = "Excel 12.0;HDR=YES"; conn.ConnectionString = builder.ToString(); conn.Open(); // // SELECT. // // 通常のSQLのように発行できる。その際シート指定は // [Sheet1$]のように行う。範囲を指定することも出来る。 // using(DbCommand command = conn.CreateCommand()) { command.CommandText = "SELECT * FROM [Sheet1$]"; DataTable table = new DataTable(); using(DbDataReader reader = command.ExecuteReader()) { table.Load(reader); } foreach(DataRow row in table.Rows) { Console.WriteLine("{0},{1},{2}", row["ID"], row["NAME"], row["AGE"]); } } using(DbCommand command = conn.CreateCommand()) { command.CommandText = "SELECT * FROM [Sheet1$A1:C7]"; DataTable table = new DataTable(); using(DbDataReader reader = command.ExecuteReader()) { table.Load(reader); } foreach(DataRow row in table.Rows) { Console.WriteLine("{0},{1},{2}", row["ID"], row["NAME"], row["AGE"]); } } // // INSERT // // こちらも普通のSQLと同じように発行できる。 // 尚、トランザクションは設定できるが効果は無い。 // (ロールバックを行ってもデータは戻らない。) // // また、INSERT,UPDATEはエクセルを開いた状態でも // 行う事ができる。 // // データの削除は行う事ができない。(制限) // using(DbCommand command = conn.CreateCommand()) { string query = string.Empty; query += " INSERT INTO [Sheet1$] "; query += " (ID, NAME, AGE) "; query += " VALUES "; query += " (7, 'gsf_zero7', 37) "; command.CommandText = query; command.ExecuteNonQuery(); using(DbCommand command2 = conn.CreateCommand()) { command2.CommandText = "SELECT * FROM [Sheet1$]"; DataTable table = new DataTable(); using(DbDataReader reader = command2.ExecuteReader()) { table.Load(reader); } foreach(DataRow row in table.Rows) { Console.WriteLine("{0},{1},{2}", row["ID"], row["NAME"], row["AGE"]); } } } // // UPDATE // using(DbCommand command = conn.CreateCommand()) { string query = string.Empty; query += " UPDATE [Sheet1$] "; query += " SET "; query += " NAME = 'updated' "; query += " ,AGE = 99 "; query += " WHERE "; query += " ID = 7 "; command.CommandText = query; command.ExecuteNonQuery(); using(DbCommand command2 = conn.CreateCommand()) { command2.CommandText = "SELECT * FROM [Sheet1$]"; DataTable table = new DataTable(); using(DbDataReader reader = command2.ExecuteReader()) { table.Load(reader); } foreach(DataRow row in table.Rows) { Console.WriteLine("{0},{1},{2}", row["ID"], row["NAME"], row["AGE"]); } } } // // DELETE. // 削除は行えない。 // } } } #endregion
尚、ソースにもコメントとして記述してますが削除処理は行えません。