データベースから取得したレコードを Excel シートの行に挿入するサンプル（発注書）VB版

[今日のみちしるべ 第二回 自分のルーツ]

今日は水道橋で社員総会でした。

帰りに神保町によってきたのですが、神保町は私にとって非常に思い出深い町で、いろんなご縁がありました。

学生時代から就職して数年ほど、この町をうろうろしていました。（もちろん仕事です。）

学生時代は友達がいたり、学校の帰りにこのあたりでお茶したりとか、就職してからは、このあたりが担当だったので、うろうろ（仕事をさぼってもいましたが。）していました。

嫁もそういえば、このあたりが職場でした。

なにかとご縁があるのですが、神保町はご存じのとおり、本の町です。今はだいぶ、古本屋が少なくなり、食堂などに変わってきていますが、まだまだ、根強く残っている老舗もあります。町の雰囲気はあまり変わっていなかったのでうれしいです。

神保町を回っていた頃はハードウェアのエンジニア（CE さんと呼ばれていましたねー。響きが懐かしいです。）でした。

ホストから出力されるラインプリンターや今の PC の走りである IBM PS55 シリーズとか、ThinkPad の初期型など、そのあたりのエンジニアをやっておりました。

今と違い、IT という言葉がまだ、無い頃なので、少しくらい機器が止まってもなんとかなる（笑）ような状況で、ハマルと 2、3日、機械が止まってしまうこともざらでした。

さすがに数日機械が止まってしまうと、お客様もお怒りで、冷たい視線が飛び交い、居心地が悪い中、冷や汗をかき、修理をおこなっておりました。

そんな事を思い出しながら、今の仕事に通ずる、私のこの業界のルーツってなんだろうと考えたところ、CE 時代の問題の切り分け（トラブルシュート）能力をとても鍛えられたのかなと思いました。

トラブルが発生して、エラーコードを頼りに修理をしてもうまくいかない場合、頭の中でフローチャートが走ります。

そのフローチャートのどこかで条件式などを間違えてしまうと、また、ハマルというループ処理に入ってしまいます。

なので、トラブルの状況から考えられる、短時間に解決できる適切なフローチャートを事前に頭に作成するというところでは身に付いたかなという感じです。

その後、数度の転職をする際の面接でも、一番得意なところは？と聞かれた際に「切り分け」と言った気がします。

自分のルーツで培った能力で、なんとかここまでこれたのかもしれません。

今はこのような場でプログラムを書いて、紹介したりもしていますが、一番、好きなのは動かないプログラムを動かすようにするデバッグの時です。（笑）

[本編]

それでは、また、長くなりましたが、本編に入りたいと思います。

このサンプルも先般、ご紹介したうちの先輩と SharePoint MVP の山崎愛さんが VSTO2005 の時に作成されたネタを VSTO2008 に移行しています。

動き的には非常によいサンプルだと思いますので、公開したいと思います。

全体的なところでは、データソースにバインドしたナビゲーションウィンドウを選択し、表示されたレコードのデータをクラスを通して、シート上に挿入するというものです。

イメージ的には以下のような流れになります。

手順：

1．ストアドプロシージャーを作成します。

1.1 Visual Studio 2008 を起動します。

1.2 [サーバー エクスプローラ]を開きます。

1.3 [データ接続]を右クリックし、[接続の追加]をクリックします。

1.4 [接続の追加]ダイアログ ボックスで以下のように指定します。
■データソース: Microsoft SQL Server (SqlClient)
■サーバー名: (local)
■Windows 認証を使用する
■データベースの選択または入力:
AdventureWorks

1.5 [テスト接続]ボタンをクリックして、テスト接続が成功することを確認し、[OK]をクリックします。

1.6 [データ接続]-[コンピュータ名.AdventureWorks.dbo]-[ストアドプロシージャ]を右クリックし、[新しいストアドプロシージャの追加]をクリックします。

1.7 表示される内容をすべて削除し、以下の内容を貼り付けます。

CREATE PROCEDURE dbo.GetProductInfo
AS
SELECT Prod.ProductID,Name,ProductNumber,ListPrice,Size,Weight,LargePhoto
FROM Production.Product Prod
INNER JOIN Production.ProductProductPhoto ProdPhoto
ON Prod.ProductID=ProdPhoto.ProductID
INNER JOIN Production.ProductPhoto Photo
ON ProdPhoto.ProductPhotoID=Photo.ProductPhotoID
WHERE Not Prod.ListPrice=0.00

1.8 [閉じる]ボタンをクリックして、内容を保存して閉じます。

ストアドプロシージャの作成が終わりました。

2. データソースの接続を設定します。

2.1 Visual Studio 2008を起動します。

2.2 新規にExcelブックのプロジェクトを作成し、名前を「OrderForm1」と入力します。

2.3 [アプリケーションのドキュメントを選択する]で[既存のドキュメントをコピーする]を選択し、添付している OrderSheet1.xlsx を指定し、[OK]をクリックします。

※このように VSTO では既存のドキュメントのカスタマイズも可能です。

2.4 既にレイアウトが作成された発注書シートが開きます。

2.5 [データ] メニューから[新しいデータソースの追加]をクリックします。

2.6 データソースの種類として[データベース]を選択し[次へ]をクリックします。

2.7 [データ接続]で「コンピュータ名.AdventureWorks.dbo」を選択します。

2.8 データベース構成ウィザードに戻り、「接続文字列」を確認したら[次へ]をクリックします。

2.9 既存の設定のまま、「次へ」をクリックします。

2.10 データベースオブジェクトとして「ストアドプロシージャ」-「GetProductInfo」のチェックボックスをオンにします。

2.11 [終了]をクリックします。

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

3.1 [プロジェクト]メニューから[ユーザーコントロールの追加]をクリックします。

3.2 [ファイル名]にProductInfoと指定し、[追加]ボタンをクリックします。

3.3 追加したProductInfoユーザーコントロールのプロパティを以下のように指定します。

オブジェクト：

ProductInfo

プロパティ値：

Size： 280, 550

BackColor: GradientInactiveCaption

3.4 [データソース ウインドウ]のGetProductInfoのドロップダウンメニューを開き、「詳細」を選択します。

続いて、Product IDからWeightまでをラベルコントロールに変更し、LargePhoto を PictureBoxコントロールに変更します。

3.5 GetProductInfo項目をProductInfoユーザーコントロール上にドラッグドロップします。

3.6 Large PhotoのPictureBoxコントロールを選択し、適当な位置に移動し、プロパティを以下のように設定します。

オブジェクト: LargePhotoPictureBox

プロパティ値:

Size: 200, 150

BackColor (Web): White

BorderStyle: Fixed3D

4. ProductInfoユーザーコントロールにデータを読み込むコードを記述します。

4.1 [ソリューション エクスプローラ]からProductInfo.vbを右クリックし [コードの表示] をクリックします。

4.2 クラス名リストから「(ProductInfoイベント)」を選択します。

4.3 イベント名リストから「Load」を選択します。

4.4 ProductInfo_Loadイベントプロシージャ内に次のコードを記述します。

Me.GetProductInfoTableAdapter.Fill(Me.AdventureWorksDataSet.GetProductInfo)

4.5 次に作業ウィンドウを表示するコードを ThisWorkbook.VB に記述します。

Dim ProductInfoPane As ProductInfo

Globals.ThisWorkbook.ActionsPane.Controls.Add(ProductInfoPane)

4.6 プロジェクトファイルを保存し、ビルド実行してみます。作業ウィンドウが起動することが確認できたら画面を閉じます。

5. 作業ウィンドウのボタンからシートにデータを挿入する方法

5.1 [プロジェクト]-[クラスの追加]で、クラスを選択し追加します。

クラス名は AddItemClass.VB にします。

5.2 クラスに以下のコードを記述します。

もしくは、添付の AddItemClass.VB をプロジェクトに挿入してください。 

※クラスの利用で作業ウィンドウのコントロールの値を利用して、複数の処理を一度に実装しています。

Public Class AddItemClass

    Public Sub AddItemToSheet(ByVal ProductNumber As String, ByVal ProductName As String, ByVal ProductUnitPrice As Decimal)
        '発注書の最終行を求める
        Dim RangeLast As Microsoft.Office.Interop.Excel.Range

        RangeLast = Globals.Sheet5.Range("D8").End(Excel.XlDirection.xlDown) 
        If RangeLast.Row > 18 Then
            MessageBox.Show("一度に10個以上の商品の発注はできません", "確認してください", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If

        '発注書の行の範囲を求めます。
Dim RangeProductNumbers As Excel.Range
        RangeProductNumbers = Globals.Sheet5.Range(Globals.Sheet5.Range("D9"), RangeLast)

       '挿入された行を宣言します。 
       Dim RangeProductNumber As Excel.Range

       '重複した内容が発生した場合にメッセージを表示します。
For Each RangeProductNumber In RangeProductNumbers
            If RangeProductNumber.Value = ProductNumber Then
                MessageBox.Show("この商品は既に発注書に追加されています", "確認してください", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Sub
            End If
        Next

        '挿入行の列の値を作業ウィンドウから持ってきて、セットします。

With RangeLast
            .Offset(1, 0).Value = ProductNumber
            .Offset(1, 1).Value = ProductName
            .Offset(1, 3).Value = ProductUnitPrice
        End With

        Globals.ThisWorkbook.RefreshAll()
    End Sub

End Class

5.3 [ProductInfo.vb[デザイン]]タブをクリックします。

5.4 ProductInfoユーザーコントロールの任意の場所に、ツールボックスからButtonコントロールをドラッグドロップします。

5.5 追加したボタンコントロールのプロパティを次のように指定します。

オブジェクト:Button1

プロパティ:

(Name):btnAddItem

Text: 発注書に追加

Size:85,40

5.6 以下のコードを追加します。

Dim AddOrderItem As New AddItemClass
AddOrderItem.AddItemToSheet(Me.ProductNumberLabel1.Text, Me.NameLabel1.Text, Me.ListPriceLabel1.Text)

以上で設定は終わりです。[デバッグ開始] をクリックして、動作確認をしてみてください。

またまた、長くなってしまいましたが（笑）、このような動きをおこなう Excel 帳票を作成することも可能です。

次回はこのサンプルの C# 版を公開します。

OrderForm1.zip