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.

[CraigChamberlain]

Hi,
I'll fork today and start with making some tests.

[CraigChamberlain]

Hi,

I've been playing with this today. I don't think editing your code base might be the right way to go as streams might be a bit niche and behave slightly differently than file based packages.

Its also easy enough for an interested person to create their own package using the OfficeOpenXml.ExcelPackage API.

$pkg = New-Object -TypeName OfficeOpenXml.ExcelPackage -ArgumentList $Stream

This plays well with all of your module so long as you use the -Passthru switch. Without the -Passthru I think it closes the package and the changes are lost as persistence when opening with a stream is a little strange.

$pkg.save() does not seem to work when opening from a stream. I had better luck with $pkg.saveAs($stream2) or to saveAs to a file, save() seems to do nothing.

You can see in the tests particular __tests__/CloseExcelFromStream.tests.ps1 that I can import from a stream and export to a stream but the original stream seems to not be mutated by the process
CraigChamberlain@561aeeb

Do you think it's worth ironing out all these problems? I think a demo page of opening and saving to a stream explaining to always use -Passthru might be better, feels like a lot of opportunity for bugs otherwise and this is a little advanced anyway for PowerShell, most people wouldn't care or would be using c#, java etc.

Thanks

Craig

[CraigChamberlain]

Hi @PSonander ,

You can make a memory stream from you're byte array and then save it back to another stream, maybe the same one you'd have to test it. You can just replace the $bytes here as you started with a similar object.

$bytes = [System.IO.File]::ReadAllBytes("$xlfileImportColumns")
$stream = [System.IO.MemoryStream]::new($bytes)
$pkg = New-Object -TypeName OfficeOpenXml.ExcelPackage -ArgumentList $Stream 
... do your work here, make sure you always use the -PassThru swtich.
$stream2 = [System.IO.MemoryStream]::new() #maybe you could reuse the first stream.  I suspect that the stream is copied to another behind the scenes by OfficeOpenXml.
$pkg.saveAs($stream2)

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

[dfinke]

I know it can be done, not sure of the effort to integrate and make sure all things still work.