ClosedXML .SaveAs(MemoryStream ms) does not progress when saving two .xlsm files (each about 7MB) at the same time

Question

To reproduce the issue, I created a console project and below is the code in my Program.cs file:

using System;
using System.IO;
using System.Threading;
using ClosedXML.Excel;

namespace TestSavingTwoBigFiles
{
    public class Program
    {
        private static string folderPath = @"C:\FOLDERPATH\";
        private static string fileName1 = folderPath + "FILENAME1.xlsm";
        private static string fileName2 = folderPath + "FILENAME2.xlsm";

        public static void StartThread(string ordinal, string fileName)
        {
            Console.WriteLine("Creating {0} file...", ordinal);
            var wb = new XLWorkbook(fileName, XLEventTracking.Disabled);
            try
            {
                using (wb)
                {
                    using (var ms = new MemoryStream())
                    {
                        Console.WriteLine("Saving {0} file...", ordinal);
                        wb.SaveAs(ms);
                    }
                }
                Console.WriteLine("{0} file saved successfully", ordinal);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                Console.ReadLine();
            }
        }

        public static void Main(string[] args)
        {
            var thread1 = new Thread(() => StartThread("first", fileName1));
            Console.WriteLine("Starting first thread");
            thread1.Start();

            var thread2 = new Thread(() => StartThread("second", fileName2));
            Console.WriteLine("Starting second thread");
            thread2.Start();
        }
    }
}

[Thanks @EmilyLin for the cleaner version]

When I run the above program with two `.xlsm files, one is ~2MB and the other one is ~7MB, the program completes successfully. However, when I run it with two ~7MB files, the program will be stuck at the saving statements and does not progress without throwing an exception. The console will stay as the following image shows and does not change.

One workaround we used was placing a lock on the SaveAs method. Is there a better way?

Thanks!

Answer 1

Placing a lock on the SaveAs method is probably the best way. In the source code for XLWorkbook.cs, both SaveAs functions use FileStream and/or MemoryStream. Neither streams are thread-safe, so your code may not work if run simultaneously with multiple threads, so you should make sure that only one thread can access the MemoryStream at the same time.

Here is an example:

using System;
using System.IO;
using System.Threading;
using ClosedXML.Excel;

namespace Whatever
{
    class Class1
    {
        private static readonly object lockObject = new object();

        public static void StartThread(string ordinal, string fileName)
        {
            Console.WriteLine(string.Format("creating {0} file...", ordinal));
            var wb = new XLWorkbook(fileName, XLEventTracking.Disabled);
            try
            {
                using (wb)
                using (var ms = new MemoryStream())
                {
                    lock (lockObject)
                    {
                        Console.WriteLine(string.Format("saving {0} file...", ordinal));
                        wb.SaveAs(ms);
                    }
                }
                Console.WriteLine(string.Format("{0} file saved successfully", ordinal));
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
        }

        public static void Main(string[] args)
        {
            var thread1 = new Thread(() => StartThread("first", "a.xlsm"));
            thread1.Start();
            var thread2 = new Thread(() => StartThread("second", "b.xlsm"));
            thread2.Start();
        }
    }
}