PowerShell for Excel


PowerShell Library for Excel Automation

This Powershell Module will allow users to interact with MS excel versions based on openxml (MS Office 2007 and later)
Hosted on Codeplex – www.slpslib.codeplex.com

Dependencies:

  •   This module uses the ‘SpreadSheetLight’ .NET library created by Vincent Tan
          www.spreadsheetlight.com

  •   Microsoft OpenXML SDK version 2.0.

  • Module Features:

  •   Create new excel files, modify existing files.
  •   Add – Delete – Copy – Rename – Move worksheets within a workbook
  •   Import Text files ( tab,comma,space,fixed width)
  •   Import a bunch of csv files as separate worksheets into a single excel file.
  •   Create tables and set built-in table styles
  •   Create DefinedNames
  •   Insert PageBreaks,SplitPanes,FreezePanes.
  •   Add Conditional Formatting Iconsets,Databars,ColorScale,& Highlighting
  •   Add DataValidation
  •   Merge/Unmerge cells, set built-in cell styles Eg: Good,Bad,Calculation etc
  •   Support for PasteSpecial: ‘Formatting’,’Formulas’,’Paste’,’Values’,’Transpose’
  •   Sort & Filter data
  •   AutoFit Rows & Columns
  •   Hide Rows & Columns
  •   Group/ungroup rows, columns
  •   Protect/Unprotect worksheets
  •   Set Row height & column width
  •   Set document Metadata such as Author,Category,Keywords etc
  •   Show/hide gridlines from one or all worksheets in a workbook
  •   set Font, Alignment, border, fill, gradient fill etc.


  • Data can come from any powershell cmdlet(eg. Get-Service) or function that outputs objects

    Note: you do not need MS excel or MS Office to be installed on the server or machine that runs this module.

    CMDLET Help

    Add-SLWorkSheet Adds one or more worksheets to an Excel Document.
    Collapse-SLColumn Collapse columns by name or index.
    Collapse-SLRow Collapse rows by index.
    Copy-SLCellStyle Copy a style from a cell and apply it to another cell or a range of cells.
    Copy-SLCellValue Copy a single or a range of cell values.
    Copy-SLWorkSheet Copies or duplicates a worksheet.
    Delete-SLColumn Delete columns by name or index.
    Delete-SLRow Delete rows by index.
    Expand-SLColumn Expand columns by name or index.
    Expand-SLRow Expand rows by index.
    Export-SLData Stores data objects in a datatable which is the input type that excel accepts.
    Get-SLCellStyle Gets the various style settings applied to a cell.
    Get-SLDefinedName Lists defined names contained in an excel document.
    Get-SLDocument Gets an excel Document from the specified path and creates an instance of it for editing.
    Group-SLColumn Group columns by name or index.
    Group-SLRow Group rows by index.
    Hide-SLColumn Hide columns by name or index.
    Hide-SLGridLines Removes Gridlines from a worksheet(s).
    Hide-SLRow Hide rows by index.
    Import-CSVToSLDocument Import one or more CSV files into an excel document.
    Insert-SLColumn Insert columns by name or index.
    Insert-SLPageBreak Insert pagebreaks.
    Insert-SLRow Insert rows by index.
    List-SLWorkSheet List all worksheets contained in a workbook.
    Merge-SLCells Merge cells.
    Move-SLWorkSheet Moves a worksheet.
    New-SLDefinedName Create a Defined Name for a cell reference, range, constant, formula, or table.
    New-SLDocument Creates a new Excel Document or an instance of an excel document that can be piped to other commands in the module.
    Protect-SLWorksheet Protect Worksheet.
    Remove-SLDataValidation Clear all data validation from a worksheet.
    Remove-SLDefinedName Remove defined names contained in an excel document.
    Remove-SLPageBreak Remove pagebreaks.
    Remove-SLWorkSheet Deletes one or more worksheets from an Excel Document.
    Rename-SLWorkSheet Renames a worksheet.
    Save-SLDocument Saves an instance of an excel document.
    Select-SLWorkSheet Select a worksheet from a workbook for editing.
    Set-SLAlignMent Set text alignment settings on a single or a range of cells.
    Set-SLAutoFitColumn Autofit columns by ColumnName or ColumnIndex.
    Set-SLAutoFitRow Autofit rows by RowIndex.
    Set-SLBorder Set Border Style on a single or a range of cells.
    Set-SLBuiltinCellStyle Apply a style based on the built-in cellstyles.
    Set-SLCellFormat Apply string formatting to cells.
    Set-SLCellValue Set a single Cell value on a single or a range of cells.
    Set-SLColumnValue Set column values.
    Set-SLColumnWidth Set column width by name or index.
    Set-SLConditionalFormatColorScale Apply conditional formatting color scale to a range.
    Cells are shaded with gradations of two or three colors that correspond to minimum, midpoint, and maximum thresholds.
    Set-SLConditionalFormatIconSet Apply conditional formatting Iconset on numbers.
    Set-SLConditionalFormattingDataBars Set conditional formatting data bars on a given range of cells.
    Set-SLConditionalFormattingOnText Apply conditional formatting Iconset to text instead of numbers.
    Set-SLDataFilter Set Autofilter on a cellrange.
    Set-SLDataValidation Add Datavalidation.
    Set-SLDocumentMetadata Set-SLDocumentMetadata [-WorkBookInstance] [-Title ] [-Author ] [-Comment ] [-Tags ] []
    Set-SLFill Set Fill settings on a single or a range of cells.
    Set-SLFont Set Font settings on a single or a range of cells.
    Set-SLFreezePane set up Split pane.
    Set-SLRowHeight Set Row height by index.
    Set-SLRowValue Set Row values.
    Set-SLSplitPane set up Split pane.
    Set-SLTableStyle Excel offers to style your data tables via some built-in styles. This cmdlet help the user choose a built-in table style.
    Set-SLVlookup Perform vlookup.Supports lookup from same or on different worksheets.
    Set-SLWorksheetTabColor Sets the tab color of a worksheet.
    Show-SLGridLines Show Gridlines from a worksheet(s).
    Sort-SLData Sort data by row or column.
    UnGroup-SLColumn UnGroup columns by name or index.
    UnGroup-SLRow UnGroup rows by index.
    UnMerge-SLCells UnMerge cells.
    UnProtect-SLWorksheet UnProtect Worksheet.