Skip to content

Instantly share code, notes, and snippets.

@daniel0x00
Created January 9, 2017 15:48
Show Gist options
  • Save daniel0x00/e096575c8d65bf997d2bddb194f12e28 to your computer and use it in GitHub Desktop.
Save daniel0x00/e096575c8d65bf997d2bddb194f12e28 to your computer and use it in GitHub Desktop.
PowerShell Excel transposing script
function Invoke-ExcelTransposing {
[CmdletBinding()]
[OutputType([psobject])]
param(
[Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$true)]
[string] $FileName,
[Parameter(Mandatory=$false, ValueFromPipelineByPropertyName=$true)]
[string] $SheetName='Sheet1'
)
begin {
$Excel = New-Object -ComObject Excel.Application
$ReturnObject = New-Object System.Object
}
process {
# Create Excel object
$Workbook = $Excel.Workbooks.Open($FileName)
$Sheet = $Workbook.Worksheets.Item($SheetName)
$Excel.Visible=$false
# Count rows
$RowCount = ($Sheet.UsedRange.Rows).Count
Write-Verbose "Row count: $RowCount"
# Iterate into rows:
for ($x=1; $x -le $RowCount-1; $x++)
{
$key = ($Sheet.Cells.Item($x,1).text).Trim().TrimEnd(':').TrimEnd(',').TrimEnd('.')
$value = $Sheet.Cells.Item($x,2).text.Trim()
# Check if we detect an empty line. In that case, returns the object to the pipeline, then create reset de return object and continue the for.
if ($key.Length -eq 0) {
$ReturnObject
$ReturnObject = $null
$ReturnObject = New-Object System.Object
continue
}
$ReturnObject | Add-Member -Type NoteProperty -Name $key -Value $value
}
}
end { $Excel.quit() }
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment