0

I use the following lines of code

byte[] byteInfo = workbook.SaveToMemory(FileFormat.OpenXMLWorkbookMacroEnabled);
workStream.Write(byteInfo, 0, byteInfo.Length);
workStream.Position = 0;
return workStream;

to download an excel file from the browser with the help of C# and Spreadsheetagear.

It works fine for less records but when I try to download a workbook with huge data (an excel with 50k rows 1k columns and macro enabled) this line 

byte[] byteInfo = workbook.SaveToMemory(FileFormat.OpenXMLWorkbookMacroEnabled);

alone takes nearly 4 - 5 min. Is there any optimised way of doing it such that it takes only 1 or 2 min to download huge file.

Leo
  • 136
  • 2
  • 2
  • 11

2 Answers2

0

try

workbook.SaveToStream(outputstream, SpreadsheetGear.FileFormat.OpenXMLWorkbook);

Streams typically can be faster as they normally save as fast as the ram will take. instead of filling up a page file.

Courtney The coder
  • 147
  • 9
  • I tried using this one, it also took almost the same amount of time. – Leo Jul 22 '18 at 16:33
  • @Leo Its would seem there might not be a lossless way to do this. Xcheque's answer might be what you need – Courtney The coder Jul 22 '18 at 16:35
  • Is there any way of sending the memory stream in rensponse, as and when it's getting created such that the conversation (from workbook to memory stream) is reflected in browser download percentage, instead of converting the whole file into memory stream, and then sending it in response – Leo Jul 22 '18 at 16:48
  • Exactly @Courtney – Leo Jul 22 '18 at 16:52
  • You wont be able to progress bar it with out knowing the length or percent completed of the download. but you could do static ms=new MemoryStream(); static bool SaveComplete=false; Thread th=new Thread(SaveWorkbook); th.IsBackground=true; th.Start(); in saveworkbook. do SaveComplete=false workbook.SaveToStream(ms, SpreadsheetGear.FileFormat.OpenXMLWorkbook); SaveComplete=true; Check on form if it complete. if not display a form saying saving etc – Courtney The coder Jul 22 '18 at 16:59
0

For large workbooks the time taken for SpreadsheetGear processes to run is related to the time taken to open the workbook into memory - rather than the time take to compile the information and download it. You can test this by making a request to open the workbook and send a simple success response without the downloaded byte array. Spreadsheet files with a size greater than 1-2MB start to slow things down and the process can get very slow beyond 5MB.

Options are:

  1. Create an empty spreadsheet and build the content by extracting it from a database and inserting into the spreadsheet for download. This is faster than opening a large spreadsheet file.
  2. Get rid of non-essential content that takes up space and increases the size of the spreadsheet
  3. Break up the spreadsheet into smaller individual files. You can test this comparing the speed to process 10 x 1MB files vs 2 x 5MB files vs 1 x 10MB file
  4. Work with CSV import / export file processes. They are faster, but obviously not as smart as the SpreadsheetGear functionality
  5. Set up a direct download of a stored spreadsheet file, not via SpreadsheetGear. This is faster but obviously requires a static file in store or one that has been created and stored by another process
Xcheque
  • 583
  • 1
  • 5
  • 14