VSTO だと、外部データと連携した一覧帳票の作成も簡単です！

Excel の一覧表を利用した使い方は非常に多いと思います。

とにかく表を管理するというやり方です。

多くの利用者はこの一覧表を手動で管理しているのかなと思います。

例えば、以下のようなものです。

この場合、コピーアンドペーストの多用や入力ミスが発生するのかなと思います。

また、データベースに保存する際の仕組みは別になるとか。

同じ入力を別なアプリケーションで実装しているというケースですね。

Excel VSTO ではこのような多重入力は手作業による Excel 一覧表の作成を大幅に簡略化してくれます。

例えば、以下のようなものを作成してみました。

この一覧表のメリットは３つあります。

1. 得意先名を選択することで、関連するデータがテキストボックスに表示され、データをシートに自動入力できます。

2. 誤入力がない。

3. Excel シートデータベースと連動しているので、データの入力ボタンを押すと自動的に内容がデータベースにアップデートされます。

この仕組みは VBA でもおこなわれていたケースがあるとは思います。

また、Windows フォームでも利用していたのかと思います。

でも、VSTO では、そのままの Excel で簡単なコーディングのみで、これらのメリットを実現してくれます。

今回はすべてのコードの紹介ではなく、要点のみについて、ご紹介します。

もちろん、サンプルは DB のみで公開いたします。

手順：

1. 準備

1.1 添付のデータベースを SQL Server のデータベースとしてマージしてください。

※添付のデータベースは Access 2003 の NorthWind サンプルデータベースを SQL サーバーにアップサイジングウィザードで持っていったものです。

2. データソースの構成

2.1 Excel ブックのソリューションを起動します。

2.2 データソース構成ウィザードを起動します。

2.3 テーブルの設定で [社員] テーブルの"社員コード"にチェックを入れます。

2.4 同じく、[受注] テーブルで、"出荷先住所2" と "締切日" 以外のフィールドにチェックを入れます。

2.5 次に [得意先] テーブルで、"得意先コード"、"得意先名"、"郵便番号"、"都道府県"、"住所1" にチェックを入れ、[完了]ボタンを押します。

2.6 データソース接続の完成です。

3.データソースの情報をシートにバインドします。

3.1 データソースの[受注]をシートのセル "A1" にドラッグアンドドロップします。

3.2 ListObject が作成され、テーブルのフィールドがシート上に表示されます。

※受注ListObject という テーブル名 (名前付き範囲の拡張みたいなものです。) が定義されます。

3.3 デバッグをするとデータソースと連携した一覧表が起動します。

※ここまででも十分、データ連携ができる Excel シートの利用ができます。

シートの Sheet_Change イベントで、Update 構文を入れることで、シート内のデータ変更がデータベースに反映されます。

4. 作業ウィンドウの作成

4.1 ユーザーコントロールの追加で "ユーザーコントロール" を追加します。

※サイズは Width 400、Height 735 くらいに設定しておきます。

4.2 [得意先]テーブルの "得意先名"のコントロールを ComboBox に変更します。

4.3 作業ウィンドウのコントロールに "得意先名"、"得意先コード"、"郵便番号"、"都道府県"、"住所1" をドラッグアンドドロップします。

 

4.4 自動で生成されたユーザーコントロール内のバインディングナビゲーター以下のコードを削除します。

4.5 また、デザイナ上でも、バインディングナビゲーターのコントロールを削除します。

4.6 次に "得意先名ComboBox"コントロールのプロパティを設定します。

DataSource 項目の設定で、"得意先BindingSource" を設定します。

4.7 DisplayMember プロパティで "得意先名" を設定します。

4.8 UserControl1.vb で得意先テーブルのデータを呼び出すコードを記載します。

Private Sub UserControl1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    得意先TableAdapter.Fill(NorthwindSQLDataSet.得意先)
End Sub

また、作業ウィンドウをシートのスタートアップ時に起動する以下のコードを ThisWorkBook.vb のスタートアップに記載します。

Dim uc As New UserControl1
Globals.ThisWorkbook.ActionsPane.Controls.Add(uc)

4.9 ここでデバッグをして、動きを確認してみます。

以下の図のように、得意先名を選択することで、関連するデータが変更されます。

4.10 [社員]テーブルの "社員"のコントロールを ComboBox に変更します。

4.11 作業ウィンドウのコントロールに "社員コード" をドラッグアンドドロップします。

 

4.12 次に "社員コードComboBox"コントロールのプロパティを設定します。

DataSource 項目の設定で、"社員BindingSource" を設定します。

4.13 DisplayMember プロパティで "社員コード" を設定します。

4.14 UserControl1.vb で社員コードテーブルのデータを呼び出すコードを記載します。

Private Sub UserControl1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    得意先TableAdapter.Fill(NorthwindSQLDataSet.得意先)
    社員TableAdapter.Fill(NorthwindSQLDataSet.社員)
End Sub

4.15 デバッグし、社員コードの情報をデータソースから取得することができるようになることを確認します。

4.16 次に "運送区分"、"受注日"、"出荷日" のフィールドに対応したコントロールを配置します。

このコントロールはデータ連携の必要はありませんので、ツールボックスから、配置してください。

"運送区分" については、リストボックスを利用し、"受注日"、"出荷日"についてでは、DateTimePicker コントロールを利用します。

イメージとしては以下のような感じです。

5．シートへのデータ入力とデータベースへの更新

次に作業ウィンドウで表示されているデータをシートの末尾の新規レコードとし、かつ、データベースへ更新する方法についてご説明します。

5.1 作業ウィンドウにボタンコントロールを配置します。

ボタン Click 時のイベントハンドリングに以下のコードを記載します。

※今回はExcel 独自のコードのお作法についてのみ、コメントいたします。

'受注ListObject テーブルの最終行を取得します。
Dim currentRow As Integer = Globals.Sheet1.受注ListObject.Range.Rows.Count.ToString()
'受注コードの最終行の値を取得します。
Dim lastRowNum As String = Globals.Sheet1.Cells(currentRow, 1).value
Dim ariaName As String = Me.都道府県TextBox.Text

If ariaName = "東京都" Then
     ListBox1.SelectedIndex = 0 
ElseIf ariaName = "北海道" Then
     ListBox1.SelectedIndex = 2
 ElseIf ariaName = "福岡県" Then
     ListBox1.SelectedIndex = 2
ElseIf ariaName = "長崎県" Then
     ListBox1.SelectedIndex = 2
ElseIf ariaName = "大分県" Then
     ListBox1.SelectedIndex = 2
 ElseIf ariaName = "熊本県" Then
     ListBox1.SelectedIndex = 2
 ElseIf ariaName = "佐賀県" Then
     ListBox1.SelectedIndex = 2
 ElseIf ariaName = "宮崎県" Then
     ListBox1.SelectedIndex = 2
 ElseIf ariaName = "鹿児島県" Then
     ListBox1.SelectedIndex = 2
ElseIf ariaName = "沖縄県" Then
     ListBox1.SelectedIndex = 2
Else
     ListBox1.SelectedIndex = 1
End If

If DateTimePicker1.Value > DateTimePicker2.Value Then
     MessageBox.Show("受注日は出荷日の前にしてください。")
     Exit Sub
End If

'リストオブジェクトの行を追加します。
Globals.Sheet1.受注ListObject.ListRows.AddEx()

Globals.Sheet1.Cells(currentRow + 1, 1).value = lastRowNum + 1
Globals.Sheet1.Cells(currentRow + 1, 2).value = Me.得意先コードTextBox.Text
Globals.Sheet1.Cells(currentRow + 1, 3).value = Me.社員コードComboBox.Text
Globals.Sheet1.Cells(currentRow + 1, 4).value = Me.得意先名ComboBox.Text
Globals.Sheet1.Cells(currentRow + 1, 5).value = Me.郵便番号TextBox.Text
 Dim deliverNo As String = Me.ListBox1.Text
Globals.Sheet1.Cells(currentRow + 1, 6).value = Me.都道府県TextBox.Text
Globals.Sheet1.Cells(currentRow + 1, 7).value = Me.住所1TextBox.Text
Globals.Sheet1.Cells(currentRow + 1, 8).value = deliverNo.Chars(0).ToString
Globals.Sheet1.Cells(currentRow + 1, 9).value = Me.DateTimePicker1.Text
Globals.Sheet1.Cells(currentRow + 1, 10).value = Me.DateTimePicker2.Text 
'配送区分の選択肢により、配送料を自動的に入力します。
If ListBox1.SelectedIndex = 0 Then
     Globals.Sheet1.Cells(currentRow + 1, 11).value = 1000
ElseIf ListBox1.SelectedIndex = 1 Then
     Globals.Sheet1.Cells(currentRow + 1, 11).value = 2000
ElseIf ListBox1.SelectedIndex = 2 Then
     Globals.Sheet1.Cells(currentRow + 1, 11).value = 3000
End If

'シートチェンジイベントをキックさせるために、一度、選択するセルをテーブル外を参照して、追加された最終行を参照します。
Globals.Sheet1.Range("A" & currentRow + 1).Select()
Globals.Sheet1.Range("A" & currentRow + 2).Select()

5.2 Sheet.vb で、Sheet_Change イベントハンドリングを生成します。

Private Sub Sheet1_Change(ByVal Target As Microsoft.Office.Interop.Excel.Range) Handles Me.Change
    Globals.Sheet1.受注TableAdapter.Update(NorthwindSQLDataSet.受注)
End Sub

※このコードを 1行入れるこどで、データ連携している 受注ListObject のデータがデータセット（メモリ）を経由して、データベースにアップデートされます。

今回のシナリオでは、新規行を作成して、その行をデータベースにアップデートしますが、途中の行のデータを変更しても、同様の動作がおこなわれます。

以下は SQL 側の画面です。更新されているのが分かるかと思います。

ということで、今回は作業ウィンドウによる新規データの入力とシートチェンジイベントによるデータ更新について、ご紹介させていただきました。

実際に確認してみたい方はサンプルをダウンロードしてみてください。

Excel_List_SQL.zip

Comments

• Anonymous
  November 03, 2008
  <p>VSTO (Visual Studio Tools for Office)、SharePoint 開発、OBA (Office Business Applicaiton) と、Office System 周りの開発、技術が最近楽しくて仕方ありません。</p> <p>ということで、お役立ちページのブックマーク。</p> ...