Using Awesome Export-Excel Cmdlet (Part 5)

This is part 5 of our mini-series about the awesome and free “ImportExcel” PowerShell module by Doug Finke. Make sure you install the module before you play with this tip:

 
PS> Install-Module -Name ImportExcel -Scope CurrentUser -Force

Recap from Part 4: Displaying Arrays in Excel

In part 4, we looked at misinterpreted data due to arrays found in the input data. As you have seen, you simply need to convert arrays to strings using the -join operator, and Excel will display the arrays correctly, i.e. as a list of comma-separated values.

But what if you’d like to display array elements in individual lines, and use a word wrap?

By default, Excel will show individual lines only in the input field of the selected cell, but not in all cells:

# get some raw data that contains arrays
$rawData = Get-EventLog -LogName System -Newest 10 | 
            Select-Object -Property TimeWritten, ReplacementStrings, InstanceId


# create this Excel file
$Path = "$env:temp\report.xlsx"
# make sure the file is deleted so we have no
# effects from previous data still present in the
# file. This requires that the file is not still
# open and locked in Excel
$exists = Test-Path -Path $Path
if ($exists) { Remove-Item -Path $Path}

$sheetName = 'Testdata'
$rawData |
  ForEach-Object {
    # convert column "ReplacementStrings" from array to string
    $_.ReplacementStrings = $_.ReplacementStrings -join "`r`n"
    # return the changed object
    $_
  } |
  Export-Excel -Path $path -ClearSheet -WorksheetName $sheetName -Show

Viewing the Result: Multi-line Display Behavior in Excel

When you run this code, the array in “ReplacementStrings” would be correctly converted into a multi-line text, but you wouldn’t see this in the sheet. Only when you click an individual cell would you see the multi-lines in the input field.

When you combine the info from our previous parts, you can easily post-process the Excel file, though, and format the cells to “Text” and “WordWrap” like this:

# get some raw data that contains arrays
$rawData = Get-EventLog -LogName System -Newest 10 | 
            Select-Object -Property TimeWritten, ReplacementStrings, InstanceId


# create this Excel file
$Path = "$env:temp\report.xlsx"
# make sure the file is deleted so we have no
# effects from previous data still present in the
# file. This requires that the file is not still
# open and locked in Excel
$exists = Test-Path -Path $Path
if ($exists) { Remove-Item -Path $Path}

$sheetName = 'Testdata'

# save the Excel object model by using -PassThru instead of -Show
$excel = $rawData |
  ForEach-Object {
    # convert column "ReplacementStrings" from array to string
    $_.ReplacementStrings = $_.ReplacementStrings -join "`r`n"
    # return the changed object
    $_
  } |
  Export-Excel -Path $path -ClearSheet -WorksheetName $sheetName -AutoSize -PassThru

#region Post-process the column with the misinterpreted formulas  
# remove the region to repro the original Excel error
$sheet1 = $excel.Workbook.Worksheets[$sheetName]
# reformat cell to number type "TEXT" with WordWrap and AutoSize
Set-Format -Address $sheet1.Cells['B:B'] -NumberFormat 'Text' -WrapText -AutoSize
#endregion

Close-ExcelPackage -ExcelPackage $excel -Show

ReTweet this Tip!