A way to convert an object in excel format similar to convertto-csv

[scottm19468]

Hello,

When I write automations that send out emails with attachments, I don't like to work with actual files. I tend to take an object and use convertto-csv, then I convert that to base64 to use in a graph API call to send a message with it as an attachment. By doing this, I never need to actually save the file anywhere first. Is there a way to do this with ImportExcel so that I can b64 an xlsx file instead of a CSV? I want to do this in order to add some extra formatting to the file that CSVs don't allow. Thanks!

[dfinke]

Could you say a bit more. You want to b64 the actual xlsx?

[scottm19468]

Hi dfinke! Short answer is yes. Here's what I currently do with Object arrays.

  $Report = $Report | Convertto-CSV -NoTypeInformation | Out-String
  $Bytes = [System.Text.Encoding]::Unicode.GetBytes($Report)
  $B64 = [Convert]::ToBase64String($Bytes)

I can then use that B64 to create an attachment in a json body to send an email via graph. I guess what I'm looking for is whether there is something similar that would replace "convertto-csv" to more of a "convertto-xls" or something where I can apply some formatting like autosize columns or something. I'm thinking I bet I could do it with an Excel com object but that would require Excel to be installed on the local machine. What I'm asking for may not be possible, but I thought I'd throw it out there just in case. Thanks!

[dfinke]

Does this work for you?

$Report=Import-Excel ./sales.xlsx 

$Report = $Report | Convertto-CSV -NoTypeInformation | Out-String
$Bytes = [System.Text.Encoding]::Unicode.GetBytes($Report)
$B64 = [Convert]::ToBase64String($Bytes)

[scottm19468]

Thanks for the reply. While that would probably work, the thing is I'm not working with any files. I think if I could write the output of export-excel to a variable instead of a file, that would work. I don't seem to know enough about working with xlsx files, but it seems that everything depends on working within a file, or at least I don't know enough about the dot net underneath. Due to time constraints I'm probably going to try a different approach, but I'd love to have a conversation with you about this topic if you're interested. Thanks!

[dfinke]

Got it. No, the module only creates the format on disk.