Excel spreadsheet having line breaks

54 Views Asked by At

I have excel spread sheet where NSG rules are added in that excel spread sheet. I am writing PowerShell code to check the completely excel sheet if any line breaks are present in the sheet (attached the screenshot and highlighted in the red color) and if any line break exists then convert the values by removing the line break and add the comma before that value. I have tried the requirement with below code, taken from this answer:

$path = "./NSG_Rules.xlsx"
$rows = Import-Excel $path

# Get the column (property) names
$propNames = $rows[0].psobject.Properties.Name

$rowsToDeleteIndices = [System.Collections.Generic.List[int]] @()

# Process all rows, expanding the cells of merged columns into individual rows.
$rowIndex = 1 # Start with 1, to account for the header row.
foreach ($row in $rows) {
    ++$rowIndex

    foreach ($propName in $propNames) {
        $propValue = $row.$propName

        # Check if the value contains a comma and needs to be expanded
        if ($propValue -match ',') {
            $values = $propValue -split ','

            # Update the current row with the first value
            $row.$propName = $values[0]

            # Create new rows for the remaining values
            for ($i = 1; $i -lt $values.Count; $i++) {
                $newRow = $row.psobject.Copy()
                $newRow.$propName = $values[$i]
                $rows += $newRow
            }
        }
    }
}

# Export the modified values back to the worksheet,
# in place, with all formatting preserved.
# Via -PassThru, obtain a reference to the package (workbook),
# for advanced operations such as row deletion.
$pkg = $rows | Export-Excel $path -PassThru

# Obtain a reference to the first worksheet again
# (the same one implicitly targeted by Import-Excel / Export-Excel).
$ws = $pkg.Workbook.Worksheets[1]

# Now delete the no longer needed rows with merged columns from it.
$i = 0
$rowsToDeleteIndices | ForEach-Object { $ws.DeleteRow($_ + $i--) }

# Close and thereby implicitly save the package.
Close-ExcelPackage $pkg`

my requirement is the excel spread sheet should update by removing the line breaks and add comma to that value. I am trying to get the same format of the input sheet as well as in the output.

Below are the screenshots for reference:

enter image description here

expected output in after code execution: enter image description here

Note: I have raised same type of question earlier but this requirement is quite different compared to other questions and sorry for raising multiple questions.

0

There are 0 best solutions below