API Reference
Cmdlet
Import-OfficeExcel
Imports rows from an Excel workbook as PowerShell objects.
Remarks
Provides a fast PowerShell read command over the OfficeIMO reader pipeline.
Examples
Import worksheet rows and filter pending items.
PS>
$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.
PS>
$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.
PS>
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.
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.
Syntax
Import-OfficeExcel [-AllSheets] [-AsDataReader] [-AsDataTable] [-AsHashtable] [-ByColumn] [-ChunkRows <Int32>] [-CultureName <String>] [-EndColumn <Nullable`1>] [-EndRow <Nullable`1>] [-FormulaMode <CachedValue|FormulaText>] [-NoHeader] [-NumericAsDecimal] -Path <String> [-Range <String>] [-SchemaSampleSize <Int32>] [-SheetIndex <Nullable`1>] [-StartColumn <Nullable`1>] [-StartRow <Nullable`1>] [-WorksheetName <String>] [<CommonParameters>]Parameter set:
PathParameters
- AllSheets SwitchParameter
- Import all worksheets. Each emitted row or column includes WorksheetName.
- AsDataReader SwitchParameter
- Emit a forward-only IDataReader for database bulk-copy workflows.
- AsDataTable SwitchParameter
- Emit a DataTable instead of enumerating row objects.
- AsHashtable SwitchParameter
- Emit rows as hashtables instead of PSCustomObjects.
- ByColumn SwitchParameter
- Emit one object per column with ColumnName, ColumnIndex, and Values instead of row objects.
- ChunkRows Int32
- Worksheet row count requested from each streaming chunk when -AsDataReader is used.
- CultureName String
- Culture used when parsing numbers and dates stored as text.
- EndColumn Nullable`1
- Ending column for an explicit rectangular range.
- EndRow Nullable`1
- Ending row for an explicit rectangular range.
- FormulaMode String
- Formula read mode. CachedValue returns workbook cached results; FormulaText returns formula expressions when present.
- Possible values:
CachedValue,FormulaText - NoHeader SwitchParameter
- Treat all rows as data and generate column names instead of using the first row as headers.
- NumericAsDecimal SwitchParameter
- Prefer decimals instead of doubles for numeric values.
- Path String
- Workbook path to import.
- Range String
- Optional A1 range to read. When omitted, the used range is imported.
- SchemaSampleSize Int32
- Maximum row count inspected when -AsDataReader infers the reader schema.
- SheetIndex Nullable`1
- Zero-based worksheet index to read.
- StartColumn Nullable`1
- Starting column for an explicit rectangular range.
- StartRow Nullable`1
- Starting row for an explicit rectangular range.
- WorksheetName String
- Worksheet name to read; defaults to the first sheet.
Outputs
System.Object
Import-OfficeExcel [-AllowHttp] [-AllSheets] [-AsDataReader] [-AsDataTable] [-AsHashtable] [-ByColumn] [-ChunkRows <Int32>] [-CultureName <String>] [-EndColumn <Nullable`1>] [-EndRow <Nullable`1>] [-FormulaMode <CachedValue|FormulaText>] [-NoHeader] [-NumericAsDecimal] [-Range <String>] [-SchemaSampleSize <Int32>] [-SheetIndex <Nullable`1>] [-StartColumn <Nullable`1>] [-StartRow <Nullable`1>] -Uri <Uri> [-WorksheetName <String>] [<CommonParameters>]Parameter set:
UriParameters
- AllowHttp SwitchParameter
- Allow HTTP workbook downloads in addition to HTTPS.
- AllSheets SwitchParameter
- Import all worksheets. Each emitted row or column includes WorksheetName.
- AsDataReader SwitchParameter
- Emit a forward-only IDataReader for database bulk-copy workflows.
- AsDataTable SwitchParameter
- Emit a DataTable instead of enumerating row objects.
- AsHashtable SwitchParameter
- Emit rows as hashtables instead of PSCustomObjects.
- ByColumn SwitchParameter
- Emit one object per column with ColumnName, ColumnIndex, and Values instead of row objects.
- ChunkRows Int32
- Worksheet row count requested from each streaming chunk when -AsDataReader is used.
- CultureName String
- Culture used when parsing numbers and dates stored as text.
- EndColumn Nullable`1
- Ending column for an explicit rectangular range.
- EndRow Nullable`1
- Ending row for an explicit rectangular range.
- FormulaMode String
- Formula read mode. CachedValue returns workbook cached results; FormulaText returns formula expressions when present.
- Possible values:
CachedValue,FormulaText - NoHeader SwitchParameter
- Treat all rows as data and generate column names instead of using the first row as headers.
- NumericAsDecimal SwitchParameter
- Prefer decimals instead of doubles for numeric values.
- Range String
- Optional A1 range to read. When omitted, the used range is imported.
- SchemaSampleSize Int32
- Maximum row count inspected when -AsDataReader infers the reader schema.
- SheetIndex Nullable`1
- Zero-based worksheet index to read.
- StartColumn Nullable`1
- Starting column for an explicit rectangular range.
- StartRow Nullable`1
- Starting row for an explicit rectangular range.
- Uri Uri
- Remote workbook URI to import.
- WorksheetName String
- Worksheet name to read; defaults to the first sheet.
Outputs
System.Object
Import-OfficeExcel [-AllSheets] [-AsDataReader] [-AsDataTable] [-AsHashtable] [-ByColumn] [-ChunkRows <Int32>] [-CultureName <String>] -Document <ExcelDocument> [-EndColumn <Nullable`1>] [-EndRow <Nullable`1>] [-FormulaMode <CachedValue|FormulaText>] [-NoHeader] [-NumericAsDecimal] [-Range <String>] [-SchemaSampleSize <Int32>] [-SheetIndex <Nullable`1>] [-StartColumn <Nullable`1>] [-StartRow <Nullable`1>] [-WorksheetName <String>] [<CommonParameters>]Parameter set:
DocumentParameters
- AllSheets SwitchParameter
- Import all worksheets. Each emitted row or column includes WorksheetName.
- AsDataReader SwitchParameter
- Emit a forward-only IDataReader for database bulk-copy workflows.
- AsDataTable SwitchParameter
- Emit a DataTable instead of enumerating row objects.
- AsHashtable SwitchParameter
- Emit rows as hashtables instead of PSCustomObjects.
- ByColumn SwitchParameter
- Emit one object per column with ColumnName, ColumnIndex, and Values instead of row objects.
- ChunkRows Int32
- Worksheet row count requested from each streaming chunk when -AsDataReader is used.
- CultureName String
- Culture used when parsing numbers and dates stored as text.
- Document ExcelDocument
- Workbook document to import from.
- EndColumn Nullable`1
- Ending column for an explicit rectangular range.
- EndRow Nullable`1
- Ending row for an explicit rectangular range.
- FormulaMode String
- Formula read mode. CachedValue returns workbook cached results; FormulaText returns formula expressions when present.
- Possible values:
CachedValue,FormulaText - NoHeader SwitchParameter
- Treat all rows as data and generate column names instead of using the first row as headers.
- NumericAsDecimal SwitchParameter
- Prefer decimals instead of doubles for numeric values.
- Range String
- Optional A1 range to read. When omitted, the used range is imported.
- SchemaSampleSize Int32
- Maximum row count inspected when -AsDataReader infers the reader schema.
- SheetIndex Nullable`1
- Zero-based worksheet index to read.
- StartColumn Nullable`1
- Starting column for an explicit rectangular range.
- StartRow Nullable`1
- Starting row for an explicit rectangular range.
- WorksheetName String
- Worksheet name to read; defaults to the first sheet.
Outputs
System.Object