Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
###########################################
# 関数定義など
###########################################
function Format-Json([Parameter(Mandatory, ValueFromPipeline)][String] $json) {
$indent = 0;
($json -Split "`n" | % {
if ($_ -match '[\}\]]\s*,?\s*$') {
# This line ends with ] or }, decrement the indentation level
$indent--
}
$line = (' ' * $indent) + $($_.TrimStart() -replace '": (["{[])', '": $1' -replace ': ', ': ')
if ($_ -match '[\{\[]\s*$') {
# This line ends with [ or {, increment the indentation level
$indent++
}
$line
}) -Join "`n"
}
$TypeMap = @{
"String" = "S"
"JSON" = "S"
"S" = "S"
"Boolean" = "BOOL"
"BOOL" = "BOOL"
"Number" = "N"
"N" = "N"
}
###########################################
# Main処理
###########################################
# エクセルオブジェクトの作成
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$appFiles = Get-ChildItem -Path ("data\*.xlsx") -File
$reqAddList = @()
$DataSetIdList = @()
$TableList = @()
foreach($file in $appFiles) {
try {
# エクセルのブック、シートオブジェクトを取得
$book = $excel.Workbooks.Open($file.FullName)
echo $file.FullName
foreach($sheet in $excel.Worksheets){
# echo $sheet.Name
# echo $sheet.UsedRange.Columns.Count
$TableList += $sheet.Name
$row = 4
while ($sheet.Cells.Item($row, 1).Text -ne '')
{
$assArray = @{}
$assArray.Add("DataSetId",$sheet.Cells.Item($row, 1).Text)
$assArray.Add("TableName",$sheet.Name)
# データセットのID(テスト実施単位のテストIDなど)のリストを作成する
if(-not ($DataSetIdList.Contains($sheet.Cells.Item($row, 1).Text))){
$DataSetIdList += $sheet.Cells.Item($row, 1).Text
}
for($col = 2;$col -lt $sheet.UsedRange.Columns.Count;$col++){
# 型名取得
$typeName = $sheet.Cells.Item(2, $col).Text
$typeName = $TypeMap.$typeName
if(-not($sheet.Cells.Item(1, $col).Text.Contains("PartitionKey")) -And -not($sheet.Cells.Item(1, $col).Text.Contains("RangeKey"))){
continue
}
if($sheet.Cells.Item($row, $col).Text -eq ""){
continue;
}
# 型に応じて変換が必要
try{
if($typeName -eq "BOOL"){
$value = [System.Convert]::ToBoolean($sheet.Cells.Item($row, $col).Text)
$assArrayChild = @{$TypeMap.$typeName = $value}
}elseif($typeName -eq "N"){
$value = [int]$sheet.Cells.Item($row, $col).Text
$assArrayChild = @{$TypeMap.$typeName = $value}
}elseif($typeName -eq "S"){
$value = [string]$sheet.Cells.Item($row, $col).Text
$assArrayChild = @{$TypeMap.$typeName = $value}
}else{
throw("ツールに対応していない型名が入力されました。")
}
} catch {
throw ( "型名変換に失敗、エクセルファイルを修正してください。:`r`n" + $_ )
}
$assArray.Add($sheet.Cells.Item(3, $col).Text, $assArrayChild)
}
$reqAddList += $assArray
$row++
}
}
# データセットIDごとに走査
foreach($DataSetId in $DataSetIdList){
echo "start $DataSetId"
$gIdAssArray = @{}
# テーブルごとに走査
foreach($table in $TableList){
$tableAssList = @()
foreach($req in $reqAddList){
if($DataSetId -eq $req."DataSetId" -and $table -eq $req."TableName"){
# echo $DataSetId
$tableName = $req."TableName"
$req.Remove("DataSetId")
$req.Remove("TableName")
$keyAssArray = @{}
$keyAssArray.Add("DeleteRequest",@{"Key" = $req})
$tableAssList += $keyAssArray
}
}
$gIdAssArray.Add($tableName, $tableAssList)
}
# データセットID単位でデータ導入用ファイルを出力する
ConvertTo-Json $gIdAssArray -Depth 10 | Format-Json | Out-File ($DataSetId + ".json") -Encoding default
echo "end $DataSetId"
}
} catch {
echo $_
} finally {
# エクセルのブックオブジェクトの削除
if($book -ne $null) {
$book.Close()
}
$book = $null
$sheet = $null
echo "ツール実行終了"
pause
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment