No.24 EXCEL連携(2) ～ビューをEXCELに書き出す

なにぬねのーつ

2007/05/09

NOTES, LotusScript, EXCEL

R5以上

EXCELOLE7.zip

ノーツとEXCELの連携を行うシリーズその2です。前回同様、EXCELとの連携にはOLEを使用します。OLEってなにって突っ込まれてもよくわかりませんが、似ていますがOTLとはなんら関係ないことないことだけは確かです。詳しいことはOLEさんに聞いてください。（もういいって

）

(1) 現在のビューをEXCELに書き出す

現在のビューをOLEを使って直接EXCELに書き出します。ノーツのビューにアクセスする方法はいくつかありますがまずはNotesViewEntryCollectionクラスを使用します。てっとり早く使いたい方は以下のコードを任意のビューのアクションボタンに記述するだけでOKです。

（重要）ノーツ6から実装されたImplode関数を使用しています。R5の場合は未実装のためImplode関数（R5版）を組み込む必要があります。

Sub Click(Source As Button) ' 現在のビューをEXCELに書き出す。 ' NotesUIView -> NotesView -> NotesViewEntryCollection -> NotesViewEntry ' EXCEL用の宣言 Dim xlApp As Variant ' Excel Application Dim xlbook As Variant ' Workbook Dim xlsheet As Variant ' Worksheet ' EXCELの起動 Set xlApp = CreateObject("Excel.Application") ' Excel OLE起動 xlApp.StatusBar = "ビューを書出し中です…" xlApp.Visible = True Set xlbook = xlApp.Workbooks.Add Set xlsheet = xlbook.Worksheets(1) 'xlApp.ScreenUpdating = False ' 画面自動更新OFF ' ノーツ用の宣言 Dim session As New NotesSession Dim ws As New NotesUIWorkspace Dim uiview As NotesUIView Dim view As NotesView Dim collection As NotesViewEntryCollection Dim entry As NotesViewEntry Dim column As NotesViewColumn Dim rows As Long, cols As Long, i As Long Dim maxrows As Long, maxcols As Long Dim v As Variant Set uiview = ws.CurrentView Set view = uiview.view rows = 1 maxcols = view.ColumnCount ' 最大列 ' 列見出しの作成 xlsheet.Rows(1).NumberFormat = "@" ' 文字列書式 For cols = 1 To maxcols Set column = view.columns(cols-1) xlsheet.Cells(rows, cols).Value = column.title Next ' ビューの出力 rows = rows + 1 Set collection = view.AllEntries maxrows = collection.count For i = 1 To collection.count Set entry = collection.GetNthEntry(i) For cols = 1 To Ubound(entry.ColumnValues) + 1 v = entry.ColumnValues(cols-1) If Instr(Typename(v), "STRING") = 1 Then ' STRING or STRING( ) xlsheet.Cells(rows, cols).NumberFormat = "@" ' 文字列書式 End If If Isarray(v) Then ' 複数値判定 xlsheet.Cells(rows, cols).Value = Implode(v, ",") Else xlsheet.Cells(rows, cols).Value = v End If xlApp.StatusBar = i & "/" & maxrows & " (" & Format(i/maxrows, "0%") & ")" Next rows = rows + 1 Next 'xlApp.ScreenUpdating = True ' 画面自動更新ON xlApp.StatusBar = "完了しました。" End Sub

EXCELオブジェクトの階層構造

EXCELオブジェクトの階層構造は以下のようになっています。

' EXCEL用の宣言
Dim xlApp As Variant ' Excel Application
Dim xlbook As Variant ' Workbook
Dim xlsheet As Variant ' Worksheet

' EXCELの起動
Set xlApp = CreateObject("Excel.Application") ' Excel OLE起動 …

xlApp.StatusBar = "ビューを書出し中です…"
xlApp.Visible = True
Set xlbook = xlApp.Workbooks.Add …

Set xlsheet = xlbook.Worksheets(1) …

'xlApp.ScreenUpdating = False ' 画面自動更新OFF…

CreateObjectでEXCELを起動します。

ワークブックを追加します。

ワークブックの最初のワークシートを取得します。

コメントを解除すると画面を更新しないため処理が大幅に短縮されます。ただし処理が完了するまで出力状況を確認することはできません。完了後にTrueに戻す必要があります。

とペアで設定します。

ノーツオブジェクトの階層構造

対してノーツオブジェクトの階層構造は以下のようになっています。

' ノーツ用の宣言
Dim session As New NotesSession
Dim ws As New NotesUIWorkspace
Dim uiview As NotesUIView
Dim view As NotesView
Dim collection As NotesViewEntryCollection
Dim entry As NotesViewEntry
Dim column As NotesViewColumn
Dim rows As Long, cols As Long, i As Long
Dim maxcols As Long, maxcols As Long
Dim v As Variant

Set uiview = ws.CurrentView …

Set view = uiview.view …

rows = 1
maxcols = view.ColumnCount ' 最大列

現在のビューを取得します。

そのバックエンドビューを取得します。

' 列見出しの作成
xlsheet.Rows(1).NumberFormat = "@" ' 文字列書式

For cols = 1 To maxcols
Set column = view.columns(cols-1)
xlsheet.Cells(rows, cols).Value = column.title
Next

EXCELの列見出しを作成します。ビューの列見出しはNotesViewのcolumnsプロパティに配列として格納されています。この配列は0から始まることに注意してください。

' ビューの出力
rows = rows + 1
Set collection = view.AllEntries …

For i = 1 To collection.count
Set entry = collection.GetNthEntry(i) …

For cols = 1 To Ubound(entry.ColumnValues) + 1
v = entry.ColumnValues(cols-1)
If Instr(Typename(v), "STRING") = 1 Then ' STRING or STRING( )
xlsheet.Cells(rows, cols).NumberFormat = "@" ' 文字列書式 …

End If
If Isarray(v) Then ' 複数値判定 …

xlsheet.Cells(rows, cols).Value = Implode(v, ",")
Else
xlsheet.Cells(rows, cols).Value = v
End If
xlApp.StatusBar = i & "/" & maxrows & " (" & Format(i/maxrows, "0%") & ")"
Next
rows = rows + 1
Next

ビューのすべてのエントリをコレクションとして取得します。この場合のコレクション（集合体）とは現在のビューのすべてのエントリ（行）を要素とする配列になります。

i番目のエントリを取り出します。

文字列値をセルの書式で文字列として設定します。例えば"00123"という値があった場合、書式を設定しないとEXCELは数値として扱うので結果として先頭から続く0が抜け落ち"123"となってしまいます。Typename関数はその引数が文字列の場合、"STRING"あるいは複数値の場合"STRING( )"を返します。Instr関数で"STRING"で始まる文字列かどうかを判断しています。

"複数値も可"にチェックが入っているフィールド、もしくはリスト式の場合の処理です。"複数値も可"のフィールドで代表的なものにはディスカッションのカテゴリ(Categories)があります。リスト式とはFieldA:FieldB:FieldCのように明示的にリスト演算子(コロン)でフィールド等を連結した式です。複数値の場合、それ自体が配列となっている（箱の中に箱がある）ので配列要素をImplodeで連結します。なおImplodeはノーツ6から実装された関数ですのでR5の場合は後述するImplode関数を自前で用意する必要があります。

'xlApp.ScreenUpdating = True ' 画面自動更新ON …

xlApp.StatusBar = "完了しました。"

処理スピードを上げる場合はコメントを解除します。

とペアで設定してください。

(2) 指定したビューをEXCELに書き出す

それでは現在のビューではなく指定したビューを書き出すにはどうしたらいいでしょうか？
これは簡単ですね。NotesUIViewからではなくNotesDatabaseのGetViewメソッドにより指定のビューを取得します。以下が修正箇所になります。

Dim db As NotesDatabase
Set db = session.CurrentDatabase
Set view = db.GetView("By Date") ' ビューの指定

Sub Click(Source As Button) ' 指定したビューをEXCELに書き出す。 ' NotesUIView -> NotesView -> NotesViewEntryCollection -> NotesViewEntry ' EXCEL用の宣言 Dim xlApp As Variant ' Excel Application Dim xlbook As Variant ' Workbook Dim xlsheet As Variant ' Worksheet ' EXCELの起動 Set xlApp = CreateObject("Excel.Application") ' Excel OLE起動 xlApp.StatusBar = "ビューを書出し中です…" xlApp.Visible = True Set xlbook = xlApp.Workbooks.Add Set xlsheet = xlbook.Worksheets(1) 'xlApp.ScreenUpdating = False ' 画面自動更新OFF ' ノーツ用の宣言 Dim session As New NotesSession Dim ws As New NotesUIWorkspace 'Dim uiview As NotesUIView Dim view As NotesView Dim collection As NotesViewEntryCollection Dim entry As NotesViewEntry Dim column As NotesViewColumn Dim rows As Long, cols As Long, i As Long Dim maxrows As Long, maxcols As Long Dim v As Variant Dim db As NotesDatabase Set db = session.CurrentDatabase Set view = db.GetView("By Date") ' ビューの指定 rows = 1 maxcols = view.ColumnCount ' 最大列 ' 列見出しの作成 xlsheet.Rows(1).NumberFormat = "@" ' 文字列書式 For cols = 1 To maxcols Set column = view.columns(cols-1) xlsheet.Cells(rows, cols).Value = column.title Next ' ビューの出力 rows = rows + 1 Set collection = view.AllEntries maxrows = collection.count For i = 1 To collection.count Set entry = collection.GetNthEntry(i) For cols = 1 To Ubound(entry.ColumnValues) + 1 v = entry.ColumnValues(cols-1) If Instr(Typename(v), "STRING") = 1 Then ' STRING or STRING( ) xlsheet.Cells(rows, cols).NumberFormat = "@" ' 文字列書式 End If If Isarray(v) Then ' 複数値判定 xlsheet.Cells(rows, cols).Value = Implode(v, ",") Else xlsheet.Cells(rows, cols).Value = v End If xlApp.StatusBar = i & "/" & maxrows & " (" & Format(i/maxrows, "0%") & ")" Next rows = rows + 1 Next 'xlApp.ScreenUpdating = True ' 画面自動更新ON xlApp.StatusBar = "完了しました。" End Sub

(3) ビューを書き出すもう一つの方法～ NotesViewNavigatorクラス

これまでNotesViewEntryCollectionを使用してきましたが、ビューエントリへのアクセスには他にもいくつか手段が存在します。NotesViewEntryCollectionとともにR5から実装されたものにNotesViewNavigatorクラスがあります。似たようなメソッド・プロパティを持ち兄弟とも言えるクラスですがNotesViewEntryCollectionクラスとの大きな違いはカテゴリエントリにアクセスできることです。（これは後述します）

先ほどのノーツオブジェクトの階層構造に新しいクラスを加えてみましょう。

(1)をコードをNotesViewNavigatorクラスで書き換えると以下のようになります。なおGetFirstDocumentは紛らわしいですがNotesDocumentではなくNotesViewEntryを返しますのでお間違いのないよう。

Sub Click(Source As Button) ' 現在のビューをEXCELに書き出す。<GetFirstDocument版> ' NotesUIView -> NotesView -> NotesViewNavigator -> NotesViewEntry ' EXCEL用の宣言 Dim xlApp As Variant ' Excel Application Dim xlbook As Variant ' Workbook Dim xlsheet As Variant ' Worksheet ' EXCELの起動 Set xlApp = CreateObject("Excel.Application") ' Excel OLE起動 xlApp.StatusBar = "ビューを書出し中です…" xlApp.Visible = True Set xlbook = xlApp.Workbooks.Add Set xlsheet = xlbook.Worksheets(1) 'xlApp.ScreenUpdating = False ' 画面自動更新OFF ' ノーツ用の宣言 Dim session As New NotesSession Dim ws As New NotesUIWorkspace Dim uiview As NotesUIView Dim view As NotesView Dim nav As NotesViewNavigator Dim entry As NotesViewEntry Dim column As NotesViewColumn Dim rows As Long, cols As Long, i As Long Dim maxrows As Long, maxcols As Long Dim v As Variant Set uiview = ws.CurrentView Set view = uiview.view Set nav = view.createViewnav() rows = 1 maxcols = view.ColumnCount ' 最大列 ' 列見出しの作成 xlsheet.Rows(1).NumberFormat = "@" ' 文字列書式 For cols = 1 To maxcols Set column = view.columns(cols-1) xlsheet.Cells(rows, cols).Value = column.title Next ' ビューの出力 rows = rows + 1 Set entry = nav.GetFirstDocument While Not (entry Is Nothing) For cols = 1 To Ubound(entry.ColumnValues) + 1 v = entry.ColumnValues(cols-1) If Instr(Typename(v), "STRING") = 1 Then ' STRING or STRING( ) xlsheet.Cells(rows, cols).NumberFormat = "@" ' 文字列書式 End If If Isarray(v) Then ' 複数値判定 xlsheet.Cells(rows, cols).Value = Implode(v, ",") Else xlsheet.Cells(rows, cols).Value = v End If xlApp.StatusBar = rows Next rows = rows + 1 Set entry = nav.GetNextDocument(entry) Wend 'xlApp.ScreenUpdating = True ' 画面自動更新ON xlApp.StatusBar = "完了しました。" End Sub

(4) ビューの集計行のみEXCELに書き出す

ビューの文書が数万行あったらどうでしょう。OLEによるEXCEL書き出しではとても遅いので集計結果だけをEXCELに書き出したいと考えるかもしれません。これには前述のNotesViewNavigatorクラスを使用します。

まずノーツ側で以下のようなカテゴリを持つ集計用ビューを作成しておきます。

以下のようにEXCELに出力されます。集計行のみ出力され商品名など明細列は出力されません。

GetFirstとGetNextCategoryのコンビで使用するがもっとも効率がいいでしょう。NotesViewNavigatorクラスには前述のGetFirstDocumentとGetFirstの２つのメソッドがありGetFirstDocumentはカテゴリエントリをスキップするのに対しGetFirstはカテゴリエントリも含みます。

Sub Click(Source As Button) ' 現在のビューをEXCELに書き出す。<GetFirst+GetNextCategory版> カテゴリ（集計行）のみ出力 ' NotesUIView -> NotesView -> NotesViewNavigator -> NotesViewEntry ' EXCEL用の宣言 Dim xlApp As Variant ' Excel Application Dim xlbook As Variant ' Workbook Dim xlsheet As Variant ' Worksheet ' EXCELの起動 Set xlApp = CreateObject("Excel.Application") ' Excel OLE起動 xlApp.StatusBar = "ビューを書出し中です…" xlApp.Visible = True Set xlbook = xlApp.Workbooks.Add Set xlsheet = xlbook.Worksheets(1) 'xlApp.ScreenUpdating = False ' 画面自動更新OFF ' ノーツ用の宣言 Dim session As New NotesSession Dim ws As New NotesUIWorkspace Dim uiview As NotesUIView Dim view As NotesView Dim nav As NotesViewNavigator Dim entry As NotesViewEntry Dim column As NotesViewColumn Dim rows As Long, cols As Long Dim maxcols As Long Dim v As Variant Set uiview = ws.CurrentView Set view = uiview.view Set nav = view.createViewnav() ' 列見出しの作成 rows = 1 maxcols = view.ColumnCount ' 最大列 xlsheet.Rows(1).NumberFormat = "@" ' 文字列書式 For cols = 1 To maxcols Set column = view.columns(cols-1) xlsheet.Cells(rows, cols).Value = column.title Next ' ビューの出力 rows = rows + 1 Set entry = nav.GetFirst While Not (entry Is Nothing) If entry.IsCategory Then For cols = 1 To Ubound(entry.ColumnValues) + 1 v = entry.ColumnValues(cols-1) If Instr(Typename(v), "STRING") = 1 Then ' STRING or STRING( ) xlsheet.Cells(rows, cols).NumberFormat = "@" ' 文字列書式 End If If Isarray(v) Then ' 複数値判定 xlsheet.Cells(rows, cols).Value = Implode(v, ",") Else xlsheet.Cells(rows, cols).Value = v End If Next rows = rows + 1 End If Set entry = nav.GetNextCategory(entry) Wend 'xlApp.ScreenUpdating = True ' 画面自動更新ON xlApp.StatusBar = "完了しました。" End Sub

(2007/12/16) 各エントリの最大列の取得方法を訂正しました。カテゴリエントリの最大列はビューの最大列とは一致しない場合があります。→47行目のUbound

まとめ

このページではビューの各エントリへのアクセスにNotesViewEntryCollectionクラスおよびNotesViewNavigatorクラスを使用する方法をご紹介しました。くどいようですがともにR5から実装されたものです。それではR5以前ではエントリへのアクセスに何を使用していたのでしょうか。NotesViewにはGetFirstDocument等のNotesDocumentを取得するメソッドがあります。さらにNotesDocumentにはNotesViewEntryクラスと同じColumnValuesプロパティがあります。これはR5.0.13では正しく動作しません。ノーツ7ではちゃんと動作するところをみるとおそらくバグだと思います。まあR5以上であればNotesDocumentを使う理由はありませんが。
ノーツ6.5よりCSV出力がサポートされましたが、かといってEXCELへのOLE出力が不要になったかといえばそうでもありません。CSVフォーマットはテキストファイルであるためフィールドに属性はありません。EXCELはCSVを開く際、その自動認識機能のため"00001"など数字のみで構成される値は数値として認識し"1"となります。テキストファイルウィザードを使用すればこの問題も回避できるのですがいちいち属性を指定しないといけないので面倒です。また罫線を引く、ヘッダを固定する、セルに色を付ける、計算式を埋め込むといった細かい芸当はOLE（VBA)ならではのものです。ただし書き出しのスピードが要求される場合はCSV出力を選択するか、次のTIPSをご覧ください。

使用条件・免責事項

[戻る]