0

For those of you proficient in OpenXML, I'm using an Excel file (as resource) as a template for creating auto generated Excel files.

I copy the file to a MemoryStream, and after working with the template, it automatically downloads the file to the user's computer.

The Excel file is huge (around 40 sheets) and has a lot of information, mostly financial stuff. It takes some time to generate, and I would like this to be faster.

I've been thinking if it's possible to use my method to alter the excel sheet asynchronously. Instead of working one sheet at the time, do them all asynchronously. I've never used async so I'm going completely blind into this.

The method to work on the sheet doesn't return anything, it just changes the current used MemoryStream.

I've changed the method definition to 

public async Task UpdateExcelUsingOpenXmlsdkAsync(string xmlStr, string fileName)

I've created a List of Tasks.

List<Task> sheetTasks = new List<Task>();

And I've wrapped my method calls inside 

sheetTasks.Add(Task.Run(() => indice.TreatExcelSheetIndice(ds.Tables["ROW"], header.Tables["RELATORIO"], spreadSheet)));

This runs inside a foreach with a switch inside to treat each Sheet differently

After the foreach I have this 

await Task.WhenAll(sheetTasks);

And then save the Spreadsheet. But it's not working. Does anyone know if this is possible?

Matt Ellen
  • 11,268
  • 4
  • 68
  • 90
Gustavo Jose
  • 45
  • 1
  • 2
  • 12
  • "not working" is pretty vague, could you provide more detail? – ashleedawg Oct 03 '18 at 12:03
  • Most likely the API is not thread safe in that way. Sheets often have references to other sheets, especially in complex financial workbooks. My intuition says that is probably not something you want to do. – Crowcoder Oct 03 '18 at 12:09
  • If the CPU is working at 100% to do one task, you cannot make it go faster by giving it two tasks to do at the same time. Have you measured anything to find out what is actually making the task slow? – Andrew Morton Oct 03 '18 at 12:09
  • Are you using a SAX or DOM approach to write the file? If you're using DOM, consider SAX (without the async). You might find the performance improves. There's an example here - https://stackoverflow.com/questions/32690851/export-big-amount-of-data-from-xlsx-outofmemoryexception/32787219#32787219 – petelids Oct 03 '18 at 14:38
  • There is also OpenXML PowerTools SpreadsheetWriter for writing large sheets https://github.com/OfficeDev/Open-Xml-PowerTools/blob/vNext/OpenXmlPowerToolsExamples/SpreadsheetWriter02/SpreadsheetWriter02.cs – Slai Jul 08 '19 at 09:21

0 Answers0