EPPlus/Table/ExcelTable.cs - epplus - Git at Google

 /*******************************************************************************
  * 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";
   private const string _totalsrowshownPath = "@totalsRowShown";

   /// <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();
   }
 }
	/*******************************************************************************
	* 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";
	private const string _totalsrowshownPath = "@totalsRowShown";

	/// <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();
	}
	}