variable with multiple lines into one cell in excel-CodePudding

I have a ps script with multiple "if" statements to output info if a checkbox is checked. I have those IF statements assigned to a variable. If I print out the variable (either on console or to a text file) the data is correct. I need to be able to write the variable into a single cell in an excel spreadsheet. I won't bore you with the entire script, but the important part. Any help is appreciated.

function Build_table {
$global:Content =$(
if ($box1.checked -eq $true) {
write-output "box 1 is checked"
write-output "some other text here" }
if ($box2.checked -eq $true) {
write-output "Box 2 is checked" }
) }  #there are several more "if" statements, but they are all similar

I have the standard lines of code to open excel and select the correct worksheet which all work fine.

$worksheet.Cells.Item(1,2) = "here is your list"
$worksheet.Cells.Item(2,2) = $Content

I have it populating other cells with other variables and that all works great since they are a single line of text. But with $Conent, it does not put anything in the cell.

Sample data that is printed out when I print out $Content:

Box 1 is checked

Box 3 is checked

Box 3 some other text....

There are no spaces between the lines of data (I just don't know why it puts the double spacing in).

I need to be able to have that output inserted into the applicable cell in multiple lines as shown.

CodePudding user response：

$content is an aray of strings. To put that in a cell, you need it to become a single multiline string.

Try:

$worksheet.Cells.Item(2,2) = $Content -join [environment]::NewLine