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();
}
}
}
