Set-CellComment on null Cells

[lemrvls]

Hi, thank you for all these great tools !

I was playing with Set-CellComment and I came to understand it will skip the $null cells as written on line 58 to 60 :

                If ($Null -eq $c.comment) {
                    [Void]$c.AddComment($Text, "ImportExcel")
                }

Would be great to add a switch like -ApplyTo that would allow DataOnly(Default), NullOnly, All ?

Sorry if I'm not doing things right, I'm new here.

[dfinke]

@lemrvls Thanks for the comment. Could you say a bit more about what you're looking for? Maybe include a screenshot of what it would like in Excel.

[lemrvls]

Taking a closer look at the code it wasn't the lines I was poiting at! $Worksheet.Cells[$Range] (line 55) is juste empty if there is nothing in the cell so $c is null too.

@dfinke , thx, I'm trying to add a comment on cells where there is no data to help the tech team and give them instructions on where to look at to have it filled.

First thing I did was trying to add on comment on every cell of the collumn :

    #CellComment Product_Display_Name, https://github.com/dfinke/ImportExcel/discussions/1386
    if ($tab_all_properties_names.IndexOf("Product_Display_Name") -ge 0) {
        $i = 1
        $c = $tab_all_properties_names.IndexOf("Product_Display_Name") + 1
        $data | % {
            $i ++
            $text = "some instructions"
            #Set-CellComment -Worksheet $ws -Row $i -ColumnNumber $c -Text $text
        }
    }

But I noticed that the one blank cell didn't have any comment added while the others did. In the capture you can see the cell with no value has no comment :

I'd like to only apply comment on this cell but I guess it won't as there is no data in the first place.

[dfinke]

@thkn-hofa Wanted to get your thoughts on this. I haven't looked deeper at the code.

Is the PS not allowing this EPPlus?

[thkn-hofa]

I'm quite sure it's EPPlus that's the problem here. When calling the columnNumber parameter, it creates an [ExcelAddress] object and iterates the cells in that range. EPPlus doesn't play nice with empty cells.

Simplified code:

$Range = [OfficeOpenXml.ExcelAddress]::new($Row, $ColumnNumber, $Row, $ColumnNumber)

Foreach ($c in $Worksheet.Cells[$Range]) {
    [Void]$c.AddComment($Text, "ImportExcel")
}

@lemrvls can you set $VerbosePreference to Continue?

$VerbosePreference = "Continue"

Then run the command and paste the output here. It should output a verbose entry each time it's going to add a comment to a cell. Check if the empty cell is listed in the verbose output. It should also output a message when an error was thrown.

[lemrvls]

Yes it's what I was trying to say earlier here :
$Worksheet.Cells[$Range] (line 55) is just empty if there is nothing in the cell, so $c is null too.

Here is the result of the test after switching on verbose preference to Continue lke you asked :

As on my foreach, the output will only write something for the cells with data and skip those without (l2, l14) :
COMMENTAIRES : I3
COMMENTAIRES : I4
COMMENTAIRES : I5
COMMENTAIRES : I6
COMMENTAIRES : I7
COMMENTAIRES : I8
COMMENTAIRES : I9
COMMENTAIRES : I10
COMMENTAIRES : I11
COMMENTAIRES : I12
COMMENTAIRES : I13
COMMENTAIRES : I15
COMMENTAIRES : I16
COMMENTAIRES : I17
COMMENTAIRES : I18
COMMENTAIRES : I19

No errors tho.

[lemrvls]

@dfinke @thkn-hofa, do you guys need more informations ? Will this ever be adressed ?