#region // CN_createXL
    /// <summary>
    /// 1- Creates an excel file from an xltm or xlsm and save it as an xlsm file with an incremented filename if needed.
    /// 2- It opens a specified sheet or creates it if it does not exists, then enter the data provided as rows and columns.
    /// 3- Data will always overwrite the content of the file.
    /// 4- It uses openXML only.  so need to reference these files using nugetpackages:
    ///    DocumentFormat.OpenXml
    ///
    /// 5- Also you need these reference:
    //     using DocumentFormat.OpenXml;
    //     using DocumentFormat.OpenXml.Packaging;
    //     using DocumentFormat.OpenXml.Spreadsheet;
    //     using System;
    //     using System.Collections.Generic;
    //     using System.Data;
    //     using System.IO;
    //     using System.Linq;
    //     using System.Text.RegularExpressions;
    //     using System.Windows.Forms;
    /// </summary>
    /// 
    //namespace NS_createXL
    //{
    public class CN_createXL
    {
        // Output Port names and their order as you wish them to appear in the Dynamo node.
        [Autodesk.DesignScript.Runtime.MultiReturn(new[] { "filePath", "sheetName", "startRow", "startCol", "rc_data" })]
        public static Dictionary<string, object> createXL(string filePath, string sheetName, int startRow, int startCol, List<List<object>> rc_data)
        {
            #region // Check if FilePath Exists if not open filedialog foe user to choose a template file
            if (File.Exists(filePath))
            {
                //file exist
            }
            else
            {
                using (OpenFileDialog openFileDialog = new OpenFileDialog())
                {
                    openFileDialog.InitialDirectory = "C:\\";
                    openFileDialog.Filter = "Excel files (*.xltm)|*.xltm|All files (*.*)|*.*";
                    openFileDialog.FilterIndex = 2;
                    openFileDialog.RestoreDirectory = true;

                    if (openFileDialog.ShowDialog() == DialogResult.OK)
                    {
                        //Get the path of specified file
                        filePath = openFileDialog.FileName;
                    }
                }
            }
            #endregion // Check if FilePath Exists if not open filedialog foe user to choose a template file

            //  Load the file into a byteArry to transfer to MemoryStream Later
            byte[] xltmDoc_byteArray = File.ReadAllBytes(filePath);
            using (MemoryStream xltmDoc_mem = new MemoryStream())
            {
                xltmDoc_mem.Write(xltmDoc_byteArray, 0, (int)xltmDoc_byteArray.Length);

                using (SpreadsheetDocument xltmDoc = SpreadsheetDocument.Open(xltmDoc_mem, true))
                {
                    // Modify the document as necessary.
                    // For this example, we insert a new paragraph at the
                    // beginning of the document.
                    //wordDoc.MainDocumentPart.Document.Body.InsertAt( new Paragraph(new Run(new Text("Newly inserted paragraph."))), 0);
                    xltmDoc.ChangeDocumentType(DocumentFormat.OpenXml.SpreadsheetDocumentType.MacroEnabledWorkbook);

                    #region  // Writing to Excel

                    WriteExcelFile(xltmDoc, sheetName, startRow, startCol, rc_data);
                    #endregion  //  Writing to Excel

                    #region  // Set filepath to unique value

                    filePath = filePath.Replace("xltm", "xlsm");

                    // Check if filePath does not exist and increment name if it does.
                    filePath = AppendFileNumberIfExists(filePath, ".xlsm");

                    #endregion  // Set filepath to unique value

                }  // END using SpreadSheet

                #region  //  Change File Type and Save (XLTM to XLSM)
                // At this point, the memory stream contains the modified document.
                // We could write it back to a SharePoint document library or serve
                // it from a web server.
                // In this example, we serialize back to the file system to verify
                // that the code worked properly.
                using (FileStream fileStream = new FileStream(filePath, System.IO.FileMode.CreateNew))
                {
                    xltmDoc_mem.WriteTo(fileStream);

                }  // END using FileStream
                #endregion  //  Change File Type and Save (XLTM to XLSM)

            }  // END using MemoryStream
            return new Dictionary<string, object> {
                        { "filePath", filePath},
                        { "sheetName", sheetName},
                        { "startRow", startRow},
                        { "startCol", startCol },
                        { "rc_data", rc_data }
                    };

        }  // END of createXL


        #region //  write to excel
        private static void WriteExcelFile(SpreadsheetDocument xltmDoc, string sheetName, int startRow, int startCol, List<List<object>> rc_data)
        {
            using (xltmDoc)
            {
                // Get the SharedStringTablePart. If it does not exist, create a new one.
                SharedStringTablePart shareStringPart;
                if (xltmDoc.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
                {
                    shareStringPart = xltmDoc.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
                }
                else
                {
                    shareStringPart = xltmDoc.WorkbookPart.AddNewPart<SharedStringTablePart>();
                }

                #region  // Find desired worksheet by name or create one if it does not exist
                WorksheetPart worksheetPart = GetWorksheetPartByName(xltmDoc, sheetName);
                if (worksheetPart == null)
                {
                    // Insert a new worksheet.
                    worksheetPart = InsertWorksheet(xltmDoc.WorkbookPart, sheetName);
                }
                #endregion  // Find desired worksheet by name or create one if it does not exist

                int index = 0;
                Cell cell = null;
                #region Loop thru list of Lists
                for (int r = 0; r < rc_data.Count; r++)
                {
                    for (int c = 0; c < rc_data[r].Count; c++)
                    {
                        #region Insert Values in Cells
                        // Insert the text into the SharedStringTablePart.
                        index = CN_createXL.InsertSharedStringItem(rc_data[r][c].ToString(), shareStringPart);

                        // Insert a cell into the worksheet.
                        cell = InsertCellInWorksheet(GetExcelColumnName(c + startCol), Convert.ToUInt32(r + startRow), worksheetPart);

                        // Set the value of cell.
                        cell.CellValue = new CellValue(index.ToString());
                        cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);

                        #endregion Insert Values in Cells
                    }
                }
                #endregion Loop thru list of Lists
                // Save the new worksheet.
                worksheetPart.Worksheet.Save();
            }  // END using xltmDoc
        }
        #endregion //  write to excel




        // Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text 
        // and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
        private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
        {
            // If the part does not contain a SharedStringTable, create one.
            if (shareStringPart.SharedStringTable == null)
            {
                shareStringPart.SharedStringTable = new SharedStringTable();
            }

            int i = 0;

            // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
            foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
            {
                if (item.InnerText == text)
                {
                    return i;
                }

                i++;
            }

            // The text does not exist in the part. Create the SharedStringItem and return its index.
            shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
            shareStringPart.SharedStringTable.Save();

            return i;
        }

        // Given a WorkbookPart, inserts a new worksheet.
        private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart, string sheetName)
        {
            // Add a new worksheet part to the workbook.
            WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            newWorksheetPart.Worksheet = new Worksheet(new SheetData());
            newWorksheetPart.Worksheet.Save();

            Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
            string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);

            // Get a unique ID for the new sheet.
            uint sheetId = 1;
            if (sheets.Elements<Sheet>().Count() > 0)
            {
                sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
            }


            // Append the new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
            sheets.Append(sheet);
            workbookPart.Workbook.Save();

            return newWorksheetPart;
        }

        // Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
        // If the cell already exists, returns it. 
        private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
        {
            Worksheet worksheet = worksheetPart.Worksheet;
            SheetData sheetData = worksheet.GetFirstChild<SheetData>();
            string cellReference = columnName + rowIndex;

            // If the worksheet does not contain a row with the specified row index, insert one.
            Row row;
            if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
            {
                row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
            }
            else
            {
                row = new Row() { RowIndex = rowIndex };
                sheetData.Append(row);
            }

            // If there is not a cell with the specified column name, insert one.  
            if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
            {
                return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
            }
            else
            {
                // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
                Cell refCell = null;
                foreach (Cell cell in row.Elements<Cell>())
                {
                    if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
                    {
                        refCell = cell;
                        break;
                    }
                }

                Cell newCell = new Cell() { CellReference = cellReference };
                row.InsertBefore(newCell, refCell);

                worksheet.Save();
                return newCell;
            }
        }




        private static WorksheetPart GetWorksheetPartByName(SpreadsheetDocument xltmDoc, string sheetName)
        {
            IEnumerable<Sheet> sheets =
               xltmDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().
               Elements<Sheet>().Where(s => s.Name == sheetName);

            if (sheets?.Count() == 0)
            {
                // The specified worksheet does not exist.

                return null;
            }

            string relationshipId = sheets?.First().Id.Value;

            WorksheetPart worksheetPart = (WorksheetPart)xltmDoc.WorkbookPart.GetPartById(relationshipId);

            return worksheetPart;
        }


        public static string GetExcelColumnName(int columnNumber)
        {
            int dividend = columnNumber;
            string columnName = String.Empty;
            int modulo;

            while (dividend > 0)
            {
                modulo = (dividend - 1) % 26;
                columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                dividend = (int)((dividend - modulo) / 26);
            }

            return columnName;
        }


        // <summary>
        /// A function to add an incremented number at the end of a file name if a file already exists. 
        /// </summary>
        /// <param name="file">The file. This should be the complete path.</param>
        /// <param name="ext">This can be empty.</param>
        /// <returns>An incremented file name. </returns>
        private static string AppendFileNumberIfExists(string file, string ext)
        {
            // This had a VB tidbit that helped to get this started. 
            // http://www.codeproject.com/Questions/212217/increment-filename-if-file-exists-using-csharp

            // If the file exists, then do stuff. Otherwise, we just return the original file name.
            if (File.Exists(file))
            {
                string folderPath = Path.GetDirectoryName(file); // The path to the file. No sense in dealing with this unecessarily. 
                string fileName = Path.GetFileNameWithoutExtension(file); // The file name with no extension. 
                string extension = string.Empty; // The file extension. 
                                                 // This lets us pass in an empty string for the file extension if required. i.e. It just makes this function a bit more versatile. 
                if (ext == string.Empty)
                {
                    extension = Path.GetExtension(file);
                }
                else
                {
                    extension = ext;
                }

                // at this point, find out if the fileName ends in a number, then get that number.
                int fileNumber = 0; // This stores the number as a number for us. 
                                    // need a regex here - \(([0-9]+)\)$
                Regex r = new Regex(@"\(([0-9]+)\)$"); // This matches the pattern we are using, i.e. ~(#).ext
                Match m = r.Match(fileName); // We pass in the file name with no extension.
                string addSpace = " "; // We'll add a space when we don't have our pattern in order to pad the pattern.
                if (m.Success)
                {
                    addSpace = string.Empty; // We have the pattern, so we don't add a space - it has already been added. 
                    string s = m.Groups[1].Captures[0].Value; // This is the single capture that we are looking for. Stored as a string.
                                                              // set fileNumber to the new number.
                    fileNumber = int.Parse(s); // Convert the number to an int.
                                               // remove the numbering from the string as we're constructing it again below.
                    fileName = fileName.Replace("(" + s + ")", "");
                }

                // Start looping. 
                do
                {
                    fileNumber += 1; // Increment the file number that we have above. 
                    file = Path.Combine(folderPath, // Combine it all.
                                            string.Format("{0}{3}({1}){2}", // The pattern to combine.
                                                                      fileName,         // The file name with no extension. 
                                                                      fileNumber,       // The file number.
                                                                      extension,        // The file extension.
                                                                      addSpace));       // A space if needed to pad the initial ~(#).ext pattern.
                }
                while (File.Exists(file)); // As long as the file name exists, keep looping. 
            }
            return file;
        }
    }  // END of CN_createXL
       //}  // End of Namespace NS_createXL
    #endregion // CN_createXL