Use a memory stream instead of exporting to a file

[PSonander]

Hi!

I generate a report in Powershell that I want to upload into a Sharepoint site as an Excel file. So far I have been exporting the data to a local Excel file and then imported the file back into a byte array. I have now tried to use the PassThru parameter to get a object and use the Stream property to get the byte array, but I always gets an empty stream.

This is my command to get the object
$excelData = $report | Export-Excel -PassThru

Properties of the $excelData object
Sheet1 : Sheet1
Package : OfficeOpenXml.Packaging.ZipPackage
Encryption : OfficeOpenXml.ExcelEncryption
Workbook : OfficeOpenXml.ExcelWorkbook
DoAdjustDrawings : True
File : C:\Users\ContainerUser\AppData\Local\Temp\temp.xlsx
Stream : System.IO.MemoryStream
Compression : Level6
Compatibility : OfficeOpenXml.Compatibility.CompatibilitySettings

Properties of the $excelData.Stream object
CanRead : True
CanSeek : True
CanWrite : True
Capacity : 0
Length : 0
Position : 0
CanTimeout : False
ReadTimeout :
WriteTimeout :

Have I misunderstood what can be done with the PassThru parameter.

I would like to avoid creating a temporary file as it requires the script to have write access on the harddrive

Thanks
Peter

[scriptingstudio]

At shallow sight Open-ExcelPackage does not utilize useStream property that is why stream property is empty.