EPPlus/Table/ExcelTable.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		Added		30-AUG-2010
 * Jan Källman		License changed GPL-->LGPL 2011-12-16
 *******************************************************************************/

using System;
using System.Collections.Generic;
using System.Security;
using System.Text;
using System.Text.RegularExpressions;
using System.Xml;
using OfficeOpenXml.Packaging;
using OfficeOpenXml.Utils;

namespace OfficeOpenXml.Table;

/// <summary>
/// Table style Enum
/// </summary>
public enum TableStyles {
  None,
  Custom,
  Light1,
  Light2,
  Light3,
  Light4,
  Light5,
  Light6,
  Light7,
  Light8,
  Light9,
  Light10,
  Light11,
  Light12,
  Light13,
  Light14,
  Light15,
  Light16,
  Light17,
  Light18,
  Light19,
  Light20,
  Light21,
  Medium1,
  Medium2,
  Medium3,
  Medium4,
  Medium5,
  Medium6,
  Medium7,
  Medium8,
  Medium9,
  Medium10,
  Medium11,
  Medium12,
  Medium13,
  Medium14,
  Medium15,
  Medium16,
  Medium17,
  Medium18,
  Medium19,
  Medium20,
  Medium21,
  Medium22,
  Medium23,
  Medium24,
  Medium25,
  Medium26,
  Medium27,
  Medium28,
  Dark1,
  Dark2,
  Dark3,
  Dark4,
  Dark5,
  Dark6,
  Dark7,
  Dark8,
  Dark9,
  Dark10,
  Dark11,
}

/// <summary>
/// An Excel Table
/// </summary>
public class ExcelTable : XmlHelper, IEqualityComparer<ExcelTable> {
  internal ExcelTable(ZipPackageRelationship rel, ExcelWorksheet sheet)
      : base(sheet.NameSpaceManager) {
    WorkSheet = sheet;
    TableUri = UriHelper.ResolvePartUri(rel.SourceUri, rel.TargetUri);
    RelationshipID = rel.Id;
    var pck = sheet._package.Package;
    Part = pck.GetPart(TableUri);

    TableXml = new();
    LoadXmlSafe(TableXml, Part.GetStream());
    Init();
    Address = new(GetXmlNodeString("@ref"));
  }

  internal ExcelTable(ExcelWorksheet sheet, ExcelAddressBase address, string name, int tblId)
      : base(sheet.NameSpaceManager) {
    WorkSheet = sheet;
    Address = address;
    TableXml = new();
    LoadXmlSafe(TableXml, GetStartXml(name, tblId), Encoding.UTF8);
    TopNode = TableXml.DocumentElement;

    Init();

    //If the table is just one row we can not have a header.
    if (address._fromRow == address._toRow) {
      ShowHeader = false;
    }
  }

  private void Init() {
    TopNode = TableXml.DocumentElement;
    SchemaNodeOrder = new[] { "autoFilter", "tableColumns", "tableStyleInfo" };
  }

  private string GetStartXml(string name, int tblId) {
    string xml = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\" ?>";
    xml += string.Format(
        "<table xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" id=\"{0}\" name=\"{1}\" displayName=\"{2}\" ref=\"{3}\" headerRowCount=\"1\">",
        tblId,
        name,
        CleanDisplayName(name),
        Address.Address);
    xml += string.Format("<autoFilter ref=\"{0}\" />", Address.Address);

    int cols = Address._toCol - Address._fromCol + 1;
    xml += string.Format("<tableColumns count=\"{0}\">", cols);
    var names = new Dictionary<string, string>();
    for (int i = 1; i <= cols; i++) {
      var cell = WorkSheet.Cells[Address._fromRow, Address._fromCol + i - 1];
      string colName;
      if (cell.Value == null || names.ContainsKey(cell.Value.ToString())) {
        //Get an unique name
        int a = i;
        do {
          colName = string.Format("Column{0}", a++);
        } while (names.ContainsKey(colName));
      } else {
        colName = SecurityElement.Escape(cell.Value.ToString());
      }
      names.Add(colName, colName);
      xml += string.Format("<tableColumn id=\"{0}\" name=\"{1}\" />", i, colName);
    }
    xml += "</tableColumns>";
    xml +=
        "<tableStyleInfo name=\"TableStyleMedium9\" showFirstColumn=\"0\" showLastColumn=\"0\" showRowStripes=\"1\" showColumnStripes=\"0\" /> ";
    xml += "</table>";

    return xml;
  }

  private string CleanDisplayName(string name) {
    return Regex.Replace(name, @"[^\w\.-_]", "_");
  }

  internal ZipPackagePart Part { get; set; }

  /// <summary>
  /// Provides access to the XML data representing the table in the package.
  /// </summary>
  public XmlDocument TableXml { get; set; }

  /// <summary>
  /// The package internal URI to the Table Xml Document.
  /// </summary>
  public Uri TableUri { get; internal set; }

  internal string RelationshipID { get; set; }

  private const string _idPath = "@id";

  internal int Id {
    get => GetXmlNodeInt(_idPath);
    set => SetXmlNodeString(_idPath, value.ToString());
  }

  private const string _namePath = "@name";
  private const string _displayNamePath = "@displayName";

  /// <summary>
  /// The name of the table object in Excel
  /// </summary>
  public string Name {
    get => GetXmlNodeString(_namePath);
    set {
      if (WorkSheet.Workbook.ExistsTableName(value)) {
        throw (new ArgumentException("Tablename is not unique"));
      }
      string prevName = Name;
      if (WorkSheet.Tables._tableNames.ContainsKey(prevName)) {
        int ix = WorkSheet.Tables._tableNames[prevName];
        WorkSheet.Tables._tableNames.Remove(prevName);
        WorkSheet.Tables._tableNames.Add(value, ix);
      }
      SetXmlNodeString(_namePath, value);
      SetXmlNodeString(_displayNamePath, CleanDisplayName(value));
    }
  }

  /// <summary>
  /// The worksheet of the table
  /// </summary>
  public ExcelWorksheet WorkSheet { get; set; }

  private ExcelAddressBase _address;

  /// <summary>
  /// The address of the table
  /// </summary>
  public ExcelAddressBase Address {
    get => _address;
    set {
      _address = value;
      SetXmlNodeString("@ref", value.Address);
      WriteAutoFilter(ShowTotal);
    }
  }

  internal ExcelTableColumnCollection _cols;

  /// <summary>
  /// Collection of the columns in the table
  /// </summary>
  public ExcelTableColumnCollection Columns {
    get {
      if (_cols == null) {
        _cols = new(this);
      }
      return _cols;
    }
  }

  private TableStyles _tableStyle = TableStyles.Medium6;

  /// <summary>
  /// The table style. If this property is cusom, the style from the StyleName propery is used.
  /// </summary>
  public TableStyles TableStyle {
    get => _tableStyle;
    set {
      _tableStyle = value;
      if (value != TableStyles.Custom) {
        SetXmlNodeString(_stylenamePath, "TableStyle" + value);
      }
    }
  }

  private const string _headerrowcountPath = "@headerRowCount";
  private const string _autofilterPath = "d:autoFilter/@ref";

  /// <summary>
  /// If the header row is visible or not
  /// </summary>
  public bool ShowHeader {
    get => GetXmlNodeInt(_headerrowcountPath) != 0;
    set {
      if (Address._toRow - Address._fromRow < 0 && value
          || Address._toRow - Address._fromRow == 1 && value && ShowTotal) {
        throw (new("Cant set ShowHeader-property. Table has too few rows"));
      }

      if (value) {
        DeleteNode(_headerrowcountPath);
        WriteAutoFilter(ShowTotal);
        //for (int i = 0; i < Columns.Count; i++)
        //{
        //    var v = WorkSheet.GetValue<string>(Address._fromRow, Address._fromCol + i);
        //    if (!string.IsNullOrEmpty(v) || v != _cols[i].Name)
        //    {
        //        _cols[i].Name = v;
        //    }
        //}
      } else {
        SetXmlNodeString(_headerrowcountPath, "0");
        DeleteAllNode(_autofilterPath);
      }
    }
  }

  internal ExcelAddressBase AutoFilterAddress {
    get {
      string a = GetXmlNodeString(_autofilterPath);
      if (a == "") {
        return null;
      }
      return new(a);
    }
  }

  private void WriteAutoFilter(bool showTotal) {
    string autofilterAddress;
    if (ShowHeader) {
      if (showTotal) {
        autofilterAddress = ExcelCellBase.GetAddress(
            Address._fromRow,
            Address._fromCol,
            Address._toRow - 1,
            Address._toCol);
      } else {
        autofilterAddress = Address.Address;
      }
      SetXmlNodeString(_autofilterPath, autofilterAddress);
    }
  }

  /// <summary>
  /// If the header row has an autofilter
  /// </summary>
  public bool ShowFilter {
    get => ShowHeader && AutoFilterAddress != null;
    set {
      if (ShowHeader) {
        if (value) {
          WriteAutoFilter(ShowTotal);
        } else {
          DeleteAllNode(_autofilterPath);
        }
      } else if (value) {
        throw (new InvalidOperationException(
                "Filter can only be applied when ShowHeader is set to true"));
      }
    }
  }

  private const string _totalsrowcountPath = "@totalsRowCount";

  /// <summary>
  /// If the total row is visible or not
  /// </summary>
  public bool ShowTotal {
    get => GetXmlNodeInt(_totalsrowcountPath) == 1;
    set {
      if (value != ShowTotal) {
        if (value) {
          Address = new ExcelAddress(
              WorkSheet.Name,
              ExcelCellBase.GetAddress(
                  Address.Start.Row,
                  Address.Start.Column,
                  Address.End.Row + 1,
                  Address.End.Column));
        } else {
          Address = new ExcelAddress(
              WorkSheet.Name,
              ExcelCellBase.GetAddress(
                  Address.Start.Row,
                  Address.Start.Column,
                  Address.End.Row - 1,
                  Address.End.Column));
        }
        SetXmlNodeString("@ref", Address.Address);
        if (value) {
          SetXmlNodeString(_totalsrowcountPath, "1");
        } else {
          DeleteNode(_totalsrowcountPath);
        }
        WriteAutoFilter(value);
      }
    }
  }

  private const string _stylenamePath = "d:tableStyleInfo/@name";

  /// <summary>
  /// The style name for custum styles
  /// </summary>
  public string StyleName {
    get => GetXmlNodeString(_stylenamePath);
    set {
      if (value.StartsWith("TableStyle")) {
        try {
          _tableStyle = (TableStyles)
            Enum.Parse(typeof(TableStyles), value.Substring(10, value.Length - 10), true);
        } catch {
          _tableStyle = TableStyles.Custom;
        }
      } else if (value == "None") {
        _tableStyle = TableStyles.None;
        value = "";
      } else {
        _tableStyle = TableStyles.Custom;
      }
      SetXmlNodeString(_stylenamePath, value, true);
    }
  }

  private const string _showfirstcolumnPath = "d:tableStyleInfo/@showFirstColumn";

  /// <summary>
  /// Display special formatting for the first row
  /// </summary>
  public bool ShowFirstColumn {
    get => GetXmlNodeBool(_showfirstcolumnPath);
    set => SetXmlNodeBool(_showfirstcolumnPath, value, false);
  }

  private const string _showlastcolumnPath = "d:tableStyleInfo/@showLastColumn";

  /// <summary>
  /// Display special formatting for the last row
  /// </summary>
  public bool ShowLastColumn {
    get => GetXmlNodeBool(_showlastcolumnPath);
    set => SetXmlNodeBool(_showlastcolumnPath, value, false);
  }

  private const string _showrowstripesPath = "d:tableStyleInfo/@showRowStripes";

  /// <summary>
  /// Display banded rows
  /// </summary>
  public bool ShowRowStripes {
    get => GetXmlNodeBool(_showrowstripesPath);
    set => SetXmlNodeBool(_showrowstripesPath, value, false);
  }

  private const string _showcolumnstripesPath = "d:tableStyleInfo/@showColumnStripes";

  /// <summary>
  /// Display banded columns
  /// </summary>
  public bool ShowColumnStripes {
    get => GetXmlNodeBool(_showcolumnstripesPath);
    set => SetXmlNodeBool(_showcolumnstripesPath, value, false);
  }

  private const string _totalsrowcellstylePath = "@totalsRowCellStyle";

  /// <summary>
  /// Named style used for the total row
  /// </summary>
  public string TotalsRowCellStyle {
    get => GetXmlNodeString(_totalsrowcellstylePath);
    set {
      if (WorkSheet.Workbook.Styles.NamedStyles.FindIndexById(value) < 0) {
        throw (new(string.Format("Named style {0} does not exist.", value)));
      }
      SetXmlNodeString(TopNode, _totalsrowcellstylePath, value, true);

      if (ShowTotal) {
        WorkSheet.Cells[Address._toRow,
          Address._fromCol,
          Address._toRow,
          Address._toCol].StyleName = value;
      }
    }
  }

  private const string _datacellstylePath = "@dataCellStyle";

  /// <summary>
  /// Named style used for the data cells
  /// </summary>
  public string DataCellStyleName {
    get => GetXmlNodeString(_datacellstylePath);
    set {
      if (WorkSheet.Workbook.Styles.NamedStyles.FindIndexById(value) < 0) {
        throw (new(string.Format("Named style {0} does not exist.", value)));
      }
      SetXmlNodeString(TopNode, _datacellstylePath, value, true);

      int fromRow = Address._fromRow + (ShowHeader ? 1 : 0),
          toRow = Address._toRow - (ShowTotal ? 1 : 0);

      if (fromRow < toRow) {
        WorkSheet.Cells[fromRow, Address._fromCol, toRow, Address._toCol].StyleName = value;
      }
    }
  }

  private const string _headerrowcellstylePath = "@headerRowCellStyle";

  /// <summary>
  /// Named style used for the header row
  /// </summary>
  public string HeaderRowCellStyle {
    get => GetXmlNodeString(_headerrowcellstylePath);
    set {
      if (WorkSheet.Workbook.Styles.NamedStyles.FindIndexById(value) < 0) {
        throw (new(string.Format("Named style {0} does not exist.", value)));
      }
      SetXmlNodeString(TopNode, _headerrowcellstylePath, value, true);

      if (ShowHeader) {
        WorkSheet.Cells[Address._fromRow,
          Address._fromCol,
          Address._fromRow,
          Address._toCol].StyleName = value;
      }
    }
  }

  public bool Equals(ExcelTable x, ExcelTable y) {
    return x.WorkSheet == y.WorkSheet && x.Id == y.Id && x.TableXml.OuterXml == y.TableXml.OuterXml;
  }

  public int GetHashCode(ExcelTable obj) {
    return obj.TableXml.OuterXml.GetHashCode();
  }
}