WorksheetWriter.cs

/*
 * NanoXLSX is a small .NET library to generate and read XLSX (Microsoft Excel 2007 or newer) files in an easy and native way  
 * Copyright Raphael Stoeckli © 2026
 * This library is licensed under the MIT License.
 * You find a copy of the license in project folder or on: http://opensource.org/licenses/MIT
 */
 
using NanoXLSX.Enums;
using NanoXLSX.Interfaces;
using NanoXLSX.Interfaces.Writer;
using NanoXLSX.Internal.Structures;
using NanoXLSX.Registry;
using NanoXLSX.Registry.Attributes;
using NanoXLSX.Utils;
using NanoXLSX.Utils.Xml;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;
using static NanoXLSX.Enums.Password;
 
namespace NanoXLSX.Internal.Writers
{
    [NanoXlsxPlugIn(PlugInUUID = PlugInUUID.WorksheetWriter)]
    internal class WorksheetWriter : IWorksheetWriter
    {
        private XmlElement worksheet;
        private Worksheet currentWorksheet;
        private IPasswordWriter passwordWriter;
        private ISortedMap sharedStrings;
        private ISharedStringWriter sharedStringWriter;
 
        #region properties
        public Workbook Workbook { get; set; }

        public Worksheet CurrentWorksheet
        {
            get => currentWorksheet;
            set
            {
                currentWorksheet = value;
                IPassword passwordInstance = ((Worksheet)CurrentWorksheet).SheetProtectionPassword;
                this.passwordWriter = PlugInLoader.GetPlugIn<IPasswordWriter>(PlugInUUID.PasswordWriter, new LegacyPasswordWriter());
                this.passwordWriter.Init(PasswordType.WorksheetProtection, passwordInstance.PasswordHash);
            }
        }

        public XmlElement XmlElement => worksheet;
 
        #endregion
        #region constructors

        internal WorksheetWriter()
        {
        }
 
        #endregion
        #region methods
        public void Init(IBaseWriter baseWriter)
        {
            this.Workbook = baseWriter.Workbook;
            this.sharedStringWriter = baseWriter.SharedStringWriter;
            this.sharedStrings = this.sharedStringWriter.SharedStrings;
        }

        public void Execute()
        {
            Worksheet ws = currentWorksheet;
            ws.RecalculateAutoFilter();
            ws.RecalculateColumns();
            worksheet = XmlElement.CreateElement("worksheet");
            worksheet.AddDefaultXmlNameSpace("http://schemas.openxmlformats.org/spreadsheetml/2006/main");
            worksheet.AddNameSpaceAttribute("mc", "xmlns", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            worksheet.AddNameSpaceAttribute("x14ac", "xmlns", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
            worksheet.AddAttribute("mc:Ignorable", "x14ac");
            if (ws.GetLastCellAddress().HasValue && ws.GetFirstCellAddress().HasValue)
            {
                worksheet.AddChildElementWithAttribute("dimension", "ref", new Range(ws.GetFirstCellAddress().Value, ws.GetLastCellAddress().Value).ToString());
            }
            if (ws.SelectedCells.Count > 0 || ws.PaneSplitTopHeight != null || ws.PaneSplitLeftWidth != null || ws.PaneSplitAddress != null ||
               ws.Hidden || ws.ZoomFactor != 100 || ws.ZoomFactors.Count > 1 || !ws.ShowGridLines || !ws.ShowRuler || !ws.ShowRowColumnHeaders || ws.ViewType != Worksheet.SheetViewType.Normal)
            {
                worksheet.AddChildElement(CreateSheetViewElement(ws));
            }
            XmlElement sheetFormatPr = worksheet.AddChildElement("sheetFormatPr");
            if (!HasPaneSplitting(ws))
            {
                // TODO: Find the right calculation to compensate baseColWidth when using pane splitting
                sheetFormatPr.AddAttribute("defaultColWidth", ParserUtils.ToString(ws.DefaultColumnWidth));
            }
            sheetFormatPr.AddAttribute("defaultRowHeight", ParserUtils.ToString(ws.DefaultRowHeight));
            sheetFormatPr.AddAttribute("baseColWidth", ParserUtils.ToString(ws.DefaultColumnWidth));
            sheetFormatPr.AddAttribute("dyDescent", "0.25", "x14ac");
 
            worksheet.AddChildElement(CreateColsElement(ws));
 
            XmlElement sheetData = worksheet.AddChildElement("sheetData");
            sheetData.AddChildElements(CreateRowElements(ws));
 
            worksheet.AddChildElement(CreateMergedCellsElement(ws));
            worksheet.AddChildElement(CreateSheetProtectionElement(ws));
            if (ws.AutoFilterRange != null)
            {
                worksheet.AddChildElementWithAttribute("autoFilter", "ref", ws.AutoFilterRange.Value.ToString());
            }
 
            WriterPlugInHandler.HandleInlineQueuePlugins(ref worksheet, Workbook, PlugInUUID.WorksheetInlineWriter, currentWorksheet.SheetID);
        }

        void IWorksheetWriter.ReleaseXmlElement()
        {
            this.worksheet = null;
        }

        private static XmlElement CreateMergedCellsElement(Worksheet worksheet)
        {
            if (worksheet.MergedCells.Count < 1)
            {
                return null;
            }
            XmlElement mergeCells = XmlElement.CreateElementWithAttribute("mergeCells", "count", ParserUtils.ToString(worksheet.MergedCells.Count));
            foreach (KeyValuePair<string, Range> item in worksheet.MergedCells)
            {
                mergeCells.AddChildElementWithAttribute("mergeCell", "ref", item.Value.ToString());
            }
            return mergeCells;
        }

        private XmlElement CreateSheetViewElement(Worksheet worksheet)
        {
            XmlElement sheetViews = XmlElement.CreateElement("sheetViews");
            XmlElement sheetView = sheetViews.AddChildElementWithAttribute("sheetView", "workbookViewId", "0");
            if (Workbook.SelectedWorksheet == worksheet.SheetID - 1 && !worksheet.Hidden)
            {
                sheetView.AddAttribute("tabSelected", "1");
            }
            if (worksheet.ViewType != Worksheet.SheetViewType.Normal)
            {
                if (worksheet.ViewType == Worksheet.SheetViewType.PageLayout)
                {
                    if (worksheet.ShowRuler)
                    {
                        sheetView.AddAttribute("showRuler", "1");
                    }
                    else
                    {
                        sheetView.AddAttribute("showRuler", "0");
                    }
                    sheetView.AddAttribute("view", "pageLayout");
                }
                else if (worksheet.ViewType == Worksheet.SheetViewType.PageBreakPreview)
                {
                    sheetView.AddAttribute("view", "pageBreakPreview");
                }
            }
            if (!worksheet.ShowGridLines)
            {
                sheetView.AddAttribute("showGridLines", "0");
            }
            if (!worksheet.ShowRowColumnHeaders)
            {
                sheetView.AddAttribute("showRowColHeaders", "0");
            }
            sheetView.AddAttribute("zoomScale", ParserUtils.ToString(worksheet.ZoomFactor));
            foreach (KeyValuePair<Worksheet.SheetViewType, int> scaleFactor in worksheet.ZoomFactors)
            {
                if (scaleFactor.Key == worksheet.ViewType)
                {
                    continue;
                }
                if (scaleFactor.Key == Worksheet.SheetViewType.Normal)
                {
                    sheetView.AddAttribute("zoomScaleNormal", ParserUtils.ToString(scaleFactor.Value));
                }
                else if (scaleFactor.Key == Worksheet.SheetViewType.PageBreakPreview)
                {
                    sheetView.AddAttribute("zoomScaleSheetLayoutView", ParserUtils.ToString(scaleFactor.Value));
                }
                else if (scaleFactor.Key == Worksheet.SheetViewType.PageLayout)
                {
                    sheetView.AddAttribute("zoomScalePageLayoutView", ParserUtils.ToString(scaleFactor.Value));
                }
            }
            sheetView.AddChildElements(CreatePaneElements(worksheet));
            if (worksheet.SelectedCells.Count > 0)
            {
                XmlElement selection = sheetView.AddChildElement("selection");
                selection.AddAttribute("activeCell", worksheet.SelectedCells[0].StartAddress.ToString());
                StringBuilder sb = new StringBuilder(worksheet.SelectedCells.Count * 4);
                for (int i = 0; i < worksheet.SelectedCells.Count; i++)
                {
                    sb.Append(worksheet.SelectedCells[i].ToString());
                    if (i < worksheet.SelectedCells.Count - 1)
                    {
                        sb.Append(' ');
                    }
                }
                selection.AddAttribute("sqref", sb.ToString());
            }
            return sheetViews;
        }

        private static bool HasPaneSplitting(Worksheet worksheet)
        {
            if (worksheet.PaneSplitLeftWidth == null && worksheet.PaneSplitTopHeight == null && worksheet.PaneSplitAddress == null)
            {
                return false;
            }
            return true;
        }

        private XmlElement CreateSheetProtectionElement(Worksheet worksheet)
        {
            if (!worksheet.UseSheetProtection)
            {
                return null;
            }
            Dictionary<Worksheet.SheetProtectionValue, int> actualLockingValues = new Dictionary<Worksheet.SheetProtectionValue, int>();
            if (!worksheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.Objects))
            {
                actualLockingValues.Add(Worksheet.SheetProtectionValue.Objects, 1);
            }
            if (!worksheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.Scenarios))
            {
                actualLockingValues.Add(Worksheet.SheetProtectionValue.Scenarios, 1);
            }
            bool allowSelectLocked = worksheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.SelectLockedCells);
            bool allowSelectUnlocked = worksheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.SelectUnlockedCells);
            if (allowSelectLocked && !allowSelectUnlocked)
            {
                // This shouldn't happen in Excel's UI, but handle it by allowing both
                allowSelectUnlocked = true;
            }
            if (!allowSelectLocked)
            {
                actualLockingValues.Add(Worksheet.SheetProtectionValue.SelectLockedCells, 1);
            }
            if (!allowSelectUnlocked)
            {
                actualLockingValues.Add(Worksheet.SheetProtectionValue.SelectUnlockedCells, 1);
            }
            // Explicit permissions (set to 0 when allowed)
            if (worksheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.FormatCells))
            {
                actualLockingValues.Add(Worksheet.SheetProtectionValue.FormatCells, 0);
            }
            if (worksheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.FormatColumns))
            {
                actualLockingValues.Add(Worksheet.SheetProtectionValue.FormatColumns, 0);
            }
            if (worksheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.FormatRows))
            {
                actualLockingValues.Add(Worksheet.SheetProtectionValue.FormatRows, 0);
            }
            if (worksheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.InsertColumns))
            {
                actualLockingValues.Add(Worksheet.SheetProtectionValue.InsertColumns, 0);
            }
            if (worksheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.InsertRows))
            {
                actualLockingValues.Add(Worksheet.SheetProtectionValue.InsertRows, 0);
            }
            if (worksheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.InsertHyperlinks))
            {
                actualLockingValues.Add(Worksheet.SheetProtectionValue.InsertHyperlinks, 0);
            }
            if (worksheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.DeleteColumns))
            {
                actualLockingValues.Add(Worksheet.SheetProtectionValue.DeleteColumns, 0);
            }
            if (worksheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.DeleteRows))
            {
                actualLockingValues.Add(Worksheet.SheetProtectionValue.DeleteRows, 0);
            }
            if (worksheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.Sort))
            {
                actualLockingValues.Add(Worksheet.SheetProtectionValue.Sort, 0);
            }
            if (worksheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.AutoFilter))
            {
                actualLockingValues.Add(Worksheet.SheetProtectionValue.AutoFilter, 0);
            }
            if (worksheet.SheetProtectionValues.Contains(Worksheet.SheetProtectionValue.PivotTables))
            {
                actualLockingValues.Add(Worksheet.SheetProtectionValue.PivotTables, 0);
            }
            XmlElement sheetProtection = XmlElement.CreateElement("sheetProtection");
            string temp;
            foreach (KeyValuePair<Worksheet.SheetProtectionValue, int> item in actualLockingValues)
            {
                temp = Worksheet.GetSheetProtectionName(item.Key); // Note! If the enum names differs from the OOXML definitions, this method will cause invalid OOXML entries
                //temp = Enum.GetName(typeof(Worksheet.SheetProtectionValue), item.Key); 
                sheetProtection.AddAttribute(temp, ParserUtils.ToString(item.Value));
            }
            if (passwordWriter.PasswordIsSet())
            {
                sheetProtection.AddAttributes(passwordWriter.GetAttributes());
            }
            sheetProtection.AddAttribute("sheet", "1");
            return sheetProtection;
        }

        private static List<DynamicRow> GetSortedSheetData(Worksheet worksheet)
        {
            List<Cell> temp = new List<Cell>(worksheet.CellValues);
            temp.Sort();
            DynamicRow row = new DynamicRow(); ;
            Dictionary<int, DynamicRow> rows = new Dictionary<int, DynamicRow>();
            int rowNumber;
            if (temp.Count > 0)
            {
                rowNumber = temp[0].RowNumber;
                row.RowNumber = rowNumber;
                foreach (Cell cell in temp)
                {
                    if (cell.RowNumber != rowNumber)
                    {
                        rows.Add(rowNumber, row);
                        row = new DynamicRow
                        {
                            RowNumber = cell.RowNumber
                        };
                        rowNumber = cell.RowNumber;
                    }
                    row.CellDefinitions.Add(cell);
                }
                if (row.CellDefinitions.Count > 0)
                {
                    rows.Add(rowNumber, row);
                }
            }
            foreach (KeyValuePair<int, float> rowHeight in worksheet.RowHeights)
            {
                if (!rows.ContainsKey(rowHeight.Key))
                {
                    row = new DynamicRow
                    {
                        RowNumber = rowHeight.Key
                    };
                    rows.Add(rowHeight.Key, row);
                }
            }
            foreach (KeyValuePair<int, bool> hiddenRow in worksheet.HiddenRows)
            {
                if (!rows.ContainsKey(hiddenRow.Key))
                {
                    row = new DynamicRow
                    {
                        RowNumber = hiddenRow.Key
                    };
                    rows.Add(hiddenRow.Key, row);
                }
            }
            List<DynamicRow> output = rows.Values.ToList();
            output.Sort((r1, r2) => r1.RowNumber.CompareTo(r2.RowNumber)); // Lambda sort
            return output;
        }

        private static List<XmlElement> CreatePaneElements(Worksheet worksheet)
        {
            if (!HasPaneSplitting(worksheet))
            {
                return null;
            }
            List<XmlElement> elements = new List<XmlElement>(2);
            XmlElement pane = XmlElement.CreateElement("pane");
            bool applyXSplit = false;
            bool applyYSplit = false;
            if (worksheet.PaneSplitAddress != null)
            {
                bool freeze = worksheet.FreezeSplitPanes != null && worksheet.FreezeSplitPanes.Value;
                int xSplit = worksheet.PaneSplitAddress.Value.Column;
                int ySplit = worksheet.PaneSplitAddress.Value.Row;
                if (xSplit > 0)
                {
                    if (freeze)
                    {
                        pane.AddAttribute("xSplit", ParserUtils.ToString(xSplit));
                    }
                    else
                    {
                        pane.AddAttribute("xSplit", ParserUtils.ToString(CalculatePaneWidth(worksheet, xSplit)));
                    }
                    applyXSplit = true;
                }
                if (ySplit > 0)
                {
                    if (freeze)
                    {
                        pane.AddAttribute("ySplit", ParserUtils.ToString(ySplit));
                    }
                    else
                    {
                        pane.AddAttribute("ySplit", ParserUtils.ToString(CalculatePaneHeight(worksheet, ySplit)));
                    }
                    applyYSplit = true;
                }
                if (freeze && applyXSplit && applyYSplit)
                {
                    pane.AddAttribute("state", "frozenSplit");
                }
                else if (freeze)
                {
                    pane.AddAttribute("state", "frozen");
                }
            }
            else
            {
                if (worksheet.PaneSplitLeftWidth != null)
                {
                    pane.AddAttribute("xSplit", ParserUtils.ToString(DataUtils.GetInternalPaneSplitWidth(worksheet.PaneSplitLeftWidth.Value)));
                    applyXSplit = true;
                }
                if (worksheet.PaneSplitTopHeight != null)
                {
                    pane.AddAttribute("ySplit", ParserUtils.ToString(DataUtils.GetInternalPaneSplitHeight(worksheet.PaneSplitTopHeight.Value)));
                    applyYSplit = true;
                }
            }
            if ((applyXSplit || applyYSplit) && worksheet.ActivePane != null)
            {
                switch (worksheet.ActivePane.Value)
                {
                    case Worksheet.WorksheetPane.BottomLeft:
                        pane.AddAttribute("activePane", "bottomLeft");
                        break;
                    case Worksheet.WorksheetPane.BottomRight:
                        pane.AddAttribute("activePane", "bottomRight");
                        break;
                    case Worksheet.WorksheetPane.TopLeft:
                        pane.AddAttribute("activePane", "topLeft");
                        break;
                    case Worksheet.WorksheetPane.TopRight:
                        pane.AddAttribute("activePane", "topRight");
                        break;
                }
            }
            string topLeftCell = worksheet.PaneSplitTopLeftCell.Value.GetAddress();
            pane.AddAttribute("topLeftCell", topLeftCell);
            elements.Add(pane);
            if (applyXSplit && !applyYSplit)
            {
                XmlElement selection = XmlElement.CreateElement("selection");
                selection.AddAttribute("pane", "topRight");
                selection.AddAttribute("activeCell", topLeftCell);
                selection.AddAttribute("sqref", topLeftCell);
                elements.Add(selection);
            }
            else if (applyYSplit && !applyXSplit)
            {
                XmlElement selection = XmlElement.CreateElement("selection");
                selection.AddAttribute("pane", "bottomLeft");
                selection.AddAttribute("activeCell", topLeftCell);
                selection.AddAttribute("sqref", topLeftCell);
                elements.Add(selection);
            }
            else if (applyYSplit && applyXSplit)
            {
                XmlElement selection = XmlElement.CreateElement("selection");
                selection.AddAttribute("activeCell", topLeftCell);
                selection.AddAttribute("sqref", topLeftCell);
                elements.Add(selection);
            }
            return elements;
        }

        private static float CalculatePaneHeight(Worksheet worksheet, int numberOfRows)
        {
            float height = 0;
            for (int i = 0; i < numberOfRows; i++)
            {
                if (worksheet.RowHeights.ContainsKey(i))
                {
                    height += DataUtils.GetInternalRowHeight(worksheet.RowHeights[i]);
                }
                else
                {
                    height += DataUtils.GetInternalRowHeight(Worksheet.DefaultWorksheetRowHeight);
                }
            }
            return DataUtils.GetInternalPaneSplitHeight(height);
        }

        private static float CalculatePaneWidth(Worksheet worksheet, int numberOfColumns)
        {
            float width = 0;
            for (int i = 0; i < numberOfColumns; i++)
            {
                if (worksheet.Columns.ContainsKey(i))
                {
                    width += DataUtils.GetInternalColumnWidth(worksheet.Columns[i].Width);
                }
                else
                {
                    width += DataUtils.GetInternalColumnWidth(Worksheet.DefaultWorksheetColumnWidth);
                }
            }
            // Add padding of 75 per column
            return DataUtils.GetInternalPaneSplitWidth(width) + ((numberOfColumns - 1) * 0f);
        }

        private static XmlElement CreateColsElement(Worksheet worksheet)
        {
            XmlElement cols = null;
            if (worksheet.Columns.Count == 0)
            {
                return cols;
            }
            foreach (KeyValuePair<int, Column> column in worksheet.Columns)
            {
                if (Comparators.CompareDimensions(column.Value.Width, worksheet.DefaultColumnWidth) == 0 && !column.Value.IsHidden && column.Value.DefaultColumnStyle == null)
                {
                    continue;
                }
                if (cols == null)
                {
                    cols = XmlElement.CreateElement("cols");
                }
                XmlElement col = cols.AddChildElement("col");
                col.AddAttribute("width", ParserUtils.ToString(DataUtils.GetInternalColumnWidth(column.Value.Width)));
                string minMax = ParserUtils.ToString(column.Key + 1); // Add 1 for Address
                col.AddAttribute("max", minMax);
                col.AddAttribute("min", minMax);
                col.AddAttribute("customWidth", "1");
                if (worksheet.Columns.ContainsKey(column.Key) && worksheet.Columns[column.Key].IsHidden)
                {
                    col.AddAttribute("hidden", "1");
                }
                if (column.Value.DefaultColumnStyle != null)
                {
                    col.AddAttribute("style", ParserUtils.ToString(column.Value.DefaultColumnStyle.InternalID.Value));
                }
            }
            return cols;
        }

        private XmlElement CreateRowElement(DynamicRow dynamicRow, Worksheet worksheet)
        {
            int rowNumber = dynamicRow.RowNumber;
            XmlElement row = XmlElement.CreateElementWithAttribute("row", "r", ParserUtils.ToString(rowNumber + 1));
            if (worksheet.RowHeights.ContainsKey(rowNumber) && Comparators.CompareDimensions(worksheet.RowHeights[rowNumber], worksheet.DefaultRowHeight) != 0)
            {
                row.AddAttribute("dyDescent", "0.25", "x14ac");
                row.AddAttribute("customHeight", "1");
                row.AddAttribute("ht", ParserUtils.ToString(DataUtils.GetInternalRowHeight(worksheet.RowHeights[rowNumber])));
            }
            if (worksheet.HiddenRows.ContainsKey(rowNumber) && worksheet.HiddenRows[rowNumber])
            {
                row.AddAttribute("hidden", "1");
            }
 
            foreach (Cell item in dynamicRow.CellDefinitions)
            {
                string cellValue = null;
                XmlAttribute? cellStyle = null;
                XmlAttribute? cellType = null;
                XmlElement formulaElement = null;
 
                if (item.CellStyle != null)
                {
                    cellStyle = XmlAttribute.CreateAttribute("s", ParserUtils.ToString(item.CellStyle.InternalID.Value));
                }
                // Data types
                if (item.DataType == Cell.CellType.Bool)
                {
                    cellType = XmlAttribute.CreateAttribute("t", "b");
                    cellValue = (bool)item.Value ? "1" : "0";
                }
                // Number casting
                else if (item.DataType == Cell.CellType.Number)
                {
                    cellType = XmlAttribute.CreateAttribute("t", "n");
                    cellValue = CastNumber(item.Value); // Not a number should not be possible
                }
                // Date parsing (style should be on cell)
                else if (item.DataType == Cell.CellType.Date)
                {
                    DateTime date = (DateTime)item.Value;
                    cellValue = DataUtils.GetOADateTimeString(date);
                }
                // Time parsing (style should be on cell)
                else if (item.DataType == Cell.CellType.Time)
                {
                    TimeSpan time = (TimeSpan)item.Value;
                    cellValue = DataUtils.GetOATimeString(time);
                }
                else if (item.DataType == Cell.CellType.String)
                {
                    cellType = XmlAttribute.CreateAttribute("t", "s");
                    if (item.Value is IFormattableText text)
                    {
                        cellValue = sharedStrings.Add(text, ParserUtils.ToString(sharedStrings.Count));
                    }
                    else
                    {
                        cellValue = sharedStrings.Add(new PlainText(item.Value.ToString()), ParserUtils.ToString(sharedStrings.Count));
                    }
                    this.sharedStringWriter.SharedStringsTotalCount++;
                }
                else if (item.DataType == Cell.CellType.Formula)
                {
                    formulaElement = CreateFormulaElement(item, out cellValue, out cellType); // If null: formula omitted -> array referenced cell
                }
                else if (item.DataType == Cell.CellType.Error)
                {
                    cellValue = ResolveErrorValue(item.Value);
                    cellType = XmlAttribute.CreateAttribute("t", "e");
                }
                // else Cell.CellType.Empty or Cell.CellType.Empty lead to empty cell (no "t" attribute & <v> element)
 
                XmlElement c = row.AddChildElementWithAttribute("c", "r", item.CellAddress);
                c.AddAttribute(cellType);
                c.AddAttribute(cellStyle);
                if (formulaElement != null)
                {
                    c.AddChildElement(formulaElement);
                }
                if (!string.IsNullOrEmpty(cellValue))
                {
                    c.AddChildElementWithValue("v", XmlUtils.SanitizeXmlValue(cellValue));
                }
 
            }
            return row;
        }

        private static string ResolveErrorValue(object value)
        {
            if (value is Errors.FormulaError formulaError)
            {
                switch (formulaError)
                {
                    case Errors.FormulaError.Null:
                    case Errors.FormulaError.DivisionByZero:
                    case Errors.FormulaError.Value:
                    case Errors.FormulaError.Reference:
                    case Errors.FormulaError.Name:
                    case Errors.FormulaError.Number:
                    case Errors.FormulaError.NotAvailable:
                    case Errors.FormulaError.GettingData:
                        return Errors.FormulaErrorToString(formulaError);
                }
            }
            else if (value is string errorText && Errors.TryParseFormulaError(errorText, out Errors.FormulaError parsedError))
            {
                return Errors.FormulaErrorToString(parsedError);
            }
            return Errors.FormulaErrorToString(Errors.FormulaError.Name);
        }

        private static XmlElement CreateFormulaElement(Cell cell, out string cellValue, out XmlAttribute? cellType)
        {
            XmlElement formulaElement = null;
            cellType = null;
            XmlAttribute? formulaTypeAttribute = null;
            XmlAttribute? formulaRangeAttribute = null;
            string formulaExpression;
            bool omitFormula = false;
            switch (cell.Formula.Type)
            {
                case FormulaData.FormulaType.DataTable:
                    formulaTypeAttribute = new XmlAttribute("t", "dataTable");
                    break;
                case FormulaData.FormulaType.Shared:
                    formulaTypeAttribute = new XmlAttribute("t", "shared");
                    break;
                case FormulaData.FormulaType.Array:
                    formulaTypeAttribute = new XmlAttribute("t", "array");
                    break;
                default:
                    formulaTypeAttribute = new XmlAttribute("t", "normal");
                    break;
            }
            if (cell.Formula.FormulaRange != null)
            {
                formulaRangeAttribute = new XmlAttribute("ref", cell.Formula.FormulaRange); // Assumed as validated
            }
            ResolveFormulaCachedValue(cell.Formula, out cellValue, out cellType);
            if (cell.Formula.MasterCellAddress != null)
            {
                omitFormula = true; // Array referenced cells are only internal formulas
            }
            if (cell.Formula.DefinedNameReference != null)
            {
                if (cell.Formula.DefinedNameReference.Error != Errors.FormulaError.NoError)
                {
                    cellType = XmlAttribute.CreateAttribute("t", "e"); // Mark cell type as error (propagated form defined name)
                }
                formulaExpression = XmlUtils.SanitizeXmlValue(cell.Formula.DefinedNameReference.Name); // reflects currently cell.Value
            }
            else
            {
                formulaExpression = XmlUtils.SanitizeXmlValue(cell.Formula.Expression); // reflects currently cell.Value
            }
            if (!omitFormula)
            {
                formulaElement = XmlElement.CreateElement("f");
                formulaElement.AddAttribute(formulaTypeAttribute);
                formulaElement.AddAttribute(formulaRangeAttribute);
                formulaElement.InnerValue = formulaExpression;
            }
            return formulaElement;
        }

        private static void ResolveFormulaCachedValue(FormulaData formula, out string cellValue, out XmlAttribute? cellType)
        {
            cellValue = string.Empty;
            cellType = null;
            if (formula.CachedValue == null)
            {
                return;
            }
            Cell.CellType cachedValueType = formula.CachedValueType;
            if (cachedValueType == Cell.CellType.Default)
            {
                cachedValueType = FormulaData.ResolveCachedValueType(formula.CachedValue);
            }
            switch (cachedValueType)
            {
                case Cell.CellType.Number:
                    cellValue = CastNumber(formula.CachedValue) ?? formula.CachedValue.ToString();
                    break;
                case Cell.CellType.Date:
                    if (formula.CachedValue is DateTime dateTime)
                    {
                        cellValue = DataUtils.GetOADateTimeString(dateTime);
                    }
                    else
                    {
                        cellValue = formula.CachedValue.ToString();
                        if (!ParserUtils.TryParseDouble(cellValue, out _, NumberStyles.Float))
                        {
                            cellType = XmlAttribute.CreateAttribute("t", "d");
                        }
                    }
                    break;
                case Cell.CellType.Time:
                    if (formula.CachedValue is TimeSpan timeSpan)
                    {
                        cellValue = DataUtils.GetOATimeString(timeSpan);
                    }
                    else
                    {
                        cellValue = formula.CachedValue.ToString();
                    }
                    break;
                case Cell.CellType.Bool:
                    if (formula.CachedValue is bool booleanValue)
                    {
                        cellValue = booleanValue ? "1" : "0";
                    }
                    else
                    {
                        cellValue = formula.CachedValue.ToString();
                        if (ParserUtils.TryParseBool(cellValue, out booleanValue))
                        {
                            cellValue = booleanValue ? "1" : "0";
                        }
                    }
                    cellType = XmlAttribute.CreateAttribute("t", "b");
                    break;
                case Cell.CellType.Error:
                    cellValue = formula.CachedValue is Errors.FormulaError formulaError
                        ? Errors.FormulaErrorToString(formulaError)
                        : formula.CachedValue.ToString();
                    cellType = XmlAttribute.CreateAttribute("t", "e");
                    break;
                case Cell.CellType.String:
                default:
                    cellValue = formula.CachedValue.ToString();
                    cellType = XmlAttribute.CreateAttribute("t", "str");
                    break;
            }
        }

        private static string CastNumber(object item)
        {
            string cellValue = null;
            Type t = item.GetType();
 
            if (t == typeof(byte)) { cellValue = ParserUtils.ToString((byte)item); }
            else if (t == typeof(sbyte)) { cellValue = ParserUtils.ToString((sbyte)item); }
            else if (t == typeof(decimal)) { cellValue = ParserUtils.ToString((decimal)item); }
            else if (t == typeof(double)) { cellValue = ParserUtils.ToString((double)item); }
            else if (t == typeof(float)) { cellValue = ParserUtils.ToString((float)item); }
            else if (t == typeof(int)) { cellValue = ParserUtils.ToString((int)item); }
            else if (t == typeof(uint)) { cellValue = ParserUtils.ToString((uint)item); }
            else if (t == typeof(long)) { cellValue = ParserUtils.ToString((long)item); }
            else if (t == typeof(ulong)) { cellValue = ParserUtils.ToString((ulong)item); }
            else if (t == typeof(short)) { cellValue = ParserUtils.ToString((short)item); }
            else if (t == typeof(ushort)) { cellValue = ParserUtils.ToString((ushort)item); }
            // Not a number = null
            return cellValue;
        }
 
 

        private List<XmlElement> CreateRowElements(Worksheet worksheet)
        {
            List<DynamicRow> cellData = GetSortedSheetData(worksheet);
            List<XmlElement> rows = new List<XmlElement>(cellData.Count);
            foreach (DynamicRow row in cellData)
            {
                rows.Add(CreateRowElement(row, worksheet));
            }
            return rows;
        }
 
        #endregion
        #region helperClasses
        internal class DynamicRow
        {
            public int RowNumber { get; set; }

            public List<Cell> CellDefinitions { get; }

            public DynamicRow()
            {
                this.CellDefinitions = new List<Cell>();
            }
        }
        #endregion
 
    }
}