ADO.NET入門記-027 (Excelに接続してデータを取得・追加・更新)(Excel,OleDb,HDR,Extended Properties,xlsx)

通常、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

尚、ソースにもコメントとして記述してますが削除処理は行えません。