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!

Etherize

This sounds like an issue with ClosedXML and I would recommend you post an issue on the developers' Codeplex project. – Dervish

@TimRogers Thanks for the response! I've posted the issue on the CloseXML project issues page. issue post – Etherize

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();
        }
    }
}

Mutualize

This should not be the issue. The OP is creating a MemorySteam in each thread and only accessing from that thread. – Infielder