How to speed-up generating PDFs via TMS Flexcel

In my current project we needed to generate lot of PDFs from the same excel template.  For this we use TMS Flexcel library. It is a simple library for generating Excel or PDF files in .NET. It loads a xls template from filesystem, populates it with data and that’s it. Quite easy.

But generating tons of files in a cycle can be a pain, because the template has to be loaded from file system in every iteration. That can be performance problem. The solution is to read the template into MemoryStream and then in each iteration create new XlsFile object from this memory stream.

The data from DotTrace showed that almost 15% of operations was spent on reading from file-system. 15% may not be that much, but it is definitely worth it, when generating files in a loop.

Old unoptimized code

foreach (Order order in orders)
{
    //Load xls template from file system
    XlsFile template = new XlsFile("path/to/template.xlsx");
    
    //Populate template with variables
    report.SetValue("Order_Number", order.Code);
    report.SetValue("Order_CustomerName", order.CustomName);
    //... etc

    //Generate the report (after that the template contains data from this iteration, and has to be re-created)
    report.Run(template);
}

Change to this

using (MemoryStream memoryStream = new MemoryStream())
{
    using (FileStream file = new FileStream("path/to/template.xlsx", FileMode.Open, FileAccess.Read))
    {
        byte[] bytes = new byte[file.Length];
        file.Read(bytes, 0, (int)file.Length);
        memoryStream.Write(bytes, 0, (int)file.Length);
    }

    var reports = new List<NamedReport>();
    foreach (Order order in orders)
    {
        //Don't forget to reset position of memory stream.
        //otherwise all reports will have the same data
        memoryStream.Position = 0;

        //This is the optimalization
        //Open the Flexcel file from MemoryStream and thus avoid reading from disk. 
        var template = new XlsFile();
        template.Open(memoryStream);

        //Create instance of report
        var report = new FlexCelReport();

        //Populate report with variables
        report.SetValue("Order_Number", order.Code);
        report.SetValue("Order_CustomerName", order.CustomName);
        //... etc

        //Generate the report 
        report.Run(template);
    }
} //end of using memoryStream