出直し!! ドミノ塾: Notes - Excel 連携：#60）ビューをそのまま Excel へ ⑤

ビューをビューっぽく Excel に出力する方法の最終回です。今回はビューのカテゴリが開閉できる機能を Excel のグループ機能で実現します。

グループ機能

Excel のメニューの［データ］－［アウトライン］－［グループ化］メニューを使うとグループ機能を有効化できます。事前に行を選択しておくと行に対するグループ化、列を選択すると列に対してグループ化されます。今回はカテゴリのように使うので行に対するグループ化を利用します。

VBA でグループ化を行うには Group メソッドを使用します。

Range.Group メソッド (Excel)

このメソッドは Range オブジェクトに属します。作成中のサンプルで利用するには、グループ化する行の範囲の Range オブジェクトを取得して Group メソッドをコールすることになります。

グループ化する範囲の取得

それでは、サンプルのビューを使って具体的に検討します。

グループ化を行うには、2023 年のカテゴリでは 5 行目、9 月のカテゴリでは 10 行目というように、そのカテゴリがスタートした行番号が必要となります。

この開始行はカテゴリ情報とセットで必要となるので、カテゴリの出力順調整で使用したスタック領域を拡張して開始行も保持させると都合がいいですね。

カテゴリ	スタック領域
カテゴリ	カテゴリ行のカラムデータ	開始行
レベル１	2024 年	5
レベル２	9 月	10

開始行はカテゴリ直後の明細行（文書）となります。カテゴリを読み込んだ後に明細行を発見したら記録するという流れで対応できます。複数のカテゴリを設定しているビューでは、カテゴリ行が続く可能性があることにも配慮が必要です。

サンプルの修正

前回のプログラムからの修正点を記載します。修正箇所が多岐にわたるため、関数全体を掲載し、修正箇所を強調して表示します。

◇ メインルーチンの修正

まず、開始行を保持させる変数として aiStack_RowNo 変数を定義します。この変数に値をセットする判定に使用しているのが bSaveRowNo です。カテゴリ行を読み込んだら True となり、True の状態で明細行を読み込んだら、行番号を記録して False に戻しています。

カテゴリを出力するとその開始行に 0 を設定しており、開始行を記録するタイミングでは iStack_RowNo が 0 の場合にのみ記録させています。これは、複数カテゴリのビューで、複数のレベルに対して開始行を記録できるようにするためです。

%REM
ビューの全データを Excel シートに出力

◆ 引数
voSheet Variant Excel シート
vnvExport NotesView 出力するビュー
vvCtgColNo Variant カテゴリ列番号のリスト（ない場合最初の要素が0）
vvCtgColor Variant カテゴリ列の文字色のリスト（要素数はrvCtgColNoと同じ）

◆ データ型（戻り値）　 Integer
次の出力行番号（最終出力行 + 1）
%END REM
Function xPrintAllData(voSheet As Variant, vnvExport As NotesView, vvCtgColNo As Variant, vvCtgColor As Variant) As Integer
Dim nvnav As NotesViewNavigator
Dim nve As NotesViewEntry

'カテゴリを含むビューの情報を取得
Set nvnav = vnvExport.CreateViewNav()

'スタックエリア用変数
Dim avStack_Value() As Variant 'カテゴリ行の全カラムデータ
Dim aiStack_RowNo() As Integer 'カテゴリのスタート行（明細行）
Dim bSaveRowNo As Boolean '次の明細行で開始位置を記録すべき時は True
Dim i As Integer

Dim iRow As Integer
Dim vLine As Variant
Dim iLv_New As Integer '読み込んだカテゴリレベル
Dim iLv_Cur As Integer '処理中のカテゴリレベル

'スタックエリア確保（カテゴリ数と同じ）
ReDim avStack_Value(UBound(vvCtgColNo))
ReDim aiStack_RowNo(UBound(vvCtgColNo))

'ビューの出力開始
iRow = 2 'シートの 2 行目から出力
iLv_Cur = 0 'カテゴリレベル

'ビューの 1 行目を取得
Set nve = nvnav.GetFirst()

'ビューのデータがなくなるまでループ
Do Until (nve Is Nothing)
   '現在のビュー行の全カラム値を取得
   vLine = nve.ColumnValues

   '行タイプに応じた処理
   If nve.IsTotal Then
      '合計行（＝最終行）
      '未出力のカテゴリがあれば出力
      iRow = xPrintLine_Category(voSheet, iRow, 1, iLv_Cur, avStack_Value, aiStack_RowNo, vvCtgColNo, vvCtgColor)
      iLv_Cur = 0 'スタックのカテゴリはない

      '合計出力
      voSheet.Rows(iRow).Interior.Color = RGB(238, 238, 238) '背景は薄いグレー
      iRow = xPrintLine(voSheet, iRow, vLine)
   ElseIf nve.IsCategory Then
      'カテゴリ列
      '読み込んだカテゴリレベル取得
      iLv_New = xGetCategoryLv(vLine, vvCtgColNo)

      'スタックのカテゴリの処理
      If iLv_New <= iLv_Cur Then
         '読み込んだカテゴリは現在と同じか上位のカテゴリ
         '読み込んだカテゴリまでを出力
         iRow = xPrintLine_Category(voSheet, iRow, iLv_New, iLv_Cur, avStack_Value, aiStack_RowNo, vvCtgColNo, vvCtgColor)

         '明細行開始位置のリセット
         For i = iLv_New To iLv_Cur
            aiStack_RowNo(i - 1) = 0
         Next
End If

      '読み込んだカテゴリをセット
      avStack_Value(iLv_New - 1) = vLine 'カテゴリをスタックに記録
      iLv_Cur = iLv_New   '読み込んだカテゴリレベルを記憶
bSaveRowNo = True '次の明細行でカテゴリ開始位置を記録させる
Else
'明細行開始位置の管理
      If bSaveRowNo Then
         '開始位置の記録が必要
         For i = 1 To iLv_Cur
            '未設定の階層にだけ行番号をセット
            If aiStack_RowNo(i - 1) = 0 Then
               aiStack_RowNo(i - 1) = iRow
            End If
         Next
         bSaveRowNo = False
      End If

'明細行（= 文書）
      iRow = xPrintLine(voSheet, iRow, vLine)
   End If

   'ビューの次の行を取得
   Set nve = nvnav.GetNext(nve)
Loop

'残りのカテゴリがあれば出力（= 合計のないビュー用）
If iLv_Cur > 0 Then
   iRow = xPrintLine_Category(voSheet, iRow, 1, iLv_Cur, avStack_Value, aiStack_RowNo, vvCtgColNo, vvCtgColor)
End If

xPrintAllData = iRow
End Function

◇ グループ化の実行

Excel でにカテゴリ行を出力する xPrintLine_Category 関数でグループ化の処理を行います。この設定でカテゴリの開始行が必要となるので、メインルーチンで記録した値を引数 vvStack_RowNo で受け取ます。

%REM
カテゴリ行を Excel シートに出力

◆ 引数
voSheet Variant Excel シート
viRow Integer 出力行（シートの行番号）
viMin Integer 出力するカテゴリレベル（上位）
viMax Integer 出力するカテゴリレベル（下位）
vvStack_Value Variant カテゴリ行の値（配列、1要素が1行分の値（配列）を保持）
vvStack_RowNo Variant カテゴリの開始行（配列）
vvCtgColNo Variant カテゴリ列番号のリスト（配列）
vvCtgColor Variant カテゴリ列の文字色のリスト（配列）
　
◆ データ型（戻り値）　 Integer
次の出力行番号
　
◆ 使い方
viMin ～ viMax 間のカテゴリを出力します。
下位カテゴリである viMax から順に出力します。
%END REM
Function xPrintLine_Category(voSheet As Variant, ByVal viRow As Integer, ByVal viMin As Integer, ByVal viMax As Integer, vvStack_Value As Variant, vvStack_RowNo As Variant, vvCtgColNo As Variant, vvCtgColor As Variant) As Integer
Dim iRow As Integer
Dim i As Integer
Dim j As Integer
Dim oRange As Variant

iRow = viRow

'カテゴリがあれば出力
For i = viMax To viMin Step -1
   'カテゴリ行の背景
   voSheet.Rows(iRow).Interior.Color = RGB(238, 238, 238) '薄いグレー

   'カテゴリ列の文字色
   For j = 0 To UBound(vvCtgColNo)
      voSheet.Cells(iRow, vvCtgColNo(j)).Font.Color = vvCtgColor(j)
   Next

'グループ化
   Set oRange = voSheet.Rows(CStr(vvStack_RowNo(i-1)) & ":" & CStr(iRow-1))
   oRange.Group

   '保存していたカテゴリ行を出力
   iRow = xPrintLine(voSheet, iRow, vvStack_Value(i-1))
Next

xPrintLine_Category = iRow
End Function

まとめ

ビューをビューっぽく Excel に出力するプログラムはこれ完成です。合計を表示しないようにビューを変更し出力すると、Excel から合計が消えます。