Last active
October 25, 2021 13:41
-
-
Save YasuoFromDeadScream/464ad03f48d90c8cf330b58e33c4ff9b to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
########################################### | |
# 関数定義など | |
########################################### | |
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 ("*.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("PrimaryKey")) -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 | |
} | |
} | |
if( $gIdAssArray.ContainsKey($tableName) -eq $true ){ | |
# $temp = $gIdAssArray[$tableName] | |
# echo $temp | |
# exit | |
# $gIdAssArray[$tableName] = | |
# $gIdAssArray.Add($tableName, $tableAssList) | |
echo "キーは存在します" | |
}else{ | |
$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