diff --git a/ClosedXML/Excel/Drawings/XLPictures.cs b/ClosedXML/Excel/Drawings/XLPictures.cs index e5832e0..c539d0e 100644 --- a/ClosedXML/Excel/Drawings/XLPictures.cs +++ b/ClosedXML/Excel/Drawings/XLPictures.cs @@ -17,7 +17,7 @@ public XLPictures(XLWorksheet worksheet) { _worksheet = worksheet; - Deleted = new HashSet(); + Deleted = new HashSet(); } public int Count diff --git a/ClosedXML/Excel/Tables/XLTables.cs b/ClosedXML/Excel/Tables/XLTables.cs index 485bb7c..76e9185 100644 --- a/ClosedXML/Excel/Tables/XLTables.cs +++ b/ClosedXML/Excel/Tables/XLTables.cs @@ -6,9 +6,16 @@ { using System.Collections; - public class XLTables : IXLTables + internal class XLTables : IXLTables { - private readonly Dictionary _tables = new Dictionary(); + private readonly Dictionary _tables; + internal ICollection Deleted { get; private set; } + + public XLTables() + { + _tables = new Dictionary(); + Deleted = new HashSet(); + } #region IXLTables Members @@ -37,7 +44,7 @@ return _tables[name]; } - #endregion + #endregion IXLTables Members public IXLTables Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) { @@ -47,11 +54,18 @@ public void Remove(Int32 index) { - _tables.Remove(_tables.ElementAt(index).Key); + this.Remove(_tables.ElementAt(index).Key); } + public void Remove(String name) { + if (!_tables.ContainsKey(name)) + throw new ArgumentOutOfRangeException(nameof(name), $"Unable to delete table because the table name {name} could not be found."); + + var table = _tables[name] as XLTable; _tables.Remove(name); + + if (table.RelId != null) Deleted.Add(table.RelId); } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index 76e8637..aacb2ab 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -170,14 +170,14 @@ var sheets = dSpreadsheet.WorkbookPart.Workbook.Sheets; Int32 position = 0; - foreach (Sheet dSheet in sheets.OfType()) + foreach (var dSheet in sheets.OfType()) { position++; var sharedFormulasR1C1 = new Dictionary(); - var wsPart = dSpreadsheet.WorkbookPart.GetPartById(dSheet.Id) as WorksheetPart; + var worksheetPart = dSpreadsheet.WorkbookPart.GetPartById(dSheet.Id) as WorksheetPart; - if (wsPart == null) + if (worksheetPart == null) { UnsupportedSheets.Add(new UnsupportedSheet { SheetId = dSheet.SheetId.Value, Position = position }); continue; @@ -195,7 +195,7 @@ var styleList = new Dictionary();// {{0, ws.Style}}; PageSetupProperties pageSetupProperties = null; - using (var reader = OpenXmlReader.Create(wsPart)) + using (var reader = OpenXmlReader.Create(worksheetPart)) { Type[] ignoredElements = new Type[] { @@ -253,7 +253,7 @@ else if (reader.ElementType == typeof(ConditionalFormatting)) LoadConditionalFormatting((ConditionalFormatting)reader.LoadCurrentElement(), ws, differentialFormats); else if (reader.ElementType == typeof(Hyperlinks)) - LoadHyperlinks((Hyperlinks)reader.LoadCurrentElement(), wsPart, ws); + LoadHyperlinks((Hyperlinks)reader.LoadCurrentElement(), worksheetPart, ws); else if (reader.ElementType == typeof(PrintOptions)) LoadPrintOptions((PrintOptions)reader.LoadCurrentElement(), ws); else if (reader.ElementType == typeof(PageMargins)) @@ -278,15 +278,19 @@ #region LoadTables - foreach (var tablePart in wsPart.TableDefinitionParts) + foreach (var tableDefinitionPart in worksheetPart.TableDefinitionParts) { - var dTable = tablePart.Table; + var relId = worksheetPart.GetIdOfPart(tableDefinitionPart); + var dTable = tableDefinitionPart.Table; + String reference = dTable.Reference.Value; String tableName = dTable?.Name ?? dTable.DisplayName ?? string.Empty; if (String.IsNullOrWhiteSpace(tableName)) throw new InvalidDataException("The table name is missing."); - XLTable xlTable = ws.Range(reference).CreateTable(tableName, false) as XLTable; + var xlTable = ws.Range(reference).CreateTable(tableName, false) as XLTable; + xlTable.RelId = relId; + if (dTable.HeaderRowCount != null && dTable.HeaderRowCount == 0) { xlTable._showHeaderRow = false; @@ -358,18 +362,18 @@ #endregion - LoadDrawings(wsPart, ws); + LoadDrawings(worksheetPart, ws); #region LoadComments - if (wsPart.WorksheetCommentsPart != null) + if (worksheetPart.WorksheetCommentsPart != null) { - var root = wsPart.WorksheetCommentsPart.Comments; + var root = worksheetPart.WorksheetCommentsPart.Comments; var authors = root.GetFirstChild().ChildElements; var comments = root.GetFirstChild().ChildElements; // **** MAYBE FUTURE SHAPE SIZE SUPPORT - XDocument xdoc = GetCommentVmlFile(wsPart); + XDocument xdoc = GetCommentVmlFile(worksheetPart); foreach (Comment c in comments) { @@ -438,15 +442,15 @@ #region Pivot tables // Delay loading of pivot tables until all sheets have been loaded - foreach (Sheet dSheet in sheets.OfType()) + foreach (var dSheet in sheets.OfType()) { - var wsPart = dSpreadsheet.WorkbookPart.GetPartById(dSheet.Id) as WorksheetPart; + var worksheetPart = dSpreadsheet.WorkbookPart.GetPartById(dSheet.Id) as WorksheetPart; - if (wsPart != null) + if (worksheetPart != null) { var ws = (XLWorksheet)WorksheetsInternal.Worksheet(dSheet.Name); - foreach (var pivotTablePart in wsPart.PivotTableParts) + foreach (var pivotTablePart in worksheetPart.PivotTableParts) { var pivotTableCacheDefinitionPart = pivotTablePart.PivotTableCacheDefinitionPart; var pivotTableDefinition = pivotTablePart.PivotTableDefinition; @@ -489,7 +493,7 @@ if (!String.IsNullOrWhiteSpace(StringValue.ToString(pivotTableDefinition?.RowHeaderCaption ?? String.Empty))) pt.SetRowHeaderCaption(StringValue.ToString(pivotTableDefinition.RowHeaderCaption)); - pt.RelId = wsPart.GetIdOfPart(pivotTablePart); + pt.RelId = worksheetPart.GetIdOfPart(pivotTablePart); pt.CacheDefinitionRelId = pivotTablePart.GetIdOfPart(pivotTableCacheDefinitionPart); pt.WorkbookCacheRelId = dSpreadsheet.WorkbookPart.GetIdOfPart(pivotTableCacheDefinitionPart); @@ -545,7 +549,7 @@ var pivotTableStyle = pivotTableDefinition.GetFirstChild(); if (pivotTableStyle != null) { - pt.Theme = (XLPivotTableTheme) Enum.Parse(typeof(XLPivotTableTheme), pivotTableStyle.Name); + pt.Theme = (XLPivotTableTheme)Enum.Parse(typeof(XLPivotTableTheme), pivotTableStyle.Name); pt.ShowRowHeaders = pivotTableStyle.ShowRowHeaders; pt.ShowColumnHeaders = pivotTableStyle.ShowColumnHeaders; pt.ShowRowStripes = pivotTableStyle.ShowRowStripes; @@ -586,7 +590,7 @@ if (pivotField != null) { - LoadFieldOptions(pf, pivotField); + LoadFieldOptions(pf, pivotField); LoadSubtotals(pf, pivotField); if (pf.SortType != null) @@ -623,7 +627,7 @@ if (pivotField != null) { - LoadFieldOptions(pf, pivotField); + LoadFieldOptions(pf, pivotField); LoadSubtotals(pf, pivotField); if (pf.SortType != null) @@ -1557,7 +1561,6 @@ nf.NumberFormatId = (Int32)nfSource.NumberFormatId.Value; else if (nfSource.FormatCode != null) nf.Format = nfSource.FormatCode.Value; - } private void LoadBorder(Border borderSource, IXLBorder border) diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index 98fa3c8..a761ef1 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -263,15 +263,10 @@ foreach (var worksheet in WorksheetsInternal.Cast().OrderBy(w => w.Position)) { - //context.RelIdGenerator.Reset(RelType.); WorksheetPart worksheetPart; var wsRelId = worksheet.RelId; if (workbookPart.Parts.Any(p => p.RelationshipId == wsRelId)) - { worksheetPart = (WorksheetPart)workbookPart.GetPartById(wsRelId); - var wsPartsToRemove = worksheetPart.TableDefinitionParts.ToList(); - wsPartsToRemove.ForEach(tdp => worksheetPart.DeletePart(tdp)); - } else worksheetPart = workbookPart.AddNewPart(wsRelId); @@ -396,22 +391,60 @@ } } - private static void GenerateTables(XLWorksheet worksheet, WorksheetPart worksheetPart, SaveContext context) + private static void GenerateTables(XLWorksheet worksheet, WorksheetPart worksheetPart, SaveContext context, XLWSContentManager cm) { - worksheetPart.Worksheet.RemoveAllChildren(); + var tables = worksheet.Tables as XLTables; - if (!worksheet.Tables.Any()) return; - - foreach (var table in worksheet.Tables) + TableParts tableParts; + if (worksheetPart.Worksheet.Elements().Any()) { - var tableRelId = context.RelIdGenerator.GetNext(RelType.Workbook); - - var xlTable = (XLTable)table; - xlTable.RelId = tableRelId; - - var tableDefinitionPart = worksheetPart.AddNewPart(tableRelId); - GenerateTableDefinitionPartContent(tableDefinitionPart, xlTable, context); + tableParts = worksheetPart.Worksheet.Elements().First(); } + else + { + var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.TableParts); + tableParts = new TableParts(); + worksheetPart.Worksheet.InsertAfter(tableParts, previousElement); + } + cm.SetElement(XLWSContentManager.XLWSContents.TableParts, tableParts); + + foreach (var deletedTableRelId in tables.Deleted) + { + if (worksheetPart.TableDefinitionParts != null) + { + var tableDefinitionPart = worksheetPart.GetPartById(deletedTableRelId); + worksheetPart.DeletePart(tableDefinitionPart); + + var tablePartsToRemove = tableParts.OfType().Where(tp => tp.Id?.Value == deletedTableRelId).ToList(); + tablePartsToRemove.ForEach(tp => tableParts.RemoveChild(tp)); + } + } + + tables.Deleted.Clear(); + + foreach (var xlTable in worksheet.Tables.Cast()) + { + if (String.IsNullOrEmpty(xlTable.RelId)) + xlTable.RelId = context.RelIdGenerator.GetNext(RelType.Workbook); + + var relId = xlTable.RelId; + + TableDefinitionPart tableDefinitionPart; + if (worksheetPart.HasPartWithId(relId)) + tableDefinitionPart = worksheetPart.GetPartById(relId) as TableDefinitionPart; + else + tableDefinitionPart = worksheetPart.AddNewPart(relId); + + GenerateTableDefinitionPartContent(tableDefinitionPart, xlTable, context); + + if (!tableParts.OfType().Any(tp => tp.Id == xlTable.RelId)) + { + var tablePart = new TablePart { Id = xlTable.RelId }; + tableParts.AppendChild(tablePart); + } + } + + tableParts.Count = (UInt32)worksheet.Tables.Count(); } private void GenerateExtendedFilePropertiesPartContent(ExtendedFilePropertiesPart extendedFilePropertiesPart) @@ -1796,8 +1829,7 @@ return name; } - private static void GenerateTableDefinitionPartContent(TableDefinitionPart tableDefinitionPart, XLTable xlTable, - SaveContext context) + private static void GenerateTableDefinitionPartContent(TableDefinitionPart tableDefinitionPart, XLTable xlTable, SaveContext context) { context.TableId++; var reference = xlTable.RangeAddress.FirstAddress + ":" + xlTable.RangeAddress.LastAddress; @@ -2326,7 +2358,7 @@ { pf.SubtotalCaption = xlpf.SubtotalCaption; } - + if (pt.ClassicPivotTableLayout) { pf.Outline = false; @@ -2534,6 +2566,7 @@ } #region Excel 2010 Features + if (xlpf.RepeatItemLabels) { var pivotFieldExtensionList = new PivotFieldExtensionList(); @@ -2548,6 +2581,7 @@ pivotFieldExtensionList.AppendChild(pivotFieldExtension); pf.AppendChild(pivotFieldExtensionList); } + #endregion Excel 2010 Features pivotFields.AppendChild(pf); @@ -4191,8 +4225,6 @@ if (worksheetPart.Worksheet == null) worksheetPart.Worksheet = new Worksheet(); - GenerateTables(xlWorksheet, worksheetPart, context); - if ( !worksheetPart.Worksheet.NamespaceDeclarations.Contains(new KeyValuePair("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"))) @@ -5344,20 +5376,7 @@ #region Tables - worksheetPart.Worksheet.RemoveAllChildren(); - { - var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.TableParts); - worksheetPart.Worksheet.InsertAfter(new TableParts(), previousElement); - } - - var tableParts = worksheetPart.Worksheet.Elements().First(); - cm.SetElement(XLWSContentManager.XLWSContents.TableParts, tableParts); - - tableParts.Count = (UInt32)xlWorksheet.Tables.Count(); - foreach ( - var tablePart in - from XLTable xlTable in xlWorksheet.Tables select new TablePart { Id = xlTable.RelId }) - tableParts.AppendChild(tablePart); + GenerateTables(xlWorksheet, worksheetPart, context, cm); #endregion Tables @@ -5381,6 +5400,7 @@ if (xlWorksheet.Pictures.Any()) RebasePictureIds(worksheetPart); + var tableParts = worksheetPart.Worksheet.Elements().First(); if (xlWorksheet.Pictures.Any() && !worksheetPart.Worksheet.OfType().Any()) { var worksheetDrawing = new Drawing { Id = worksheetPart.GetIdOfPart(worksheetPart.DrawingsPart) }; @@ -5396,7 +5416,6 @@ worksheetPart.DeletePart(worksheetPart.DrawingsPart); } - #endregion Drawings #region LegacyDrawing @@ -5413,7 +5432,6 @@ cm.SetElement(XLWSContentManager.XLWSContents.LegacyDrawing, worksheetPart.Worksheet.Elements().First()); } - } #endregion LegacyDrawing @@ -5567,12 +5585,14 @@ filterColumn.Append(top101); break; + case XLFilterType.Dynamic: var dynamicFilter = new DynamicFilter { Type = xlFilterColumn.DynamicType.ToOpenXml(), Val = xlFilterColumn.DynamicValue }; filterColumn.Append(dynamicFilter); break; + case XLFilterType.DateTimeGrouping: var dateTimeGroupFilters = new Filters(); foreach (var filter in kp.Value) @@ -5607,7 +5627,6 @@ filterColumn.Append(filters); break; - } autoFilter.Append(filterColumn); } diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs index 1c7b41b..3769bfd 100644 --- a/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/Excel/XLWorksheet.cs @@ -628,7 +628,7 @@ targetSheet.NamedRanges.Add(nr.Name, ranges); } - foreach (XLTable t in Tables.Cast()) + foreach (var t in Tables.Cast()) { String tableName = t.Name; var table = targetSheet.Tables.Any(tt => tt.Name == tableName) @@ -1577,6 +1577,7 @@ { return Pictures.Add(imageFile, name); } + public override Boolean IsEntireRow() { return true; @@ -1596,6 +1597,5 @@ else this.Cell(ro, co).SetValue(value); } - } } diff --git a/ClosedXML/Excel/XLWorksheets.cs b/ClosedXML/Excel/XLWorksheets.cs index cfb0478..94cace4 100644 --- a/ClosedXML/Excel/XLWorksheets.cs +++ b/ClosedXML/Excel/XLWorksheets.cs @@ -14,16 +14,16 @@ private readonly XLWorkbook _workbook; private readonly Dictionary _worksheets = new Dictionary(); + internal ICollection Deleted { get; private set; } #endregion Constructor - public HashSet Deleted = new HashSet(); - #region Constructor public XLWorksheets(XLWorkbook workbook) { _workbook = workbook; + Deleted = new HashSet(); } #endregion Constructor diff --git a/ClosedXML_Tests/Excel/Tables/TablesTests.cs b/ClosedXML_Tests/Excel/Tables/TablesTests.cs index 9d30ab8..f8fcddc 100644 --- a/ClosedXML_Tests/Excel/Tables/TablesTests.cs +++ b/ClosedXML_Tests/Excel/Tables/TablesTests.cs @@ -441,6 +441,41 @@ } [Test] + public void CanDeleteTable() + { + var l = new List() + { + new TestObjectWithAttributes() { Column1 = "a", Column2 = "b", MyField = 4, UnOrderedColumn = 999 }, + new TestObjectWithAttributes() { Column1 = "c", Column2 = "d", MyField = 5, UnOrderedColumn = 777 } + }; + + using (var ms = new MemoryStream()) + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().InsertTable(l); + //wb.SaveAs(ms); + + wb.SaveAs(@"c:\temp\deletetable1.xlsx"); + } + + ms.Seek(0, SeekOrigin.Begin); + + using (var wb = new XLWorkbook(@"c:\temp\deletetable1.xlsx")) + { + var ws = wb.Worksheets.First(); + var table = ws.Tables.First(); + + ws.Tables.Remove(table.Name); + Assert.AreEqual(0, ws.Tables.Count()); + //wb.Save(); + wb.SaveAs(@"c:\temp\deletetable2.xlsx"); + } + } + } + + [Test] public void CanDeleteTableField() { var l = new List() diff --git a/ClosedXML_Tests/Resource/Examples/AutoFilter/RegularAutoFilter.xlsx b/ClosedXML_Tests/Resource/Examples/AutoFilter/RegularAutoFilter.xlsx index 1e430a1..b4d626c 100644 --- a/ClosedXML_Tests/Resource/Examples/AutoFilter/RegularAutoFilter.xlsx +++ b/ClosedXML_Tests/Resource/Examples/AutoFilter/RegularAutoFilter.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/AddingDataSet.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/AddingDataSet.xlsx index cd861ef..12cb876 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/AddingDataSet.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/AddingDataSet.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/AddingDataTableAsWorksheet.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/AddingDataTableAsWorksheet.xlsx index 7c3a2ec..c3e6540 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/AddingDataTableAsWorksheet.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/AddingDataTableAsWorksheet.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx index c24dcf3..40d3c0f 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/ShowCase.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/ShowCase.xlsx index 6e45f45..0d7a0b2 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/ShowCase.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/ShowCase.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx index 7e934f9..0c5a347 100644 --- a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/AddingRowToTables.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/AddingRowToTables.xlsx index 014ea30..2f0ed47 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/AddingRowToTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/AddingRowToTables.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/SortExample.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/SortExample.xlsx index e426613..a3db8ac 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/SortExample.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/SortExample.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Tables/InsertingTables.xlsx b/ClosedXML_Tests/Resource/Examples/Tables/InsertingTables.xlsx index fe5e52c..97daf3b 100644 --- a/ClosedXML_Tests/Resource/Examples/Tables/InsertingTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Tables/InsertingTables.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Tables/ResizingTables.xlsx b/ClosedXML_Tests/Resource/Examples/Tables/ResizingTables.xlsx index 5d223ac..c86cfe2 100644 --- a/ClosedXML_Tests/Resource/Examples/Tables/ResizingTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Tables/ResizingTables.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Tables/UsingTables.xlsx b/ClosedXML_Tests/Resource/Examples/Tables/UsingTables.xlsx index 39402fe..e18c66c 100644 --- a/ClosedXML_Tests/Resource/Examples/Tables/UsingTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Tables/UsingTables.xlsx Binary files differ