Workbook.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.Collections.Generic;
using System.Linq;
using NanoXLSX.Colors;
using NanoXLSX.Exceptions;
using NanoXLSX.Interfaces;
using NanoXLSX.Internal;
using NanoXLSX.Registry;
using NanoXLSX.Themes;
using NanoXLSX.Utils;
 
namespace NanoXLSX
{
    public class Workbook
    {
        static Workbook()
        {
            PlugInLoader.Initialize();
        }
 
        #region privateFields
        private string filename;
        private List<Worksheet> worksheets;
        private Worksheet currentWorksheet;
        private Metadata workbookMetadata;
        private IPassword workbookProtectionPassword;
        private bool lockWindowsIfProtected;
        private bool lockStructureIfProtected;
        private int selectedWorksheet;
        private Shortener shortener;
        private readonly List<Color> mruColors = new List<Color>();
        private readonly List<DefinedName> definedNames = new List<DefinedName>();
        internal bool importInProgress; // Used by NanoXLSX.Reader
        #endregion
 
        #region properties

        internal AuxiliaryData AuxiliaryData { get; private set; }

        public Shortener WS
        {
            get { return shortener; }
        }
 

        public Worksheet CurrentWorksheet
        {
            get { return currentWorksheet; }
        }

        public string Filename
        {
            get { return filename; }
            set { filename = value; }
        }

        public bool LockStructureIfProtected
        {
            get { return lockStructureIfProtected; }
        }

        public bool LockWindowsIfProtected
        {
            get { return lockWindowsIfProtected; }
        }

        public Metadata WorkbookMetadata
        {
            get { return workbookMetadata; }
            set { workbookMetadata = value; }
        }

        public int SelectedWorksheet
        {
            get { return selectedWorksheet; }
        }

        public bool UseWorkbookProtection { get; set; }

        public virtual IPassword WorkbookProtectionPassword { get { return workbookProtectionPassword; } internal set => workbookProtectionPassword = value; }

        public List<Worksheet> Worksheets
        {
            get { return worksheets; }
        }
 

        public bool Hidden { get; set; }

        public Theme WorkbookTheme { get; set; } = Theme.GetDefaultTheme();

        internal FeatureSet Features { get; } = new FeatureSet() { };
 
        #endregion
 
        #region constructors
        public Workbook()
        {
            Init();
        }

        public Workbook(bool createWorkSheet)
        {
            Init();
            if (createWorkSheet)
            {
                AddWorksheet("Sheet1");
            }
        }

        public Workbook(string sheetName)
        {
            Init();
            AddWorksheet(sheetName, true);
        }

        public Workbook(string filename, string sheetName)
        {
            Init();
            this.filename = filename;
            AddWorksheet(sheetName, true);
        }

        public Workbook(string filename, string sheetName, bool sanitizeSheetName)
        {
            Init();
            this.filename = filename;
            if (sanitizeSheetName)
            {
                AddWorksheet(Worksheet.SanitizeWorksheetName(sheetName, this));
            }
            else
            {
                AddWorksheet(sheetName);
            }
        }
 
        #endregion
 
        #region methods

        public void AddMruColor(string color)
        {
            Validators.ValidateGenericColor(color);
            mruColors.Add(Color.CreateRgb(color));
        }

        public void AddMruColor(Color color)
        {
            mruColors.Add(color);
        }

        public IReadOnlyList<Color> GetMruColors()
        {
            return mruColors;
        }

        public void ClearMruColors()
        {
            mruColors.Clear();
        }
 
        #region definedNames

        public DefinedName AddDefinedNameCell(string name, Worksheet worksheet, string cellAddress, Worksheet localWorksheet = null, string comment = null)
        {
            Address address = new Address(cellAddress);
            return AddDefinedNameCell(name, worksheet, address, localWorksheet, comment);
        }

        public DefinedName AddDefinedNameCell(string name, Worksheet worksheet, int column, int row, Worksheet localWorksheet = null, string comment = null)
        {
            Address address = new Address(column, row);
            return AddDefinedNameCell(name, worksheet, address, localWorksheet, comment);
        }

        public DefinedName AddDefinedNameCell(string name, Worksheet worksheet, Address cellAddress, Worksheet localWorksheet = null, string comment = null)
        {
            if (worksheet == null)
            {
                throw new WorksheetException("A defined name to a cell must have a worksheet");
            }
            if (cellAddress == null)
            {
                throw new WorksheetException("The cell address pointing to a defined name cannot be null");
            }
            return AddDefinedName(name, DefinedName.NameType.Cell, cellAddress, worksheet, localWorksheet, comment);
        }

        public DefinedName AddDefinedNameRange(string name, Worksheet worksheet, string rangeAddress, Worksheet localWorksheet = null, string comment = null)
        {
            Range range = new Range(rangeAddress);
            return AddDefinedNameRange(name, worksheet, range, localWorksheet, comment);
        }

        public DefinedName AddDefinedNameRange(string name, Worksheet worksheet, Address startAddress, Address endAddress, Worksheet localWorksheet = null, string comment = null)
        {
            Range range = new Range(startAddress, endAddress);
            return AddDefinedNameRange(name, worksheet, range, localWorksheet, comment);
        }

        public DefinedName AddDefinedNameRange(string name, Worksheet worksheet, int startColumn, int startRow, int endColumn, int endRow, Worksheet localWorksheet = null, string comment = null)
        {
            Range range = new Range(startColumn, startRow, endColumn, endRow);
            return AddDefinedNameRange(name, worksheet, range, localWorksheet, comment);
        }

        public DefinedName AddDefinedNameRange(string name, Worksheet worksheet, Range rangeAddress, Worksheet localWorksheet = null, string comment = null)
        {
            if (worksheet == null)
            {
                throw new WorksheetException("A defined name to a cell must have a worksheet");
            }
            return AddDefinedName(name, DefinedName.NameType.Range, rangeAddress, worksheet, localWorksheet, comment);
        }

        public DefinedName AddDefinedNameConstant(string name, object value, Worksheet localWorksheet = null, string comment = null)
        {
            if (value == null)
            {
                throw new WorksheetException("A constant value pointing to a defined name cannot be nul");
            }
            return AddDefinedName(name, DefinedName.NameType.Constant, value, null, localWorksheet, comment);
        }

        public DefinedName AddDefinedNameFormula(string name, string formula, Worksheet localWorksheet = null, string comment = null)
        {
            if (string.IsNullOrWhiteSpace(formula))
            {
                throw new WorksheetException("A formula value pointing to a defined name cannot be null or empty");
            }
            // Note: Added strings like '[0]' will out-of-the-box throw an exception on saving a workbook 
            return AddDefinedName(name, DefinedName.NameType.Formula, formula, null, localWorksheet, comment);
        }

        internal DefinedName AddDefinedName(string name, DefinedName.NameType type, object value, Worksheet targetWorksheet, Worksheet localWorksheet, string comment)
        {
            // Validation is implemented in DefinedName class
            DefinedName definedName = new DefinedName(this, type, name, value, targetWorksheet, localWorksheet, comment);
            AddDefinedName(definedName);
            return definedName;
        }

        internal void AddDefinedName(DefinedName definedName)
        {
            definedNames.Add(definedName);
        }

        public bool RemoveDefinedName(string name, Worksheet localSheet = null)
        {
            return RemoveDefinedName(name, true, localSheet);
        }

        internal bool RemoveDefinedName(string name, bool invalidate, Worksheet localSheet = null)
        {
            int index = FindDefinedNameIndex(name, localSheet);
            if (index < 0)
            {
                return false;
            }
            DefinedName definedName = definedNames[index];
            if (invalidate)
            {
                InvalidateDefinedNameReferences(definedName);
            }
            definedName.Features.Remove(Features);  // Decrease counter of defined name features
            definedNames.RemoveAt(index);
            return true;
        }

        public void ClearDefinedNames()
        {
            for (int i = definedNames.Count - 1; i >= 0; i--)
            {
                DefinedName definedName = definedNames[i];
                InvalidateDefinedNameReferences(definedName);
                definedName.Features.Remove(Features);  // Decrease counter of defined name features
                definedNames.RemoveAt(i);
            }
        }

        public DefinedName GetDefinedName(string name, Worksheet localSheet = null)
        {
            int index = FindDefinedNameIndex(name, localSheet);
            return index < 0 ? null : definedNames[index];
        }

        public IReadOnlyList<DefinedName> GetDefinedNames()
        {
            return definedNames;
        }

        internal int FindDefinedNameIndex(string name, Worksheet localSheet)
        {
            for (int i = 0; i < definedNames.Count; i++)
            {
                DefinedName candidate = definedNames[i];
                if (string.Equals(candidate.Name, name, System.StringComparison.OrdinalIgnoreCase)
                    && ReferenceEquals(candidate.LocalSheet, localSheet))
                {
                    return i;
                }
            }
            return -1;
        }
 
        #endregion

        public void AddWorksheet(string name)
        {
            foreach (Worksheet item in worksheets)
            {
                if (item.SheetName == name)
                {
                    throw new WorksheetException("The worksheet with the name '" + name + "' already exists.");
                }
            }
            int number = GetNextWorksheetId();
            Worksheet newWs = new Worksheet(name, number, this);
            currentWorksheet = newWs;
            worksheets.Add(newWs);
            newWs.Features.Add(Features);
            shortener.SetCurrentWorksheetInternal(currentWorksheet);
        }

        public void AddWorksheet(string name, bool sanitizeSheetName)
        {
            if (sanitizeSheetName)
            {
                string sanitized = Worksheet.SanitizeWorksheetName(name, this);
                AddWorksheet(sanitized);
            }
            else
            {
                AddWorksheet(name);
            }
        }

        public void AddWorksheet(Worksheet worksheet)
        {
            AddWorksheet(worksheet, false);
        }

        public void AddWorksheet(Worksheet worksheet, bool sanitizeSheetName)
        {
            if (sanitizeSheetName)
            {
                string name = Worksheet.SanitizeWorksheetName(worksheet.SheetName, this);
                worksheet.SheetName = name;
            }
            else
            {
                if (string.IsNullOrEmpty(worksheet.SheetName))
                {
                    throw new WorksheetException("The name of the passed worksheet is null or empty.");
                }
                for (int i = 0; i < worksheets.Count; i++)
                {
                    if (worksheets[i].SheetName == worksheet.SheetName)
                    {
                        throw new WorksheetException("The worksheet with the name '" + worksheet.SheetName + "' already exists.");
                    }
                }
            }
            worksheet.SheetID = GetNextWorksheetId();
            currentWorksheet = worksheet;
            worksheets.Add(worksheet);
            worksheet.WorkbookReference = this;
            worksheet.Features.Add(Features);
        }

        public void RemoveWorksheet(string name)
        {
            Worksheet worksheetToRemove = worksheets.FirstOrDefault(w => w.SheetName == name);
            if (worksheetToRemove == null)
            {
                throw new WorksheetException("The worksheet with the name '" + name + "' does not exist.");
            }
            int index = worksheets.IndexOf(worksheetToRemove);
            bool resetCurrentWorksheet = worksheetToRemove == currentWorksheet;
            RemoveWorksheet(index, resetCurrentWorksheet);
        }

 
        public void RemoveWorksheet(int index)
        {
            if (index < 0 || index >= worksheets.Count)
            {
                throw new WorksheetException("The worksheet index " + index + " is out of range");
            }
            bool resetCurrentWorksheet = worksheets[index] == currentWorksheet;
            RemoveWorksheet(index, resetCurrentWorksheet);
        }

        internal void ResolveMergedCells()
        {
            foreach (Worksheet worksheet in worksheets)
            {
                worksheet.ResolveMergedCells();
            }
        }

        public Worksheet SetCurrentWorksheet(string name)
        {
            currentWorksheet = GetWorksheet(name);
            shortener.SetCurrentWorksheetInternal(currentWorksheet);
            return currentWorksheet;
        }

        public Worksheet SetCurrentWorksheet(int worksheetIndex)
        {
            currentWorksheet = GetWorksheet(worksheetIndex);
            shortener.SetCurrentWorksheetInternal(currentWorksheet);
            return currentWorksheet;
        }

        public void SetCurrentWorksheet(Worksheet worksheet)
        {
            int index = worksheets.IndexOf(worksheet);
            if (index < 0)
            {
                throw new WorksheetException("The passed worksheet object is not in the worksheet collection.");
            }
            currentWorksheet = worksheets[index];
            shortener.SetCurrentWorksheetInternal(worksheet);
        }

        public void SetSelectedWorksheet(string name)
        {
            int index = worksheets.FindIndex(w => w.SheetName == name);
            if (index < 0)
            {
                throw new WorksheetException("No worksheet with the name '" + name + "' was found in this workbook.");
            }
            selectedWorksheet = index;
        }

        public void SetSelectedWorksheet(int worksheetIndex)
        {
            if (worksheetIndex < 0 || worksheetIndex > worksheets.Count - 1)
            {
                throw new RangeException("The worksheet index " + worksheetIndex + " is out of range");
            }
            selectedWorksheet = worksheetIndex;
            ValidateWorksheets();
        }

        public void SetSelectedWorksheet(Worksheet worksheet)
        {
            selectedWorksheet = worksheets.IndexOf(worksheet);
            if (selectedWorksheet < 0)
            {
                throw new WorksheetException("The passed worksheet object is not in the worksheet collection.");
            }
            ValidateWorksheets();
        }

        public Worksheet GetWorksheet(string name)
        {
            int index = worksheets.FindIndex(w => w.SheetName == name);
            if (index < 0)
            {
                throw new WorksheetException("No worksheet with the name '" + name + "' was found in this workbook.");
            }
            return worksheets[index];
        }

        public Worksheet GetWorksheet(int index)
        {
            if (index < 0 || index > worksheets.Count - 1)
            {
                throw new RangeException("The worksheet index " + index + " is out of range");
            }
            return worksheets[index];
        }

        public void SetWorkbookProtection(bool state, bool protectWindows, bool protectStructure, string password)
        {
            lockWindowsIfProtected = protectWindows;
            lockStructureIfProtected = protectStructure;
            workbookProtectionPassword.SetPassword(password);
            if (!protectWindows && !protectStructure)
            {
                UseWorkbookProtection = false;
            }
            else
            {
                UseWorkbookProtection = state;
            }
        }

        public Worksheet CopyWorksheetIntoThis(string sourceWorksheetName, string newWorksheetName, bool sanitizeSheetName = true)
        {
            Worksheet sourceWorksheet = GetWorksheet(sourceWorksheetName);
            return CopyWorksheetTo(sourceWorksheet, newWorksheetName, this, sanitizeSheetName);
        }

        public Worksheet CopyWorksheetIntoThis(int sourceWorksheetIndex, string newWorksheetName, bool sanitizeSheetName = true)
        {
            Worksheet sourceWorksheet = GetWorksheet(sourceWorksheetIndex);
            return CopyWorksheetTo(sourceWorksheet, newWorksheetName, this, sanitizeSheetName);
        }

        public Worksheet CopyWorksheetIntoThis(Worksheet sourceWorksheet, string newWorksheetName, bool sanitizeSheetName = true)
        {
            return CopyWorksheetTo(sourceWorksheet, newWorksheetName, this, sanitizeSheetName);
        }

        public Worksheet CopyWorksheetTo(string sourceWorksheetName, string newWorksheetName, Workbook targetWorkbook, bool sanitizeSheetName = true)
        {
            Worksheet sourceWorksheet = GetWorksheet(sourceWorksheetName);
            return CopyWorksheetTo(sourceWorksheet, newWorksheetName, targetWorkbook, sanitizeSheetName);
        }

        public Worksheet CopyWorksheetTo(int sourceWorksheetIndex, string newWorksheetName, Workbook targetWorkbook, bool sanitizeSheetName = true)
        {
            Worksheet sourceWorksheet = GetWorksheet(sourceWorksheetIndex);
            return CopyWorksheetTo(sourceWorksheet, newWorksheetName, targetWorkbook, sanitizeSheetName);
        }
 

        public static Worksheet CopyWorksheetTo(Worksheet sourceWorksheet, string newWorksheetName, Workbook targetWorkbook, bool sanitizeSheetName = true)
        {
            if (targetWorkbook == null)
            {
                throw new WorksheetException("The target workbook cannot be null");
            }
            if (sourceWorksheet == null)
            {
                throw new WorksheetException("The source worksheet cannot be null");
            }
            Worksheet copy = sourceWorksheet.Copy();
            copy.SetSheetName(newWorksheetName);
            Worksheet currentWorksheet = targetWorkbook.CurrentWorksheet;
            targetWorkbook.AddWorksheet(copy, sanitizeSheetName);
            targetWorkbook.SetCurrentWorksheet(currentWorksheet);
            return copy;
        }
 

        internal void ValidateWorksheets()
        {
            if (importInProgress)
            {
                // No validation during import
                return;
            }
            int worksheetCount = worksheets.Count;
            if (worksheetCount == 0)
            {
                throw new WorksheetException("The workbook must contain at least one worksheet");
            }
            for (int i = 0; i < worksheetCount; i++)
            {
                if (worksheets[i].Hidden)
                {
                    if (i == selectedWorksheet)
                    {
                        throw new WorksheetException("The worksheet with the index " + selectedWorksheet + " cannot be set as selected, since it is set hidden");
                    }
                }
            }
        }

        private void InvalidateDefinedNameReferences(DefinedName definedName)
        {
            foreach (Worksheet worksheet in worksheets)
            {
                if (!worksheet.Features.ContainsDefinedNameReferences)
                {
                    continue;
                }
                foreach (KeyValuePair<string, Cell> cell in worksheet.Cells)
                {
                    if (cell.Value.DataType == Cell.CellType.Formula)
                    {
                        FormulaData formula = cell.Value.Formula;
                        if (formula != null && ReferenceEquals(formula.DefinedNameReference, definedName))
                        {
                            formula.DefinedNameReference = null;
                        }
                    }
                }
            }
        }
 

        private void RemoveWorksheet(int index, bool resetCurrentWorksheet)
        {
            worksheets[index].Features.Remove(Features); // Remove cascading features
            worksheets.RemoveAt(index);
            if (worksheets.Count > 0)
            {
                for (int i = 0; i < worksheets.Count; i++)
                {
                    worksheets[i].SheetID = i + 1;
                }
                if (resetCurrentWorksheet)
                {
                    currentWorksheet = worksheets[worksheets.Count - 1];
                }
                if (selectedWorksheet == index || selectedWorksheet > worksheets.Count - 1)
                {
                    selectedWorksheet = worksheets.Count - 1;
                }
            }
            else
            {
                currentWorksheet = null;
                selectedWorksheet = 0;
            }
            ValidateWorksheets();
        }

        private int GetNextWorksheetId()
        {
            if (worksheets.Count == 0)
            {
                return 1;
            }
            return worksheets.Max(w => w.SheetID) + 1;
        }

        private void Init()
        {
            worksheets = new List<Worksheet>();
            workbookMetadata = new Metadata();
            shortener = new Shortener(this);
            workbookProtectionPassword = new LegacyPassword(LegacyPassword.PasswordType.WorkbookProtection);
            AuxiliaryData = new AuxiliaryData();
        }
 
 
        #endregion
    }
}