Slow to export

[alanwei1995]

Read and complete the full issue template

Do not randomly delete sections. They are here for a reason.

Do you want to request a feature or report a bug?

• Bug
• Feature
• Question

Did you test against the latest CI build?

• Yes
• No

If you answered No, please test with the latest development build first.

Version of ClosedXML

v 0.101.0

Version of ClosedXML.Report

v 0.2.8

What is the current behavior?

I have an Excel template that I turn into a Stream so the data from a grid can export to the template using ClosedXML.Report. I have an array[18000] with about 18000 rows and 8 columns, all just strings from a datagrid. When I export it using ClosedXML, it takes like 5-10mins before it shows me the "Save To" location popup, and the file size is only like 1.3mb. Whereas when the array is 1000 rows, it only takes a couple seconds. I'm not sure if using the ClosedXML.Report for the template has to do with the performance or not, but it does get stuck after template.Generate();

What is the expected behavior or new feature?

I was using ClosedXML version 0.95.0 and I expected the performance to increase, but its roughly the same.

Is this a regression from the previous version?

See above

Reproducibility

This is an important section. Read it carefully. Failure to do so will cause a 'RTFM' comment.

Without a code sample, it is unlikely that your issue will get attention. Don't be lazy. Do the effort and assist the developers to reproduce your problem. Code samples should be minimal complete and verifiable. Sample spreadsheets should be attached whenever applicable. Remove sensitive information.

Code to reproduce problem:

using ClosedXML.Excel;
using ClosedXML.Report;

   var template = new XLTemplate(StreamTemplate); // this is the Excel Template I created, type Stream
   SomeArray[] data; // 18000+ rows of data from a grid
   template.AddVariable("VariableName", data) // variable is from the Excel template
   template.Generate(); // when I debug and step over here, this is where it takes a long time before the 'File save to location' pops up
   IXLWorkbook workbook = template.Workbook;
   MemoryStream ExcelStream = new();
   workbook.SaveAs(ExcelStream); 
  

• I attached a sample spreadsheet. (You can drag files on to this issue)

Thanks!

[jahav]

Moving to ClosedXML.Report.

Report generates data in a worksheet very slow, inefficient way. It goes cell by cell and checks everything again and again, creates data into temporary sheet and then copies data to expected place (see RangeTemplate.VerticalTable and around).

Hopefully, it will get some some TLS after 0.104, but it this time, it is just a low priority.