DOCSEARCHbyAlgolia
Submit Docs

ImportExcel PowerShell Module

repository·master·Indexed 25 days ago

https://github.com/dfinke/importexcel

A PowerShell module for automating Excel tasks—including creating tables, pivot tables, and charts—without requiring Microsoft Excel to be installed. It is compatible with Windows, Linux, and Mac. Key functions include Export-Excel for creating .xlsx files, Import-Excel for reading spreadsheets, and specialized cmdlets like Add-ExcelImage, BarChart, ColumnChart, DoChart, LineChart, and PieChart for advanced document generation.

Tokens
38.8K
Snippets
70
Records
235
Agent score
77%

What's inside ImportExcel

  2. Use named ranges and tables as chart data sources

    master

    When using Add-ExcelChart, you can specify data sources using several methods:

    1. Explicit Cell Ranges: Use standard Excel notation like -XRange "A2:B7".
    2. Named Ranges: If you used -AutoNameRange during Export-Excel, you can refer to columns by their names: -XRange "Name" -YRange "VirtualMemorySize".
    3. Excel Tables: If you exported data as a table (using -TableName), use the TableName[ColumnName] syntax: -XRange "Processes[Name]" -YRange "Processes[PM]".
    Add-ExcelChart -Worksheet $Excel.Workbook.Worksheets[1] -Title Stats -ChartType LineMarkersStacked -XRange "Processes[Name]" -YRange "Processes[PM]", "Processes[VirtualMemorySize]" -SeriesHeader 'PM', 'VMSize'

  3. Install the ImportExcel module

    master

    You can install the ImportExcel module directly from the PowerShell Gallery. It works on Windows, Linux, and macOS and does not require Microsoft Excel to be installed on the machine.

    Recommended: Install for the current user

    Use this to install to your personal PowerShell Modules folder (does not require administrator privileges).

    Install for everyone

    Use this to install to the computer-wide PowerShell Modules folder (requires elevated/administrator privileges).

  4. Create dropdown lists for Excel cells

    master

    You can create dropdown lists (List validation) using two methods:

    1. Reference a range of cells (using -Formula)

    Use this when the list of allowed values exists in a worksheet. Important: Use absolute references (e.g., $A$2:$A$1000) to ensure all cells in the target range check against the same list.

    Add-ExcelDataValidationRule -WorkSheet $PlanSheet -Range 'B2:B1001' -ValidationType List -Formula 'values!$a$2:$a$1000' -ShowErrorMessage -ErrorStyle stop -ErrorTitle 'Invalid Data' -ErrorBody 'You must select an item from the list'

    2. Use a hardcoded set of values (using -ValueSet)

    Use this to provide a specific array of strings directly.

    Add-ExcelDataValidationRule -WorkSheet $PlanSheet -Range 'I2:N1001' -ValidationType List -ValueSet @('yes','YES','Yes') -ShowErrorMessage -ErrorStyle stop -ErrorTitle 'Invalid Data' -ErrorBody "Select Yes or leave blank for no"

  5. Merge multiple Excel worksheets into one

    master

    Use Merge-MultipleSheets to combine multiple worksheets from one or more Excel files into a single worksheet. The command identifies differences between sheets (Added, Removed, Changed, or Same) and applies conditional formatting to highlight these changes.

    By default, it uses the Name column as the unique key to pair rows. It also generates _Is columns (which are hidden by default) to track the status of each row.

    dir Server*.xlsx | Merge-MulipleSheets -WorksheetName Services -OutputFile Test2.xlsx -OutputSheetName Services -Show

  6. Compare two Excel worksheets with Compare-WorkSheet

    master
    Use Compare-WorkSheet to identify differences between two Excel worksheets. The command reads the specified sheets, uses a unique 'key' column to match rows (defaulting to 'Name'), and compares the remaining columns. It can output differences to the console, a GridView, or highlight changes directly in the Excel files using background and font colors.

  7. Preserve cell formatting using -AsText

    master
    By default, ConvertFrom-ExcelSheet returns the underlying value of a cell. If a cell has custom formatting (e.g., a specific date format or a leading zero in a number) that you want to preserve in the exported file, use the -AsText parameter followed by the column names. You can use * as a wildcard to apply this to all columns.

  9. Combine multiple Excel sheets using Join-Worksheet

    master

    The Join-Worksheet command merges data from all sheets in an Excel workbook into a single sheet. It supports two primary modes of operation:

    1. Merging identical layouts: If sheets share the same structure, the header from the first sheet is used. By default, a new column is added to each row containing the name of the source sheet. Use -FromLabel <String> to specify the name of this label column.
    2. Combining dissimilar data: If sheets have different structures, use the -NoHeader parameter. When combined with -LabelBlocks, the name of each source sheet is placed as a title above its respective block of data on the combined sheet.

    Common formatting options include -AutoSize, -FreezeTopRow, -BoldTopRow, and -HideSource (to hide the original sheets after merging).

  10. Use scriptblocks for dynamic row values in Set-ExcelColumn

    master

    When using the -Value parameter with a scriptblock, Set-ExcelColumn evaluates the block for every row. This is useful for generating formulas or strings that depend on the current row number.

    Variables and Escaping

    • $row: Represents the current row number.
    • $worksheet: Provides access to the worksheet object.
    • Escaping: To prevent PowerShell from misinterpreting variables that are part of a string (e.g., a column name like G4), use the backtick escape character: "$columnName\4"`.
    • Expression Evaluation: When evaluating expressions within a string inside a scriptblock, wrap them in $() (e.g., $($row + 1)).

  11. Combine dissimilar Excel sheets using LabelBlocks

    master

    To combine sheets that do not share the same columns (dissimilar data), use Join-Worksheet with the -NoHeader and -LabelBlocks parameters. This prevents the command from trying to align columns based on headers and instead treats each sheet as a distinct block of data. The -LabelBlocks parameter places the name of each source sheet as a title above its data block on the summary sheet.

    You can also customize the summary title using -Title <String>, -TitleBold, and -TitleSize <Int32>.