Cmdlet

Import-OfficeExcel

Aliases: ExcelImport

Module PSWriteOffice

Aliases

ExcelImport

Inputs

Int32 OfficeIMO.Excel.ExcelDocument System.String System.Uri

Source

ImportOfficeExcelCommand.cs:40 Edit on GitHub

Imports rows from an Excel workbook as PowerShell objects.

Syntax

Import-OfficeExcel [-AllSheets] [-AsDataReader] [-AsDataTable] [-AsHashtable] [-ByColumn] [-ChunkRows <Int32>] [-CultureName <String>] [-EndColumn <Int32>] [-EndRow <Int32>] [-FormulaMode <CachedValue|FormulaText>] [-NoHeader] [-NumericAsDecimal] [-Path] <String> [-Range <String>] [-SchemaSampleSize <Int32>] [-SheetIndex <Int32>] [-StartColumn <Int32>] [-StartRow <Int32>] [-WorksheetName <String>] [<CommonParameters>]

Parameter set: Path

Import-OfficeExcel [-AllowHttp] [-AllSheets] [-AsDataReader] [-AsDataTable] [-AsHashtable] [-ByColumn] [-ChunkRows <Int32>] [-CultureName <String>] [-EndColumn <Int32>] [-EndRow <Int32>] [-FormulaMode <CachedValue|FormulaText>] [-NoHeader] [-NumericAsDecimal] [-Range <String>] [-SchemaSampleSize <Int32>] [-SheetIndex <Int32>] [-StartColumn <Int32>] [-StartRow <Int32>] [-Uri] <Uri> [-WorksheetName <String>] [<CommonParameters>]

Parameter set: Uri

Import-OfficeExcel [-AllSheets] [-AsDataReader] [-AsDataTable] [-AsHashtable] [-ByColumn] [-ChunkRows <Int32>] [-CultureName <String>] -Document <ExcelDocument> [-EndColumn <Int32>] [-EndRow <Int32>] [-FormulaMode <CachedValue|FormulaText>] [-NoHeader] [-NumericAsDecimal] [-Range <String>] [-SchemaSampleSize <Int32>] [-SheetIndex <Int32>] [-StartColumn <Int32>] [-StartRow <Int32>] [-WorksheetName <String>] [<CommonParameters>]

Parameter set: Document

Description

Provides a fast PowerShell read command over the OfficeIMO reader pipeline.

Examples

Authored help example

Import worksheet rows and filter pending items.

$rows = Import-OfficeExcel -Path .\Report.xlsx -WorksheetName Data -NumericAsDecimal
$rows |
    Where-Object Status -eq 'Pending' |
    Export-Csv -Path .\PendingRows.csv -NoTypeInformation

Reads the used range on the Data worksheet, emits PSCustomObjects, and filters them in PowerShell.

Import every worksheet and keep the source sheet name.

$rows = Import-OfficeExcel -Path .\Workbook.xlsx -AllSheets
$rows | Group-Object WorksheetName

Reads the used range from each worksheet and adds a WorksheetName property to each emitted row.

Import a worksheet by column.

Import-OfficeExcel -Path .\Workbook.xlsx -WorksheetName Metrics -ByColumn |
                Where-Object ColumnName -eq 'Revenue' |
                Select-Object -ExpandProperty Values

Returns one object per column with the column name, 1-based column index, and the column values as an array.

Parameters

-AllSheets SwitchParameter optional: Import all worksheets. Each emitted row or column includes WorksheetName.
-AsDataReader SwitchParameter optional: Emit a forward-only IDataReader for database bulk-copy workflows.
-AsDataTable SwitchParameter optional: Emit a DataTable instead of enumerating row objects.
-AsHashtable SwitchParameter optional: Emit rows as hashtables instead of PSCustomObjects.
-ByColumn SwitchParameter optional: Emit one object per column with ColumnName, ColumnIndex, and Values instead of row objects.
-ChunkRows Int32 optional: Worksheet row count requested from each streaming chunk when -AsDataReader is used.
-CultureName String optional: Culture used when parsing numbers and dates stored as text.
-EndColumn Int32 optional: Ending column for an explicit rectangular range.
-EndRow Int32 optional: Ending row for an explicit rectangular range.
-FormulaMode OfficeExcelFormulaMode optional: Formula read mode. CachedValue returns workbook cached results; FormulaText returns formula expressions when present.; Possible values: CachedValue, FormulaText
-NoHeader SwitchParameter optional: Treat all rows as data and generate column names instead of using the first row as headers.
-NumericAsDecimal SwitchParameter optional: Prefer decimals instead of doubles for numeric values.
-Path String requiredposition: 0pipeline: True (ByPropertyName)aliases: FilePath, FullName, InputPathsets: Path: Workbook path to import.
-Range String optionalpipeline: True (ByPropertyName): Optional A1 range to read. When omitted, the used range is imported.
-SchemaSampleSize Int32 optional: Maximum row count inspected when -AsDataReader infers the reader schema.
-SheetIndex Int32 optionalpipeline: True (ByPropertyName): Zero-based worksheet index to read.
-StartColumn Int32 optional: Starting column for an explicit rectangular range.
-StartRow Int32 optional: Starting row for an explicit rectangular range.
-WorksheetName String optionalpipeline: True (ByPropertyName)aliases: Sheet: Worksheet name to read; defaults to the first sheet.
-AllowHttp SwitchParameter optionalsets: Uri: Allow HTTP workbook downloads in addition to HTTPS.
-Uri Uri requiredposition: 0pipeline: True (ByPropertyName)aliases: Urlsets: Uri: Remote workbook URI to import.
-Document ExcelDocument requiredpipeline: True (ByValue)sets: Document: Workbook document to import from.

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.