Excel VBAのコーディング事例をご紹介しています。
事例のテーマを決めて、その機能を実現するためのコーディングを動く形でお示して行きます。
今回は「XSDがどのような構造であればExcelでエクスポートできるのか?－その2」の中でご紹介できなかったに実際のコーディングとして「キー項目の挿入」と「キー項目での合体」の2つの2プログラムをご紹介いたします。
なおコーディング自体は「構造化・標準化は考慮しつつも極力コーディング量は減らし、その上で可読性を維持する」という思いは持ちつつも「テーマに沿って動くことを優先して実装」しています。
Excel バージョン2108(ビルド 14326.20348)を使用しています。
今回のテーマの背景
いきなりにはなりますが、『ExcelでXMLファイルがエクスポートできない最大の要因(一番のボトルネック)は、スキーマファイルが「リストのリスト」という構造を含んでいるケースである』と考えています。
「リストのリスト」とは「maxOccursが1以外に設定されている要素の子要素の中に、 maxOccursが1以外に設定されている子要素がある」事を意味しています。詳細は「XSDがどのような構造であればExcelでエクスポートできるのか?－その1」をご参照いただければ幸いです。
結局このような良くある構造がサポートされていないために「Excelでは複雑なXMLファイルはエクスポートできない」と低く評価されている事と存じます。
そうは言ってもできないものはできないとして、何とかunboundedの「リストのリスト」に対応できないか?と考えたのが、スキーマファイルをmaxOccurs属性を持つ要素で2つに分割するやり方です。
「リストのリスト」として1つにまとまっているものを、『unboundedの「リスト」とunboundedの「リスト」』の2つに分ければ、Excelでそれぞれをエクスポートする事ができるはずです。
つまり元となる1つのXMLファイルを、それぞれのスキーマファイルでExcelに読み込んで何かしらのデータ修正作業をした後、2つのXMLファイルを書き出して、その書き出された2つのXMLファイルを１つにまとめて元のXMLの形に戻すという面倒臭いやり方です。
このためには、2つのXMLファイルのなかに両者を結び付けるためのキーとなる項目が必要です。
ただし普通は元となるXMLファイルの中にそのようなキーとなる項目は無いはずで、そうなると次のようなプログラムを2つ作るしか方法がありません。
- 元となるXMLファイルを読み込み、(分割される事になる)「maxOccurs属性を持つ要素」の中にキー項目を挿入する。
- 2つのXMLファイルを読み込み、キー項目に基づき合体させて1つのXMLファイルにする。
元となるXMLファイル
実際のXMLファイルは「XSDがどのような構造であればExcelでエクスポートできるのか?－その2」に掲載していますので、お手数ですがご参照いただければ幸いです。
ここではXMLファイルの項目内容についてご説明いたします。
※赤太字はmaxOccurs属性がunboundedの要素
()は設定値／注釈
[]は属性
…は省略を表す。
※緑色はキー項目を挿入する場所を示す。
<purchaseOrders …> <purchaseOrder orderDate="…" confirmDate="…"> <shipTo country="…"> <name>…</name> <street>…</street> <city>…</city> <state>…</state> <zip>…</zip> </shipTo> <billTo> … </billTo> <comment>…</comment> --------------------------------------------- <items> <item partNum="…"> <productName>…</productName> <quantity>…</quantity> <USPrice>…</USPrice> <comment>…</comment> <shipDate>…</shipDate> ---------------------------------------- </item> </items> </purchaseOrder> </purchaseOrders>
購買注文群(ルート要素) 購買注文[注文日][確定日(必須)] 送り先[国] 送り先名 番地 都市 州 郵便番号 請求先 子要素はshipTpと同じため省略 コメント(省略可) ←キー項目挿入場所-1 品目群 品目(省略可)[部品番号] 商品名 数量 ドル価格 コメント 出荷日(省略可) ←キー項目挿入場所-2
1.XMLファイルの目印タグの前にキー項目「orderNum」を挿入する
前章でご説明した「キー項目の挿入場所-1と-挿入場所-2」に”<orderNum>”というタグを挿入するプログラムを作ります。
処理要件としては次のようになります。
- XMLファイルは一括読み込みをする。この時ファイルの文字コードが”UTF-8″でも対応できるようにする。
- XMLファイルの”encoding”は”UTF-8″に設定されている場合が多いために”UTF-8″が扱える必要があります。
- 一括読み込みしたデータを目印タグを決めてsplit関数で分割し配列にセットする。
- 挿入場所は２ヶ所あり、それぞれの目印タグが必要になります。配列も２個必要です。
- 目印タグはsplit関数が消去するため、出力時にデータを補ってあげる必要があります。
- 挿入するキー項目の値は、挿入場所-1と-挿入場所-2ともに同じ値になります。
- 挿入場所②のitem要素は「省略可能」に設定されているのでデータが無い場合があるので注意が必要です。
- split関数で分割した時にできる、最初と最後のパートは出力時にデータを漏らさないようにします。
目印タグを考える
目印タグは2つ必要になりますが、まずは1つ目から見て行きます。
一つ目の目印タグ
今回はご説明していませんが、2つに分割したスキーマファイルではキー項目をpurchasOrder要素の子要素の中で一番最後に挿入しています。(左図の挿入場所-1にあたる場所です。)
そこで挿入場所-1の目印タグは”<items>”とします。
この理由はitems要素の1つ前のcomment要素が省略可能になっていためです。
なおitems要素の子要素のitemも省略可能な設定になっていますが、items要素自身は省略され無いので目印タグにしても大丈夫です。
ただしitem要素が省略されて<items>タグが空タグになる場合に、<items>タグの書き方は2パターンあるので注意が必要です。
上図でご説明すると「パターンA」はitem要素がある場合であるのに対して、「パターンB」はitem要素が省略された空タグの場合です。
- パターンA
- <items>…</items>
- パターンB
- <items/>
Excelでエクスポートすると空タグはパターンBで出力されます。
このような場合、目印タグが完全なタグではなく一部分が欠けていたとしても問題なく目印タグとして機能します。
という事で今回は”<items”という不完全なタグを使います。
2つ目の目印タグ
もう一つの目印タグは上図「挿入場所その2」の位置で、目印タグとしては”</item>”を使用します。
今回はご説明していませんがスキーマファイルでorderNum要素を追加した場所は、item要素の子要素のshipDate要素の後になります。
<shipDate>タグを目印タグに使用していないのはshipDate要素が省略可能に設定されているためです。
目印タグでsplitした時、配列に格納されるデータを把握する
次に目印タグでデータをsplitした場合、どのような形で配列に格納されるのかを調べます。
- 紫色の枠線で囲った箇所が①～③までの３ヶ所あります。
これは目印タグ”<items”で切り刻んだ時にできるパートで、①が最初、③が最後のパートになり、②はitems要素の数分だけ存在します。
なおitems要素が「空タグか?、空タグでないか?」はコーディング上は意識しなくても大丈夫です。
- 赤色の枠線で囲った箇所は①と②の2ヶ所あります。
対象範囲としては紫色の枠線②と重なります。
最初のパートと繰り返しのパートは形が同じになり、赤色の枠線②が最後のパートになります。
赤色の枠線①のバートはitem要素の数だけ存在します。
実際のコーディング例
では次に実際のコーディング例をご説明いたします。
- 8行目
- sample-02x.xmlファイルを格納している実際のフォルダーに変更します。
- 9-10行目
- 2つの目印タグとキー項目名をコンスタント変数にセットしています。
- 11-17行目
- ADOのStreamオブジェクトでCharsetプロパティに”UTF-8″を指定し、LoadFromFileメソッドで対象ファイルを設定し、ReadTextメソッドで一括読み込みします。
- 20行目
- 最初のパートであることを判断しています。
- 21行目
- 最初のパートの時は前章の紫色①の部分を書き出してからキー項目を挿入します。
- 22行目
- split関数で消去された目印タグを元に戻します。String関数で半角スペースを2個補っていますが、これは読み込まれたXMLファイルに設定されているインデントです。というのは元々設定されていたインデントは挿入したキー項目で使われてしまうため追加して補います。
- 24行目
- 最初のパート以外では、前章赤色②の部分を書き出してからキー項目を挿入します。
- 29行目
- 前章赤色①の部分を書き出してからキー項目を挿入します。この時インデントで半角スペースを1個補っていますが、これは目印タグが1階層上の親階層であるのに対して挿入するのが子階層に位置するためです。
- 33行目
- 前章紫色③の部分を書き出します。
- 35行目
- 結果はイミディエイトウィンドウに書き出しています。ローカルファイルに書き出す場合は適宜修正をしてください。
Option Explicit
Sub keyTagInsert()
Dim buf As String
Dim arr1() As String, arr1w() As String, rslt As String
Dim i As Integer, j As Integer
'固有値設定
Const Flnm As String = "C:\XML\sample-microsoft\sample-02x.xml"
Const Marktg1 As String = "<items", Marktg2 As String = "</item>"
Const Keytg As String = "orderNum"
With CreateObject("ADODB.Stream")
.Charset = "UTF-8"
.Open
.LoadFromFile Flnm
buf = .Readtext
.Close
End With
arr1 = Split(buf, Marktg1, , vbBinaryCompare)
For i = 0 To UBound(arr1) - 1
If i = 0 Then
rslt = rslt & arr1(i) & "<" & Keytg & ">" & i + 1 & "</" & Keytg & ">" & vbCrLf & _
String(2, 32) & Marktg1
Else
rslt = rslt & arr1w(j) & "<" & Keytg & ">" & i + 1 & "</" & Keytg & ">" & vbCrLf & _
String(2, 32) & Marktg1
End If
arr1w = Split(arr1(i + 1), Marktg2, , vbBinaryCompare)
For j = 0 To UBound(arr1w) - 1
rslt = rslt & arr1w(j) & " <" & Keytg & ">" & i + 1 & "</" & Keytg & ">" & vbCrLf & _
String(3, 32) & Marktg2
Next j
Next i
rslt = rslt & arr1w(j)
'イミディエイトウィンドウ出力
Debug.Print rslt
End Sub
※インデントの設定につきましては、対象とするXMLファイルや目印タグの階層の位置によって異なってくると思います。
見栄えの問題なので気にしなくても良いかもしれませんが、必要に応じて適宜修正をしてください。
2.2つのXMLファイルを読み込み、キー項目に基づき合体させる
前章のプログラムでXMLファイルにキー項目が挿入されたので、それぞれのスキーマファイルでExcelに読み込んでデータの修正を行い、その後、2つのExcelデータをそれぞれエクスポートして2つのXMLファイルに書き出します。
これを挿入されたキー項目に基づいて１つにまとめるコーディングを次に考えます。
元となる2つのXMLファイル
実際にExcelからエクスポートした2つのXMLファイル「sample-02x-1e.xml」と「sample-02x-2e.xml」は先の章と同じく「XSDがどのような構造であればExcelでエクスポートできるのか?－その2」に掲載していますので、お手数ですがご参照いただければ幸いです。
キー項目を使って2つのXMLファイルを1つにまとめるプログラムを作る
処理の要件としては下記のようになります。
- キー項目の値が「1から最後まで」以降の処理を繰り返します。
- 最後はsample-02x-1e.xmlをキー項目名”<ns1:orderNum>”でsplitした配列の添え字の最大値になります。
- sample-02x-1e.xmlをキー項目”<ns1:orderNum>X</ns1:orderNum>”(Xは1から最後までの数字)に改行コードを付けた文字列でsplitします。
- インデントは残し、それ以外改行コードも含めて挿入されたキー項目はずへて消去します。
- 続けてsample-02x-2e.xmlも同じキー項目の値でsplitします。こちらはインデントも含めて1行すべて消去します。
- splitした最初のパートからitem要素を取り出すための目印タグとして”<ns1:items>”を使用します。
- 初回はファイル全体を対象にsplitしますが、次にsplitするのは残余(splitした一番最後のパート)の部分になるように変数にセットします。
- 残余の部分は”</item>”タグから始まるので(キー項目が”</item>”の前にセットされているため)、”</item>”タグを取り除く必要があります。
- items要素が空タグだった場合はキー項目は存在しないので、空タグであることを判断して処理します。
上記を実際のXMLファイルの構造に当てはめて補足いたします。
sample-02x-1e.xml
sample-02x-2e.xml
sample-02x-1e.xml
split関数によりキー項目で分割されると①～③のパートに分かれます。
①は最初のパートで③が最後のパートになります。
②はpurchaseOrder要素の数に基づき繰り返します。
sample-02x-2e.xml
items要素が一つにまとまっているために、分割した時に、”<ns1:items>”と”</ns1:items>”のタグを補ってあげる必要があります。
最初のパート①は使用しないitem要素以外の項目があるので、”<ns1:items>”でsplitして分離します。
item要素の数に基づき②は繰り返すのですが、キー項目が変わった時は、「残余」を対象にsplitします。
その場合次に取り出されるのは①’の部分になり、そうするとキー項目が変わった時は”</ns1:item>”(左図太字)を取り除く必要があります。
従いまして、キー項目が変わった時の最後で”<ns1:item”タグを補完してあげる必要があります。
最後のパートにつきましては、すでに補完されている項目とsample-02x-1e.xmlの最後のパートになるので、使用しない事にします。
実際のコーディング例
では次に実際のコーディング例をご説明いたします。
- 8-9行目
- sample-02x-1e.xmlとsample-02x-2e.xmlのファイルを格納している実際のフォルダーに変更します。
- 28行目
- firstfgというboolean型に初期値を設定しています。このフラグは前章のsample-02x-2e.xmlでの①の処理が「1回しか入らない事を制御する」ために使用します。
- 29-30行目
- sample-02x-1e.xmlをキー項目名””でsplitした配列の添え字の最大値をimaxにセットします。
- imaxは31行目のFor文の添え字の最大値として使用します。
- 32行目
- 改行コードを含めたキー項目でsample-02x-1e.xmlをsplitします。
- 34行目
- キー項目でsplitした残余を読み込み変数にセットして一度処理したパートを除外します。
- 35行目
- sample-02x-2e.xmlも32行目と同じキー項目の値でsplitします。こちらはインデントも含めて1行すべて消去します。
- 36行目
- 配列arr2の添え字の最大値がゼロになるのは、該当のキー項目値がsample-02x-2e.xmlに存在しない場合です。つまり該当のitems要素が空タグである事を意味します。
- 38行目
- sample-02x-2e.xmlを当該キー項目で分割した時最初のパートである事を判断しています。
- 39-40行目
- 28行目でご説明したように、このIf文には1回しか該当しないように制御しています。
- 42行目
- split(…)(1)なので目印タグで分割した時の最初から2番目のパートを使用しています。split(…)(0)は使用しません。
- 45行目
- キー替わりの際の処理になります。前章の①’の部分ですが、このsplit関数は目印タグを削除するために使用しています。
- 48行目
- 同一キー項目値の繰り返し処理になります。前章の②の部分です。
- 51行目
- キー替わりの際の45行目で削除した目印タグをインデントと合わせて補完しています。
- 52行目
- items要素は1つにまとまっているために、キー替わりでタグを補完する必要があります。
- 53行目
- キー項目でsplitした残余を読み込み変数にセットして一度処理したパートを除外します。
- 55-56行目
- 空タグの時の処理です。配列には元の値がそのまま入っていますので、そのまま次の処理で使用するように読み込み変数にセットします。
- 59行目
- 前章sample-02x-1e.xmlの③のパートをセットします。
- 61行目
- 結果はイミディエイトウィンドウに書き出しています。ローカルファイルに書き出す場合は適宜修正をしてください。
Option Explicit
Sub xmlTagMarge()
Dim buf1 As String, buf2 As String
Dim arr1() As String, arr2() As String, rslt As String
Dim i1 As Integer, i2 As Integer, imax As Integer, j As Integer, firstfg As Boolean
'固有値設定
Const flnm1 As String = "C:\XML\sample-microsoft\sample-02x-1e.xml"
Const flnm2 As String = "C:\XML\sample-microsoft\sample-02x-2e.xml"
Const Marktg11 As String = "<ns1:items>", Marktg12 As String = "</ns1:items>"
Const Marktg2 As String = "</ns1:item>"
Const Marktg3 As String = "<ns1:items/>"
Const keytg As String = "ns1:orderNum"
With CreateObject("ADODB.Stream")
.Charset = "UTF-8"
.Open
.LoadFromFile flnm1
buf1 = .Readtext
.Close
End With
With CreateObject("ADODB.Stream")
.Charset = "UTF-8"
.Open
.LoadFromFile flnm2
buf2 = .Readtext
.Close
End With
firstfg = True
arr1 = Split(buf1, "<" & keytg & ">", , vbBinaryCompare)
imax = UBound(arr1)
For i1 = 1 To imax
arr1 = Split(buf1, "<" & keytg & ">" & i1 & "</" & keytg & ">" & vbCrLf, , vbBinaryCompare)
rslt = rslt & arr1(0)
buf1 = arr1(1)
arr2 = Split(buf2, String(4, 9) & "<" & keytg & ">" & i1 & "</" & keytg & ">" & vbCrLf, , vbBinaryCompare)
If UBound(arr2) Then
For i2 = 0 To UBound(arr2) - 1
If i2 = 0 Then
If firstfg Then
firstfg = False
rslt = rslt & Marktg11 '次のSplit消去補完
rslt = rslt & Split(arr2(0), Marktg11, , vbBinaryCompare)(1)
Else
rslt = rslt & Marktg11 & vbCrLf '欠落補完
rslt = rslt & Split(arr2(0), Marktg2 & vbCrLf, , vbBinaryCompare)(1) '削除
End If
Else
rslt = rslt & arr2(i2)
End If
Next i2
rslt = rslt & String(3, 9) & Marktg2 & vbCrLf 'キー替わり補完
rslt = rslt & String(2, 9) & Marktg12 & vbCrLf '欠落補完
buf2 = arr2(i2)
Else
rslt = rslt & Marktg3 & vbCrLf '空タグ
buf2 = arr2(0)
End If
Next i1
rslt = rslt & buf1
'イミディエイトウィンドウ出力
Debug.Print rslt
End Sub
※インデントにつきましては先のプログラムと同様適宜修正をしてください。
まとめ
今回は「XSDがどのような構造であればExcelでエクスポートできるのか?－その2」ではご紹介できなかったに実際のプログラムをご説明いたしました。
XMLファイルにタグを挿入したり、タグを消去したりするような要件があった場合に、ご参考にしていただければ幸いです。
以上最後までご一読いただき誠にありがとうございました。
