diff --git a/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/Excel/Tables/XLTable.cs index 5e8f3a6..4e193cf 100644 --- a/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/Excel/Tables/XLTable.cs @@ -107,7 +107,7 @@ } - public String RelId { get; set; } + internal String RelId { get; set; } public IXLTableRange DataRange { diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index b551198..006fe16 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -176,6 +176,7 @@ ws.Visibility = dSheet.State.Value.ToClosedXml(); var styleList = new Dictionary();// {{0, ws.Style}}; + PageSetupProperties pageSetupProperties = null; using (var reader = OpenXmlReader.Create(wsPart)) { @@ -233,11 +234,11 @@ else if (reader.ElementType == typeof(PageMargins)) LoadPageMargins((PageMargins)reader.LoadCurrentElement(), ws); else if (reader.ElementType == typeof(PageSetup)) - LoadPageSetup((PageSetup)reader.LoadCurrentElement(), ws); + LoadPageSetup((PageSetup)reader.LoadCurrentElement(), ws, pageSetupProperties); else if (reader.ElementType == typeof(HeaderFooter)) LoadHeaderFooter((HeaderFooter)reader.LoadCurrentElement(), ws); else if (reader.ElementType == typeof(SheetProperties)) - LoadSheetProperties((SheetProperties)reader.LoadCurrentElement(), ws); + LoadSheetProperties((SheetProperties)reader.LoadCurrentElement(), ws, out pageSetupProperties); else if (reader.ElementType == typeof(RowBreaks)) LoadRowBreaks((RowBreaks)reader.LoadCurrentElement(), ws); else if (reader.ElementType == typeof(ColumnBreaks)) @@ -1598,28 +1599,34 @@ ws.PageSetup.RowBreaks.Add(Int32.Parse(rowBreak.Id.InnerText)); } - private void LoadSheetProperties(SheetProperties sheetProperty, XLWorksheet ws) + private void LoadSheetProperties(SheetProperties sheetProperty, XLWorksheet ws, out PageSetupProperties pageSetupProperties) { + pageSetupProperties = null; if (sheetProperty == null) return; if (sheetProperty.TabColor != null) ws.TabColor = GetColor(sheetProperty.TabColor); - if (sheetProperty.OutlineProperties == null) return; - - if (sheetProperty.OutlineProperties.SummaryBelow != null) + if (sheetProperty.OutlineProperties != null) { - ws.Outline.SummaryVLocation = sheetProperty.OutlineProperties.SummaryBelow - ? XLOutlineSummaryVLocation.Bottom - : XLOutlineSummaryVLocation.Top; + + if (sheetProperty.OutlineProperties.SummaryBelow != null) + { + ws.Outline.SummaryVLocation = sheetProperty.OutlineProperties.SummaryBelow + ? XLOutlineSummaryVLocation.Bottom + : XLOutlineSummaryVLocation.Top; + } + + if (sheetProperty.OutlineProperties.SummaryRight != null) + { + ws.Outline.SummaryHLocation = sheetProperty.OutlineProperties.SummaryRight + ? XLOutlineSummaryHLocation.Right + : XLOutlineSummaryHLocation.Left; + } } - if (sheetProperty.OutlineProperties.SummaryRight != null) - { - ws.Outline.SummaryHLocation = sheetProperty.OutlineProperties.SummaryRight - ? XLOutlineSummaryHLocation.Right - : XLOutlineSummaryHLocation.Left; - } + if (sheetProperty.PageSetupProperties != null) + pageSetupProperties = sheetProperty.PageSetupProperties; } private static void LoadHeaderFooter(HeaderFooter headerFooter, XLWorksheet ws) @@ -1663,7 +1670,7 @@ ((XLHeaderFooter)ws.PageSetup.Footer).SetAsInitial(); } - private static void LoadPageSetup(PageSetup pageSetup, XLWorksheet ws) + private static void LoadPageSetup(PageSetup pageSetup, XLWorksheet ws, PageSetupProperties pageSetupProperties) { if (pageSetup == null) return; @@ -1671,11 +1678,16 @@ ws.PageSetup.PaperSize = (XLPaperSize)Int32.Parse(pageSetup.PaperSize.InnerText); if (pageSetup.Scale != null) ws.PageSetup.Scale = Int32.Parse(pageSetup.Scale.InnerText); - else + if (pageSetupProperties != null && pageSetupProperties.FitToPage != null && pageSetupProperties.FitToPage.Value) { - if (pageSetup.FitToWidth != null) + if (pageSetup.FitToWidth == null) + ws.PageSetup.PagesWide = 1; + else ws.PageSetup.PagesWide = Int32.Parse(pageSetup.FitToWidth.InnerText); - if (pageSetup.FitToHeight != null) + + if (pageSetup.FitToHeight == null) + ws.PageSetup.PagesTall = 1; + else ws.PageSetup.PagesTall = Int32.Parse(pageSetup.FitToHeight.InnerText); } if (pageSetup.PageOrder != null) diff --git a/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs b/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs index 9bcb8a2..c4dee57 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs @@ -101,7 +101,7 @@ id++; } } - public void AddValues(List values, RelType relType) + public void AddValues(IEnumerable values, RelType relType) { if (!_relIds.ContainsKey(relType)) { diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index 81d32a3..ee3ac7d 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -222,7 +222,14 @@ worksheets.Deleted.ToList().ForEach(ws => DeleteSheetAndDependencies(workbookPart, ws)); - context.RelIdGenerator.AddValues(workbookPart.Parts.Select(p => p.RelationshipId).ToList(), RelType.Workbook); + // Ensure all RelId's have been added to the context + context.RelIdGenerator.AddValues(workbookPart.Parts.Select(p => p.RelationshipId), RelType.Workbook); + context.RelIdGenerator.AddValues(WorksheetsInternal.Cast().Where(ws => !XLHelper.IsNullOrWhiteSpace(ws.RelId)).Select(ws => ws.RelId), RelType.Workbook); + context.RelIdGenerator.AddValues(WorksheetsInternal + .Cast() + .SelectMany(ws => ws.Tables.Cast()) + .Where(t => !XLHelper.IsNullOrWhiteSpace(t.RelId)) + .Select(t => t.RelId), RelType.Workbook); var extendedFilePropertiesPart = document.ExtendedFilePropertiesPart ?? document.AddNewPart( @@ -259,13 +266,10 @@ worksheetPart = workbookPart.AddNewPart(wsRelId); - context.RelIdGenerator.AddValues(worksheetPart.HyperlinkRelationships.Select(hr => hr.Id).ToList(), - RelType.Workbook); - context.RelIdGenerator.AddValues(worksheetPart.Parts.Select(p => p.RelationshipId).ToList(), - RelType.Workbook); + context.RelIdGenerator.AddValues(worksheetPart.HyperlinkRelationships.Select(hr => hr.Id), RelType.Workbook); + context.RelIdGenerator.AddValues(worksheetPart.Parts.Select(p => p.RelationshipId), RelType.Workbook); if (worksheetPart.DrawingsPart != null) - context.RelIdGenerator.AddValues( - worksheetPart.DrawingsPart.Parts.Select(p => p.RelationshipId).ToList(), RelType.Workbook); + context.RelIdGenerator.AddValues(worksheetPart.DrawingsPart.Parts.Select(p => p.RelationshipId), RelType.Workbook); // delete comment related parts (todo: review) DeleteComments(worksheetPart, worksheet, context); @@ -581,7 +585,7 @@ foreach (var xlSheet in WorksheetsInternal.Cast().OrderBy(w => w.Position)) { string rId; - if (xlSheet.SheetId == 0) + if (xlSheet.SheetId == 0 && XLHelper.IsNullOrWhiteSpace(xlSheet.RelId)) { rId = context.RelIdGenerator.GetNext(RelType.Workbook); @@ -593,8 +597,13 @@ } else { + if (XLHelper.IsNullOrWhiteSpace(xlSheet.RelId)) + { rId = String.Format("rId{0}", xlSheet.SheetId); - context.RelIdGenerator.AddValues(new List { rId }, RelType.Workbook); + context.RelIdGenerator.AddValues(new List { rId }, RelType.Workbook); + } + else + rId = xlSheet.RelId; } if (!workbook.Sheets.Cast().Any(s => s.Id == rId)) @@ -1818,7 +1827,7 @@ PivotCaches pivotCaches; uint cacheId = 0; if (workbookPart.Workbook.PivotCaches == null) - pivotCaches = workbookPart.Workbook.AppendChild(new PivotCaches()); + pivotCaches = workbookPart.Workbook.InsertAfter(new PivotCaches(), workbookPart.Workbook.CalculationProperties); else { pivotCaches = workbookPart.Workbook.PivotCaches; @@ -1899,12 +1908,10 @@ } else { - foreach (var cellValue in source.Cells().Where(cell => - cell.Address.ColumnNumber == columnNumber && - cell.Address.RowNumber > - source.FirstRow().RowNumber()).Select( - cell => cell.Value.ToString()) - .Where(cellValue => !xlpf.SharedStrings.Contains(cellValue))) + foreach (var cellValue in source.Cells() + .Where(cell => cell.Address.ColumnNumber == columnNumber && cell.Address.RowNumber > source.FirstRow().RowNumber()) + .Select(cell => cell.Value.ToString()) + .Where(cellValue => !xlpf.SharedStrings.Select(ss => ss.ToLower()).Contains(cellValue.ToLower()))) { xlpf.SharedStrings.Add(cellValue); } @@ -4365,15 +4372,11 @@ { pageSetup.Scale = null; - if (xlWorksheet.PageSetup.PagesWide > 0) + if (xlWorksheet.PageSetup.PagesWide >= 0 && xlWorksheet.PageSetup.PagesWide != 1) pageSetup.FitToWidth = (UInt32)xlWorksheet.PageSetup.PagesWide; - else - pageSetup.FitToWidth = 0; - if (xlWorksheet.PageSetup.PagesTall > 0) + if (xlWorksheet.PageSetup.PagesTall >= 0 && xlWorksheet.PageSetup.PagesTall != 1) pageSetup.FitToHeight = (UInt32)xlWorksheet.PageSetup.PagesTall; - else - pageSetup.FitToHeight = 0; } #endregion diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs index c622c30..6bd296d 100644 --- a/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/Excel/XLWorksheet.cs @@ -121,7 +121,7 @@ internal Boolean ColumnWidthChanged { get; set; } public Int32 SheetId { get; set; } - public String RelId { get; set; } + internal String RelId { get; set; } public XLDataValidations DataValidations { get; private set; } public IXLCharts Charts { get; private set; } public XLSheetProtection Protection { get; private set; } diff --git a/ClosedXML_Examples/PivotTables/PivotTables.cs b/ClosedXML_Examples/PivotTables/PivotTables.cs index 5678228..9af9775 100644 --- a/ClosedXML_Examples/PivotTables/PivotTables.cs +++ b/ClosedXML_Examples/PivotTables/PivotTables.cs @@ -38,9 +38,11 @@ new Pastry("Danish", 394, -20.24, "Apr"), new Pastry("Danish", 190, 60, "May"), new Pastry("Danish", 221, 24.76, "June"), + + // Deliberately add different casings of same string to ensure pivot table doesn't duplicate it. new Pastry("Scone", 135, 0, "Apr"), - new Pastry("Scone", 122, 5.19, "May"), - new Pastry("Scone", 243, 44.2, "June") + new Pastry("SconE", 122, 5.19, "May"), + new Pastry("SCONE", 243, 44.2, "June") }; using (var wb = new XLWorkbook()) diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index d3b51c1..bcf86c9 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -252,6 +252,7 @@ + diff --git a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs index bfcafc2..085d0d0 100644 --- a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs +++ b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs @@ -41,6 +41,22 @@ } } + [Test] + public void CanLoadAndSaveFileWithMismatchingSheetIdAndRelId() + { + // This file's workbook.xml contains: + // + // and the mismatch between the sheetId and r:id can create problems. + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\FileWithMismatchSheetIdAndRelId.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + using (var ms = new MemoryStream()) + { + wb.SaveAs(ms, true); + } + } + } + /// /// For non-English locales, the default style ("Normal" in English) can be /// another piece of text (e.g. Обычный in Russian). diff --git a/ClosedXML_Tests/Resource/Examples/PageSetup/TwoPages.xlsx b/ClosedXML_Tests/Resource/Examples/PageSetup/TwoPages.xlsx index 1e2deb7..76199d6 100644 --- a/ClosedXML_Tests/Resource/Examples/PageSetup/TwoPages.xlsx +++ b/ClosedXML_Tests/Resource/Examples/PageSetup/TwoPages.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx index c4025e5..d28109f 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/Misc/FileWithMismatchSheetIdAndRelId.xlsx b/ClosedXML_Tests/Resource/Misc/FileWithMismatchSheetIdAndRelId.xlsx new file mode 100644 index 0000000..23c9a88 --- /dev/null +++ b/ClosedXML_Tests/Resource/Misc/FileWithMismatchSheetIdAndRelId.xlsx Binary files differ