差分

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

--- powershell:excelをcsvから更新1 [2023/04/06 09:33] – taatin
+++ powershell:excelをcsvから更新1 [2023/04/19 05:40] (現在) – taatin
@@ 行 1: / 行 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ファイルを開く
+  * シート「オブジェクト」と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オブジェクトの解放
+<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 {
+    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
@@ 行 29: / 行 125: @@
 $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を比較して更新
@@ 行 35: / 行 137: @@
 # 1. シート「オブジェクト」とCSVファイル「Object.csv」を比較
 $objectCsv = Import-Csv -Path "Object.csv"
-$objectSheet = $worksheetObject.UsedRange.Value2
+$headersMatch = ($objectCsv[0].PSObject.Properties.Name -join ",") -eq ($objectSheet[0,] -join ",")
-$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 ",")) {
+        if (($objectCsv[$i].PSObject.Properties.Value -join ",") -ne ($objectSheet[$i + 1,] -join ",")) {
             $rowsMatch = $false
             break
@@ 行 54: / 行 155: @@
 # 2. シート「ポリシ」とCSVファイル「Polisy.csv」を比較
 $polisyCsv = Import-Csv -Path "Polisy.csv"
-$polisySheet = $worksheetPolisy.UsedRange.Value2
+$headersMatch = ($polisyCsv[0].PSObject.Properties.Name | Select-Object -First 8 -join ",") -eq ($polisySheet[0,0..7] -join ",")
-$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++) {
-        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 ($polisySheet[$i + 1,0..7] -join ",")) {
             $rowsMatch = $false
             break
@@ 行 93: / 行 193: @@
 [System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbook)
 [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel)
-</code>
+</code>