EPPlus/ExcelWorksheetView.cs

/*******************************************************************************
 * You may amend and distribute as you like, but don't remove this header!
 *
 * EPPlus provides server-side generation of Excel 2007/2010 spreadsheets.
 * See http://www.codeplex.com/EPPlus for details.
 *
 * Copyright (C) 2011  Jan K�llman
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.

 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
 * See the GNU Lesser General Public License for more details.
 *
 * The GNU Lesser General Public License can be viewed at http://www.opensource.org/licenses/lgpl-license.php
 * If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html
 *
 * All code and executables are provided "as is" with no warranty either express or implied.
 * The author accepts no liability for any damage or loss of business that this product may cause.
 *
 * Code change notes:
 *
 * Author							Change						Date
 * ******************************************************************************
 * Jan K�llman		    Initial Release		       2009-10-01
 * Jan K�llman		    License changed GPL-->LGPL 2011-12-27
 *******************************************************************************/

using System;
using System.Xml;

namespace OfficeOpenXml;

/// <summary>
/// Represents the different view states of the worksheet
/// </summary>
public class ExcelWorksheetView : XmlHelper {
  /// <summary>
  /// The worksheet panes after a freeze or split.
  /// </summary>
  public class ExcelWorksheetPanes : XmlHelper {
    private XmlElement _selectionNode;

    internal ExcelWorksheetPanes(XmlNamespaceManager ns, XmlNode topNode)
        : base(ns, topNode) {
      if (topNode.Name == "selection") {
        _selectionNode = topNode as XmlElement;
      }
    }

    private const string _activeCellPath = "@activeCell";

    /// <summary>
    /// Set the active cell. Must be set within the SelectedRange.
    /// </summary>
    public string ActiveCell {
      get {
        string address = GetXmlNodeString(_activeCellPath);
        if (address == "") {
          return "A1";
        }
        return address;
      }
      set {
        int toCol,
            toRow;
        if (_selectionNode == null) {
          CreateSelectionElement();
        }
        ExcelCellBase.GetRowColFromAddress(
            value,
            out var fromRow,
            out var fromCol,
            out toRow,
            out toCol);
        SetXmlNodeString(_activeCellPath, value);
        if (((XmlElement)TopNode).GetAttribute("sqref") == "") {
          SelectedRange = ExcelCellBase.GetAddress(fromRow, fromCol);
        }
        //TODO:Add fix for out of range here
      }
    }

    private void CreateSelectionElement() {
      _selectionNode = TopNode.OwnerDocument.CreateElement("selection", ExcelPackage._schemaMain);
      TopNode.AppendChild(_selectionNode);
      TopNode = _selectionNode;
    }

    private const string _selectionRangePath = "@sqref";

    /// <summary>
    /// Selected Cells.Used in combination with ActiveCell
    /// </summary>
    public string SelectedRange {
      get {
        string address = GetXmlNodeString(_selectionRangePath);
        if (address == "") {
          return "A1";
        }
        return address;
      }
      set {
        int toCol,
            toRow;
        if (_selectionNode == null) {
          CreateSelectionElement();
        }
        ExcelCellBase.GetRowColFromAddress(
            value,
            out var fromRow,
            out var fromCol,
            out toRow,
            out toCol);
        SetXmlNodeString(_selectionRangePath, value);
        if (((XmlElement)TopNode).GetAttribute("activeCell") == "") {
          ActiveCell = ExcelCellBase.GetAddress(fromRow, fromCol);
        }
        //TODO:Add fix for out of range here
      }
    }
  }

  private readonly ExcelWorksheet _worksheet;

  /// <summary>
  /// Creates a new ExcelWorksheetView which provides access to all the view states of the worksheet.
  /// </summary>
  /// <param name="ns"></param>
  /// <param name="node"></param>
  /// <param name="xlWorksheet"></param>
  internal ExcelWorksheetView(XmlNamespaceManager ns, XmlNode node, ExcelWorksheet xlWorksheet)
      : base(ns, node) {
    _worksheet = xlWorksheet;
    SchemaNodeOrder = new[] { "sheetViews", "sheetView", "pane", "selection" };
    Panes = LoadPanes();
  }

  private ExcelWorksheetPanes[] LoadPanes() {
    XmlNodeList nodes = TopNode.SelectNodes("//d:selection", NameSpaceManager);
    if (nodes.Count == 0) {
      return new[] { new ExcelWorksheetPanes(NameSpaceManager, TopNode) };
    }
    ExcelWorksheetPanes[] panes = new ExcelWorksheetPanes[nodes.Count];
    int i = 0;
    foreach (XmlElement elem in nodes) {
      panes[i++] = new(NameSpaceManager, elem);
    }
    return panes;
  }

  /// <summary>
  /// Returns a reference to the sheetView element
  /// </summary>
  protected internal XmlElement SheetViewElement => (XmlElement)TopNode;

  /// <summary>
  /// The active cell.
  /// </summary>
  public string ActiveCell {
    get => Panes[Panes.GetUpperBound(0)].ActiveCell;
    set => Panes[Panes.GetUpperBound(0)].ActiveCell = value;
  }

  /// <summary>
  /// Selected Cells in the worksheet.Used in combination with ActiveCell
  /// </summary>
  public string SelectedRange {
    get => Panes[Panes.GetUpperBound(0)].SelectedRange;
    set => Panes[Panes.GetUpperBound(0)].SelectedRange = value;
  }

  /// <summary>
  /// Indicates if the worksheet is selected within the workbook
  /// </summary>
  public bool TabSelected {
    get => GetXmlNodeBool("@tabSelected");
    set {
      if (value) {
        //    // ensure no other worksheet has its tabSelected attribute set to 1
        foreach (ExcelWorksheet sheet in _worksheet.Workbook.Worksheets) {
          sheet.View.TabSelected = false;
        }

        SheetViewElement.SetAttribute("tabSelected", "1");
        XmlElement bookView =
            _worksheet.Workbook.WorkbookXml.SelectSingleNode(
                "//d:workbookView",
                _worksheet.NameSpaceManager) as XmlElement;
        if (bookView != null) {
          bookView.SetAttribute("activeTab", (_worksheet.PositionID - 1).ToString());
        }
      } else {
        SetXmlNodeString("@tabSelected", "0");
      }
    }
  }

  /// <summary>
  /// Sets the view mode of the worksheet to pagelayout
  /// </summary>
  public bool PageLayoutView {
    get => GetXmlNodeString("@view") == "pageLayout";
    set {
      if (value) {
        SetXmlNodeString("@view", "pageLayout");
      } else {
        SheetViewElement.RemoveAttribute("view");
      }
    }
  }

  /// <summary>
  /// Sets the view mode of the worksheet to pagebreak
  /// </summary>
  public bool PageBreakView {
    get => GetXmlNodeString("@view") == "pageBreakPreview";
    set {
      if (value) {
        SetXmlNodeString("@view", "pageBreakPreview");
      } else {
        SheetViewElement.RemoveAttribute("view");
      }
    }
  }

  /// <summary>
  /// Show gridlines in the worksheet
  /// </summary>
  public bool ShowGridLines {
    get => GetXmlNodeBool("@showGridLines");
    set => SetXmlNodeString("@showGridLines", value ? "1" : "0");
  }

  /// <summary>
  /// Show the Column/Row headers (containg column letters and row numbers)
  /// </summary>
  public bool ShowHeaders {
    get => GetXmlNodeBool("@showRowColHeaders");
    set => SetXmlNodeString("@showRowColHeaders", value ? "1" : "0");
  }

  /// <summary>
  /// Window zoom magnification for current view representing percent values.
  /// </summary>
  public int ZoomScale {
    get => GetXmlNodeInt("@zoomScale");
    set {
      if (value < 10 || value > 400) {
        throw new ArgumentOutOfRangeException("Zoome scale out of range (10-400)");
      }
      SetXmlNodeString("@zoomScale", value.ToString());
    }
  }

  /// <summary>
  /// Flag indicating whether the sheet is in 'right to left' display mode. When in this mode,Column A is on the far right, Column B ;is one column left of Column A, and so on. Also,information in cells is displayed in the Right to Left format.
  /// </summary>
  public bool RightToLeft {
    get => GetXmlNodeBool("@rightToLeft");
    set => SetXmlNodeString("@rightToLeft", value ? "1" : "0");
  }

  internal bool WindowProtection {
    get => GetXmlNodeBool("@windowProtection", false);
    set => SetXmlNodeBool("@windowProtection", value, false);
  }

  /// <summary>
  /// Reference to the panes
  /// </summary>
  public ExcelWorksheetPanes[] Panes { get; internal set; }

  private readonly string _paneNodePath = "d:pane";
  private readonly string _selectionNodePath = "d:selection";

  /// <summary>
  /// Freeze the columns/rows to left and above the cell
  /// </summary>
  /// <param name="row"></param>
  /// <param name="column"></param>
  public void FreezePanes(int row, int column) {
    //TODO:fix this method to handle splits as well.
    if (row == 1 && column == 1) {
      UnFreezePanes();
    }
    string sqRef = SelectedRange,
        activeCell = ActiveCell;

    XmlElement paneNode = TopNode.SelectSingleNode(_paneNodePath, NameSpaceManager) as XmlElement;
    if (paneNode == null) {
      CreateNode(_paneNodePath);
      paneNode = TopNode.SelectSingleNode(_paneNodePath, NameSpaceManager) as XmlElement;
    }
    paneNode.RemoveAll(); //Clear all attributes
    if (column > 1) {
      paneNode.SetAttribute("xSplit", (column - 1).ToString());
    }
    if (row > 1) {
      paneNode.SetAttribute("ySplit", (row - 1).ToString());
    }
    paneNode.SetAttribute("topLeftCell", ExcelCellBase.GetAddress(row, column));
    paneNode.SetAttribute("state", "frozen");

    RemoveSelection();

    if (row > 1 && column == 1) {
      paneNode.SetAttribute("activePane", "bottomLeft");
      XmlElement sel = TopNode.OwnerDocument.CreateElement("selection", ExcelPackage._schemaMain);
      sel.SetAttribute("pane", "bottomLeft");
      if (activeCell != "") {
        sel.SetAttribute("activeCell", activeCell);
      }
      if (sqRef != "") {
        sel.SetAttribute("sqref", sqRef);
      }
      sel.SetAttribute("sqref", sqRef);
      TopNode.InsertAfter(sel, paneNode);
    } else if (column > 1 && row == 1) {
      paneNode.SetAttribute("activePane", "topRight");
      XmlElement sel = TopNode.OwnerDocument.CreateElement("selection", ExcelPackage._schemaMain);
      sel.SetAttribute("pane", "topRight");
      if (activeCell != "") {
        sel.SetAttribute("activeCell", activeCell);
      }
      if (sqRef != "") {
        sel.SetAttribute("sqref", sqRef);
      }
      TopNode.InsertAfter(sel, paneNode);
    } else {
      paneNode.SetAttribute("activePane", "bottomRight");
      XmlElement sel1 = TopNode.OwnerDocument.CreateElement("selection", ExcelPackage._schemaMain);
      sel1.SetAttribute("pane", "topRight");
      string cell = ExcelCellBase.GetAddress(1, column);
      sel1.SetAttribute("activeCell", cell);
      sel1.SetAttribute("sqref", cell);
      paneNode.ParentNode.InsertAfter(sel1, paneNode);

      XmlElement sel2 = TopNode.OwnerDocument.CreateElement("selection", ExcelPackage._schemaMain);
      cell = ExcelCellBase.GetAddress(row, 1);
      sel2.SetAttribute("pane", "bottomLeft");
      sel2.SetAttribute("activeCell", cell);
      sel2.SetAttribute("sqref", cell);
      sel1.ParentNode.InsertAfter(sel2, sel1);

      XmlElement sel3 = TopNode.OwnerDocument.CreateElement("selection", ExcelPackage._schemaMain);
      sel3.SetAttribute("pane", "bottomRight");
      if (activeCell != "") {
        sel3.SetAttribute("activeCell", activeCell);
      }
      if (sqRef != "") {
        sel3.SetAttribute("sqref", sqRef);
      }
      sel2.ParentNode.InsertAfter(sel3, sel2);
    }
    Panes = LoadPanes();
  }

  private void RemoveSelection() {
    //Find selection nodes and remove them
    XmlNodeList selections = TopNode.SelectNodes(_selectionNodePath, NameSpaceManager);
    foreach (XmlNode sel in selections) {
      sel.ParentNode.RemoveChild(sel);
    }
  }

  /// <summary>
  /// Unlock all rows and columns to scroll freely
  /// /// </summary>
  public void UnFreezePanes() {
    string sqRef = SelectedRange,
        activeCell = ActiveCell;

    XmlElement paneNode = TopNode.SelectSingleNode(_paneNodePath, NameSpaceManager) as XmlElement;
    if (paneNode != null) {
      paneNode.ParentNode.RemoveChild(paneNode);
    }
    RemoveSelection();

    Panes = LoadPanes();

    SelectedRange = sqRef;
    ActiveCell = activeCell;
  }
}