Chat GPTでつくったスクリプトや設定の共有とかメモ

ユーザ用ツール

サイト用ツール

トレース: • excelをcsvから更新1
powershell:excelをcsvから更新1

目次

要件の更新

  • Excelファイルを開く
  • シート「オブジェクト」とCSVファイル「Object.csv」を比較
  • CSVファイルの要素を洗い出し、同じ要素がシート「オブジェクト」の1行目に存在するか確認
  • ForeachでObject.csvの各行の要素の値が、「オブジェクト」シートの同じ行の値であれば、同じ内容とする
  • 異なるファイルであった場合、$UpdateSheetSummaryに「・オブジェクト更新」の文字を追加する
  • シート「ポリシ」とCSVファイル「Polisy.csv」を比較
  • CSVファイルの要素を左から順に8個洗い出し、シート「ポリシ」のA1列-H1列に記載の要素が同じことを確認
  • ForeachでObject.csvの各行の左から8個の要素の値が、「ポリシ」シートとA列-H列各行が同じ値であれば、同じ内容とする
  • 異なるファイルであった場合、$UpdateSheetSummaryに「・ポリシ」の文字を追加する
  • 履歴シートの更新
  • C列に値が入っている最終行を探し、その3行下が更新行
  • 更新行に次の情報を入力する
  • Revision (C列)
  • UpdateDate (H列)
  • UpdateSheet (M列)
  • UpdateDetails (R列)
  • バックアップを作成
  • 変更を保存し、Excelを閉じる
  • COMオブジェクトの解放
# Excelファイルを開く
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$workbook = $excel.Workbooks.Open("C:\path\to\TEST.xlsx")
$worksheetHistory = $workbook.Worksheets.Item("履歴")
$worksheetObject = $workbook.Worksheets.Item("オブジェクト")
$worksheetPolisy = $workbook.Worksheets.Item("ポリシ")

# シートとCSVを比較して更新
$UpdateSheetSummary = ""

# 1. シート「オブジェクト」とCSVファイル「Object.csv」を比較
$objectCsv = Import-Csv -Path "Object.csv"
$objectSheetHeaders = $worksheetObject.Rows.Item(1).Cells.Value2
$objectSheetData = $worksheetObject.Range("A2", $worksheetObject.Cells.SpecialCells("xlCellTypeLastCell")).Value2
$headersMatch = ($objectCsv[0].PSObject.Properties.Name -join ",") -eq ($objectSheetHeaders -join ",")

if ($headersMatch) {
    $rowsMatch = $true
    for ($i = 0; $i -lt $objectCsv.Count; $i++) {
        if (($objectCsv[$i].PSObject.Properties.Value -join ",") -ne ($objectSheetData[$i] -join ",")) {
            $rowsMatch = $false
            break
        }
    }
} else {
    $rowsMatch = $false
}
if (-not $rowsMatch) {
    $UpdateSheetSummary += "・オブジェクト更新"
}

# 2. シート「ポリシ」とCSVファイル「Polisy.csv」を比較
$polisyCsv = Import-Csv -Path "Polisy.csv"
$polisySheetHeaders = $worksheetPolisy.Rows.Item(1).Cells.Value2
$polisySheetData = $worksheetPolisy.Range("A2", $worksheetPolisy.Cells.SpecialCells("xlCellTypeLastCell")).Value2
$headersMatch = ($polisyCsv[0].PSObject.Properties.Name | Select-Object -First 8 -join ",") -eq ($polisySheetHeaders | Select-Object -First 8 -join ",")

if ($headersMatch) {
    $rowsMatch = $true
    for ($i = 0; $i -lt $polisyCsv.Count; $i++) {
        if (($polisyCsv[$i].PSObject.Properties.Value | Select-Object -First 8 -join ",") -ne ($polisySheetData[$i] | Select-Object -First 8 -join ",")) {
            $rowsMatch = $false
            break
        }
    }
} else {
    $rowsMatch = $false
}
if (-not $rowsMatch) {
    $UpdateSheetSummary += "・ポリシ"
}

# 履歴シートの更新
$lastUpdateRow = ($worksheetHistory.Cells["C:C"].Where({$_.Value -ne $null}) | Select-Object -Last 1).Start.Row
$updateRow = $lastUpdateRow + 3
$worksheetHistory.Cells.Item($updateRow, "C").Value2 = $worksheetHistory.Cells.Item(2, "W").Value2 + 1
$worksheetHistory.Cells.Item($updateRow, "H").Value2 = $worksheetHistory.Cells.Item(2, "AB").Value2
$worksheetHistory.Cells.Item($updateRow, "M").Value2 = $UpdateSheetSummary
$worksheetHistory.Cells.Item($updateRow, "R").Value2 = (Get-Date).ToString("yyyy/MM/dd") + " " + $UpdateSheetSummary + "`r`n`r`n"

# バックアップを作成
$backupFileName = $worksheetHistory.Cells.Item(2, "AB").Value2.Replace("/", "") + "_TEST.xlsx"
$workbook.SaveCopyAs("C:\path\to\backup\$backupFileName")

# 変更を保存し、Excelを閉じる
$workbook.Save()
$workbook.Close()
$excel.Quit()

# COMオブジェクトの解放
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($worksheetHistory)
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($worksheetObject)
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($worksheetPolisy)
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbook)
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel)

　更新前

function Get-Range {
    param (
        $worksheet,
        $range
    )
    $rows = $range.Rows.Count
    $cols = $range.Columns.Count
    $array = New-Object 'object[,]' $rows,$cols
    for ($r = 1; $r -le $rows; $r++) {
        for ($c = 1; $c -le $cols; $c++) {
            $array[$r-1, $c-1] = $worksheet.Cells.Item($r, $c).Value2
        }
    }
    return $array
}

# Excelファイルを開く
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$workbook = $excel.Workbooks.Open("C:\path\to\TEST.xlsx")
$worksheetHistory = $workbook.Worksheets.Item("履歴")
$worksheetObject = $workbook.Worksheets.Item("オブジェクト")
$worksheetPolisy = $workbook.Worksheets.Item("ポリシ")

$objectRange = $worksheetObject.UsedRange
$objectSheet = Get-Range -worksheet $worksheetObject -range $objectRange

$polisyRange = $worksheetPolisy.UsedRange
$polisySheet = Get-Range -worksheet $worksheetPolisy -range $polisyRange

# シートとCSVを比較して更新
$UpdateSheetSummary = ""

# 1. シート「オブジェクト」とCSVファイル「Object.csv」を比較
$objectCsv = Import-Csv -Path "Object.csv"
$headersMatch = ($objectCsv[0].PSObject.Properties.Name -join ",") -eq ($objectSheet[0,] -join ",")
if ($headersMatch) {
    $rowsMatch = $true
    for ($i = 0; $i -lt $objectCsv.Count; $i++) {
        if (($objectCsv[$i].PSObject.Properties.Value -join ",") -ne ($objectSheet[$i + 1,] -join ",")) {
            $rowsMatch = $false
            break
        }
    }
} else {
    $rowsMatch = $false
}
if (-not $rowsMatch) {
    $UpdateSheetSummary += "・オブジェクト更新"
}

# 2. シート「ポリシ」とCSVファイル「Polisy.csv」を比較
$polisyCsv = Import-Csv -Path "Polisy.csv"
$headersMatch = ($polisyCsv[0].PSObject.Properties.Name | Select-Object -First 8 -join ",") -eq ($polisySheet[0,0..7] -join ",")
if ($headersMatch) {
    $rowsMatch = $true
    for ($i = 0; $i -lt $polisyCsv.Count; $i++) {
        if (($polisyCsv[$i].PSObject.Properties.Value | Select-Object -First 8 -join ",") -ne ($polisySheet[$i + 1,0..7] -join ",")) {
            $rowsMatch = $false
            break
        }
    }
} else {
    $rowsMatch = $false
}
if (-not $rowsMatch) {
    $UpdateSheetSummary += "・ポリシ"
}
# 履歴シートの更新
$lastUpdateRow = ($worksheetHistory.Cells["C:C"].Where({$_.Value -ne $null}) | Select-Object -Last 1).Start.Row
$updateRow = $lastUpdateRow + 3
$worksheetHistory.Cells.Item($updateRow, "C").Value2 = $worksheetHistory.Cells.Item(2, "W").Value2 + 1
$worksheetHistory.Cells.Item($updateRow, "H").Value2 = $worksheetHistory.Cells.Item(2, "AB").Value2
$worksheetHistory.Cells.Item($updateRow, "M").Value2 = $UpdateSheetSummary
$worksheetHistory.Cells.Item($updateRow, "R").Value2 = (Get-Date).ToString("yyyy/MM/dd") + " " + $UpdateSheetSummary + "`r`n`r`n"

# バックアップを作成
$backupFileName = $worksheetHistory.Cells.Item(2, "AB").Value2.Replace("/", "") + "_TEST.xlsx"
$workbook.SaveCopyAs("C:\path\to\backup\$backupFileName")

# 変更を保存し、Excelを閉じる
$workbook.Save()
$workbook.Close()
$excel.Quit()

# COMオブジェクトの解放
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($worksheetHistory)
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($worksheetObject)
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($worksheetPolisy)
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbook)
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel)

要件更新前

  • TEST.xlsxのバックアップを取得し、“YYYYMMDD_TEST.xlsx”という名前で保存する。
  • シート「履歴」AB2セルには、日付が記載されています。
  • シート「オブジェクト」とCSVファイル「Object.csv」、シート「ポリシ」とCSVファイル「Polisy.csv」を比較し、差分があるシートを削除し、新しいシートを追加します。更新したシート名を変数$UpdateSheetSummaryに格納します。
  • シート「履歴」AB2セルに、日付を“YYYY/MM/DD”のフォーマットで入力します。W2セルの値を1増やし、小数点がある場合は切り捨てます。
  • 履歴が記載されているC列を確認し、最後に数字が記載されている行を前回の更新行とし、その3行下を今回の更新行とします。
  • 更新行のC列にW2セルの値をコピーし、H列にAB2セルの値をコピーします。M列に$UpdateSheetSummaryを記載し、R列に“日付 $UpdateSheetSummary (LF)(LF)“を記載します。
  • 最後に、TEST.xlsxを保存します。
  • 注意事項:
  • シートとCSVの比較はA列からH列を比較してください。
  • PowerShellスクリプトには、シートとCSVファイルの比較および更新に関するコードと、最後の数値が記載されている行を見つけるコードが不足しているため、適切なコードに置き換える必要があります。
# Excelモジュールを読み込む
Import-Module ImportExcel

# ファイルパスを定義
$excelFilePath = "TEST.xlsx"
$backupDate = (Get-Content $excelFilePath | Select-Object -ExpandProperty 'AB2').ToString("yyyyMMdd")
$backupFilePath = $backupDate + "_TEST.xlsx"

# 元のファイルのバックアップを作成
Copy-Item $excelFilePath $backupFilePath

# Excelファイルを開く
$excel = Open-ExcelPackage -Path $excelFilePath

# シートとCSVを取得
$sheetHistory = $excel.Workbook.Worksheets['履歴']
$sheetObject = $excel.Workbook.Worksheets['オブジェクト']
$sheetPolicy = $excel.Workbook.Worksheets['ポリシ']
$csvObject = Import-Csv "Object.csv"
$csvPolicy = Import-Csv "Polisy.csv"

# シートとCSVを比較して更新
$UpdateSheetSummary = # A列からH列までのシートとCSVファイルを比較して更新するコード

# 「履歴」シートを更新
$sheetHistory.AB2.Value = (Get-Date).ToString("yyyy/MM/dd")
$sheetHistory.W2.Value = [math]::Floor($sheetHistory.W2.Value) + 1

# C列にRevision、H列にUpdateDate、M列にUpdateSheet、R列にUpdateDetailsがあります
$lastUpdateRow = ($sheetHistory.Cells["C:C"].Where({$_.Value -ne $null}) | Select-Object -Last 1).Start.Row
$updateRow = $lastUpdateRow + 3

$sheetHistory.Cells["C$updateRow"].Value = $sheetHistory.W2.Value
$sheetHistory.Cells["H$updateRow"].Value = $sheetHistory.AB2.Value
$sheetHistory.Cells["M$updateRow"].Value = $UpdateSheetSummary
$sheetHistory.Cells["R$updateRow"].Value = (Get-Date).ToString("yyyy/MM/dd") + " $UpdateSheetSummary (LF)(LF)"

# Excelファイルを保存して閉じる
Close-ExcelPackage $excel -Show

シートとCSVファイルの比較および更新に関するコードの例 

# 1. シート「オブジェクト」とCSVファイル「Object.csv」を比較
# CSVファイルを読み込む
$objectCsv = Import-Csv -Path "Object.csv"
$objectSheet = $worksheetObject.UsedRange.Value2

# ヘッダー行の要素を取得し、シート「オブジェクト」の1行目に同じ要素が存在するか確認
$headersMatch = ($objectCsv[0].PSObject.Properties.Name -join ",") -eq ($objectSheet[0] -join ",")

# ヘッダー行が一致した場合、要素の値を比較
if ($headersMatch) {
    $rowsMatch = $true
    for ($i = 0; $i -lt $objectCsv.Count; $i++) {
        # 各行の要素の値が一致するか確認
        if (($objectCsv[$i].PSObject.Properties.Value -join ",") -ne ($objectSheet[$i + 1] -join ",")) {
            $rowsMatch = $false
            break
        }
    }
} else {
    $rowsMatch = $false
}

if (-not $rowsMatch) {
    $UpdateSheetSummary += "・オブジェクト更新"
}

# 2. シート「ポリシ」とCSVファイル「Polisy.csv」を比較
# CSVファイルを読み込む
$polisyCsv = Import-Csv -Path "Polisy.csv"
$polisySheet = $worksheetPolisy.UsedRange.Value2

# ヘッダー行の要素を左から順に8個取得し、シート「ポリシ」のA1列-H1列に記載の要素が同じことを確認
$headersMatch = ($polisyCsv[0].PSObject.Properties.Name | Select-Object -First 8 -join ",") -eq ($polisySheet[0] | Select-Object -First 8 -join ",")

# ヘッダー行が一致した場合、要素の値を比較
if ($headersMatch) {
    $rowsMatch = $true
    for ($i = 0; $i -lt $polisyCsv.Count; $i++) {
        # 各行の左から8個の要素の値が一致するか確認
        if (($polisyCsv[$i].PSObject.Properties.Value | Select-Object -First 8 -join ",") -ne ($polisySheet[$i + 1] | Select-Object -First 8 -join ",")) {
            $rowsMatch = $false
            break
        }
    }
} else {
    $rowsMatch = $false
}

if (-not $rowsMatch) {
    $UpdateSheetSummary += "・ポリシ"
}

最後の数値が記載されている行を見つけるコードの例 

$lastUpdateRow = ($sheetHistory.Cells["C:C"].Where({$_.Value -ne $null}) | Select-Object -Last 1).Start.Row
# 開始行を設定（この例ではC10000セルから開始）
$startingRow = 10000
$column = "C"
$lastNumberCell = $null

# C10000セルから逆順に検索を開始
for ($i = $startingRow; $i -gt 0; $i--) {
    # セルの値を取得
    $cell = $sheetHistory.Cells["$column$i"]
    
    # セルの値が数値である場合、検索を終了
    if ($null -ne $cell.Value -and $cell.Value -is [int]) {
        $lastNumberCell = $cell
        break
    }
}

# 最後に数値が記載されているセルの3行下を更新行とする
$updateRow = $lastNumberCell.Start.Row + 3
powershell/excelをcsvから更新1.txt · 最終更新: 2023/04/19 05:40 by taatin
Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki