差分

このページの2つのバージョン間の差分を表示します。

--- powershell:excelをcsvから更新1 [2023/04/19 04:19] – taatin
+++ powershell:excelをcsvから更新1 [2023/04/19 05:40] (現在) – taatin
@@ 行 20: / 行 20: @@
   * 変更を保存し、Excelを閉じる
   * COMオブジェクトの解放
+<code># 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)
+</code>
+======　更新前 ======
 <code>
 function Get-Range {