NPOI C# populate Excel spreadsheet containing a table and XML Map

Question

Using C# and NPOI, I'm trying to import data into an existing Excel spreadsheet that contains a table and an XML Map as below.

When I try to save and close the spreadsheet at end of process I get a NullReferenceException as below, despite the templateWorkbook &/or fsOut objects not being null:

The process runs successfully if I manually remove the XML Map from the template spreadsheet prior to running the code, see sample output below:

However, the XML Map is crucial to the next step of the process, so I must work with the version that contains the XML Map. Why would the XML Map prevent the saving and closing of the file? Am I missing something? Do I have to somehow sync the XML Map with the table in the spreadsheet? Any help would be greatly appreciated. Here's the code:

using NPOI.POIFS.NIO;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.XWPF.UserModel;
using NPOI.XSSF.UserModel.Extensions;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Security.Policy;
using System.Text;
using System.Threading.Tasks;
using NPOI.SS.Util;
using System.Diagnostics;

namespace ExportSQLToExcel
{
    internal static class SQLToXLSXExporter
    {
        private const int HDRCOUNT = 3;

        internal static void ExportDataToXLSX(string? templateFileName, Int64 iRowsPerChunk)
        {
            if (File.Exists(templateFileName) == false)
            {
                throw (new FileNotFoundException(string.Format("File not found: {0}", templateFileName)));
            }

            string connectionString = @"Server=localhost; Database=UK1TST;Trusted_Connection=True;";

            var builder = new SqlConnectionStringBuilder(connectionString);

            Console.WriteLine(String.Format("Server: {0}", builder.DataSource));
            Console.WriteLine(String.Format("Database: {0}", builder.InitialCatalog));

            SqlConnection sqlCon = new SqlConnection(connectionString);
            sqlCon.Open();

            SqlCommand sqlCmd = new SqlCommand(@"SELECT * FROM [GLPOST]", sqlCon);
            SqlDataReader reader = sqlCmd.ExecuteReader();

            string workingFolder = @"D:\MyDocsFromRodNuc\Temp\";
            int iChunkNum = 1;
            string? chunkName = Path.Combine(workingFolder, string.Format("{0}_{1}{2}", Path.GetFileNameWithoutExtension(templateFileName), iChunkNum.ToString().PadLeft(2, '0'), Path.GetExtension(templateFileName)));
            string? templateFileNameTemp = Path.Combine(workingFolder, Path.GetFileName(templateFileName));

            // copy in the empty template
            File.Copy(templateFileName, templateFileNameTemp, true);

            XSSFWorkbook? templateWorkbook = null;

            using (FileStream fs = new FileStream(templateFileNameTemp, FileMode.Open, FileAccess.Read))
            {
                templateWorkbook = new XSSFWorkbook(fs);
            }
            string sheetName = "Gen. Journal Line";
            ISheet sheet = templateWorkbook.GetSheet(sheetName) ?? templateWorkbook.CreateSheet(sheetName);
            XSSFSheet XSheet = (XSSFSheet)sheet;
            var lstTables = XSheet.GetTables();
            XSSFTable? XTable = null;
            
            foreach (var item in lstTables)
            {
                XTable = (XSSFTable)item;
                break;
            }

            if (XTable == null)
            {
                throw (new ApplicationException(string.Format("No table found in spreadsheet: {0}", templateFileNameTemp)));
            }

            var inARefs = XTable.GetCellReferences();
            string sAreaRef = inARefs.FormatAsString();
            Debug.WriteLine(sAreaRef);

            // get the starting row number for the appending of extra rows
            // int iStartRow = XTable.EndRowIndex + 1;
            int iStartRow = XTable.EndRowIndex;

            // START OF: Build list of input field names from Sage DB Query
            string[] inputFieldNames = new string[reader.FieldCount];
            for (int i = 0; i < reader.FieldCount; i++)
                inputFieldNames[i] = reader.GetName(i);
            // END OF: Build list of input field names from Sage DB Query

            object[] inputFieldValues = new object[reader.FieldCount];

            int iRowCount = iStartRow;
            int iInputColNum = 0;
            int iOutputColNum = 0;
            while (reader.Read())
            {
                if (iRowCount == iRowsPerChunk)
                {
                    // save & close Excel Workbook (chunk)
                    using (FileStream fsOut = new FileStream(chunkName, FileMode.Create, FileAccess.Write))
                    {
                        templateWorkbook.Write(fsOut, false);
                    }

                    iChunkNum++;
                    iRowCount = iStartRow;
                    chunkName = Path.Combine(workingFolder, string.Format("{0}_{1}{2}", Path.GetFileNameWithoutExtension(templateFileName), iChunkNum.ToString().PadLeft(2, '0'), Path.GetExtension(templateFileName)));

                    // copy in the empty template (again)
                    File.Copy(templateFileName, templateFileNameTemp, true);

                    using (FileStream fs = new FileStream(templateFileNameTemp, FileMode.Open, FileAccess.Read))
                    {
                        templateWorkbook = new XSSFWorkbook(fs);
                    }
                }

                // read a row from the DB Query
                reader.GetValues(inputFieldValues);

                // Write row
                IRow dataRow = XSheet.CreateRow(iRowCount);

                string curInputFieldName = string.Empty;
                NPOI.SS.UserModel.ICell? cell = null;
                iInputColNum = -1;
                string sItem = string.Empty;

                // populate some fixed key field values.

                cell = dataRow.CreateCell(0); // Journal Template Name
                cell.SetCellType(CellType.String);
                cell.SetCellValue("GENERAL");

                cell = dataRow.CreateCell(1); // Journal Batch Name
                cell.SetCellType(CellType.String);
                cell.SetCellValue("DEFAULT");

                cell = dataRow.CreateCell(2); // Line No.
                cell.SetCellType(CellType.Numeric);
                cell.SetCellValue(iRowCount);

                // for each input column
                foreach (var item in inputFieldValues)
                {
                    iInputColNum++;
                    curInputFieldName = inputFieldNames[iInputColNum];
                    if (string.IsNullOrEmpty(item.ToString()))
                    {
                        sItem = string.Empty;
                    }
                    else 
                    {
                        sItem = item.ToString().TrimEnd();
                    }

                    switch (curInputFieldName)
                    {
                        case "ACCTID": case "JNLDTLDESC":
                            iOutputColNum = GetOutputColumNumber(curInputFieldName);
                            cell = dataRow.CreateCell(iOutputColNum);
                            cell.SetCellType(CellType.String);
                            cell.SetCellValue(sItem);
                            break;
                        case "DOCDATE":
                            iOutputColNum = GetOutputColumNumber(curInputFieldName);
                            cell = dataRow.CreateCell(iOutputColNum);
                            cell.SetCellType(CellType.String);
                            cell.SetCellValue(ProcessSageDate(sItem));
                            break;
                    }
                }
                if (iRowCount != iStartRow)
                {
                    sAreaRef = IncrementAreaRefRow(sAreaRef);
                }
                iRowCount++;
            }

            XTable.SetCellReferences(new NPOI.SS.Util.AreaReference(sAreaRef, NPOI.SS.SpreadsheetVersion.EXCEL2007));
            XTable.UpdateReferences();

            using (FileStream fsOut = new FileStream(chunkName, FileMode.CreateNew, FileAccess.Write))
            {
                templateWorkbook.Write(fsOut);
                fsOut.Close();
                templateWorkbook.Close();
            }
            reader.Close();
            sqlCon.Close();

        }

        private static int GetOutputColumNumber(string curInputFieldName)
        {
            int iColumnNum = -1;
            switch (curInputFieldName.ToUpper())
            {
                case "ACCTID":
                    iColumnNum = 4;
                    break;
                case "JNLDTLDESC":
                    iColumnNum = 8;
                    break;
                case "DOCDATE":
                    iColumnNum = 5;
                    break;
            }
            return (iColumnNum);
        }

        private static string IncrementAreaRefRow(string sAreaRef)
        {
            // e.g. A3:M4 to A3:M5
            // &/OR:
            // e.g. A3:M14 to A3:M15
            // &/OR:
            // e.g. A3:M214 to A3:M215

            string[] parts = sAreaRef.Split(':');
            string endRef = parts[1];
            var sbEndRef = new StringBuilder();
            sbEndRef.Length = 0;
            CharEnumerator chEnum = endRef.GetEnumerator();
            while (chEnum.MoveNext())
            {
                if (char.IsDigit(chEnum.Current))
                {
                    sbEndRef.Append(chEnum.Current);
                }
            }
            // string sRowNum = sAreaRef.Substring(sAreaRef.Length - 1);
            int iRowNum = int.Parse(sbEndRef.ToString());
            iRowNum++;
            var sbAreaRefOut = new StringBuilder();
            sbAreaRefOut.Length = 0;
            sbAreaRefOut.Append(sAreaRef.Substring(0, sAreaRef.Length - sbEndRef.Length));
            sbAreaRefOut.Append(iRowNum.ToString());
            return (sbAreaRefOut.ToString());
        }

        private static string ProcessSageDate(string? inputDate)
        {
            if (string.IsNullOrEmpty(inputDate) ) 
            {
                inputDate = DateTime.Now.ToString("dd/mm/yyyy");
            }

            // (e.g.) 20191014 to 14/10/2019
            StringBuilder lsb = new StringBuilder();
            lsb.Length = 0;
            lsb.Append(inputDate.Substring(inputDate.Length - 2)); // dd
            lsb.Append("/");
            lsb.Append(inputDate.Substring(4,2)); // mm
            lsb.Append("/");
            lsb.Append(inputDate.Substring(0, 4)); // yyyy

            return (lsb.ToString());
        }

    }
}