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 から合計が消えます。

検索用のビューのようにカテゴリのないビューを出力するとカテゴリ（グループ化）も合計も表示されません。

『ビューを完全再現！』とまではいきませんが、ビューを指定するだけでそこそこ同じ見た目で出力できるので、利用価値はあるのではないでしょうか？

前回

Notes - Excel 連携

Notes - Excel 連携：#59）ビューをそのまま Excel へ ④ - 出力順序調整と行タイプの判定

ビューをビューっぽく Excel に出力する方法の 4 回目です。今回の挑戦では、ビューのカテゴリを Excel のグループを使って表現します。グループを使うには、合計行を明細の下に配置する必要があります。ビューと位置が逆転するので、出力順序の調整行います。

出力順序の調整の仕様

まずは仕様を整理するため、手順を図式化します。

ビューから 1 行ずつ順に読み込み、順次処理します。読み込んだ行のタイプに応じて処理が分かれます。

行タイプ	処理
カテゴリ	明細行の出力後に出力します。 複数のカテゴリを出力する場合には、下位のカテゴリから順に出力します。
明細行（文書）	読み込んだ順にそのまま出力します。
合計行	ビューの最後の行。 出力していないカテゴリがあれば出力してから、合計行を出力します。

行タイプの判定

ビューから読み込んだデータの行のタイプは、NotesViewEntry のプロパティで判定できます。IsCategory が True の場合はカテゴリ行、IsTotal が True の場合は合計行となります。それ以外は明細行となります（IsDocument プロパティを使用しても OK です）。

カテゴリレベルの取得

カテゴリ行の処理ではカテゴリレベルを知る必要があります。これは、カテゴリ列の値で判定できそうです。下図は ColumnValues の値をそのまま Excel に表示したときの画面ですが、サブカテゴリでは上位カテゴリの列には値が入らないことを利用します。

カテゴリ行の ColumnValues 値からカテゴリのレベルを取得する関数は次の通りです。

%REM カテゴリ列の階層を返す ◆ 引数 vvLine      　 Variant    カテゴリ列のカラムデータ vvCtgColNo Variant    カテゴリ列番号のリスト（ない場合最初の要素が0） ◆ データ型（戻り値）　 Integer カテゴリレベル（最上位カテゴリ = 1） %END REM Function xGetCategoryLv(vvLine As Variant, vvCtgCol As Variant) As Integer    Dim i As Integer    'カテゴリ列がない場合はキャンセル    If vvCtgCol(0) = 0 Then Exit Function    '上位カテゴリから順にチェック    For i = 0 To UBound(vvCtgCol)       '値が入っている列が階層       If CStr(vvLine(vvCtgCol(i)-1)) <> "" Then          xGetCategoryLv = i + 1          Exit Function       End If    Next End Function

メインルーチンの修正

それでは、今回整理した手順に従い出力順序を調整するように修正します。まずは、前回紹介した xPrintAllData 関数に追加します。強調されている部分が変更点で、薄い色の部分は変更のない箇所です。

%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 iRow As Integer    Dim vLine As Variant    Dim iLv_New As Integer    '読み込んだカテゴリレベル    Dim iLv_Cur As Integer      '処理中のカテゴリレベル    'スタックエリア確保（カテゴリ数と同じ）    ReDim avStack_Value(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, 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, vvCtgColNo, vvCtgColor)          End If          '読み込んだカテゴリをセット          avStack_Value(iLv_New - 1) = vLine     'カテゴリをスタックに記録          iLv_Cur = iLv_New '読み込んだカテゴリレベルを記憶       Else          '明細行（= 文書）          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, vvCtgColNo, vvCtgColor)    End If    xPrintAllData = iRow End Function

出力順の調整で重要な変数は avStack_Value です。定義されているカテゴリ数分の要素を持つの配列となっており、現在処理中のカテゴリ行の全カラムデータを保持します。明細行出力後はここからカテゴリのデータを取得して出力するということですね。なお、全カラムデータ自身が配列であるため、avStack_Value は Variant 型の配列となっています。

カテゴリの情報は上位のカテゴリから順にセットされ、下位から順に出力されます。このような動作から変数名に ”Stack” と付けています。

サンプルのビューではカテゴリ列は 2 つですが、ビューによって階層数はさまざまです。カテゴリがないこともあります。また、合計行がない場合も考えられます。どのようなビューにも対応できるよう、合計行の処理とループを抜けたあとに、出力されていないカテゴリの処理を行っている点もポイントです。

カテゴリ行の出力

最後に、カテゴリを Excel シートに出力する関数の紹介です。

引数の viMin と viMax で出力するカテゴリレベルを受け取ります。その範囲で出力するのですが、下位の階層から出力させるため For ループの Step が -1 となっています。

%REM カテゴリ行を Excel シートに出力 ◆ 引数 voSheet    Variant   Excel シート viRow        Integer   出力行（シートの行番号） viMin          Integer   出力するカテゴリレベル（上位） viMax         Integer   出力するカテゴリレベル（下位） vvStack_Value    Variant    カテゴリ行の値（配列、1要素が1行分の値（配列）を保持） 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, 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       '保存していたカテゴリ行を出力       iRow = xPrintLine(voSheet, iRow, vvStack_Value(i-1))    Next    xPrintLine_Category = iRow End Function

Excel シートへの出力は 前回（#58）作成した xPrintLine 関数で行っているのですが、カテゴリ行の専用の装飾である背景の設定とカテゴリ列の文字色の設定はこの関数内で行っています。#57 で取得したカテゴリ列の位置（vvCtgColNo）と色（vvCtgColor）をやっと使いましたね。

動作確認と次回の予定

ここまでできたら実行して結果を確認します。カテゴリ列がうまく明細の下に、下位から順に表示されていれば、成功です。

次回は最後の仕上げとして、Excel のグループ機能を設定します。

前回

Notes - Excel 連携

次回

Notes - Excel 連携：#58）ビューをそのまま Excel へ ③ - ビューデータの出力

今回からビューの中身（データ）を出力する部分を作成します。 

利用するのは NotesViewNavigator クラスです。このクラスの特徴はビューに表示された文書だけでなく、カテゴリ列やカテゴリ列に表示されている合計値、最終行に表示される総合計にもアクセスできます。

ビューのエントリを出力

NotesViewNavigator クラスでは、GetFirst メソッドで最初の行、GetNext メソッドで次の行を取得できます。NotesView クラスの GetFirstDocument メソッドなどと同じですね。

戻り値は NotesViewEntry オブジェクトで、ColumnValues プロパティから列の値を取得できます。ColumnValues は列の数だけの配列となっており、最初の要素が 1 列目の値となります（要素番号は 0 から）。

これらの機能を利用して、まずはビューのエントリを順に取得して Excel シートに出力してみます。xPrintAllData 関数は、ビューの全データを順に取得して、出力関数に渡しています。

%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 iRow As Integer    Dim vLine As Variant    'ビューの出力開始    iRow = 2      'シートの 2 行目から出力    'ビューの 1 行目を取得    Set nve = nvnav.GetFirst()    'ビューのデータがなくなるまでループ    Do Until (nve Is Nothing)       '現在のビュー行の全カラム値を取得       vLine = nve.ColumnValues       '明細行として出力       iRow = xPrintLine(voSheet, iRow, vLine)       'ビューの次の行を取得       Set nve = nvnav.GetNext(nve)    Loop    xPrintAllData = iRow End Function

1 行分のデータを Excel シートに出力する関数 xPrintLine は次の通りです。

%REM 1 行分の全カラムデータを Excel シートに出力 ◆ 引数 voSheet　Variant    Excel シート viRow         Integer    出力行（シートの行番号） vvLine        Variant    出力データ（配列） ◆ データ型（戻り値）　 Integer 次の出力行番号 %END REM Function xPrintLine(voSheet As Variant, ByVal viRow As Integer, vvLine As Variant) As Integer    Dim i As Integer    '全カラムデータを 1 列目から順に出力    For i = 0 To UBound(vvLine)       voSheet.Cells(viRow, i + 1).Value = vvLine(i)    Next    '戻り値    xPrintLine = viRow + 1 End Function

実行結果

ここまでのプログラムを実行すると次のようになります。ビューの各行、各列の値がそのまま表示されていることがわかります。

前回作成した関数 xSetHeader で列の色をセットしているので、ビューと同じ色でデータが出力されていますね。

次回の予定

今回はビュー並び順のまま素直にデータを出力してみました。このままだとカテゴリの行が見づらいですね。ということで、次回はカテゴリ行の調整を行います。

前回

Notes - Excel 連携

次回

Notes - Excel 連携：#57）ビューをそのまま Excel へ ② - 設計の読み込みと出力シートの準備

ビューをビューっぽく Excel に出力する方法の 2 回目です。

今回作成するサンプルはビューの設計を読みながら出力する仕様としますので、ビューの設計（状態）に依存しません。ただ、具体的なビューがないと説明しにくいので、以下のビューをサンプルとして進めます。

このビューは、この連載で帳票の出力について紹介した時に利用したものです（#20 ～）。複合機の使用実績をイメージしたビューで、年月別に白黒/カラーの出力枚数を表示しています。今回のサンプル用にない数の合計を表示するように列の設定を変更しています。

今回はビューの設計を読み込んでヘッダ行の設定と列幅や文字色を設定する部分までを作成します。

メインルーチンの構造

新規でエージェントを作成してコーディングを開始します。

今回は指定したビューの設計を参照して、それを Excel で再現することが目標です。そこで、メインルーチン xViewToExcel の引数は、DB とビュー名称だけにして、再利用しやすい構造としています。

Option Declare Private xns As NotesSession Sub Initialize    Dim ndb As NotesDatabase    Set xns = New NotesSession    Set ndb = xns.CurrentDatabase    'ビューを Excel シートに出力して画面に表示    Call xViewToExcel(ndb, "vXlsUsage_Sum")     '複合機使用実績ビュー（合計付き） End Sub

では、メインルーチン xViewToExcel の作成を始めます。

Excel シートを作成してヘッダ行を固定した後、ビュー設計の読み込みとヘッダーの出力する関数 xSetHeader をコールしています（詳細は後述）。

%REM ビュー設計の読み込みとヘッダーの出力 ◆ 引数 vndb　               NotesDatabase     出力するビューがあるデータベース vsViewName    String                        出力するビュー名称 ◆ データ型（戻り値）　 Boolean %END REM Private Function xViewToExcel(vndb As NotesDatabase, ByVal vsViewName As String) As Boolean    'Excel の準備    Dim oXls As Variant    Dim oSheet As Variant    On Error GoTo Err_General    Set oXls = CreateObject("Excel.Application")    Call oXls.Workbooks.Add    Set oSheet = oXls.Workbooks(1).WorkSheets(1)    'ヘッダ行固定    oXls.ActiveWindow.SplitRow = 1    oXls.ActiveWindow.FreezePanes = True    '出力ビュー取得    Dim nv As NotesView    Set nv = vndb.GetView(vsViewName)    nv.AutoUpdate = False    'ビュー設計の読み込みとヘッダーの出力    Dim iCategory As Integer             'カテゴリの数    Dim vCtgColNoList As Variant   'カテゴリ列番号のリスト    Dim vCtgColorList As Variant     'カテゴリ列の文字色のリスト    iCategory = xSetHeader(oSheet, nv, vCtgColNoList, vCtgColorList) Exit_Func:           '終了処理    If Not(oXls Is Nothing) Then       'Excel を画面に表示       oXls.Visible = True    End If    xViewToExcel = True    Exit Function Err_General:      'エラー処理    MsgBox Error$, 16, vndb.Title    Resume Exit_Func End Function

ビュー設計取得

NotesView クラスには ColumnCount プロパティがあり、設計で定義されている列数が取得できます。また、Columns プロパティから列の情報である NotesViewColumn オブジェクトが取得できます。Columns は配列になっており、Columns(0) で 1 列目を取得します（要素番号は 0 から）。

NotesViewColumn オブジェクトのプロパティとして、カテゴリ列であるか、列のタイトルや幅など設計の設定にアクセスできます。

サンプルプログラム

今回のサンプルでは、ビューのカテゴリを Excel のグループ機能で表すことからカテゴリの情報は重要で、ビューの中身（データ）を出力する際にも利用します。そこで、今回作成する関数 xSetHeader では、カテゴリの数、その列番号と文字色をリスト（カテゴリ数分の要素数の配列）で返します。

例えば今回のビューの場合、カテゴリ列は 2 つです。要素数が 2 つの配列で、列番号は 2 と 4、文字色はどちらも赤を返す動作となります。

%REM ビュー設計の読み込みとヘッダーの出力 ◆ 引数 voSheet         Variant         Excel シート vnvExport     NotesView  出力するビュー rvCtgColNo  Variant         戻り値。カテゴリ列番号のリスト（ない場合最初の要素が0） rvCtgColor    Variant         戻り値。カテゴリ列の文字色のリスト（要素数はrvCtgColNoと同じ） ◆ データ型（戻り値）　 Integer カテゴリ列の数を返します。 %END REM Function xSetHeader(voSheet As Variant, vnvExport As NotesView, rvCtgColNo As Variant, rvCtgColor As Variant) As Integer    Dim iCtg As Integer    Dim aiCtgColNo() As Integer    Dim alCtgColor() As Long    Dim iCol As Integer    Dim i As Integer    Dim nvc As NotesViewColumn    iCtg = 0    ReDim aiCtgColNo(iCtg)    ReDim alCtgColor(iCtg)    iCol = vnvExport.ColumnCount    For i = 1 To iCol       Set nvc = vnvExport.Columns(i-1)       voSheet.Cells(1, i).Value = nvc.Title       'カテゴリ列か？       If nvc.IsCategory = True Then          '列番号記録          ReDim Preserve aiCtgColNo(iCtg)          aiCtgColNo(iCtg) = i          '文字色を取得して記録          ReDim Preserve alCtgColor(iCtg)          alCtgColor(iCtg) = xColorNotesToExcel(nvc.FontColor)          iCtg = iCtg + 1       End If       '文字色のセット（列全体の色をセット）       If nvc.IsCategory = True Then          'カテゴリはいったんグレー          voSheet.Columns(i).Font.Color = RGB(238, 238, 238)      '薄いグレー       Else          voSheet.Columns(i).Font.Color = xColorNotesToExcel(nvc.FontColor)       End If       '列幅の調整       voSheet.Columns(i).ColumnWidth = nvc.Width * 1.5      'そのままだとちょっと狭い       '非表示列       If nvc.IsHidden Then          voSheet.Columns(i).Hidden = True       End If    Next    'ヘッダ行の背景色設定    voSheet.Rows(1).Interior.Color = RGB(238, 238, 238)      '薄いグレー    '戻り値セット    rvCtgColNo = aiCtgColNo    rvCtgColor = alCtgColor    xSetHeader = iCtg End Function

この関数ではビュー上の各列と同じ色を Excel シートの列にそのままセットしています。ただし、ノーツと Excel では色の指定方法が異なりますので、xColorNotesToExcel 関数でノーツの色から Excel の色へ変換しています。

なお、カテゴリ列の色は戻り値で返していますが、シートの列にはグレーをいったんセットしています。これに関してはカテゴリを出力する際に詳細を説明します。

色の変換

上記関数で登場したノーツの色から Excel の色へ変換する関数は以下の通りです。NotesColorObject のプロパティから RGB の値を取得して、Excel の色番号に変換しています。

%REM ノーツの色から Excel の色へ変換 ◆ 引数 viNotesColor　 Integer 変換するノーツ色 ◆ データ型（戻り値）　 Long Excel の色番号 %END REM Function xColorNotesToExcel(ByVal viNotesColor As Integer) As Long    Dim ns As New NotesSession    Dim nc As NotesColorObject    Set nc = ns.CreateColorObject()    nc.NotesColor = viNotesColor    xColorNotesToExcel = RGB(nc.Red, nc.Green, nc.Blue) End Function

RGB 値から Excel の色番号を取得する関数は過去にこの連載内で作成済みです。連載で順次作成してきたスクリプトライブラリ lsXls を組み込むか、以下の関数をエージェントに追加してください。

%REM RGB メソッド　( lsXls ライブラリ ) --------------------------------------------------- R, G, B の各要素から Excel で使用する色番号を返します。 　 ◆ 引数 vbyR　 Byte 赤 vbyG　 Byte 緑 vbyB　 Byte 青 　　 ◆ 戻り値   Long %END REM Public Function RGB(ByVal vbyR As Byte, ByVal vbyG As Byte, ByVal vbyB As Byte) As Long    RGB = vbyR + CLng(vbyG) * 256 + CLng(vbyB) * 256 ^ 2 End Function

前回

Notes - Excel 連携

次回

Notes - Excel 連携：#56）ビューをそのまま Excel へ ①

ノーツのビューを Excel に書き出したいという要望は、ユーザ部門だけでなく、システム部門でもたびたび発生します。ノーツクライアントには、書き出し機能や表形式でコピーなどさまざまな方法がありますが、ビューを見た目通りに書き出せるわけではありません。

そこで今回は、ビューをビューっぽく Excel に出力する方法に挑戦します。

基本方針

Excel エクスポートの機能を作成する場合、出力する列の数や出力するフィールドや計算式などを定義して、それに従い Excel シートに書き出すパターンが多いと思います。

今回は、新しい試みとして、ビューの設計に従って出力する方法に挑戦します。例えば、下図のように、列の並びと値をビューから取得する感じです。文字の色や列幅も極力再現します。

カテゴリは、Excel のグループ機能を活用し、開閉することとします。グループ機能は小計を表示するような機能であるため、明細データの下側に合計が配置されます。ノーツのカテゴリと大きく違う点ですね。

ワークシートの左側に閉じたり開いたりする機能がありますが、行としてカテゴリ（合計行）であることわかりやすくするため、背景をグレーにします。ビューとは印象が違いますが、それなりにわかりやすくできます。

次回の予告

設計要素から定義を読み込むことになるので DXL の出番かと考えていましたが、標準のノーツクラスでも設計要素の情報が取得できました。今回は、標準クラスで取得できる範囲で、可能な限り、ビューをそのまま出力してみます。うまく実現できればビューを指定するだけで、フォーマットとデータを ”まるっと” 出力できるようになります。

次回から実現する機能と利用するメソッドを解説しながら開発を進めます。

前回

Notes - Excel 連携

次回

CSV ファイルの読み込み ④ - ファイルの読み込み 文字コードの設定

今回の記事にあたって郵便局の郵便番号ダウンロードサイトを見ていると新しいデータ形式のダウンロードが始まったことを確認しました（新しいといっても 2023 年のことで、すでに 2 年以上前ですが...）。

住所の郵便番号（1レコード1行、UTF-8形式）（CSV形式）

従前のデータでは、条件によっては 1 郵便番号が 2 レコードになるパターンがあったようなのですが、このデータでは発生しないようです。これが当たり前だと思うのですが、このデータであれば安心して利用できますね。また、カタカナが半角から全角文字になっているのもありがたいと感じます。

ただ、このデータはこれまでと違い UTF-8 形式となっています。ということで、今回は文字コードを意識したファイルの読み込みを行います。

文字コードの指定

『CSV ファイルの読み込み ① - ファイルの準備』で Open ステートメントを紹介しました。この時はあえて省略したのですが、オプションで文字コードセットを指定する機能があります。例えば UTF-8 を指定する場合は次のようになります。

Open "c:\tmp\utf_ken_all.csv" For Input As #iFP Charset = "UTF-8"

この部分だけは VBA の名前付き引数のような記述で、LotusScript ではあまり見ない感じですね。VBA の名前付き引数の指定は := ですが、ここでは = だけなので注意してください。

= の後ろが指定できる文字コードセットで UTF-8 以外にも多数の指定ができます。詳しくはデザイナーヘルプの『MIME 文字セット名』を参照してください（Open ステートメントのヘルプからリンクされています）。

読み込みの仕様

まず、読み込んだデータを保存するフォームを作成します。

今回作成するサンプルで CSV データから取得する項目は赤字の部分となります。

01101,"064 ","0640941","ホッカイドウ","サッポロシチュウオウク","アサヒガオカ","北海道","札幌市中央区","旭ケ丘",0,0,1,0,0,0

最初のカラムは全国地方公共団体コードです。前 2 桁は都道府県コード（JIS X0401）、後ろ 3 桁は市区町村コード（JIS X0402）です。何かで利用できそうなので、別フィールドにそれぞれ保存することとします。

サンプルプログラム

UTF-8 形式の郵便番号 CSV ファイルを読み込むサンプルプログラムは次の通りです。ファイルオープン時に UTF-8 を指定しています。

前回と同様に読み込んだデータは xCSVtoList 関数で配列化しています。この配列にあわせて、セットするフィールド名を配列 asFldLst にあらかじめ用意しておきます。これらを利用して、文書に値をセットをループで実現し、シンプルなメインルーチンにできました。前回 のまとめで ”構造化との相性が高い” としたのはこういった記述ができるからですね。

Option Declare Sub Initialize    Dim ns As New NotesSession    Dim ndb As NotesDatabase    Dim nd As NotesDocument    Dim iFP As Integer    Dim vLine As Variant    Dim sLine As String    Dim i As Integer    Dim l As Long    Dim asFldLst() As String     '保存フィールド名    '保存フィールド名初期化    ReDim asFldLst(8)    asFldLst(0) = "JisCode"    asFldLst(2) = "ZipCode"    asFldLst(3) = "Address1_Kn"    asFldLst(4) = "Address2_Kn"    asFldLst(5) = "Address3_Kn"    asFldLst(6) = "Address1_Kj"    asFldLst(7) = "Address2_Kj"    asFldLst(8) = "Address3_Kj"    '初期化    Set ndb = ns.CurrentDatabase    'ファイルオープン    iFP = FreeFile()    Open "c:\tmp\utf_ken_all.csv" For Input As #iFP Charset = "UTF-8"    On Error GoTo Err_Loop        ’エラー処理有効化    '1 行目取得    Line Input #iFP, sLine    Do Until EOF(iFP)       l = l + 1       'CSV の 1 行を項目ごとの配列に分離       vLine = xCSVtoList(sLine)       '読み込んだデータを文書に保存       Set nd = ndb.CreateDocument()       nd.Form = "fZip"       For i = 0 To UBound(asFldLst)          If asFldLst(i) <> "" Then             Call nd.Replaceitemvalue(asFldLst(i), vLine(i))          End If       Next       '全国地方公共団体コード の処理       nd.JisCode_Pref = Left(vLine(0), 2)      '都道府県コード（JIS X0401）       nd.JisCode_City = Right(vLine(0), 3)   '市区町村コード（JIS X0402）       Call nd.Save(True, True) Loop_Next:       '次の行取得       Line Input #iFP, sLine    Loop    Exit Sub Err_Loop:    Print CStr(l) & " 行目の処理でエラーが発生しました。"    Resume Loop_Next End Sub

なお、関数 xCSVtoList は前回掲載したまま変更がないので割愛しています。

CSV ファイルの読み込み ③ - ファイルの読み込み Line Input #

前回は Input # による CSV ファイルの読み込みについて紹介しました。今回はもう一つの読み込み命令である Line Input # を紹介します。

Line Input #

この命令はテキストファイルから 1 行分のデータを読み込む命令となります。Input # とは違い行単位で処理できるので、CSV だけでなく可変長のテキストファイルなど、より自由なフォーマットのファイル操作にも対応できます。

Line Input #fileNumber , varName

fileNumber	データを読み込むファイル番号
varName	読み込んだ 1 行分のデータをセットする変数

サンプルプログラム

では、Line Input # を使用した CSV ファイル読み込みエージェントを紹介します。

前回の Input # のサンプルと違うのは、CSV データ 1 行分を変数 sLine に読み込み、関数 xCSVtoList で項目ごとに分離している点です。

Option Declare Sub Initialize    Dim ns As New NotesSession    Dim ndb As NotesDatabase    Dim nd As NotesDocument    Dim iFP As Integer    Dim vLine As Variant    Dim sLine As String    '初期化    Set ndb = ns.CurrentDatabase    'ファイルオープン    iFP = FreeFile()    Open "c:\tmp\KEN_ALL_ROME.CSV" For Input As #iFP    '1 行目取得    Line Input #iFP, sLine    Do Until EOF(iFP)       'CSV の 1 行を項目ごとの配列に分離       vLine = xCSVtoList(sLine)       '読み込んだデータを文書に保存       Set nd = ndb.CreateDocument()       nd.Form = "fZip_Rome"       nd.ZipCode = vLine(0)       nd.Address1_Kj = vLine(1)       nd.Address2_Kj = vLine(2)       nd.Address3_Kj = vLine(3)       nd.Address1_En = vLine(4)       nd.Address2_En = vLine(5)       nd.Address3_En = vLine(6)       Call nd.Save(True, True)       '次の行取得       Line Input #iFP, sLine    Loop    Close End Sub Private Function xCSVtoList(ByVal vsLine As String) As Variant    Dim v As Variant    Dim i As Integer    Dim s As String    'カンマで分離（文字列内のカンマは考慮しない）    v = Split(vsLine, ",")    For i = LBound(v) To UBound(v)       '各要素の前後のダブルクォーテーションを削除       s = v(i)       If Left(s, 1) = |"| Then          s = Right(s, Len(s)-1)       End If       If Right(s, 1) = |"| Then          s = Left(s, Len(s)-1)       End If       v(i) = s    Next    xCSVtoList = v End Function

今回の CSV ファイルでは文字列内にダブルクォーテーションやカンマが入る前提としていないので、xCSVtoList 内の処理は非常に単純な仕様としています。

まとめ

今回は Line Input # を使用した CSV 取り込みを紹介しました。Line Input # と前回の Input # と基本的な機能で比較してみます。

	Line Input #	Input#
読み取り単位	カンマや改行で区切られた項目単位	1 行単位
クォート(")の扱い	そのまま文字として読み取る	文字列では自動削除
型変換	常に文字列（自身で変換）	変数に応じて自動変換
CSV 向き？	Split などで手動で分離	簡易的な CSV なら自動対応

一見 Input # の方が優勢に見えますね。ただ、プログラミングに力点を置いて比較すると立場が逆転します。

	Line Input #	Input#
応用性	高い	低い
	CSV 以外にも対応 クォート、可変列なども自力で自由に処理	CSV 以外はほぼ無理
構造化との 相性	高い	微妙
	配列処理やクラス化しやすい 型変換や検証が柔軟	直接変数に読み込むため構造化に不向き
データ検証	容易	困難
	生データを扱えるので、欠損や不正などチェックしやすい	エラーが発生せず、初期値となることがある
例外処理の 自由度	高い	低い
	自力で自由にチェックできる	想定外のデータでずれ、エラーが出ない
保守性	高い	低い
	読み込みルールを変更しやすい	変数定義と一致させるため、全体修正が必要となることも

与えられるデータに含まれる文字や精度、将来想定される修正などを考慮の上、状況に応じた使い分けが必要な感じです。まるで、ローコードとプロコードの比較のようですね。

CSV ファイルの読み込み ② - ファイルの読み込み Input #

前回に続いて CSV の読み込み方法を紹介します。

まず、利用する CSV のサンプルですが、郵便局の Web サイトからダウンロードできる 郵便番号（ローマ字） を利用します。フォーマットは 1 郵便番号当たり 7 項目の CSV ファイルとなっています。

"0600000","北海道","札幌市　中央区","以下に掲載がない場合","HOKKAIDO","SAPPORO SHI CHUO KU","IKANIKEISAIGANAIBAAI" "0640941","北海道","札幌市　中央区","旭ケ丘","HOKKAIDO","SAPPORO SHI CHUO KU","ASAHIGAOKA"      ・・・

CSV ファイルなどのテキストファイルの読み込みには Input # と Line Input # の 2 つの方法があるのですが、今回は Input # を紹介します。

Input #

このステートメントの構文は次の通りです。

Input #fileNumber , variableList

fileNumber	データを読み込むファイル番号
variableList	読み込んだデータをセットする変数リスト（カンマ区切り）

変数リストの設定は LotusScript のプログラミングであまり登場しないパターンで、データを受け取る変数名を必要な数だけカンマ区切りで列挙します。例えば、事例の CSV の場合では、以下のように記述します。青字部分が変数リストとなります。

Dim iFP As Integer    Dim sZip As String    Dim sKj1 As String    Dim sKj2 As String    Dim sKj3 As String    Dim sEn1 As String    Dim sEn2 As String    Dim sEn3 As String    'ファイルオープン（読み込みモード）    iFP = FreeFile()    Open "c:\tmp\KEN_ALL_ROME.CSV" For Input As #iFP    Input #iFP, sZip, sKj1, sKj2, sKj3, sEn1, sEn2, sEn3          ・・・

実行すると下図のように CSV 内の各カラムの値がそれぞれの変数に代入されます。

カンマをデータの区切りとして分離、文字列の始めと終わりのダブルクォーテーションを削除してくれることが特徴です。

この挙動からわかる通り、Input # は CSV ファイルの読み込みを意識した仕様になっていることがわかります。

サンプルプログラム

では、具体的に事例の CSV ファイルをフォームに読み込むエージェントを紹介します。

Option Declare Sub Initialize    Dim ns As New NotesSession    Dim ndb As NotesDatabase    Dim nd As NotesDocument    Dim iFP As Integer    Dim sZip As String    Dim sKj1 As String    Dim sKj2 As String    Dim sKj3 As String    Dim sEn1 As String    Dim sEn2 As String    Dim sEn3 As String    '初期化    Set ndb = ns.CurrentDatabase    'ファイルオープン    iFP = FreeFile()    Open "c:\tmp\KEN_ALL_ROME.CSV" For Input As #iFP    '1件目取得    Input #iFP, sZip, sKj1, sKj2, sKj3, sEn1, sEn2, sEn3    Do Until EOF(iFP)       '読み込んだデータを文書に保存       Set nd = ndb.CreateDocument()       nd.Form = "fZip_Rome"       nd.ZipCode = sZip       nd.Address1_Kj = sKj1       nd.Address2_Kj = sKj2       nd.Address3_Kj = sKj3       nd.Address1_En = sEn1       nd.Address2_En = sEn2       nd.Address3_En = sEn3       Call nd.Save(True, True)       '次のデータを取得       Input #iFP, sZip, sKj1, sKj2, sKj3, sEn1, sEn2, sEn3    Loop    Close End Sub

Do ～ Loop の判定で使用している EOF(iFP) はそのファイルが最後（End Of File）であるかを判定する命令です。ループの最後の Input # で次のデータを読み込んでループを繰り返しています。これでファイルを順に最後まで読み込むこととなります。

Input # の注意点

上記の通り Input # は CSV 読み込みの処理ではとても便利に利用できます。ただ、おいしい話には裏があるもので、注意点がいくつかあります。

◇  数値変換できない場合は 0 となる

例にはありませんが、数値変数を指定すると数値として読み込んでくれる機能があります。うまく使えば、データの型操作を最小限にすることができるのですが、利用上の注意があります。それは、読み込んだデータが数値に変換できない場合は、0 となることです。

例えば、郵便番号 sZip を数値（Integer 型）に変更して実行します。

Dim sZip As Integer

変数が数値型であるため、ファイルから変数に割り当てられるデータは、ダブルクォーテーションが付いた "0640941" のような値となります。ダブルクォーテーションは数値に変換できないので変数の値は 0 となり、結果作成される文書の値も 0 となってしまいます。

◇ 特殊文字に注意

CSV ファイルのフォーマットでは、ダブルクォーテーションで括られた範囲内が文字列となります。ただ、文字列内にダブルクォーテーションを含める場合には、"" のように二重に書くことになっています（ダブルクォーテーションエスケープ）。ところが、Input # では対応していないようです。

少々意地悪なテストデータを与えてみました。Input # 経由で読み取れたデータに色を付けると次のようになりました。『以下に"掲載"がない場合』と読み取ってほしいところが、3 データに分離されてしまいました。どうやら、文字列内のダブルクォーテーションは区切り文字として判定するようです。

"0600000","北海道","札幌市　中央区","以下に""掲載""がない場合","HOKKAIDO","SAPPORO SHI CHUO KU","IKANI,KEISAI,GANAIBAAI"

ちなみに、最後のカラムにあるように、文字列内の ,（カンマ）は区切り文字ではなく、データとして正しく取り扱いました。文字列内のカンマは区切り文字と判定しないようです。

特殊文字を含む CSV の場合は対応が完全ではないので、注意がいるようですね。

◇ レコードという考え方がない

Input # は区切り文字に従い順にデータを読み込むことに特化しています。例えば以下のように読み込み変数を一つだけ指定して実行してみます。

Input #iFP, sZip

すると郵便番号を読み込んだ後、次は同じ１行目の都道府県名が読み込まれました。行単位で読み込みを行う場合には、行内に存在するすべてのカラム数分の変数を記述する必要がある点に注意が必要です。

もし、一部のカラムしか処理に必要ない場合には、ダミーの変数をセットすることができます。例えば最初のカラム以外不要なのであれば、次のように記述できます。

Input #iFP, sZip, sTmp, sTmp, sTmp, sTmp, sTmp, sTmp

無用な変数宣言を減らせるので、少しだけコードが短く、見やすくできますね。

まとめ

今回は CSV 取り込みに特化した Input # を紹介しました。便利な機能ではあるのですが、注意点がいくつか存在しました。

特に、カラムがずれる現象が発生には要注意です。ずれが発生すると後続のデータでは、数値カラムに文字が入ったりします。通常 LotusScript では型変換エラーが発生して気付けるのですが、上記の通り Input # ではエラーが発生しません。レコード単位で処理する機能がないことと相まって、いったんずれが発生すると、元に戻ることがありません。

絶望的な結果となったにも関わらず、何となく動いてしまい、正常終了扱いとなっていることがあります...。

CSV ファイルの読み込み ① - ファイルの準備

LotusScript でファイルを操作する方法には NotesStream クラスを利用する方法と下記のステートメントを使用する方法の 2 種類があります。ざっくりいうと前者はバイナリファイル、後者はテキストファイルの操作に向いています。

Open	読み書きできるようファイルを開く
Close	開いているファイルを閉じる
FreeFile	使用していないファイル番号を返す
Input # Line Input #	ファイルからデータを読み込む
Print # Write #	ファイルにデータを書き込む

今回は、これらステートメントを利用した CSV ファイルの読み込みについて紹介します。

Open

ファイル操作を開始するには、まず Open 命令でファイルを開く必要があります。構文は下記の通りで、開くファイルと開き方を指定します。

Open fileName For mode As #fileNumber

filename	開くファイルを文字列で指定 フルパスで指定しない場合、カレントディレクトリを基準に開く
mode	ファイルの開き方を指定（下表参照）
fileNumber	1 ～ 255 までの整数 他のファイル操作コマンドは、この番号でファイルを参照

指定できるモードの種類は以下の通りです。CSV ファイル操作では Input / Output / Append を使用します。

Input	シーケンシャル入力モードで開く テキストファイルの読み込みで使用
Output	シーケンシャル出力モードで開く テキストファイルの書き込みで使用
Append	Outputと同じく書き込み用 既存のファイルに追記する
Random	ランダムアクセスモードで開く ファイルはレコード番号でアクセスし、Get # / Put # ステートメントで読み書きする
Binary	バイナリファイルモードで開く Get # / Put # ステートメントで読み書きする

FreeFile

Open ではファイル番号（fileNumber）をしていました。このファイル番号を指定してファイル操作を行うことになるのですが、その番号を取得するのが FreeFile ステートメントです。

fileNumber　= FreeFile()

戻り値は、使用していないファイル番号です。この命令を使えば、何番を使えるのか意識する必要がなく、使用できる番号を簡単に取得できます。

Close

開いているファイルを閉じます。引数で閉じるファイル番号を指定できますが、省略するとすべてのファイルを閉じます。

Close fileNumber

LotusScript スクリプトが終了すると開いているファイルは自動的に閉じるようですが、プログラムの意図を明確にするためにも処理が終わったら閉じるようにしましょう。

サンプル

CSV ファイル（テキストファイル）を読み込みモードで開く処理は次のように記述します。

Sub Initialize    Dim iFP As Integer    'ファイルオープン（読み込みモード）    iFP = FreeFile()    Open "c:\tmp\KEN_ALL_ROME.CSV" For Input As #iFP              （ファイル読み込み処理）        'ファイルのクローズ    Close End Sub

FreeFile で使用するファイル番号を取得して、Open で使用しています。変数名 iFP の前に # が付いていますが、これはファイル番号を表すキーワードだと覚えてください。LotusScript 登場のはるか前にあった BASIC 言語の名残となります。

次回の予定

これで CSV ファイルの読み込み準備ができました。次回はファイルからデータを読み込む処理について紹介します。