-
-
Save RamblingCookieMonster/7f49beeaebb570204581 to your computer and use it in GitHub Desktop.
zPSExcel.Intro.ps1
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# Download PSExcel from https://github.com/RamblingCookieMonster/PSExcel | |
# Unblock the zip | |
# Extract the PSExcel folder to a module path (e.g. $env:USERPROFILE\Documents\WindowsPowerShell\Modules\) | |
# Import the module. | |
Import-Module PSExcel #Alternatively, Import-Module \\Path\To\PSExcel | |
# Get commands in the module | |
Get-Command -Module PSExcel | |
# Get help for a command | |
Get-Help Import-XLSX -Full | |
#Create some demo data | |
$DemoData = 1..10 | Foreach-Object{ | |
$EID = Get-Random -Minimum 1 -Maximum 1000 | |
$Date = (Get-Date).adddays(-$EID) | |
New-Object -TypeName PSObject -Property @{ | |
Name = "jsmith$_" | |
EmployeeID = $EID | |
Date = $Date | |
} | Select Name, EmployeeID, Date | |
} | |
# Export it | |
$DemoData | Export-XLSX -Path C:\Temp\Demo.xlsx | |
# Import it back | |
$Imported = Import-XLSX -Path C:\Temp\Demo.xlsx -Header samaccountname, EID, Date | |
# Open that Excel file... | |
$Excel = New-Excel -Path C:\Temp\Demo.xlsx | |
# Get a workbook | |
$Workbook = $Excel | Get-Workbook | |
# Get a worksheet - can pipe ExcelPackage or Workbook. | |
# Filtering on Name is optional | |
$Excel | Get-Worksheet | |
$WorkSheet = $Workbook | Get-Worksheet -Name Worksheet1 | |
# Freeze the top row | |
$WorkSheet | Set-FreezePane -Row 2 | |
# Save and close! | |
$Excel | Close-Excel -Save | |
# Re-open the file | |
$Excel = New-Excel -Path C:\Temp\Demo.xlsx | |
# Add bold, size 15 formatting to the header | |
$Excel | | |
Get-WorkSheet | | |
Format-Cell -Header -Bold $True -Size 14 | |
# Save and re-open the saved changes | |
$Excel = $Excel | Save-Excel -Passthru | |
# Text was too large! Set it to 11 | |
$Excel | | |
Get-WorkSheet | | |
Format-Cell -Header -Size 11 | |
$Excel | | |
Get-WorkSheet | | |
Format-Cell -StartColumn 1 -EndColumn 1 -Autofit -AutofitMaxWidth 7 -Color DarkRed | |
# Save and close | |
$Excel | Save-Excel -Close | |
# Search a spreadsheet | |
Search-CellValue -Path C:\Temp\Demo.xlsx { $_ -like 'jsmith10' -or $_ -eq 280 } | |
# Add a table, autofit the data. We use force to overwrite our previous demo. | |
$DemoData | Export-XLSX -Path C:\Temp\Demo.xlsx -Table -Autofit -Force | |
# Fun with pivot tables and charts! Props to Doug Finke | |
# Get files in your profile, create a chart breaking down size by file extension | |
Get-ChildItem $env:USERPROFILE -Recurse -File | | |
Export-XLSX -Path C:\Temp\Files.xlsx -PivotRows Extension -PivotValues Length -ChartType Pie |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment