none
XLSX create file using streaming writes RRS feed

  • Question

  • Hi,

    I have a specific need where my execution environment is limited by memory restrictions.

    I need to read lots of rows from SQ server, write and XLSX file and then write the file to storage - all within around 512MB of memory allocation.

    My ORM allows me to stream results, and my storage system allows me to save using a stream. My issue is that I cant seem to find a way to write to XLSX using a stream without having the whole XLSX file in memory.

    I would have assume with the advent of things like AWS and Azure that this would be a regular requirement.

    For example Azure function of AWS Lambda - I would like to use the lowest allowable memory allocation e.g. 128MB and be able to read a million rows from SQL server say 1000 rows at a time and build the XLSX file and then starting saving the XLSX stream while reading the next 1000 rows, write the new rows to the XLSX stream etc.

    Just to reiterate - I can do the stream from SQL server, saving to storage using a stream - just the writing to XLSX is not there out the box. Im using EPPLus at the moment as this seems to be most efficient library other than writing raw xml.

    I know this is possible as there is a node package that does this but I want to stick to c#!

    thanks!

    Friday, January 11, 2019 10:27 PM

All replies

  • Hi deanvanrooyen,

    I would suggest that you could refer the following document.

    Streaming support to and from SQL Server simplifies writing applications that stream data, without having to fully load the data into memory, resulting in fewer memory overflow exceptions.


    Streaming support will also enable middle-tier applications to scale better, especially in scenarios where business objects connect to SQL Azure in order to send, retrieve, and manipulate large BLOBs.

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sqlclient-streaming-support

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 14, 2019 8:45 AM
    Moderator