Skip to main content

Search OfficeIMO

Enter a topic, API type, or PowerShell command.

Documentation Search all OfficeIMO/
Cmdlet

Edit-OfficeExcelRow

Module
PSWriteOffice
Inputs
OfficeIMO.Excel.ExcelDocument System.String[]

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: Document
Edit-OfficeExcelRow
[-NumericAsDecimal]
[-PassThru]
[-Path] <String[]>
[-Range <String>]
[-ScriptBlock] <ScriptBlock>
[-Sheet <OfficeSheetSelector>]
[<CommonParameters>]
#
Parameter set: Path
Edit-OfficeExcelRow
-LiteralPath <String[]>
[-NumericAsDecimal]
[-PassThru]
[-Range <String>]
[-ScriptBlock] <ScriptBlock>
[-Sheet <OfficeSheetSelector>]
[<CommonParameters>]
#
Parameter set: LiteralPath

Examples

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-OfficeExcel

Sets 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 optionalpipeline: True (ByValue)sets: Document
Caller-owned workbook; omission uses the enclosing Excel content block.
-NumericAsDecimal SwitchParameter optional
Prefer decimals instead of doubles for numeric values.
-PassThru SwitchParameter optional
Emits the native result after the operation.
-Range String optional
A1 cell range on the selected worksheet.
-ScriptBlock ScriptBlock requiredposition: 1
Script block to run once per editable row. The row is passed as the first argument.
-Sheet OfficeSheetSelector optional
Worksheet name, one-based position or native worksheet object. Multi-sheet commands also accept wildcard names.
-Path String[] requiredposition: 0pipeline: True (ByValue)sets: Path
Workbook paths resolved with filesystem wildcard support.
-LiteralPath String[] requiredpipeline: True (ByPropertyName)aliases: FullName, PSPathsets: LiteralPath
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.