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