Powershell Datagridview Zeilen bearbeiten

PS C:\Windows\system32> Install-Module -Name PSExcel -Force
PS C:\Windows\system32> Install-Module -Name ImportExcel -Force

Import-Module ImportExcel

function Edit-GridData {

    [CmdletBinding()]
    param(
        [Parameter(
            Position=0,Mandatory = $true,
            ValueFromPipeline=$true,
            ValueFromPipelineByPropertyName = $true
         )]
        [PSObject]
        $InputObject,
        
        [Parameter(
            Position=1,
            Mandatory=$false
        )]
        [string[]]
        $LockedColumns
    )

    $table = New-Object System.Data.DataTable
    $InputObject[0].PSObject.Properties.Name | ForEach-Object {
        $table.Columns.Add($_, [System.Type]::GetType("System.String")).Caption = $_  
    }

    $InputObject | ForEach-Object {
        $row = $table.NewRow()
        foreach ($column in $table.Columns) {
            $value = $_.($column.ColumnName).ToString()
            $row.($column.ColumnName) = $value
        }
        $table.Rows.Add($row)
    }

    [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") | Out-Null  

    $grid = New-Object System.Windows.Forms.DataGridView
    $grid.AllowUserToAddRows = $true
    $grid.AllowUserToDeleteRows = $true
    $grid.AutoSizeColumnsMode = [System.Windows.Forms.DataGridViewAutoSizeColumnsMode]::Fill
    $grid.ColumnHeadersHeightSizeMode = 'AutoSize'  
    $grid.RowTemplate.Height = 30
    $grid.DataSource = $table
    
    # Set ReadOnly property of each row to True
    foreach ($row in $grid.Rows) {
        $row.ReadOnly = $true
    }

    $grid.Anchor = "None"  
    $grid.Dock = "Fill"  

    $buttonPanel = New-Object System.Windows.Forms.Panel
    $buttonPanel.Dock = "Bottom"  
    $buttonPanel.Height = 40
    $buttonPanel.Margin = '0, 0, 0, 0'  

    $okButton = New-Object System.Windows.Forms.Button
    $okButton.Text = "OK"  
    $okButton.Width = 80
    $okButton.Height = 30
    $okButton.DialogResult = [System.Windows.Forms.DialogResult]::OK
    $okButton.Anchor = "Right"  


    $buttonPanel.Controls.Add($okButton)

    $dialog = New-Object System.Windows.Forms.Form
    $dialog.Text = "Edit Grid View"  
    $dialog.Width = 1600
    $dialog.Height = 1000
    $dialog.Controls.Add($grid)
    $dialog.Controls.Add($buttonPanel)

    $result = $dialog.ShowDialog()

    if ($result -eq [System.Windows.Forms.DialogResult]::OK) {
         $output = @()
        foreach ($row in $grid.Rows) {
            $obj = New-Object System.Management.Automation.PSObject
            for ($i = 0; $i -lt $grid.Columns.Count; $i++) {
                $columnName = $grid.Columns[$i].DataPropertyName
                $columnValue = $row.Cells[$i].Value
                if (-not [string]::IsNullOrEmpty($columnValue)) {
                    $obj | Add-Member -MemberType NoteProperty -Name $columnName -Value $columnValue
                }
            }
            $output += $obj
        }
        return [PSObject]$output
    }
}

function overrideDataList {

    [CmdletBinding()]
    param(
        [Parameter(
            Position=0,
            Mandatory=$true,
            ValueFromPipeline=$true,
            ValueFromPipelineByPropertyName=$true
        )]
        [PSObject[]]
        $InputObject,
        
        [Parameter(
            Position=1,
            Mandatory=$true
        )]
        [PSObject[]]
        $updateData,
        
        [Parameter(
            Position=2,
            Mandatory=$true
        )]
        [String]
        $ByPropertyName
    )

    foreach ($update in $updateData) {
        $index = $InputObject."$ByPropertyName".IndexOf($update."$ByPropertyName")  
        if ($index -ne -1) {
            $InputObject[$index] = $update
        }
    }



    return $InputObject
}

# Create sample data
$sampleData = [PSCustomObject]@{
    Name = "John Doe"  
    Email = "johndoe@example.com"  
    Phone = "555-1234"  
}

$sampleData = Edit-GridData -InputObject $sampleData

# Export sample data to Excel file
$excelFile = ".\Phonebook.xlsx"  
$sampleData | Export-Excel -Path $excelFile -AutoSize -TableName "Phonebook" -WorksheetName "Phonebook"  

# Import data from Excel file
$importedData = Import-Excel -Path $excelFile -WorksheetName "Phonebook"  

# Edit data using grid view
$updatedData = Edit-GridData -InputObject $importedData

# Update imported data with edited data
$importedData = overrideDataList -InputObject $importedData -updateData $updatedData -ByPropertyName "Name"  

# Export updated data to Excel file
$importedData | Export-Excel -Path $excelFile -AutoSize -TableName "Phonebook" -WorksheetName "Phonebook"  

function Edit-GridData {

    [CmdletBinding()]
    param(
        [Parameter(
            Position=0,Mandatory = $true,
            ValueFromPipeline=$true,
            ValueFromPipelineByPropertyName = $true
         )]
        [PSObject]
        $InputObject,
        
        [Parameter(
            Position=1,
            Mandatory=$false
        )]
        [string[]]
        $LockedColumns
    )

    $table = New-Object System.Data.DataTable
    $InputObject[0].PSObject.Properties.Name | ForEach-Object {
        $table.Columns.Add($_, [System.Type]::GetType("System.String")).Caption = $_  
    }

    $InputObject | ForEach-Object {
        $row = $table.NewRow()
        foreach ($column in $table.Columns) {
            $value = $_.($column.ColumnName).ToString()
            $row.($column.ColumnName) = $value
        }
        $table.Rows.Add($row)
    }

    [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") | Out-Null  

    $grid = New-Object System.Windows.Forms.DataGridView
    $grid.AllowUserToAddRows = $true
    $grid.AllowUserToDeleteRows = $true
    $grid.AutoSizeColumnsMode = [System.Windows.Forms.DataGridViewAutoSizeColumnsMode]::Fill
    $grid.ColumnHeadersHeightSizeMode = 'AutoSize'  
    $grid.RowTemplate.Height = 30
    $grid.DataSource = $table
    
    # Set ReadOnly property of each row to True
    foreach ($row in $grid.Rows) {
        $row.ReadOnly = $true
    }

    $grid.Anchor = "None"  
    $grid.Dock = "Fill"  

    $buttonPanel = New-Object System.Windows.Forms.Panel
    $buttonPanel.Dock = "Bottom"  
    $buttonPanel.Height = 40
    $buttonPanel.Margin = '0, 0, 0, 0'  

    $okButton = New-Object System.Windows.Forms.Button
    $okButton.Text = "OK"  
    $okButton.Width = 80
    $okButton.Height = 30
    $okButton.DialogResult = [System.Windows.Forms.DialogResult]::OK
    $okButton.Anchor = "Right"  


    $buttonPanel.Controls.Add($okButton)

    $dialog = New-Object System.Windows.Forms.Form
    $dialog.Text = "Edit Grid View"  
    $dialog.Width = 1600
    $dialog.Height = 1000
    $dialog.Controls.Add($grid)
    $dialog.Controls.Add($buttonPanel)

    $result = $dialog.ShowDialog()

    if ($result -eq [System.Windows.Forms.DialogResult]::OK) {
         $output = @()
        foreach ($row in $grid.Rows) {
            $obj = New-Object System.Management.Automation.PSObject
            for ($i = 0; $i -lt $grid.Columns.Count; $i++) {
                $columnName = $grid.Columns[$i].DataPropertyName
                $columnValue = $row.Cells[$i].Value
                if (-not [string]::IsNullOrEmpty($columnValue)) {
                    $obj | Add-Member -MemberType NoteProperty -Name $columnName -Value $columnValue
                }
            }
            $output += $obj
        }
        return [PSObject]$output
    }
}

function overrideDataList {

    [CmdletBinding()]
    param(
        [Parameter(
            Position=0,
            Mandatory=$true,
            ValueFromPipeline=$true,
            ValueFromPipelineByPropertyName=$true
        )]
        [PSObject[]]
        $InputObject,
        
        [Parameter(
            Position=1,
            Mandatory=$true
        )]
        [PSObject[]]
        $updateData,
        
        [Parameter(
            Position=2,
            Mandatory=$true
        )]
        [String]
        $ByPropertyName
    )

    foreach ($update in $updateData) {
        $index = $InputObject."$ByPropertyName".IndexOf($update."$ByPropertyName")  
        if ($index -ne -1) {
            $InputObject[$index] = $update
        }
    }



    return $InputObject
}

# Create sample data
$sampleData = [PSCustomObject]@{
    Name = "John Doe"  
    Email = "johndoe@example.com"  
    Phone = "555-1234"  
}

$sampleData = Edit-GridData -InputObject $sampleData

# Export sample data to Excel file
$excelFile = ".\Phonebook.xlsx"  
$sampleData | Export-Excel -Path $excelFile -AutoSize -TableName "Phonebook" -WorksheetName "Phonebook"  

# Import data from Excel file
$importedData = Import-Excel -Path $excelFile -WorksheetName "Phonebook"  

# Edit data using grid view
$updatedData = Edit-GridData -InputObject $importedData

# Edit data using grid view
$updatedData = Edit-GridData -InputObject $importedData

# Update imported data with edited data
$importedData = overrideDataList -InputObject $importedData -updateData $updatedData -ByPropertyName "Name"