create_ddb_del_data_json.ps1

###########################################
# 関数定義など
###########################################

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
	}
}