Borbin the 🐱

Windows Powershell has several cmdlets to output data to different formats. CSV, XML, HTML and JSON for example, but Excel is missing. And this is for a good reason: an Excel document is specific to the problem and not to the data. CSV is aligning the data in text rows, XML/HTML packs data in a tag structure and JSON uses its own text structure to serialize, but no one stores raw data in the Excel format for itself.

If you like to use Excel with your data and you don't need any additional formatting, go with the fast CSV. But if you like to create a native Excel document with a customized data presentation, you can use ConvertTo-XLSX.

The following picture show how ConvertTo-XLSX outputs the data. The data might reside on different sheets or position and might be transformed using the different Excel graphical functions. This code is about fast automation and use only little of what Excel has to offer to format your data.

The fast automation is achieved by using an Excel Range and select all data in one automation step. The header line is still done using individual cell automation to allow customizable appearance like bold or colored entries.

Once all data is collected, the data is transferred to a matrix. Then the data is assigned to an Excel Range using ToExcelColumn to compose the area (A1 notation).

Function ToExcelColumn([int]$col)
{
    # Author: Jürgen Eidt, Dec 2012
    # to Excel column name conversion
    [string]$base26 = ""

    # Convert to modified base26
    while ($col -gt 0)
    {
        [int]$r = ($col - 1) % 26
        $base26 = [char]([int][char]'A' + $r) + $base26
        $col = ($col - $r) / 26
    }

    $base26
}

The ConvertTo-XLSX function accepts pipe input and optional parameters for Title and FilePath to store the Excel file.

Note:

In this example, one specific Excel version is used:

$xl = New-Object -ComObject "Excel.Application.15"

For general purpose, use:

$xl = New-Object -ComObject "Excel.Application"

Function ConvertTo-XLSX
{
    # Author: Jürgen Eidt, Dec 2012
    param(
        [Parameter(Mandatory, ValueFromPipeline)]
        $InputObject,

        [Parameter()]
        [string]$Title,

        [Parameter()]
        [string]$FilePath
    )

    begin
    {
        $objectsToProcess = @()
    }

    process
    {
        $objectsToProcess += $inputObject
    }

    end
    {
        # create the Excel object
        $xl = New-Object -ComObject "Excel.Application.15"

        $wb = $xl.Workbooks.Add()
        $ws = $wb.ActiveSheet
        $cells = $ws.Cells

        [int]$_row = 1
        [int]$_col = 1

        # add optional title
        if($title -ne "")
        {
            $cells.item($_row,$_col) = $Title
            $cells.item($_row,$_col).font.bold = $true
            $cells.item($_row,$_col).font.size = 18

            $_row += 2
        }

        if($objectsToProcess.Count -gt 0)
        {
            $Columns = $_col

            # add column headings in bold
            foreach($property in $objectsToProcess[0].PSobject.Properties)
            {
                ($cell = $cells.item($_row,$_col)).Value2 = $property.Name
                $cell.font.bold = $true
                $_col++
            }

            [int]$Rows = $objectsToProcess.Count
            [int]$Columns = $_col - $Columns

            # create the data matrix that is later copied to the Excel range
            $XLdataMatrix = New-Object 'string[,]' ($Rows),($Columns)

            $z = 0
            # add the data
            foreach($data in $objectsToProcess)
            {
                $s = 0
                # write each property data to the matrix
                foreach($property in $data.PSobject.Properties)
                {
                    if($s -ge $Columns)
                    {
                        break
                    }

                    if($property.Value -ne $null)
                    {
                        $XLdataMatrix[$z,$s] = $property.Value.ToString()
                    }
                    $s++
                }
                $z++
            } 

            $_row++
            $_col = 1

            # create the range argument for the matching data matrix, for example: "A1:K100"
            $rangeArg = "{0}{1}:{2}{3}" -f (ToExcelColumn $_col), $_row, (ToExcelColumn $Columns), ($_row+$Rows-1)

            # copy the data matrix to the range
            $ws.Range($rangeArg).Value2 = $XLdataMatrix
        }

        [void]$ws.Columns.AutoFit()
        [void]$ws.Rows.AutoFit()

        # show the new created Excel sheet
        $xl.Visible = $true

        if($FilePath)
        {
            $wb.SaveAs($FilePath)
        }

        # release Excel object, see http://technet.microsoft.com/en-us/library/ff730962.aspx
        $xl.Quit()
        [void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
        Remove-Variable xl
    }
}

Use the function like all other convertTo functions to convert the data presentation. You might want to fiilter the data first for specific properties to be used.

$path = "C:\Windows\Fonts"

Get-ChildItem -Path $path -File -Recurse | ConvertTo-XLSX -Title "Filelist for '$path'"