bullet points in a cell

[Stephanevg]

Hi again,

I was trying to find a way to create a hierarchy of data inside a cell. Ideally I would like to do that with bullet points (kind of with a ul/li way).
I searched the examples but couldn't really find anything that would go in that direction.

Would you have maybe an idea / hint how I could achieve that ?

[dfinke]

hmm. don't know. can that be done manually in XL? if so, we can unzip the xlsx, see the fields/styles set and maybe reverse engineer

[Stephanevg]

Sooo, indeed, I actually couldn't find a place in the GUI to add a bullet point. So I guess this is not possible...:/

[Stephanevg]

So, I am a bit stuborn. It is indeed not possible to have bullet points as we know them (ul/li) but you can actually create an entry with multiple lines, and simply start the 'entry line' with a character that 'looks' just like a bullet point.

$DetailCorrectedContent += '• ' + $li.InnerText + "`r`n"

You need to set the following option on the cell for it to be displayed correctly too:

Unfortunatley, I didn't find a switch on Export-Excel that would start with (contain) the word 'line'. Do you know if it is already possible to do this ?

If not, I had a look at some of the internal XML files, and think I have found a difference between a cell that had the option and another that didn't had it:

When I add the s="1" property, it looks like it does what I was hoping for.

I went step further, and it is possible to set this setting as well on a whole column. What it looks like it is doing, is adding this s=1 attribute on every single cell (row) that the columns contains. (In my case, I added on the column 'F'.

If this is not present at the moment, I could potentially add this as a feature if you want. I just unsure where exactly this should be added and would need a little bit of guidance ^^. Let me know!

ps: There is a manual hack. If you just double click the cell, it will automatically set that attribute, and the "bullet points" will not be listed as 'inline' anymore, but each of them on a seperate line.

[dfinke]

When you Export-Excel you can use -PassThru which returns the object model of the new xlsx.

Put a breakpoint there and you can poke around and set features not directly exposed. Use AI for that and add EPPlus. Someone in the C# world may have solved.

I use this for lots of things.

Once you're happy, close the file.

$xlpkg = $data | Export-Excel ./file.xlsx -PassThru

...

Close-ExcelPackage $xlpkg # this saves it, can also use -Show