Worksheet.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.Linq;
using System.Text;
using System.Text.RegularExpressions;
using NanoXLSX.Exceptions;
using NanoXLSX.Interfaces;
using NanoXLSX.Registry;
using NanoXLSX.Styles;
using NanoXLSX.Utils;
using FormatException = NanoXLSX.Exceptions.FormatException;
 
namespace NanoXLSX
{
    public class Worksheet
    {
        static Worksheet()
        {
            PlugInLoader.Initialize();
        }
 
        #region constants
        public static readonly int MaxWorksheetNameLength = 31;
        public static readonly float DefaultWorksheetColumnWidth = 10f;
        public static readonly float DefaultWorksheetRowHeight = 15f;
        public static readonly int MaxColumnNumber = 16383;
#pragma warning disable CA1805 // Do not initialize unnecessarily
        public static readonly int MinColumnNumber = 0;
#pragma warning restore CA1805
#pragma warning disable CA1805 // Do not initialize unnecessarily
        public static readonly float MinColumnWidth = 0f;
#pragma warning restore CA1805
#pragma warning disable CA1805 // Do not initialize unnecessarily
        public static readonly float MinRowHeight = 0f;
#pragma warning restore CA1805
        public static readonly float MaxColumnWidth = 255f;
        public static readonly int MaxRowNumber = 1048575;
#pragma warning disable CA1805 // Do not initialize unnecessarily
        public static readonly int MinRowNumber = 0;
#pragma warning restore CA1805 
        public static readonly float MaxRowHeight = 409.5f;
#pragma warning disable CA1805 // Do not initialize unnecessarily
        public const int AutoZoomFactor = 0;
#pragma warning restore CA1805
        public const int MinZoomFactor = 10;
        public const int maxZoomFactor = 400;
        #endregion
 
        #region enums
        public enum CellDirection
        {
            ColumnToColumn,
            RowToRow,
            Disabled
        }

        public enum SheetProtectionValue
        {
            // sheet, // Is always on 1 if protected
            Objects,
            Scenarios,
            FormatCells,
            FormatColumns,
            FormatRows,
            InsertColumns,
            InsertRows,
            InsertHyperlinks,
            DeleteColumns,
            DeleteRows,
            SelectLockedCells,
            Sort,
            AutoFilter,
            PivotTables,
            SelectUnlockedCells
        }

        public enum WorksheetPane
        {
            BottomRight,
            TopRight,
            BottomLeft,
            TopLeft
        }

        public enum SheetViewType
        {
            Normal,
            PageBreakPreview,
            PageLayout
        }
        #endregion
 
        #region privateFields
        private Style activeStyle;
        private Range? autoFilterRange;
        private readonly Dictionary<string, Cell> cells;
        private readonly Dictionary<int, Column> columns;
        private string sheetName;
        private int currentRowNumber;
        private int currentColumnNumber;
        private float defaultRowHeight;
        private float defaultColumnWidth;
        private readonly Dictionary<int, float> rowHeights;
        private readonly Dictionary<int, bool> hiddenRows;
        private readonly Dictionary<string, Range> mergedCells;
        private readonly List<SheetProtectionValue> sheetProtectionValues;
        private bool useActiveStyle;
        private bool hidden;
        private Workbook workbookReference;
        private IPassword sheetProtectionPassword;
        private List<Range> selectedCells;
        private bool? freezeSplitPanes;
        private float? paneSplitLeftWidth;
        private float? paneSplitTopHeight;
        private Address? paneSplitTopLeftCell;
        private Address? paneSplitAddress;
        private WorksheetPane? activePane;
        private int sheetID;
        private SheetViewType viewType;
        private Dictionary<SheetViewType, int> zoomFactor;
        #endregion
 
        #region properties
        public Range? AutoFilterRange
        {
            get { return autoFilterRange; }
        }

        public Dictionary<string, Cell> Cells
        {
            get { return cells; }
        }

        public Dictionary<int, Column> Columns
        {
            get { return columns; }
        }

        public CellDirection CurrentCellDirection { get; set; }

        public float DefaultColumnWidth
        {
            get { return defaultColumnWidth; }
            set
            {
                if (value < MinColumnWidth || value > MaxColumnWidth)
                {
                    throw new RangeException("The passed default column width is out of range (" + MinColumnWidth + " to " + MaxColumnWidth + ")");
                }
                defaultColumnWidth = value;
            }
        }

        public float DefaultRowHeight
        {
            get { return defaultRowHeight; }
            set
            {
                if (value < MinRowHeight || value > MaxRowHeight)
                {
                    throw new RangeException("The passed default row height is out of range (" + MinRowHeight + " to " + MaxRowHeight + ")");
                }
                defaultRowHeight = value;
            }
        }

        public Dictionary<int, bool> HiddenRows
        {
            get { return hiddenRows; }
        }

        public Dictionary<int, float> RowHeights
        {
            get { return rowHeights; }
        }

        public Dictionary<string, Range> MergedCells
        {
            get { return mergedCells; }
        }

        public List<Range> SelectedCells
        {
            get { return selectedCells; }
        }

        public int SheetID
        {
            get => sheetID;
            set
            {
                if (value < 1)
                {
                    throw new FormatException("The ID " + value + " is invalid. Worksheet IDs must be >0");
                }
                sheetID = value;
            }
        }

        public string SheetName
        {
            get { return sheetName; }
            set { SetSheetName(value); }
        }

        public virtual IPassword SheetProtectionPassword
        {
            get { return sheetProtectionPassword; }
            internal set { sheetProtectionPassword = value; }
        }

        public List<SheetProtectionValue> SheetProtectionValues
        {
            get { return sheetProtectionValues; }
        }

        public bool UseSheetProtection { get; set; }

        public Workbook WorkbookReference
        {
            get { return workbookReference; }
            set
            {
                workbookReference = value;
                if (value != null)
                {
                    workbookReference.ValidateWorksheets();
                }
            }
        }

        public bool Hidden
        {
            get { return hidden; }
            set
            {
                hidden = value;
                if (value && workbookReference != null)
                {
                    workbookReference.ValidateWorksheets();
                }
            }
        }

        public float? PaneSplitTopHeight
        {
            get { return paneSplitTopHeight; }
        }

        public float? PaneSplitLeftWidth
        {
            get { return paneSplitLeftWidth; }
        }

        public bool? FreezeSplitPanes
        {
            get { return freezeSplitPanes; }
        }

        public Address? PaneSplitTopLeftCell
        {
            get { return paneSplitTopLeftCell; }
        }

        public Address? PaneSplitAddress
        {
            get { return paneSplitAddress; }
        }
 

        public WorksheetPane? ActivePane
        {
            get { return activePane; }
        }

        public Style ActiveStyle
        {
            get { return activeStyle; }
        }

        public bool ShowGridLines { get; set; }

        public bool ShowRowColumnHeaders { get; set; }

        public bool ShowRuler { get; set; }

        public SheetViewType ViewType
        {
            get
            {
                return viewType;
            }
            set
            {
                viewType = value;
                SetZoomFactor(value, 100);
            }
        }

        public int ZoomFactor
        {
            set
            {
                SetZoomFactor(viewType, value);
            }
            get
            {
                return zoomFactor[viewType];
            }
        }

        public Dictionary<SheetViewType, int> ZoomFactors
        {
            get
            {
                return zoomFactor;
            }
        }
 
 
        #endregion
 
 
        #region constructors
        public Worksheet()
        {
            CurrentCellDirection = CellDirection.ColumnToColumn;
            cells = new Dictionary<string, Cell>();
            currentRowNumber = 0;
            currentColumnNumber = 0;
            defaultColumnWidth = DefaultWorksheetColumnWidth;
            defaultRowHeight = DefaultWorksheetRowHeight;
            rowHeights = new Dictionary<int, float>();
            mergedCells = new Dictionary<string, Range>();
            selectedCells = new List<Range>();
            sheetProtectionValues = new List<SheetProtectionValue>();
            hiddenRows = new Dictionary<int, bool>();
            columns = new Dictionary<int, Column>();
            activeStyle = null;
            workbookReference = null;
            viewType = SheetViewType.Normal;
            zoomFactor = new Dictionary<SheetViewType, int>
            {
                { viewType, 100 }
            };
            ShowGridLines = true;
            ShowRowColumnHeaders = true;
            ShowRuler = true;
            sheetProtectionPassword = new LegacyPassword(LegacyPassword.PasswordType.WorksheetProtection);
        }

        public Worksheet(string name)
            : this()
        {
            SetSheetName(name);
        }

        public Worksheet(string name, int id, Workbook reference)
            : this()
        {
            SetSheetName(name);
            SheetID = id;
            workbookReference = reference;
        }
 
        #endregion
 
        #region methods_AddNextCell

        public void AddNextCell(object value)
        {
            AddNextCell(CastValue(value, currentColumnNumber, currentRowNumber), true, null);
        }
 

        public void AddNextCell(object value, Style style)
        {
            AddNextCell(CastValue(value, currentColumnNumber, currentRowNumber), true, style);
        }
 

        private void AddNextCell(Cell cell, bool incremental, Style style)
        {
            // date and time styles are already defined by the passed cell object
            if (style != null || (activeStyle != null && useActiveStyle))
            {
                if (cell.CellStyle == null && useActiveStyle)
                {
                    cell.SetStyle(activeStyle);
                }
                else if (cell.CellStyle == null && style != null)
                {
                    cell.SetStyle(style);
                }
                else if (cell.CellStyle != null && useActiveStyle)
                {
                    Style mixedStyle = (Style)cell.CellStyle.Copy();
                    mixedStyle.Append(activeStyle);
                    cell.SetStyle(mixedStyle);
                }
                else if (cell.CellStyle != null && style != null)
                {
                    Style mixedStyle = (Style)cell.CellStyle.Copy();
                    mixedStyle.Append(style);
                    cell.SetStyle(mixedStyle);
                }
            }
            string address = cell.CellAddress;
            if (cells.ContainsKey(address))
            {
                cells[address] = cell;
            }
            else
            {
                cells.Add(address, cell);
            }
            if (incremental)
            {
                if (CurrentCellDirection == CellDirection.ColumnToColumn)
                {
                    currentColumnNumber++;
                }
                else if (CurrentCellDirection == CellDirection.RowToRow)
                {
                    currentRowNumber++;
                }
                else
                {
                    // disabled / no-op
                }
            }
            else
            {
                if (CurrentCellDirection == CellDirection.ColumnToColumn)
                {
                    currentColumnNumber = cell.ColumnNumber + 1;
                    currentRowNumber = cell.RowNumber;
                }
                else if (CurrentCellDirection == CellDirection.RowToRow)
                {
                    currentColumnNumber = cell.ColumnNumber;
                    currentRowNumber = cell.RowNumber + 1;
                }
                else
                {
                    // disabled / no-op
                }
            }
        }

        private static Cell CastValue(object value, int column, int row)
        {
            Cell c;
            if (value != null && value.GetType() == typeof(Cell))
            {
                c = (Cell)value;
                c.CellAddress2 = new Address(column, row);
            }
            else
            {
                c = new Cell(value, Cell.CellType.Default, column, row);
            }
            return c;
        }
 
 
        #endregion
 
        #region methods_AddCell

        public void AddCell(object value, int columnNumber, int rowNumber)
        {
            AddNextCell(CastValue(value, columnNumber, rowNumber), false, null);
        }

        public void AddCell(object value, int columnNumber, int rowNumber, Style style)
        {
            AddNextCell(CastValue(value, columnNumber, rowNumber), false, style);
        }
 

        public void AddCell(object value, string address)
        {
            int column;
            int row;
            Cell.ResolveCellCoordinate(address, out column, out row);
            AddCell(value, column, row);
        }

        public void AddCell(object value, string address, Style style)
        {
            int column;
            int row;
            Cell.ResolveCellCoordinate(address, out column, out row);
            AddCell(value, column, row, style);
        }
 
        #endregion
 
        #region methods_AddCellFormula

        public void AddCellFormula(string formula, string address)
        {
            int column;
            int row;
            Cell.ResolveCellCoordinate(address, out column, out row);
            Cell c = new Cell(formula, Cell.CellType.Formula, column, row);
            AddNextCell(c, false, null);
        }

        public void AddCellFormula(string formula, string address, Style style)
        {
            int column;
            int row;
            Cell.ResolveCellCoordinate(address, out column, out row);
            Cell c = new Cell(formula, Cell.CellType.Formula, column, row);
            AddNextCell(c, false, style);
        }

        public void AddCellFormula(string formula, int columnNumber, int rowNumber)
        {
            Cell c = new Cell(formula, Cell.CellType.Formula, columnNumber, rowNumber);
            AddNextCell(c, false, null);
        }

        public void AddCellFormula(string formula, int columnNumber, int rowNumber, Style style)
        {
            Cell c = new Cell(formula, Cell.CellType.Formula, columnNumber, rowNumber);
            AddNextCell(c, false, style);
        }

        public void AddNextCellFormula(string formula)
        {
            Cell c = new Cell(formula, Cell.CellType.Formula, currentColumnNumber, currentRowNumber);
            AddNextCell(c, true, null);
        }

        public void AddNextCellFormula(string formula, Style style)
        {
            Cell c = new Cell(formula, Cell.CellType.Formula, currentColumnNumber, currentRowNumber);
            AddNextCell(c, true, style);
        }
 
        #endregion
 
        #region methods_AddCellRange

        public void AddCellRange(IReadOnlyList<object> values, Address startAddress, Address endAddress)
        {
            AddCellRangeInternal(values, startAddress, endAddress, null);
        }

        public void AddCellRange(IReadOnlyList<object> values, Address startAddress, Address endAddress, Style style)
        {
            AddCellRangeInternal(values, startAddress, endAddress, style);
        }

        public void AddCellRange(IReadOnlyList<object> values, string cellRange)
        {
            Range range = Cell.ResolveCellRange(cellRange);
            AddCellRangeInternal(values, range.StartAddress, range.EndAddress, null);
        }

        public void AddCellRange(IReadOnlyList<object> values, string cellRange, Style style)
        {
            Range range = Cell.ResolveCellRange(cellRange);
            AddCellRangeInternal(values, range.StartAddress, range.EndAddress, style);
        }

        public void AddCellRange(IReadOnlyList<object> values, Range cellRange)
        {
            AddCellRangeInternal(values, cellRange.StartAddress, cellRange.EndAddress, null);
        }

        public void AddCellRange(IReadOnlyList<object> values, Range cellRange, Style style)
        {
            AddCellRangeInternal(values, cellRange.StartAddress, cellRange.EndAddress, style);
        }

        private void AddCellRangeInternal<T>(IReadOnlyList<T> values, Address startAddress, Address endAddress, Style style)
        {
            if (values == null)
            {
                throw new RangeException("The passed value list cannot be null");
            }
            List<Address> addresses = Cell.GetCellRange(startAddress, endAddress) as List<Address>;
            if (values.Count != addresses.Count)
            {
                throw new RangeException("The number of passed values (" + values.Count + ") differs from the number of cells within the range (" + addresses.Count + ")");
            }
            List<Cell> list = Cell.ConvertArray(values) as List<Cell>;
            int len = values.Count;
            for (int i = 0; i < len; i++)
            {
                list[i].RowNumber = addresses[i].Row;
                list[i].ColumnNumber = addresses[i].Column;
                AddNextCell(list[i], false, style);
            }
        }
        #endregion
 
        #region methods_RemoveCell
        public bool RemoveCell(int columnNumber, int rowNumber)
        {
            string address = Cell.ResolveCellAddress(columnNumber, rowNumber);
            return cells.Remove(address);
        }

        public bool RemoveCell(string address)
        {
            int row;
            int column;
            Cell.ResolveCellCoordinate(address, out column, out row);
            return RemoveCell(column, row);
        }
        #endregion
 
        #region methods_setStyle

        public void SetStyle(Range cellRange, Style style)
        {
            IReadOnlyList<Address> addresses = cellRange.ResolveEnclosedAddresses();
            foreach (Address address in addresses)
            {
                string key = address.GetAddress();
                if (this.cells.ContainsKey(key))
                {
                    if (style == null)
                    {
                        cells[key].RemoveStyle();
                    }
                    else
                    {
                        cells[key].SetStyle(style);
                    }
                }
                else
                {
                    if (style != null)
                    {
                        AddCell(null, address.Column, address.Row, style);
                    }
                }
            }
        }

        public void SetStyle(Address startAddress, Address endAddress, Style style)
        {
            SetStyle(new Range(startAddress, endAddress), style);
        }

        public void SetStyle(Address address, Style style)
        {
            SetStyle(address, address, style);
        }

        public void SetStyle(string addressExpression, Style style)
        {
            Cell.AddressScope scope = Cell.GetAddressScope(addressExpression);
            if (scope == Cell.AddressScope.SingleAddress)
            {
                Address address = new Address(addressExpression);
                SetStyle(address, style);
            }
            else if (scope == Cell.AddressScope.Range)
            {
                Range range = new Range(addressExpression);
                SetStyle(range, style);
            }
            else
            {
                throw new FormatException("The passed address'" + addressExpression + "' is neither a cell address, nor a range");
            }
        }
 
        #endregion
 
        #region boundaryFunctions
        public int GetFirstColumnNumber()
        {
            return GetBoundaryNumber(false, true);
        }

        public int GetFirstDataColumnNumber()
        {
            return GetBoundaryDataNumber(false, true, true);
        }

        public int GetFirstRowNumber()
        {
            return GetBoundaryNumber(true, true);
        }

        public int GetFirstDataRowNumber()
        {
            return GetBoundaryDataNumber(true, true, true);
        }

        public int GetLastColumnNumber()
        {
            return GetBoundaryNumber(false, false);
        }

        public int GetLastDataColumnNumber()
        {
            return GetBoundaryDataNumber(false, false, true);
        }

        public int GetLastRowNumber()
        {
            return GetBoundaryNumber(true, false);
        }
 

        public int GetLastDataRowNumber()
        {
            return GetBoundaryDataNumber(true, false, true);
        }

 
        public Address? GetLastCellAddress()
        {
            int lastRow = GetLastRowNumber();
            int lastColumn = GetLastColumnNumber();
            if (lastRow < 0 || lastColumn < 0)
            {
                return null;
            }
            return new Address(lastColumn, lastRow);
        }

 
        public Address? GetLastDataCellAddress()
        {
            int lastRow = GetLastDataRowNumber();
            int lastColumn = GetLastDataColumnNumber();
            if (lastRow < 0 || lastColumn < 0)
            {
                return null;
            }
            return new Address(lastColumn, lastRow);
        }

        public Address? GetFirstCellAddress()
        {
            int firstRow = GetFirstRowNumber();
            int firstColumn = GetFirstColumnNumber();
            if (firstRow < 0 || firstColumn < 0)
            {
                return null;
            }
            return new Address(firstColumn, firstRow);
        }

        public Address? GetFirstDataCellAddress()
        {
            int firstRow = GetFirstDataRowNumber();
            int firstColumn = GetFirstDataColumnNumber();
            if (firstRow < 0 || firstColumn < 0)
            {
                return null;
            }
            return new Address(firstColumn, firstRow);
        }

        private int GetBoundaryDataNumber(bool row, bool min, bool ignoreEmpty)
        {
            if (cells.Count == 0)
            {
                return -1;
            }
            if (!ignoreEmpty)
            {
                if (row && min)
                {
                    return cells.Min(x => x.Value.RowNumber);
                }
                else if (row)
                {
                    return cells.Max(x => x.Value.RowNumber);
                }
                else if (min)
                {
                    return cells.Min(x => x.Value.ColumnNumber);
                }
                else
                {
                    return cells.Max(x => x.Value.ColumnNumber);
                }
            }
            List<Cell> nonEmptyCells = cells.Values.Where(x => x.Value != null && x.Value.ToString() != string.Empty).ToList();
            if (nonEmptyCells.Count == 0)
            {
                return -1;
            }
            if (row && min)
            {
                return nonEmptyCells.Min(x => x.RowNumber);
            }
            else if (row)
            {
                return nonEmptyCells.Max(x => x.RowNumber);
            }
            else if (min)
            {
                return nonEmptyCells.Min(x => x.ColumnNumber);
            }
            else
            {
                return nonEmptyCells.Max(x => x.ColumnNumber);
            }
        }

        private int GetBoundaryNumber(bool row, bool min)
        {
            int cellBoundary = GetBoundaryDataNumber(row, min, false);
            if (row)
            {
                int heightBoundary = -1;
                if (rowHeights.Count > 0)
                {
                    heightBoundary = min ? RowHeights.Min(x => x.Key) : RowHeights.Max(x => x.Key);
                }
                int hiddenBoundary = -1;
                if (hiddenRows.Count > 0)
                {
                    hiddenBoundary = min ? HiddenRows.Min(x => x.Key) : HiddenRows.Max(x => x.Key);
                }
                return min ? GetMinRow(cellBoundary, heightBoundary, hiddenBoundary) : GetMaxRow(cellBoundary, heightBoundary, hiddenBoundary);
            }
            else
            {
                int columnDefBoundary = -1;
                if (columns.Count > 0)
                {
                    columnDefBoundary = min ? Columns.Min(x => x.Key) : Columns.Max(x => x.Key);
                }
                if (min)
                {
                    return cellBoundary >= 0 && cellBoundary < columnDefBoundary ? cellBoundary : columnDefBoundary;
                }
                else
                {
                    return cellBoundary >= 0 && cellBoundary > columnDefBoundary ? cellBoundary : columnDefBoundary;
                }
            }
        }

        private static int GetMaxRow(int cellBoundary, int heightBoundary, int hiddenBoundary)
        {
            int highest = -1;
            if (cellBoundary >= 0)
            {
                highest = cellBoundary;
            }
            if (heightBoundary >= 0 && heightBoundary > highest)
            {
                highest = heightBoundary;
            }
            if (hiddenBoundary >= 0 && hiddenBoundary > highest)
            {
                highest = hiddenBoundary;
            }
            return highest;
        }

        private static int GetMinRow(int cellBoundary, int heightBoundary, int hiddenBoundary)
        {
            int lowest = int.MaxValue;
            if (cellBoundary >= 0)
            {
                lowest = cellBoundary;
            }
            if (heightBoundary >= 0 && heightBoundary < lowest)
            {
                lowest = heightBoundary;
            }
            if (hiddenBoundary >= 0 && hiddenBoundary < lowest)
            {
                lowest = hiddenBoundary;
            }
            return lowest == int.MaxValue ? -1 : lowest;
        }
        #endregion
 
        #region Insert-Search-Replace

        public void InsertRow(int rowNumber, int numberOfNewRows)
        {
            // All cells below the first row must receive a new address (row + count);
            var upperRow = this.GetRow(rowNumber);
 
            // Identify all cells below the insertion point to adjust their addresses
            var cellsToChange = this.Cells.Where(c => c.Value.CellAddress2.Row > rowNumber).ToList();
 
            // Make a copy of the cells to be moved and then delete the original cells;
            Dictionary<string, Cell> newCells = new Dictionary<string, Cell>();
            foreach (var cell in cellsToChange)
            {
                var row = cell.Value.CellAddress2.Row;
                var col = cell.Value.CellAddress2.Column;
                Address newAddress = new Address(col, row + numberOfNewRows);
 
                Cell newCell = new Cell(cell.Value.Value, cell.Value.DataType, newAddress);
                if (cell.Value.CellStyle != null)
                {
                    newCell.SetStyle(cell.Value.CellStyle); // Apply the style from the "old" cell.
                }
                newCells.Add(newAddress.GetAddress(), newCell);
 
                // Delete the original cells since the key cannot be changed.
                this.Cells.Remove(cell.Key);
            }
 
            // Fill the gap with new cells, using the same style as the first row.
            foreach (Cell cell in upperRow)
            {
                for (int i = 0; i < numberOfNewRows; i++)
                {
                    Address newAddress = new Address(cell.CellAddress2.Column, cell.CellAddress2.Row + 1 + i);
                    Cell newCell = new Cell(null, Cell.CellType.Empty, newAddress);
                    if (cell.CellStyle != null)
                        newCell.SetStyle(cell.CellStyle);
                    this.Cells.Add(newAddress.GetAddress(), newCell);
                }
            }
 
            // Re-add the previous cells from the copy back with a new key.
            foreach (KeyValuePair<string, Cell> cellKeyValue in newCells)
            {
                this.Cells.Add(cellKeyValue.Key, cellKeyValue.Value);  //cell.Value is the cell incl. Style etc.
            }
        }

        public void InsertColumn(int columnNumber, int numberOfNewColumns)
        {
            var leftColumn = this.GetColumn(columnNumber);
            var cellsToChange = this.Cells.Where(c => c.Value.CellAddress2.Column > columnNumber).ToList();
 
            Dictionary<string, Cell> newCells = new Dictionary<string, Cell>();
            foreach (var cell in cellsToChange)
            {
                var row = cell.Value.CellAddress2.Row;
                var col = cell.Value.CellAddress2.Column;
                Address newAddress = new Address(col + numberOfNewColumns, row);
 
                Cell newCell = new Cell(cell.Value.Value, cell.Value.DataType, newAddress);
                if (cell.Value.CellStyle != null)
                {
                    newCell.SetStyle(cell.Value.CellStyle); // Apply the style from the "old" cell.
                }
                newCells.Add(newAddress.GetAddress(), newCell);
 
                // Delete the original cells since the key cannot be changed.
                this.Cells.Remove(cell.Key);
            }
 
            // Fill the gap with new cells, using the same style as the first row.
            foreach (Cell cell in leftColumn)
            {
                for (int i = 0; i < numberOfNewColumns; i++)
                {
                    Address newAddress = new Address(cell.CellAddress2.Column + 1 + i, cell.CellAddress2.Row);
                    Cell newCell = new Cell(null, Cell.CellType.Empty, newAddress);
                    if (cell.CellStyle != null)
                        newCell.SetStyle(cell.CellStyle);
                    this.Cells.Add(newAddress.GetAddress(), newCell);
                }
            }
 
            // Re-add the previous cells from the copy back with a new key.
            foreach (KeyValuePair<string, Cell> cellKeyValue in newCells)
            {
                this.Cells.Add(cellKeyValue.Key, cellKeyValue.Value);  //cell.Value is the cell incl. Style etc.
            }
        }

        public Cell FirstCellByValue(object searchValue)
        {
            var cell = this.Cells.FirstOrDefault(c =>
                Equals(c.Value.Value, searchValue))
                .Value;
            return cell;
        }

        public Cell FirstOrDefaultCell(Func<Cell, bool> predicate)
        {
            return this.Cells.Values
                .FirstOrDefault(c => c != null && (c.Value == null || predicate(c)));
        }

        public List<Cell> CellsByValue(object searchValue)
        {
            return this.Cells.Where(c =>
                Equals(c.Value.Value, searchValue))
                .Select(c => c.Value)
                .ToList();
        }

        public int ReplaceCellValue(object oldValue, object newValue)
        {
            int count = 0;
            List<Cell> foundCells = this.CellsByValue(oldValue);
            foreach (var cell in foundCells)
            {
                cell.Value = newValue;
                count++;
            }
            return count;
        }
        #endregion
 
        #region common_methods

        public void AddAllowedActionOnSheetProtection(SheetProtectionValue typeOfProtection)
        {
            if (!sheetProtectionValues.Contains(typeOfProtection))
            {
                if (typeOfProtection == SheetProtectionValue.SelectLockedCells && !sheetProtectionValues.Contains(SheetProtectionValue.SelectUnlockedCells))
                {
                    sheetProtectionValues.Add(SheetProtectionValue.SelectUnlockedCells);
                }
                sheetProtectionValues.Add(typeOfProtection);
                UseSheetProtection = true;
            }
        }

        public void AddHiddenColumn(int columnNumber)
        {
            SetColumnHiddenState(columnNumber, true);
        }

        public void AddHiddenColumn(string columnAddress)
        {
            int columnNumber = Cell.ResolveColumn(columnAddress);
            SetColumnHiddenState(columnNumber, true);
        }

        public void AddHiddenRow(int rowNumber)
        {
            SetRowHiddenState(rowNumber, true);
        }

        public void ClearActiveStyle()
        {
            useActiveStyle = false;
            activeStyle = null;
        }

        public Cell GetCell(Address address)
        {
            if (!cells.ContainsKey(address.GetAddress()))
            {
                throw new WorksheetException("The cell with the address " + address.GetAddress() + " does not exist in this worksheet");
            }
            return cells[address.GetAddress()];
        }

        public Cell GetCell(int columnNumber, int rowNumber)
        {
            return GetCell(new Address(columnNumber, rowNumber));
        }

        public bool HasCell(Address address)
        {
            return cells.ContainsKey(address.GetAddress());
        }

        public bool HasCell(int columnNumber, int rowNumber)
        {
            return HasCell(new Address(columnNumber, rowNumber));
        }

        public void ResetColumn(int columnNumber)
        {
            if (columns.TryGetValue(columnNumber, out var value) && !value.HasAutoFilter) // AutoFilters cannot have gaps 
            {
                columns.Remove(columnNumber);
            }
            else if (columns.TryGetValue(columnNumber, out var value2))
            {
                value2.IsHidden = false;
                value2.Width = DefaultWorksheetColumnWidth;
            }
        }

        public IReadOnlyList<Cell> GetRow(int rowNumber)
        {
            List<Cell> list = new List<Cell>();
            foreach (KeyValuePair<string, Cell> cell in cells)
            {
                if (cell.Value.RowNumber == rowNumber)
                {
                    list.Add(cell.Value);
                }
            }
            list.Sort((c1, c2) => (c1.ColumnNumber.CompareTo(c2.ColumnNumber))); // Lambda sort
            return list;
        }

        public IReadOnlyList<Cell> GetColumn(string columnAddress)
        {
            int column = Cell.ResolveColumn(columnAddress);
            return GetColumn(column);
        }

        public IReadOnlyList<Cell> GetColumn(int columnNumber)
        {
            List<Cell> list = new List<Cell>();
            foreach (KeyValuePair<string, Cell> cell in cells)
            {
                if (cell.Value.ColumnNumber == columnNumber)
                {
                    list.Add(cell.Value);
                }
            }
            list.Sort((c1, c2) => (c1.RowNumber.CompareTo(c2.RowNumber))); // Lambda sort
            return list;
        }

        public int GetCurrentColumnNumber()
        {
            return currentColumnNumber;
        }

        public int GetCurrentRowNumber()
        {
            return currentRowNumber;
        }

        public void GoToNextColumn()
        {
            currentColumnNumber++;
            currentRowNumber = 0;
            Cell.ValidateColumnNumber(currentColumnNumber);
        }

        public void GoToNextColumn(int numberOfColumns, bool keepRowPosition = false)
        {
            currentColumnNumber += numberOfColumns;
            if (!keepRowPosition)
            {
                currentRowNumber = 0;
            }
            Cell.ValidateColumnNumber(currentColumnNumber);
        }

        public void GoToNextRow()
        {
            currentRowNumber++;
            currentColumnNumber = 0;
            Cell.ValidateRowNumber(currentRowNumber);
        }

        public void GoToNextRow(int numberOfRows, bool keepColumnPosition = false)
        {
            currentRowNumber += numberOfRows;
            if (!keepColumnPosition)
            {
                currentColumnNumber = 0;
            }
            Cell.ValidateRowNumber(currentRowNumber);
        }

        public string MergeCells(Range cellRange)
        {
            return MergeCells(cellRange.StartAddress, cellRange.EndAddress);
        }

        public string MergeCells(string cellRange)
        {
            Range range = Cell.ResolveCellRange(cellRange);
            return MergeCells(range.StartAddress, range.EndAddress);
        }

        public string MergeCells(Address startAddress, Address endAddress)
        {
            string key = startAddress + ":" + endAddress;
            Range value = new Range(startAddress, endAddress);
            IReadOnlyList<Address> result = value.ResolveEnclosedAddresses();
            foreach (KeyValuePair<string, Range> item in mergedCells)
            {
                if (item.Value.ResolveEnclosedAddresses().Intersect(result).Any())
                {
                    throw new RangeException("The passed range: " + value.ToString() + " contains cells that are already in the defined merge range: " + item.Key);
                }
            }
            mergedCells.Add(key, value);
            return key;
        }

        internal void RecalculateAutoFilter()
        {
            if (autoFilterRange == null)
            { return; }
            int start = autoFilterRange.Value.StartAddress.Column;
            int end = autoFilterRange.Value.EndAddress.Column;
            int endRow = 0;
            foreach (KeyValuePair<string, Cell> item in Cells)
            {
                if (item.Value.ColumnNumber < start || item.Value.ColumnNumber > end)
                { continue; }
                if (item.Value.RowNumber > endRow)
                { endRow = item.Value.RowNumber; }
            }
            Column c;
            for (int i = start; i <= end; i++)
            {
                if (!columns.TryGetValue(i, out var value))
                {
                    c = new Column(i)
                    {
                        HasAutoFilter = true
                    };
                    columns.Add(i, c);
                }
                else
                {
                    value.HasAutoFilter = true;
                }
            }
            autoFilterRange = new Range(start, 0, end, endRow);
        }

        internal void RecalculateColumns()
        {
            List<int> columnsToDelete = new List<int>();
            foreach (KeyValuePair<int, Column> col in columns)
            {
                if (!col.Value.HasAutoFilter && !col.Value.IsHidden && Comparators.CompareDimensions(col.Value.Width, DefaultWorksheetColumnWidth) == 0 && col.Value.DefaultColumnStyle == null)
                {
                    columnsToDelete.Add(col.Key);
                }
            }
            foreach (int index in columnsToDelete)
            {
                columns.Remove(index);
            }
        }

        internal void ResolveMergedCells()
        {
            Style mergeStyle = BasicStyles.MergeCellStyle;
            Cell cell;
            foreach (KeyValuePair<string, Range> range in MergedCells)
            {
                int pos = 0;
                List<Address> addresses = Cell.GetCellRange(range.Value.StartAddress, range.Value.EndAddress) as List<Address>;
                foreach (Address address in addresses)
                {
                    if (!Cells.ContainsKey(address.GetAddress()))
                    {
                        cell = new Cell
                        {
                            DataType = Cell.CellType.Empty,
                            RowNumber = address.Row,
                            ColumnNumber = address.Column
                        };
                        AddCell(cell, cell.ColumnNumber, cell.RowNumber);
                    }
                    else
                    {
                        cell = Cells[address.GetAddress()];
                    }
                    if (pos != 0)
                    {
                        cell.DataType = Cell.CellType.Empty;
                        if (cell.CellStyle == null)
                        {
                            cell.SetStyle(mergeStyle);
                        }
                        else
                        {
                            Style mixedMergeStyle = cell.CellStyle;
                            // TODO: There should be a better possibility to identify particular style elements that deviates
                            mixedMergeStyle.CurrentCellXf.ForceApplyAlignment = mergeStyle.CurrentCellXf.ForceApplyAlignment;
                            cell.SetStyle(mixedMergeStyle);
                        }
                    }
                    pos++;
                }
            }
        }

        public void RemoveAutoFilter()
        {
            autoFilterRange = null;
        }

        public void RemoveHiddenColumn(int columnNumber)
        {
            SetColumnHiddenState(columnNumber, false);
        }

        public void RemoveHiddenColumn(string columnAddress)
        {
            int columnNumber = Cell.ResolveColumn(columnAddress);
            SetColumnHiddenState(columnNumber, false);
        }

        public void RemoveHiddenRow(int rowNumber)
        {
            SetRowHiddenState(rowNumber, false);
        }

        public void RemoveMergedCells(string range)
        {
            range = ParserUtils.ToUpper(range);
            if (range == null || !mergedCells.ContainsKey(range))
            {
                throw new RangeException("The cell range " + range + " was not found in the list of merged cell ranges");
            }
 
            List<Address> addresses = Cell.GetCellRange(range) as List<Address>;
            foreach (Address address in addresses)
            {
                if (cells.ContainsKey(address.GetAddress()))
                {
                    Cell cell = cells[address.GetAddress()];
                    if (BasicStyles.MergeCellStyle.Equals(cell.CellStyle))
                    {
                        cell.RemoveStyle();
                    }
                    cell.ResolveCellType(); // resets the type
                }
            }
            mergedCells.Remove(range);
        }

        public void RemoveRowHeight(int rowNumber)
        {
            if (rowHeights.ContainsKey(rowNumber))
            {
                rowHeights.Remove(rowNumber);
            }
        }

        public void RemoveAllowedActionOnSheetProtection(SheetProtectionValue value)
        {
            if (sheetProtectionValues.Contains(value))
            {
                sheetProtectionValues.Remove(value);
            }
        }

        public void SetActiveStyle(Style style)
        {
            if (style == null)
            {
                useActiveStyle = false;
            }
            else
            {
                useActiveStyle = true;
            }
            activeStyle = style;
        }

        public void SetAutoFilter(int startColumn, int endColumn)
        {
            string start = Cell.ResolveCellAddress(startColumn, 0);
            string end = Cell.ResolveCellAddress(endColumn, 0);
            if (endColumn < startColumn)
            {
                SetAutoFilter(end + ":" + start);
            }
            else
            {
                SetAutoFilter(start + ":" + end);
            }
        }

        public void SetAutoFilter(string range)
        {
            autoFilterRange = Cell.ResolveCellRange(range);
            RecalculateAutoFilter();
            RecalculateColumns();
        }

        private void SetColumnHiddenState(int columnNumber, bool state)
        {
            Cell.ValidateColumnNumber(columnNumber);
            if (columns.TryGetValue(columnNumber, out var value))
            {
                value.IsHidden = state;
            }
            else if (state)
            {
                Column c = new Column(columnNumber)
                {
                    IsHidden = true
                };
                columns.Add(columnNumber, c);
            }
            if (!columns[columnNumber].IsHidden && Comparators.CompareDimensions(columns[columnNumber].Width, DefaultWorksheetColumnWidth) == 0 && !columns[columnNumber].HasAutoFilter)
            {
                columns.Remove(columnNumber);
            }
        }

        public void SetColumnWidth(string columnAddress, float width)
        {
            int columnNumber = Cell.ResolveColumn(columnAddress);
            SetColumnWidth(columnNumber, width);
        }

        public void SetColumnWidth(int columnNumber, float width)
        {
            Cell.ValidateColumnNumber(columnNumber);
            if (width < MinColumnWidth || width > MaxColumnWidth)
            {
                throw new RangeException("The column width (" + width + ") is out of range. Range is from " + MinColumnWidth + " to " + MaxColumnWidth + " (chars).");
            }
            if (columns.TryGetValue(columnNumber, out var value))
            {
                value.Width = width;
            }
            else
            {
                Column c = new Column(columnNumber)
                {
                    Width = width
                };
                columns.Add(columnNumber, c);
            }
        }

        public Style SetColumnDefaultStyle(string columnAddress, Style style)
        {
            int columnNumber = Cell.ResolveColumn(columnAddress);
            return SetColumnDefaultStyle(columnNumber, style);
        }

        public Style SetColumnDefaultStyle(int columnNumber, Style style)
        {
            Cell.ValidateColumnNumber(columnNumber);
            if (this.columns.TryGetValue(columnNumber, out var value))
            {
                return value.SetDefaultColumnStyle(style);
            }
            else
            {
                Column c = new Column(columnNumber);
                Style returnStyle = c.SetDefaultColumnStyle(style);
                this.columns.Add(columnNumber, c);
                return returnStyle;
            }
        }

        public void SetCurrentCellAddress(int columnNumber, int rowNumber)
        {
            SetCurrentColumnNumber(columnNumber);
            SetCurrentRowNumber(rowNumber);
        }

        public void SetCurrentCellAddress(string address)
        {
            int row;
            int column;
            Cell.ResolveCellCoordinate(address, out column, out row);
            SetCurrentCellAddress(column, row);
        }

        public void SetCurrentColumnNumber(int columnNumber)
        {
            Cell.ValidateColumnNumber(columnNumber);
            currentColumnNumber = columnNumber;
        }

        public void SetCurrentRowNumber(int rowNumber)
        {
            Cell.ValidateRowNumber(rowNumber);
            currentRowNumber = rowNumber;
        }

        public void AddSelectedCells(Range range)
        {
            selectedCells = DataUtils.MergeRange(selectedCells, range).ToList();
        }

        public void AddSelectedCells(Address startAddress, Address endAddress)
        {
            AddSelectedCells(new Range(startAddress, endAddress));
        }

        public void AddSelectedCells(string rangeOrAddress)
        {
            Range? resolved = ParseRange(rangeOrAddress);
            if (resolved != null)
            {
                AddSelectedCells(resolved.Value);
            }
        }

        public void AddSelectedCells(Address address)
        {
            AddSelectedCells(new Range(address, address));
        }

        public void ClearSelectedCells()
        {
            selectedCells.Clear();
        }

        public void RemoveSelectedCells(Range range)
        {
            selectedCells = DataUtils.SubtractRange(selectedCells, range).ToList();
        }

        public void RemoveSelectedCells(String rangeOrAddress)
        {
            Range? resolved = ParseRange(rangeOrAddress);
            if (resolved != null)
            {
                RemoveSelectedCells(resolved.Value);
            }
        }

        public void RemoveSelectedCells(Address startAddress, Address endAddress)
        {
            RemoveSelectedCells(new Range(startAddress, endAddress));
        }

        public void RemoveSelectedCells(Address address)
        {
            RemoveSelectedCells(new Range(address, address));
        }

        public void SetSheetProtectionPassword(string password)
        {
            if (string.IsNullOrEmpty(password))
            {
                sheetProtectionPassword.UnsetPassword();
                UseSheetProtection = false;
            }
            else
            {
                sheetProtectionPassword.SetPassword(password);
                UseSheetProtection = true;
            }
        }

        public void SetRowHeight(int rowNumber, float height)
        {
            Cell.ValidateRowNumber(rowNumber);
            if (height < MinRowHeight || height > MaxRowHeight)
            {
                throw new RangeException("The row height (" + height + ") is out of range. Range is from " + MinRowHeight + " to " + MaxRowHeight + " (equals 546px).");
            }
            if (rowHeights.ContainsKey(rowNumber))
            {
                rowHeights[rowNumber] = height;
            }
            else
            {
                rowHeights.Add(rowNumber, height);
            }
        }

        private void SetRowHiddenState(int rowNumber, bool state)
        {
            Cell.ValidateRowNumber(rowNumber);
            if (hiddenRows.ContainsKey(rowNumber))
            {
                if (state)
                {
                    hiddenRows[rowNumber] = true;
                }
                else
                {
                    hiddenRows.Remove(rowNumber);
                }
            }
            else if (state)
            {
                hiddenRows.Add(rowNumber, true);
            }
        }

        public void SetSheetName(string name)
        {
            if (string.IsNullOrEmpty(name))
            {
                throw new FormatException("the worksheet name must be between 1 and " + MaxWorksheetNameLength + " characters");
            }
            if (name.Length > MaxWorksheetNameLength)
            {
                throw new FormatException("the worksheet name must be between 1 and " + MaxWorksheetNameLength + " characters");
            }
            Regex regex = new Regex(@"[\‍[\‍]\*\?/\\‍]");
            Match match = regex.Match(name);
            if (match.Captures.Count > 0)
            {
                throw new FormatException(@"the worksheet name must not contain the characters [  ]  * ? / \ ");
            }
            sheetName = name;
        }

        public void SetSheetName(string name, bool sanitize)
        {
            if (sanitize)
            {
                sheetName = ""; // Empty name (temporary) to prevent conflicts during sanitizing
                sheetName = SanitizeWorksheetName(name, workbookReference);
            }
            else
            {
                SetSheetName(name);
            }
        }

        public void SetHorizontalSplit(float topPaneHeight, Address topLeftCell, WorksheetPane? activePane)
        {
            SetSplit(null, topPaneHeight, topLeftCell, activePane);
        }

        public void SetHorizontalSplit(int numberOfRowsFromTop, bool freeze, Address topLeftCell, WorksheetPane? activePane)
        {
            SetSplit(null, numberOfRowsFromTop, freeze, topLeftCell, activePane);
        }

        public void SetVerticalSplit(float leftPaneWidth, Address topLeftCell, WorksheetPane? activePane)
        {
            SetSplit(leftPaneWidth, null, topLeftCell, activePane);
        }

        public void SetVerticalSplit(int numberOfColumnsFromLeft, bool freeze, Address topLeftCell, WorksheetPane? activePane)
        {
            SetSplit(numberOfColumnsFromLeft, null, freeze, topLeftCell, activePane);
        }

        public void SetSplit(int? numberOfColumnsFromLeft, int? numberOfRowsFromTop, bool freeze, Address topLeftCell, WorksheetPane? activePane)
        {
            if (freeze)
            {
                if (numberOfColumnsFromLeft != null && topLeftCell.Column < numberOfColumnsFromLeft.Value)
                {
                    throw new WorksheetException("The column number " + topLeftCell.Column +
                        " is not valid for a frozen, vertical split with the split pane column number " + numberOfColumnsFromLeft.Value);
                }
                if (numberOfRowsFromTop != null && topLeftCell.Row < numberOfRowsFromTop.Value)
                {
                    throw new WorksheetException("The row number " + topLeftCell.Row +
                        " is not valid for a frozen, horizontal split height the split pane row number " + numberOfRowsFromTop.Value);
                }
            }
            this.paneSplitLeftWidth = null;
            this.paneSplitTopHeight = null;
            this.freezeSplitPanes = freeze;
            int row = numberOfRowsFromTop != null ? numberOfRowsFromTop.Value : 0;
            int column = numberOfColumnsFromLeft != null ? numberOfColumnsFromLeft.Value : 0;
            this.paneSplitAddress = new Address(column, row);
            this.paneSplitTopLeftCell = topLeftCell;
            this.activePane = activePane;
        }

        public void SetSplit(float? leftPaneWidth, float? topPaneHeight, Address topLeftCell, WorksheetPane? activePane)
        {
            this.paneSplitLeftWidth = leftPaneWidth;
            this.paneSplitTopHeight = topPaneHeight;
            this.freezeSplitPanes = null;
            this.paneSplitAddress = null;
            this.paneSplitTopLeftCell = topLeftCell;
            this.activePane = activePane;
        }

        public void ResetSplit()
        {
            this.paneSplitLeftWidth = null;
            this.paneSplitTopHeight = null;
            this.freezeSplitPanes = null;
            this.paneSplitAddress = null;
            this.paneSplitTopLeftCell = null;
            this.activePane = null;
        }

        public Worksheet Copy()
        {
            Worksheet copy = new Worksheet();
            foreach (KeyValuePair<string, Cell> cell in this.cells)
            {
                copy.AddCell(cell.Value.Copy(), cell.Key);
            }
            copy.activePane = this.activePane;
            copy.activeStyle = this.activeStyle;
            if (this.autoFilterRange.HasValue)
            {
                copy.autoFilterRange = this.autoFilterRange.Value.Copy();
            }
            foreach (KeyValuePair<int, Column> column in this.columns)
            {
                copy.columns.Add(column.Key, column.Value.Copy());
            }
            copy.CurrentCellDirection = this.CurrentCellDirection;
            copy.currentColumnNumber = this.currentColumnNumber;
            copy.currentRowNumber = this.currentRowNumber;
            copy.defaultColumnWidth = this.defaultColumnWidth;
            copy.defaultRowHeight = this.defaultRowHeight;
            copy.freezeSplitPanes = this.freezeSplitPanes;
            copy.hidden = this.hidden;
            foreach (KeyValuePair<int, bool> row in this.hiddenRows)
            {
                copy.hiddenRows.Add(row.Key, row.Value);
            }
            foreach (KeyValuePair<string, Range> cell in this.mergedCells)
            {
                copy.mergedCells.Add(cell.Key, cell.Value.Copy());
            }
            if (this.paneSplitAddress.HasValue)
            {
                copy.paneSplitAddress = this.paneSplitAddress.Value.Copy();
            }
            copy.paneSplitLeftWidth = this.paneSplitLeftWidth;
            copy.paneSplitTopHeight = this.paneSplitTopHeight;
            if (this.paneSplitTopLeftCell.HasValue)
            {
                copy.paneSplitTopLeftCell = this.paneSplitTopLeftCell.Value.Copy();
            }
            foreach (KeyValuePair<int, float> row in this.rowHeights)
            {
                copy.rowHeights.Add(row.Key, row.Value);
            }
            foreach (Range range in selectedCells)
            {
                copy.AddSelectedCells(range);
            }
            copy.sheetProtectionPassword.CopyFrom(this.sheetProtectionPassword);
            foreach (SheetProtectionValue value in this.sheetProtectionValues)
            {
                copy.sheetProtectionValues.Add(value);
            }
            copy.useActiveStyle = this.useActiveStyle;
            copy.UseSheetProtection = this.UseSheetProtection;
            copy.ShowGridLines = this.ShowGridLines;
            copy.ShowRowColumnHeaders = this.ShowRowColumnHeaders;
            copy.ShowRuler = this.ShowRuler;
            copy.ViewType = this.ViewType;
            copy.zoomFactor.Clear();
            foreach (KeyValuePair<SheetViewType, int> zoomFactor in this.zoomFactor)
            {
                copy.SetZoomFactor(zoomFactor.Key, zoomFactor.Value);
            }
            return copy;
        }

        public void SetZoomFactor(SheetViewType sheetViewType, int zoomFactor)
        {
            if (zoomFactor != AutoZoomFactor && (zoomFactor < MinZoomFactor || zoomFactor > maxZoomFactor))
            {
                throw new WorksheetException("The zoom factor " + zoomFactor + " is not valid. Valid are values between " + MinZoomFactor + " and " + maxZoomFactor + ", or " + AutoZoomFactor + " (automatic)");
            }
            if (this.zoomFactor.ContainsKey(sheetViewType))
            {
                this.zoomFactor[sheetViewType] = zoomFactor;
            }
            else
            {
                this.zoomFactor.Add(sheetViewType, zoomFactor);
            }
        }
 
 
 
        #region static_methods
        public static string SanitizeWorksheetName(string input, Workbook workbook)
        {
            if (string.IsNullOrEmpty(input))
            {
                input = "Sheet1";
            }
            int len;
            if (input.Length > MaxWorksheetNameLength)
            {
                len = MaxWorksheetNameLength;
            }
            else
            {
                len = input.Length;
            }
            StringBuilder sb = new StringBuilder(MaxWorksheetNameLength);
            char c;
            for (int i = 0; i < len; i++)
            {
                c = input[i];
                if (c == '[' || c == ']' || c == '*' || c == '?' || c == '\\' || c == '/')
                { sb.Append('_'); }
                else
                { sb.Append(c); }
            }
            return GetUnusedWorksheetName(sb.ToString(), workbook);
        }

        private static Range? ParseRange(string rangeOrAddress)
        {
            if (string.IsNullOrEmpty(rangeOrAddress))
            {
                return null;
            }
            Range range;
            if (rangeOrAddress.Contains(":"))
            {
                range = Cell.ResolveCellRange(rangeOrAddress);
            }
            else
            {
                Address address = Cell.ResolveCellCoordinate(rangeOrAddress);
                range = new Range(address, address);
            }
            return range;
        }

        private static string GetUnusedWorksheetName(string name, Workbook workbook)
        {
            if (workbook == null)
            {
                throw new WorksheetException("The workbook reference is null");
            }
            if (!WorksheetExists(name, workbook))
            { return name; }
            Regex regex = new Regex(@"^(.*?)(\d{1,31})$");
            Match match = regex.Match(name);
            string prefix = name;
            int number = 1;
            if (match.Groups.Count > 1)
            {
                prefix = match.Groups[1].Value;
                _ = int.TryParse(match.Groups[2].Value, out number);
                // if this failed, the start number is 0 (parsed number was >max. int32)
            }
            while (true)
            {
                string numberString = ParserUtils.ToString(number);
                if (numberString.Length + prefix.Length > MaxWorksheetNameLength)
                {
                    int endIndex = prefix.Length - (numberString.Length + prefix.Length - MaxWorksheetNameLength);
                    prefix = prefix.Substring(0, endIndex);
                }
                string newName = prefix + numberString;
                if (!WorksheetExists(newName, workbook))
                { return newName; }
                number++;
            }
        }

        private static bool WorksheetExists(string name, Workbook workbook)
        {
            int len = workbook.Worksheets.Count;
            for (int i = 0; i < len; i++)
            {
                if (workbook.Worksheets[i].SheetName == name)
                {
                    return true;
                }
            }
            return false;
        }

        internal static string GetSheetProtectionName(SheetProtectionValue protection)
        {
            string output = "";
            switch (protection)
            {
                case SheetProtectionValue.Objects: output = "objects"; break;
                case SheetProtectionValue.Scenarios: output = "scenarios"; break;
                case SheetProtectionValue.FormatCells: output = "formatCells"; break;
                case SheetProtectionValue.FormatColumns: output = "formatColumns"; break;
                case SheetProtectionValue.FormatRows: output = "formatRows"; break;
                case SheetProtectionValue.InsertColumns: output = "insertColumns"; break;
                case SheetProtectionValue.InsertRows: output = "insertRows"; break;
                case SheetProtectionValue.InsertHyperlinks: output = "insertHyperlinks"; break;
                case SheetProtectionValue.DeleteColumns: output = "deleteColumns"; break;
                case SheetProtectionValue.DeleteRows: output = "deleteRows"; break;
                case SheetProtectionValue.SelectLockedCells: output = "selectLockedCells"; break;
                case SheetProtectionValue.Sort: output = "sort"; break;
                case SheetProtectionValue.AutoFilter: output = "autoFilter"; break;
                case SheetProtectionValue.PivotTables: output = "pivotTables"; break;
                case SheetProtectionValue.SelectUnlockedCells: output = "selectUnlockedCells"; break;
            }
            return output;
        }

        internal static WorksheetPane? GetWorksheetPaneEnum(string pane)
        {
            WorksheetPane? output = null;
            switch (pane)
            {
                case "topLeft": output = WorksheetPane.TopLeft; break;
                case "topRight": output = WorksheetPane.TopRight; break;
                case "bottomLeft": output = WorksheetPane.BottomLeft; break;
                case "bottomRight": output = WorksheetPane.BottomRight; break;
            }
            return output;
        }

        internal static SheetViewType GetSheetViewTypeEnum(string viewType)
        {
            SheetViewType output = SheetViewType.Normal;
            switch (viewType)
            {
                case "pageBreakPreview": output = SheetViewType.PageBreakPreview; break;
                case "pageLayout": output = SheetViewType.PageLayout; break;
            }
            return output;
        }
        #endregion
        #endregion
 
    }
}