PS 7.1 (or .Net Core?) fails on -TitleBackground Color param of Export-Excel

Welcome Forums General PowerShell Q&A PS 7.1 (or .Net Core?) fails on -TitleBackground Color param of Export-Excel

Viewing 4 reply threads
  • Author
    Posts
    • #229363
      Participant
      Topics: 86
      Replies: 138
      Points: 676
      Rank: Major Contributor

      I have a PS 5.1 script which still works very well to this day.  But when I run it on PS 7.1, this portion of the script:

      #
      # Warning: the first 3 lines of this 4-lint "Export-Excel" cmdlet
      # has the PowerShell back-tick" which may not be visible.
      #
      Export-Excel -path $Newfile -Title $CellA1 -Titlesize 13 `
      -TitleFillPattern Solid -TitleBackgroundColor 10092543 `
      -Autosize -FreezePane 3,2 -FreezeFirstColumn `
      -Numberformat '##,###.00' -ExcludeProperty ID
      </pre></pre>
      fails with the following error message:
      
      <pre>
      Export-Excel: C:\Users\xxt35\Documents\PowerShell\V2P May-19 (0700)\PP13 - Yearly summary.ps1:293:64
      Line |
      293 | … -TitleFillPattern Solid -TitleBackgroundColor 10092543 `
      | ~~~~~~~~
      | Cannot process argument transformation on parameter 'TitleBackgroundColor'. Cannot convert the
      | "10092543" value of type "System.Int32" to type "System.Drawing.Color".
      
      

      The integer 10092543 which follows ‘-TitleBackgroundColor’ is the value / setting for this parameter.  It is supposed to set the color background for a specific cell to light blue (Aqua blue?).

      Would be grateful if anyone knows if there’s a fix to this.  Any advice, tips or workarounds will be highly appreciated.  Many thanks.

       

      
      
      
      
      • This topic was modified 6 days, 23 hours ago by Ramon TAN.
    • #229369
      Participant
      Topics: 3
      Replies: 324
      Points: 1,056
      Helping Hand
      Rank: Community Hero
    • #229387
      Participant
      Topics: 86
      Replies: 138
      Points: 676
      Rank: Major Contributor

      Hello Mr. Maurer,

      Many thanks for your quick response.  Yes, I installed that version of ImportExcel.

       

    • #229408
      Participant
      Topics: 0
      Replies: 11
      Points: 157
      Helping Hand
      Rank: Participant

      Hello Ramon,

      In the latest version of ImportExcel module there this check:

      if($TitleBackgroundColor -is [string]){
      $TitleBackgroundColor=[System.Drawing.Color]::$TitleBackgroundColor
      }
      
      

      So when you will specify color as string it should work.

      Also color code 10092543 (Aqua Blue) is decimal not int, so another option would be to explicitly convert it to decimal and then pass to Export-Excel function.

      https://convertingcolors.com/decimal-color-10092543.html

      Hope that helps.

       

      • This reply was modified 6 days, 20 hours ago by AndySvints. Reason: Code formatting
      • This reply was modified 6 days, 20 hours ago by AndySvints.
    • #229423
      Participant
      Topics: 86
      Replies: 138
      Points: 676
      Rank: Major Contributor

      Thank you very much Mr. AndySvints.  I tried several fixes, e.g.:

      (1)  Export-Excel (details skipped) … -TitleBackgroundColor ‘10092543’

      (2) Export-Excel (details skipped) ,,,, -TitlebackgroundColor $t

      where  [System.Drawing.Color]$t = ‘10092543’

      and a few others miore or less in the same vein (out of desperation), and they all failed.  It seems that even a [string] type is not considered valid by Export-Excel in PS 7.  But in PS 5.1 just

      
      Export-Excel  .... -TitleBackgroundColor 10092543
      
      

      works fine.

       

       

Viewing 4 reply threads
  • You must be logged in to reply to this topic.