diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs index 01b546c..4fd603b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs @@ -5,6 +5,13 @@ { public enum XLCellValues { Text, Number, Boolean, DateTime, TimeSpan } + public enum XLClearOptions + { + ContentsAndFormats, + Contents, + Formats + } + public interface IXLCell { /// @@ -103,14 +110,10 @@ TimeSpan GetTimeSpan(); /// - /// Clears the contents of this cell (including styles). + /// Clears the contents of this cell. /// - IXLCell Clear(); - - /// - /// Clears the styles of this cell (preserving number formats). - /// - IXLCell ClearStyles(); + /// Specify what you want to clear. + IXLCell Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); /// /// Deletes the current cell and shifts the surrounding cells according to the shiftDeleteCells parameter. diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCells.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCells.cs index ee0c3d5..5fba63b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCells.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCells.cs @@ -31,14 +31,10 @@ IXLCells SetDataType(XLCellValues dataType); /// - /// Clears the contents of these cells (including styles). + /// Clears the contents of these cells. /// - void Clear(); - - /// - /// Clears the styles of this range (preserving number formats). - /// - void ClearStyles(); + /// Specify what you want to clear. + IXLCells Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); /// /// Sets the cells' formula with A1 references. diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index 620bd4e..7f86b52 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -12,6 +12,7 @@ internal partial class XLCell : IXLCell, IXLStylized { + public Boolean StyleChanged { get; set; } public static readonly DateTime BaseDate = new DateTime(1899, 12, 30); private static Dictionary _formatCodes; @@ -68,14 +69,34 @@ #region Constructor + private Int32 _styleCacheId; public XLCell(XLWorksheet worksheet, XLAddress address, IXLStyle defaultStyle) { Address = address; ShareString = true; - - _style = defaultStyle == null ? new XLStyle(this, worksheet.Style) : new XLStyle(this, defaultStyle); - + _worksheet = worksheet; + + SetStyle(defaultStyle ?? worksheet.Style); + + } + + private IXLStyle GetStyleForRead() + { + return Worksheet.Workbook.GetStyleById(GetStyleId()); + } + public Int32 GetStyleId() + { + if (StyleChanged) + SetStyle(Style); + + return _styleCacheId; + } + private void SetStyle(IXLStyle styleToUse) + { + _styleCacheId = Worksheet.Workbook.GetStyleId(styleToUse); + _style = null; + StyleChanged = false; } #endregion @@ -134,7 +155,7 @@ { _cellValue = value.ToString(); _dataType = XLCellValues.Text; - if (_cellValue.Contains(Environment.NewLine) && !Style.Alignment.WrapText) + if (_cellValue.Contains(Environment.NewLine) && !GetStyleForRead().Alignment.WrapText) Style.Alignment.WrapText = true; } else if (value is TimeSpan) @@ -541,15 +562,22 @@ return null; } + private IXLStyle GetStyle() + { + //return _style ?? (_style = new XLStyle(this, Worksheet.Workbook.GetStyleById(_styleCacheId))); + if (_style != null) + return _style; + + return _style = new XLStyle(this, Worksheet.Workbook.GetStyleById(_styleCacheId)); + } public IXLStyle Style { - get - { - _style = new XLStyle(this, _style); - return _style; - } + get { return GetStyle(); } - set { _style = new XLStyle(this, value); } + set + { + SetStyle(value); + } } public IXLCell SetDataType(XLCellValues dataType) @@ -597,8 +625,8 @@ "Cannot set data type to DateTime because '{0}' is not recognized as a date.", _cellValue)); } - - if (Style.NumberFormat.Format == String.Empty && Style.NumberFormat.NumberFormatId == 0) + var style = GetStyleForRead(); + if (style.NumberFormat.Format == String.Empty && style.NumberFormat.NumberFormatId == 0) { Style.NumberFormat.NumberFormatId = _cellValue.Contains('.') ? 22 : 14; } @@ -609,7 +637,8 @@ if (TimeSpan.TryParse(_cellValue, out tsTest)) { _cellValue = tsTest.ToString(); - if (Style.NumberFormat.Format == String.Empty && Style.NumberFormat.NumberFormatId == 0) + var style = GetStyleForRead(); + if (style.NumberFormat.Format == String.Empty && style.NumberFormat.NumberFormatId == 0) Style.NumberFormat.NumberFormatId = 46; } else @@ -653,17 +682,25 @@ } } - public IXLCell Clear() + public IXLCell Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) { - AsRange().Clear(); + if (clearOptions == XLClearOptions.Contents || clearOptions == XLClearOptions.ContentsAndFormats) + { + Hyperlink = null; + _richText = null; + _cellValue = String.Empty; + FormulaA1 = String.Empty; + } + + if (clearOptions == XLClearOptions.Formats || clearOptions == XLClearOptions.ContentsAndFormats) + { + DataValidation.Clear(); + SetStyle(Worksheet.Style); + } + return this; } - public IXLCell ClearStyles() - { - Style = _worksheet.Style; - return this; - } public void Delete(XLShiftDeletedCells shiftDeleteCells) { @@ -733,20 +770,24 @@ set { - _hyperlink = value; - _hyperlink.Worksheet = _worksheet; - _hyperlink.Cell = this; if (_worksheet.Hyperlinks.Any(hl => Address.Equals(hl.Cell.Address))) _worksheet.Hyperlinks.Delete(Address); + _hyperlink = value; + + if (_hyperlink == null) return; + + _hyperlink.Worksheet = _worksheet; + _hyperlink.Cell = this; + _worksheet.Hyperlinks.Add(_hyperlink); if (SettingHyperlink) return; - if (Style.Font.FontColor.Equals(_worksheet.Style.Font.FontColor)) + if (GetStyleForRead().Font.FontColor.Equals(_worksheet.Style.Font.FontColor)) Style.Font.FontColor = XLColor.FromTheme(XLThemeColor.Hyperlink); - if (Style.Font.Underline == _worksheet.Style.Font.Underline) + if (GetStyleForRead().Font.Underline == _worksheet.Style.Font.Underline) Style.Font.Underline = XLFontUnderlineValues.Single; } } @@ -812,7 +853,8 @@ { if (_richText == null) { - _richText = _cellValue.Length == 0 ? new XLRichText(_style.Font) : new XLRichText(GetFormattedString(), _style.Font); + var style = GetStyleForRead(); + _richText = _cellValue.Length == 0 ? new XLRichText(style.Font) : new XLRichText(GetFormattedString(), style.Font); _dataType = XLCellValues.Text; } @@ -882,24 +924,26 @@ private bool IsDateFormat() { + var style = GetStyleForRead(); return _dataType == XLCellValues.Number - && StringExtensions.IsNullOrWhiteSpace(Style.NumberFormat.Format) - && ((Style.NumberFormat.NumberFormatId >= 14 - && Style.NumberFormat.NumberFormatId <= 22) - || (Style.NumberFormat.NumberFormatId >= 45 - && Style.NumberFormat.NumberFormatId <= 47)); + && StringExtensions.IsNullOrWhiteSpace(style.NumberFormat.Format) + && ((style.NumberFormat.NumberFormatId >= 14 + && style.NumberFormat.NumberFormatId <= 22) + || (style.NumberFormat.NumberFormatId >= 45 + && style.NumberFormat.NumberFormatId <= 47)); } private string GetFormat() { string format; - if (StringExtensions.IsNullOrWhiteSpace(Style.NumberFormat.Format)) + var style = GetStyleForRead(); + if (StringExtensions.IsNullOrWhiteSpace(style.NumberFormat.Format)) { var formatCodes = GetFormatCodes(); - format = formatCodes[Style.NumberFormat.NumberFormatId]; + format = formatCodes[style.NumberFormat.NumberFormatId]; } else - format = Style.NumberFormat.Format; + format = style.NumberFormat.Format; return format; } @@ -993,23 +1037,24 @@ DateTime dtTest; bool bTest; TimeSpan tsTest; - if (_style.NumberFormat.Format == "@") + var style = GetStyleForRead(); + if (style.NumberFormat.Format == "@") { _dataType = XLCellValues.Text; - if (val.Contains(Environment.NewLine) && !Style.Alignment.WrapText) + if (val.Contains(Environment.NewLine) && !style.Alignment.WrapText) Style.Alignment.WrapText = true; } else if (val[0] == '\'') { val = val.Substring(1, val.Length - 1); _dataType = XLCellValues.Text; - if (val.Contains(Environment.NewLine) && !Style.Alignment.WrapText) + if (val.Contains(Environment.NewLine) && !style.Alignment.WrapText) Style.Alignment.WrapText = true; } else if (value is TimeSpan || (TimeSpan.TryParse(val, out tsTest) && !Double.TryParse(val, out dTest))) { _dataType = XLCellValues.TimeSpan; - if (Style.NumberFormat.Format == String.Empty && Style.NumberFormat.NumberFormatId == 0) + if (style.NumberFormat.Format == String.Empty && style.NumberFormat.NumberFormatId == 0) Style.NumberFormat.NumberFormatId = 46; } else if (Double.TryParse(val, out dTest)) @@ -1018,7 +1063,7 @@ { _dataType = XLCellValues.DateTime; - if (Style.NumberFormat.Format == String.Empty && Style.NumberFormat.NumberFormatId == 0) + if (style.NumberFormat.Format == String.Empty && style.NumberFormat.NumberFormatId == 0) { Style.NumberFormat.NumberFormatId = dtTest.Date == dtTest ? 14 : 22; } @@ -1033,7 +1078,7 @@ else { _dataType = XLCellValues.Text; - if (val.Contains(Environment.NewLine) && !Style.Alignment.WrapText) + if (val.Contains(Environment.NewLine) && !style.Alignment.WrapText) Style.Alignment.WrapText = true; } } @@ -1297,7 +1342,7 @@ _dataType = source._dataType; FormulaR1C1 = source.FormulaR1C1; - _style = new XLStyle(this, source._style); + SetStyle(source._style ?? source.Worksheet.Workbook.GetStyleById(source._styleCacheId)); if (source._hyperlink != null) { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs index 8c1d087..330f9c8 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs @@ -8,6 +8,7 @@ internal class XLCells : IXLCells, IXLStylized, IEnumerable { + public Boolean StyleChanged { get; set; } #region Fields private readonly bool _includeFormats; @@ -174,14 +175,11 @@ set { this.ForEach(c => c.DataType = value); } } - public void Clear() - { - this.ForEach(c => c.Clear()); - } - public void ClearStyles() + public IXLCells Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) { - this.ForEach(c => c.ClearStyles()); + this.ForEach(c => c.Clear(clearOptions)); + return this; } public String FormulaA1 diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs index 9f65f5a..5725233 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs @@ -178,5 +178,11 @@ IXLColumn ColumnLeft(Int32 step); IXLColumn ColumnRight(); IXLColumn ColumnRight(Int32 step); + + /// + /// Clears the contents of this column. + /// + /// Specify what you want to clear. + new IXLColumn Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs index d8ca025..017dbfe 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs @@ -113,5 +113,11 @@ IXLColumns AddVerticalPageBreaks(); IXLColumns SetDataType(XLCellValues dataType); + + /// + /// Clears the contents of these columns. + /// + /// Specify what you want to clear. + IXLColumns Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs index ce88c57..aac366d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs @@ -11,7 +11,7 @@ private bool _collapsed; private bool _isHidden; private int _outlineLevel; - private IXLStyle _style; + private Double _width; #endregion @@ -31,7 +31,7 @@ Worksheet.RangeShiftedColumns += WorksheetRangeShiftedColumns; else { - _style = new XLStyle(this, xlColumnParameters.DefaultStyle); + SetStyle(xlColumnParameters.DefaultStyle ?? Worksheet.Style); _width = xlColumnParameters.Worksheet.ColumnWidth; } } @@ -47,11 +47,12 @@ _collapsed = column._collapsed; _isHidden = column._isHidden; _outlineLevel = column._outlineLevel; - _style = new XLStyle(this, column.Style); + SetStyle(column.Style ?? Worksheet.Style); } #endregion + public Boolean IsReference { get; private set; } public override IEnumerable Styles @@ -77,14 +78,16 @@ { get { - return IsReference ? Worksheet.Internals.ColumnsCollection[ColumnNumber()].InnerStyle : new XLStyle(new XLStylizedContainer(_style, this), _style); + return IsReference ? + Worksheet.Internals.ColumnsCollection[ColumnNumber()].InnerStyle : + GetStyle(); } set { if (IsReference) Worksheet.Internals.ColumnsCollection[ColumnNumber()].InnerStyle = value; else - _style = new XLStyle(this, value); + SetStyle(value); } } @@ -132,11 +135,10 @@ } } - public new void Clear() + public new IXLColumn Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) { - var range = AsRange(); - range.Clear(); - Style = Worksheet.Style; + base.Clear(clearOptions); + return this; } public IXLCell Cell(Int32 rowNumber) @@ -162,7 +164,7 @@ { get { - return IsReference ? Worksheet.Internals.ColumnsCollection[ColumnNumber()].Style : _style; + return IsReference ? Worksheet.Internals.ColumnsCollection[ColumnNumber()].Style : GetStyle(); } set { @@ -170,7 +172,7 @@ Worksheet.Internals.ColumnsCollection[ColumnNumber()].Style = value; else { - _style = new XLStyle(this, value); + SetStyle(value); Int32 minRow = 1; Int32 maxRow = 0; @@ -558,7 +560,7 @@ var newColumn = (XLColumn)column; newColumn._width = _width; - newColumn._style = new XLStyle(newColumn, Style); + newColumn.Style = GetStyle(); return newColumn; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs index 13b2ff1..1855dd0 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs @@ -8,6 +8,7 @@ internal class XLColumns : IXLColumns, IXLStylized { + public Boolean StyleChanged { get; set; } private readonly List _columns = new List(); private readonly XLWorksheet _worksheet; internal IXLStyle style; @@ -266,5 +267,11 @@ { _columns.ForEach(c => c.Collapsed = true); } + + public IXLColumns Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) + { + _columns.ForEach(c=>c.Clear(clearOptions)); + return this; + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/IXLDataValidation.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/IXLDataValidation.cs index 6e72cf9..f2072de 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/IXLDataValidation.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/IXLDataValidation.cs @@ -38,6 +38,7 @@ void List(IXLRange range, Boolean inCellDropdown); void Custom(String customValidation); - + void Clear(); + Boolean IsDirty(); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDataValidation.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDataValidation.cs index fdb6c34..47d3378 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDataValidation.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDataValidation.cs @@ -7,12 +7,34 @@ public XLDataValidation(IXLRanges ranges) { Ranges = ranges; + Initialize(); + } + + private void Initialize() + { AllowedValues = XLAllowedValues.AnyValue; IgnoreBlanks = true; ShowErrorMessage = true; ShowInputMessage = true; InCellDropdown = true; + InputTitle = String.Empty; + InputMessage = String.Empty; + ErrorTitle = String.Empty; + ErrorMessage = String.Empty; + ErrorStyle = XLErrorStyle.Stop; Operator = XLOperator.Between; + + } + + public Boolean IsDirty() + { + return + AllowedValues != XLAllowedValues.AnyValue + || (ShowInputMessage && + (!StringExtensions.IsNullOrWhiteSpace(InputTitle) || !StringExtensions.IsNullOrWhiteSpace(InputMessage))) + ||(ShowErrorMessage && + (!StringExtensions.IsNullOrWhiteSpace(ErrorTitle) || !StringExtensions.IsNullOrWhiteSpace(ErrorMessage))); + } public XLDataValidation(IXLDataValidation dataValidation) @@ -143,5 +165,10 @@ MinValue = dataValidation.MinValue; MaxValue = dataValidation.MaxValue; } + + public void Clear() + { + Initialize(); + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Hyperlinks/XLHyperlink_Internal.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Hyperlinks/XLHyperlink_Internal.cs index 0f860db..e533c39 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Hyperlinks/XLHyperlink_Internal.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Hyperlinks/XLHyperlink_Internal.cs @@ -11,8 +11,8 @@ internal XLHyperlink(XLHyperlink hyperlink) { - externalAddress = hyperlink.externalAddress; - internalAddress = hyperlink.internalAddress; + _externalAddress = hyperlink._externalAddress; + _internalAddress = hyperlink._internalAddress; Tooltip = hyperlink.Tooltip; IsExternal = hyperlink.IsExternal; } @@ -22,7 +22,7 @@ Tooltip = tooltip; if (address[0] == '.') { - externalAddress = new Uri(address, UriKind.Relative); + _externalAddress = new Uri(address, UriKind.Relative); IsExternal = true; } else @@ -30,12 +30,12 @@ Uri uri; if(Uri.TryCreate(address, UriKind.Absolute, out uri)) { - externalAddress = new Uri(uri.AbsoluteUri, UriKind.Absolute); + _externalAddress = new Uri(uri.AbsoluteUri, UriKind.Absolute); IsExternal = true; } else { - internalAddress = address; + _internalAddress = address; IsExternal = false; } } @@ -44,21 +44,21 @@ internal void SetValues(Uri uri, String tooltip) { Tooltip = tooltip; - externalAddress = uri; + _externalAddress = uri; IsExternal = true; } internal void SetValues(IXLCell cell, String tooltip) { Tooltip = tooltip; - internalAddress = cell.Address.ToString(); + _internalAddress = cell.Address.ToString(); IsExternal = false; } internal void SetValues(IXLRangeBase range, String tooltip) { Tooltip = tooltip; - internalAddress = range.RangeAddress.ToString(); + _internalAddress = range.RangeAddress.ToString(); IsExternal = false; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Hyperlinks/XLHyperlink_public.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Hyperlinks/XLHyperlink_public.cs index 93e83ae..3f0dab5 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Hyperlinks/XLHyperlink_public.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Hyperlinks/XLHyperlink_public.cs @@ -5,12 +5,14 @@ { public partial class XLHyperlink { - public Boolean IsExternal { get; set; } + private Uri _externalAddress; + private String _internalAddress; public XLHyperlink(String address) { SetValues(address, String.Empty); } + public XLHyperlink(String address, String tooltip) { SetValues(address, tooltip); @@ -20,6 +22,7 @@ { SetValues(cell, String.Empty); } + public XLHyperlink(IXLCell cell, String tooltip) { SetValues(cell, tooltip); @@ -29,6 +32,7 @@ { SetValues(range, String.Empty); } + public XLHyperlink(IXLRangeBase range, String tooltip) { SetValues(range, tooltip); @@ -38,73 +42,61 @@ { SetValues(uri, String.Empty); } + public XLHyperlink(Uri uri, String tooltip) { SetValues(uri, tooltip); } - private Uri externalAddress; - public Uri ExternalAddress + public Boolean IsExternal { get; set; } + + public Uri ExternalAddress { get { - if (IsExternal) - return externalAddress; - else - return null; + return IsExternal ? _externalAddress : null; } set { - externalAddress = value; + _externalAddress = value; IsExternal = true; } } public IXLCell Cell { get; internal set; } - private String internalAddress; public String InternalAddress { get { if (IsExternal) - { return null; - } - else + if (_internalAddress.Contains('!')) { - if (internalAddress.Contains('!')) - { - if (internalAddress[0] != '\'') - return String.Format("'{0}'!{1}", internalAddress.Substring(0, internalAddress.IndexOf('!')), internalAddress.Substring(internalAddress.IndexOf('!') + 1)); - else - return internalAddress; - } - else - { - return String.Format("'{0}'!{1}", Worksheet.Name, internalAddress); - } + return _internalAddress[0] != '\'' + ? String.Format("'{0}'!{1}", _internalAddress.Substring(0, _internalAddress.IndexOf('!')), + _internalAddress.Substring(_internalAddress.IndexOf('!') + 1)) + : _internalAddress; } + return String.Format("'{0}'!{1}", Worksheet.Name, _internalAddress); } set { - internalAddress = value; + _internalAddress = value; IsExternal = false; } } public String Tooltip { get; set; } + public void Delete() { - if (Cell != null) - { - Worksheet.Hyperlinks.Delete(Cell.Address); - if (Cell.Style.Font.FontColor.Equals(XLColor.FromTheme(XLThemeColor.Hyperlink))) - Cell.Style.Font.FontColor = Worksheet.Style.Font.FontColor; + if (Cell == null) return; + Worksheet.Hyperlinks.Delete(Cell.Address); + if (Cell.Style.Font.FontColor.Equals(XLColor.FromTheme(XLThemeColor.Hyperlink))) + Cell.Style.Font.FontColor = Worksheet.Style.Font.FontColor; - if (Cell.Style.Font.Underline != Worksheet.Style.Font.Underline) - Cell.Style.Font.Underline = Worksheet.Style.Font.Underline; - } + Cell.Style.Font.Underline = Worksheet.Style.Font.Underline; } } -} +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs index 3733011..726b951 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs @@ -242,7 +242,12 @@ IXLRange Sort(XLSortOrientation sortOrientation, String elementsToSortBy, Boolean matchCase); IXLRange SetDataType(XLCellValues dataType); - + + /// + /// Clears the contents of this range. + /// + /// Specify what you want to clear. + new IXLRange Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs index f976f56..a174fef 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -197,14 +197,10 @@ IXLRange AddToNamed(String rangeName, XLScope scope, String comment); /// - /// Clears the contents of this range (including styles). + /// Clears the contents of this range. /// - void Clear(); - - /// - /// Clears the styles of this range (preserving number formats). - /// - void ClearStyles(); + /// Specify what you want to clear. + IXLRangeBase Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); IXLRangeBase SetValue(T value); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs index 7951260..c60a326 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs @@ -103,6 +103,12 @@ IXLTable AsTable(String name); IXLTable CreateTable(); IXLTable CreateTable(String name); + + /// + /// Clears the contents of this column. + /// + /// Specify what you want to clear. + new IXLRangeColumn Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs index 4a9e5ef..ea05fd0 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs @@ -5,10 +5,6 @@ { public interface IXLRangeColumns: IEnumerable { - /// - /// Clears the contents of the columns (including styles). - /// - void Clear(); /// /// Adds a column range to this group. @@ -41,5 +37,10 @@ IXLRangeColumns SetDataType(XLCellValues dataType); + /// + /// Clears the contents of these columns. + /// + /// Specify what you want to clear. + IXLRangeColumns Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs index 8793a32..ac4592f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs @@ -106,6 +106,12 @@ IXLRangeRow RowBelow(Int32 step); IXLRow WorksheetRow(); + + /// + /// Clears the contents of this row. + /// + /// Specify what you want to clear. + new IXLRangeRow Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs index 2b7cb89..e9b1247 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs @@ -10,10 +10,6 @@ /// /// The row range to add. void Add(IXLRangeRow rowRange); - /// - /// Clears the contents of the rows (including styles). - /// - void Clear(); /// /// Returns the collection of cells. @@ -39,5 +35,11 @@ IXLStyle Style { get; set; } IXLRangeRows SetDataType(XLCellValues dataType); + + /// + /// Clears the contents of these rows. + /// + /// Specify what you want to clear. + IXLRangeRows Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRanges.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRanges.cs index e875fdb..5aa181a 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRanges.cs @@ -6,10 +6,6 @@ public interface IXLRanges: IEnumerable { /// - /// Clears the contents of the ranges (including styles). - /// - void Clear(); - /// /// Adds the specified range to this group. /// /// The range to add to this group. @@ -44,6 +40,7 @@ /// If the named range exists, it will add these ranges to that named range. /// Name of the range. /// The scope for the named range. + /// IXLRanges AddToNamed(String rangeName, XLScope scope); /// @@ -52,6 +49,7 @@ /// Name of the range. /// The scope for the named range. /// The comments for the named range. + /// IXLRanges AddToNamed(String rangeName, XLScope scope, String comment); /// @@ -85,5 +83,11 @@ IXLCells CellsUsed(Boolean includeStyles); IXLRanges SetDataType(XLCellValues dataType); + + /// + /// Clears the contents of these ranges. + /// + /// Specify what you want to clear. + IXLRanges Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs index 8fd7ccc..97f4d48 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs @@ -26,7 +26,7 @@ Worksheet.RangeShiftedColumns += WorksheetRangeShiftedColumns; xlRangeParameters.IgnoreEvents = true; } - DefaultStyle = new XLStyle(this, xlRangeParameters.DefaultStyle); + SetStyle(xlRangeParameters.DefaultStyle); } #endregion @@ -937,9 +937,9 @@ if (transposeOption == XLTransposeOptions.MoveCells) { if (rowCount > columnCount) - InsertColumnsAfter(rowCount - columnCount, false); + InsertColumnsAfter(false, rowCount - columnCount, false); else if (columnCount > rowCount) - InsertRowsBelow(columnCount - rowCount, false); + InsertRowsBelow(false, columnCount - rowCount, false); } else { @@ -979,6 +979,10 @@ ^ Worksheet.GetHashCode(); } - + public new IXLRange Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) + { + base.Clear(clearOptions); + return this; + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index 686db30..fe050f2 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -2,16 +2,37 @@ using System.Collections.Generic; using System.Linq; + namespace ClosedXML.Excel { internal abstract class XLRangeBase : IXLRangeBase, IXLStylized { + public Boolean StyleChanged { get; set; } #region Fields - protected IXLStyle DefaultStyle; + private IXLStyle _style; #endregion + private Int32 _styleCacheId; + protected void SetStyle(IXLStyle styleToUse) + { + _styleCacheId = Worksheet.Workbook.GetStyleId(styleToUse); + _style = null; + StyleChanged = false; + } + public Int32 GetStyleId() + { + if (StyleChanged) + SetStyle(Style); + + return _styleCacheId; + } + protected IXLStyle GetStyle() + { + return _style ?? (_style = new XLStyle(this, Worksheet.Workbook.GetStyleById(_styleCacheId))); + } + #region Constructor protected XLRangeBase(XLRangeAddress rangeAddress) @@ -261,31 +282,36 @@ return AsRange(); } - public void Clear() + public IXLRangeBase Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) { - // Remove cells inside range - Worksheet.Internals.CellsCollection.RemoveAll( - RangeAddress.FirstAddress.RowNumber, - RangeAddress.FirstAddress.ColumnNumber, - RangeAddress.LastAddress.RowNumber, - RangeAddress.LastAddress.ColumnNumber - ); - - ClearMerged(); - - var hyperlinksToRemove = Worksheet.Hyperlinks.Where(hl => Contains(hl.Cell.AsRange())).ToList(); - hyperlinksToRemove.ForEach(hl => Worksheet.Hyperlinks.Delete(hl)); - } - - public void ClearStyles() - { - foreach (XLCell cell in CellsUsed(true)) + var includeFormats = clearOptions == XLClearOptions.Formats || + clearOptions == XLClearOptions.ContentsAndFormats; + foreach (var cell in CellsUsed(includeFormats)) { - var newStyle = new XLStyle(cell, Worksheet.Style) {NumberFormat = cell.Style.NumberFormat}; - cell.Style = newStyle; + cell.Clear(clearOptions); } + + if (includeFormats) + { + ClearMerged(); + + var hyperlinksToRemove = Worksheet.Hyperlinks.Where(hl => Contains(hl.Cell.AsRange())).ToList(); + hyperlinksToRemove.ForEach(hl => Worksheet.Hyperlinks.Delete(hl)); + } + + if (clearOptions == XLClearOptions.ContentsAndFormats) + { + Worksheet.Internals.CellsCollection.RemoveAll( + RangeAddress.FirstAddress.RowNumber, + RangeAddress.FirstAddress.ColumnNumber, + RangeAddress.LastAddress.RowNumber, + RangeAddress.LastAddress.ColumnNumber + ); + } + return this; } + public bool Contains(String rangeAddress) { string addressToUse = rangeAddress.Contains("!") @@ -335,7 +361,7 @@ public virtual IXLStyle Style { - get { return DefaultStyle; } + get { return GetStyle(); } set { Cells().ForEach(c => c.Style = value); } } @@ -413,8 +439,8 @@ public virtual IXLStyle InnerStyle { - get { return DefaultStyle; } - set { DefaultStyle = new XLStyle(this, value); } + get { return GetStyle(); } + set { SetStyle(value); } } #endregion @@ -676,7 +702,7 @@ return retVal; } - public IXLRangeColumns InsertColumnsAfter(Boolean onlyUsedCells, Int32 numberOfColumns) + public IXLRangeColumns InsertColumnsAfter(Boolean onlyUsedCells, Int32 numberOfColumns, Boolean formatFromLeft = true) { int columnCount = ColumnCount(); int firstColumn = RangeAddress.FirstAddress.ColumnNumber + columnCount; @@ -692,7 +718,7 @@ lastRow = ExcelHelper.MaxRowNumber; var newRange = Worksheet.Range(firstRow, firstColumn, lastRow, lastColumn); - return newRange.InsertColumnsBefore(onlyUsedCells, numberOfColumns); + return newRange.InsertColumnsBefore(onlyUsedCells, numberOfColumns, formatFromLeft); } public IXLRangeColumns InsertColumnsBefore(Int32 numberOfColumns) @@ -721,7 +747,7 @@ return retVal; } - public IXLRangeColumns InsertColumnsBefore(Boolean onlyUsedCells, Int32 numberOfColumns) + public IXLRangeColumns InsertColumnsBefore(Boolean onlyUsedCells, Int32 numberOfColumns, Boolean formatFromLeft = true) { foreach (XLWorksheet ws in Worksheet.Workbook.WorksheetsInternal) { @@ -734,7 +760,7 @@ var cellsToInsert = new Dictionary(); var cellsToDelete = new List(); - var cellsToBlank = new List(); + //var cellsToBlank = new List(); int firstColumn = RangeAddress.FirstAddress.ColumnNumber; int firstRow = RangeAddress.FirstAddress.RowNumber; int lastRow = RangeAddress.FirstAddress.RowNumber + RowCount() - 1; @@ -759,8 +785,8 @@ newCell.FormulaA1 = oldCell.FormulaA1; cellsToInsert.Add(newKey, newCell); cellsToDelete.Add(oldKey); - if (oldKey.ColumnNumber < firstColumn + numberOfColumns) - cellsToBlank.Add(oldKey); + //if (oldKey.ColumnNumber < firstColumn + numberOfColumns) + // cellsToBlank.Add(oldKey); } } } @@ -779,28 +805,58 @@ newCell.FormulaA1 = c.FormulaA1; cellsToInsert.Add(newKey, newCell); cellsToDelete.Add(c.Address); - if (c.Address.ColumnNumber < firstColumn + numberOfColumns) - cellsToBlank.Add(c.Address); + //if (c.Address.ColumnNumber < firstColumn + numberOfColumns) + // cellsToBlank.Add(c.Address); } } cellsToDelete.ForEach(c => Worksheet.Internals.CellsCollection.Remove(c.RowNumber, c.ColumnNumber)); cellsToInsert.ForEach( c => Worksheet.Internals.CellsCollection.Add(c.Key.RowNumber, c.Key.ColumnNumber, c.Value)); - foreach (IXLAddress c in cellsToBlank) - { - var styleToUse = Worksheet.Internals.RowsCollection.ContainsKey(c.RowNumber) - ? Worksheet.Internals.RowsCollection[c.RowNumber].Style - : Worksheet.Style; - Worksheet.Cell(c.RowNumber, c.ColumnNumber).Style = styleToUse; - } Worksheet.NotifyRangeShiftedColumns((XLRange)AsRange(), numberOfColumns); - return Worksheet.Range( + var rangeToReturn = Worksheet.Range( RangeAddress.FirstAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber - numberOfColumns, RangeAddress.LastAddress.RowNumber, RangeAddress.LastAddress.ColumnNumber - numberOfColumns - ).Columns(); + ); + + if (formatFromLeft && rangeToReturn.RangeAddress.FirstAddress.ColumnNumber > 1) + { + var model = rangeToReturn.FirstColumn().ColumnLeft(); + var modelFirstRow = model.FirstCellUsed(true); + var modelLastRow = model.LastCellUsed(true); + if (modelLastRow != null) + { + Int32 firstRoReturned = modelFirstRow.Address.RowNumber + - model.RangeAddress.FirstAddress.RowNumber + 1; + Int32 lastRoReturned = modelLastRow.Address.RowNumber + - model.RangeAddress.FirstAddress.RowNumber + 1; + for (Int32 ro = firstRoReturned; ro <= lastRoReturned; ro++) + { + rangeToReturn.Row(ro).Style = model.Cell(ro).Style; + } + } + } + else + { + var lastRoUsed = rangeToReturn.LastRowUsed(true); + if (lastRoUsed != null) + { + Int32 lastRoReturned = lastRoUsed.RowNumber(); + for (Int32 ro = 1; ro <= lastRoReturned; ro++) + { + var styleToUse = Worksheet.Internals.RowsCollection.ContainsKey(ro) + ? Worksheet.Internals.RowsCollection[ro].Style + : Worksheet.Style; + rangeToReturn.Row(ro).Style = styleToUse; + } + + } + } + + + return rangeToReturn.Columns(); } public IXLRangeRows InsertRowsBelow(Int32 numberOfRows) @@ -829,7 +885,7 @@ return retVal; } - public IXLRangeRows InsertRowsBelow(Boolean onlyUsedCells, Int32 numberOfRows) + public IXLRangeRows InsertRowsBelow(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove = true) { int rowCount = RowCount(); int firstRow = RangeAddress.FirstAddress.RowNumber + rowCount; @@ -845,7 +901,7 @@ lastColumn = ExcelHelper.MaxColumnNumber; var newRange = Worksheet.Range(firstRow, firstColumn, lastRow, lastColumn); - return newRange.InsertRowsAbove(onlyUsedCells, numberOfRows); + return newRange.InsertRowsAbove(onlyUsedCells, numberOfRows, formatFromAbove); } public IXLRangeRows InsertRowsAbove(Int32 numberOfRows) @@ -874,7 +930,7 @@ return retVal; } - public IXLRangeRows InsertRowsAbove(Boolean onlyUsedCells, Int32 numberOfRows) + public IXLRangeRows InsertRowsAbove(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove = true) { foreach (XLWorksheet ws in Worksheet.Workbook.WorksheetsInternal) { @@ -887,7 +943,7 @@ var cellsToInsert = new Dictionary(); var cellsToDelete = new List(); - var cellsToBlank = new List(); + //var cellsToBlank = new List(); int firstRow = RangeAddress.FirstAddress.RowNumber; int firstColumn = RangeAddress.FirstAddress.ColumnNumber; int lastColumn = RangeAddress.FirstAddress.ColumnNumber + ColumnCount() - 1; @@ -912,8 +968,8 @@ newCell.FormulaA1 = oldCell.FormulaA1; cellsToInsert.Add(newKey, newCell); cellsToDelete.Add(oldKey); - if (oldKey.RowNumber < firstRow + numberOfRows) - cellsToBlank.Add(oldKey); + //if (oldKey.RowNumber < firstRow + numberOfRows) + // cellsToBlank.Add(oldKey); } } } @@ -933,27 +989,65 @@ newCell.FormulaA1 = c.FormulaA1; cellsToInsert.Add(newKey, newCell); cellsToDelete.Add(c.Address); - if (c.Address.RowNumber < firstRow + numberOfRows) - cellsToBlank.Add(c.Address); + //if (c.Address.RowNumber < firstRow + numberOfRows) + // cellsToBlank.Add(c.Address); } } cellsToDelete.ForEach(c => Worksheet.Internals.CellsCollection.Remove(c.RowNumber, c.ColumnNumber)); cellsToInsert.ForEach( c => Worksheet.Internals.CellsCollection.Add(c.Key.RowNumber, c.Key.ColumnNumber, c.Value)); - foreach (IXLAddress c in cellsToBlank) - { - var styleToUse = Worksheet.Internals.ColumnsCollection.ContainsKey(c.ColumnNumber) - ? Worksheet.Internals.ColumnsCollection[c.ColumnNumber].Style - : Worksheet.Style; - Worksheet.Cell(c.RowNumber, c.ColumnNumber).Style = styleToUse; - } + + //foreach (IXLAddress c in cellsToBlank) + //{ + // IXLStyle styleToUse; + + // styleToUse = Worksheet.Internals.ColumnsCollection.ContainsKey(c.ColumnNumber) + // ? Worksheet.Internals.ColumnsCollection[c.ColumnNumber].Style + // : Worksheet.Style; + // Worksheet.Cell(c.RowNumber, c.ColumnNumber).Style = styleToUse; + //} Worksheet.NotifyRangeShiftedRows((XLRange)AsRange(), numberOfRows); - return Worksheet.Range( + var rangeToReturn = Worksheet.Range( RangeAddress.FirstAddress.RowNumber - numberOfRows, RangeAddress.FirstAddress.ColumnNumber, RangeAddress.LastAddress.RowNumber - numberOfRows, RangeAddress.LastAddress.ColumnNumber - ).Rows(); + ); + + if (formatFromAbove && rangeToReturn.RangeAddress.FirstAddress.RowNumber > 1) + { + var model = rangeToReturn.FirstRow().RowAbove(); + var modelFirstColumn = model.FirstCellUsed(true); + var modelLastColumn = model.LastCellUsed(true); + if (modelLastColumn != null) + { + Int32 firstCoReturned = modelFirstColumn.Address.ColumnNumber + - model.RangeAddress.FirstAddress.ColumnNumber + 1; + Int32 lastCoReturned = modelLastColumn.Address.ColumnNumber + - model.RangeAddress.FirstAddress.ColumnNumber + 1; + for (Int32 co = firstCoReturned; co <= lastCoReturned; co++) + { + rangeToReturn.Column(co).Style = model.Cell(co).Style; + } + } + } + else + { + var lastCoUsed = rangeToReturn.LastColumnUsed(true); + if (lastCoUsed != null) + { + Int32 lastCoReturned = lastCoUsed.ColumnNumber(); + for (Int32 co = 1; co <= lastCoReturned; co++) + { + var styleToUse = Worksheet.Internals.ColumnsCollection.ContainsKey(co) + ? Worksheet.Internals.ColumnsCollection[co].Style + : Worksheet.Style; + rangeToReturn.Column(co).Style = styleToUse; + } + } + } + + return rangeToReturn.Rows(); } private void ClearMerged() diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs index 482daba..b245522 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs @@ -14,7 +14,7 @@ Worksheet.RangeShiftedRows += WorksheetRangeShiftedRows; Worksheet.RangeShiftedColumns += WorksheetRangeShiftedColumns; - DefaultStyle = new XLStyle(this, rangeParameters.DefaultStyle); + SetStyle(rangeParameters.DefaultStyle); } #endregion @@ -351,5 +351,11 @@ { return AsRange().CreateTable(name); } + + public new IXLRangeColumn Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) + { + base.Clear(clearOptions); + return this; + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs index f8cd33e..cd9fd51 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs @@ -8,6 +8,7 @@ internal class XLRangeColumns : IXLRangeColumns, IXLStylized { + public Boolean StyleChanged { get; set; } private readonly List _ranges = new List(); private IXLStyle _style; @@ -18,9 +19,10 @@ #region IXLRangeColumns Members - public void Clear() + public IXLRangeColumns Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) { - _ranges.ForEach(r => r.Clear()); + _ranges.ForEach(c => c.Clear(clearOptions)); + return this; } public void Delete() diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs index 7706021..a703a95 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs @@ -14,7 +14,7 @@ if (quickLoad) return; Worksheet.RangeShiftedRows += WorksheetRangeShiftedRows; Worksheet.RangeShiftedColumns += WorksheetRangeShiftedColumns; - DefaultStyle = new XLStyle(this, rangeParameters.DefaultStyle); + SetStyle(rangeParameters.DefaultStyle); } #endregion @@ -336,5 +336,11 @@ } #endregion + + public new IXLRangeRow Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) + { + base.Clear(clearOptions); + return this; + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs index 24af38b..49b7cf0 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs @@ -8,6 +8,7 @@ internal class XLRangeRows : IXLRangeRows, IXLStylized { + public Boolean StyleChanged { get; set; } private readonly List _ranges = new List(); private IXLStyle _style; @@ -18,9 +19,10 @@ #region IXLRangeRows Members - public void Clear() + public IXLRangeRows Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) { - _ranges.ForEach(r => r.Clear()); + _ranges.ForEach(c => c.Clear(clearOptions)); + return this; } public void Delete() diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs index ba0ab10..dc8374e 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs @@ -8,6 +8,7 @@ internal class XLRanges : IXLRanges, IXLStylized { + public Boolean StyleChanged { get; set; } private readonly List _ranges = new List(); private Int32 _count; private IXLStyle _style; @@ -19,9 +20,10 @@ #region IXLRanges Members - public void Clear() + public IXLRanges Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) { - _ranges.ForEach(r => r.Clear()); + _ranges.ForEach(c => c.Clear(clearOptions)); + return this; } public void Add(IXLRangeBase range) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs index 81cdef2..69bd602 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs @@ -181,5 +181,11 @@ IXLRow RowAbove(Int32 step); IXLRow RowBelow(); IXLRow RowBelow(Int32 step); + + /// + /// Clears the contents of this row. + /// + /// Specify what you want to clear. + new IXLRow Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRows.cs index 1f10648..0f695bf 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRows.cs @@ -113,5 +113,11 @@ IXLRows AddHorizontalPageBreaks(); IXLRows SetDataType(XLCellValues dataType); + + /// + /// Clears the contents of these rows. + /// + /// Specify what you want to clear. + IXLRows Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs index 7163ac9..8cb19db 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs @@ -12,7 +12,7 @@ private Double _height; private Boolean _isHidden; private Int32 _outlineLevel; - private IXLStyle _style; + #endregion @@ -33,7 +33,8 @@ } else { - _style = new XLStyle(this, xlRowParameters.DefaultStyle); + //_style = new XLStyle(this, xlRowParameters.DefaultStyle); + SetStyle(xlRowParameters.DefaultStyle ?? Worksheet.Style); _height = xlRowParameters.Worksheet.RowHeight; } } @@ -48,7 +49,7 @@ _collapsed = row._collapsed; _isHidden = row._isHidden; _outlineLevel = row._outlineLevel; - _style = new XLStyle(this, row.Style); + SetStyle(row.Style ?? Worksheet.Style); } #endregion @@ -80,14 +81,14 @@ { return IsReference ? Worksheet.Internals.RowsCollection[RowNumber()].InnerStyle - : new XLStyle(new XLStylizedContainer(_style, this), _style); + : GetStyle(); } set { if (IsReference) Worksheet.Internals.RowsCollection[RowNumber()].InnerStyle = value; else - _style = new XLStyle(this, value); + SetStyle(value); } } @@ -151,11 +152,10 @@ return Worksheet.Rows(rowNum, rowNum + numberOfRows - 1); } - public new void Clear() + public new IXLRow Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) { - var range = AsRange(); - range.Clear(); - Style = Worksheet.Style; + base.Clear(clearOptions); + return this; } public IXLCell Cell(Int32 columnNumber) @@ -312,14 +312,14 @@ public override IXLStyle Style { - get { return IsReference ? Worksheet.Internals.RowsCollection[RowNumber()].Style : _style; } + get { return IsReference ? Worksheet.Internals.RowsCollection[RowNumber()].Style : GetStyle(); } set { if (IsReference) Worksheet.Internals.RowsCollection[RowNumber()].Style = value; else { - _style = new XLStyle(this, value); + SetStyle(value); Int32 minColumn = 1; Int32 maxColumn = 0; @@ -470,7 +470,9 @@ var newRow = (XLRow)row; newRow._height = _height; - newRow._style = new XLStyle(newRow, Style); + //newRow._style = new XLStyle(newRow, Style); + newRow.Style = GetStyle(); + return newRow; } @@ -560,7 +562,7 @@ Worksheet.Internals.RowsCollection[RowNumber()].SetStyleNoColumns(value); else { - _style = new XLStyle(this, value); + SetStyle(value); int row = RowNumber(); foreach (XLCell c in Worksheet.Internals.CellsCollection.GetCellsInRow(row)) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs index 56f2b3f..e27a222 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs @@ -8,6 +8,7 @@ internal class XLRows : IXLRows, IXLStylized { + public Boolean StyleChanged { get; set; } private readonly List _rows = new List(); private readonly XLWorksheet _worksheet; internal IXLStyle style; @@ -257,5 +258,11 @@ { _rows.Add(row); } + + public IXLRows Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) + { + _rows.ForEach(c => c.Clear(clearOptions)); + return this; + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLStylized.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLStylized.cs index 91d973b..45d1287 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLStylized.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLStylized.cs @@ -10,6 +10,7 @@ Boolean UpdatingStyle { get; set; } IXLStyle InnerStyle { get; set; } IXLRanges RangesUsed { get; } + Boolean StyleChanged { get; set; } //Boolean IsDefault { get; set; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLAlignment.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLAlignment.cs index 9e83f55..2efb995 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLAlignment.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLAlignment.cs @@ -27,18 +27,17 @@ public XLAlignment(IXLStylized container, IXLAlignment d = null) { _container = container; - if (d != null) - { - _horizontal = d.Horizontal; - _vertical = d.Vertical; - _indent = d.Indent; - _justifyLastLine = d.JustifyLastLine; - _readingOrder = d.ReadingOrder; - _relativeIndent = d.RelativeIndent; - _shrinkToFit = d.ShrinkToFit; - _textRotation = d.TextRotation; - _wrapText = d.WrapText; - } + if (d == null) return; + + _horizontal = d.Horizontal; + _vertical = d.Vertical; + _indent = d.Indent; + _justifyLastLine = d.JustifyLastLine; + _readingOrder = d.ReadingOrder; + _relativeIndent = d.RelativeIndent; + _shrinkToFit = d.ShrinkToFit; + _textRotation = d.TextRotation; + _wrapText = d.WrapText; } #region IXLAlignment Members @@ -48,6 +47,7 @@ get { return _horizontal; } set { + SetStyleChanged(); Boolean updateIndent = !( value == XLAlignmentHorizontalValues.Left || value == XLAlignmentHorizontalValues.Right @@ -59,8 +59,7 @@ _container.Styles.ForEach(s => { s.Alignment.Horizontal = value; - if (updateIndent) - s.Alignment.Indent = 0; + if (updateIndent) s.Alignment.Indent = 0; }); } else @@ -77,6 +76,7 @@ get { return _vertical; } set { + SetStyleChanged(); if (_container != null && !_container.UpdatingStyle) _container.Styles.ForEach(s => s.Alignment.Vertical = value); else @@ -89,24 +89,28 @@ get { return _indent; } set { + SetStyleChanged(); if (_container != null && !_container.UpdatingStyle) - { _container.Styles.ForEach(s => s.Alignment.Indent = value); - } else { - if (_horizontal == XLAlignmentHorizontalValues.General) - _horizontal = XLAlignmentHorizontalValues.Left; + if (_indent != value) + { + if (_horizontal == XLAlignmentHorizontalValues.General) + _horizontal = XLAlignmentHorizontalValues.Left; - if (value > 0 && !( - _horizontal == XLAlignmentHorizontalValues.Left - || _horizontal == XLAlignmentHorizontalValues.Right - || _horizontal == XLAlignmentHorizontalValues.Distributed - )) - throw new ArgumentException( - "For indents, only left, right, and distributed horizontal alignments are supported."); + if (value > 0 && !( + _horizontal == XLAlignmentHorizontalValues.Left + || _horizontal == XLAlignmentHorizontalValues.Right + || _horizontal == XLAlignmentHorizontalValues.Distributed + )) + { + throw new ArgumentException( + "For indents, only left, right, and distributed horizontal alignments are supported."); + } - _indent = value; + _indent = value; + } } } } @@ -116,6 +120,7 @@ get { return _justifyLastLine; } set { + SetStyleChanged(); if (_container != null && !_container.UpdatingStyle) _container.Styles.ForEach(s => s.Alignment.JustifyLastLine = value); else @@ -128,6 +133,7 @@ get { return _readingOrder; } set { + SetStyleChanged(); if (_container != null && !_container.UpdatingStyle) _container.Styles.ForEach(s => s.Alignment.ReadingOrder = value); else @@ -140,6 +146,7 @@ get { return _relativeIndent; } set { + SetStyleChanged(); if (_container != null && !_container.UpdatingStyle) _container.Styles.ForEach(s => s.Alignment.RelativeIndent = value); else @@ -152,6 +159,7 @@ get { return _shrinkToFit; } set { + SetStyleChanged(); if (_container != null && !_container.UpdatingStyle) _container.Styles.ForEach(s => s.Alignment.ShrinkToFit = value); else @@ -164,13 +172,14 @@ get { return _textRotation; } set { + SetStyleChanged(); Int32 rotation = value; if (rotation != 255 && (rotation < -90 || rotation > 180)) throw new ArgumentException("TextRotation must be between -90 and 180 degrees, or 255."); if (rotation < 0) - rotation = 90 + (rotation*-1); + rotation = 90 + (rotation * -1); if (_container != null && !_container.UpdatingStyle) _container.Styles.ForEach(s => s.Alignment.TextRotation = rotation); @@ -184,6 +193,7 @@ get { return _wrapText; } set { + SetStyleChanged(); if (_container != null && !_container.UpdatingStyle) _container.Styles.ForEach(s => s.Alignment.WrapText = value); else @@ -196,6 +206,7 @@ get { return _textRotation == 255; } set { + SetStyleChanged(); if (_container != null && !_container.UpdatingStyle) _container.Styles.ForEach(s => s.Alignment.TextRotation = value ? 255 : 0); else @@ -312,6 +323,11 @@ #endregion + private void SetStyleChanged() + { + if (_container != null) _container.StyleChanged = true; + } + public override string ToString() { var sb = new StringBuilder(); @@ -338,16 +354,16 @@ public override bool Equals(object obj) { - return Equals((XLAlignment) obj); + return Equals((XLAlignment)obj); } public override int GetHashCode() { - return (Int32) Horizontal - ^ (Int32) Vertical + return (Int32)Horizontal + ^ (Int32)Vertical ^ Indent ^ JustifyLastLine.GetHashCode() - ^ (Int32) ReadingOrder + ^ (Int32)ReadingOrder ^ RelativeIndent ^ ShrinkToFit.GetHashCode() ^ TextRotation diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLBorder.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLBorder.cs index b90a516..839b9e0 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLBorder.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLBorder.cs @@ -1,46 +1,61 @@ using System; using System.Text; + namespace ClosedXML.Excel { internal class XLBorder : IXLBorder { - IXLStylized container; + private readonly IXLStylized _container; + private XLBorderStyleValues _bottomBorder; + private IXLColor _bottomBorderColor; + private XLBorderStyleValues _diagonalBorder; + private IXLColor _diagonalBorderColor; + private Boolean _diagonalDown; + private Boolean _diagonalUp; + private XLBorderStyleValues _leftBorder; + private IXLColor _leftBorderColor; + private XLBorderStyleValues _rightBorder; + private IXLColor _rightBorderColor; + private XLBorderStyleValues _topBorder; + private IXLColor _topBorderColor; - public XLBorder() : this(null, XLWorkbook.DefaultStyle.Border) { } + public XLBorder() : this(null, XLWorkbook.DefaultStyle.Border) + { + } public XLBorder(IXLStylized container, IXLBorder defaultBorder) { - this.container = container; - if (defaultBorder != null) - { - leftBorder = defaultBorder.LeftBorder; - leftBorderColor = new XLColor(defaultBorder.LeftBorderColor); - rightBorder = defaultBorder.RightBorder; - rightBorderColor = new XLColor(defaultBorder.RightBorderColor); - topBorder = defaultBorder.TopBorder; - topBorderColor = new XLColor(defaultBorder.TopBorderColor); - bottomBorder = defaultBorder.BottomBorder; - bottomBorderColor = new XLColor(defaultBorder.BottomBorderColor); - diagonalBorder = defaultBorder.DiagonalBorder; - diagonalBorderColor = new XLColor(defaultBorder.DiagonalBorderColor); - diagonalUp = defaultBorder.DiagonalUp; - diagonalDown = defaultBorder.DiagonalDown; - } + _container = container; + if (defaultBorder == null) return; + + _leftBorder = defaultBorder.LeftBorder; + _leftBorderColor = new XLColor(defaultBorder.LeftBorderColor); + _rightBorder = defaultBorder.RightBorder; + _rightBorderColor = new XLColor(defaultBorder.RightBorderColor); + _topBorder = defaultBorder.TopBorder; + _topBorderColor = new XLColor(defaultBorder.TopBorderColor); + _bottomBorder = defaultBorder.BottomBorder; + _bottomBorderColor = new XLColor(defaultBorder.BottomBorderColor); + _diagonalBorder = defaultBorder.DiagonalBorder; + _diagonalBorderColor = new XLColor(defaultBorder.DiagonalBorderColor); + _diagonalUp = defaultBorder.DiagonalUp; + _diagonalDown = defaultBorder.DiagonalDown; } + #region IXLBorder Members + public XLBorderStyleValues OutsideBorder { set { - if (container != null && !container.UpdatingStyle) + if (_container == null || _container.UpdatingStyle) return; + + foreach (IXLRange r in _container.RangesUsed) { - foreach (var r in container.RangesUsed) - { - r.FirstColumn().Style.Border.LeftBorder = value; - r.LastColumn().Style.Border.RightBorder = value; - r.FirstRow().Style.Border.TopBorder = value; - r.LastRow().Style.Border.BottomBorder = value; - } + r.FirstColumn().Style.Border.LeftBorder = value; + r.LastColumn().Style.Border.RightBorder = value; + r.FirstRow().Style.Border.TopBorder = value; + r.LastRow().Style.Border.BottomBorder = value; } } } @@ -49,211 +64,296 @@ { set { - if (container != null && !container.UpdatingStyle) + if (_container == null || _container.UpdatingStyle) return; + + foreach (IXLRange r in _container.RangesUsed) { - foreach (var r in container.RangesUsed) - { - r.FirstColumn().Style.Border.LeftBorderColor = value; - r.LastColumn().Style.Border.RightBorderColor = value; - r.FirstRow().Style.Border.TopBorderColor = value; - r.LastRow().Style.Border.BottomBorderColor = value; - } + r.FirstColumn().Style.Border.LeftBorderColor = value; + r.LastColumn().Style.Border.RightBorderColor = value; + r.FirstRow().Style.Border.TopBorderColor = value; + r.LastRow().Style.Border.BottomBorderColor = value; } } } - private XLBorderStyleValues leftBorder; public XLBorderStyleValues LeftBorder { - get - { - return leftBorder; - } + get { return _leftBorder; } set { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Border.LeftBorder = value); + SetStyleChanged(); + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Border.LeftBorder = value); else - leftBorder = value; + _leftBorder = value; } } - private IXLColor leftBorderColor; public IXLColor LeftBorderColor { - get - { - return leftBorderColor; - } + get { return _leftBorderColor; } set { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Border.LeftBorderColor = value); + SetStyleChanged(); + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Border.LeftBorderColor = value); else - leftBorderColor = value; + _leftBorderColor = value; } } - private XLBorderStyleValues rightBorder; public XLBorderStyleValues RightBorder { - get - { - return rightBorder; - } + get { return _rightBorder; } set { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Border.RightBorder = value); + SetStyleChanged(); + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Border.RightBorder = value); else - rightBorder = value; + _rightBorder = value; } } - private IXLColor rightBorderColor; public IXLColor RightBorderColor { - get - { - return rightBorderColor; - } + get { return _rightBorderColor; } set { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Border.RightBorderColor = value); + SetStyleChanged(); + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Border.RightBorderColor = value); else - rightBorderColor = value; + _rightBorderColor = value; } } - private XLBorderStyleValues topBorder; public XLBorderStyleValues TopBorder { - get - { - return topBorder; - } + get { return _topBorder; } set { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Border.TopBorder = value); + SetStyleChanged(); + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Border.TopBorder = value); else - topBorder = value; + _topBorder = value; } } - private IXLColor topBorderColor; public IXLColor TopBorderColor { - get - { - return topBorderColor; - } + get { return _topBorderColor; } set { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Border.TopBorderColor = value); + SetStyleChanged(); + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Border.TopBorderColor = value); else - topBorderColor = value; + _topBorderColor = value; } } - private XLBorderStyleValues bottomBorder; public XLBorderStyleValues BottomBorder { - get - { - return bottomBorder; - } + get { return _bottomBorder; } set { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Border.BottomBorder = value); + SetStyleChanged(); + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Border.BottomBorder = value); else - bottomBorder = value; + _bottomBorder = value; } } - private IXLColor bottomBorderColor; public IXLColor BottomBorderColor { - get - { - return bottomBorderColor; - } + get { return _bottomBorderColor; } set { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Border.BottomBorderColor = value); + SetStyleChanged(); + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Border.BottomBorderColor = value); else - bottomBorderColor = value; + _bottomBorderColor = value; } } - private XLBorderStyleValues diagonalBorder; public XLBorderStyleValues DiagonalBorder { - get - { - return diagonalBorder; - } + get { return _diagonalBorder; } set { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Border.DiagonalBorder = value); + SetStyleChanged(); + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Border.DiagonalBorder = value); else - diagonalBorder = value; + _diagonalBorder = value; } } - private IXLColor diagonalBorderColor; public IXLColor DiagonalBorderColor { - get - { - return diagonalBorderColor; - } + get { return _diagonalBorderColor; } set { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Border.DiagonalBorderColor = value); + SetStyleChanged(); + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Border.DiagonalBorderColor = value); else - diagonalBorderColor = value; + _diagonalBorderColor = value; } } - private Boolean diagonalUp; public Boolean DiagonalUp { - get - { - return diagonalUp; - } + get { return _diagonalUp; } set { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Border.DiagonalUp = value); + SetStyleChanged(); + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Border.DiagonalUp = value); else - diagonalUp = value; + _diagonalUp = value; } } - private Boolean diagonalDown; public Boolean DiagonalDown { - get - { - return diagonalDown; - } + get { return _diagonalDown; } set { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Border.DiagonalDown = value); + SetStyleChanged(); + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Border.DiagonalDown = value); else - diagonalDown = value; + _diagonalDown = value; } } + public bool Equals(IXLBorder other) + { + var otherB = other as XLBorder; + return + _leftBorder == otherB._leftBorder + && _leftBorderColor.Equals(otherB._leftBorderColor) + && _rightBorder == otherB._rightBorder + && _rightBorderColor.Equals(otherB._rightBorderColor) + && _topBorder == otherB._topBorder + && _topBorderColor.Equals(otherB._topBorderColor) + && _bottomBorder == otherB._bottomBorder + && _bottomBorderColor.Equals(otherB._bottomBorderColor) + && _diagonalBorder == otherB._diagonalBorder + && _diagonalBorderColor.Equals(otherB._diagonalBorderColor) + && _diagonalUp == otherB._diagonalUp + && _diagonalDown == otherB._diagonalDown + ; + } + + public IXLStyle SetOutsideBorder(XLBorderStyleValues value) + { + OutsideBorder = value; + return _container.Style; + } + + public IXLStyle SetOutsideBorderColor(IXLColor value) + { + OutsideBorderColor = value; + return _container.Style; + } + + public IXLStyle SetLeftBorder(XLBorderStyleValues value) + { + LeftBorder = value; + return _container.Style; + } + + public IXLStyle SetLeftBorderColor(IXLColor value) + { + LeftBorderColor = value; + return _container.Style; + } + + public IXLStyle SetRightBorder(XLBorderStyleValues value) + { + RightBorder = value; + return _container.Style; + } + + public IXLStyle SetRightBorderColor(IXLColor value) + { + RightBorderColor = value; + return _container.Style; + } + + public IXLStyle SetTopBorder(XLBorderStyleValues value) + { + TopBorder = value; + return _container.Style; + } + + public IXLStyle SetTopBorderColor(IXLColor value) + { + TopBorderColor = value; + return _container.Style; + } + + public IXLStyle SetBottomBorder(XLBorderStyleValues value) + { + BottomBorder = value; + return _container.Style; + } + + public IXLStyle SetBottomBorderColor(IXLColor value) + { + BottomBorderColor = value; + return _container.Style; + } + + public IXLStyle SetDiagonalUp() + { + DiagonalUp = true; + return _container.Style; + } + + public IXLStyle SetDiagonalUp(Boolean value) + { + DiagonalUp = value; + return _container.Style; + } + + public IXLStyle SetDiagonalDown() + { + DiagonalDown = true; + return _container.Style; + } + + public IXLStyle SetDiagonalDown(Boolean value) + { + DiagonalDown = value; + return _container.Style; + } + + public IXLStyle SetDiagonalBorder(XLBorderStyleValues value) + { + DiagonalBorder = value; + return _container.Style; + } + + public IXLStyle SetDiagonalBorderColor(IXLColor value) + { + DiagonalBorderColor = value; + return _container.Style; + } + + #endregion + + private void SetStyleChanged() + { + if (_container != null) _container.StyleChanged = true; + } + public override string ToString() { var sb = new StringBuilder(); @@ -283,61 +383,25 @@ return sb.ToString(); } - public bool Equals(IXLBorder other) - { - var otherB = other as XLBorder; - return - leftBorder == otherB.leftBorder - && leftBorderColor.Equals(otherB.leftBorderColor) - && rightBorder==otherB.rightBorder - && rightBorderColor.Equals(otherB.rightBorderColor) - && topBorder==otherB.topBorder - && topBorderColor.Equals(otherB.topBorderColor) - && bottomBorder==otherB.bottomBorder - && bottomBorderColor.Equals(otherB.bottomBorderColor) - && diagonalBorder==otherB.diagonalBorder - && diagonalBorderColor.Equals(otherB.diagonalBorderColor) - && diagonalUp==otherB.diagonalUp - && diagonalDown==otherB.diagonalDown - ; - } - public override bool Equals(object obj) { - return this.Equals((XLBorder)obj); + return Equals((XLBorder)obj); } public override int GetHashCode() { - return (Int32)LeftBorder - ^ LeftBorderColor.GetHashCode() - ^ (Int32)RightBorder - ^ RightBorderColor.GetHashCode() - ^ (Int32)TopBorder - ^ TopBorderColor.GetHashCode() - ^ (Int32)BottomBorder - ^ BottomBorderColor.GetHashCode() - ^ (Int32)DiagonalBorder - ^ DiagonalBorderColor.GetHashCode() - ^ DiagonalUp.GetHashCode() - ^ DiagonalDown.GetHashCode(); + return (Int32)LeftBorder + ^ LeftBorderColor.GetHashCode() + ^ (Int32)RightBorder + ^ RightBorderColor.GetHashCode() + ^ (Int32)TopBorder + ^ TopBorderColor.GetHashCode() + ^ (Int32)BottomBorder + ^ BottomBorderColor.GetHashCode() + ^ (Int32)DiagonalBorder + ^ DiagonalBorderColor.GetHashCode() + ^ DiagonalUp.GetHashCode() + ^ DiagonalDown.GetHashCode(); } - - public IXLStyle SetOutsideBorder(XLBorderStyleValues value) { OutsideBorder = value; return container.Style; } - public IXLStyle SetOutsideBorderColor(IXLColor value) { OutsideBorderColor = value; return container.Style; } - - public IXLStyle SetLeftBorder(XLBorderStyleValues value) { LeftBorder = value; return container.Style; } - public IXLStyle SetLeftBorderColor(IXLColor value) { LeftBorderColor = value; return container.Style; } - public IXLStyle SetRightBorder(XLBorderStyleValues value) { RightBorder = value; return container.Style; } - public IXLStyle SetRightBorderColor(IXLColor value) { RightBorderColor = value; return container.Style; } - public IXLStyle SetTopBorder(XLBorderStyleValues value) { TopBorder = value; return container.Style; } - public IXLStyle SetTopBorderColor(IXLColor value) { TopBorderColor = value; return container.Style; } - public IXLStyle SetBottomBorder(XLBorderStyleValues value) { BottomBorder = value; return container.Style; } - public IXLStyle SetBottomBorderColor(IXLColor value) { BottomBorderColor = value; return container.Style; } - public IXLStyle SetDiagonalUp() { DiagonalUp = true; return container.Style; } public IXLStyle SetDiagonalUp(Boolean value) { DiagonalUp = value; return container.Style; } - public IXLStyle SetDiagonalDown() { DiagonalDown = true; return container.Style; } public IXLStyle SetDiagonalDown(Boolean value) { DiagonalDown = value; return container.Style; } - public IXLStyle SetDiagonalBorder(XLBorderStyleValues value) { DiagonalBorder = value; return container.Style; } - public IXLStyle SetDiagonalBorderColor(IXLColor value) { DiagonalBorderColor = value; return container.Style; } - } -} +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFill.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFill.cs index 1bbde61..ea9c69c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFill.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFill.cs @@ -5,101 +5,138 @@ { internal class XLFill : IXLFill { + #region IXLFill Members + + public bool Equals(IXLFill other) + { + return + _patternType == other.PatternType + && _patternColor.Equals(other.PatternColor) + ; + } + + #endregion + + private void SetStyleChanged() + { + if (_container != null) _container.StyleChanged = true; + } + + public override bool Equals(object obj) + { + return Equals((XLFill)obj); + } + + public override int GetHashCode() + { + return BackgroundColor.GetHashCode() + ^ (Int32)PatternType + ^ PatternColor.GetHashCode(); + } + #region Properties + private IXLColor _patternBackgroundColor; + private IXLColor _patternColor; + private XLFillPatternValues _patternType; + public IXLColor BackgroundColor { - get - { - return patternColor; - } + get { return _patternColor; } set - { - if (container != null && !container.UpdatingStyle) - { - container.Styles.ForEach(s => s.Fill.BackgroundColor = value); - } + { + SetStyleChanged(); + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Fill.BackgroundColor = value); else { - patternType = XLFillPatternValues.Solid; - patternColor = new XLColor(value); - patternBackgroundColor = new XLColor(value); + _patternType = XLFillPatternValues.Solid; + _patternColor = new XLColor(value); + _patternBackgroundColor = new XLColor(value); } } } - private IXLColor patternColor; public IXLColor PatternColor { - get - { - return patternColor; - } + get { return _patternColor; } set { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Fill.PatternColor = value); + SetStyleChanged(); + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Fill.PatternColor = value); else - patternColor = value; + _patternColor = value; } } - private IXLColor patternBackgroundColor; public IXLColor PatternBackgroundColor { - get - { - return patternBackgroundColor; - } + get { return _patternBackgroundColor; } set { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Fill.PatternBackgroundColor = value); + SetStyleChanged(); + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Fill.PatternBackgroundColor = value); else - patternBackgroundColor = value; + _patternBackgroundColor = value; } } - private XLFillPatternValues patternType; public XLFillPatternValues PatternType { - get - { - return patternType; - } + get { return _patternType; } set { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Fill.PatternType = value); + SetStyleChanged(); + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Fill.PatternType = value); else - patternType = value; + _patternType = value; } } - public IXLStyle SetBackgroundColor(IXLColor value) { BackgroundColor = value; return container.Style; } - public IXLStyle SetPatternColor(IXLColor value) { PatternColor = value; return container.Style; } - public IXLStyle SetPatternBackgroundColor(IXLColor value) { PatternBackgroundColor = value; return container.Style; } - public IXLStyle SetPatternType(XLFillPatternValues value) { PatternType = value; return container.Style; } + public IXLStyle SetBackgroundColor(IXLColor value) + { + BackgroundColor = value; + return _container.Style; + } + public IXLStyle SetPatternColor(IXLColor value) + { + PatternColor = value; + return _container.Style; + } + + public IXLStyle SetPatternBackgroundColor(IXLColor value) + { + PatternBackgroundColor = value; + return _container.Style; + } + + public IXLStyle SetPatternType(XLFillPatternValues value) + { + PatternType = value; + return _container.Style; + } #endregion #region Constructors - public XLFill(): this(null, XLWorkbook.DefaultStyle.Fill) - { + private readonly IXLStylized _container; + + public XLFill() : this(null, XLWorkbook.DefaultStyle.Fill) + { } - IXLStylized container; public XLFill(IXLStylized container, IXLFill defaultFill = null) { - this.container = container; - if (defaultFill != null) - { - patternType = defaultFill.PatternType; - patternColor = new XLColor(defaultFill.PatternColor); - patternBackgroundColor = new XLColor(defaultFill.PatternBackgroundColor); - } + _container = container; + if (defaultFill == null) return; + _patternType = defaultFill.PatternType; + _patternColor = new XLColor(defaultFill.PatternColor); + _patternBackgroundColor = new XLColor(defaultFill.PatternBackgroundColor); } #endregion @@ -118,25 +155,5 @@ } #endregion - - public bool Equals(IXLFill other) - { - return - patternType == other.PatternType - && patternColor.Equals(other.PatternColor) - ; - } - - public override bool Equals(object obj) - { - return this.Equals((XLFill)obj); - } - - public override int GetHashCode() - { - return BackgroundColor.GetHashCode() - ^ (Int32)PatternType - ^ PatternColor.GetHashCode(); - } } -} +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs index f54e220..9d86ca6 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs @@ -25,19 +25,18 @@ public XLFont(IXLStylized container, IXLFontBase defaultFont) { _container = container; - if (defaultFont != null) - { - _bold = defaultFont.Bold; - _italic = defaultFont.Italic; - _underline = defaultFont.Underline; - _strikethrough = defaultFont.Strikethrough; - _verticalAlignment = defaultFont.VerticalAlignment; - _shadow = defaultFont.Shadow; - _fontSize = defaultFont.FontSize; - _fontColor = new XLColor(defaultFont.FontColor); - _fontName = defaultFont.FontName; - _fontFamilyNumbering = defaultFont.FontFamilyNumbering; - } + if (defaultFont == null) return; + + _bold = defaultFont.Bold; + _italic = defaultFont.Italic; + _underline = defaultFont.Underline; + _strikethrough = defaultFont.Strikethrough; + _verticalAlignment = defaultFont.VerticalAlignment; + _shadow = defaultFont.Shadow; + _fontSize = defaultFont.FontSize; + _fontColor = new XLColor(defaultFont.FontColor); + _fontName = defaultFont.FontName; + _fontFamilyNumbering = defaultFont.FontFamilyNumbering; } #region IXLFont Members @@ -47,6 +46,7 @@ get { return _bold; } set { + SetStyleChanged(); if (_container != null && !_container.UpdatingStyle) _container.Styles.ForEach(s => s.Font.Bold = value); else @@ -59,6 +59,7 @@ get { return _italic; } set { + SetStyleChanged(); if (_container != null && !_container.UpdatingStyle) _container.Styles.ForEach(s => s.Font.Italic = value); else @@ -71,6 +72,7 @@ get { return _underline; } set { + SetStyleChanged(); if (_container != null && !_container.UpdatingStyle) _container.Styles.ForEach(s => s.Font.Underline = value); else @@ -83,6 +85,7 @@ get { return _strikethrough; } set { + SetStyleChanged(); if (_container != null && !_container.UpdatingStyle) _container.Styles.ForEach(s => s.Font.Strikethrough = value); else @@ -95,6 +98,7 @@ get { return _verticalAlignment; } set { + SetStyleChanged(); if (_container != null && !_container.UpdatingStyle) _container.Styles.ForEach(s => s.Font.VerticalAlignment = value); else @@ -107,6 +111,7 @@ get { return _shadow; } set { + SetStyleChanged(); if (_container != null && !_container.UpdatingStyle) _container.Styles.ForEach(s => s.Font.Shadow = value); else @@ -119,6 +124,7 @@ get { return _fontSize; } set { + SetStyleChanged(); if (_container != null && !_container.UpdatingStyle) _container.Styles.ForEach(s => s.Font.FontSize = value); else @@ -131,6 +137,7 @@ get { return _fontColor; } set { + SetStyleChanged(); if (_container != null && !_container.UpdatingStyle) _container.Styles.ForEach(s => s.Font.FontColor = value); else @@ -143,6 +150,7 @@ get { return _fontName; } set { + SetStyleChanged(); if (_container != null && !_container.UpdatingStyle) _container.Styles.ForEach(s => s.Font.FontName = value); else @@ -155,6 +163,7 @@ get { return _fontFamilyNumbering; } set { + SetStyleChanged(); if (_container != null && !_container.UpdatingStyle) _container.Styles.ForEach(s => s.Font.FontFamilyNumbering = value); else @@ -274,6 +283,11 @@ #endregion + private void SetStyleChanged() + { + if (_container != null) _container.StyleChanged = true; + } + public override string ToString() { var sb = new StringBuilder(); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLNumberFormat.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLNumberFormat.cs index ad9b4b0..1b6620a 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLNumberFormat.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLNumberFormat.cs @@ -4,20 +4,51 @@ { internal class XLNumberFormat : IXLNumberFormat { + #region IXLNumberFormat Members + + public bool Equals(IXLNumberFormat other) + { + var otherNf = other as XLNumberFormat; + + return + _numberFormatId == otherNf._numberFormatId + && _format == otherNf._format + ; + } + + #endregion + + private void SetStyleChanged() + { + if (_container != null) _container.StyleChanged = true; + } + + public override bool Equals(object obj) + { + return Equals((XLNumberFormat)obj); + } + + public override int GetHashCode() + { + return NumberFormatId + ^ Format.GetHashCode(); + } + #region Properties - readonly IXLStylized _container; + private readonly IXLStylized _container; + private String _format = String.Empty; private Int32 _numberFormatId; + public Int32 NumberFormatId { get { return _numberFormatId; } set { + SetStyleChanged(); if (_container != null && !_container.UpdatingStyle) - { _container.Styles.ForEach(s => s.NumberFormat.NumberFormatId = value); - } else { _numberFormatId = value; @@ -26,16 +57,14 @@ } } - private String _format = String.Empty; public String Format { get { return _format; } set { + SetStyleChanged(); if (_container != null && !_container.UpdatingStyle) - { _container.Styles.ForEach(s => s.NumberFormat.Format = value); - } else { _format = value; @@ -44,8 +73,17 @@ } } - public IXLStyle SetNumberFormatId(Int32 value) { NumberFormatId = value; return _container.Style; } - public IXLStyle SetFormat(String value) { Format = value; return _container.Style; } + public IXLStyle SetNumberFormatId(Int32 value) + { + NumberFormatId = value; + return _container.Style; + } + + public IXLStyle SetFormat(String value) + { + Format = value; + return _container.Style; + } #endregion @@ -75,26 +113,5 @@ } #endregion - - public bool Equals(IXLNumberFormat other) - { - var otherNf = other as XLNumberFormat; - - return - _numberFormatId == otherNf._numberFormatId - && _format == otherNf._format - ; - } - - public override bool Equals(object obj) - { - return Equals((XLNumberFormat)obj); - } - - public override int GetHashCode() - { - return NumberFormatId - ^ Format.GetHashCode(); - } } -} +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLProtection.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLProtection.cs index b54c8bf..7d0c2ef 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLProtection.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLProtection.cs @@ -4,17 +4,19 @@ { internal class XLProtection : IXLProtection { - readonly IXLStylized _container; + private readonly IXLStylized _container; + private Boolean _hidden; private Boolean _locked; + + #region IXLProtection Members + public Boolean Locked { - get - { - return _locked; - } + get { return _locked; } set { + SetStyleChanged(); if (_container != null && !_container.UpdatingStyle) _container.Styles.ForEach(s => s.Protection.Locked = value); else @@ -22,15 +24,12 @@ } } - private Boolean _hidden; public Boolean Hidden { - get - { - return _hidden; - } + get { return _hidden; } set { + SetStyleChanged(); if (_container != null && !_container.UpdatingStyle) _container.Styles.ForEach(s => s.Protection.Hidden = value); else @@ -38,6 +37,42 @@ } } + public bool Equals(IXLProtection other) + { + var otherP = other as XLProtection; + if (otherP == null) + return false; + + return _locked == otherP._locked + && _hidden == otherP._hidden; + } + + public IXLStyle SetLocked() + { + Locked = true; + return _container.Style; + } + + public IXLStyle SetLocked(Boolean value) + { + Locked = value; + return _container.Style; + } + + public IXLStyle SetHidden() + { + Hidden = true; + return _container.Style; + } + + public IXLStyle SetHidden(Boolean value) + { + Hidden = value; + return _container.Style; + } + + #endregion + #region Constructors public XLProtection() @@ -56,14 +91,9 @@ #endregion - public bool Equals(IXLProtection other) + private void SetStyleChanged() { - var otherP = other as XLProtection; - if (otherP == null) - return false; - - return _locked == otherP._locked - && _hidden == otherP._hidden; + if (_container != null) _container.StyleChanged = true; } public override bool Equals(object obj) @@ -86,10 +116,5 @@ return Hidden ? "Hidden" : "None"; } - - public IXLStyle SetLocked() { Locked = true; return _container.Style; } public IXLStyle SetLocked(Boolean value) { Locked = value; return _container.Style; } - public IXLStyle SetHidden() { Hidden = true; return _container.Style; } public IXLStyle SetHidden(Boolean value) { Hidden = value; return _container.Style; } - } - -} +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLStylizedContainer.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLStylizedContainer.cs index 7802ba8..a404485 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLStylizedContainer.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLStylizedContainer.cs @@ -2,14 +2,17 @@ namespace ClosedXML.Excel { + using System; + internal class XLStylizedContainer: IXLStylized { - IXLStylized container; + public Boolean StyleChanged { get; set; } + readonly IXLStylized _container; public XLStylizedContainer(IXLStyle style, IXLStylized container) { - this.Style = style; - this.container = container; - this.RangesUsed = container.RangesUsed; + Style = style; + _container = container; + RangesUsed = container.RangesUsed; } public IXLStyle Style { get; set; } @@ -18,9 +21,9 @@ { get { - container.UpdatingStyle = true; + _container.UpdatingStyle = true; yield return Style; - container.UpdatingStyle = false; + _container.UpdatingStyle = false; } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTable.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTable.cs index a2e6650..2f7aa12 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTable.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTable.cs @@ -351,5 +351,10 @@ IXLTable SetShowTotalsRow(); IXLTable SetShowTotalsRow(Boolean value); IXLTable SetShowAutoFilter(); IXLTable SetShowAutoFilter(Boolean value); + /// + /// Clears the contents of this table. + /// + /// Specify what you want to clear. + new IXLTable Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRow.cs index 58cb25e..305197d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRow.cs @@ -16,5 +16,11 @@ new IXLTableRow RowAbove(Int32 step); new IXLTableRow RowBelow(); new IXLTableRow RowBelow(Int32 step); + + /// + /// Clears the contents of this row. + /// + /// Specify what you want to clear. + new IXLTableRow Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRows.cs index 07952dc..5408847 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRows.cs @@ -12,11 +12,6 @@ void Add(IXLTableRow tableRow); /// - /// Clears the contents of the rows (including styles). - /// - void Clear(); - - /// /// Returns the collection of cells. /// IXLCells Cells(); @@ -34,6 +29,10 @@ IXLStyle Style { get; set; } - + /// + /// Clears the contents of these rows. + /// + /// Specify what you want to clear. + IXLTableRows Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTables.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTables.cs index ce14b63..f876966 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTables.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTables.cs @@ -8,6 +8,11 @@ void Add(IXLTable table); IXLTable Table(Int32 index); IXLTable Table(String name); - + + /// + /// Clears the contents of these tables. + /// + /// Specify what you want to clear. + IXLTables Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs index 5af07de..b091377 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs @@ -490,5 +490,11 @@ throw new ArgumentOutOfRangeException("The header row doesn't contain field name '" + name + "'."); } + + public new IXLTable Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) + { + base.Clear(clearOptions); + return this; + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRow.cs index 1e7b0f2..37b915b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRow.cs @@ -103,5 +103,11 @@ } #endregion + + public new IXLTableRow Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) + { + base.Clear(clearOptions); + return this; + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRows.cs index c4771e2..082e07c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRows.cs @@ -7,6 +7,7 @@ internal class XLTableRows : IXLTableRows, IXLStylized { + public Boolean StyleChanged { get; set; } private readonly List _ranges = new List(); private IXLStyle _style; @@ -60,9 +61,10 @@ #region IXLTableRows Members - public void Clear() + public IXLTableRows Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) { - _ranges.ForEach(r => r.Clear()); + _ranges.ForEach(r => r.Clear(clearOptions)); + return this; } public void Add(IXLTableRow range) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTables.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTables.cs index 3f8dacc..c5615d7 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTables.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTables.cs @@ -38,5 +38,11 @@ } #endregion + + public IXLTables Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) + { + _tables.Values.ForEach(t => t.Clear(clearOptions)); + return this; + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs index ebc4364..783121d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs @@ -166,6 +166,27 @@ private readonly Dictionary _unsupportedSheets = new Dictionary(); + private readonly Dictionary _stylesById = new Dictionary(); + private readonly Dictionary _stylesByStyle = new Dictionary(); + + internal Int32 GetStyleId(IXLStyle style) + { + Int32 cached; + if (_stylesByStyle.TryGetValue(style, out cached)) + return cached; + + var count = _stylesByStyle.Count; + var styleToUse = new XLStyle(null, style); + _stylesByStyle.Add(styleToUse, count); + _stylesById.Add(count, styleToUse); + return count; + } + + internal IXLStyle GetStyleById(Int32 id) + { + return _stylesById[id]; + } + #region Nested Type: XLLoadSource private enum XLLoadSource diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index 7eb5d19..6a73107 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -347,7 +347,7 @@ XLWorksheet ws, Dictionary styleList, Cell cell) { Int32 styleIndex = cell.StyleIndex != null ? Int32.Parse(cell.StyleIndex.InnerText) : 0; - var xlCell = ws.Cell(cell.CellReference); + var xlCell = ws.CellFast(cell.CellReference); if (styleList.ContainsKey(styleIndex)) xlCell.Style = styleList[styleIndex]; @@ -578,22 +578,10 @@ ApplyStyle(xlRow, styleIndex, s, fills, borders, fonts, numberingFormats); else { - //((XLRow)xlRow).style = ws.Style; - //((XLRow)xlRow).SetStyleNoColumns(ws.Style); xlRow.Style = DefaultStyle; - //xlRow.Style = ws.Style; } } - //OpenXmlReader reader = OpenXmlReader.Create(row); - //while (reader.Read()) - //{ - // if (reader.ElementType == typeof(Cell)) - // { - // LoadCells(sharedStrings, s, numberingFormats, fills, borders, fonts, sharedFormulasR1C1, ws, styleList, (Cell)reader.LoadCurrentElement()); - // } - //} - foreach (Cell cell in row.Elements()) LoadCells(sharedStrings, s, numberingFormats, fills, borders, fonts, sharedFormulasR1C1, ws, styleList, cell); @@ -999,7 +987,7 @@ { if (alignment.Horizontal != null) xlStylized.InnerStyle.Alignment.Horizontal = alignment.Horizontal.Value.ToClosedXml(); - if (alignment.Indent != null) + if (alignment.Indent != null && alignment.Indent != 0) xlStylized.InnerStyle.Alignment.Indent = Int32.Parse(alignment.Indent.ToString()); if (alignment.JustifyLastLine != null) xlStylized.InnerStyle.Alignment.JustifyLastLine = alignment.JustifyLastLine; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs index c7185a5..41c0380 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs @@ -13,7 +13,7 @@ [DebuggerBrowsable(DebuggerBrowsableState.Never)] private readonly RelIdGenerator _relIdGenerator; [DebuggerBrowsable(DebuggerBrowsableState.Never)] - private readonly Dictionary _sharedStyles; + private readonly Dictionary _sharedStyles; [DebuggerBrowsable(DebuggerBrowsableState.Never)] private readonly Dictionary _sharedFonts; [DebuggerBrowsable(DebuggerBrowsableState.Never)] @@ -25,7 +25,7 @@ public SaveContext() { _relIdGenerator = new RelIdGenerator(); - _sharedStyles = new Dictionary(); + _sharedStyles = new Dictionary(); _sharedFonts = new Dictionary(); _tableNames = new HashSet(); _tableId = 0; @@ -37,7 +37,7 @@ [DebuggerStepThrough] get { return _relIdGenerator; } } - public Dictionary SharedStyles + public Dictionary SharedStyles { [DebuggerStepThrough] get { return _sharedStyles; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index 776a69d..e53fe2f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -1571,6 +1571,7 @@ private void GenerateWorkbookStylesPartContent(WorkbookStylesPart workbookStylesPart, SaveContext context) { var defaultStyle = new XLStyle(null, DefaultStyle); + Int32 defaultStyleId = GetStyleId(defaultStyle); if (!context.SharedFonts.ContainsKey(defaultStyle.Font)) context.SharedFonts.Add(defaultStyle.Font, new FontInfo {FontId = 0, Font = defaultStyle.Font}); @@ -1614,7 +1615,7 @@ else defaultFormatId = 0; - context.SharedStyles.Add(defaultStyle, + context.SharedStyles.Add(defaultStyleId, new StyleInfo { StyleId = defaultFormatId, @@ -1631,28 +1632,30 @@ UInt32 fillCount = 3; UInt32 borderCount = 1; Int32 numberFormatCount = 1; - var xlStyles = new HashSet(); + var xlStyles = new HashSet(); foreach (XLWorksheet worksheet in WorksheetsInternal) { - foreach (IXLStyle s in worksheet.Styles.Where(s => !xlStyles.Contains(s))) + + foreach (var s in worksheet.GetStyleIds().Where(s => !xlStyles.Contains(s))) xlStyles.Add(s); foreach ( - IXLStyle s in - worksheet.Internals.ColumnsCollection.Select(kp => kp.Value.Style).Where( + Int32 s in + worksheet.Internals.ColumnsCollection.Select(kp => kp.Value.GetStyleId()).Where( s => !xlStyles.Contains(s))) xlStyles.Add(s); foreach ( - IXLStyle s in - worksheet.Internals.RowsCollection.Select(kp => kp.Value.Style).Where(s => !xlStyles.Contains(s)) + Int32 s in + worksheet.Internals.RowsCollection.Select(kp => kp.Value.GetStyleId()).Where(s => !xlStyles.Contains(s)) ) xlStyles.Add(s); } - foreach (IXLStyle xlStyle in xlStyles) + foreach (Int32 id in xlStyles) { + var xlStyle = GetStyleById(id); if (!context.SharedFonts.ContainsKey(xlStyle.Font)) context.SharedFonts.Add(xlStyle.Font, new FontInfo {FontId = fontCount++, Font = xlStyle.Font}); @@ -1680,15 +1683,16 @@ var allSharedFills = ResolveFills(workbookStylesPart, sharedFills); var allSharedBorders = ResolveBorders(workbookStylesPart, sharedBorders); - foreach (IXLStyle xlStyle in xlStyles) + foreach (Int32 id in xlStyles) { - if (context.SharedStyles.ContainsKey(xlStyle)) continue; + var xlStyle = GetStyleById(id); + if (context.SharedStyles.ContainsKey(id)) continue; int numberFormatId = xlStyle.NumberFormat.NumberFormatId >= 0 ? xlStyle.NumberFormat.NumberFormatId : allSharedNumberFormats[xlStyle.NumberFormat].NumberFormatId; - context.SharedStyles.Add(xlStyle, + context.SharedStyles.Add(id, new StyleInfo { StyleId = styleCount++, @@ -1712,8 +1716,8 @@ } workbookStylesPart.Stylesheet.CellStyles.Count = (UInt32)workbookStylesPart.Stylesheet.CellStyles.Count(); - var newSharedStyles = new Dictionary(); - foreach (KeyValuePair ss in context.SharedStyles) + var newSharedStyles = new Dictionary(); + foreach (KeyValuePair ss in context.SharedStyles) { Int32 styleId = -1; foreach (CellFormat f in workbookStylesPart.Stylesheet.CellFormats) @@ -2535,7 +2539,7 @@ maxInColumnsCollection = 0; } - uint worksheetStyleId = context.SharedStyles[xlWorksheet.Style].StyleId; + uint worksheetStyleId = context.SharedStyles[xlWorksheet.GetStyleId()].StyleId; if (minInColumnsCollection > 1) { UInt32Value min = 1; @@ -2566,7 +2570,7 @@ Int32 outlineLevel = 0; if (xlWorksheet.Internals.ColumnsCollection.ContainsKey(co)) { - styleId = context.SharedStyles[xlWorksheet.Internals.ColumnsCollection[co].Style].StyleId; + 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; @@ -2574,7 +2578,7 @@ } else { - styleId = context.SharedStyles[xlWorksheet.Style].StyleId; + styleId = context.SharedStyles[xlWorksheet.GetStyleId()].StyleId; columnWidth = worksheetColumnWidth; } @@ -2711,9 +2715,11 @@ row.Height = thisRow.Height; row.CustomHeight = true; } - if (!thisRow.Style.Equals(xlWorksheet.Style)) + + //if (!thisRow.Style.Equals(xlWorksheet.Style)) + if(thisRow.GetStyleId() != xlWorksheet.GetStyleId()) { - row.StyleIndex = context.SharedStyles[thisRow.Style].StyleId; + row.StyleIndex = context.SharedStyles[thisRow.GetStyleId()].StyleId; row.CustomFormat = true; } if (thisRow.IsHidden) @@ -2738,13 +2744,13 @@ } if (!cellsByRow.ContainsKey(distinctRow)) continue; - + Boolean isNewRow = !row.Elements().Any(); foreach (XLCell opCell in cellsByRow[distinctRow] .OrderBy(c => c.Address.ColumnNumber) .Select(c => (XLCell)c)) { - uint styleId = context.SharedStyles[opCell.Style].StyleId; + uint styleId = context.SharedStyles[opCell.GetStyleId()].StyleId; var dataType = opCell.DataType; string cellReference = (opCell.Address).GetTrimmedAddress(); @@ -2954,7 +2960,7 @@ #region DataValidations - if (!xlWorksheet.DataValidations.Any()) + if (!xlWorksheet.DataValidations.Any(d=> d.IsDirty() )) { worksheetPart.Worksheet.RemoveAllChildren(); cm.SetElement(XLWSContentManager.XLWSContents.DataValidations, null); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index c4ae5d2..f8f461b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -55,7 +55,7 @@ PivotTables = new XLPivotTables(); Protection = new XLSheetProtection(); Workbook = workbook; - _style = new XLStyle(this, workbook.Style); + SetStyle(workbook.Style); Internals = new XLWorksheetInternals(new XLCellsCollection(), new XLColumnsCollection(), new XLRowsCollection(), new XLRanges()); PageSetup = new XLPageSetup((XLPageSetup)workbook.PageOptions, this); @@ -88,19 +88,29 @@ get { UpdatingStyle = true; - yield return _style; + yield return GetStyle(); foreach (XLCell c in Internals.CellsCollection.GetCells()) yield return c.Style; UpdatingStyle = false; } } + public HashSet GetStyleIds() + { + var retVal = new HashSet {GetStyleId()}; + foreach (int id in Internals.CellsCollection.GetCells().Select(c => c.GetStyleId()).Where(id => !retVal.Contains(id))) + { + retVal.Add(id); + } + return retVal; + } + public override Boolean UpdatingStyle { get; set; } public override IXLStyle InnerStyle { - get { return new XLStyle(new XLStylizedContainer(_style, this), _style); } - set { _style = new XLStyle(this, value); } + get { return GetStyle(); } + set { SetStyle(value); } } internal Boolean RowHeightChanged { get; set; } @@ -115,14 +125,33 @@ public XLWorkbook Workbook { get; private set; } + private Int32 _styleCacheId; + public new Int32 GetStyleId() + { + if (StyleChanged) + SetStyle(Style); + + return _styleCacheId; + } + private new void SetStyle(IXLStyle styleToUse) + { + _styleCacheId = Worksheet.Workbook.GetStyleId(styleToUse); + _style = null; + StyleChanged = false; + } + private new IXLStyle GetStyle() + { + return _style ?? (_style = new XLStyle(this, Worksheet.Workbook.GetStyleById(_styleCacheId))); + } + public override IXLStyle Style { - get { return _style; } + get { return GetStyle(); } set { - _style = new XLStyle(this, value); + SetStyle(value); foreach (XLCell cell in Internals.CellsCollection.GetCells()) - cell.Style = _style; + cell.Style = value; } } @@ -590,7 +619,7 @@ Workbook.WorksheetsInternal.Delete(Name); } - public new void Clear() + public void Clear() { Internals.CellsCollection.Clear(); Internals.ColumnsCollection.Clear(); @@ -644,7 +673,7 @@ targetSheet.Visibility = Visibility; targetSheet.ColumnWidth = ColumnWidth; targetSheet.RowHeight = RowHeight; - targetSheet._style = new XLStyle(targetSheet, _style); + targetSheet.SetStyle(Style); targetSheet.PageSetup = new XLPageSetup((XLPageSetup)PageSetup, targetSheet); targetSheet.Outline = new XLOutline(Outline); targetSheet.SheetView = new XLSheetView(SheetView); @@ -1154,6 +1183,11 @@ .Ranges.First().FirstCell(); } + public XLCell CellFast(String cellAddressInRange) + { + return Cell(XLAddress.Create(this, cellAddressInRange)); + } + public override XLRange Range(String rangeAddressStr) { if (ExcelHelper.IsValidRangeAddress(rangeAddressStr)) diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/LambdaExpressions.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/LambdaExpressions.cs index 44ac1e5..282d90b 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/LambdaExpressions.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/LambdaExpressions.cs @@ -1,6 +1,5 @@ using System.IO; using System.Linq; -using ClosedXML; using ClosedXML.Excel; namespace ClosedXML_Examples @@ -27,10 +26,18 @@ .Where(r => !r.Cell(3).GetBoolean()) // where the 3rd cell of each row is false .ForEach(r => r.Delete()); // delete the row and shift the cells up (the default for rows in a range) - // Put a light gray background to all text cells - rngData.Cells() // From all cells - .Where(c => c.DataType == XLCellValues.Text) // where the data type is Text - .ForEach(c => c.Style.Fill.BackgroundColor = XLColor.LightGray); // Fill with a light gray + //// Put a light gray background to all text cells + //rngData.Cells() // From all cells + // .Where(c => c.DataType == XLCellValues.Text) // where the data type is Text + // .ForEach(c => c.Style.Fill.BackgroundColor = XLColor.LightGray); // Fill with a light gray + + var cells = rngData.Cells(); + var filtered = cells.Where(c => c.DataType == XLCellValues.Text); + var list = filtered.ToList(); + foreach (var c in list) + { + c.Style.Fill.BackgroundColor = XLColor.LightGray; + } // Put a thick border to the bottom of the table (we may have deleted the bottom cells with the border) rngData.LastRow().Style.Border.BottomBorder = XLBorderStyleValues.Thick; diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/UsingTables.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/UsingTables.cs index 1d16762..4935de4 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/UsingTables.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/UsingTables.cs @@ -1,6 +1,7 @@ using System; using System.IO; using ClosedXML.Excel; +using System.Linq; namespace ClosedXML_Examples.Ranges @@ -22,8 +23,13 @@ var firstCell = ws.FirstCellUsed(); var lastCell = ws.LastCellUsed(); var range = ws.Range(firstCell.Address, lastCell.Address); - range.Row(1).Delete(); // Deleting the "Contacts" header (we don't need it for our purposes) - range.ClearStyles(); // We want to use a theme for table, not the hard coded format of the BasicTable + range.FirstRow().Delete(); // Deleting the "Contacts" header (we don't need it for our purposes) + + // We want to use a theme for table, not the hard coded format of the BasicTable + range.Clear(XLClearOptions.Formats); + // Put back the date and number formats + range.Column(4).Style.NumberFormat.NumberFormatId = 15; + range.Column(5).Style.NumberFormat.Format = "$ #,##0"; var table = range.CreateTable(); // You can also use range.AsTable() if you want to // manipulate the range as a table but don't want diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj index d7e4122..dbeda88 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -190,6 +190,7 @@ +