Cmdlet
Edit-OfficeExcelRow
Runs a script block against each editable row of a range and writes the changes back.
Syntax
Edit-OfficeExcelRow
[-Document <ExcelDocument>]
[-NumericAsDecimal]
[-PassThru]
[-Range <String>]
[-ScriptBlock] <ScriptBlock>
[-Sheet <OfficeSheetSelector>]
[<CommonParameters>]#Parameter set:
DocumentEdit-OfficeExcelRow
[-NumericAsDecimal]
[-PassThru]
[-Path] <String[]>
[-Range <String>]
[-ScriptBlock] <ScriptBlock>
[-Sheet <OfficeSheetSelector>]
[<CommonParameters>]#Parameter set:
PathEdit-OfficeExcelRow
-LiteralPath <String[]>
[-NumericAsDecimal]
[-PassThru]
[-Range <String>]
[-ScriptBlock] <ScriptBlock>
[-Sheet <OfficeSheetSelector>]
[<CommonParameters>]#Parameter set:
LiteralPathExamples
Update rows in place
Edit-OfficeExcelRow -Path .\Reports\Releases.xlsx -Sheet Releases -ScriptBlock {
param($row)
if ($row['Status'].Value -eq 'Draft') { $row.Set('Status', 'Ready') }
}Marks every draft row on the Releases sheet as ready and saves the workbook.
Recalculate renewal dates in an open workbook
$book = Open-OfficeExcel -Path .\Contracts\Licences.xlsx
Edit-OfficeExcelRow -Document $book -Sheet Licences -Range A1:F400 -ScriptBlock {
param($row)
if ($row['State'].Value -eq 'Active') {
$row.Set('RenewalDate', $row.Get[datetime]('StartDate').AddYears(1))
}
}
Save-OfficeExcel -Document $book -PassThru | Close-OfficeExcelSets the renewal date of each active licence to one year after its start date in a live workbook, which the script then saves.
Edit rows while building a workbook
$tickets = Import-OfficeCsv -Path .\Data\Tickets.csv
New-OfficeExcel -Path .\Reports\Tickets.xlsx {
ExcelSheet 'Tickets' {
ExcelTable $tickets -Name Tickets
}
Edit-OfficeExcelRow -Sheet Tickets -ScriptBlock {
param($row)
if (-not $row['Owner'].Value) { $row.Set('Owner', 'Unassigned') }
}
}Writes imported rows and then fixes up their status inside the same block, without a -Document variable.
Parameters
- -Document ExcelDocument
- Caller-owned workbook; omission uses the enclosing Excel content block.
- -NumericAsDecimal SwitchParameter
- Prefer decimals instead of doubles for numeric values.
- -PassThru SwitchParameter
- Emits the native result after the operation.
- -Range String
- A1 cell range on the selected worksheet.
- -ScriptBlock ScriptBlock
- Script block to run once per editable row. The row is passed as the first argument.
- -Sheet OfficeSheetSelector
- Worksheet name, one-based position or native worksheet object. Multi-sheet commands also accept wildcard names.
- -Path String[]
- Workbook paths resolved with filesystem wildcard support.
- -LiteralPath String[]
- Literal workbook paths, including FileInfo.FullName and provider PSPath.
Common Parameters
This command supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutVariable, -OutBuffer, -PipelineVariable, -Verbose, -WarningAction, and -WarningVariable.
For more information, see about_CommonParameters.