private static void GenerateWorksheetPartContent(WorksheetPart worksheetPart, XLWorksheet xlWorksheet, SaveContext context) { #region Worksheet if (worksheetPart.Worksheet == null) worksheetPart.Worksheet = new Worksheet(); GenerateTables(xlWorksheet, worksheetPart, context); if ( !worksheetPart.Worksheet.NamespaceDeclarations.Contains(new KeyValuePair<String, String>("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"))) { worksheetPart.Worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); } #endregion var cm = new XLWSContentManager(worksheetPart.Worksheet); #region SheetProperties if (worksheetPart.Worksheet.SheetProperties == null) worksheetPart.Worksheet.SheetProperties = new SheetProperties(); worksheetPart.Worksheet.SheetProperties.TabColor = xlWorksheet.TabColor.HasValue ? GetTabColor(xlWorksheet.TabColor) : null; cm.SetElement(XLWSContentManager.XLWSContents.SheetProperties, worksheetPart.Worksheet.SheetProperties); if (worksheetPart.Worksheet.SheetProperties.OutlineProperties == null) worksheetPart.Worksheet.SheetProperties.OutlineProperties = new OutlineProperties(); worksheetPart.Worksheet.SheetProperties.OutlineProperties.SummaryBelow = (xlWorksheet.Outline.SummaryVLocation == XLOutlineSummaryVLocation.Bottom); worksheetPart.Worksheet.SheetProperties.OutlineProperties.SummaryRight = (xlWorksheet.Outline.SummaryHLocation == XLOutlineSummaryHLocation.Right); if (worksheetPart.Worksheet.SheetProperties.PageSetupProperties == null && (xlWorksheet.PageSetup.PagesTall > 0 || xlWorksheet.PageSetup.PagesWide > 0)) worksheetPart.Worksheet.SheetProperties.PageSetupProperties = new PageSetupProperties {FitToPage = true}; #endregion var maxColumn = 0; var sheetDimensionReference = "A1"; if (xlWorksheet.Internals.CellsCollection.Count > 0) { maxColumn = xlWorksheet.Internals.CellsCollection.MaxColumnUsed; var maxRow = xlWorksheet.Internals.CellsCollection.MaxRowUsed; sheetDimensionReference = "A1:" + XLHelper.GetColumnLetterFromNumber(maxColumn) + maxRow.ToStringLookup(); } if (xlWorksheet.Internals.ColumnsCollection.Count > 0) { var maxColCollection = xlWorksheet.Internals.ColumnsCollection.Keys.Max(); if (maxColCollection > maxColumn) maxColumn = maxColCollection; } #region SheetViews if (worksheetPart.Worksheet.SheetDimension == null) worksheetPart.Worksheet.SheetDimension = new SheetDimension {Reference = sheetDimensionReference}; cm.SetElement(XLWSContentManager.XLWSContents.SheetDimension, worksheetPart.Worksheet.SheetDimension); if (worksheetPart.Worksheet.SheetViews == null) worksheetPart.Worksheet.SheetViews = new SheetViews(); cm.SetElement(XLWSContentManager.XLWSContents.SheetViews, worksheetPart.Worksheet.SheetViews); var sheetView = (SheetView)worksheetPart.Worksheet.SheetViews.FirstOrDefault(); if (sheetView == null) { sheetView = new SheetView {WorkbookViewId = 0U}; worksheetPart.Worksheet.SheetViews.AppendChild(sheetView); } if (xlWorksheet.TabSelected) sheetView.TabSelected = true; else sheetView.TabSelected = null; if (xlWorksheet.ShowFormulas) sheetView.ShowFormulas = true; else sheetView.ShowFormulas = null; if (xlWorksheet.ShowGridLines) sheetView.ShowGridLines = null; else sheetView.ShowGridLines = false; if (xlWorksheet.ShowOutlineSymbols) sheetView.ShowOutlineSymbols = null; else sheetView.ShowOutlineSymbols = false; if (xlWorksheet.ShowRowColHeaders) sheetView.ShowRowColHeaders = null; else sheetView.ShowRowColHeaders = false; if (xlWorksheet.ShowRuler) sheetView.ShowRuler = null; else sheetView.ShowRuler = false; if (xlWorksheet.ShowWhiteSpace) sheetView.ShowWhiteSpace = null; else sheetView.ShowWhiteSpace = false; if (xlWorksheet.ShowZeros) sheetView.ShowZeros = null; else sheetView.ShowZeros = false; if (xlWorksheet.RightToLeft) sheetView.RightToLeft = true; else sheetView.RightToLeft = null; if (xlWorksheet.SheetView.View == XLSheetViewOptions.Normal) sheetView.View = null; else sheetView.View = xlWorksheet.SheetView.View.ToOpenXml(); var pane = sheetView.Elements<Pane>().FirstOrDefault(); if (pane == null) { pane = new Pane(); sheetView.AppendChild(pane); } pane.State = PaneStateValues.FrozenSplit; Double hSplit = xlWorksheet.SheetView.SplitColumn; Double ySplit = xlWorksheet.SheetView.SplitRow; pane.HorizontalSplit = hSplit; pane.VerticalSplit = ySplit; pane.TopLeftCell = XLHelper.GetColumnLetterFromNumber(xlWorksheet.SheetView.SplitColumn + 1) + (xlWorksheet.SheetView.SplitRow + 1); if (hSplit == 0 && ySplit == 0) sheetView.RemoveAllChildren<Pane>(); if (xlWorksheet.SelectedRanges.Any() || xlWorksheet.ActiveCell != null) { sheetView.RemoveAllChildren<Selection>(); var firstSelection = xlWorksheet.SelectedRanges.FirstOrDefault(); var selection = new Selection(); if (xlWorksheet.ActiveCell != null) selection.ActiveCell = xlWorksheet.ActiveCell.Address.ToStringRelative(false); else if (firstSelection != null) selection.ActiveCell = firstSelection.RangeAddress.FirstAddress.ToStringRelative(false); var seqRef = new List<String> {selection.ActiveCell.Value}; seqRef.AddRange(xlWorksheet.SelectedRanges .Select(range => range.RangeAddress.ToStringRelative(false))); selection.SequenceOfReferences = new ListValue<StringValue> {InnerText = String.Join(" ", seqRef.Distinct().ToArray())}; sheetView.Append(selection); } #endregion var maxOutlineColumn = 0; if (xlWorksheet.ColumnCount() > 0) maxOutlineColumn = xlWorksheet.GetMaxColumnOutline(); var maxOutlineRow = 0; if (xlWorksheet.RowCount() > 0) maxOutlineRow = xlWorksheet.GetMaxRowOutline(); #region SheetFormatProperties if (worksheetPart.Worksheet.SheetFormatProperties == null) worksheetPart.Worksheet.SheetFormatProperties = new SheetFormatProperties(); cm.SetElement(XLWSContentManager.XLWSContents.SheetFormatProperties, worksheetPart.Worksheet.SheetFormatProperties); worksheetPart.Worksheet.SheetFormatProperties.DefaultRowHeight = xlWorksheet.RowHeight; if (xlWorksheet.RowHeightChanged) worksheetPart.Worksheet.SheetFormatProperties.CustomHeight = true; else worksheetPart.Worksheet.SheetFormatProperties.CustomHeight = null; var worksheetColumnWidth = GetColumnWidth(xlWorksheet.ColumnWidth); if (xlWorksheet.ColumnWidthChanged) worksheetPart.Worksheet.SheetFormatProperties.DefaultColumnWidth = worksheetColumnWidth; else worksheetPart.Worksheet.SheetFormatProperties.DefaultColumnWidth = null; if (maxOutlineColumn > 0) worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelColumn = (byte)maxOutlineColumn; else worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelColumn = null; if (maxOutlineRow > 0) worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelRow = (byte)maxOutlineRow; else worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelRow = null; #endregion #region Columns if (xlWorksheet.Internals.CellsCollection.Count == 0 && xlWorksheet.Internals.ColumnsCollection.Count == 0 && xlWorksheet.Style.Equals(DefaultStyle)) worksheetPart.Worksheet.RemoveAllChildren<Columns>(); else { if (!worksheetPart.Worksheet.Elements<Columns>().Any()) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.Columns); worksheetPart.Worksheet.InsertAfter(new Columns(), previousElement); } var columns = worksheetPart.Worksheet.Elements<Columns>().First(); cm.SetElement(XLWSContentManager.XLWSContents.Columns, columns); var sheetColumnsByMin = columns.Elements<Column>().ToDictionary(c => c.Min.Value, c => c); //Dictionary<UInt32, Column> sheetColumnsByMax = columns.Elements<Column>().ToDictionary(c => c.Max.Value, c => c); Int32 minInColumnsCollection; Int32 maxInColumnsCollection; if (xlWorksheet.Internals.ColumnsCollection.Count > 0) { minInColumnsCollection = xlWorksheet.Internals.ColumnsCollection.Keys.Min(); maxInColumnsCollection = xlWorksheet.Internals.ColumnsCollection.Keys.Max(); } else { minInColumnsCollection = 1; maxInColumnsCollection = 0; } var worksheetStyleId = context.SharedStyles[xlWorksheet.GetStyleId()].StyleId; if (minInColumnsCollection > 1) { UInt32Value min = 1; UInt32Value max = (UInt32)(minInColumnsCollection - 1); for (var co = min; co <= max; co++) { var column = new Column { Min = co, Max = co, Style = worksheetStyleId, Width = worksheetColumnWidth, CustomWidth = true }; UpdateColumn(column, columns, sheetColumnsByMin); //, sheetColumnsByMax); } } for (var co = minInColumnsCollection; co <= maxInColumnsCollection; co++) { UInt32 styleId; Double columnWidth; var isHidden = false; var collapsed = false; var outlineLevel = 0; if (xlWorksheet.Internals.ColumnsCollection.ContainsKey(co)) { styleId = context.SharedStyles[xlWorksheet.Internals.ColumnsCollection[co].GetStyleId()].StyleId; columnWidth = GetColumnWidth(xlWorksheet.Internals.ColumnsCollection[co].Width); isHidden = xlWorksheet.Internals.ColumnsCollection[co].IsHidden; collapsed = xlWorksheet.Internals.ColumnsCollection[co].Collapsed; outlineLevel = xlWorksheet.Internals.ColumnsCollection[co].OutlineLevel; } else { styleId = context.SharedStyles[xlWorksheet.GetStyleId()].StyleId; columnWidth = worksheetColumnWidth; } var column = new Column { Min = (UInt32)co, Max = (UInt32)co, Style = styleId, Width = columnWidth, CustomWidth = true }; if (isHidden) column.Hidden = true; if (collapsed) column.Collapsed = true; if (outlineLevel > 0) column.OutlineLevel = (byte)outlineLevel; UpdateColumn(column, columns, sheetColumnsByMin); //, sheetColumnsByMax); } var collection = maxInColumnsCollection; foreach ( var col in columns.Elements<Column>().Where(c => c.Min > (UInt32)(collection)).OrderBy( c => c.Min.Value)) { col.Style = worksheetStyleId; col.Width = worksheetColumnWidth; col.CustomWidth = true; if ((Int32)col.Max.Value > maxInColumnsCollection) maxInColumnsCollection = (Int32)col.Max.Value; } if (maxInColumnsCollection < XLHelper.MaxColumnNumber && !xlWorksheet.Style.Equals(DefaultStyle)) { var column = new Column { Min = (UInt32)(maxInColumnsCollection + 1), Max = (UInt32)(XLHelper.MaxColumnNumber), Style = worksheetStyleId, Width = worksheetColumnWidth, CustomWidth = true }; columns.AppendChild(column); } CollapseColumns(columns, sheetColumnsByMin); if (!columns.Any()) { worksheetPart.Worksheet.RemoveAllChildren<Columns>(); cm.SetElement(XLWSContentManager.XLWSContents.Columns, null); } } #endregion #region SheetData if (!worksheetPart.Worksheet.Elements<SheetData>().Any()) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.SheetData); worksheetPart.Worksheet.InsertAfter(new SheetData(), previousElement); } var sheetData = worksheetPart.Worksheet.Elements<SheetData>().First(); cm.SetElement(XLWSContentManager.XLWSContents.SheetData, sheetData); var cellsByRow = new Dictionary<Int32, List<IXLCell>>(); foreach (var c in xlWorksheet.Internals.CellsCollection.GetCells()) { var rowNum = c.Address.RowNumber; if (!cellsByRow.ContainsKey(rowNum)) cellsByRow.Add(rowNum, new List<IXLCell>()); cellsByRow[rowNum].Add(c); } var lastRow = 0; var sheetDataRows = sheetData.Elements<Row>().ToDictionary(r => r.RowIndex == null ? ++lastRow : (Int32)r.RowIndex.Value, r => r); foreach ( var r in xlWorksheet.Internals.RowsCollection.Deleted.Where(r => sheetDataRows.ContainsKey(r.Key))) { sheetData.RemoveChild(sheetDataRows[r.Key]); sheetDataRows.Remove(r.Key); xlWorksheet.Internals.CellsCollection.Deleted.RemoveWhere(d => d.Row == r.Key); } var distinctRows = cellsByRow.Keys.Union(xlWorksheet.Internals.RowsCollection.Keys); var noRows = (sheetData.Elements<Row>().FirstOrDefault() == null); foreach (var distinctRow in distinctRows.OrderBy(r => r)) { Row row; // = sheetData.Elements<Row>().FirstOrDefault(r => r.RowIndex.Value == (UInt32)distinctRow); if (sheetDataRows.ContainsKey(distinctRow)) row = sheetDataRows[distinctRow]; else { row = new Row {RowIndex = (UInt32)distinctRow}; if (noRows) { sheetData.AppendChild(row); noRows = false; } else { if (sheetDataRows.Any(r => r.Key > row.RowIndex.Value)) { var minRow = sheetDataRows.Where(r => r.Key > (Int32)row.RowIndex.Value).Min(r => r.Key); var rowBeforeInsert = sheetDataRows[minRow]; sheetData.InsertBefore(row, rowBeforeInsert); } else sheetData.AppendChild(row); } } if (maxColumn > 0) row.Spans = new ListValue<StringValue> {InnerText = "1:" + maxColumn.ToStringLookup()}; row.Height = null; row.CustomHeight = null; row.Hidden = null; row.StyleIndex = null; row.CustomFormat = null; row.Collapsed = null; if (xlWorksheet.Internals.RowsCollection.ContainsKey(distinctRow)) { var thisRow = xlWorksheet.Internals.RowsCollection[distinctRow]; if (thisRow.HeightChanged) { row.Height = thisRow.Height; row.CustomHeight = true; row.CustomFormat = true; } if (thisRow.GetStyleId() != xlWorksheet.GetStyleId()) { row.StyleIndex = context.SharedStyles[thisRow.GetStyleId()].StyleId; row.CustomFormat = true; } if (thisRow.IsHidden) row.Hidden = true; if (thisRow.Collapsed) row.Collapsed = true; if (thisRow.OutlineLevel > 0) row.OutlineLevel = (byte)thisRow.OutlineLevel; } var lastCell = 0; var cellsByReference = row.Elements<Cell>().ToDictionary(c => c.CellReference == null ? XLHelper.GetColumnLetterFromNumber( ++lastCell) + distinctRow : c.CellReference.Value, c => c); foreach (var c in xlWorksheet.Internals.CellsCollection.Deleted.ToList()) { var key = XLHelper.GetColumnLetterFromNumber(c.Column) + c.Row.ToStringLookup(); if (!cellsByReference.ContainsKey(key)) continue; row.RemoveChild(cellsByReference[key]); xlWorksheet.Internals.CellsCollection.Deleted.Remove(c); } if (!cellsByRow.ContainsKey(distinctRow)) continue; var isNewRow = !row.Elements<Cell>().Any(); var mRows = row.Elements<Cell>().ToDictionary(c => XLHelper.GetColumnNumberFromAddress(c.CellReference.Value), c => c); foreach (var opCell in cellsByRow[distinctRow] .OrderBy(c => c.Address.ColumnNumber) .Select(c => (XLCell)c)) { var styleId = context.SharedStyles[opCell.GetStyleId()].StyleId; var dataType = opCell.DataType; var cellReference = (opCell.Address).GetTrimmedAddress(); Cell cell; if (cellsByReference.ContainsKey(cellReference)) cell = cellsByReference[cellReference]; else { cell = new Cell {CellReference = new StringValue(cellReference)}; if (isNewRow) row.AppendChild(cell); else { var newColumn = XLHelper.GetColumnNumberFromAddress(cellReference); Cell cellBeforeInsert = null; int[] lastCo = {Int32.MaxValue}; foreach (var c in mRows.Where(kp => kp.Key > newColumn).Where(c => lastCo[0] > c.Key)) { cellBeforeInsert = c.Value; lastCo[0] = c.Key; } if (cellBeforeInsert == null) row.AppendChild(cell); else row.InsertBefore(cell, cellBeforeInsert); } } cell.StyleIndex = styleId; var formula = opCell.FormulaA1; if (opCell.HasFormula) { if (formula.StartsWith("{")) { formula = formula.Substring(1, formula.Length - 2); var f = new CellFormula {FormulaType = CellFormulaValues.Array}; if (opCell.FormulaReference.FirstAddress.Equals(opCell.Address)) { f.Text = formula; f.Reference = opCell.FormulaReference.ToStringRelative(); } cell.CellFormula = f; } else cell.CellFormula = new CellFormula(formula); cell.CellValue = null; } else { cell.CellFormula = null; cell.DataType = opCell.DataType == XLCellValues.DateTime ? null : GetCellValue(opCell); var cellValue = new CellValue(); if (dataType == XLCellValues.Text) { if (opCell.InnerText.Length == 0) cell.CellValue = null; else { if (opCell.ShareString) { cellValue.Text = opCell.SharedStringId.ToString(); cell.CellValue = cellValue; } else { var text = opCell.GetString(); var t = new Text(text); if (text.PreserveSpaces()) t.Space = SpaceProcessingModeValues.Preserve; cell.InlineString = new InlineString {Text = t}; } } } else if (dataType == XLCellValues.TimeSpan) { var timeSpan = opCell.GetTimeSpan(); cellValue.Text = XLCell.BaseDate.Add(timeSpan).ToOADate().ToString(CultureInfo.InvariantCulture); cell.CellValue = cellValue; } else if (dataType == XLCellValues.DateTime || dataType == XLCellValues.Number) { if (!XLHelper.IsNullOrWhiteSpace(opCell.InnerText)) cellValue.Text = Double.Parse(opCell.InnerText).ToString(CultureInfo.InvariantCulture); cell.CellValue = cellValue; } else { cellValue.Text = opCell.InnerText; cell.CellValue = cellValue; } } } xlWorksheet.Internals.CellsCollection.Deleted.RemoveWhere(d => d.Row == distinctRow); } foreach ( var r in xlWorksheet.Internals.CellsCollection.Deleted.Select(c => c.Row).Distinct().Where( sheetDataRows.ContainsKey)) { sheetData.RemoveChild(sheetDataRows[r]); sheetDataRows.Remove(r); } #endregion #region SheetProtection if (xlWorksheet.Protection.Protected) { if (!worksheetPart.Worksheet.Elements<SheetProtection>().Any()) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.SheetProtection); worksheetPart.Worksheet.InsertAfter(new SheetProtection(), previousElement); } var sheetProtection = worksheetPart.Worksheet.Elements<SheetProtection>().First(); cm.SetElement(XLWSContentManager.XLWSContents.SheetProtection, sheetProtection); var protection = xlWorksheet.Protection; sheetProtection.Sheet = protection.Protected; if (!XLHelper.IsNullOrWhiteSpace(protection.PasswordHash)) sheetProtection.Password = protection.PasswordHash; sheetProtection.FormatCells = GetBooleanValue(!protection.FormatCells, true); sheetProtection.FormatColumns = GetBooleanValue(!protection.FormatColumns, true); sheetProtection.FormatRows = GetBooleanValue(!protection.FormatRows, true); sheetProtection.InsertColumns = GetBooleanValue(!protection.InsertColumns, true); sheetProtection.InsertHyperlinks = GetBooleanValue(!protection.InsertHyperlinks, true); sheetProtection.InsertRows = GetBooleanValue(!protection.InsertRows, true); sheetProtection.DeleteColumns = GetBooleanValue(!protection.DeleteColumns, true); sheetProtection.DeleteRows = GetBooleanValue(!protection.DeleteRows, true); sheetProtection.AutoFilter = GetBooleanValue(!protection.AutoFilter, true); sheetProtection.PivotTables = GetBooleanValue(!protection.PivotTables, true); sheetProtection.Sort = GetBooleanValue(!protection.Sort, true); sheetProtection.SelectLockedCells = GetBooleanValue(!protection.SelectLockedCells, false); sheetProtection.SelectUnlockedCells = GetBooleanValue(!protection.SelectUnlockedCells, false); } else { worksheetPart.Worksheet.RemoveAllChildren<SheetProtection>(); cm.SetElement(XLWSContentManager.XLWSContents.SheetProtection, null); } #endregion #region AutoFilter worksheetPart.Worksheet.RemoveAllChildren<AutoFilter>(); if (xlWorksheet.AutoFilter.Enabled) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.AutoFilter); worksheetPart.Worksheet.InsertAfter(new AutoFilter(), previousElement); var autoFilter = worksheetPart.Worksheet.Elements<AutoFilter>().First(); cm.SetElement(XLWSContentManager.XLWSContents.AutoFilter, autoFilter); PopulateAutoFilter(xlWorksheet.AutoFilter, autoFilter); } else { cm.SetElement(XLWSContentManager.XLWSContents.AutoFilter, null); } #endregion #region MergeCells if ((xlWorksheet).Internals.MergedRanges.Any()) { if (!worksheetPart.Worksheet.Elements<MergeCells>().Any()) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.MergeCells); worksheetPart.Worksheet.InsertAfter(new MergeCells(), previousElement); } var mergeCells = worksheetPart.Worksheet.Elements<MergeCells>().First(); cm.SetElement(XLWSContentManager.XLWSContents.MergeCells, mergeCells); mergeCells.RemoveAllChildren<MergeCell>(); foreach (var mergeCell in (xlWorksheet).Internals.MergedRanges.Select( m => m.RangeAddress.FirstAddress.ToString() + ":" + m.RangeAddress.LastAddress.ToString()).Select( merged => new MergeCell {Reference = merged})) mergeCells.AppendChild(mergeCell); mergeCells.Count = (UInt32)mergeCells.Count(); } else { worksheetPart.Worksheet.RemoveAllChildren<MergeCells>(); cm.SetElement(XLWSContentManager.XLWSContents.MergeCells, null); } #endregion #region Conditional Formatting if (!xlWorksheet.ConditionalFormats.Any()) { worksheetPart.Worksheet.RemoveAllChildren<ConditionalFormatting>(); cm.SetElement(XLWSContentManager.XLWSContents.ConditionalFormatting, null); } else { worksheetPart.Worksheet.RemoveAllChildren<ConditionalFormatting>(); var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.ConditionalFormatting); //if (!worksheetPart.Worksheet.Elements<ConditionalFormatting>().Any()) //{ // var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.ConditionalFormatting); // worksheetPart.Worksheet.InsertAfter(new ConditionalFormatting(), previousElement); //} //var conditionalFormats = worksheetPart.Worksheet.Elements<ConditionalFormatting>().First(); //cm.SetElement(XLWSContentManager.XLWSContents.ConditionalFormatting, conditionalFormats); ////conditionalFormats.RemoveAllChildren<ConditionalFormat>(); var priority = 0; foreach (var cf in xlWorksheet.ConditionalFormats) { priority++; var conditionalFormatting = new ConditionalFormatting { SequenceOfReferences = new ListValue<StringValue> {InnerText = cf.Range.RangeAddress.ToStringRelative(false)} }; conditionalFormatting.Append(XLCFConverters.Convert(cf, priority, context)); worksheetPart.Worksheet.InsertAfter(conditionalFormatting, previousElement); previousElement = conditionalFormatting; cm.SetElement(XLWSContentManager.XLWSContents.ConditionalFormatting, conditionalFormatting); } } #endregion #region DataValidations if (!xlWorksheet.DataValidations.Any(d => d.IsDirty())) { worksheetPart.Worksheet.RemoveAllChildren<DataValidations>(); cm.SetElement(XLWSContentManager.XLWSContents.DataValidations, null); } else { if (!worksheetPart.Worksheet.Elements<DataValidations>().Any()) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.DataValidations); worksheetPart.Worksheet.InsertAfter(new DataValidations(), previousElement); } var dataValidations = worksheetPart.Worksheet.Elements<DataValidations>().First(); cm.SetElement(XLWSContentManager.XLWSContents.DataValidations, dataValidations); dataValidations.RemoveAllChildren<DataValidation>(); foreach (var dv in xlWorksheet.DataValidations) { var sequence = dv.Ranges.Aggregate(String.Empty, (current, r) => current + (r.RangeAddress + " ")); if (sequence.Length > 0) sequence = sequence.Substring(0, sequence.Length - 1); var dataValidation = new DataValidation { AllowBlank = dv.IgnoreBlanks, Formula1 = new Formula1(dv.MinValue), Formula2 = new Formula2(dv.MaxValue), Type = dv.AllowedValues.ToOpenXml(), ShowErrorMessage = dv.ShowErrorMessage, Prompt = dv.InputMessage, PromptTitle = dv.InputTitle, ErrorTitle = dv.ErrorTitle, Error = dv.ErrorMessage, ShowDropDown = !dv.InCellDropdown, ShowInputMessage = dv.ShowInputMessage, ErrorStyle = dv.ErrorStyle.ToOpenXml(), Operator = dv.Operator.ToOpenXml(), SequenceOfReferences = new ListValue<StringValue> {InnerText = sequence} }; dataValidations.AppendChild(dataValidation); } dataValidations.Count = (UInt32)xlWorksheet.DataValidations.Count(); } #endregion #region Hyperlinks var relToRemove = worksheetPart.HyperlinkRelationships.ToList(); relToRemove.ForEach(worksheetPart.DeleteReferenceRelationship); if (!xlWorksheet.Hyperlinks.Any()) { worksheetPart.Worksheet.RemoveAllChildren<Hyperlinks>(); cm.SetElement(XLWSContentManager.XLWSContents.Hyperlinks, null); } else { if (!worksheetPart.Worksheet.Elements<Hyperlinks>().Any()) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.Hyperlinks); worksheetPart.Worksheet.InsertAfter(new Hyperlinks(), previousElement); } var hyperlinks = worksheetPart.Worksheet.Elements<Hyperlinks>().First(); cm.SetElement(XLWSContentManager.XLWSContents.Hyperlinks, hyperlinks); hyperlinks.RemoveAllChildren<Hyperlink>(); foreach (var hl in xlWorksheet.Hyperlinks) { Hyperlink hyperlink; if (hl.IsExternal) { var rId = context.RelIdGenerator.GetNext(RelType.Workbook); hyperlink = new Hyperlink {Reference = hl.Cell.Address.ToString(), Id = rId}; worksheetPart.AddHyperlinkRelationship(hl.ExternalAddress, true, rId); } else { hyperlink = new Hyperlink { Reference = hl.Cell.Address.ToString(), Location = hl.InternalAddress, Display = hl.Cell.GetFormattedString() }; } if (!XLHelper.IsNullOrWhiteSpace(hl.Tooltip)) hyperlink.Tooltip = hl.Tooltip; hyperlinks.AppendChild(hyperlink); } } #endregion #region PrintOptions if (!worksheetPart.Worksheet.Elements<PrintOptions>().Any()) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.PrintOptions); worksheetPart.Worksheet.InsertAfter(new PrintOptions(), previousElement); } var printOptions = worksheetPart.Worksheet.Elements<PrintOptions>().First(); cm.SetElement(XLWSContentManager.XLWSContents.PrintOptions, printOptions); printOptions.HorizontalCentered = xlWorksheet.PageSetup.CenterHorizontally; printOptions.VerticalCentered = xlWorksheet.PageSetup.CenterVertically; printOptions.Headings = xlWorksheet.PageSetup.ShowRowAndColumnHeadings; printOptions.GridLines = xlWorksheet.PageSetup.ShowGridlines; #endregion #region PageMargins if (!worksheetPart.Worksheet.Elements<PageMargins>().Any()) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.PageMargins); worksheetPart.Worksheet.InsertAfter(new PageMargins(), previousElement); } var pageMargins = worksheetPart.Worksheet.Elements<PageMargins>().First(); cm.SetElement(XLWSContentManager.XLWSContents.PageMargins, pageMargins); pageMargins.Left = xlWorksheet.PageSetup.Margins.Left; pageMargins.Right = xlWorksheet.PageSetup.Margins.Right; pageMargins.Top = xlWorksheet.PageSetup.Margins.Top; pageMargins.Bottom = xlWorksheet.PageSetup.Margins.Bottom; pageMargins.Header = xlWorksheet.PageSetup.Margins.Header; pageMargins.Footer = xlWorksheet.PageSetup.Margins.Footer; #endregion #region PageSetup if (!worksheetPart.Worksheet.Elements<PageSetup>().Any()) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.PageSetup); worksheetPart.Worksheet.InsertAfter(new PageSetup(), previousElement); } var pageSetup = worksheetPart.Worksheet.Elements<PageSetup>().First(); cm.SetElement(XLWSContentManager.XLWSContents.PageSetup, pageSetup); pageSetup.Orientation = xlWorksheet.PageSetup.PageOrientation.ToOpenXml(); pageSetup.PaperSize = (UInt32)xlWorksheet.PageSetup.PaperSize; pageSetup.BlackAndWhite = xlWorksheet.PageSetup.BlackAndWhite; pageSetup.Draft = xlWorksheet.PageSetup.DraftQuality; pageSetup.PageOrder = xlWorksheet.PageSetup.PageOrder.ToOpenXml(); pageSetup.CellComments = xlWorksheet.PageSetup.ShowComments.ToOpenXml(); pageSetup.Errors = xlWorksheet.PageSetup.PrintErrorValue.ToOpenXml(); if (xlWorksheet.PageSetup.FirstPageNumber > 0) { pageSetup.FirstPageNumber = (UInt32)xlWorksheet.PageSetup.FirstPageNumber; pageSetup.UseFirstPageNumber = true; } else { pageSetup.FirstPageNumber = null; pageSetup.UseFirstPageNumber = null; } if (xlWorksheet.PageSetup.HorizontalDpi > 0) pageSetup.HorizontalDpi = (UInt32)xlWorksheet.PageSetup.HorizontalDpi; else pageSetup.HorizontalDpi = null; if (xlWorksheet.PageSetup.VerticalDpi > 0) pageSetup.VerticalDpi = (UInt32)xlWorksheet.PageSetup.VerticalDpi; else pageSetup.VerticalDpi = null; if (xlWorksheet.PageSetup.Scale > 0) { pageSetup.Scale = (UInt32)xlWorksheet.PageSetup.Scale; pageSetup.FitToWidth = null; pageSetup.FitToHeight = null; } else { pageSetup.Scale = null; if (xlWorksheet.PageSetup.PagesWide > 0) pageSetup.FitToWidth = (UInt32)xlWorksheet.PageSetup.PagesWide; else pageSetup.FitToWidth = 0; if (xlWorksheet.PageSetup.PagesTall > 0) pageSetup.FitToHeight = (UInt32)xlWorksheet.PageSetup.PagesTall; else pageSetup.FitToHeight = 0; } #endregion #region HeaderFooter var headerFooter = worksheetPart.Worksheet.Elements<HeaderFooter>().FirstOrDefault(); if (headerFooter == null) headerFooter = new HeaderFooter(); else worksheetPart.Worksheet.RemoveAllChildren<HeaderFooter>(); { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.HeaderFooter); worksheetPart.Worksheet.InsertAfter(headerFooter, previousElement); cm.SetElement(XLWSContentManager.XLWSContents.HeaderFooter, headerFooter); } if (((XLHeaderFooter)xlWorksheet.PageSetup.Header).Changed || ((XLHeaderFooter)xlWorksheet.PageSetup.Footer).Changed) { //var headerFooter = worksheetPart.Worksheet.Elements<HeaderFooter>().First(); headerFooter.RemoveAllChildren(); headerFooter.ScaleWithDoc = xlWorksheet.PageSetup.ScaleHFWithDocument; headerFooter.AlignWithMargins = xlWorksheet.PageSetup.AlignHFWithMargins; headerFooter.DifferentFirst = xlWorksheet.PageSetup.DifferentFirstPageOnHF; headerFooter.DifferentOddEven = xlWorksheet.PageSetup.DifferentOddEvenPagesOnHF; var oddHeader = new OddHeader(xlWorksheet.PageSetup.Header.GetText(XLHFOccurrence.OddPages)); headerFooter.AppendChild(oddHeader); var oddFooter = new OddFooter(xlWorksheet.PageSetup.Footer.GetText(XLHFOccurrence.OddPages)); headerFooter.AppendChild(oddFooter); var evenHeader = new EvenHeader(xlWorksheet.PageSetup.Header.GetText(XLHFOccurrence.EvenPages)); headerFooter.AppendChild(evenHeader); var evenFooter = new EvenFooter(xlWorksheet.PageSetup.Footer.GetText(XLHFOccurrence.EvenPages)); headerFooter.AppendChild(evenFooter); var firstHeader = new FirstHeader(xlWorksheet.PageSetup.Header.GetText(XLHFOccurrence.FirstPage)); headerFooter.AppendChild(firstHeader); var firstFooter = new FirstFooter(xlWorksheet.PageSetup.Footer.GetText(XLHFOccurrence.FirstPage)); headerFooter.AppendChild(firstFooter); } #endregion #region RowBreaks if (!worksheetPart.Worksheet.Elements<RowBreaks>().Any()) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.RowBreaks); worksheetPart.Worksheet.InsertAfter(new RowBreaks(), previousElement); } var rowBreaks = worksheetPart.Worksheet.Elements<RowBreaks>().First(); var rowBreakCount = xlWorksheet.PageSetup.RowBreaks.Count; if (rowBreakCount > 0) { rowBreaks.Count = (UInt32)rowBreakCount; rowBreaks.ManualBreakCount = (UInt32)rowBreakCount; var lastRowNum = (UInt32)xlWorksheet.RangeAddress.LastAddress.RowNumber; foreach (var break1 in xlWorksheet.PageSetup.RowBreaks.Select(rb => new Break { Id = (UInt32)rb, Max = lastRowNum, ManualPageBreak = true })) rowBreaks.AppendChild(break1); cm.SetElement(XLWSContentManager.XLWSContents.RowBreaks, rowBreaks); } else { worksheetPart.Worksheet.RemoveAllChildren<RowBreaks>(); cm.SetElement(XLWSContentManager.XLWSContents.RowBreaks, null); } #endregion #region ColumnBreaks if (!worksheetPart.Worksheet.Elements<ColumnBreaks>().Any()) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.ColumnBreaks); worksheetPart.Worksheet.InsertAfter(new ColumnBreaks(), previousElement); } var columnBreaks = worksheetPart.Worksheet.Elements<ColumnBreaks>().First(); var columnBreakCount = xlWorksheet.PageSetup.ColumnBreaks.Count; if (columnBreakCount > 0) { columnBreaks.Count = (UInt32)columnBreakCount; columnBreaks.ManualBreakCount = (UInt32)columnBreakCount; var maxColumnNumber = (UInt32)xlWorksheet.RangeAddress.LastAddress.ColumnNumber; foreach (var break1 in xlWorksheet.PageSetup.ColumnBreaks.Select(cb => new Break { Id = (UInt32)cb, Max = maxColumnNumber, ManualPageBreak = true })) columnBreaks.AppendChild(break1); cm.SetElement(XLWSContentManager.XLWSContents.ColumnBreaks, columnBreaks); } else { worksheetPart.Worksheet.RemoveAllChildren<ColumnBreaks>(); cm.SetElement(XLWSContentManager.XLWSContents.ColumnBreaks, null); } #endregion #region Drawings //worksheetPart.Worksheet.RemoveAllChildren<Drawing>(); //{ // OpenXmlElement previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.Drawing); // worksheetPart.Worksheet.InsertAfter(new Drawing() { Id = String.Format("rId{0}", 1) }, previousElement); //} //Drawing drawing = worksheetPart.Worksheet.Elements<Drawing>().First(); //cm.SetElement(XLWSContentManager.XLWSContents.Drawing, drawing); #endregion #region Tables worksheetPart.Worksheet.RemoveAllChildren<TableParts>(); { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.TableParts); worksheetPart.Worksheet.InsertAfter(new TableParts(), previousElement); } var tableParts = worksheetPart.Worksheet.Elements<TableParts>().First(); cm.SetElement(XLWSContentManager.XLWSContents.TableParts, tableParts); tableParts.Count = (UInt32)xlWorksheet.Tables.Count(); foreach ( var tablePart in from XLTable xlTable in xlWorksheet.Tables select new TablePart {Id = xlTable.RelId}) tableParts.AppendChild(tablePart); #endregion #region LegacyDrawing if (xlWorksheet.LegacyDrawingIsNew) { worksheetPart.Worksheet.RemoveAllChildren<LegacyDrawing>(); { if (!XLHelper.IsNullOrWhiteSpace(xlWorksheet.LegacyDrawingId)) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.LegacyDrawing); worksheetPart.Worksheet.InsertAfter(new LegacyDrawing {Id = xlWorksheet.LegacyDrawingId}, previousElement); } } } #endregion #region LegacyDrawingHeaderFooter //LegacyDrawingHeaderFooter legacyHeaderFooter = worksheetPart.Worksheet.Elements<LegacyDrawingHeaderFooter>().FirstOrDefault(); //if (legacyHeaderFooter != null) //{ // worksheetPart.Worksheet.RemoveAllChildren<LegacyDrawingHeaderFooter>(); // { // var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.LegacyDrawingHeaderFooter); // worksheetPart.Worksheet.InsertAfter(new LegacyDrawingHeaderFooter { Id = xlWorksheet.LegacyDrawingId }, // previousElement); // } //} #endregion }
/// <summary> /// Saves the header and footer information to the worksheet XML /// </summary> protected internal void Save() { // The header/footer elements must appear in this order, if they appear: // <oddHeader /> // <oddFooter /> // <evenHeader /> // <evenFooter /> // <firstHeader /> // <firstFooter /> XmlNode node; if (_header != null) { node = _headerFooterNode.AppendChild(_headerFooterNode.OwnerDocument.CreateElement("oddHeader", ExcelPackage.schemaMain)); node.InnerText = Header.GetHeaderFooterText(); } if (_footer != null) { node = _headerFooterNode.AppendChild(_headerFooterNode.OwnerDocument.CreateElement("oddFooter", ExcelPackage.schemaMain)); node.InnerText = Footer.GetHeaderFooterText(); } // only set evenHeader and evenFooter if (differentOddEven) { if (_evenHeader != null) { node = _headerFooterNode.AppendChild(_headerFooterNode.OwnerDocument.CreateElement("evenHeader", ExcelPackage. schemaMain)); node.InnerText = EvenHeader.GetHeaderFooterText(); } if (_evenFooter != null) { node = _headerFooterNode.AppendChild(_headerFooterNode.OwnerDocument.CreateElement("evenFooter", ExcelPackage. schemaMain)); node.InnerText = EvenFooter.GetHeaderFooterText(); } } // only set firstHeader and firstFooter if (differentFirst) { if (_firstHeader != null) { node = _headerFooterNode.AppendChild(_headerFooterNode.OwnerDocument.CreateElement("firstHeader", ExcelPackage. schemaMain)); node.InnerText = FirstHeader.GetHeaderFooterText(); } if (_firstFooter != null) { node = _headerFooterNode.AppendChild(_headerFooterNode.OwnerDocument.CreateElement("firstFooter", ExcelPackage. schemaMain)); node.InnerText = FirstFooter.GetHeaderFooterText(); } } }