WorksheetReader.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 System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Xml;
using NanoXLSX.Enums;
using NanoXLSX.Exceptions;
using NanoXLSX.Interfaces;
using NanoXLSX.Interfaces.Reader;
using NanoXLSX.Registry;
using NanoXLSX.Styles;
using NanoXLSX.Utils;
using NanoXLSX.Utils.Xml;
using static NanoXLSX.Enums.Password;
using IOException = NanoXLSX.Exceptions.IOException;
 
namespace NanoXLSX.Internal.Readers
{
    public class WorksheetReader : IWorksheetReader
    {
        #region privateFields
        private Stream stream;
        private HashSet<string> dateStyles;
        private HashSet<string> timeStyles;
        private Dictionary<string, Style> resolvedStyles;
        private IPasswordReader passwordReader;
        private ReaderOptions readerOptions;
        #endregion
 
        #region properties

        public virtual string DocumentType { get { return @"http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"; } }

        public Workbook Workbook { get; set; }
        public IOptions Options { get; set; }
        public Action<Stream, Workbook, string, IOptions, int?> InlinePluginHandler { get; set; }
        public int CurrentWorksheetID { get; set; }

        public List<String> SharedStrings { get; set; }
        #endregion
 
        #region constructors
        public WorksheetReader()
        {
        }
        #endregion
 
        #region functions
        public void Init(Stream stream, Workbook workbook, IOptions readerOptions, Action<Stream, Workbook, string, IOptions, int?> inlinePluginHandler)
        {
            this.stream = stream;
            this.Workbook = workbook;
            this.Options = readerOptions;
            this.readerOptions = readerOptions as ReaderOptions;
            this.InlinePluginHandler = inlinePluginHandler;
            if (dateStyles == null || timeStyles == null || this.resolvedStyles == null)
            {
                StyleReaderContainer styleReaderContainer = workbook.AuxiliaryData.GetData<StyleReaderContainer>(PlugInUUID.StyleReader, PlugInUUID.StyleEntity);
                ProcessStyles(styleReaderContainer);
            }
            if (this.passwordReader == null)
            {
                this.passwordReader = PlugInLoader.GetPlugIn<IPasswordReader>(PlugInUUID.PasswordReader, new LegacyPasswordReader());
                this.passwordReader.Init(PasswordType.WorksheetProtection, (ReaderOptions)readerOptions);
            }
        }

        public void Execute()
        {
            try
            {
                WorksheetDefinition worksheetDefinition = Workbook.AuxiliaryData.GetData<WorksheetDefinition>(PlugInUUID.WorkbookReader, PlugInUUID.WorksheetDefinitionEntity, CurrentWorksheetID);
                Worksheet worksheet = new Worksheet(worksheetDefinition.WorksheetName, worksheetDefinition.SheetID, Workbook)
                {
                    Hidden = worksheetDefinition.Hidden
                };
                using (stream) // Close after processing
                {
                    StringBuilder sb = new StringBuilder();
                    using (XmlReader reader = XmlReader.Create(stream, XmlStreamUtils.CreateSettings()))
                    {
                        while (reader.Read())
                        {
                            if (reader.NodeType != XmlNodeType.Element)
                            {
                                continue;
                            }
                            switch (reader.LocalName.ToLowerInvariant())
                            {
                                case "sheetviews":
                                    GetSheetView(reader, worksheet);
                                    break;
                                case "sheetformatpr":
                                    GetSheetFormats(reader, worksheet);
                                    break;
                                case "cols":
                                    GetColumns(reader, worksheet, readerOptions);
                                    break;
                                case "sheetdata":
                                    GetRows(reader, worksheet, readerOptions, sb);
                                    break;
                                case "sheetprotection":
                                    GetSheetProtection(reader, worksheet);
                                    break;
                                case "mergecells":
                                    GetMergedCells(reader, worksheet);
                                    break;
                                case "autofilter":
                                    GetAutoFilters(reader, worksheet);
                                    break;
                            }
                        }
                        SetWorkbookRelation(worksheet);
                        InlinePluginHandler?.Invoke(stream, Workbook, PlugInUUID.WorksheetInlineReader, Options, CurrentWorksheetID);
                    }
                }
            }
            catch (NotSupportedContentException)
            {
                throw; // rethrow
            }
            catch (IOException)
            {
                throw;
            }
            catch (Exception ex)
            {
                throw new IOException("The XML entry could not be read from the input stream. Please see the inner exception:", ex);
            }
        }

        private void SetWorkbookRelation(Worksheet worksheet)
        {
            Workbook.AddWorksheet(worksheet);
            int selectedWorksheetId = Workbook.AuxiliaryData.GetData<int>(PlugInUUID.WorkbookReader, PlugInUUID.SelectedWorksheetEntity);
            if (selectedWorksheetId == CurrentWorksheetID)
            {
                Workbook.SetSelectedWorksheet(worksheet);
            }
        }

        private void ProcessStyles(StyleReaderContainer styleReaderContainer)
        {
            this.dateStyles = new HashSet<string>();
            this.timeStyles = new HashSet<string>();
            this.resolvedStyles = new Dictionary<string, Style>();
            for (int i = 0; i < styleReaderContainer.StyleCount; i++)
            {
                bool isDate;
                bool isTime;
                string index = ParserUtils.ToString(i);
                Style style = styleReaderContainer.GetStyle(i, out isDate, out isTime);
                if (isDate)
                {
                    this.dateStyles.Add(index);
                }
                if (isTime)
                {
                    this.timeStyles.Add(index);
                }
                this.resolvedStyles.Add(index, style);
            }
        }

        private void GetRows(XmlReader reader, Worksheet worksheet, ReaderOptions readerOptions, StringBuilder sb)
        {
            using (XmlReader sheetDataReader = reader.ReadSubtree())
            {
                sheetDataReader.Read(); // consume the sheetData open tag
                while (sheetDataReader.Read())
                {
                    if (!XmlStreamUtils.IsElement(sheetDataReader, "row"))
                    {
                        continue;
                    }
                    string rowAttribute = sheetDataReader.GetAttribute("r");
                    if (rowAttribute != null)
                    {
                        int rowNumber = ParserUtils.ParseInt(rowAttribute) - 1; // Transform to zero-based
                        string hiddenAttribute = sheetDataReader.GetAttribute("hidden");
                        if (hiddenAttribute != null && ParserUtils.ParseBinaryBool(hiddenAttribute) == 1)
                        {
                            worksheet.AddHiddenRow(rowNumber);
                        }
                        string heightAttribute = sheetDataReader.GetAttribute("ht");
                        if (heightAttribute != null)
                        {
                            worksheet.RowHeights.Add(rowNumber, GetValidatedHeight(ParserUtils.ParseFloat(heightAttribute), readerOptions));
                        }
                    }
                    if (!sheetDataReader.IsEmptyElement)
                    {
                        using (XmlReader rowReader = sheetDataReader.ReadSubtree())
                        {
                            rowReader.Read(); // consume the row open tag
                            while (rowReader.Read())
                            {
                                if (!XmlStreamUtils.IsElement(rowReader, "c"))
                                {
                                    continue;
                                }
                                ReadCell(rowReader, worksheet, sb);
                            }
                        }
                    }
                }
            }
        }

        private static void GetSheetView(XmlReader reader, Worksheet worksheet)
        {
            using (XmlReader subtree = reader.ReadSubtree())
            {
                subtree.Read(); // consume sheetViews
                while (subtree.Read())
                {
                    if (!XmlStreamUtils.IsElement(subtree, "sheetView"))
                    {
                        continue;
                    }
                    string attribute = subtree.GetAttribute("view") ?? string.Empty;
                    worksheet.ViewType = Worksheet.GetSheetViewTypeEnum(attribute);
                    attribute = subtree.GetAttribute("zoomScale");
                    if (attribute != null)
                    {
                        worksheet.ZoomFactor = ParserUtils.ParseInt(attribute);
                    }
                    attribute = subtree.GetAttribute("zoomScaleNormal");
                    if (attribute != null)
                    {
                        worksheet.ZoomFactors[Worksheet.SheetViewType.Normal] = ParserUtils.ParseInt(attribute);
                    }
                    attribute = subtree.GetAttribute("zoomScalePageLayoutView");
                    if (attribute != null)
                    {
                        worksheet.ZoomFactors[Worksheet.SheetViewType.PageLayout] = ParserUtils.ParseInt(attribute);
                    }
                    attribute = subtree.GetAttribute("zoomScaleSheetLayoutView");
                    if (attribute != null)
                    {
                        worksheet.ZoomFactors[Worksheet.SheetViewType.PageBreakPreview] = ParserUtils.ParseInt(attribute);
                    }
                    attribute = subtree.GetAttribute("showGridLines");
                    if (attribute != null)
                    {
                        worksheet.ShowGridLines = ParserUtils.ParseBinaryBool(attribute) == 1;
                    }
                    attribute = subtree.GetAttribute("showRowColHeaders");
                    if (attribute != null)
                    {
                        worksheet.ShowRowColumnHeaders = ParserUtils.ParseBinaryBool(attribute) == 1;
                    }
                    attribute = subtree.GetAttribute("showRuler");
                    if (attribute != null)
                    {
                        worksheet.ShowRuler = ParserUtils.ParseBinaryBool(attribute) == 1;
                    }
                    using (XmlReader sheetViewReader = subtree.ReadSubtree())
                    {
                        sheetViewReader.Read(); // consume sheetView
                        while (sheetViewReader.Read())
                        {
                            if (sheetViewReader.NodeType != XmlNodeType.Element)
                            {
                                continue;
                            }
                            if (XmlStreamUtils.IsElement(sheetViewReader, "selection"))
                            {
                                attribute = sheetViewReader.GetAttribute("sqref");
                                if (attribute != null)
                                {
                                    if (attribute.Contains(" "))
                                    {
                                        string[] ranges = attribute.Split(' ');
                                        foreach (string range in ranges)
                                        {
                                            CollectSelectedCells(range, worksheet);
                                        }
                                    }
                                    else
                                    {
                                        CollectSelectedCells(attribute, worksheet);
                                    }
                                }
                            }
                            else if (XmlStreamUtils.IsElement(sheetViewReader, "pane"))
                            {
                                SetPaneSplit(sheetViewReader, worksheet);
                            }
                        }
                    }
                }
            }
        }

        private static void CollectSelectedCells(string attribute, Worksheet worksheet)
        {
            if (attribute.Contains(":"))
            {
                // One range
                worksheet.AddSelectedCells(new Range(attribute));
            }
            else
            {
                // One cell
                worksheet.AddSelectedCells(new Range(attribute + ":" + attribute));
            }
        }

        private static void SetPaneSplit(XmlReader reader, Worksheet worksheet)
        {
            string attribute = reader.GetAttribute("state");
            bool useNumbers = false;
            bool frozenState = false;
            bool ySplitDefined = false;
            bool xSplitDefined = false;
            int? paneSplitRowIndex = null;
            int? paneSplitColumnIndex = null;
            float? paneSplitHeight = null;
            float? paneSplitWidth = null;
            Address topLeftCell = new Address(0, 0); // default value
            Worksheet.WorksheetPane? activePane = null;
            if (attribute != null)
            {
                if (ParserUtils.ToLower(attribute) == "frozen" || ParserUtils.ToLower(attribute) == "frozensplit")
                {
                    frozenState = true;
                }
                useNumbers = frozenState;
            }
            attribute = reader.GetAttribute("ySplit");
            if (attribute != null)
            {
                ySplitDefined = true;
                if (useNumbers)
                {
                    paneSplitRowIndex = ParserUtils.ParseInt(attribute);
                }
                else
                {
                    paneSplitHeight = DataUtils.GetPaneSplitHeight(ParserUtils.ParseFloat(attribute));
                }
            }
            attribute = reader.GetAttribute("xSplit");
            if (attribute != null)
            {
                xSplitDefined = true;
                if (useNumbers)
                {
                    paneSplitColumnIndex = ParserUtils.ParseInt(attribute);
                }
                else
                {
                    paneSplitWidth = DataUtils.GetPaneSplitWidth(ParserUtils.ParseFloat(attribute));
                }
            }
            attribute = reader.GetAttribute("topLeftCell");
            if (attribute != null)
            {
                topLeftCell = new Address(attribute);
            }
            attribute = reader.GetAttribute("activePane") ?? string.Empty;
            activePane = Worksheet.GetWorksheetPaneEnum(attribute);
            if (frozenState)
            {
                if (ySplitDefined && !xSplitDefined)
                {
                    worksheet.SetHorizontalSplit(paneSplitRowIndex.Value, frozenState, topLeftCell, activePane);
                }
                if (!ySplitDefined && xSplitDefined)
                {
                    worksheet.SetVerticalSplit(paneSplitColumnIndex.Value, frozenState, topLeftCell, activePane);
                }
                else if (ySplitDefined && xSplitDefined)
                {
                    worksheet.SetSplit(paneSplitColumnIndex.Value, paneSplitRowIndex.Value, frozenState, topLeftCell, activePane);
                }
            }
            else
            {
                if (ySplitDefined && !xSplitDefined)
                {
                    worksheet.SetHorizontalSplit(paneSplitHeight.Value, topLeftCell, activePane);
                }
                if (!ySplitDefined && xSplitDefined)
                {
                    worksheet.SetVerticalSplit(paneSplitWidth.Value, topLeftCell, activePane);
                }
                else if (ySplitDefined && xSplitDefined)
                {
                    worksheet.SetSplit(paneSplitWidth, paneSplitHeight, topLeftCell, activePane);
                }
            }
        }

        private void GetSheetProtection(XmlReader reader, Worksheet worksheet)
        {
            int hasProtection = 0;
            hasProtection += ReadSheetProtectionAttribute(reader, Worksheet.SheetProtectionValue.AutoFilter, worksheet);
            hasProtection += ReadSheetProtectionAttribute(reader, Worksheet.SheetProtectionValue.DeleteColumns, worksheet);
            hasProtection += ReadSheetProtectionAttribute(reader, Worksheet.SheetProtectionValue.DeleteRows, worksheet);
            hasProtection += ReadSheetProtectionAttribute(reader, Worksheet.SheetProtectionValue.FormatCells, worksheet);
            hasProtection += ReadSheetProtectionAttribute(reader, Worksheet.SheetProtectionValue.FormatColumns, worksheet);
            hasProtection += ReadSheetProtectionAttribute(reader, Worksheet.SheetProtectionValue.FormatRows, worksheet);
            hasProtection += ReadSheetProtectionAttribute(reader, Worksheet.SheetProtectionValue.InsertColumns, worksheet);
            hasProtection += ReadSheetProtectionAttribute(reader, Worksheet.SheetProtectionValue.InsertHyperlinks, worksheet);
            hasProtection += ReadSheetProtectionAttribute(reader, Worksheet.SheetProtectionValue.InsertRows, worksheet);
            hasProtection += ReadSheetProtectionAttribute(reader, Worksheet.SheetProtectionValue.Objects, worksheet);
            hasProtection += ReadSheetProtectionAttribute(reader, Worksheet.SheetProtectionValue.PivotTables, worksheet);
            hasProtection += ReadSheetProtectionAttribute(reader, Worksheet.SheetProtectionValue.Scenarios, worksheet);
            hasProtection += ReadSheetProtectionAttribute(reader, Worksheet.SheetProtectionValue.SelectLockedCells, worksheet);
            hasProtection += ReadSheetProtectionAttribute(reader, Worksheet.SheetProtectionValue.SelectUnlockedCells, worksheet);
            hasProtection += ReadSheetProtectionAttribute(reader, Worksheet.SheetProtectionValue.Sort, worksheet);
            if (hasProtection > 0)
            {
                worksheet.UseSheetProtection = true;
            }
            string outerXml;
            using (XmlReader subtree = reader.ReadSubtree())
            {
                subtree.MoveToContent();
                outerXml = subtree.ReadOuterXml();
            }
            XmlDocument miniDoc = new XmlDocument { XmlResolver = null };
            miniDoc.LoadXml(outerXml);
            this.passwordReader.ReadXmlAttributes(miniDoc.DocumentElement);
            if (this.passwordReader.PasswordIsSet())
            {
                if (this.passwordReader is LegacyPasswordReader && (this.passwordReader as LegacyPasswordReader).ContemporaryAlgorithmDetected && (readerOptions == null || !readerOptions.IgnoreNotSupportedPasswordAlgorithms))
                {
                    throw new NotSupportedContentException("A not supported, contemporary password algorithm for the worksheet protection was detected. Check possible packages to add support to NanoXLSX, or ignore this error by a reader option");
                }
                worksheet.SheetProtectionPassword.CopyFrom(this.passwordReader);
            }
        }

        private static int ReadSheetProtectionAttribute(XmlReader reader, Worksheet.SheetProtectionValue sheetProtectionValue, Worksheet worksheet)
        {
            string attrName = Worksheet.GetSheetProtectionName(sheetProtectionValue);
            if (reader.GetAttribute(attrName) != null)
            {
                worksheet.SheetProtectionValues.Add(sheetProtectionValue);
                return 1;
            }
            return 0;
        }

        private static void GetMergedCells(XmlReader reader, Worksheet worksheet)
        {
            using (XmlReader subtree = reader.ReadSubtree())
            {
                subtree.Read(); // consume the mergeCells open tag
                while (subtree.Read())
                {
                    if (!XmlStreamUtils.IsElement(subtree, "mergeCell"))
                    {
                        continue;
                    }
                    string attribute = subtree.GetAttribute("ref");
                    if (attribute != null)
                    {
                        worksheet.MergeCells(new Range(attribute));
                    }
                }
            }
        }

        private static void GetSheetFormats(XmlReader reader, Worksheet worksheet)
        {
            string attribute = reader.GetAttribute("defaultColWidth");
            if (attribute != null)
            {
                worksheet.DefaultColumnWidth = ParserUtils.ParseFloat(attribute);
            }
            attribute = reader.GetAttribute("defaultRowHeight");
            if (attribute != null)
            {
                worksheet.DefaultRowHeight = ParserUtils.ParseFloat(attribute);
            }
        }

        private static void GetAutoFilters(XmlReader reader, Worksheet worksheet)
        {
            string autoFilterRef = reader.GetAttribute("ref");
            if (autoFilterRef != null)
            {
                Range range = new Range(autoFilterRef);
                worksheet.SetAutoFilter(range.StartAddress.Column, range.EndAddress.Column);
            }
        }

        private void GetColumns(XmlReader reader, Worksheet worksheet, ReaderOptions readerOptions)
        {
            using (XmlReader subtree = reader.ReadSubtree())
            {
                subtree.Read(); // consume the cols open tag
                while (subtree.Read())
                {
                    if (!XmlStreamUtils.IsElement(subtree, "col"))
                    {
                        continue;
                    }
                    int? min = null;
                    int? max = null;
                    List<int> indices = new List<int>();
                    string attribute = subtree.GetAttribute("min");
                    if (attribute != null)
                    {
                        min = ParserUtils.ParseInt(attribute);
                        max = min;
                        indices.Add(min.Value);
                    }
                    attribute = subtree.GetAttribute("max");
                    if (attribute != null)
                    {
                        max = ParserUtils.ParseInt(attribute);
                    }
                    if (min != null && max.Value != min.Value)
                    {
                        for (int i = min.Value; i <= max.Value; i++)
                        {
                            indices.Add(i);
                        }
                    }
                    attribute = subtree.GetAttribute("width");
                    float width = Worksheet.DefaultWorksheetColumnWidth;
                    if (attribute != null)
                    {
                        width = ParserUtils.ParseFloat(attribute);
                    }
                    attribute = subtree.GetAttribute("hidden");
                    bool hidden = false;
                    if (attribute != null && ParserUtils.ParseBinaryBool(attribute) == 1)
                    {
                        hidden = true;
                    }
                    attribute = subtree.GetAttribute("style");
                    Style defaultStyle = null;
                    if (attribute != null && resolvedStyles.TryGetValue(attribute, out var attributeValue))
                    {
                        defaultStyle = attributeValue;
                    }
                    foreach (int index in indices)
                    {
                        string columnAddress = Cell.ResolveColumnAddress(index - 1); // Transform to zero-based
                        if (defaultStyle != null)
                        {
                            worksheet.SetColumnDefaultStyle(columnAddress, defaultStyle);
                        }
                        if (width != Worksheet.DefaultWorksheetColumnWidth)
                        {
                            worksheet.SetColumnWidth(columnAddress, GetValidatedWidth(width, readerOptions));
                        }
                        if (hidden)
                        {
                            worksheet.AddHiddenColumn(columnAddress);
                        }
                    }
                }
            }
        }

        private void ReadCell(XmlReader rowReader, Worksheet worksheet, StringBuilder sb)
        {
            string address = rowReader.GetAttribute("r"); // Mandatory
            string type = rowReader.GetAttribute("t");    // can be null
            string styleNumber = rowReader.GetAttribute("s"); // can be null
            string value = "";
            string cachedValue = null;
            bool hasCachedValue = false;
            bool hasInlineString = false;
            string formulaExpression = null;
            bool hasFormula = false;
            string formulaType = null;
            string formulaReference = null;
            if (!rowReader.IsEmptyElement)
            {
                using (XmlReader cellReader = rowReader.ReadSubtree())
                {
                    cellReader.Read(); // consume <c>
                    while (cellReader.Read())
                    {
                        if (cellReader.NodeType != XmlNodeType.Element)
                        {
                            continue;
                        }
                        if (cellReader.LocalName.Equals("f", StringComparison.OrdinalIgnoreCase))
                        {
                            formulaType = cellReader.GetAttribute("t"); // Can be null
                            formulaReference = cellReader.GetAttribute("ref"); // Can be null
                            formulaExpression = cellReader.ReadElementContentAsString();
                            hasFormula = true;
                        }
                        if (cellReader.LocalName.Equals("v", StringComparison.OrdinalIgnoreCase))
                        {
                            if (hasFormula)
                            {
                                cachedValue = cellReader.ReadElementContentAsString();
                                hasCachedValue = true;
                            }
                            else
                            {
                                value = cellReader.ReadElementContentAsString();
                            }
                        }
                        else if (cellReader.LocalName.Equals("is", StringComparison.OrdinalIgnoreCase))
                        {
                            sb.Clear();
                            using (XmlReader isReader = cellReader.ReadSubtree())
                            {
                                isReader.Read(); // consume <is>
                                while (isReader.Read())
                                {
                                    if (isReader.NodeType == XmlNodeType.Element &&
                                        isReader.LocalName.Equals("t", StringComparison.OrdinalIgnoreCase))
                                    {
                                        sb.Append(isReader.ReadElementContentAsString());
                                    }
                                }
                            }
                            value = sb.ToString();
                            hasInlineString = true;
                        }
                    }
                }
            }
            if (hasFormula)
            {
                if (type == "s" && hasCachedValue)
                {
                    cachedValue = ResolveSharedString(cachedValue)?.ToString();
                }
                else if (type == "inlineStr" && hasInlineString)
                {
                    cachedValue = value;
                    hasCachedValue = true;
                }
                value = formulaExpression;
            }
            else if (type == "str" && !hasFormula)
            {
                // Linked cell of a formula. The master cell will be resolved later in a finalizing processor
                cachedValue = value; // Value is actually cached value (value is kept for compatibility)
                hasCachedValue = true;
                hasFormula = true; // Triggers upsert
            }
            Cell cell = hasFormula
                ? CreateCell(value, Cell.CellType.Formula, new Address(address), styleNumber)
                : ResolveCellData(value, type, styleNumber, address);
            UpsertFormulaData(cell, formulaExpression, cachedValue, hasCachedValue, type, formulaType, formulaReference);
            worksheet.AddCell(cell, address);
        }

        private Cell ResolveCellData(string raw, string type, string styleNumber, string address)
        {
            Cell.CellType importedType = Cell.CellType.Default;
            object rawValue;
            if (type == "e")
            {
                importedType = Cell.CellType.Error;
                if (!Errors.TryParseFormulaError(raw, out Errors.FormulaError error))
                {
                    error = Errors.FormulaError.UnknownError;
                }
                rawValue = error;
            }
            else if (type == "b")
            {
                rawValue = TryParseBool(raw);
                if (rawValue != null)
                {
                    importedType = Cell.CellType.Bool;
                }
                else
                {
                    rawValue = GetNumericValue(raw);
                    if (rawValue != null)
                    {
                        importedType = Cell.CellType.Number;
                    }
                }
            }
            else if (type == "s")
            {
                importedType = Cell.CellType.String;
                rawValue = ResolveSharedString(raw);
            }
            else if (type == "inlineStr")
            {
                importedType = Cell.CellType.String;
                rawValue = raw;
            }
            else if (dateStyles.Contains(styleNumber) && (type == null || type == "" || type == "n"))
            {
                rawValue = GetDateTimeValue(raw, Cell.CellType.Date, out importedType);
            }
            else if (timeStyles.Contains(styleNumber) && (type == null || type == "" || type == "n"))
            {
                rawValue = GetDateTimeValue(raw, Cell.CellType.Time, out importedType);
            }
            else
            {
                importedType = Cell.CellType.Number;
                rawValue = GetNumericValue(raw);
            }
            if (rawValue == null && raw == "")
            {
                importedType = Cell.CellType.Empty;
                rawValue = null;
            }
            else if (rawValue == null && raw.Length > 0)
            {
                importedType = Cell.CellType.String;
                rawValue = raw;
            }
            Address cellAddress = new Address(address);
            if (readerOptions != null)
            {
                if (readerOptions.EnforcedColumnTypes.Count > 0)
                {
                    rawValue = GetEnforcedColumnValue(rawValue, importedType, cellAddress);
                }
                rawValue = GetGloballyEnforcedValue(rawValue, cellAddress);
                rawValue = GetGloballyEnforcedFlagValues(rawValue, cellAddress);
                importedType = ResolveType(rawValue);
                if (importedType == Cell.CellType.Date && rawValue is DateTime && (DateTime)rawValue < DataUtils.FirstAllowedExcelDate)
                {
                    // Fix conversion from time to date, where time has no days
                    rawValue = ((DateTime)rawValue).AddDays(1);
                }
            }
            return CreateCell(rawValue, importedType, cellAddress, styleNumber);
        }

        private static void UpsertFormulaData(Cell cell, string expression, string cachedValue, bool hasCachedValue, string cachedValueType, string formulaType, string formulaReference)
        {
            if (cell.DataType != Cell.CellType.Formula)
            {
                return;
            }
            FormulaData formula = cell.Formula;
            Cell.CellType resolvedCachedValueType = ResolveFormulaCachedValueType(cachedValueType, hasCachedValue);
            formula.Expression = expression;
            if (resolvedCachedValueType == Cell.CellType.Error)
            {
                if (!Errors.TryParseFormulaError(cachedValue, out Errors.FormulaError error))
                {
                    error = Errors.FormulaError.UnknownError;
                }
                formula.CachedValue = error;
            }
            else
            {
                formula.CachedValue = cachedValue;
            }
            formula.CachedValueType = resolvedCachedValueType;
            formula.FormulaRange = formulaReference;
            if (!string.IsNullOrEmpty(formulaType))
            {
                switch (formulaType)
                {
                    case "array":
                        formula.Type = FormulaData.FormulaType.Array;
                        break;
                    case "dataTable":
                        formula.Type = FormulaData.FormulaType.DataTable;
                        break;
                    case "shared":
                        formula.Type = FormulaData.FormulaType.Shared;
                        break;
                    default:
                        formula.Type = FormulaData.FormulaType.Normal;
                        break;
                }
            }
            else
            {
                formula.Type = FormulaData.FormulaType.Normal; // Default
            }
            // Note: Defined name and formula master cell (array) resolution has to be performed in a finalizing processor
            cell.Formula = formula;
        }

        private static Cell.CellType ResolveFormulaCachedValueType(string type, bool hasCachedValue)
        {
            if (!hasCachedValue)
            {
                return Cell.CellType.Default;
            }
            switch (type)
            {
                case null:
                case "":
                case "n":
                    return Cell.CellType.Number;
                case "str":
                case "s":
                case "inlineStr":
                    return Cell.CellType.String;
                case "b":
                    return Cell.CellType.Bool;
                case "e":
                    return Cell.CellType.Error;
                case "d":
                    return Cell.CellType.Date;
                default:
                    return Cell.CellType.Default;
            }
        }

        private static Cell.CellType ResolveType(object value)
        {
            if (value == null)
            {
                return Cell.CellType.Empty;
            }
            switch (value)
            {
                case uint _:
                case long _:
                case ulong _:
                case short _:
                case ushort _:
                case decimal _:
                case float _:
                case double _:
                case byte _:
                case sbyte _:
                case int _:
                    return Cell.CellType.Number;
                case DateTime _:
                    return Cell.CellType.Date;
                case TimeSpan _:
                    return Cell.CellType.Time;
                case bool _:
                    return Cell.CellType.Bool;
                case Errors.FormulaError _:
                    return Cell.CellType.Error;
                default:
                    return Cell.CellType.String;
            }
        }

        private object GetGloballyEnforcedFlagValues(object data, Address address)
        {
            if (address.Row < readerOptions.EnforcingStartRowNumber)
            {
                return data;
            }
            if (readerOptions.EnforceDateTimesAsNumbers)
            {
                if (data is DateTime)
                {
                    data = DataUtils.GetOADateTime((DateTime)data, true);
                }
                else if (data is TimeSpan)
                {
                    data = DataUtils.GetOATime((TimeSpan)data);
                }
            }
            if (readerOptions.EnforceEmptyValuesAsString && data == null)
            {
                return "";
            }
            return data;
        }

        private object GetGloballyEnforcedValue(object data, Address address)
        {
            if (address.Row < readerOptions.EnforcingStartRowNumber)
            {
                return data;
            }
            if (readerOptions.GlobalEnforcingType == ReaderOptions.GlobalType.AllNumbersToDouble)
            {
                object tempDouble = ConvertToDouble(data, readerOptions);
                if (tempDouble != null)
                {
                    return tempDouble;
                }
            }
            else if (readerOptions.GlobalEnforcingType == ReaderOptions.GlobalType.AllNumbersToDecimal)
            {
                object tempDecimal = ConvertToDecimal(data, readerOptions);
                if (tempDecimal != null)
                {
                    return tempDecimal;
                }
            }
            else if (readerOptions.GlobalEnforcingType == ReaderOptions.GlobalType.AllNumbersToInt)
            {
                object tempInt = ConvertToInt(data);
                if (tempInt != null)
                {
                    return tempInt;
                }
            }
            else if (readerOptions.GlobalEnforcingType == ReaderOptions.GlobalType.EverythingToString)
            {
                return ConvertToString(data, readerOptions);
            }
            return data;
        }

        private object GetEnforcedColumnValue(object data, Cell.CellType importedTyp, Address address)
        {
            if (address.Row < readerOptions.EnforcingStartRowNumber)
            {
                return data;
            }
            if (!readerOptions.EnforcedColumnTypes.TryGetValue(address.Column, out var columnType))
            {
                return data;
            }
            switch (columnType)
            {
                case ReaderOptions.ColumnType.Numeric:
                    return GetNumericValue(data, importedTyp, readerOptions);
                case ReaderOptions.ColumnType.Decimal:
                    return ConvertToDecimal(data, readerOptions);
                case ReaderOptions.ColumnType.Double:
                    return ConvertToDouble(data, readerOptions);
                case ReaderOptions.ColumnType.Date:
                    return ConvertToDate(data, readerOptions);
                case ReaderOptions.ColumnType.Time:
                    return ConvertToTime(data, readerOptions);
                case ReaderOptions.ColumnType.Bool:
                    return ConvertToBool(data, readerOptions);
                default:
                    return ConvertToString(data, readerOptions);
            }
        }

        private object ConvertToBool(object data, ReaderOptions readerOptions)
        {
            switch (data)
            {
                case bool _:
                    return data;
                case uint _:
                case long _:
                case ulong _:
                case short _:
                case ushort _:
                case float _:
                case byte _:
                case sbyte _:
                case int _:
                    object tempObject = ConvertToDouble(data, readerOptions);
                    if (tempObject is double)
                    {
                        double tempDouble = (double)tempObject;
                        if (double.Equals(tempDouble, 0d))
                        {
                            return false;
                        }
                        else if (double.Equals(tempDouble, 1d))
                        {
                            return true;
                        }
                    }
                    break;
                case string _:
 
                    string tempString = (string)data;
                    bool? tempBool = TryParseBool(tempString);
                    if (tempBool != null)
                    {
                        return tempBool.Value;
                    }
                    break;
            }
            return data;
        }

        private static bool? TryParseBool(string raw)
        {
            if (raw == "0")
            {
                return false;
            }
            else if (raw == "1")
            {
                return true;
            }
            else
            {
                bool value;
                if (bool.TryParse(raw, out value))
                {
                    return value;
                }
                else
                {
                    return null;
                }
            }
        }

        private object ConvertToDouble(object data, ReaderOptions readerOptions)
        {
            object value = ConvertToDecimal(data, readerOptions);
            if (value is decimal)
            {
                return Decimal.ToDouble((decimal)value);
            }
            else if (value is float)
            {
                return Convert.ToDouble((float)value);
            }
            return value;
        }

        private object ConvertToDecimal(object data, ReaderOptions readerOptions)
        {
            IConvertible converter;
            switch (data)
            {
                case double doubleValue:
                    try
                    {
                        return Convert.ToDecimal(doubleValue);
                    }
                    catch (OverflowException)
                    {
                        return data;
                    }
                case uint _:
                case long _:
                case ulong _:
                case short _:
                case ushort _:
                case float _:
                case byte _:
                case sbyte _:
                case int _:
                    converter = data as IConvertible;
                    double tempDouble = converter.ToDouble(DataUtils.InvariantCulture);
                    if (tempDouble > (double)decimal.MaxValue || tempDouble < (double)decimal.MinValue)
                    {
                        return data;
                    }
                    else
                    {
                        return converter.ToDecimal(DataUtils.InvariantCulture);
                    }
                case bool _:
                    if ((bool)data)
                    {
                        return decimal.One;
                    }
                    else
                    {
                        return decimal.Zero;
                    }
                case DateTime _:
                    return new decimal(DataUtils.GetOADateTime((DateTime)data));
                case TimeSpan _:
                    return new decimal(DataUtils.GetOATime((TimeSpan)data));
                case string _:
                    decimal dValue;
                    string tempString = (string)data;
                    if (ParserUtils.TryParseDecimal(tempString, out dValue))
                    {
                        return dValue;
                    }
                    DateTime? tempDate = TryParseDate(tempString, readerOptions);
                    if (tempDate != null)
                    {
                        return new decimal(DataUtils.GetOADateTime(tempDate.Value));
                    }
                    TimeSpan? tempTime = TryParseTime(tempString, readerOptions);
                    if (tempTime != null)
                    {
                        return new decimal(DataUtils.GetOATime(tempTime.Value));
                    }
                    break;
            }
            return data;
        }

        private static object ConvertToInt(object data)
        {
            double tempDouble;
            switch (data)
            {
                case uint _:
                case long _:
                case ulong _:
                    break;
                case DateTime _:
                    tempDouble = DataUtils.GetOADateTime((DateTime)data, true);
                    return ConvertDoubleToInt(tempDouble);
                case TimeSpan _:
                    tempDouble = DataUtils.GetOATime((TimeSpan)data);
                    return ConvertDoubleToInt(tempDouble);
                case float _:
                case double _:
                    int? tempInt = TryConvertDoubleToInt(data);
                    if (tempInt != null)
                    {
                        return tempInt;
                    }
                    break;
                case bool _:
                    return (bool)data ? 1 : 0;
                case string _:
                    int tempInt2;
                    if (ParserUtils.TryParseInt((string)data, out tempInt2))
                    {
                        return tempInt2;
                    }
                    break;
            }
            return null;
        }

        private object ConvertToDate(object data, ReaderOptions readerOptions)
        {
            switch (data)
            {
                case DateTime _:
                    return data;
                case TimeSpan _:
                    DateTime root = DataUtils.FirstAllowedExcelDate;
                    TimeSpan time = (TimeSpan)data;
                    root = root.AddDays(-1); // Fix offset of 1
                    root = root.AddHours(time.Hours);
                    root = root.AddMinutes(time.Minutes);
                    root = root.AddSeconds(time.Seconds);
                    return root;
                case double _:
                case uint _:
                case long _:
                case ulong _:
                case short _:
                case ushort _:
                case float _:
                case byte _:
                case sbyte _:
                case int _:
                    return ConvertDateFromDouble(data, readerOptions);
                case string _:
                    DateTime? date2 = TryParseDate((string)data, readerOptions);
                    if (date2 != null)
                    {
                        return date2.Value;
                    }
                    return ConvertDateFromDouble(data, readerOptions);
            }
            return data;
        }

        private DateTime? TryParseDate(string raw, ReaderOptions readerOptions)
        {
            DateTime dateTime;
            bool isDateTime;
            if (readerOptions == null || string.IsNullOrEmpty(readerOptions.DateTimeFormat) || readerOptions.TemporalCultureInfo == null)
            {
                isDateTime = DateTime.TryParse(raw, ReaderOptions.DefaultCultureInfo, DateTimeStyles.None, out dateTime);
            }
            else
            {
                isDateTime = DateTime.TryParseExact(raw, readerOptions.DateTimeFormat, readerOptions.TemporalCultureInfo, DateTimeStyles.None, out dateTime);
            }
            if (isDateTime && dateTime >= DataUtils.FirstAllowedExcelDate && dateTime <= DataUtils.LastAllowedExcelDate)
            {
                return dateTime;
            }
            return null;
        }

        private object ConvertToTime(object data, ReaderOptions readerOptions)
        {
            switch (data)
            {
                case DateTime _:
                    return ConvertTimeFromDouble(data, readerOptions);
                case TimeSpan _:
                    return data;
                case double _:
                case uint _:
                case long _:
                case ulong _:
                case short _:
                case ushort _:
                case float _:
                case byte _:
                case sbyte _:
                case int _:
                    return ConvertTimeFromDouble(data, readerOptions);
                case string _:
                    TimeSpan? time = TryParseTime((string)data, readerOptions);
                    if (time != null)
                    {
                        return time;
                    }
                    return ConvertTimeFromDouble(data, readerOptions);
            }
            return data;
        }

        private static TimeSpan? TryParseTime(string raw, ReaderOptions readerOptions)
        {
            TimeSpan timeSpan;
            bool isTimeSpan;
            if (readerOptions == null || string.IsNullOrEmpty(readerOptions.TimeSpanFormat) || readerOptions.TemporalCultureInfo == null)
            {
                isTimeSpan = TimeSpan.TryParse(raw, ReaderOptions.DefaultCultureInfo, out timeSpan);
            }
            else
            {
                isTimeSpan = TimeSpan.TryParseExact(raw, readerOptions.TimeSpanFormat, readerOptions.TemporalCultureInfo, out timeSpan);
            }
            if (isTimeSpan && timeSpan.Days >= 0 && timeSpan.Days < DataUtils.MaxOADateValue)
            {
                return timeSpan;
            }
            return null;
        }

        private static object GetDateTimeValue(string raw, Cell.CellType valueType, out Cell.CellType resolvedType)
        {
            double dValue;
            if (!ParserUtils.TryParseDouble(raw, out dValue))
            {
                resolvedType = Cell.CellType.String;
                return raw;
            }
            if ((valueType == Cell.CellType.Date && (dValue < DataUtils.MinOADateValue || dValue > DataUtils.MaxOADateValue)) || (valueType == Cell.CellType.Time && (dValue < 0.0 || dValue > DataUtils.MaxOADateValue)))
            {
                // fallback to number (cannot be anything else)
                resolvedType = Cell.CellType.Number;
                return GetNumericValue(raw);
            }
            DateTime tempDate = DataUtils.GetDateFromOA(dValue);
            if (dValue < 1.0)
            {
                tempDate = tempDate.AddDays(1); // Modify wrong 1st date when < 1
            }
            if (valueType == Cell.CellType.Date)
            {
                resolvedType = Cell.CellType.Date;
                return tempDate;
            }
            else
            {
                resolvedType = Cell.CellType.Time;
                return new TimeSpan((int)dValue, tempDate.Hour, tempDate.Minute, tempDate.Second);
            }
        }

        private object ConvertDateFromDouble(object data, ReaderOptions readerOptions)
        {
            object oaDate = ConvertToDouble(data, readerOptions);
            if (oaDate is double && (double)oaDate < DataUtils.MaxOADateValue)
            {
                DateTime date = DataUtils.GetDateFromOA((double)oaDate);
                if (date >= DataUtils.FirstAllowedExcelDate && date <= DataUtils.LastAllowedExcelDate)
                {
                    return date;
                }
            }
            return data;
        }

        private object ConvertTimeFromDouble(object data, ReaderOptions readerOptions)
        {
            object oaDate = ConvertToDouble(data, readerOptions);
            if (oaDate is double)
            {
                double d = (double)oaDate;
                if (d >= DataUtils.MinOADateValue && d <= DataUtils.MaxOADateValue)
                {
                    DateTime date = DataUtils.GetDateFromOA(d);
                    return new TimeSpan((int)d, date.Hour, date.Minute, date.Second);
                }
            }
            return data;
        }

        private static int? TryConvertDoubleToInt(object data)
        {
            IConvertible converter = data as IConvertible;
            double dValue = converter.ToDouble(ReaderOptions.DefaultCultureInfo);
            if (dValue > int.MinValue && dValue < int.MaxValue)
            {
                return converter.ToInt32(ReaderOptions.DefaultCultureInfo);
            }
            return null;
        }

        private static int ConvertDoubleToInt(object data)
        {
            IConvertible converter = data as IConvertible;
            return converter.ToInt32(ReaderOptions.DefaultCultureInfo);
        }

        private static string ConvertToString(object data, ReaderOptions readerOptions)
        {
            switch (data)
            {
                case int _:
                    return ((int)data).ToString(ReaderOptions.DefaultCultureInfo);
                case uint _:
                    return ((uint)data).ToString(ReaderOptions.DefaultCultureInfo);
                case long _:
                    return ((long)data).ToString(ReaderOptions.DefaultCultureInfo);
                case ulong _:
                    return ((ulong)data).ToString(ReaderOptions.DefaultCultureInfo);
                case float _:
                    return ((float)data).ToString(ReaderOptions.DefaultCultureInfo);
                case double _:
                    return ((double)data).ToString(ReaderOptions.DefaultCultureInfo);
                case bool _:
                    return ((bool)data).ToString(ReaderOptions.DefaultCultureInfo);
                case DateTime _:
                    return ((DateTime)data).ToString(readerOptions.DateTimeFormat, ParserUtils.InvariantCulture);
                case TimeSpan _:
                    return ((TimeSpan)data).ToString(readerOptions.TimeSpanFormat, ParserUtils.InvariantCulture);
                default:
                    if (data == null)
                    {
                        return null;
                    }
                    return data.ToString();
            }
        }

        private object GetNumericValue(object raw, Cell.CellType importedType, ReaderOptions readerOptions)
        {
            if (raw == null)
            {
                return null;
            }
            object tempObject;
            switch (importedType)
            {
                case Cell.CellType.String:
                    string tempString = raw.ToString();
                    tempObject = GetNumericValue(tempString);
                    if (tempObject != null)
                    {
                        return tempObject;
                    }
                    DateTime? tempDate = TryParseDate(tempString, readerOptions);
                    if (tempDate != null)
                    {
                        return DataUtils.GetOADateTime(tempDate.Value);
                    }
                    TimeSpan? tempTime = TryParseTime(tempString, readerOptions);
                    if (tempTime != null)
                    {
                        return DataUtils.GetOATime(tempTime.Value);
                    }
                    tempObject = ConvertToBool(raw, readerOptions);
                    if (tempObject is bool)
                    {
                        return (bool)tempObject ? 1 : 0;
                    }
                    break;
                case Cell.CellType.Number:
                    return raw;
                case Cell.CellType.Date:
                    return DataUtils.GetOADateTime((DateTime)raw);
                case Cell.CellType.Time:
                    return DataUtils.GetOATime((TimeSpan)raw);
                case Cell.CellType.Bool:
                    if ((bool)raw)
                    {
                        return 1;
                    }
                    return 0;
            }
            return raw;
        }
 

        private static object GetNumericValue(string raw)
        {
            bool hasDecimalPoint = raw.Contains(".");
 
            // Only try integer parsing if there's no decimal point
            if (!hasDecimalPoint)
            {
                // integer section (unchanged)
                uint uiValue;
                int iValue;
                bool canBeUint = ParserUtils.TryParseUint(raw, out uiValue);
                bool canBeInt = ParserUtils.TryParseInt(raw, out iValue);
                if (canBeUint && !canBeInt)
                {
                    return uiValue;
                }
                else if (canBeInt)
                {
                    return iValue;
                }
                ulong ulValue;
                long lValue;
                bool canBeUlong = ParserUtils.TryParseUlong(raw, out ulValue);
                bool canBeLong = ParserUtils.TryParseLong(raw, out lValue);
                if (canBeUlong && !canBeLong)
                {
                    return ulValue;
                }
                else if (canBeLong)
                {
                    return lValue;
                }
            }
 
            decimal dcValue;
            double dValue;
            float fValue;
 
            // Decimal/float section
            if (ParserUtils.TryParseDecimal(raw, out dcValue))
            {
                // Check if the value can be accurately represented as float
                float testFloat = decimal.ToSingle(dcValue);
                decimal backToDecimal = (decimal)testFloat;
 
                // If converting to float and back preserves the value, use float
                if (dcValue == backToDecimal)
                {
                    return testFloat;
                }
                else
                {
                    // Otherwise use double for better precision
                    return decimal.ToDouble(dcValue);
                }
            }
            // High range float section
            else if (ParserUtils.TryParseFloat(raw, out fValue) && fValue >= float.MinValue && fValue <= float.MaxValue && !float.IsInfinity(fValue))
            {
                return fValue;
            }
            if (ParserUtils.TryParseDouble(raw, out dValue))
            {
                return dValue;
            }
            return null;
        }

        private static float GetValidatedWidth(float rawValue, ReaderOptions readerOptions)
        {
            if (rawValue < Worksheet.MinColumnWidth)
            {
                if (readerOptions.EnforceStrictValidation)
                {
                    throw new WorksheetException($"The worksheet contains an invalid column width (too small: {rawValue}) value. This error is ignored when disabling the reader option 'EnforceStrictValidation'");
                }
                else
                {
                    return Worksheet.MinColumnWidth;
                }
            }
            else if (rawValue > Worksheet.MaxColumnWidth)
            {
                if (readerOptions.EnforceStrictValidation)
                {
                    throw new WorksheetException($"The worksheet contains an invalid column width (too large: {rawValue}) value.  This error is ignored when disabling the reader option 'EnforceStrictValidation'");
                }
                else
                {
                    return Worksheet.MaxColumnWidth;
                }
            }
            else
            {
                return rawValue;
            }
        }

        private static float GetValidatedHeight(float rawValue, ReaderOptions readerOptions)
        {
            if (rawValue < Worksheet.MinRowHeight)
            {
                if (readerOptions.EnforceStrictValidation)
                {
                    throw new WorksheetException($"The worksheet contains an invalid row height (too small: {rawValue}) value. Consider using the ImportOption 'EnforceValidRowDimensions' to ignore this error.");
                }
                else
                {
                    return Worksheet.MinRowHeight;
                }
            }
            else if (rawValue > Worksheet.MaxRowHeight)
            {
                if (readerOptions.EnforceStrictValidation)
                {
                    throw new WorksheetException($"The worksheet contains an invalid row height (too large: {rawValue}) value. Consider using the ImportOption 'EnforceValidRowDimensions' to ignore this error.");
                }
                else
                {
                    return Worksheet.MaxRowHeight;
                }
            }
            else
            {
                return rawValue;
            }
        }

        private string ResolveSharedString(string raw)
        {
            if (SharedStrings == null)
            {
                throw new IOException("The worksheet contains a shared-string cell, but no shared strings relationship was found for the workbook.");
            }
            int stringId;
            if (ParserUtils.TryParseInt(raw, out stringId))
            {
                string resolvedString = SharedStrings.ElementAtOrDefault(stringId);
                if (resolvedString == null)
                {
                    return raw;
                }
                else
                {
                    return resolvedString;
                }
            }
            return raw;
        }

        private Cell CreateCell(object value, Cell.CellType type, Address address, string styleNumber = null)
        {
            Cell cell = new Cell(value, type, address);
            if (styleNumber != null && resolvedStyles.TryGetValue(styleNumber, out var styleValue))
            {
                cell.SetStyle(styleValue);
            }
            return cell;
        }
        #endregion
    }
}