差分

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

--- powershell:excelをcsvから更新1 [2023/04/19 04:18] – taatin
+++ powershell:excelをcsvから更新1 [2023/04/19 05:40] (現在) – taatin
@@ 行 20: / 行 20: @@
   * 変更を保存し、Excelを閉じる
   * COMオブジェクトの解放
+<code># Excelファイルを開く
-<code>
-# Excelファイルを開く
 $excel = New-Object -ComObject Excel.Application
 $excel.Visible = $false
@@ 行 35: / 行 33: @@
 # 1. シート「オブジェクト」とCSVファイル「Object.csv」を比較
 $objectCsv = Import-Csv -Path "Object.csv"
-$objectSheet = $worksheetObject.UsedRange.Value2
+$objectSheetHeaders = $worksheetObject.Rows.Item(1).Cells.Value2
-$objectSheetHeaders = ($objectSheet | Select-Object -First 1 | ForEach-Object { $_ -join "," }).Split(",")
+$objectSheetData = $worksheetObject.Range("A2", $worksheetObject.Cells.SpecialCells("xlCellTypeLastCell")).Value2
 $headersMatch = ($objectCsv[0].PSObject.Properties.Name -join ",") -eq ($objectSheetHeaders -join ",")
@@ 行 42: / 行 40: @@
     $rowsMatch = $true
     for ($i = 0; $i -lt $objectCsv.Count; $i++) {
-        if (($objectCsv[$i].PSObject.Properties.Value -join ",") -ne ($objectSheet[$i + 1] -join ",")) {
+        if (($objectCsv[$i].PSObject.Properties.Value -join ",") -ne ($objectSheetData[$i] -join ",")) {
             $rowsMatch = $false
             break
@@ 行 56: / 行 54: @@
 # 2. シート「ポリシ」とCSVファイル「Polisy.csv」を比較
 $polisyCsv = Import-Csv -Path "Polisy.csv"
-$polisySheet = $worksheetPolisy.UsedRange.Value2
+$polisySheetHeaders = $worksheetPolisy.Rows.Item(1).Cells.Value2
-$polisySheetHeaders = ($polisySheet | Select-Object -First 1 | ForEach-Object { $_ -join "," }).Split(",")
+$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 ",")
@@ 行 63: / 行 61: @@
     $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] | Select-Object -First 8 -join ",")) {
+        if (($polisyCsv[$i].PSObject.Properties.Value | Select-Object -First 8 -join ",") -ne ($polisySheetData[$i] | Select-Object -First 8 -join ",")) {
             $rowsMatch = $false
             break
@@ 行 76: / 行 74: @@
 # 履歴シートの更新
+$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
@@ 行 97: / 行 96: @@
 [System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbook)
 [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel)
 </code>
+======　更新前 ======
 <code>
 function Get-Range {
@@ 行 114: / 行 117: @@
     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
-function Get-Range {
-    param (
+# シートとCSVを比較して更新
-        $worksheet,
+$UpdateSheetSummary = ""
-        $range
-    )
+# 1. シート「オブジェクト」とCSVファイル「Object.csv」を比較
-    $rows = $range.Rows.Count
+$objectCsv = Import-Csv -Path "Object.csv"
-    $cols = $range.Columns.Count
+$headersMatch = ($objectCsv[0].PSObject.Properties.Name -join ",") -eq ($objectSheet[0,] -join ",")
-    $array = New-Object 'object[,]' $rows,$cols
+if ($headersMatch) {
-    for ($r = 1; $r -le $rows; $r++) {
+    $rowsMatch = $true
-        for ($c = 1; $c -le $cols; $c++) {
+    for ($i = 0; $i -lt $objectCsv.Count; $i++) {
-            $array[$r-1, $c-1] = $worksheet.Cells.Item($r, $c).Value2
+        if (($objectCsv[$i].PSObject.Properties.Value -join ",") -ne ($objectSheet[$i + 1,] -join ",")) {
+            $rowsMatch = $false
+            break
         }
     }
-    return $array
+} else {
+    $rowsMatch = $false
+}
+if (-not $rowsMatch) {
+    $UpdateSheetSummary += "・オブジェクト更新"
 }
-$polisyRange = $worksheetPolisy.UsedRange
+# 2. シート「ポリシ」とCSVファイル「Polisy.csv」を比較
-$polisySheet = Get-Range -worksheet $worksheetPolisy -range $polisyRange
+$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)
 </code>