diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFContainsConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFContainsConverter.cs index 3abf4c3..83ccf67 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFContainsConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFContainsConverter.cs @@ -11,7 +11,7 @@ public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { String val = cf.Values[1].Value; - var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = cf.Operator.ToOpenXml(), Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = ConditionalFormattingOperatorValues.ContainsText, Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; var formula = new Formula { Text = "NOT(ISERROR(SEARCH(\"" + val + "\"," + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + ")))" }; diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFEndsWithConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFEndsWithConverter.cs index 1fa1532..1425fc6 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFEndsWithConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFEndsWithConverter.cs @@ -11,7 +11,7 @@ public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { String val = cf.Values[1].Value; - var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = cf.Operator.ToOpenXml(), Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = ConditionalFormattingOperatorValues.EndsWith, Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; var formula = new Formula { Text = "RIGHT(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "," + val.Length.ToString() + ")=\"" + val + "\"" }; diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotContainsConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotContainsConverter.cs index 86dcee8..6d86b12 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotContainsConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotContainsConverter.cs @@ -11,7 +11,7 @@ public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { String val = cf.Values[1].Value; - var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = cf.Operator.ToOpenXml(), Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = ConditionalFormattingOperatorValues.NotContains, Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; var formula = new Formula { Text = "ISERROR(SEARCH(\"" + val + "\"," + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "))" }; diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFStartsWithConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFStartsWithConverter.cs index 82cfb88..7cee965 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFStartsWithConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFStartsWithConverter.cs @@ -11,7 +11,7 @@ public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) { String val = cf.Values[1].Value; - var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = cf.Operator.ToOpenXml(), Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = ConditionalFormattingOperatorValues.BeginsWith, Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; var formula = new Formula { Text = "LEFT(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "," + val.Length.ToString() + ")=\"" + val + "\"" }; diff --git a/ClosedXML/Excel/PageSetup/XLPageSetup.cs b/ClosedXML/Excel/PageSetup/XLPageSetup.cs index 9aca46d..8fdc197 100644 --- a/ClosedXML/Excel/PageSetup/XLPageSetup.cs +++ b/ClosedXML/Excel/PageSetup/XLPageSetup.cs @@ -192,11 +192,13 @@ { if (!RowBreaks.Contains(row)) RowBreaks.Add(row); + RowBreaks.Sort(); } public void AddVerticalPageBreak(Int32 column) { if (!ColumnBreaks.Contains(column)) ColumnBreaks.Add(column); + ColumnBreaks.Sort(); } //public void SetPageBreak(IXLRange range, XLPageBreakLocations breakLocation) diff --git a/ClosedXML/Excel/PivotTables/XLPivotTable.cs b/ClosedXML/Excel/PivotTables/XLPivotTable.cs index b6020cd..c762261 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotTable.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotTable.cs @@ -91,6 +91,10 @@ public IXLPivotTable SetInsertBlankLines() { InsertBlankLines = true; return this; } public IXLPivotTable SetInsertBlankLines(Boolean value) { InsertBlankLines = value; return this; } + internal String RelId { get; set; } + internal String CacheDefinitionRelId { get; set; } + internal String WorkbookCacheRelId { get; set; } + private void SetExcelDefaults() { EmptyCellReplacement = String.Empty; 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/XLConstants.cs b/ClosedXML/Excel/XLConstants.cs index d1cbe58..bf4a2a8 100644 --- a/ClosedXML/Excel/XLConstants.cs +++ b/ClosedXML/Excel/XLConstants.cs @@ -4,6 +4,7 @@ public static class XLConstants { public const string PivotTableValuesSentinalLabel = "{{Values}}"; + public const int NumberOfBuiltInStyles = 163; internal static class Comment { diff --git a/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/Excel/XLWorkbook.cs index 3cfe9ef..8eaf483 100644 --- a/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/Excel/XLWorkbook.cs @@ -406,6 +406,23 @@ return false; } + public IXLRange RangeFromFullAddress(String rangeAddress, out IXLWorksheet ws) + { + ws = null; + if (!rangeAddress.Contains('!')) return null; + + var split = rangeAddress.Split('!'); + var first = split[0]; + var wsName = first.StartsWith("'") ? first.Substring(1, first.Length - 2) : first; + var localRange = split[1]; + if (TryGetWorksheet(wsName, out ws)) + { + return ws.Range(localRange); + } + return null; + } + + /// /// Saves the current workbook. /// @@ -739,17 +756,23 @@ return Ranges(namedCells).Cells(); } - public IXLRange Range(String namedRange) + public IXLRange Range(String range) { - var range = NamedRange(namedRange); - if (range == null) return null; - return range.Ranges.FirstOrDefault(); + var namedRange = NamedRange(range); + if (namedRange != null) + return namedRange.Ranges.FirstOrDefault(); + else + { + IXLWorksheet ws; + var r = RangeFromFullAddress(range, out ws); + return r; + } } - public IXLRanges Ranges(String namedRanges) + public IXLRanges Ranges(String ranges) { var retVal = new XLRanges(); - var rangePairs = namedRanges.Split(','); + var rangePairs = ranges.Split(','); foreach (var range in rangePairs.Select(r => Range(r.Trim())).Where(range => range != null)) { retVal.Add(range); diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index b551198..bb05e45 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)) @@ -400,6 +401,142 @@ } } LoadDefinedNames(workbook); + + #region Pivot tables + + // Delay loading of pivot tables until all sheets have been loaded + foreach (Sheet dSheet in sheets.OfType()) + { + var wsPart = dSpreadsheet.WorkbookPart.GetPartById(dSheet.Id) as WorksheetPart; + + if (wsPart != null) + { + var ws = (XLWorksheet)WorksheetsInternal.Worksheet(dSheet.Name); + + foreach (var pivotTablePart in wsPart.PivotTableParts) + { + var pivotTableCacheDefinitionPart = pivotTablePart.PivotTableCacheDefinitionPart; + var pivotTableDefinition = pivotTablePart.PivotTableDefinition; + + var target = ws.FirstCell(); + if (pivotTableDefinition.Location != null && pivotTableDefinition.Location.Reference != null && pivotTableDefinition.Location.Reference.HasValue) + { + target = ws.Range(pivotTableDefinition.Location.Reference.Value).FirstCell(); + } + + IXLRange source = null; + if (pivotTableCacheDefinitionPart.PivotCacheDefinition != null + && pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheSource != null + && pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheSource.WorksheetSource != null) + { + // TODO: Implement other sources besides worksheetSource (e.g. Table source?) + source = ws.Workbook.Range(pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheSource.WorksheetSource.Name.Value); + } + + if (target != null && source != null) + { + var pt = ws.PivotTables.AddNew(pivotTableDefinition.Name, target, source) as XLPivotTable; + pt.RelId = wsPart.GetIdOfPart(pivotTablePart); + pt.CacheDefinitionRelId = pivotTablePart.GetIdOfPart(pivotTableCacheDefinitionPart); + pt.WorkbookCacheRelId = dSpreadsheet.WorkbookPart.GetIdOfPart(pivotTableCacheDefinitionPart); + + if (pivotTableDefinition.MergeItem != null) pt.MergeAndCenterWithLabels = pivotTableDefinition.MergeItem.Value; + if (pivotTableDefinition.Indent != null) pt.RowLabelIndent = (int)pivotTableDefinition.Indent.Value; + if (pivotTableDefinition.PageOverThenDown != null) pt.FilterAreaOrder = pivotTableDefinition.PageOverThenDown.Value ? XLFilterAreaOrder.OverThenDown : XLFilterAreaOrder.DownThenOver; + if (pivotTableDefinition.PageWrap != null) pt.FilterFieldsPageWrap = (int)pivotTableDefinition.PageWrap.Value; + if (pivotTableDefinition.UseAutoFormatting != null) pt.AutofitColumns = pivotTableDefinition.UseAutoFormatting.Value; + if (pivotTableDefinition.PreserveFormatting != null) pt.PreserveCellFormatting = pivotTableDefinition.PreserveFormatting.Value; + if (pivotTableDefinition.RowGrandTotals != null) pt.ShowGrandTotalsRows = pivotTableDefinition.RowGrandTotals.Value; + if (pivotTableDefinition.ColumnGrandTotals != null) pt.ShowGrandTotalsColumns = pivotTableDefinition.ColumnGrandTotals.Value; + if (pivotTableDefinition.SubtotalHiddenItems != null) pt.FilteredItemsInSubtotals = pivotTableDefinition.SubtotalHiddenItems.Value; + if (pivotTableDefinition.MultipleFieldFilters != null) pt.AllowMultipleFilters = pivotTableDefinition.MultipleFieldFilters.Value; + if (pivotTableDefinition.CustomListSort != null) pt.UseCustomListsForSorting = pivotTableDefinition.CustomListSort.Value; + if (pivotTableDefinition.ShowDrill != null) pt.ShowExpandCollapseButtons = pivotTableDefinition.ShowDrill.Value; + if (pivotTableDefinition.ShowDataTips != null) pt.ShowContextualTooltips = pivotTableDefinition.ShowDataTips.Value; + if (pivotTableDefinition.ShowMemberPropertyTips != null) pt.ShowPropertiesInTooltips = pivotTableDefinition.ShowMemberPropertyTips.Value; + if (pivotTableDefinition.ShowHeaders != null) pt.DisplayCaptionsAndDropdowns = pivotTableDefinition.ShowHeaders.Value; + if (pivotTableDefinition.GridDropZones != null) pt.ClassicPivotTableLayout = pivotTableDefinition.GridDropZones.Value; + if (pivotTableDefinition.ShowEmptyRow != null) pt.ShowEmptyItemsOnRows = pivotTableDefinition.ShowEmptyRow.Value; + if (pivotTableDefinition.ShowEmptyColumn != null) pt.ShowEmptyItemsOnColumns = pivotTableDefinition.ShowEmptyColumn.Value; + if (pivotTableDefinition.ShowItems != null) pt.DisplayItemLabels = pivotTableDefinition.ShowItems.Value; + if (pivotTableDefinition.FieldListSortAscending != null) pt.SortFieldsAtoZ = pivotTableDefinition.FieldListSortAscending.Value; + if (pivotTableDefinition.PrintDrill != null) pt.PrintExpandCollapsedButtons = pivotTableDefinition.PrintDrill.Value; + if (pivotTableDefinition.ItemPrintTitles != null) pt.RepeatRowLabels = pivotTableDefinition.ItemPrintTitles.Value; + if (pivotTableDefinition.FieldPrintTitles != null) pt.PrintTitles = pivotTableDefinition.FieldPrintTitles.Value; + if (pivotTableDefinition.EnableDrill != null) pt.EnableShowDetails = pivotTableDefinition.EnableDrill.Value; + + if (pivotTableDefinition.ShowMissing != null && pivotTableDefinition.MissingCaption != null) + pt.EmptyCellReplacement = pivotTableDefinition.MissingCaption.Value; + + if (pivotTableDefinition.ShowError != null && pivotTableDefinition.ErrorCaption != null) + pt.ErrorValueReplacement = pivotTableDefinition.ErrorCaption.Value; + + // Row labels + foreach (var rf in pivotTableDefinition.RowFields.Cast()) + { + if (rf.Index.Value == -2) + pt.RowLabels.Add(XLConstants.PivotTableValuesSentinalLabel); + else if (rf.Index < pivotTableDefinition.PivotFields.Count) + { + var pf = pivotTableDefinition.PivotFields.ElementAt(rf.Index.Value) as PivotField; + if (pf != null && pf.Name != null) pt.RowLabels.Add(pf.Name.Value); + } + } + + // Column labels + foreach (var cf in pivotTableDefinition.ColumnFields.Cast()) + { + if (cf.Index.Value == -2) + pt.ColumnLabels.Add(XLConstants.PivotTableValuesSentinalLabel); + + else if (cf.Index < pivotTableDefinition.PivotFields.Count) + { + var pf = pivotTableDefinition.PivotFields.ElementAt(cf.Index.Value) as PivotField; + if (pf != null && pf.Name != null) pt.ColumnLabels.Add(pf.Name.Value); + } + } + + // Values + foreach (var df in pivotTableDefinition.DataFields.Cast()) + { + if ((int)df.Field.Value == -2) + pt.Values.Add(XLConstants.PivotTableValuesSentinalLabel); + + else if (df.Field.Value < pivotTableDefinition.PivotFields.Count) + { + var pf = pivotTableDefinition.PivotFields.ElementAt((int)df.Field.Value) as PivotField; + if (pf != null && pf.Name != null) + { + var pv = pt.Values.Add(pf.Name.Value, df.Name.Value); + if (df.NumberFormatId != null) pv.NumberFormat.SetNumberFormatId((int)df.NumberFormatId.Value); + if (df.Subtotal != null) pv = pv.SetSummaryFormula(df.Subtotal.Value.ToClosedXml()); + if (df.ShowDataAs != null) + { + var calculation = pv.Calculation; + calculation = df.ShowDataAs.Value.ToClosedXml(); + pv = pv.SetCalculation(calculation); + } + if (df.BaseField != null) { + var col = pt.SourceRange.Column(df.BaseField.Value + 1); + + var items = col.CellsUsed() + .Select(c => c.Value) + .Skip(1) // Skip header column + .Distinct().ToList(); + + pv.BaseField = col.FirstCell().GetValue(); + if (df.BaseItem != null) pv.BaseItem = items[(int)df.BaseItem.Value].ToString(); + } + } + } + } + } + } + } + } + + #endregion + } #region Comment Helpers @@ -1448,6 +1585,10 @@ } } + /// + /// Loads the conditional formatting. + /// https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.conditionalformattingrule%28v=office.15%29.aspx?f=255&MSPPError=-2147217396 + /// private void LoadConditionalFormatting(ConditionalFormatting conditionalFormatting, XLWorksheet ws, Dictionary differentialFormats) { if (conditionalFormatting == null) return; @@ -1464,18 +1605,26 @@ LoadBorder(differentialFormats[(Int32)fr.FormatId.Value].Border, conditionalFormat.Style.Border); LoadNumberFormat(differentialFormats[(Int32)fr.FormatId.Value].NumberingFormat, conditionalFormat.Style.NumberFormat); } - if (fr.Operator != null) + + // The conditional formatting type is compulsory. If it doesn't exist, skip the entire rule. + if (fr.Type == null) continue; + conditionalFormat.ConditionalFormatType = fr.Type.Value.ToClosedXml(); + + if (conditionalFormat.ConditionalFormatType == XLConditionalFormatType.CellIs && fr.Operator != null) conditionalFormat.Operator = fr.Operator.Value.ToClosedXml(); - if (fr.Type != null) - conditionalFormat.ConditionalFormatType = fr.Type.Value.ToClosedXml(); - if (fr.Text != null) + + if (fr.Text != null && !XLHelper.IsNullOrWhiteSpace(fr.Text)) conditionalFormat.Values.Add(GetFormula(fr.Text.Value)); - if (fr.Percent != null) - conditionalFormat.Percent = fr.Percent.Value; - if (fr.Bottom != null) - conditionalFormat.Bottom = fr.Bottom.Value; - if (fr.Rank != null) - conditionalFormat.Values.Add(GetFormula(fr.Rank.Value.ToString())); + + if (conditionalFormat.ConditionalFormatType == XLConditionalFormatType.Top10) + { + if (fr.Percent != null) + conditionalFormat.Percent = fr.Percent.Value; + if (fr.Bottom != null) + conditionalFormat.Bottom = fr.Bottom.Value; + if (fr.Rank != null) + conditionalFormat.Values.Add(GetFormula(fr.Rank.Value.ToString())); + } if (fr.Elements().Any()) { @@ -1598,28 +1747,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 +1818,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 +1826,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) @@ -2025,4 +2185,4 @@ return false; } } -} \ No newline at end of file +} 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 283e18d..12d56bf 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -64,9 +64,9 @@ switch (xlCell.DataType) { case XLCellValues.Text: - { - return xlCell.ShareString ? CvSharedString : CvInlineString; - } + { + return xlCell.ShareString ? CvSharedString : CvInlineString; + } case XLCellValues.Number: return CvNumber; case XLCellValues.DateTime: @@ -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); @@ -402,13 +406,13 @@ } if (properties.Application == null) - properties.AppendChild(new Application {Text = "Microsoft Excel"}); + properties.AppendChild(new Application { Text = "Microsoft Excel" }); if (properties.DocumentSecurity == null) - properties.AppendChild(new DocumentSecurity {Text = "0"}); + properties.AppendChild(new DocumentSecurity { Text = "0" }); if (properties.ScaleCrop == null) - properties.AppendChild(new ScaleCrop {Text = "false"}); + properties.AppendChild(new ScaleCrop { Text = "false" }); if (properties.HeadingPairs == null) properties.HeadingPairs = new HeadingPairs(); @@ -416,16 +420,16 @@ if (properties.TitlesOfParts == null) properties.TitlesOfParts = new TitlesOfParts(); - properties.HeadingPairs.VTVector = new VTVector {BaseType = VectorBaseValues.Variant}; + properties.HeadingPairs.VTVector = new VTVector { BaseType = VectorBaseValues.Variant }; - properties.TitlesOfParts.VTVector = new VTVector {BaseType = VectorBaseValues.Lpstr}; + properties.TitlesOfParts.VTVector = new VTVector { BaseType = VectorBaseValues.Lpstr }; var vTVectorOne = properties.HeadingPairs.VTVector; var vTVectorTwo = properties.TitlesOfParts.VTVector; var modifiedWorksheets = - ((IEnumerable)WorksheetsInternal).Select(w => new {w.Name, Order = w.Position}).ToList(); + ((IEnumerable)WorksheetsInternal).Select(w => new { w.Name, Order = w.Position }).ToList(); var modifiedNamedRanges = GetModifiedNamedRanges(); var modifiedWorksheetsCount = modifiedWorksheets.Count; var modifiedNamedRangesCount = modifiedNamedRanges.Count; @@ -436,10 +440,10 @@ vTVectorTwo.Size = (UInt32)(modifiedNamedRangesCount + modifiedWorksheetsCount); foreach ( - var vTlpstr3 in modifiedWorksheets.OrderBy(w => w.Order).Select(w => new VTLPSTR {Text = w.Name})) + var vTlpstr3 in modifiedWorksheets.OrderBy(w => w.Order).Select(w => new VTLPSTR { Text = w.Name })) vTVectorTwo.AppendChild(vTlpstr3); - foreach (var vTlpstr7 in modifiedNamedRanges.Select(nr => new VTLPSTR {Text = nr})) + foreach (var vTlpstr7 in modifiedNamedRanges.Select(nr => new VTLPSTR { Text = nr })) vTVectorTwo.AppendChild(vTlpstr7); if (Properties.Manager != null) @@ -471,8 +475,8 @@ private static void InsertOnVtVector(VTVector vTVector, String property, Int32 index, String text) { var m = from e1 in vTVector.Elements() - where e1.Elements().Any(e2 => e2.Text == property) - select e1; + where e1.Elements().Any(e2 => e2.Text == property) + select e1; if (!m.Any()) { if (vTVector.Size == null) @@ -480,7 +484,7 @@ vTVector.Size += 2U; var variant1 = new Variant(); - var vTlpstr1 = new VTLPSTR {Text = property}; + var vTlpstr1 = new VTLPSTR { Text = property }; variant1.AppendChild(vTlpstr1); vTVector.InsertAt(variant1, index); @@ -581,40 +585,45 @@ 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); - while (WorksheetsInternal.Cast().Any(w => w.SheetId == Int32.Parse(rId.Substring(3)))) - rId = context.RelIdGenerator.GetNext(RelType.Workbook); + while (WorksheetsInternal.Cast().Any(w => w.SheetId == Int32.Parse(rId.Substring(3)))) + rId = context.RelIdGenerator.GetNext(RelType.Workbook); - xlSheet.SheetId = Int32.Parse(rId.Substring(3)); - xlSheet.RelId = rId; + xlSheet.SheetId = Int32.Parse(rId.Substring(3)); + xlSheet.RelId = rId; } 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)) { - var newSheet = new Sheet - { - Name = xlSheet.Name, - Id = rId, - SheetId = (UInt32)xlSheet.SheetId - }; + var newSheet = new Sheet + { + Name = xlSheet.Name, + Id = rId, + SheetId = (UInt32)xlSheet.SheetId + }; - workbook.Sheets.AppendChild(newSheet); - } + workbook.Sheets.AppendChild(newSheet); + } } var sheetElements = from sheet in workbook.Sheets.Elements() - join worksheet in ((IEnumerable)WorksheetsInternal) on sheet.Id.Value - equals worksheet.RelId - orderby worksheet.Position - select sheet; + join worksheet in ((IEnumerable)WorksheetsInternal) on sheet.Id.Value + equals worksheet.RelId + orderby worksheet.Position + select sheet; UInt32 firstSheetVisible = 0; var activeTab = @@ -665,7 +674,7 @@ if (workbookView == null) { - workbookView = new WorkbookView {ActiveTab = activeTab, FirstSheet = firstSheetVisible}; + workbookView = new WorkbookView { ActiveTab = activeTab, FirstSheet = firstSheetVisible }; workbook.BookViews.AppendChild(workbookView); } else @@ -686,7 +695,7 @@ if (worksheet.PageSetup.PrintAreas.Any()) { - var definedName = new DefinedName {Name = "_xlnm.Print_Area", LocalSheetId = sheetId}; + var definedName = new DefinedName { Name = "_xlnm.Print_Area", LocalSheetId = sheetId }; var worksheetName = worksheet.Name; var definedNameText = worksheet.PageSetup.PrintAreas.Aggregate(String.Empty, (current, printArea) => @@ -796,7 +805,7 @@ workbook.DefinedNames = definedNames; if (workbook.CalculationProperties == null) - workbook.CalculationProperties = new CalculationProperties {CalculationId = 125725U}; + workbook.CalculationProperties = new CalculationProperties { CalculationId = 125725U }; if (CalculateMode == XLCalculateMode.Default) workbook.CalculationProperties.CalculationMode = null; @@ -821,7 +830,7 @@ var x = 0; Worksheets.ForEach(w => w.Tables.ForEach(t => x = (t as XLTable).FieldNames.Count)); - sharedStringTablePart.SharedStringTable = new SharedStringTable {Count = 0, UniqueCount = 0}; + sharedStringTablePart.SharedStringTable = new SharedStringTable { Count = 0, UniqueCount = 0 }; var stringId = 0; @@ -860,7 +869,7 @@ EndingBaseIndex = (UInt32)p.End }; - var text = new Text {Text = p.Text}; + var text = new Text { Text = p.Text }; if (p.Text.PreserveSpaces()) text.Space = SpaceProcessingModeValues.Preserve; @@ -869,7 +878,7 @@ } var f = new XLFont(null, c.RichText.Phonetics); if (!context.SharedFonts.ContainsKey(f)) - context.SharedFonts.Add(f, new FontInfo {Font = f}); + context.SharedFonts.Add(f, new FontInfo { Font = f }); var phoneticProperties = new PhoneticProperties { @@ -904,7 +913,7 @@ { var s = c.Value.ToString(); var sharedStringItem = new SharedStringItem(); - var text = new Text {Text = XmlEncoder.EncodeString(s)}; + var text = new Text { Text = XmlEncoder.EncodeString(s) }; if (!s.Trim().Equals(s)) text.Space = SpaceProcessingModeValues.Preserve; sharedStringItem.Append(text); @@ -930,16 +939,16 @@ var bold = rt.Bold ? new Bold() : null; var italic = rt.Italic ? new Italic() : null; var underline = rt.Underline != XLFontUnderlineValues.None - ? new Underline {Val = rt.Underline.ToOpenXml()} + ? new Underline { Val = rt.Underline.ToOpenXml() } : null; var strike = rt.Strikethrough ? new Strike() : null; var verticalAlignment = new VerticalTextAlignment - {Val = rt.VerticalAlignment.ToOpenXml()}; + { Val = rt.VerticalAlignment.ToOpenXml() }; var shadow = rt.Shadow ? new Shadow() : null; - var fontSize = new FontSize {Val = rt.FontSize}; + var fontSize = new FontSize { Val = rt.FontSize }; var color = GetNewColor(rt.FontColor); - var fontName = new RunFont {Val = rt.FontName}; - var fontFamilyNumbering = new FontFamily {Val = (Int32)rt.FontFamilyNumbering}; + var fontName = new RunFont { Val = rt.FontName }; + var fontFamilyNumbering = new FontFamily { Val = (Int32)rt.FontFamilyNumbering }; if (bold != null) runProperties.Append(bold); if (italic != null) runProperties.Append(italic); @@ -954,7 +963,7 @@ runProperties.Append(fontName); runProperties.Append(fontFamilyNumbering); - var text = new Text {Text = rt.Text}; + var text = new Text { Text = rt.Text }; if (rt.Text.PreserveSpaces()) text.Space = SpaceProcessingModeValues.Preserve; @@ -998,7 +1007,7 @@ { cc.Array = true; calculationChain.AppendChild(cc); - calculationChain.AppendChild(new CalculationCell {CellReference = c.Address.ToString(), InChildChain = true}); + calculationChain.AppendChild(new CalculationCell { CellReference = c.Address.ToString(), InChildChain = true }); } else { @@ -1018,13 +1027,13 @@ //var cCellsToRemove = new List(); var m = from cc in calculationChain.Elements() - where !(cc.SheetId != null || cc.InChildChain != null) - && calculationChain.Elements() - .Where(c1 => c1.SheetId != null) - .Select(c1 => c1.CellReference.Value) - .Contains(cc.CellReference.Value) - || cellsWithoutFormulas.Contains(cc.CellReference.Value) - select cc; + where !(cc.SheetId != null || cc.InChildChain != null) + && calculationChain.Elements() + .Where(c1 => c1.SheetId != null) + .Select(c1 => c1.CellReference.Value) + .Contains(cc.CellReference.Value) + || cellsWithoutFormulas.Contains(cc.CellReference.Value) + select cc; //m.ToList().ForEach(cc => cCellsToRemove.Add(cc)); m.ToList().ForEach(cc => calculationChain.RemoveChild(cc)); } @@ -1035,12 +1044,12 @@ private void GenerateThemePartContent(ThemePart themePart) { - var theme1 = new Theme {Name = "Office Theme"}; + var theme1 = new Theme { Name = "Office Theme" }; theme1.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main"); var themeElements1 = new ThemeElements(); - var colorScheme1 = new ColorScheme {Name = "Office"}; + var colorScheme1 = new ColorScheme { Name = "Office" }; var dark1Color1 = new Dark1Color(); var systemColor1 = new SystemColor @@ -1061,52 +1070,52 @@ light1Color1.AppendChild(systemColor2); var dark2Color1 = new Dark2Color(); - var rgbColorModelHex1 = new RgbColorModelHex {Val = Theme.Text2.Color.ToHex().Substring(2)}; + var rgbColorModelHex1 = new RgbColorModelHex { Val = Theme.Text2.Color.ToHex().Substring(2) }; dark2Color1.AppendChild(rgbColorModelHex1); var light2Color1 = new Light2Color(); - var rgbColorModelHex2 = new RgbColorModelHex {Val = Theme.Background2.Color.ToHex().Substring(2)}; + var rgbColorModelHex2 = new RgbColorModelHex { Val = Theme.Background2.Color.ToHex().Substring(2) }; light2Color1.AppendChild(rgbColorModelHex2); var accent1Color1 = new Accent1Color(); - var rgbColorModelHex3 = new RgbColorModelHex {Val = Theme.Accent1.Color.ToHex().Substring(2)}; + var rgbColorModelHex3 = new RgbColorModelHex { Val = Theme.Accent1.Color.ToHex().Substring(2) }; accent1Color1.AppendChild(rgbColorModelHex3); var accent2Color1 = new Accent2Color(); - var rgbColorModelHex4 = new RgbColorModelHex {Val = Theme.Accent2.Color.ToHex().Substring(2)}; + var rgbColorModelHex4 = new RgbColorModelHex { Val = Theme.Accent2.Color.ToHex().Substring(2) }; accent2Color1.AppendChild(rgbColorModelHex4); var accent3Color1 = new Accent3Color(); - var rgbColorModelHex5 = new RgbColorModelHex {Val = Theme.Accent3.Color.ToHex().Substring(2)}; + var rgbColorModelHex5 = new RgbColorModelHex { Val = Theme.Accent3.Color.ToHex().Substring(2) }; accent3Color1.AppendChild(rgbColorModelHex5); var accent4Color1 = new Accent4Color(); - var rgbColorModelHex6 = new RgbColorModelHex {Val = Theme.Accent4.Color.ToHex().Substring(2)}; + var rgbColorModelHex6 = new RgbColorModelHex { Val = Theme.Accent4.Color.ToHex().Substring(2) }; accent4Color1.AppendChild(rgbColorModelHex6); var accent5Color1 = new Accent5Color(); - var rgbColorModelHex7 = new RgbColorModelHex {Val = Theme.Accent5.Color.ToHex().Substring(2)}; + var rgbColorModelHex7 = new RgbColorModelHex { Val = Theme.Accent5.Color.ToHex().Substring(2) }; accent5Color1.AppendChild(rgbColorModelHex7); var accent6Color1 = new Accent6Color(); - var rgbColorModelHex8 = new RgbColorModelHex {Val = Theme.Accent6.Color.ToHex().Substring(2)}; + var rgbColorModelHex8 = new RgbColorModelHex { Val = Theme.Accent6.Color.ToHex().Substring(2) }; accent6Color1.AppendChild(rgbColorModelHex8); var hyperlink1 = new DocumentFormat.OpenXml.Drawing.Hyperlink(); - var rgbColorModelHex9 = new RgbColorModelHex {Val = Theme.Hyperlink.Color.ToHex().Substring(2)}; + var rgbColorModelHex9 = new RgbColorModelHex { Val = Theme.Hyperlink.Color.ToHex().Substring(2) }; hyperlink1.AppendChild(rgbColorModelHex9); var followedHyperlinkColor1 = new FollowedHyperlinkColor(); - var rgbColorModelHex10 = new RgbColorModelHex {Val = Theme.FollowedHyperlink.Color.ToHex().Substring(2)}; + var rgbColorModelHex10 = new RgbColorModelHex { Val = Theme.FollowedHyperlink.Color.ToHex().Substring(2) }; followedHyperlinkColor1.AppendChild(rgbColorModelHex10); @@ -1123,41 +1132,41 @@ colorScheme1.AppendChild(hyperlink1); colorScheme1.AppendChild(followedHyperlinkColor1); - var fontScheme2 = new FontScheme {Name = "Office"}; + var fontScheme2 = new FontScheme { Name = "Office" }; var majorFont1 = new MajorFont(); - var latinFont1 = new LatinFont {Typeface = "Cambria"}; - var eastAsianFont1 = new EastAsianFont {Typeface = ""}; - var complexScriptFont1 = new ComplexScriptFont {Typeface = ""}; - var supplementalFont1 = new SupplementalFont {Script = "Jpan", Typeface = "ＭＳ Ｐゴシック"}; - var supplementalFont2 = new SupplementalFont {Script = "Hang", Typeface = "맑은 고딕"}; - var supplementalFont3 = new SupplementalFont {Script = "Hans", Typeface = "宋体"}; - var supplementalFont4 = new SupplementalFont {Script = "Hant", Typeface = "新細明體"}; - var supplementalFont5 = new SupplementalFont {Script = "Arab", Typeface = "Times New Roman"}; - var supplementalFont6 = new SupplementalFont {Script = "Hebr", Typeface = "Times New Roman"}; - var supplementalFont7 = new SupplementalFont {Script = "Thai", Typeface = "Tahoma"}; - var supplementalFont8 = new SupplementalFont {Script = "Ethi", Typeface = "Nyala"}; - var supplementalFont9 = new SupplementalFont {Script = "Beng", Typeface = "Vrinda"}; - var supplementalFont10 = new SupplementalFont {Script = "Gujr", Typeface = "Shruti"}; - var supplementalFont11 = new SupplementalFont {Script = "Khmr", Typeface = "MoolBoran"}; - var supplementalFont12 = new SupplementalFont {Script = "Knda", Typeface = "Tunga"}; - var supplementalFont13 = new SupplementalFont {Script = "Guru", Typeface = "Raavi"}; - var supplementalFont14 = new SupplementalFont {Script = "Cans", Typeface = "Euphemia"}; - var supplementalFont15 = new SupplementalFont {Script = "Cher", Typeface = "Plantagenet Cherokee"}; - var supplementalFont16 = new SupplementalFont {Script = "Yiii", Typeface = "Microsoft Yi Baiti"}; - var supplementalFont17 = new SupplementalFont {Script = "Tibt", Typeface = "Microsoft Himalaya"}; - var supplementalFont18 = new SupplementalFont {Script = "Thaa", Typeface = "MV Boli"}; - var supplementalFont19 = new SupplementalFont {Script = "Deva", Typeface = "Mangal"}; - var supplementalFont20 = new SupplementalFont {Script = "Telu", Typeface = "Gautami"}; - var supplementalFont21 = new SupplementalFont {Script = "Taml", Typeface = "Latha"}; - var supplementalFont22 = new SupplementalFont {Script = "Syrc", Typeface = "Estrangelo Edessa"}; - var supplementalFont23 = new SupplementalFont {Script = "Orya", Typeface = "Kalinga"}; - var supplementalFont24 = new SupplementalFont {Script = "Mlym", Typeface = "Kartika"}; - var supplementalFont25 = new SupplementalFont {Script = "Laoo", Typeface = "DokChampa"}; - var supplementalFont26 = new SupplementalFont {Script = "Sinh", Typeface = "Iskoola Pota"}; - var supplementalFont27 = new SupplementalFont {Script = "Mong", Typeface = "Mongolian Baiti"}; - var supplementalFont28 = new SupplementalFont {Script = "Viet", Typeface = "Times New Roman"}; - var supplementalFont29 = new SupplementalFont {Script = "Uigh", Typeface = "Microsoft Uighur"}; + var latinFont1 = new LatinFont { Typeface = "Cambria" }; + var eastAsianFont1 = new EastAsianFont { Typeface = "" }; + var complexScriptFont1 = new ComplexScriptFont { Typeface = "" }; + var supplementalFont1 = new SupplementalFont { Script = "Jpan", Typeface = "ＭＳ Ｐゴシック" }; + var supplementalFont2 = new SupplementalFont { Script = "Hang", Typeface = "맑은 고딕" }; + var supplementalFont3 = new SupplementalFont { Script = "Hans", Typeface = "宋体" }; + var supplementalFont4 = new SupplementalFont { Script = "Hant", Typeface = "新細明體" }; + var supplementalFont5 = new SupplementalFont { Script = "Arab", Typeface = "Times New Roman" }; + var supplementalFont6 = new SupplementalFont { Script = "Hebr", Typeface = "Times New Roman" }; + var supplementalFont7 = new SupplementalFont { Script = "Thai", Typeface = "Tahoma" }; + var supplementalFont8 = new SupplementalFont { Script = "Ethi", Typeface = "Nyala" }; + var supplementalFont9 = new SupplementalFont { Script = "Beng", Typeface = "Vrinda" }; + var supplementalFont10 = new SupplementalFont { Script = "Gujr", Typeface = "Shruti" }; + var supplementalFont11 = new SupplementalFont { Script = "Khmr", Typeface = "MoolBoran" }; + var supplementalFont12 = new SupplementalFont { Script = "Knda", Typeface = "Tunga" }; + var supplementalFont13 = new SupplementalFont { Script = "Guru", Typeface = "Raavi" }; + var supplementalFont14 = new SupplementalFont { Script = "Cans", Typeface = "Euphemia" }; + var supplementalFont15 = new SupplementalFont { Script = "Cher", Typeface = "Plantagenet Cherokee" }; + var supplementalFont16 = new SupplementalFont { Script = "Yiii", Typeface = "Microsoft Yi Baiti" }; + var supplementalFont17 = new SupplementalFont { Script = "Tibt", Typeface = "Microsoft Himalaya" }; + var supplementalFont18 = new SupplementalFont { Script = "Thaa", Typeface = "MV Boli" }; + var supplementalFont19 = new SupplementalFont { Script = "Deva", Typeface = "Mangal" }; + var supplementalFont20 = new SupplementalFont { Script = "Telu", Typeface = "Gautami" }; + var supplementalFont21 = new SupplementalFont { Script = "Taml", Typeface = "Latha" }; + var supplementalFont22 = new SupplementalFont { Script = "Syrc", Typeface = "Estrangelo Edessa" }; + var supplementalFont23 = new SupplementalFont { Script = "Orya", Typeface = "Kalinga" }; + var supplementalFont24 = new SupplementalFont { Script = "Mlym", Typeface = "Kartika" }; + var supplementalFont25 = new SupplementalFont { Script = "Laoo", Typeface = "DokChampa" }; + var supplementalFont26 = new SupplementalFont { Script = "Sinh", Typeface = "Iskoola Pota" }; + var supplementalFont27 = new SupplementalFont { Script = "Mong", Typeface = "Mongolian Baiti" }; + var supplementalFont28 = new SupplementalFont { Script = "Viet", Typeface = "Times New Roman" }; + var supplementalFont29 = new SupplementalFont { Script = "Uigh", Typeface = "Microsoft Uighur" }; majorFont1.AppendChild(latinFont1); majorFont1.AppendChild(eastAsianFont1); @@ -1193,38 +1202,38 @@ majorFont1.AppendChild(supplementalFont29); var minorFont1 = new MinorFont(); - var latinFont2 = new LatinFont {Typeface = "Calibri"}; - var eastAsianFont2 = new EastAsianFont {Typeface = ""}; - var complexScriptFont2 = new ComplexScriptFont {Typeface = ""}; - var supplementalFont30 = new SupplementalFont {Script = "Jpan", Typeface = "ＭＳ Ｐゴシック"}; - var supplementalFont31 = new SupplementalFont {Script = "Hang", Typeface = "맑은 고딕"}; - var supplementalFont32 = new SupplementalFont {Script = "Hans", Typeface = "宋体"}; - var supplementalFont33 = new SupplementalFont {Script = "Hant", Typeface = "新細明體"}; - var supplementalFont34 = new SupplementalFont {Script = "Arab", Typeface = "Arial"}; - var supplementalFont35 = new SupplementalFont {Script = "Hebr", Typeface = "Arial"}; - var supplementalFont36 = new SupplementalFont {Script = "Thai", Typeface = "Tahoma"}; - var supplementalFont37 = new SupplementalFont {Script = "Ethi", Typeface = "Nyala"}; - var supplementalFont38 = new SupplementalFont {Script = "Beng", Typeface = "Vrinda"}; - var supplementalFont39 = new SupplementalFont {Script = "Gujr", Typeface = "Shruti"}; - var supplementalFont40 = new SupplementalFont {Script = "Khmr", Typeface = "DaunPenh"}; - var supplementalFont41 = new SupplementalFont {Script = "Knda", Typeface = "Tunga"}; - var supplementalFont42 = new SupplementalFont {Script = "Guru", Typeface = "Raavi"}; - var supplementalFont43 = new SupplementalFont {Script = "Cans", Typeface = "Euphemia"}; - var supplementalFont44 = new SupplementalFont {Script = "Cher", Typeface = "Plantagenet Cherokee"}; - var supplementalFont45 = new SupplementalFont {Script = "Yiii", Typeface = "Microsoft Yi Baiti"}; - var supplementalFont46 = new SupplementalFont {Script = "Tibt", Typeface = "Microsoft Himalaya"}; - var supplementalFont47 = new SupplementalFont {Script = "Thaa", Typeface = "MV Boli"}; - var supplementalFont48 = new SupplementalFont {Script = "Deva", Typeface = "Mangal"}; - var supplementalFont49 = new SupplementalFont {Script = "Telu", Typeface = "Gautami"}; - var supplementalFont50 = new SupplementalFont {Script = "Taml", Typeface = "Latha"}; - var supplementalFont51 = new SupplementalFont {Script = "Syrc", Typeface = "Estrangelo Edessa"}; - var supplementalFont52 = new SupplementalFont {Script = "Orya", Typeface = "Kalinga"}; - var supplementalFont53 = new SupplementalFont {Script = "Mlym", Typeface = "Kartika"}; - var supplementalFont54 = new SupplementalFont {Script = "Laoo", Typeface = "DokChampa"}; - var supplementalFont55 = new SupplementalFont {Script = "Sinh", Typeface = "Iskoola Pota"}; - var supplementalFont56 = new SupplementalFont {Script = "Mong", Typeface = "Mongolian Baiti"}; - var supplementalFont57 = new SupplementalFont {Script = "Viet", Typeface = "Arial"}; - var supplementalFont58 = new SupplementalFont {Script = "Uigh", Typeface = "Microsoft Uighur"}; + var latinFont2 = new LatinFont { Typeface = "Calibri" }; + var eastAsianFont2 = new EastAsianFont { Typeface = "" }; + var complexScriptFont2 = new ComplexScriptFont { Typeface = "" }; + var supplementalFont30 = new SupplementalFont { Script = "Jpan", Typeface = "ＭＳ Ｐゴシック" }; + var supplementalFont31 = new SupplementalFont { Script = "Hang", Typeface = "맑은 고딕" }; + var supplementalFont32 = new SupplementalFont { Script = "Hans", Typeface = "宋体" }; + var supplementalFont33 = new SupplementalFont { Script = "Hant", Typeface = "新細明體" }; + var supplementalFont34 = new SupplementalFont { Script = "Arab", Typeface = "Arial" }; + var supplementalFont35 = new SupplementalFont { Script = "Hebr", Typeface = "Arial" }; + var supplementalFont36 = new SupplementalFont { Script = "Thai", Typeface = "Tahoma" }; + var supplementalFont37 = new SupplementalFont { Script = "Ethi", Typeface = "Nyala" }; + var supplementalFont38 = new SupplementalFont { Script = "Beng", Typeface = "Vrinda" }; + var supplementalFont39 = new SupplementalFont { Script = "Gujr", Typeface = "Shruti" }; + var supplementalFont40 = new SupplementalFont { Script = "Khmr", Typeface = "DaunPenh" }; + var supplementalFont41 = new SupplementalFont { Script = "Knda", Typeface = "Tunga" }; + var supplementalFont42 = new SupplementalFont { Script = "Guru", Typeface = "Raavi" }; + var supplementalFont43 = new SupplementalFont { Script = "Cans", Typeface = "Euphemia" }; + var supplementalFont44 = new SupplementalFont { Script = "Cher", Typeface = "Plantagenet Cherokee" }; + var supplementalFont45 = new SupplementalFont { Script = "Yiii", Typeface = "Microsoft Yi Baiti" }; + var supplementalFont46 = new SupplementalFont { Script = "Tibt", Typeface = "Microsoft Himalaya" }; + var supplementalFont47 = new SupplementalFont { Script = "Thaa", Typeface = "MV Boli" }; + var supplementalFont48 = new SupplementalFont { Script = "Deva", Typeface = "Mangal" }; + var supplementalFont49 = new SupplementalFont { Script = "Telu", Typeface = "Gautami" }; + var supplementalFont50 = new SupplementalFont { Script = "Taml", Typeface = "Latha" }; + var supplementalFont51 = new SupplementalFont { Script = "Syrc", Typeface = "Estrangelo Edessa" }; + var supplementalFont52 = new SupplementalFont { Script = "Orya", Typeface = "Kalinga" }; + var supplementalFont53 = new SupplementalFont { Script = "Mlym", Typeface = "Kartika" }; + var supplementalFont54 = new SupplementalFont { Script = "Laoo", Typeface = "DokChampa" }; + var supplementalFont55 = new SupplementalFont { Script = "Sinh", Typeface = "Iskoola Pota" }; + var supplementalFont56 = new SupplementalFont { Script = "Mong", Typeface = "Mongolian Baiti" }; + var supplementalFont57 = new SupplementalFont { Script = "Viet", Typeface = "Arial" }; + var supplementalFont58 = new SupplementalFont { Script = "Uigh", Typeface = "Microsoft Uighur" }; minorFont1.AppendChild(latinFont2); minorFont1.AppendChild(eastAsianFont2); @@ -1262,46 +1271,46 @@ fontScheme2.AppendChild(majorFont1); fontScheme2.AppendChild(minorFont1); - var formatScheme1 = new FormatScheme {Name = "Office"}; + var formatScheme1 = new FormatScheme { Name = "Office" }; var fillStyleList1 = new FillStyleList(); var solidFill1 = new SolidFill(); - var schemeColor1 = new SchemeColor {Val = SchemeColorValues.PhColor}; + var schemeColor1 = new SchemeColor { Val = SchemeColorValues.PhColor }; solidFill1.AppendChild(schemeColor1); - var gradientFill1 = new GradientFill {RotateWithShape = true}; + var gradientFill1 = new GradientFill { RotateWithShape = true }; var gradientStopList1 = new GradientStopList(); - var gradientStop1 = new GradientStop {Position = 0}; + var gradientStop1 = new GradientStop { Position = 0 }; - var schemeColor2 = new SchemeColor {Val = SchemeColorValues.PhColor}; - var tint1 = new Tint {Val = 50000}; - var saturationModulation1 = new SaturationModulation {Val = 300000}; + var schemeColor2 = new SchemeColor { Val = SchemeColorValues.PhColor }; + var tint1 = new Tint { Val = 50000 }; + var saturationModulation1 = new SaturationModulation { Val = 300000 }; schemeColor2.AppendChild(tint1); schemeColor2.AppendChild(saturationModulation1); gradientStop1.AppendChild(schemeColor2); - var gradientStop2 = new GradientStop {Position = 35000}; + var gradientStop2 = new GradientStop { Position = 35000 }; - var schemeColor3 = new SchemeColor {Val = SchemeColorValues.PhColor}; - var tint2 = new Tint {Val = 37000}; - var saturationModulation2 = new SaturationModulation {Val = 300000}; + var schemeColor3 = new SchemeColor { Val = SchemeColorValues.PhColor }; + var tint2 = new Tint { Val = 37000 }; + var saturationModulation2 = new SaturationModulation { Val = 300000 }; schemeColor3.AppendChild(tint2); schemeColor3.AppendChild(saturationModulation2); gradientStop2.AppendChild(schemeColor3); - var gradientStop3 = new GradientStop {Position = 100000}; + var gradientStop3 = new GradientStop { Position = 100000 }; - var schemeColor4 = new SchemeColor {Val = SchemeColorValues.PhColor}; - var tint3 = new Tint {Val = 15000}; - var saturationModulation3 = new SaturationModulation {Val = 350000}; + var schemeColor4 = new SchemeColor { Val = SchemeColorValues.PhColor }; + var tint3 = new Tint { Val = 15000 }; + var saturationModulation3 = new SaturationModulation { Val = 350000 }; schemeColor4.AppendChild(tint3); schemeColor4.AppendChild(saturationModulation3); @@ -1311,42 +1320,42 @@ gradientStopList1.AppendChild(gradientStop1); gradientStopList1.AppendChild(gradientStop2); gradientStopList1.AppendChild(gradientStop3); - var linearGradientFill1 = new LinearGradientFill {Angle = 16200000, Scaled = true}; + var linearGradientFill1 = new LinearGradientFill { Angle = 16200000, Scaled = true }; gradientFill1.AppendChild(gradientStopList1); gradientFill1.AppendChild(linearGradientFill1); - var gradientFill2 = new GradientFill {RotateWithShape = true}; + var gradientFill2 = new GradientFill { RotateWithShape = true }; var gradientStopList2 = new GradientStopList(); - var gradientStop4 = new GradientStop {Position = 0}; + var gradientStop4 = new GradientStop { Position = 0 }; - var schemeColor5 = new SchemeColor {Val = SchemeColorValues.PhColor}; - var shade1 = new Shade {Val = 51000}; - var saturationModulation4 = new SaturationModulation {Val = 130000}; + var schemeColor5 = new SchemeColor { Val = SchemeColorValues.PhColor }; + var shade1 = new Shade { Val = 51000 }; + var saturationModulation4 = new SaturationModulation { Val = 130000 }; schemeColor5.AppendChild(shade1); schemeColor5.AppendChild(saturationModulation4); gradientStop4.AppendChild(schemeColor5); - var gradientStop5 = new GradientStop {Position = 80000}; + var gradientStop5 = new GradientStop { Position = 80000 }; - var schemeColor6 = new SchemeColor {Val = SchemeColorValues.PhColor}; - var shade2 = new Shade {Val = 93000}; - var saturationModulation5 = new SaturationModulation {Val = 130000}; + var schemeColor6 = new SchemeColor { Val = SchemeColorValues.PhColor }; + var shade2 = new Shade { Val = 93000 }; + var saturationModulation5 = new SaturationModulation { Val = 130000 }; schemeColor6.AppendChild(shade2); schemeColor6.AppendChild(saturationModulation5); gradientStop5.AppendChild(schemeColor6); - var gradientStop6 = new GradientStop {Position = 100000}; + var gradientStop6 = new GradientStop { Position = 100000 }; - var schemeColor7 = new SchemeColor {Val = SchemeColorValues.PhColor}; - var shade3 = new Shade {Val = 94000}; - var saturationModulation6 = new SaturationModulation {Val = 135000}; + var schemeColor7 = new SchemeColor { Val = SchemeColorValues.PhColor }; + var shade3 = new Shade { Val = 94000 }; + var saturationModulation6 = new SaturationModulation { Val = 135000 }; schemeColor7.AppendChild(shade3); schemeColor7.AppendChild(saturationModulation6); @@ -1356,7 +1365,7 @@ gradientStopList2.AppendChild(gradientStop4); gradientStopList2.AppendChild(gradientStop5); gradientStopList2.AppendChild(gradientStop6); - var linearGradientFill2 = new LinearGradientFill {Angle = 16200000, Scaled = false}; + var linearGradientFill2 = new LinearGradientFill { Angle = 16200000, Scaled = false }; gradientFill2.AppendChild(gradientStopList2); gradientFill2.AppendChild(linearGradientFill2); @@ -1377,15 +1386,15 @@ var solidFill2 = new SolidFill(); - var schemeColor8 = new SchemeColor {Val = SchemeColorValues.PhColor}; - var shade4 = new Shade {Val = 95000}; - var saturationModulation7 = new SaturationModulation {Val = 105000}; + var schemeColor8 = new SchemeColor { Val = SchemeColorValues.PhColor }; + var shade4 = new Shade { Val = 95000 }; + var saturationModulation7 = new SaturationModulation { Val = 105000 }; schemeColor8.AppendChild(shade4); schemeColor8.AppendChild(saturationModulation7); solidFill2.AppendChild(schemeColor8); - var presetDash1 = new PresetDash {Val = PresetLineDashValues.Solid}; + var presetDash1 = new PresetDash { Val = PresetLineDashValues.Solid }; outline1.AppendChild(solidFill2); outline1.AppendChild(presetDash1); @@ -1399,10 +1408,10 @@ }; var solidFill3 = new SolidFill(); - var schemeColor9 = new SchemeColor {Val = SchemeColorValues.PhColor}; + var schemeColor9 = new SchemeColor { Val = SchemeColorValues.PhColor }; solidFill3.AppendChild(schemeColor9); - var presetDash2 = new PresetDash {Val = PresetLineDashValues.Solid}; + var presetDash2 = new PresetDash { Val = PresetLineDashValues.Solid }; outline2.AppendChild(solidFill3); outline2.AppendChild(presetDash2); @@ -1416,10 +1425,10 @@ }; var solidFill4 = new SolidFill(); - var schemeColor10 = new SchemeColor {Val = SchemeColorValues.PhColor}; + var schemeColor10 = new SchemeColor { Val = SchemeColorValues.PhColor }; solidFill4.AppendChild(schemeColor10); - var presetDash3 = new PresetDash {Val = PresetLineDashValues.Solid}; + var presetDash3 = new PresetDash { Val = PresetLineDashValues.Solid }; outline3.AppendChild(solidFill4); outline3.AppendChild(presetDash3); @@ -1442,8 +1451,8 @@ RotateWithShape = false }; - var rgbColorModelHex11 = new RgbColorModelHex {Val = "000000"}; - var alpha1 = new Alpha {Val = 38000}; + var rgbColorModelHex11 = new RgbColorModelHex { Val = "000000" }; + var alpha1 = new Alpha { Val = 38000 }; rgbColorModelHex11.AppendChild(alpha1); @@ -1465,8 +1474,8 @@ RotateWithShape = false }; - var rgbColorModelHex12 = new RgbColorModelHex {Val = "000000"}; - var alpha2 = new Alpha {Val = 35000}; + var rgbColorModelHex12 = new RgbColorModelHex { Val = "000000" }; + var alpha2 = new Alpha { Val = 35000 }; rgbColorModelHex12.AppendChild(alpha2); @@ -1488,8 +1497,8 @@ RotateWithShape = false }; - var rgbColorModelHex13 = new RgbColorModelHex {Val = "000000"}; - var alpha3 = new Alpha {Val = 35000}; + var rgbColorModelHex13 = new RgbColorModelHex { Val = "000000" }; + var alpha3 = new Alpha { Val = 35000 }; rgbColorModelHex13.AppendChild(alpha3); @@ -1499,13 +1508,13 @@ var scene3DType1 = new Scene3DType(); - var camera1 = new Camera {Preset = PresetCameraValues.OrthographicFront}; - var rotation1 = new Rotation {Latitude = 0, Longitude = 0, Revolution = 0}; + var camera1 = new Camera { Preset = PresetCameraValues.OrthographicFront }; + var rotation1 = new Rotation { Latitude = 0, Longitude = 0, Revolution = 0 }; camera1.AppendChild(rotation1); - var lightRig1 = new LightRig {Rig = LightRigValues.ThreePoints, Direction = LightRigDirectionValues.Top}; - var rotation2 = new Rotation {Latitude = 0, Longitude = 0, Revolution = 1200000}; + var lightRig1 = new LightRig { Rig = LightRigValues.ThreePoints, Direction = LightRigDirectionValues.Top }; + var rotation2 = new Rotation { Latitude = 0, Longitude = 0, Revolution = 1200000 }; lightRig1.AppendChild(rotation2); @@ -1513,7 +1522,7 @@ scene3DType1.AppendChild(lightRig1); var shape3DType1 = new Shape3DType(); - var bevelTop1 = new BevelTop {Width = 63500L, Height = 25400L}; + var bevelTop1 = new BevelTop { Width = 63500L, Height = 25400L }; shape3DType1.AppendChild(bevelTop1); @@ -1528,31 +1537,31 @@ var backgroundFillStyleList1 = new BackgroundFillStyleList(); var solidFill5 = new SolidFill(); - var schemeColor11 = new SchemeColor {Val = SchemeColorValues.PhColor}; + var schemeColor11 = new SchemeColor { Val = SchemeColorValues.PhColor }; solidFill5.AppendChild(schemeColor11); - var gradientFill3 = new GradientFill {RotateWithShape = true}; + var gradientFill3 = new GradientFill { RotateWithShape = true }; var gradientStopList3 = new GradientStopList(); - var gradientStop7 = new GradientStop {Position = 0}; + var gradientStop7 = new GradientStop { Position = 0 }; - var schemeColor12 = new SchemeColor {Val = SchemeColorValues.PhColor}; - var tint4 = new Tint {Val = 40000}; - var saturationModulation8 = new SaturationModulation {Val = 350000}; + var schemeColor12 = new SchemeColor { Val = SchemeColorValues.PhColor }; + var tint4 = new Tint { Val = 40000 }; + var saturationModulation8 = new SaturationModulation { Val = 350000 }; schemeColor12.AppendChild(tint4); schemeColor12.AppendChild(saturationModulation8); gradientStop7.AppendChild(schemeColor12); - var gradientStop8 = new GradientStop {Position = 40000}; + var gradientStop8 = new GradientStop { Position = 40000 }; - var schemeColor13 = new SchemeColor {Val = SchemeColorValues.PhColor}; - var tint5 = new Tint {Val = 45000}; - var shade5 = new Shade {Val = 99000}; - var saturationModulation9 = new SaturationModulation {Val = 350000}; + var schemeColor13 = new SchemeColor { Val = SchemeColorValues.PhColor }; + var tint5 = new Tint { Val = 45000 }; + var shade5 = new Shade { Val = 99000 }; + var saturationModulation9 = new SaturationModulation { Val = 350000 }; schemeColor13.AppendChild(tint5); schemeColor13.AppendChild(shade5); @@ -1560,11 +1569,11 @@ gradientStop8.AppendChild(schemeColor13); - var gradientStop9 = new GradientStop {Position = 100000}; + var gradientStop9 = new GradientStop { Position = 100000 }; - var schemeColor14 = new SchemeColor {Val = SchemeColorValues.PhColor}; - var shade6 = new Shade {Val = 20000}; - var saturationModulation10 = new SaturationModulation {Val = 255000}; + var schemeColor14 = new SchemeColor { Val = SchemeColorValues.PhColor }; + var shade6 = new Shade { Val = 20000 }; + var saturationModulation10 = new SaturationModulation { Val = 255000 }; schemeColor14.AppendChild(shade6); schemeColor14.AppendChild(saturationModulation10); @@ -1575,34 +1584,34 @@ gradientStopList3.AppendChild(gradientStop8); gradientStopList3.AppendChild(gradientStop9); - var pathGradientFill1 = new PathGradientFill {Path = PathShadeValues.Circle}; - var fillToRectangle1 = new FillToRectangle {Left = 50000, Top = -80000, Right = 50000, Bottom = 180000}; + var pathGradientFill1 = new PathGradientFill { Path = PathShadeValues.Circle }; + var fillToRectangle1 = new FillToRectangle { Left = 50000, Top = -80000, Right = 50000, Bottom = 180000 }; pathGradientFill1.AppendChild(fillToRectangle1); gradientFill3.AppendChild(gradientStopList3); gradientFill3.AppendChild(pathGradientFill1); - var gradientFill4 = new GradientFill {RotateWithShape = true}; + var gradientFill4 = new GradientFill { RotateWithShape = true }; var gradientStopList4 = new GradientStopList(); - var gradientStop10 = new GradientStop {Position = 0}; + var gradientStop10 = new GradientStop { Position = 0 }; - var schemeColor15 = new SchemeColor {Val = SchemeColorValues.PhColor}; - var tint6 = new Tint {Val = 80000}; - var saturationModulation11 = new SaturationModulation {Val = 300000}; + var schemeColor15 = new SchemeColor { Val = SchemeColorValues.PhColor }; + var tint6 = new Tint { Val = 80000 }; + var saturationModulation11 = new SaturationModulation { Val = 300000 }; schemeColor15.AppendChild(tint6); schemeColor15.AppendChild(saturationModulation11); gradientStop10.AppendChild(schemeColor15); - var gradientStop11 = new GradientStop {Position = 100000}; + var gradientStop11 = new GradientStop { Position = 100000 }; - var schemeColor16 = new SchemeColor {Val = SchemeColorValues.PhColor}; - var shade7 = new Shade {Val = 30000}; - var saturationModulation12 = new SaturationModulation {Val = 200000}; + var schemeColor16 = new SchemeColor { Val = SchemeColorValues.PhColor }; + var shade7 = new Shade { Val = 30000 }; + var saturationModulation12 = new SaturationModulation { Val = 200000 }; schemeColor16.AppendChild(shade7); schemeColor16.AppendChild(saturationModulation12); @@ -1612,8 +1621,8 @@ gradientStopList4.AppendChild(gradientStop10); gradientStopList4.AppendChild(gradientStop11); - var pathGradientFill2 = new PathGradientFill {Path = PathShadeValues.Circle}; - var fillToRectangle2 = new FillToRectangle {Left = 50000, Top = 50000, Right = 50000, Bottom = 50000}; + var pathGradientFill2 = new PathGradientFill { Path = PathShadeValues.Circle }; + var fillToRectangle2 = new FillToRectangle { Left = 50000, Top = 50000, Right = 50000, Bottom = 50000 }; pathGradientFill2.AppendChild(fillToRectangle2); @@ -1659,7 +1668,7 @@ }; if (p.Type == XLCustomPropertyType.Text) { - var vTlpwstr1 = new VTLPWSTR {Text = p.GetValue()}; + var vTlpwstr1 = new VTLPWSTR { Text = p.GetValue() }; customDocumentProperty.AppendChild(vTlpwstr1); } else if (p.Type == XLCustomPropertyType.Date) @@ -1682,7 +1691,7 @@ } else { - var vTBool1 = new VTBool {Text = p.GetValue().ToString().ToLower()}; + var vTBool1 = new VTBool { Text = p.GetValue().ToString().ToLower() }; customDocumentProperty.AppendChild(vTBool1); } properties2.AppendChild(customDocumentProperty); @@ -1749,7 +1758,7 @@ else table.TotalsRowShown = false; - var tableColumns1 = new TableColumns {Count = (UInt32)xlTable.ColumnCount()}; + var tableColumns1 = new TableColumns { Count = (UInt32)xlTable.ColumnCount() }; UInt32 columnId = 0; foreach (var xlField in xlTable.Fields) @@ -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; @@ -1826,23 +1835,40 @@ cacheId = pivotCaches.Cast().Max(pc => pc.CacheId.Value) + 1; } - foreach (var pt in xlWorksheet.PivotTables) + foreach (var pt in xlWorksheet.PivotTables.Cast()) { // TODO: Avoid duplicate pivot caches of same source range - var ptCdp = context.RelIdGenerator.GetNext(RelType.Workbook); - var pivotTableCacheDefinitionPart = workbookPart.AddNewPart(ptCdp); + var workbookCacheRelId = pt.WorkbookCacheRelId; + PivotCache pivotCache; + PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart; + if (!XLHelper.IsNullOrWhiteSpace(pt.WorkbookCacheRelId)) + { + pivotCache = pivotCaches.Cast().Single(pc => pc.Id.Value == pt.WorkbookCacheRelId); + pivotTableCacheDefinitionPart = workbookPart.GetPartById(pt.WorkbookCacheRelId) as PivotTableCacheDefinitionPart; + } + else + { + workbookCacheRelId = context.RelIdGenerator.GetNext(RelType.Workbook); + pivotCache = new PivotCache { CacheId = cacheId++, Id = workbookCacheRelId }; + pivotTableCacheDefinitionPart = workbookPart.AddNewPart(workbookCacheRelId); + } + GeneratePivotTableCacheDefinitionPartContent(pivotTableCacheDefinitionPart, pt); - var pivotCache = new PivotCache { CacheId = cacheId++, Id = ptCdp }; + if (XLHelper.IsNullOrWhiteSpace(pt.WorkbookCacheRelId)) + pivotCaches.AppendChild(pivotCache); - pivotCaches.AppendChild(pivotCache); + PivotTablePart pivotTablePart; + if (XLHelper.IsNullOrWhiteSpace(pt.RelId)) + pivotTablePart = worksheetPart.AddNewPart(context.RelIdGenerator.GetNext(RelType.Workbook)); + else + pivotTablePart = worksheetPart.GetPartById(pt.RelId) as PivotTablePart; - var pivotTablePart = - worksheetPart.AddNewPart(context.RelIdGenerator.GetNext(RelType.Workbook)); GeneratePivotTablePartContent(pivotTablePart, pt, pivotCache.CacheId, context); - pivotTablePart.AddPart(pivotTableCacheDefinitionPart, context.RelIdGenerator.GetNext(RelType.Workbook)); + if (XLHelper.IsNullOrWhiteSpace(pt.RelId)) + pivotTablePart.AddPart(pivotTableCacheDefinitionPart, context.RelIdGenerator.GetNext(RelType.Workbook)); } } @@ -1866,8 +1892,8 @@ pivotCacheDefinition.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); - var cacheSource = new CacheSource {Type = SourceValues.Worksheet}; - cacheSource.AppendChild(new WorksheetSource {Name = source.ToString()}); + var cacheSource = new CacheSource { Type = SourceValues.Worksheet }; + cacheSource.AppendChild(new WorksheetSource { Name = source.ToString() }); var cacheFields = new CacheFields(); @@ -1895,27 +1921,25 @@ if (onlyNumbers) { sharedItems = new SharedItems - {ContainsSemiMixedTypes = false, ContainsString = false, ContainsNumber = true}; + { ContainsSemiMixedTypes = false, ContainsString = false, ContainsNumber = true }; } 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); } foreach (var li in xlpf.SharedStrings) { - sharedItems.AppendChild(new StringItem {Val = li}); + sharedItems.AppendChild(new StringItem { Val = li }); } } - var cacheField = new CacheField {Name = xlpf.SourceName}; + var cacheField = new CacheField { Name = xlpf.SourceName }; cacheField.AppendChild(sharedItems); cacheFields.AppendChild(cacheField); } @@ -1925,7 +1949,9 @@ pivotTableCacheDefinitionPart.PivotCacheDefinition = pivotCacheDefinition; - var pivotTableCacheRecordsPart = pivotTableCacheDefinitionPart.AddNewPart("rId1"); + var pivotTableCacheRecordsPart = pivotTableCacheDefinitionPart.GetPartsOfType().Any() ? + pivotTableCacheDefinitionPart.GetPartsOfType().First() : + pivotTableCacheDefinitionPart.AddNewPart("rId1"); var pivotCacheRecords = new PivotCacheRecords(); pivotCacheRecords.AddNamespaceDeclaration("r", @@ -2001,40 +2027,40 @@ var columnFields = new ColumnFields(); var rowItems = new RowItems(); var columnItems = new ColumnItems(); - var pageFields = new PageFields {Count = (uint)pt.ReportFilters.Count()}; - var pivotFields = new PivotFields {Count = Convert.ToUInt32(pt.SourceRange.ColumnCount())}; + var pageFields = new PageFields { Count = (uint)pt.ReportFilters.Count() }; + var pivotFields = new PivotFields { Count = Convert.ToUInt32(pt.SourceRange.ColumnCount()) }; - foreach (var xlpf in pt.Fields.OrderBy(f => pt.RowLabels.Any(p => p.SourceName == f.SourceName) ? pt.RowLabels.IndexOf(f) : Int32.MaxValue )) + foreach (var xlpf in pt.Fields.OrderBy(f => pt.RowLabels.Any(p => p.SourceName == f.SourceName) ? pt.RowLabels.IndexOf(f) : Int32.MaxValue)) { if (pt.RowLabels.Any(p => p.SourceName == xlpf.SourceName)) { - var f = new Field {Index = pt.Fields.IndexOf(xlpf)}; + var f = new Field { Index = pt.Fields.IndexOf(xlpf) }; rowFields.AppendChild(f); for (var i = 0; i < xlpf.SharedStrings.Count; i++) { var rowItem = new RowItem(); - rowItem.AppendChild(new MemberPropertyIndex {Val = i}); + rowItem.AppendChild(new MemberPropertyIndex { Val = i }); rowItems.AppendChild(rowItem); } - var rowItemTotal = new RowItem {ItemType = ItemValues.Grand}; + var rowItemTotal = new RowItem { ItemType = ItemValues.Grand }; rowItemTotal.AppendChild(new MemberPropertyIndex()); rowItems.AppendChild(rowItemTotal); } else if (pt.ColumnLabels.Any(p => p.SourceName == xlpf.SourceName)) { - var f = new Field {Index = pt.Fields.IndexOf(xlpf)}; + var f = new Field { Index = pt.Fields.IndexOf(xlpf) }; columnFields.AppendChild(f); for (var i = 0; i < xlpf.SharedStrings.Count; i++) { var rowItem = new RowItem(); - rowItem.AppendChild(new MemberPropertyIndex {Val = i}); + rowItem.AppendChild(new MemberPropertyIndex { Val = i }); columnItems.AppendChild(rowItem); } - var rowItemTotal = new RowItem {ItemType = ItemValues.Grand}; + var rowItemTotal = new RowItem { ItemType = ItemValues.Grand }; rowItemTotal.AppendChild(new MemberPropertyIndex()); columnItems.AppendChild(rowItemTotal); } @@ -2054,7 +2080,7 @@ foreach (var xlpf in pt.Fields) { - var pf = new PivotField {ShowAll = false, Name = xlpf.CustomName}; + var pf = new PivotField { ShowAll = false, Name = xlpf.CustomName }; if (pt.RowLabels.Any(p => p.SourceName == xlpf.SourceName)) { @@ -2069,7 +2095,7 @@ location.ColumnsPerPage = 1; location.RowPageCount = 1; pf.Axis = PivotTableAxisValues.AxisPage; - pageFields.AppendChild(new PageField {Hierarchy = -1, Field = pt.Fields.IndexOf(xlpf)}); + pageFields.AppendChild(new PageField { Hierarchy = -1, Field = pt.Fields.IndexOf(xlpf) }); } else if (pt.Values.Any(p => p.SourceName == xlpf.SourceName)) { @@ -2082,7 +2108,7 @@ { for (uint i = 0; i < xlpf.SharedStrings.Count; i++) { - fieldItems.AppendChild(new Item {Index = i}); + fieldItems.AppendChild(new Item { Index = i }); } } @@ -2143,7 +2169,7 @@ } else { - fieldItems.AppendChild(new Item {ItemType = ItemValues.Default}); + fieldItems.AppendChild(new Item { ItemType = ItemValues.Default }); } fieldItems.Count = Convert.ToUInt32(fieldItems.Count()); @@ -2175,7 +2201,7 @@ rowItem.Index = Convert.ToUInt32(i); rowItem.AppendChild(new MemberPropertyIndex() { Val = i }); columnItems.AppendChild(rowItem); - } + } } if (columnFields.Any()) @@ -2233,7 +2259,7 @@ if (items.Any(i => i.Equals(value.BaseItem))) df.BaseItem = Convert.ToUInt32(items.IndexOf(value.BaseItem)); - } + } } else { @@ -2255,7 +2281,7 @@ pivotTableDefinition.AppendChild(new PivotTableStyle { - Name = Enum.GetName(typeof (XLPivotTableTheme), pt.Theme), + Name = Enum.GetName(typeof(XLPivotTableTheme), pt.Theme), ShowRowHeaders = pt.ShowRowHeaders, ShowColumnHeaders = pt.ShowColumnHeaders, ShowRowStripes = pt.ShowRowStripes, @@ -2267,12 +2293,12 @@ var pivotTableDefinitionExtensionList = new PivotTableDefinitionExtensionList(); var pivotTableDefinitionExtension = new PivotTableDefinitionExtension - {Uri = "{962EF5D1-5CA2-4c93-8EF4-DBF5C05439D2}"}; + { Uri = "{962EF5D1-5CA2-4c93-8EF4-DBF5C05439D2}" }; pivotTableDefinitionExtension.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"); var pivotTableDefinition2 = new DocumentFormat.OpenXml.Office2010.Excel.PivotTableDefinition - {EnableEdit = pt.EnableCellEditing, HideValuesRow = !pt.ShowValuesRow}; + { EnableEdit = pt.EnableCellEditing, HideValuesRow = !pt.ShowValuesRow }; pivotTableDefinition2.AddNamespaceDeclaration("xm", "http://schemas.microsoft.com/office/excel/2006/main"); pivotTableDefinitionExtension.AppendChild(pivotTableDefinition2); @@ -2294,7 +2320,7 @@ var authorsDict = new Dictionary(); foreach (var c in xlWorksheet.Internals.CellsCollection.GetCells(c => c.HasComment)) { - var comment = new Comment {Reference = c.Address.ToStringRelative()}; + var comment = new Comment { Reference = c.Address.ToStringRelative() }; var authorName = c.Comment.Author; Int32 authorId; @@ -2316,7 +2342,7 @@ } var authors = new Authors(); - foreach (var author in authorsDict.Select(a => new Author {Text = a.Key})) + foreach (var author in authorsDict.Select(a => new Author { Text = a.Key })) { authors.Append(author); } @@ -2340,8 +2366,8 @@ const string shapeTypeId = "_x0000_t202"; // arbitrary, assigned by office new Vml.Shapetype( - new Vml.Stroke {JoinStyle = Vml.StrokeJoinStyleValues.Miter}, - new Vml.Path {AllowGradientShape = true, ConnectionPointType = ConnectValues.Rectangle} + new Vml.Stroke { JoinStyle = Vml.StrokeJoinStyleValues.Miter }, + new Vml.Path { AllowGradientShape = true, ConnectionPointType = ConnectValues.Rectangle } ) { Id = shapeTypeId, @@ -2381,7 +2407,7 @@ // Unique per cell (workbook?), e.g.: "_x0000_s1026" var anchor = GetAnchor(c); var textBox = GetTextBox(c.Comment.Style); - var fill = new Vml.Fill {Color2 = "#" + c.Comment.Style.ColorsAndLines.FillColor.Color.ToHex().Substring(2)}; + var fill = new Vml.Fill { Color2 = "#" + c.Comment.Style.ColorsAndLines.FillColor.Color.ToHex().Substring(2) }; if (c.Comment.Style.ColorsAndLines.FillTransparency < 1) fill.Opacity = Math.Round(Convert.ToDouble(c.Comment.Style.ColorsAndLines.FillTransparency), 2).ToString( @@ -2390,8 +2416,8 @@ var shape = new Vml.Shape( fill, stroke, - new Vml.Shadow {On = true, Color = "black", Obscured = true}, - new Vml.Path {ConnectionPointType = ConnectValues.None}, + new Vml.Shadow { On = true, Color = "black", Obscured = true }, + new Vml.Path { ConnectionPointType = ConnectValues.None }, textBox, new ClientData( new MoveWithCells(c.Comment.Style.Properties.Positioning == XLDrawingAnchor.Absolute @@ -2404,12 +2430,13 @@ new HorizontalTextAlignment(c.Comment.Style.Alignment.Horizontal.ToString().ToCamel()), new Vml.Spreadsheet.VerticalTextAlignment(c.Comment.Style.Alignment.Vertical.ToString().ToCamel()), new AutoFill("False"), - new CommentRowTarget {Text = (rowNumber - 1).ToString()}, - new CommentColumnTarget {Text = (columnNumber - 1).ToString()}, + new CommentRowTarget { Text = (rowNumber - 1).ToString() }, + new CommentColumnTarget { Text = (columnNumber - 1).ToString() }, new Locked(c.Comment.Style.Protection.Locked ? "True" : "False"), new LockText(c.Comment.Style.Protection.LockText ? "True" : "False"), new Visible(c.Comment.Visible ? "True" : "False") - ) {ObjectType = ObjectValues.Note} + ) + { ObjectType = ObjectValues.Note } ) { Id = shapeId, @@ -2467,7 +2494,7 @@ } if (a.AutomaticSize) sb.Append("mso-fit-shape-to-text:t;"); - var retVal = new Vml.TextBox {Style = sb.ToString()}; + var retVal = new Vml.TextBox { Style = sb.ToString() }; var dm = ds.Margins; if (!dm.Automatic) retVal.Inset = String.Format("{0}in,{1}in,{2}in,{3}in", @@ -2550,7 +2577,7 @@ var defaultStyle = new XLStyle(null, DefaultStyle); var defaultStyleId = GetStyleId(defaultStyle); if (!context.SharedFonts.ContainsKey(defaultStyle.Font)) - context.SharedFonts.Add(defaultStyle.Font, new FontInfo {FontId = 0, Font = defaultStyle.Font as XLFont}); + context.SharedFonts.Add(defaultStyle.Font, new FontInfo { FontId = 0, Font = defaultStyle.Font as XLFont }); var sharedFills = new Dictionary {{defaultStyle.Fill, new FillInfo {FillId = 2, Fill = defaultStyle.Fill as XLFill}}}; @@ -2577,17 +2604,12 @@ if (workbookStylesPart.Stylesheet.CellStyles == null) workbookStylesPart.Stylesheet.CellStyles = new CellStyles(); + // To determine the default workbook style, we look for the style with builtInId = 0 (I hope that is the correct approach) UInt32 defaultFormatId; - if (workbookStylesPart.Stylesheet.CellStyles.Elements().Any(c => c.Name == "Normal")) - { - defaultFormatId = - workbookStylesPart.Stylesheet.CellStyles.Elements().Single(c => c.Name == "Normal").FormatId.Value; - } + if (workbookStylesPart.Stylesheet.CellStyles.Elements().Any(c => c.BuiltinId != null && c.BuiltinId.HasValue && c.BuiltinId.Value == 0)) + defaultFormatId = workbookStylesPart.Stylesheet.CellStyles.Elements().Single(c => c.BuiltinId != null && c.BuiltinId.HasValue && c.BuiltinId.Value == 0).FormatId.Value; else if (workbookStylesPart.Stylesheet.CellStyles.Elements().Any()) - { - defaultFormatId = - workbookStylesPart.Stylesheet.CellStyles.Elements().Max(c => c.FormatId.Value) + 1; - } + defaultFormatId = workbookStylesPart.Stylesheet.CellStyles.Elements().Max(c => c.FormatId.Value) + 1; else defaultFormatId = 0; @@ -2642,7 +2664,7 @@ sharedNumberFormats.Add(numberFormat, new NumberFormatInfo { - NumberFormatId = numberFormatCount + 164, + NumberFormatId = XLConstants.NumberOfBuiltInStyles + numberFormatCount, NumberFormat = numberFormat }); numberFormatCount++; @@ -2652,14 +2674,14 @@ { if (!context.SharedFonts.ContainsKey(xlStyle.Font)) context.SharedFonts.Add(xlStyle.Font, - new FontInfo {FontId = fontCount++, Font = xlStyle.Font as XLFont}); + new FontInfo { FontId = fontCount++, Font = xlStyle.Font as XLFont }); if (!sharedFills.ContainsKey(xlStyle.Fill)) - sharedFills.Add(xlStyle.Fill, new FillInfo {FillId = fillCount++, Fill = xlStyle.Fill as XLFill}); + sharedFills.Add(xlStyle.Fill, new FillInfo { FillId = fillCount++, Fill = xlStyle.Fill as XLFill }); if (!sharedBorders.ContainsKey(xlStyle.Border)) sharedBorders.Add(xlStyle.Border, - new BorderInfo {BorderId = borderCount++, Border = xlStyle.Border as XLBorder}); + new BorderInfo { BorderId = borderCount++, Border = xlStyle.Border as XLBorder }); if (xlStyle.NumberFormat.NumberFormatId != -1 || sharedNumberFormats.ContainsKey(xlStyle.NumberFormat)) @@ -2668,7 +2690,7 @@ sharedNumberFormats.Add(xlStyle.NumberFormat, new NumberFormatInfo { - NumberFormatId = numberFormatCount + 164, + NumberFormatId = XLConstants.NumberOfBuiltInStyles + numberFormatCount, NumberFormat = xlStyle.NumberFormat }); numberFormatCount++; @@ -2708,13 +2730,9 @@ ResolveCellStyleFormats(workbookStylesPart, context); ResolveRest(workbookStylesPart, context); - if (workbookStylesPart.Stylesheet.CellStyles.Elements().All(c => c.Name != "Normal")) - { - //var defaultFormatId = context.SharedStyles.Values.Where(s => s.Style.Equals(DefaultStyle)).Single().StyleId; + if (!workbookStylesPart.Stylesheet.CellStyles.Elements().Any(c => c.BuiltinId != null && c.BuiltinId.HasValue && c.BuiltinId.Value == 0U)) + workbookStylesPart.Stylesheet.CellStyles.AppendChild(new CellStyle { Name = "Normal", FormatId = defaultFormatId, BuiltinId = 0U }); - var cellStyle1 = new CellStyle {Name = "Normal", FormatId = defaultFormatId, BuiltinId = 0U}; - workbookStylesPart.Stylesheet.CellStyles.AppendChild(cellStyle1); - } workbookStylesPart.Stylesheet.CellStyles.Count = (UInt32)workbookStylesPart.Stylesheet.CellStyles.Count(); var newSharedStyles = new Dictionary(); @@ -2785,18 +2803,18 @@ SaveContext context) { var differentialFormat = new DifferentialFormat(); - differentialFormat.Append(GetNewFont(new FontInfo {Font = cf.Style.Font as XLFont}, false)); + differentialFormat.Append(GetNewFont(new FontInfo { Font = cf.Style.Font as XLFont }, false)); if (!XLHelper.IsNullOrWhiteSpace(cf.Style.NumberFormat.Format)) { var numberFormat = new NumberingFormat { - NumberFormatId = (UInt32)(differentialFormats.Count() + 164), + NumberFormatId = (UInt32)(XLConstants.NumberOfBuiltInStyles + differentialFormats.Count()), FormatCode = cf.Style.NumberFormat.Format }; differentialFormat.Append(numberFormat); } - differentialFormat.Append(GetNewFill(new FillInfo {Fill = cf.Style.Fill as XLFill}, false)); - differentialFormat.Append(GetNewBorder(new BorderInfo {Border = cf.Style.Border as XLBorder}, false)); + differentialFormat.Append(GetNewFill(new FillInfo { Fill = cf.Style.Fill as XLFill }, false)); + differentialFormat.Append(GetNewBorder(new BorderInfo { Border = cf.Style.Border as XLBorder }, false)); differentialFormats.Append(differentialFormat); @@ -2994,7 +3012,7 @@ workbookStylesPart.Stylesheet.Borders.AppendChild(border); } allSharedBorders.Add(borderInfo.Border, - new BorderInfo {Border = borderInfo.Border, BorderId = (UInt32)borderId}); + new BorderInfo { Border = borderInfo.Border, BorderId = (UInt32)borderId }); } workbookStylesPart.Stylesheet.Borders.Count = (UInt32)workbookStylesPart.Stylesheet.Borders.Count(); return allSharedBorders; @@ -3011,7 +3029,7 @@ if (borderInfo.Border.LeftBorderModified || borderInfo.Border.LeftBorderColorModified || ignoreMod) { - var leftBorder = new LeftBorder {Style = borderInfo.Border.LeftBorder.ToOpenXml()}; + var leftBorder = new LeftBorder { Style = borderInfo.Border.LeftBorder.ToOpenXml() }; if (borderInfo.Border.LeftBorderColorModified || ignoreMod) { var leftBorderColor = GetNewColor(borderInfo.Border.LeftBorderColor); @@ -3022,7 +3040,7 @@ if (borderInfo.Border.RightBorderModified || borderInfo.Border.RightBorderColorModified || ignoreMod) { - var rightBorder = new RightBorder {Style = borderInfo.Border.RightBorder.ToOpenXml()}; + var rightBorder = new RightBorder { Style = borderInfo.Border.RightBorder.ToOpenXml() }; if (borderInfo.Border.RightBorderColorModified || ignoreMod) { var rightBorderColor = GetNewColor(borderInfo.Border.RightBorderColor); @@ -3033,7 +3051,7 @@ if (borderInfo.Border.TopBorderModified || borderInfo.Border.TopBorderColorModified || ignoreMod) { - var topBorder = new TopBorder {Style = borderInfo.Border.TopBorder.ToOpenXml()}; + var topBorder = new TopBorder { Style = borderInfo.Border.TopBorder.ToOpenXml() }; if (borderInfo.Border.TopBorderColorModified || ignoreMod) { var topBorderColor = GetNewColor(borderInfo.Border.TopBorderColor); @@ -3044,7 +3062,7 @@ if (borderInfo.Border.BottomBorderModified || borderInfo.Border.BottomBorderColorModified || ignoreMod) { - var bottomBorder = new BottomBorder {Style = borderInfo.Border.BottomBorder.ToOpenXml()}; + var bottomBorder = new BottomBorder { Style = borderInfo.Border.BottomBorder.ToOpenXml() }; if (borderInfo.Border.BottomBorderColorModified || ignoreMod) { var bottomBorderColor = GetNewColor(borderInfo.Border.BottomBorderColor); @@ -3055,7 +3073,7 @@ if (borderInfo.Border.DiagonalBorderModified || borderInfo.Border.DiagonalBorderColorModified || ignoreMod) { - var DiagonalBorder = new DiagonalBorder {Style = borderInfo.Border.DiagonalBorder.ToOpenXml()}; + var DiagonalBorder = new DiagonalBorder { Style = borderInfo.Border.DiagonalBorder.ToOpenXml() }; if (borderInfo.Border.DiagonalBorderColorModified || ignoreMod) { var DiagonalBorderColor = GetNewColor(borderInfo.Border.DiagonalBorderColor); @@ -3145,7 +3163,7 @@ var fill = GetNewFill(fillInfo); workbookStylesPart.Stylesheet.Fills.AppendChild(fill); } - allSharedFills.Add(fillInfo.Fill, new FillInfo {Fill = fillInfo.Fill, FillId = (UInt32)fillId}); + allSharedFills.Add(fillInfo.Fill, new FillInfo { Fill = fillInfo.Fill, FillId = (UInt32)fillId }); } workbookStylesPart.Stylesheet.Fills.Count = (UInt32)workbookStylesPart.Stylesheet.Fills.Count(); @@ -3161,7 +3179,7 @@ )) return; var fill1 = new Fill(); - var patternFill1 = new PatternFill {PatternType = patternValues}; + var patternFill1 = new PatternFill { PatternType = patternValues }; fill1.AppendChild(patternFill1); fills.AppendChild(fill1); } @@ -3254,7 +3272,7 @@ var font = GetNewFont(fontInfo); workbookStylesPart.Stylesheet.Fonts.AppendChild(font); } - newFonts.Add(fontInfo.Font, new FontInfo {Font = fontInfo.Font, FontId = (UInt32)fontId}); + newFonts.Add(fontInfo.Font, new FontInfo { Font = fontInfo.Font, FontId = (UInt32)fontId }); } context.SharedFonts.Clear(); foreach (var kp in newFonts) @@ -3270,25 +3288,25 @@ var italic = (fontInfo.Font.ItalicModified || ignoreMod) && fontInfo.Font.Italic ? new Italic() : null; var underline = (fontInfo.Font.UnderlineModified || ignoreMod) && fontInfo.Font.Underline != XLFontUnderlineValues.None - ? new Underline {Val = fontInfo.Font.Underline.ToOpenXml()} + ? new Underline { Val = fontInfo.Font.Underline.ToOpenXml() } : null; var strike = (fontInfo.Font.StrikethroughModified || ignoreMod) && fontInfo.Font.Strikethrough ? new Strike() : null; var verticalAlignment = fontInfo.Font.VerticalAlignmentModified || ignoreMod - ? new VerticalTextAlignment {Val = fontInfo.Font.VerticalAlignment.ToOpenXml()} + ? new VerticalTextAlignment { Val = fontInfo.Font.VerticalAlignment.ToOpenXml() } : null; var shadow = (fontInfo.Font.ShadowModified || ignoreMod) && fontInfo.Font.Shadow ? new Shadow() : null; var fontSize = fontInfo.Font.FontSizeModified || ignoreMod - ? new FontSize {Val = fontInfo.Font.FontSize} + ? new FontSize { Val = fontInfo.Font.FontSize } : null; var color = fontInfo.Font.FontColorModified || ignoreMod ? GetNewColor(fontInfo.Font.FontColor) : null; var fontName = fontInfo.Font.FontNameModified || ignoreMod - ? new FontName {Val = fontInfo.Font.FontName} + ? new FontName { Val = fontInfo.Font.FontName } : null; var fontFamilyNumbering = fontInfo.Font.FontFamilyNumberingModified || ignoreMod - ? new FontFamilyNumbering {Val = (Int32)fontInfo.Font.FontFamilyNumbering} + ? new FontFamilyNumbering { Val = (Int32)fontInfo.Font.FontFamilyNumbering } : null; if (bold != null) @@ -3349,7 +3367,7 @@ private bool FontsAreEqual(Font f, IXLFont xlFont) { - var nf = new XLFont {Bold = f.Bold != null, Italic = f.Italic != null}; + var nf = new XLFont { Bold = f.Bold != null, Italic = f.Italic != null }; if (f.Underline != null) { nf.Underline = f.Underline.Val != null @@ -3395,7 +3413,7 @@ var allSharedNumberFormats = new Dictionary(); foreach (var numberFormatInfo in sharedNumberFormats.Values.Where(nf => nf.NumberFormatId != defaultFormatId)) { - var numberingFormatId = 164; + var numberingFormatId = XLConstants.NumberOfBuiltInStyles + 1; var foundOne = false; foreach (NumberingFormat nf in workbookStylesPart.Stylesheet.NumberingFormats) { @@ -3489,7 +3507,7 @@ if (worksheetPart.Worksheet.SheetProperties.PageSetupProperties == null && (xlWorksheet.PageSetup.PagesTall > 0 || xlWorksheet.PageSetup.PagesWide > 0)) - worksheetPart.Worksheet.SheetProperties.PageSetupProperties = new PageSetupProperties {FitToPage = true}; + worksheetPart.Worksheet.SheetProperties.PageSetupProperties = new PageSetupProperties { FitToPage = true }; #endregion @@ -3514,7 +3532,7 @@ #region SheetViews if (worksheetPart.Worksheet.SheetDimension == null) - worksheetPart.Worksheet.SheetDimension = new SheetDimension {Reference = sheetDimensionReference}; + worksheetPart.Worksheet.SheetDimension = new SheetDimension { Reference = sheetDimensionReference }; cm.SetElement(XLWSContentManager.XLWSContents.SheetDimension, worksheetPart.Worksheet.SheetDimension); @@ -3526,7 +3544,7 @@ var sheetView = (SheetView)worksheetPart.Worksheet.SheetViews.FirstOrDefault(); if (sheetView == null) { - sheetView = new SheetView {WorkbookViewId = 0U}; + sheetView = new SheetView { WorkbookViewId = 0U }; worksheetPart.Worksheet.SheetViews.AppendChild(sheetView); } @@ -3619,12 +3637,12 @@ selection.ActiveCell = firstSelection.RangeAddress.FirstAddress.ToStringRelative(false); - var seqRef = new List {selection.ActiveCell.Value}; + var seqRef = new List { selection.ActiveCell.Value }; seqRef.AddRange(xlWorksheet.SelectedRanges .Select(range => range.RangeAddress.ToStringRelative(false))); - selection.SequenceOfReferences = new ListValue {InnerText = String.Join(" ", seqRef.Distinct().ToArray())}; + selection.SequenceOfReferences = new ListValue { InnerText = String.Join(" ", seqRef.Distinct().ToArray()) }; sheetView.Append(selection); } @@ -3840,7 +3858,7 @@ row = sheetDataRows[distinctRow]; else { - row = new Row {RowIndex = (UInt32)distinctRow}; + row = new Row { RowIndex = (UInt32)distinctRow }; if (noRows) { sheetData.AppendChild(row); @@ -3860,7 +3878,7 @@ } if (maxColumn > 0) - row.Spans = new ListValue {InnerText = "1:" + maxColumn.ToInvariantString()}; + row.Spans = new ListValue { InnerText = "1:" + maxColumn.ToInvariantString() }; row.Height = null; row.CustomHeight = null; @@ -4137,7 +4155,7 @@ foreach (var mergeCell in (xlWorksheet).Internals.MergedRanges.Select( m => m.RangeAddress.FirstAddress.ToString() + ":" + m.RangeAddress.LastAddress.ToString()).Select( - merged => new MergeCell {Reference = merged})) + merged => new MergeCell { Reference = merged })) mergeCells.AppendChild(mergeCell); mergeCells.Count = (UInt32)mergeCells.Count(); @@ -4176,7 +4194,7 @@ SequenceOfReferences = new ListValue { InnerText = cfGroup.RangeId } }; - foreach(var cf in cfGroup.CfList) + foreach (var cf in cfGroup.CfList) { conditionalFormatting.Append(XLCFConverters.Convert(cf, priority, context)); priority++; @@ -4230,7 +4248,7 @@ ErrorStyle = dv.ErrorStyle.ToOpenXml(), Operator = dv.Operator.ToOpenXml(), SequenceOfReferences = - new ListValue {InnerText = sequence} + new ListValue { InnerText = sequence } }; dataValidations.AppendChild(dataValidation); @@ -4266,7 +4284,7 @@ if (hl.IsExternal) { var rId = context.RelIdGenerator.GetNext(RelType.Workbook); - hyperlink = new Hyperlink {Reference = hl.Cell.Address.ToString(), Id = rId}; + hyperlink = new Hyperlink { Reference = hl.Cell.Address.ToString(), Id = rId }; worksheetPart.AddHyperlinkRelationship(hl.ExternalAddress, true, rId); } else @@ -4373,15 +4391,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 @@ -4522,7 +4536,7 @@ tableParts.Count = (UInt32)xlWorksheet.Tables.Count(); foreach ( var tablePart in - from XLTable xlTable in xlWorksheet.Tables select new TablePart {Id = xlTable.RelId}) + from XLTable xlTable in xlWorksheet.Tables select new TablePart { Id = xlTable.RelId }) tableParts.AppendChild(tablePart); #endregion @@ -4536,7 +4550,7 @@ if (!XLHelper.IsNullOrWhiteSpace(xlWorksheet.LegacyDrawingId)) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.LegacyDrawing); - worksheetPart.Worksheet.InsertAfter(new LegacyDrawing {Id = xlWorksheet.LegacyDrawingId}, + worksheetPart.Worksheet.InsertAfter(new LegacyDrawing { Id = xlWorksheet.LegacyDrawingId }, previousElement); } } @@ -4567,7 +4581,7 @@ foreach (var kp in xlAutoFilter.Filters) { - var filterColumn = new FilterColumn {ColumnId = (UInt32)kp.Key - 1}; + var filterColumn = new FilterColumn { ColumnId = (UInt32)kp.Key - 1 }; var xlFilterColumn = xlAutoFilter.Column(kp.Key); var filterType = xlFilterColumn.FilterType; if (filterType == XLFilterType.Custom) @@ -4575,7 +4589,7 @@ var customFilters = new CustomFilters(); foreach (var filter in kp.Value) { - var customFilter = new CustomFilter {Val = filter.Value.ToString()}; + var customFilter = new CustomFilter { Val = filter.Value.ToString() }; if (filter.Operator != XLFilterOperator.Equal) customFilter.Operator = filter.Operator.ToOpenXml(); @@ -4589,7 +4603,7 @@ } else if (filterType == XLFilterType.TopBottom) { - var top101 = new Top10 {Val = (double)xlFilterColumn.TopBottomValue}; + var top101 = new Top10 { Val = (double)xlFilterColumn.TopBottomValue }; if (xlFilterColumn.TopBottomType == XLTopBottomType.Percent) top101.Percent = true; if (xlFilterColumn.TopBottomPart == XLTopBottomPart.Bottom) @@ -4600,7 +4614,7 @@ else if (filterType == XLFilterType.Dynamic) { var dynamicFilter = new DynamicFilter - {Type = xlFilterColumn.DynamicType.ToOpenXml(), Val = xlFilterColumn.DynamicValue}; + { Type = xlFilterColumn.DynamicType.ToOpenXml(), Val = xlFilterColumn.DynamicValue }; filterColumn.Append(dynamicFilter); } else @@ -4608,7 +4622,7 @@ var filters = new Filters(); foreach (var filter in kp.Value) { - filters.Append(new Filter {Val = filter.Value.ToString()}); + filters.Append(new Filter { Val = filter.Value.ToString() }); } filterColumn.Append(filters); diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs index c622c30..227ee49 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; } @@ -1165,7 +1165,21 @@ Workbook.Worksheets.ForEach(ws => MoveNamedRangesColumns(range, columnsShifted, ws.NamedRanges)); MoveNamedRangesColumns(range, columnsShifted, Workbook.NamedRanges); ShiftConditionalFormattingColumns(range, columnsShifted); + ShiftPageBreaksColumns(range, columnsShifted); } + + private void ShiftPageBreaksColumns(XLRange range, int columnsShifted) + { + for (var i = 0; i < PageSetup.ColumnBreaks.Count; i++) + { + int br = PageSetup.ColumnBreaks[i]; + if (range.RangeAddress.FirstAddress.ColumnNumber <= br) + { + PageSetup.ColumnBreaks[i] = br + columnsShifted; + } + } + } + private void ShiftConditionalFormattingColumns(XLRange range, int columnsShifted) { Int32 firstColumn = range.RangeAddress.FirstAddress.ColumnNumber; @@ -1219,7 +1233,21 @@ Workbook.Worksheets.ForEach(ws => MoveNamedRangesRows(range, rowsShifted, ws.NamedRanges)); MoveNamedRangesRows(range, rowsShifted, Workbook.NamedRanges); ShiftConditionalFormattingRows(range, rowsShifted); + ShiftPageBreaksRows(range, rowsShifted); } + + private void ShiftPageBreaksRows(XLRange range, int rowsShifted) + { + for (var i = 0; i < PageSetup.RowBreaks.Count; i++) + { + int br = PageSetup.RowBreaks[i]; + if (range.RangeAddress.FirstAddress.RowNumber <= br) + { + PageSetup.RowBreaks[i] = br + rowsShifted; + } + } + } + private void ShiftConditionalFormattingRows(XLRange range, int rowsShifted) { Int32 firstRow = range.RangeAddress.FirstAddress.RowNumber; diff --git a/ClosedXML_Examples/Misc/CellValues.cs b/ClosedXML_Examples/Misc/CellValues.cs index 6919bcf..ea08869 100644 --- a/ClosedXML_Examples/Misc/CellValues.cs +++ b/ClosedXML_Examples/Misc/CellValues.cs @@ -153,8 +153,8 @@ TimeSpan timeSpan1 = (TimeSpan)cellTimeSpan.Value; TimeSpan timeSpan2 = cellTimeSpan.GetTimeSpan(); TimeSpan timeSpan3 = cellTimeSpan.GetValue(); - String timeSpanString = cellTimeSpan.GetString(); - String timeSpanFormattedString = cellTimeSpan.GetFormattedString(); + String timeSpanString = "'" + cellTimeSpan.GetString(); + String timeSpanFormattedString = "'" + cellTimeSpan.GetFormattedString(); // Set the values back to cells ws.Cell(7, 3).Value = timeSpan1; 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 89a0eff..e89fd07 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -77,6 +77,7 @@ + @@ -251,6 +252,9 @@ + + + diff --git a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs index 3568007..8398a20 100644 --- a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs +++ b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs @@ -1,10 +1,8 @@ -using System; +using ClosedXML.Excel; +using NUnit.Framework; using System.Collections.Generic; -using System.Data; using System.IO; using System.Linq; -using ClosedXML.Excel; -using NUnit.Framework; namespace ClosedXML_Tests.Excel { @@ -20,7 +18,8 @@ var files = new List() { @"Misc\TableWithCustomTheme.xlsx", - @"Misc\EmptyTable.xlsx" + @"Misc\EmptyTable.xlsx", + @"Misc\LoadPivotTables.xlsx" }; foreach (var file in files) @@ -40,5 +39,62 @@ table.DataRange.InsertRowsBelow(5); } } + + [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); + } + } + } + + [Test] + public void CanLoadBasicPivotTable() + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\LoadPivotTables.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + var ws = wb.Worksheet("PivotTable1"); + var pt = ws.PivotTable("PivotTable1"); + Assert.AreEqual("PivotTable1", pt.Name); + + Assert.AreEqual(1, pt.RowLabels.Count()); + Assert.AreEqual("Name", pt.RowLabels.Single().SourceName); + + Assert.AreEqual(1, pt.ColumnLabels.Count()); + Assert.AreEqual("Month", pt.ColumnLabels.Single().SourceName); + + var pv = pt.Values.Single(); + Assert.AreEqual("Sum of NumberOfOrders", pv.CustomName); + Assert.AreEqual("NumberOfOrders", pv.SourceName); + } + } + + /// + /// For non-English locales, the default style ("Normal" in English) can be + /// another piece of text (e.g. Обычный in Russian). + /// This test ensures that the default style is correctly detected and + /// no style conflicts occur on save. + /// + [Test] + public void CanSaveFileWithDefaultStyleNameNotInEnglish() + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\FileWithDefaultStyleNameNotInEnglish.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + using (var ms = new MemoryStream()) + { + wb.SaveAs(ms, true); + } + } + } } } diff --git a/ClosedXML_Tests/Excel/PageSetup/PageBreaksTests.cs b/ClosedXML_Tests/Excel/PageSetup/PageBreaksTests.cs new file mode 100644 index 0000000..04d7464 --- /dev/null +++ b/ClosedXML_Tests/Excel/PageSetup/PageBreaksTests.cs @@ -0,0 +1,78 @@ +using System.Diagnostics; +using ClosedXML.Excel; +using NUnit.Framework; + +namespace ClosedXML_Tests.Excel +{ + [TestFixture] + public class PageBreaksTests + { + [Test] + public void RowBreaksShouldBeSorted() + { + var wb = new XLWorkbook(); + IXLWorksheet sheet = wb.AddWorksheet("Sheet1"); + + sheet.PageSetup.AddHorizontalPageBreak(10); + sheet.PageSetup.AddHorizontalPageBreak(12); + sheet.PageSetup.AddHorizontalPageBreak(5); + Assert.That(sheet.PageSetup.RowBreaks, Is.EqualTo(new[] { 5, 10, 12 })); + } + + [Test] + public void ColumnBreaksShouldBeSorted() + { + var wb = new XLWorkbook(); + IXLWorksheet sheet = wb.AddWorksheet("Sheet1"); + + sheet.PageSetup.AddVerticalPageBreak(10); + sheet.PageSetup.AddVerticalPageBreak(12); + sheet.PageSetup.AddVerticalPageBreak(5); + Assert.That(sheet.PageSetup.ColumnBreaks, Is.EqualTo(new[] { 5, 10, 12 })); + } + + [Test] + public void RowBreaksShiftWhenInsertedRowAbove() + { + var wb = new XLWorkbook(); + IXLWorksheet sheet = wb.AddWorksheet("Sheet1"); + + sheet.PageSetup.AddHorizontalPageBreak(10); + sheet.Row(5).InsertRowsAbove(1); + Assert.AreEqual(11, sheet.PageSetup.RowBreaks[0]); + } + + [Test] + public void RowBreaksNotShiftWhenInsertedRowBelow() + { + var wb = new XLWorkbook(); + IXLWorksheet sheet = wb.AddWorksheet("Sheet1"); + + sheet.PageSetup.AddHorizontalPageBreak(10); + sheet.Row(15).InsertRowsAbove(1); + Assert.AreEqual(10, sheet.PageSetup.RowBreaks[0]); + } + + [Test] + public void ColumnBreaksShiftWhenInsertedColumnBefore() + { + var wb = new XLWorkbook(); + IXLWorksheet sheet = wb.AddWorksheet("Sheet1"); + + sheet.PageSetup.AddVerticalPageBreak(10); + sheet.Column(5).InsertColumnsBefore(1); + Assert.AreEqual(11, sheet.PageSetup.ColumnBreaks[0]); + } + + [Test] + public void ColumnBreaksNotShiftWhenInsertedColumnAfter() + { + var wb = new XLWorkbook(); + IXLWorksheet sheet = wb.AddWorksheet("Sheet1"); + + sheet.PageSetup.AddVerticalPageBreak(10); + sheet.Column(15).InsertColumnsBefore(1); + Assert.AreEqual(10, sheet.PageSetup.ColumnBreaks[0]); + } + } +} \ No newline at end of file diff --git a/ClosedXML_Tests/Resource/Examples/Misc/CellValues.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/CellValues.xlsx index ebd1aae..9b5b3c3 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/CellValues.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/CellValues.xlsx Binary files differ 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/FileWithDefaultStyleNameNotInEnglish.xlsx b/ClosedXML_Tests/Resource/Misc/FileWithDefaultStyleNameNotInEnglish.xlsx new file mode 100644 index 0000000..e94bc6a --- /dev/null +++ b/ClosedXML_Tests/Resource/Misc/FileWithDefaultStyleNameNotInEnglish.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 diff --git a/ClosedXML_Tests/Resource/Misc/LoadPivotTables.xlsx b/ClosedXML_Tests/Resource/Misc/LoadPivotTables.xlsx new file mode 100644 index 0000000..6fad7fe --- /dev/null +++ b/ClosedXML_Tests/Resource/Misc/LoadPivotTables.xlsx Binary files differ