C# Excel Interop Not saving and creating tmp files

Question

I have an application that queries data from an oracle source to a datatable then outputs the datatable to Excel. This was working fine until December 2022 when it suddenly started failing to export at inconsistent points. I am getting document not saved exceptions from Excel and it is generating tmp files. The results are completely inconsistent per run and are limited to the files in which I try to place multiple worksheets. I am exporting over 100 worksheets with this app and have been successfully doing so for 4 years. I am not an expert when it comes to this so any assistance is valued.

• I have made sure that I am not recreating multiple instances of Excel by passing the reference to the app to the methods exporting the data (The app is being disposed of successfully after completion to my knowledge as it is no longer in task manager.)
• I am calling .Save on the files that already exist (This is where the Document not Saved exceptions generate.) .SaveAs(path) for new files is working file in the same export.
• Just added detail I pull one datatable from the DB and filter it using dataviews which I then set back to individual tables. If one of these filtered tables fails to export properly and generates a tmp file all the other filtered tables from that original dataset fail to export and do not create tmp files.
• Not sure why this started failing when no changes to the code were made at the time failures began.
• I would like to return to being able to export multiple worksheets to a single workbook but after all my searching I have exhausted what I can find to try.

Here is my code I call for the exports. I create the Application instance outside of here and then use it to open/close workbooks.

using System;
using System.IO;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;
using System.Linq;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
using Microsoft.CSharp;
using Outlook = Microsoft.Office.Interop.Outlook;
using MSWord = Microsoft.Office.Interop.Word;
using MSExcel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Excel;
using DataTable = System.Data.DataTable;
using System.Text.RegularExpressions;
using System.Net.Mail;
using System.Runtime.CompilerServices;
using Microsoft.Office.Interop.Word;
using MailMessage = System.Net.Mail.MailMessage;

public class MSOfficeClassV2
{

    public MSOfficeClassV2()
    {

    }

    #region Excel
    public static void Export2Excel(MSExcel.Application msXLApp,MSExcel.Workbook xlWB, DataTable dtExport, string sFile, string TabName)
    {
        MSExcel.Worksheet xlWS;


        if (File.Exists(sFile))
        {
            xlWS = xlWB.Worksheets.Add(After: xlWB.Worksheets[xlWB.Worksheets.Count]);
        }
        else
        {
            xlWS = xlWB.ActiveSheet;
        }

        xlWS.Activate();

        //Rename worksheet tab
        try
        {
            //Attempt to rename, will error to catch if tab name already exists 
            xlWS.Name = TabName;
        }
        catch
        {
            //If tab name already exists, delete it and rename
            MSExcel.Worksheet xlWS2Delete = xlWB.Worksheets[TabName];
            xlWS2Delete.Delete();
            xlWS.Name = TabName;
        }

        //Get row and column count from export data
        int columnCount = dtExport.Columns.Count;
        int rowCount = dtExport.Rows.Count;

        SetHeaderRowValues(xlWS, dtExport, columnCount);

        SetCellData(msXLApp, xlWS, dtExport, rowCount, columnCount);

        FormatWorksheet(msXLApp, xlWS, rowCount, columnCount);

        Marshal.ReleaseComObject(xlWS);

        //Save file
        if (File.Exists(sFile))
        {
            xlWB.Save();
        }
        else
        {
            xlWB.SaveAs(sFile);
        }
    }

    public static MSExcel.Application NewExcelApp()
    {
        //New application instance of Excel
        MSExcel.Application msExcelApp = new MSExcel.Application();
        msExcelApp.Visible = false;
        msExcelApp.DisplayAlerts = false;

        return msExcelApp;
    }

    public static MSExcel.Workbook newWorkbook(MSExcel.Application msXLApp, string sFile)
    {
        MSExcel.Workbook xlWB;


        if (File.Exists(sFile))
        {
             xlWB = msXLApp.Workbooks.Open(sFile);
        }
        else
        {
            xlWB = msXLApp.Workbooks.Add();
        }

        xlWB.Activate();

        return xlWB;
    }

    public static void closeWorkbook(MSExcel.Workbook xlWB)
    {
        xlWB.Close(0);
        Thread.Sleep(2000);
        Marshal.ReleaseComObject(xlWB);
    }

    public static void closeExcelApplication(MSExcel.Application msXLApp)
    {
        msXLApp.Quit();
        Marshal.ReleaseComObject(msXLApp);

        GC.Collect();
        GC.WaitForPendingFinalizers();
    }

    private static void SetHeaderRowValues(Worksheet xlWS, DataTable dtExport, int columnCount)
    {
        //Create new object array to hold header values
        object[] Header = new object[columnCount];
        //Set each column name to object array value
        for (int h = 0; h < columnCount; h++)
        {
            Header[h] = dtExport.Columns[h].ColumnName;
        }

        //Define header row range
        MSExcel.Range headerRange = xlWS.get_Range((MSExcel.Range)xlWS.Cells[1, 1], (MSExcel.Range)xlWS.Cells[1, columnCount]);
        //Set header row values
        headerRange.Value = Header;

        FormatHeaderRow(Header, headerRange);
        
    }

    private static void FormatHeaderRow(object[] Header, MSExcel.Range headerRange)
    {
        //Format header row
        headerRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
        headerRange.Font.Bold = true;
    }

    private static void SetCellData(MSExcel.Application msExcelApp, Worksheet xlWS, DataTable dtExport, int rowCount, int columnCount)
    {
        //Create object to hold data values
        object[,] cellData = new object[rowCount, columnCount];

        //Loop rows and columns to set aray valures from datatable
        for(int r = 0; r < rowCount; r++)
        {
            for(int c = 0; c < columnCount; c++)
            {
                cellData[r, c] = dtExport.Rows[r][c];
            }
        }

        //Define data cell range
        MSExcel.Range dataRange = xlWS.get_Range((MSExcel.Range)xlWS.Cells[2, 1], (MSExcel.Range)xlWS.Cells[rowCount+1, columnCount]);
        //Set data range values
        dataRange.Value = cellData;
    }

    private static void FormatWorksheet(MSExcel.Application msExcelApp, Worksheet xlWS, int rowCount, int columnCount)
    {
        MSExcel.Range dataRange = xlWS.get_Range((MSExcel.Range)xlWS.Cells[1, 1], (MSExcel.Range)xlWS.Cells[rowCount + 1, columnCount]);
        xlWS.ListObjects.AddEx(XlListObjectSourceType.xlSrcRange, dataRange, Type.Missing,XlYesNoGuess.xlYes,Type.Missing).Name = "myStyle";
        xlWS.ListObjects.get_Item("myStyle").TableStyle = "TableStyleLight16";
        xlWS.Columns.AutoFit();
        xlWS.Rows.AutoFit();
        msExcelApp.ActiveWindow.SplitRow = 1;
        msExcelApp.ActiveWindow.FreezePanes = true;
    }

    #endregion
}

Answer 1

It is just scheduled via task manager to run unattended.

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

If you are building a solution that runs in a server-side context, you should try to use components that have been made safe for unattended execution. Or, you should try to find alternatives that allow at least part of the code to run client-side. If you use an Office application from a server-side solution, the application will lack many of the necessary capabilities to run successfully. Additionally, you will be taking risks with the stability of your overall solution. Read more about that in the Considerations for server-side Automation of Office article.

If you deal with open XML documents you may consider using the Open XML SDK instead, see Welcome to the Open XML SDK 2.5 for Office. Otherwise, consider using any third-party components designed for the server-side execution and where Office applications are not required to be automated.