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'"

To use a fast Excel automation, you assign the data matrix to an Excel Range, but ranges in Excel use letters for the column.

Columns start with A to Z, then continues with AA until ZZ and AAA from there all the way to the system limit.

So how to convert the column index to the Excel Column naming?

It is not a base 26 numbering schema: The ones-digit has a value range from 0..25 and all higher order digits have a range from 1..26.

Bijective base 26 on http://en.wikipedia.org/wiki/Hexavigesimal talks about this, but has a broken algorithm. AAA is not 677 but 703 (corrected for one-based numbering). The implementation skips one letter in the higher order digits.

The correct numbering is:

 1=A
  :
 26=Z
 27=AA
  :
 702=ZZ
 703=AAA

  : 

This text file contain all columns up to 20000:ACOF (and this ZIP from 1..AAAAA).

Combined with the fact that Excel indices start from 1 instead of 0, I developed the following solution (implemented in Windows Powershell):

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
}

For completeness: The reverse operation (from column representation to the actual int value):

Function ExcelColumnToInt([string]$A1)
{
    # Author: Jürgen Eidt, Dec 2012
    # Excel column name conversion

    [int]$col = 0
    [char[]]$A1.ToUpperInvariant() | % { $col = 26 * $col + [int]$_ + 1 - [int][char]'A' }

    $col
}