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 © 2025
 * 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.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.Linq;
using System.Text;
using static NanoXLSX.Internal.Enums.WriterPassword;
 
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;
                //TODO add plug-in hook to overwrite password instance
                this.passwordWriter = new LegacyPasswordWriter(PasswordType.WorksheetProtection, passwordInstance.PasswordHash);
            }
        }

        public XmlElement XmlElement { get => 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);
        }

        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>();
            foreach (KeyValuePair<string, Cell> item in worksheet.Cells)
            {
                temp.Add(item.Value);
            }
            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");
            }
 
            string valueDef = "";
            foreach (Cell item in dynamicRow.CellDefinitions)
            {
                XmlAttribute? styleDef = null;
                XmlAttribute? typeDef = null;
 
                if (item.CellStyle != null)
                {
                    styleDef = XmlAttribute.CreateAttribute("s", ParserUtils.ToString(item.CellStyle.InternalID.Value));
                }
                if (item.DataType == Cell.CellType.Bool)
                {
                    typeDef = XmlAttribute.CreateAttribute("t", "b");
                    if ((bool)item.Value) { valueDef = "1"; }
                    else { valueDef = "0"; }
 
                }
                // Number casting
                else if (item.DataType == Cell.CellType.Number)
                {
                    typeDef = XmlAttribute.CreateAttribute("t", "n");
                    Type t = item.Value.GetType();
 
                    if (t == typeof(byte)) { valueDef = ParserUtils.ToString((byte)item.Value); }
                    else if (t == typeof(sbyte)) { valueDef = ParserUtils.ToString((sbyte)item.Value); }
                    else if (t == typeof(decimal)) { valueDef = ParserUtils.ToString((decimal)item.Value); }
                    else if (t == typeof(double)) { valueDef = ParserUtils.ToString((double)item.Value); }
                    else if (t == typeof(float)) { valueDef = ParserUtils.ToString((float)item.Value); }
                    else if (t == typeof(int)) { valueDef = ParserUtils.ToString((int)item.Value); }
                    else if (t == typeof(uint)) { valueDef = ParserUtils.ToString((uint)item.Value); }
                    else if (t == typeof(long)) { valueDef = ParserUtils.ToString((long)item.Value); }
                    else if (t == typeof(ulong)) { valueDef = ParserUtils.ToString((ulong)item.Value); }
                    else if (t == typeof(short)) { valueDef = ParserUtils.ToString((short)item.Value); }
                    else if (t == typeof(ushort)) { valueDef = ParserUtils.ToString((ushort)item.Value); }
                }
                // Date parsing
                else if (item.DataType == Cell.CellType.Date)
                {
                    DateTime date = (DateTime)item.Value;
                    valueDef = DataUtils.GetOADateTimeString(date);
                }
                // Time parsing
                else if (item.DataType == Cell.CellType.Time)
                {
                    TimeSpan time = (TimeSpan)item.Value;
                    valueDef = DataUtils.GetOATimeString(time);
                }
                else
                {
                    string typeAttribute = null;
                    if (item.Value == null)
                    {
                        typeAttribute = null;
                        valueDef = null;
                        // No typeDef
                    }
                    else // Handle sharedStrings
                    {
                        if (item.DataType == Cell.CellType.Formula)
                        {
                            typeAttribute = "str";
                            valueDef = item.Value.ToString();
                        }
                        else
                        {
                            typeAttribute = "s";
                            if (item.Value is IFormattableText text)
                            {
                                valueDef = sharedStrings.Add(text, ParserUtils.ToString(sharedStrings.Count));
                            }
                            else
                            {
                                valueDef = sharedStrings.Add(new PlainText(item.Value.ToString()), ParserUtils.ToString(sharedStrings.Count));
                            }
                            this.sharedStringWriter.SharedStringsTotalCount++;
                        }
                    }
                    typeDef = XmlAttribute.CreateAttribute("t", typeAttribute);
                }
                if (item.DataType != Cell.CellType.Empty)
                {
                    XmlElement c = row.AddChildElementWithAttribute("c", "r", item.CellAddress);
                    c.AddAttribute(typeDef);
                    c.AddAttribute(styleDef);
                    if (item.DataType == Cell.CellType.Formula)
                    {
                        c.AddChildElementWithValue("f", XmlUtils.SanitizeXmlValue(item.Value.ToString()));
                    }
                    else
                    {
                        c.AddChildElementWithValue("v", XmlUtils.SanitizeXmlValue(valueDef));
                    }
                }
                else if (valueDef == null || item.DataType == Cell.CellType.Empty) // Empty cell
                {
                    XmlElement c = row.AddChildElementWithAttribute("c", "r", item.CellAddress);
                    c.AddAttribute(styleDef);
                }
            }
            return row;
        }

        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
 
    }
}