diff --git a/ClosedXML/Excel/Drawings/IXLPictures.cs b/ClosedXML/Excel/Drawings/IXLPictures.cs index ff449cd..34ed782 100644 --- a/ClosedXML/Excel/Drawings/IXLPictures.cs +++ b/ClosedXML/Excel/Drawings/IXLPictures.cs @@ -25,12 +25,14 @@ IXLPicture Add(String imageFile, String name); + bool Contains(String pictureName); + void Delete(String pictureName); void Delete(IXLPicture picture); IXLPicture Picture(String pictureName); - bool TryGetPicture(string pictureName, out IXLPicture picture); + bool TryGetPicture(String pictureName, out IXLPicture picture); } } diff --git a/ClosedXML/Excel/Drawings/XLPicture.cs b/ClosedXML/Excel/Drawings/XLPicture.cs index d0732db..e79bd12 100644 --- a/ClosedXML/Excel/Drawings/XLPicture.cs +++ b/ClosedXML/Excel/Drawings/XLPicture.cs @@ -14,7 +14,6 @@ { private const String InvalidNameChars = @":\/?*[]"; private static IDictionary FormatMap; - private readonly IXLWorksheet _worksheet; private Int32 height; private Int32 id; private String name = string.Empty; @@ -95,8 +94,7 @@ private XLPicture(IXLWorksheet worksheet) { - if (worksheet == null) throw new ArgumentNullException(nameof(worksheet)); - this._worksheet = worksheet; + this.Worksheet = worksheet ?? throw new ArgumentNullException(nameof(worksheet)); this.Placement = XLPicturePlacement.MoveAndSize; this.Markers = new Dictionary() { @@ -121,7 +119,7 @@ private set { - if (!value.Worksheet.Equals(this._worksheet)) + if (!value.Worksheet.Equals(this.Worksheet)) throw new ArgumentOutOfRangeException(nameof(value.Worksheet)); this.Markers[XLMarkerPosition.BottomRight] = new XLMarker(value); } @@ -145,7 +143,7 @@ get { return id; } internal set { - if ((_worksheet.Pictures.FirstOrDefault(p => p.Id.Equals(value)) ?? this) != this) + if ((Worksheet.Pictures.FirstOrDefault(p => p.Id.Equals(value)) ?? this) != this) throw new ArgumentException($"The picture ID '{value}' already exists."); id = value; @@ -162,7 +160,7 @@ if (this.Placement != XLPicturePlacement.FreeFloating) throw new ArgumentException("To set the left-hand offset, the placement should be FreeFloating"); - Markers[XLMarkerPosition.TopLeft] = new XLMarker(_worksheet.Cell(1, 1).Address, new Point(value, this.Top)); + Markers[XLMarkerPosition.TopLeft] = new XLMarker(Worksheet.Cell(1, 1).Address, new Point(value, this.Top)); } } @@ -173,7 +171,7 @@ { if (name == value) return; - if ((_worksheet.Pictures.FirstOrDefault(p => p.Name.Equals(value, StringComparison.OrdinalIgnoreCase)) ?? this) != this) + if ((Worksheet.Pictures.FirstOrDefault(p => p.Name.Equals(value, StringComparison.OrdinalIgnoreCase)) ?? this) != this) throw new ArgumentException($"The picture name '{value}' already exists."); SetName(value); @@ -194,7 +192,7 @@ if (this.Placement != XLPicturePlacement.FreeFloating) throw new ArgumentException("To set the top offset, the placement should be FreeFloating"); - Markers[XLMarkerPosition.TopLeft] = new XLMarker(_worksheet.Cell(1, 1).Address, new Point(this.Left, value)); + Markers[XLMarkerPosition.TopLeft] = new XLMarker(Worksheet.Cell(1, 1).Address, new Point(this.Left, value)); } } @@ -207,7 +205,7 @@ private set { - if (!value.Worksheet.Equals(this._worksheet)) + if (!value.Worksheet.Equals(this.Worksheet)) throw new ArgumentOutOfRangeException(nameof(value.Worksheet)); this.Markers[XLMarkerPosition.TopLeft] = new XLMarker(value); @@ -225,10 +223,7 @@ } } - public IXLWorksheet Worksheet - { - get { return _worksheet; } - } + public IXLWorksheet Worksheet { get; } internal IDictionary Markers { get; private set; } diff --git a/ClosedXML/Excel/Drawings/XLPictures.cs b/ClosedXML/Excel/Drawings/XLPictures.cs index 3cf1007..3b60573 100644 --- a/ClosedXML/Excel/Drawings/XLPictures.cs +++ b/ClosedXML/Excel/Drawings/XLPictures.cs @@ -12,7 +12,6 @@ { private readonly List _pictures = new List(); private readonly XLWorksheet _worksheet; - internal ICollection Deleted { get; private set; } public XLPictures(XLWorksheet worksheet) { @@ -26,6 +25,8 @@ get { return _pictures.Count; } } + internal ICollection Deleted { get; private set; } + public IXLPicture Add(Stream stream) { var picture = new XLPicture(_worksheet, stream); @@ -89,6 +90,11 @@ return picture; } + public bool Contains(string pictureName) + { + return _pictures.Any(p => string.Equals(p.Name, pictureName, StringComparison.OrdinalIgnoreCase)); + } + public void Delete(IXLPicture picture) { Delete(picture.Name); diff --git a/ClosedXML/Excel/IXLWorksheets.cs b/ClosedXML/Excel/IXLWorksheets.cs index 6a64b8b..8b074ac 100644 --- a/ClosedXML/Excel/IXLWorksheets.cs +++ b/ClosedXML/Excel/IXLWorksheets.cs @@ -18,6 +18,8 @@ void Add(DataSet dataSet); + Boolean Contains(String sheetName); + void Delete(String sheetName); void Delete(Int32 position); diff --git a/ClosedXML/Excel/PivotTables/IXLPivotTable.cs b/ClosedXML/Excel/PivotTables/IXLPivotTable.cs index bcc4292..c3c6899 100644 --- a/ClosedXML/Excel/PivotTables/IXLPivotTable.cs +++ b/ClosedXML/Excel/PivotTables/IXLPivotTable.cs @@ -225,5 +225,7 @@ IXLPivotTable SetLayout(XLPivotLayout value); IXLPivotTable SetInsertBlankLines(); IXLPivotTable SetInsertBlankLines(Boolean value); + IXLWorksheet Worksheet { get; } + } } diff --git a/ClosedXML/Excel/PivotTables/IXLPivotTables.cs b/ClosedXML/Excel/PivotTables/IXLPivotTables.cs index bbce037..ce11a21 100644 --- a/ClosedXML/Excel/PivotTables/IXLPivotTables.cs +++ b/ClosedXML/Excel/PivotTables/IXLPivotTables.cs @@ -1,16 +1,20 @@ using System; using System.Collections.Generic; -using System.Linq; -using System.Text; namespace ClosedXML.Excel { - public interface IXLPivotTables: IEnumerable + public interface IXLPivotTables : IEnumerable { - IXLPivotTable PivotTable(String name); void Add(String name, IXLPivotTable pivotTable); + IXLPivotTable AddNew(String name, IXLCell target, IXLRange source); + + Boolean Contains(String name); + void Delete(String name); + void DeleteAll(); + + IXLPivotTable PivotTable(String name); } } diff --git a/ClosedXML/Excel/PivotTables/XLPivotTable.cs b/ClosedXML/Excel/PivotTables/XLPivotTable.cs index 1d7895d..0e20ea8 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotTable.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotTable.cs @@ -8,10 +8,12 @@ [DebuggerDisplay("{Name}")] internal class XLPivotTable : IXLPivotTable { + private String _name; public Guid Guid { get; private set; } - public XLPivotTable() + public XLPivotTable(IXLWorksheet worksheet) { + this.Worksheet = worksheet ?? throw new ArgumentNullException(nameof(worksheet)); this.Guid = Guid.NewGuid(); Fields = new XLPivotFields(this); @@ -42,7 +44,41 @@ public IXLPivotTable SetTheme(XLPivotTableTheme value) { Theme = value; return this; } - public String Name { get; set; } + public String Name + { + get { return _name; } + set + { + if (_name == value) return; + + var oldname = _name ?? string.Empty; + + if (String.IsNullOrWhiteSpace(value)) + throw new ArgumentException($"The table name '{value}' is invalid"); + + // Table names are case insensitive + if (!oldname.Equals(value, StringComparison.OrdinalIgnoreCase) + && Worksheet.Tables.Any(t => t.Name.Equals(value, StringComparison.OrdinalIgnoreCase))) + throw new ArgumentException($"This worksheet already contains a table named '{value}'"); + + if (value[0] != '_' && !char.IsLetter(value[0])) + throw new ArgumentException($"The table name '{value}' does not begin with a letter or an underscore"); + + if (value.Length > 255) + throw new ArgumentException("The table name is more than 255 characters"); + + if (new[] { 'C', 'R' }.Any(c => value.ToUpper().Equals(c.ToString()))) + throw new ArgumentException($"The table name '{value}' is invalid"); + + _name = value; + + if (!String.IsNullOrWhiteSpace(oldname) && !String.Equals(oldname, _name, StringComparison.OrdinalIgnoreCase)) + { + Worksheet.PivotTables.Delete(oldname); + Worksheet.PivotTables.Add(_name, this); + } + } + } public IXLPivotTable SetName(String value) { Name = value; return this; } @@ -323,7 +359,8 @@ AllowMultipleFilters = true; // Multiple Field Filters SortFieldsAtoZ = false; // Default Sort Order UseCustomListsForSorting = true; // Custom List AutoSort - } + + public IXLWorksheet Worksheet { get; } } } diff --git a/ClosedXML/Excel/PivotTables/XLPivotTables.cs b/ClosedXML/Excel/PivotTables/XLPivotTables.cs index 6a71642..7bb7ac3 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotTables.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotTables.cs @@ -6,7 +6,12 @@ { internal class XLPivotTables : IXLPivotTables { - private readonly Dictionary _pivotTables = new Dictionary(); + private readonly Dictionary _pivotTables = new Dictionary(StringComparer.OrdinalIgnoreCase); + + public XLPivotTables(IXLWorksheet worksheet) + { + this.Worksheet = worksheet ?? throw new ArgumentNullException(nameof(worksheet)); + } public void Add(String name, IXLPivotTable pivotTable) { @@ -15,11 +20,21 @@ public IXLPivotTable AddNew(string name, IXLCell target, IXLRange source) { - var pivotTable = new XLPivotTable { Name = name, TargetCell = target, SourceRange = source }; + var pivotTable = new XLPivotTable(this.Worksheet) + { + Name = name, + TargetCell = target, + SourceRange = source + }; _pivotTables.Add(name, pivotTable); return pivotTable; } + public Boolean Contains(String name) + { + return _pivotTables.ContainsKey(name); + } + public void Delete(String name) { _pivotTables.Remove(name); @@ -49,5 +64,7 @@ { return PivotTable(name); } + + public IXLWorksheet Worksheet { get; private set; } } } diff --git a/ClosedXML/Excel/Tables/IXLTables.cs b/ClosedXML/Excel/Tables/IXLTables.cs index cf864d3..98f19dc 100644 --- a/ClosedXML/Excel/Tables/IXLTables.cs +++ b/ClosedXML/Excel/Tables/IXLTables.cs @@ -7,18 +7,20 @@ { void Add(IXLTable table); - IXLTable Table(Int32 index); - - IXLTable Table(String name); - /// /// Clears the contents of these tables. /// /// Specify what you want to clear. IXLTables Clear(XLClearOptions clearOptions = XLClearOptions.All); + Boolean Contains(String name); + void Remove(Int32 index); void Remove(String name); + + IXLTable Table(Int32 index); + + IXLTable Table(String name); } } diff --git a/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/Excel/Tables/XLTable.cs index b7a791c..b1068d7 100644 --- a/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/Excel/Tables/XLTable.cs @@ -234,7 +234,7 @@ if (_fieldNames?.Any() ?? false) this.Fields.ForEach(f => (f as XLTableField).UpdateTableFieldTotalsRowFormula()); - if (!String.IsNullOrWhiteSpace(oldname)) + if (!String.IsNullOrWhiteSpace(oldname) && !String.Equals(oldname, _name, StringComparison.OrdinalIgnoreCase)) { Worksheet.Tables.Add(this); Worksheet.Tables.Remove(oldname); diff --git a/ClosedXML/Excel/Tables/XLTables.cs b/ClosedXML/Excel/Tables/XLTables.cs index 9ae8e7f..2773e51 100644 --- a/ClosedXML/Excel/Tables/XLTables.cs +++ b/ClosedXML/Excel/Tables/XLTables.cs @@ -9,16 +9,34 @@ internal class XLTables : IXLTables { private readonly Dictionary _tables; - internal ICollection Deleted { get; private set; } public XLTables() { - _tables = new Dictionary(); + _tables = new Dictionary(StringComparer.OrdinalIgnoreCase); Deleted = new HashSet(); } + internal ICollection Deleted { get; private set; } + #region IXLTables Members + public void Add(IXLTable table) + { + _tables.Add(table.Name, table); + (table as XLTable)?.OnAddedToTables(); + } + + public IXLTables Clear(XLClearOptions clearOptions = XLClearOptions.All) + { + _tables.Values.ForEach(t => t.Clear(clearOptions)); + return this; + } + + public Boolean Contains(String name) + { + return _tables.ContainsKey(name); + } + public IEnumerator GetEnumerator() { return _tables.Values.GetEnumerator(); @@ -29,30 +47,6 @@ return GetEnumerator(); } - public void Add(IXLTable table) - { - _tables.Add(table.Name, table); - (table as XLTable)?.OnAddedToTables(); - } - - public IXLTable Table(Int32 index) - { - return _tables.ElementAt(index).Value; - } - - public IXLTable Table(String name) - { - return _tables[name]; - } - - #endregion IXLTables Members - - public IXLTables Clear(XLClearOptions clearOptions = XLClearOptions.All) - { - _tables.Values.ForEach(t => t.Clear(clearOptions)); - return this; - } - public void Remove(Int32 index) { this.Remove(_tables.ElementAt(index).Key); @@ -68,5 +62,17 @@ if (table.RelId != null) Deleted.Add(table.RelId); } + + public IXLTable Table(Int32 index) + { + return _tables.ElementAt(index).Value; + } + + public IXLTable Table(String name) + { + return _tables[name]; + } + + #endregion IXLTables Members } } diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs index 826f35b..a8946b3 100644 --- a/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/Excel/XLWorksheet.cs @@ -62,7 +62,7 @@ Tables = new XLTables(); Hyperlinks = new XLHyperlinks(); DataValidations = new XLDataValidations(); - PivotTables = new XLPivotTables(); + PivotTables = new XLPivotTables(this); Protection = new XLSheetProtection(); AutoFilter = new XLAutoFilter(); ConditionalFormats = new XLConditionalFormats(); diff --git a/ClosedXML/Excel/XLWorksheets.cs b/ClosedXML/Excel/XLWorksheets.cs index 0fd1528..62b379a 100644 --- a/ClosedXML/Excel/XLWorksheets.cs +++ b/ClosedXML/Excel/XLWorksheets.cs @@ -13,7 +13,7 @@ #region Constructor private readonly XLWorkbook _workbook; - private readonly Dictionary _worksheets = new Dictionary(); + private readonly Dictionary _worksheets = new Dictionary(StringComparer.OrdinalIgnoreCase); internal ICollection Deleted { get; private set; } #endregion Constructor @@ -45,9 +45,14 @@ get { return _worksheets.Count; } } + public Boolean Contains(String sheetName) + { + return _worksheets.ContainsKey(sheetName); + } + public bool TryGetWorksheet(string sheetName, out IXLWorksheet worksheet) { - if (_worksheets.TryGetValue(sheetName.UnescapeSheetName().ToLowerInvariant(), out XLWorksheet w)) + if (_worksheets.TryGetValue(sheetName.UnescapeSheetName(), out XLWorksheet w)) { worksheet = w; return true; @@ -60,12 +65,11 @@ { sheetName = sheetName.UnescapeSheetName(); - if (_worksheets.TryGetValue(sheetName.ToLowerInvariant(), out XLWorksheet w)) + if (_worksheets.TryGetValue(sheetName, out XLWorksheet w)) return w; - var wss = _worksheets.Where(ws => string.Equals(ws.Key, sheetName, StringComparison.OrdinalIgnoreCase)); - if (wss.Any()) - return wss.First().Value; + if (_worksheets.ContainsKey(sheetName)) + return _worksheets[sheetName]; throw new ArgumentException("There isn't a worksheet named '" + sheetName + "'."); } @@ -105,15 +109,15 @@ private void Add(String sheetName, XLWorksheet sheet) { - if (_worksheets.Any(ws => ws.Key.Equals(sheetName, StringComparison.OrdinalIgnoreCase))) + if (_worksheets.ContainsKey(sheetName)) throw new ArgumentException(String.Format("A worksheet with the same name ({0}) has already been added.", sheetName), nameof(sheetName)); - _worksheets.Add(sheetName.ToLowerInvariant(), sheet); + _worksheets.Add(sheetName, sheet); } public void Delete(String sheetName) { - Delete(_worksheets[sheetName.ToLowerInvariant()].Position); + Delete(_worksheets[sheetName].Position); } public void Delete(Int32 position) @@ -169,14 +173,14 @@ public void Rename(String oldSheetName, String newSheetName) { - if (String.IsNullOrWhiteSpace(oldSheetName) || !_worksheets.ContainsKey(oldSheetName.ToLowerInvariant())) return; + if (String.IsNullOrWhiteSpace(oldSheetName) || !_worksheets.ContainsKey(oldSheetName)) return; if (!oldSheetName.Equals(newSheetName, StringComparison.OrdinalIgnoreCase) - && _worksheets.Any(ws1 => ws1.Key.Equals(newSheetName, StringComparison.OrdinalIgnoreCase))) + && _worksheets.ContainsKey(newSheetName)) throw new ArgumentException(String.Format("A worksheet with the same name ({0}) has already been added.", newSheetName), nameof(newSheetName)); - var ws = _worksheets[oldSheetName.ToLowerInvariant()]; - _worksheets.Remove(oldSheetName.ToLowerInvariant()); + var ws = _worksheets[oldSheetName]; + _worksheets.Remove(oldSheetName); Add(newSheetName, ws); } }