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.

[CraigChamberlain]

I would love to be able to open a package from a file stream too.

For example with the PnP.PowerShell I can open a filestream to a doc without having to cache it locally on disk:

$s = Get-PnPFile -Url "sites/Site/Library/FileName.ext" -AsMemoryStream

[dfinke]

@CraigChamberlain yes. Not sure I want to go thru the work to make this happen. Though it may be easy.

Just this moring I worked up how to read a csv from a zip file using a stream and thought, maybe it is time to revisit this with the ImportExcel module.

And yes, it'd be great to read an xlsx from a url too. I have that for csv files.

[CraigChamberlain]

I should really have a shot of understanding this. Its a great tool I use everyday. Shouldn't be for you to do everything

…

On Tue, 12 Nov 2024, 15:27 Doug Finke, ***@***.***> wrote: @CraigChamberlain <https://github.com/CraigChamberlain> yes. Not sure I want to go thru the work to make this happen. Though it may be easy. Just this moring I worked up how to read a csv from a zip file using a stream and thought, maybe it is time to revisit this with the ImportExcel module. And yes, it'd be great to read an xlsx from a url too. I have that for csv files. — Reply to this email directly, view it on GitHub <#1625 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ALRDEQ47YHDGAH3DXF4YIN32AIM7PAVCNFSM6AAAAABMEHZZGGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDINZQHAZTQMJWGQ> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[dfinke]

Give it a try, you can open a PR and we can chat as you go. One challenge is there are not enough unit tests to prove the current implementation works.