C#などで開発する時にエクセルの一括取込み機能を作るときがあると思います。
いちいち調べて、プログラムを組んでと言うのが面倒なので備忘録として残しておきます。
機能としては「検索」と「エクセル取込更新」の二つの機能があります。
実際に作ったファイルはこちらからダウンロードできます。
データベース(SQLite)と取り込み用のエクセルも入っているのでダウンロードしてもらえればすぐに実行して試すことができます。
デモンストレーションで動かした動画を載せておきます。
開発環境
開発環境はVisualStudio2015
データベースはSQLiteです。
VisualStudio2015のインストール
開発環境はVisualStudio2015なのでインストールが必要です。
設定は特に必要なく、標準インストールで良いです。
1時間くらいかかります。
こちらからダウンロードできます。
DB.Browser.for.SQLiteのインストール
SQLiteは専用ソフトがないとデータベースを開いて中を見ることができません。
開くとこんな感じで見ることができます。
こちらからダウンロードできます。
エクセル取込み更新のコード
フォーム画面のプログラムは以下です。
※以下のソースはここでは書いていませんので、必要あれば、ダウンロードをお願いします。
・DBを更新や検索するSQLのファイル
・SQLiteのDBへアクセスしたり、更新する部品を書いたファイル
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 |
using System.Data.OleDb; using System.IO; --------------------------- /// <summary> /// 検索ボタンクリック時 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void buttonSearch_Click(object sender, EventArgs e) { DataSet ds = new DataSet(); //datatableを格納用 DataTable dt = new DataTable(); //データを格納 DataAccess dataAccess = new DataAccess(); //インスタンス化 //データを取得 ds.Tables.Add(dataAccess.getSupplierAll().Copy()); //データグリッドビューにセット dataGridView.DataSource = ds.Tables["Supplier"]; } /// <summary> /// エクセル取込更新ボタンクリック時 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void buttonUpdateByExcel_Click(object sender, EventArgs e) { DataSet dsExcelSheets = new DataSet(); //エクセルシートごとのdatatableを格納用 DataTable dtSheet = new DataTable(); //エクセルシートのデータを格納 DataAccess dataAccess = new DataAccess(); //インスタンス化 string filePath = ""; //エクセルのパスを格納 //ダイアログでファイルパスを取得 filePath = getExcelPath(); //エクセルをDatatableに取込む dsExcelSheets.Tables.Add(getDataTableFromExcel(filePath, "Supplier", true, true).Copy()); //Datatableの内容でDBを更新 //Datatableの行数分ループ foreach (DataRow dr in dsExcelSheets.Tables[0].Rows) { dataAccess.updateSupplier(dr); } //Supplierというdatatableがあれば削除 if(dsExcelSheets.Tables.Contains("Supplier")) { dsExcelSheets.Tables.Remove("Supplier"); } //データを取得 dsExcelSheets.Tables.Add(dataAccess.getSupplierAll().Copy()); //データグリッドビューにセット dataGridView.DataSource = dsExcelSheets.Tables["Supplier"]; MessageBox.Show("エクセル更新完了"); } /// <summary> /// ファイルパスを返す /// </summary> /// <returns></returns> public string getExcelPath() { string path = ""; //ファイルダイアログを生成する OpenFileDialog op = new OpenFileDialog(); op.Title = "ファイルを開く"; op.Filter = "すべてのファイル(*.*)|*.*"; op.FilterIndex = 1; //オープンファイルダイアログを表示する DialogResult dialog = op.ShowDialog(); //「開く」ボタンが選択された時の処理 if (dialog == DialogResult.OK) { path = op.FileName; } //「キャンセル」時の処理 else if (dialog == DialogResult.Cancel) { path = ""; } return path; } /// <summary> /// シートをdatatableに格納 /// </summary> /// <param name="strFilePath"></param> /// <param name="strSheetName"></param> /// <param name="isInHeader"></param> /// <param name="isAllStrColum"></param> /// <returns></returns> public DataTable getDataTableFromExcel(string excelFilePath, string strSheetName, Boolean isInHeader = true, Boolean isAllStrColum = true) { DataTable dt = new DataTable(); string strInHeader = isInHeader ? "YES" : "NO"; // ヘッダー設定 string strIMEX = isAllStrColum ? "IMEX=1;" : ""; // 文字列型設定 string strFileEx = Path.GetExtension(excelFilePath); // ファイル拡張子 string strExcelVer = "Excel "; // Excelファイルver確認 //テーブル名をセット dt.TableName = strSheetName; //拡張子ごとにエクセルのバージョンを取得 if (strFileEx == ".xls") { strExcelVer += "8.0;"; } else if (strFileEx == ".xlsx" || strFileEx == ".xlsm") { strExcelVer += "12.0;"; } else { return null; } //コネクションストリングを作成 String strCon = "Provider=Microsoft.ACE.OLEDB.12.0;" // プロバイダ設定 //= "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFilePath + "; " // ソースファイル指定 + "Extended Properties=\"" + strExcelVer // Excelファイルver指定 + "HDR=" + strInHeader + ";" // ヘッダー設定 + strIMEX // フィールドの型を強制的にテキスト + "\""; OleDbConnection con = new OleDbConnection(strCon); String strCmd = "SELECT * FROM [" + strSheetName + "$]"; // 読み込み OleDbCommand cmd = new OleDbCommand(strCmd, con); OleDbDataAdapter adp = new OleDbDataAdapter(cmd); adp.Fill(dt); return dt; } |
解説
エクセルをdatatableに取込み
41行目でエクセルを読み込んで、datatableに取込んでいます。
58行目のgetDataTableFromExcelメソッドでエクセルの内容をSELECT * で全件取得し、datatableに格納させています。
以下のエラーが出る可能性があります。
‘Microsoft.ACE.OLEDB.12.0’ プロバイダーはローカルのコンピューターに登録されていません。
AccessDatabaseEngine_X64.exeをダウンロードしてインストールしてください。
こちらからダウンロード可能です。
https://www.microsoft.com/ja-jp/download/details.aspx?id=13255
参考サイト：Microsoft.ACE.OLEDB.12.0プロバイダーはローカルコンピュータに登録されていません
原因は32ビットのofficeを動かしているため、64ビットようを使う必要があるからだそうです。
エクセルを立上げずに読取る方法も紹介しています。
datatableのデータを使ってDB更新
45行目～48行目でdatatableのレコード分ループして更新しています。
1行ずつdatarowに入った値を元にDBを更新するSQLを発行しています。
dataAccess.updateSupplier(dr);メソッドの中身は以下のように記載しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
/// <summary> /// supplier_cdをキーにSupplierへ更新 /// </summary> /// <param name="drArg"></param> /// <returns></returns> public int updateSupplier(DataRow drArg) { int updateCount = 0; //更新件数を格納 DbHelper = new SQLiteDBHelper(localDBPath); //インスタンス化 StringBuilder sb = new StringBuilder(); //クエリ格納用 //パラメータをセット SQLiteParameter[] parameters = new SQLiteParameter[] { new SQLiteParameter("@supplier_cd",drArg.ItemArray[0]), new SQLiteParameter("@supplier_name",drArg.ItemArray[1]), new SQLiteParameter("@addr",drArg.ItemArray[2]), new SQLiteParameter("@post_num",drArg.ItemArray[3]), new SQLiteParameter("@tel",drArg.ItemArray[4]), new SQLiteParameter("@fax",drArg.ItemArray[5]) }; sb.AppendLine("UPDATE Supplier SET"); sb.AppendLine(" supplier_name = @supplier_name,"); sb.AppendLine(" addr = @addr,"); sb.AppendLine(" post_num = @post_num,"); sb.AppendLine(" tel = @tel,"); sb.AppendLine(" fax = @fax"); sb.AppendLine("WHERE supplier_cd = @supplier_cd"); //更新 updateCount = DbHelper.ExecuteNonQuery(sb.ToString(), parameters); return updateCount; } |
更新後のデータを表示
56行目～60行目で更新後のデータを検索し、表示しています。
更新結果
更新前の状態で検索をすると以下の状態です。
以下の内容でtel列とfax列をエクセル更新します。
エクセル取込更新ボタン後は以下が表示されます。
参照設定
SQLite
SQLite.dllがあるので、参照設定追加をしてください。
dllは「～UpdateDatabaseByExcel\SQLite\System.Data.SQLite.dll」のパスに配置されています。
