EPPLus C# create and write file to stream in junks

I am using EPPlus (chosen over openxml). I have a very specific need and battling to find a solution (its odd as I would have assumed that this would be a common requirement!): I am loading lots of rows from SQL server, then creating and xlsx report and then streaming the file to S3. Memory is a scarce resource in my case so I have been challenged to stream the data from SQL server, create the excel file (with some basic formatting) and then copy the file up to S3 - all of this using streaming/chunking - so I can have some control over utilization of memory.

I can stream from sql server no problem, and can stream to S3 no problem, the issue is now creating the xlsx file without having to load the whole object into memory. Most examples look like this:

MemoryStream stream = new MemoryStream();

using (ExcelPackage package = new ExcelPackage(stream))

{

    //Here goes the ExcelPackage code etc

    package.Save() or SaveAs etc.

}

The issue with this is that the comment 'Here goes the ExcelPackage code etc' is normally code iterating through a list of data and writing to the file's cells or range of cells.

How can I grab the content of the xlsx file while iterating through the 'list' of data and making sure that epplus inst loading the whole file into memory? On testing a file with 1000000 rows and 10 columns we are around 90MB. I would like to say pull 1000 rows from sql server, write the rows to epplus and then start streaming the ExcelPackage to S3, whilst pulling the next 1000 rows from sql server - wash, rinse, repeat...

I'm also very opposed to hand crafting open xml files :)

thanks.

asked Nov 22 '18 at 5:18

user3086298

127129

2

I doubt that this is even possible. The XLSX format is basically just a ZIP file, with a header that includes the file size (compressed and uncompressed) - these values are not known until the entire file has been written. So, you either need to load the entire document into memory or use a stream that supports random access.

– Bradley Smith
Nov 22 '18 at 5:28

I do not believe this is possible either - due to the file type and due to the way Excel files need to be saved. If memory is tight and low usage is a requirement, I would look at using another file format. The only thing I can think of is outputting multiple files or using ADO.NET to write inserts against the file. Not too sure about the memory consumption there.

– Jason Bayldon
Nov 22 '18 at 6:19

Maybe you can work with a text file and save it as a csv file.

– ikerbera
Nov 22 '18 at 7:38

thanks for the reponses, I have seen this done in a node js project using ExcelJs.stream.xlsx.WorkbookWriter - passing in a stream that is itself itself is passed into an S3 uploader. So Im wondering if passing in a stream to the constructor of EPPLUS that is effectively the same stream passed to our S3 uploader will be the same - I will have to test this if no one else here has the definitive...

– user3086298
Nov 26 '18 at 8:00

add a comment |

I can stream from sql server no problem, and can stream to S3 no problem, the issue is now creating the xlsx file without having to load the whole object into memory. Most examples look like this:

MemoryStream stream = new MemoryStream();

using (ExcelPackage package = new ExcelPackage(stream))

{

    //Here goes the ExcelPackage code etc

    package.Save() or SaveAs etc.

}

The issue with this is that the comment 'Here goes the ExcelPackage code etc' is normally code iterating through a list of data and writing to the file's cells or range of cells.

I'm also very opposed to hand crafting open xml files :)

thanks.

asked Nov 22 '18 at 5:18

user3086298

127129

2

I doubt that this is even possible. The XLSX format is basically just a ZIP file, with a header that includes the file size (compressed and uncompressed) - these values are not known until the entire file has been written. So, you either need to load the entire document into memory or use a stream that supports random access.

– Bradley Smith
Nov 22 '18 at 5:28

I do not believe this is possible either - due to the file type and due to the way Excel files need to be saved. If memory is tight and low usage is a requirement, I would look at using another file format. The only thing I can think of is outputting multiple files or using ADO.NET to write inserts against the file. Not too sure about the memory consumption there.

– Jason Bayldon
Nov 22 '18 at 6:19

Maybe you can work with a text file and save it as a csv file.

– ikerbera
Nov 22 '18 at 7:38

thanks for the reponses, I have seen this done in a node js project using ExcelJs.stream.xlsx.WorkbookWriter - passing in a stream that is itself itself is passed into an S3 uploader. So Im wondering if passing in a stream to the constructor of EPPLUS that is effectively the same stream passed to our S3 uploader will be the same - I will have to test this if no one else here has the definitive...

– user3086298
Nov 26 '18 at 8:00

add a comment |

I can stream from sql server no problem, and can stream to S3 no problem, the issue is now creating the xlsx file without having to load the whole object into memory. Most examples look like this:

MemoryStream stream = new MemoryStream();

using (ExcelPackage package = new ExcelPackage(stream))

{

    //Here goes the ExcelPackage code etc

    package.Save() or SaveAs etc.

}

The issue with this is that the comment 'Here goes the ExcelPackage code etc' is normally code iterating through a list of data and writing to the file's cells or range of cells.

I'm also very opposed to hand crafting open xml files :)

thanks.

asked Nov 22 '18 at 5:18

user3086298

127129

I can stream from sql server no problem, and can stream to S3 no problem, the issue is now creating the xlsx file without having to load the whole object into memory. Most examples look like this:

MemoryStream stream = new MemoryStream();

using (ExcelPackage package = new ExcelPackage(stream))

{

    //Here goes the ExcelPackage code etc

    package.Save() or SaveAs etc.

}

The issue with this is that the comment 'Here goes the ExcelPackage code etc' is normally code iterating through a list of data and writing to the file's cells or range of cells.

I'm also very opposed to hand crafting open xml files :)

thanks.

c# asp.net .net sql-server epplus

asked Nov 22 '18 at 5:18

user3086298

127129

asked Nov 22 '18 at 5:18

user3086298

127129

asked Nov 22 '18 at 5:18

user3086298

127129

asked Nov 22 '18 at 5:18

user3086298

127129

asked Nov 22 '18 at 5:18

user3086298

127129

2

I doubt that this is even possible. The XLSX format is basically just a ZIP file, with a header that includes the file size (compressed and uncompressed) - these values are not known until the entire file has been written. So, you either need to load the entire document into memory or use a stream that supports random access.

– Bradley Smith
Nov 22 '18 at 5:28

I do not believe this is possible either - due to the file type and due to the way Excel files need to be saved. If memory is tight and low usage is a requirement, I would look at using another file format. The only thing I can think of is outputting multiple files or using ADO.NET to write inserts against the file. Not too sure about the memory consumption there.

– Jason Bayldon
Nov 22 '18 at 6:19

Maybe you can work with a text file and save it as a csv file.

– ikerbera
Nov 22 '18 at 7:38

thanks for the reponses, I have seen this done in a node js project using ExcelJs.stream.xlsx.WorkbookWriter - passing in a stream that is itself itself is passed into an S3 uploader. So Im wondering if passing in a stream to the constructor of EPPLUS that is effectively the same stream passed to our S3 uploader will be the same - I will have to test this if no one else here has the definitive...

– user3086298
Nov 26 '18 at 8:00

add a comment |

2

I doubt that this is even possible. The XLSX format is basically just a ZIP file, with a header that includes the file size (compressed and uncompressed) - these values are not known until the entire file has been written. So, you either need to load the entire document into memory or use a stream that supports random access.

– Bradley Smith
Nov 22 '18 at 5:28

I do not believe this is possible either - due to the file type and due to the way Excel files need to be saved. If memory is tight and low usage is a requirement, I would look at using another file format. The only thing I can think of is outputting multiple files or using ADO.NET to write inserts against the file. Not too sure about the memory consumption there.

– Jason Bayldon
Nov 22 '18 at 6:19

Maybe you can work with a text file and save it as a csv file.

– ikerbera
Nov 22 '18 at 7:38

thanks for the reponses, I have seen this done in a node js project using ExcelJs.stream.xlsx.WorkbookWriter - passing in a stream that is itself itself is passed into an S3 uploader. So Im wondering if passing in a stream to the constructor of EPPLUS that is effectively the same stream passed to our S3 uploader will be the same - I will have to test this if no one else here has the definitive...

– user3086298
Nov 26 '18 at 8:00

I doubt that this is even possible. The XLSX format is basically just a ZIP file, with a header that includes the file size (compressed and uncompressed) - these values are not known until the entire file has been written. So, you either need to load the entire document into memory or use a stream that supports random access.

– Bradley Smith
Nov 22 '18 at 5:28

I do not believe this is possible either - due to the file type and due to the way Excel files need to be saved. If memory is tight and low usage is a requirement, I would look at using another file format. The only thing I can think of is outputting multiple files or using ADO.NET to write inserts against the file. Not too sure about the memory consumption there.

– Jason Bayldon
Nov 22 '18 at 6:19

Maybe you can work with a text file and save it as a csv file.

– ikerbera
Nov 22 '18 at 7:38

thanks for the reponses, I have seen this done in a node js project using ExcelJs.stream.xlsx.WorkbookWriter - passing in a stream that is itself itself is passed into an S3 uploader. So Im wondering if passing in a stream to the constructor of EPPLUS that is effectively the same stream passed to our S3 uploader will be the same - I will have to test this if no one else here has the definitive...

– user3086298
Nov 26 '18 at 8:00

add a comment |

0

active

oldest

votes

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53424301%2fepplus-c-sharp-create-and-write-file-to-stream-in-junks%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

0

active

oldest

votes

0

active

oldest

votes

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu