Last active
July 23, 2017 09:31
-
-
Save sensq/2fe327b8b4cc3afec3c6cba0546634a8 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
# コマンドライン引数取得(すべて入力必須) | |
Param( | |
[Parameter(Mandatory)] | |
[string]$BookName, # 対象ブック名 | |
[Parameter(Mandatory)] | |
[string]$SheetName, # 対象シート名 | |
[Parameter(Mandatory)] | |
[string]$StartCell, # 対象テーブルの開始セル(テーブルの一番左上のセル)のアドレス | |
[Parameter(Mandatory)] | |
[string]$EndCell # 対象テーブルの終了セル(テーブルの一番右下のセル)のアドレス | |
) | |
##### 入力パラメータ ##### | |
# 入力パラメータオブジェクト作成 | |
$input = [PSCustomObject]@{ | |
bookname = $BookName # "testdata.xlsx" | |
sheetname = $SheetName # "Sheet1" | |
startcell = $StartCell # "D6" | |
endcell = $EndCell # "W12" | |
} | |
##### 関数定義 ##### | |
# 既に開かれているExcelブックから指定された名前のブックオブジェクトを取得する関数 | |
Function Get-ExcelBook($book_name){ | |
#=== 稼働中の Excel を捕まえる | |
$ex = [System.Runtime.InteropServices.Marshal]::GetActiveObject("Excel.Application") | |
if ($ex -eq $null){ | |
return $null | |
} | |
#=== Open中のWorkBookから目的のBookを見つける | |
foreach ($bk in $ex.WorkBooks){ | |
if ($bk.Name -eq $book_name){ | |
return $bk | |
} | |
} | |
return $null | |
} | |
# Excelのテーブルをオブジェクトとして取得する関数 | |
Function Get-TableObject($sheet, $start, $end){ | |
# テーブルオブジェクト作成 | |
$table = [PSCustomObject]@{ | |
start = $sheet.Range($start) | |
end = $sheet.Range($end) | |
key = @() | |
data = @() | |
} | |
#$table = New-Object PSCustomObject | |
#$table | Add-Member -MemberType NoteProperty -Name start -Value $sheet.Range($start) | |
#$table | Add-Member -MemberType NoteProperty -Name end -Value $sheet.Range($end) | |
#$table | Add-Member -MemberType NoteProperty -Name key -Value @() | |
#$table | Add-Member -MemberType NoteProperty -Name data -Value @() | |
# テーブルをオブジェクト化 | |
for($row=$table.start.Row; $row -le $table.end.Row; $row++){ | |
# 最初にデータの無い行は無視 | |
if($sheet.cells.item($row, $table.start.Column).text -eq "" ){ | |
continue | |
} | |
# 1レコード用オブジェクトを準備 | |
$record = New-Object PSCustomObject | |
$key_ref_number = 0 | |
for($col=$table.start.Column; $col -le $table.end.Column; $col++){ | |
# 最初にデータの無い列は無視 | |
if($sheet.cells.item($table.start.Row, $col).text -eq "" ){ | |
continue | |
} | |
# 1行目の値からキー名作成 | |
if($row -eq $table.start.Row){ | |
$table.key += $sheet.cells.item($row, $col).text | |
} | |
# 1レコード作成 | |
else{ | |
$key_name = ($table.key[$key_ref_number]) | |
$val = ($sheet.cells.item($row, $col).text) | |
$record | Add-Member -MemberType NoteProperty -Name $key_name -Value $val | |
$key_ref_number += 1 | |
} | |
} | |
# 1レコード追加 | |
if($row -gt $table.start.Row){ | |
$table.data += $record | |
} | |
} | |
return $table | |
} | |
##### メイン処理 ##### | |
# 指定されたブックを開く | |
$book = (Get-ExcelBook $input.bookname) | |
if($book -eq $null){ | |
echo "Error: 指定された名前のブックが開かれていません。" | |
exit | |
} | |
# 指定されたシートオブジェクトを取得 | |
try{ | |
$sheet = $book.Sheets($input.sheetname) | |
}catch{ | |
echo "Error: 指定された名前のシートが存在しません。" | |
exit | |
} | |
# Excelのテーブルをオブジェクトとして取得 | |
$table = Get-TableObject $sheet $input.startcell $input.endcell | |
return $table.data |
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
$table = .\Get-ExcelTable.ps1 -BookName "testdata.xlsx" -SheetName "Sheet1" -StartCell "D6" -EndCell "W12" | |
echo ('#named.conf') | |
$table | %{ | |
echo ("// $($_.備考)") | |
echo ("zone $($_.ドメイン名) in {") | |
echo (" type ""$($_.type)"";") | |
echo (" file ""$($_.ファイル)"";") | |
echo ("};") | |
echo ("") | |
} | |
echo ('#named.conf.yml') | |
echo ('- named_conf:') | |
$table | %{ | |
echo (" - domain_name: '$($_.ドメイン名)' # $($_.備考)") | |
echo (" type: '$($_.type)'") | |
echo (" file: '$($_.ファイル)'") | |
} |
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
#named.conf | |
// テスト1サーバ | |
zone test1.com in { | |
type "master"; | |
file "test1.zone"; | |
}; | |
// テスト2サーバ | |
zone test2.com in { | |
type "master"; | |
file "test2.zone"; | |
}; | |
// テスト3サーバ | |
zone test3.com in { | |
type "master"; | |
file "test3.zone"; | |
}; | |
// テスト4サーバ | |
zone test4.com in { | |
type "master"; | |
file "test4.zone"; | |
}; | |
// テスト5サーバ | |
zone test5.com in { | |
type "master"; | |
file "test5.zone"; | |
}; | |
// テスト6サーバ | |
zone test6.com in { | |
type "master"; | |
file "test6.zone"; | |
}; | |
#named.conf.yml | |
- named_conf: | |
- domain_name: 'test1.com' # テスト1サーバ | |
type: 'master' | |
file: 'test1.zone' | |
- domain_name: 'test2.com' # テスト2サーバ | |
type: 'master' | |
file: 'test2.zone' | |
- domain_name: 'test3.com' # テスト3サーバ | |
type: 'master' | |
file: 'test3.zone' | |
- domain_name: 'test4.com' # テスト4サーバ | |
type: 'master' | |
file: 'test4.zone' | |
- domain_name: 'test5.com' # テスト5サーバ | |
type: 'master' | |
file: 'test5.zone' | |
- domain_name: 'test6.com' # テスト6サーバ | |
type: 'master' | |
file: 'test6.zone' |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment