今回のテーマ
Excelデータを読み込む際に、解析データをスクリプト内で保持するパターンを紹介します。お題
次ようなExcelファイルがフォルダ内に大量にあるものとします。No. | 日時 | 顧客名 | 顧客属性 | 都道府県 | 利用金額 |
---|---|---|---|---|---|
1 | 2020/10/4 | "住処商事" | xxxxxx | 東京 | 1300 |
2 | 2020/10/25 | "香料販売" | xxxxxx | 神奈川 | 14340 |
3 | 2020/10/25 | "Satoru co., Ltd" | xxxxxx | 東京 | 2340 |
... | .... | .... | .... | .... | .... |
サンプルスクリプト
option Explicit Dim fso, ExcelApp, args, f, oFolder, ofile, TotalAmount, TokyoCustomer const xlToLeft = -4159 const xlUp = -4162 const ExcelHeaderLine = 1 'ヘッダー行 const ExcelColHeader = 1 Set fso = CreateObject("Scripting.FileSystemObject") Set ExcelApp = CreateObject("Excel.Application") set TotalAmount = CreateObject("Scripting.Dictionary") '結果を保存するデータ set TokyoCustomer = CreateObject("Scripting.Dictionary") '結果を保存するデータ set args = WScript.Arguments set oFolder = fso.GetFolder( args(0)) for each ofile in oFolder.Files ReadExcelFile ofile.path, TotalAmount, TokyoCustomer next WScript.echo "【顧客別合計金額】" DisplayResult TotalAmount, "顧客名,合計金額" 'データの中身を表示する。 WScript.echo WScript.echo "【東京に拠点のある顧客リスト】" DisplayResult TokyoCustomer, "顧客名,1" 'データの中身を表示する。 ExcelApp.Quit WScript.Quit '--------------------------------------------------- ' ReadExcelFile ' Excelシートの中身を読み出す(1ファイル) ' "名前"列をキーに、値は固定値(1)を代入 → 目的に応じて変更 '--------------------------------------------------- Sub ReadExcelFile( filepath, TotalAmount,TokyoCustomer ) dim obook, ofile, row, oSheet,maxrow, schema, oneLine, UserName, col dim prefecture, amount if fso.FileExists( filepath ) then set ofile = fso.getFile( filepath ) set schema = CreateObject("Scripting.Dictionary") '列名-列番号 set oneLine = CreateObject("Scripting.Dictionary") '列名-データ set oBook = ExcelApp.Workbooks.Open(ofile.path) set oSheet = oBook.WorkSheets(1) maxrow = oSheet.Cells(oSheet.Rows.count,ExcelColHeader).end(xlUp).row ParseSchemaExcel oSheet, ExcelHeaderLine, ExcelColHeader, schema for row = ExcelHeaderLine + 1 to maxrow ParseLineExcel oSheet, row, schema, oneLine 'ロジックエリア 開始 UserName= oneLine.item("顧客名") prefecture= oneLine.item("都道府県") amount= oneLine.item("利用金額") Dict_SumNumeric TotalAmount,UserName, amount if prefecture = "東京" then Dict_IfNotAdd TokyoCustomer, UserName, 1 next oBook.close 'Excelファイルを閉じる else WScript.echo "Can't file " & filepath end if end sub '--------------------------------------------------- ' Dict_IfNotAdd ' Dictionaryでもしキーがなければ追加する。 '--------------------------------------------------- Sub Dict_IfNotAdd( oData, key, avalue ) if not oData.Exists( key ) then oData.Add key, avalue end if end sub '--------------------------------------------------- ' Dict_SumNumeric ' Dictionaryでもしキーがなければ追加し、あれば加算する。 '--------------------------------------------------- Sub Dict_SumNumeric( oData, key, avalue ) if oData.exists( key ) then oData.item( key ) = oData.item( key) + CDbl(avalue) else oData.Add key, CDbl(avalue) end if end sub '--------------------------------------------------- ' ParseSchemaExcel ' ヘッダー列(列名)を読み取り、schemaディクショナリに格納する。 ' 基本はいじらない。 '--------------------------------------------------- Sub ParseSchemaExcel(oSheet, row, colline, schema) dim col, maxcol schema.removeall maxcol = oSheet.Cells(row,oSheet.Columns.count).end(xlToLeft).column for col = colline to maxcol if oSheet.cells( row, col ).value <> "" then schema.add Trim(oSheet.cells( row, col ).value), col end if next end sub '--------------------------------------------------- ' ParseLineExcel ' データ列を読み取り、列名と関連付けてDictionaryに格納する。 ' 基本はいじらない。 '--------------------------------------------------- Sub ParseLineExcel( oSheet, row, schema, oneline ) dim key oneline.removeall for each key in schema.keys oneLine.add key, oSheet.Cells( row, schema.item(key) ).value next end Sub
使い方は
>cscript <スクリプトファイル名>
です。コンソールに保持データが出力されます。解説
ExcelやCSVのデータの”読み取り→出力”のパターンで圧倒的に多いのは
①特定のキーの値を合計する。(例:顧客別利用額合計)
と
②条件に合ったキーのリストを作る
です。これらをvbsのディクショナリーデータで保持する場合、
① キー(顧客名など):値(利用金額など)
および
②キー(顧客名):値(なんでもよい)
といった保持になります。処理のロジックは
①顧客名が既存のキーになければ、”キー:顧客名(username)、値:利用金額(value)”でレコードを追加する
もし既存にあれば、利用金額を加算する。という処理になります。
if Data.exists( username) then Data.item( username) = Data.item(username) + value else Data.Add username, value end if
および
②条件にあった場合、顧客名が既存のキーになければ、”キー:顧客名(username)、値:なんでもよい”でレコードを追加する
if not Data.exists( username) then Data.Add username, "" end if
となります。これらはそれぞれ典型的なパターンであり、上記を毎回(プログラムを作るたび)書くのは無駄なので、テンプレートでは関数化しています。
①のパターンは
Dict_SumNumeric ディクショナリー, キー, 値(数値)
②のパターンは
Dict_IfNotAdd ディクショナリー、キー、値(文字列でもなんでもよい)
として用意しています。これであればプログラム作成時の変更量が大幅に削減され、バグも出にくくなります。今回の場合は、データがふたつになった以外のロジックの記述は
UserName= oneLine.item("顧客名") prefecture= oneLine.item("都道府県") amount= oneLine.item("利用金額") Dict_SumNumeric TotalAmount,UserName, amount if prefecture = "東京" then Dict_IfNotAdd TokyoCustomer, UserName, 1 end if
この7行だけです。あとは、内部保持したデータを標準出力に表示する部分を追加してプログラムは終了です。
このパターンは結構多いので、テンプレートとして活用いただければ幸いです。