Can't create a cell with a multi-line value

[npherson]

Excel allows you to start newline within a cell using "Alt+Enter":
https://support.microsoft.com/en-us/office/start-a-new-line-of-text-inside-a-cell-in-excel-33e41eab-8b5e-4193-93d6-9a06ecf812b3

However, I can't find any way to do that from Export-Excel while editing a cell value. I tried to get creative, but the best I could manage was the newline showing up in the Formula bar but not in the sheet itself. If you click into the formula bar and hit enter, excel fixes whatever is wrong and changes it to a multi-line cell.

Here's what I've tried:

$data = ConvertFrom-Csv @"
Region,State,Units,Price
West,Texas,927,923.71
North,Tennessee has a very long name,466,770.67
East,Florida,520,458.68
East,Maine,828,661.24
West,Virginia,465,053.58
North,Missouri,436,235.67
South,Kansas,214,992.47
North,North Dakota,789,640.72
South,Delaware,712,508.55
"@


$xlfilename = "./testLineSplit.xlsx"
Remove-Item $xlfilename -ErrorAction SilentlyContinue

$xlpkg = $data | Export-Excel $xlfilename -WorksheetName TestWrap1 -PassThru

# Get the North Dakota cell and break it into words 
$ws = $xlpkg.Workbook.Worksheets["TestWrap1"]
$string = $ws.Cells["B9"].Value -split "\s+"


# Try with just the output of the string array...
$ws.Cells["B11"].Value = $string

# Try with lots of different ways to insert newline into the string...
$ws.Cells["B12"].Value = $string[0] + "`r`n" +  $string[1] 
$ws.Cells["B13"].Value = $string[0] +  [char]0x000d + [char]0x000a  +  $string[1] 
$ws.Cells["B14"].Value = $string[0] +  [environment]::NewLine +  $string[1]
$ws.Cells["B15"].Value = @($string[0], $string[1]) -join [environment]::NewLine


# Take the array out of the equation...
$stringA = $string[0]
$stringB = $string[1]
$ws.Cells["B16"].Value = @"
$stringA
$stringB 
"@

# Take the variable out of the equation and just do multiline strings...
$ws.Cells["B17"].Value = @"
North
Dakota
"@

$ws.Cells["B18"].Value = @'
North
Dakota
'@


Close-ExcelPackage $xlpkg -Show

[npherson]

This other issue seemed to be about exactly what I'm trying to do, but then turned into a 'wraptext' issue which is not what I'm looking to accomplish... I want to replicate the "Alt+Enter" behavior.

#425

[npherson]

OMG. I just looked at the cell propertied before and after clicking into the formula bar and hitting enter. Excel is 'fixing' it by turning on wrap text! So it is exactly like that other thread!

After setting the value of each cell, I added this to change the format to include wrap text:
$ws.Cells["B17"].Style.WrapText = $true

And, yes, that seems to be the ticket!

[dfinke]

Glad Excel did the trick 🙂

Check out Set-ExcelRange -Worksheet $ws -Range B2 -WrapText