diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs
index bebfeae..5141aae 100644
--- a/ClosedXML/Excel/Cells/XLCell.cs
+++ b/ClosedXML/Excel/Cells/XLCell.cs
@@ -2356,23 +2356,32 @@
public IXLCell CopyFrom(IXLCell otherCell, Boolean copyDataValidations)
{
+ return CopyFrom(otherCell, copyDataValidations, copyConditionalFormats: true);
+ }
+
+ public IXLCell CopyFrom(IXLCell otherCell, Boolean copyDataValidations, bool copyConditionalFormats)
+ {
var source = otherCell as XLCell; // To expose GetFormulaR1C1, etc
CopyFromInternal(source, copyDataValidations);
- var conditionalFormats = source.Worksheet.ConditionalFormats.Where(c => c.Ranges.Any(range => range.Contains(source))).ToList();
- foreach (var cf in conditionalFormats)
+ if (copyConditionalFormats)
{
- if (source.Worksheet == Worksheet)
+ var conditionalFormats = source.Worksheet.ConditionalFormats
+ .Where(c => c.Ranges.Any(range => range.Contains(source))).ToList();
+ foreach (var cf in conditionalFormats)
{
- if (!cf.Ranges.Any(range => range.Contains(this)))
+ if (source.Worksheet == Worksheet)
{
- cf.Ranges.Add(this);
+ if (!cf.Ranges.Any(range => range.Contains(this)))
+ {
+ cf.Ranges.Add(this);
+ }
}
- }
- else
- {
- CopyConditionalFormatsFrom(source.AsRange());
+ else
+ {
+ CopyConditionalFormatsFrom(source.AsRange());
+ }
}
}
diff --git a/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs b/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs
index 0128e12..ba18bf4 100644
--- a/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs
+++ b/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs
@@ -134,27 +134,44 @@
IXLCFIconSet IconSet(XLIconSetStyle iconSetStyle, Boolean reverseIconOrder = false, Boolean showIconOnly = false);
XLConditionalFormatType ConditionalFormatType { get; }
+
XLIconSetStyle IconSetStyle { get; }
+
XLTimePeriod TimePeriod { get; }
+
Boolean ReverseIconOrder { get; }
+
Boolean ShowIconOnly { get; }
+
Boolean ShowBarOnly { get; }
+
Boolean StopIfTrue { get; }
+
///
/// The first of the .
///
IXLRange Range { get; set; }
+
IXLRanges Ranges { get; }
+
XLDictionary Values { get; }
+
XLDictionary Colors { get; }
+
XLDictionary ContentTypes { get; }
+
XLDictionary IconSetOperators { get; }
XLCFOperator Operator { get; }
+
Boolean Bottom { get; }
+
Boolean Percent { get; }
IXLConditionalFormat SetStopIfTrue();
+
IXLConditionalFormat SetStopIfTrue(Boolean value);
+
+ IXLConditionalFormat CopyTo(IXLWorksheet targetSheet);
}
}
diff --git a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs
index d388336..aab56c9 100644
--- a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs
+++ b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs
@@ -1,7 +1,6 @@
using System;
using System.Collections.Generic;
using System.Linq;
-using ClosedXML.Utils;
namespace ClosedXML.Excel
{
@@ -22,14 +21,14 @@
public bool Equals(IXLConditionalFormat x, IXLConditionalFormat y)
{
- var xx = (XLConditionalFormat) x;
- var yy = (XLConditionalFormat) y;
+ var xx = (XLConditionalFormat)x;
+ var yy = (XLConditionalFormat)y;
if (ReferenceEquals(xx, yy)) return true;
if (ReferenceEquals(xx, null)) return false;
if (ReferenceEquals(yy, null)) return false;
if (xx.GetType() != yy.GetType()) return false;
- var xxValues = xx.Values.Values.Where(v => !v.IsFormula).Select(v=>v.Value);
+ var xxValues = xx.Values.Values.Where(v => !v.IsFormula).Select(v => v.Value);
var yyValues = yy.Values.Values.Where(v => !v.IsFormula).Select(v => v.Value);
var xxFormulas = x.Ranges.Any() ? xx.Values.Values.Where(v => v.IsFormula).Select(f => ((XLCell)x.Ranges.First().FirstCell()).GetFormulaR1C1(f.Value)) : null;
var yyFormulas = y.Ranges.Any() ? yy.Values.Values.Where(v => v.IsFormula).Select(f => ((XLCell)y.Ranges.First().FirstCell()).GetFormulaR1C1(f.Value)) : null;
@@ -105,12 +104,14 @@
}
private static readonly IEqualityComparer FullComparerInstance = new FullEqualityComparer(true);
+
public static IEqualityComparer FullComparer
{
get { return FullComparerInstance; }
}
private static readonly IEqualityComparer NoRangeComparerInstance = new FullEqualityComparer(false);
+
public static IEqualityComparer NoRangeComparer
{
get { return NoRangeComparerInstance; }
@@ -145,26 +146,23 @@
}
public XLConditionalFormat(XLConditionalFormat conditionalFormat, IXLRange targetRange)
+ : this(conditionalFormat, new[] { targetRange })
+ {
+ }
+
+ public XLConditionalFormat(XLConditionalFormat conditionalFormat, IEnumerable targetRanges)
: this(conditionalFormat.StyleValue)
{
- if (targetRange != null)
- Ranges.Add(targetRange);
-
- ConditionalFormatType = conditionalFormat.ConditionalFormatType;
- TimePeriod = conditionalFormat.TimePeriod;
- IconSetStyle = conditionalFormat.IconSetStyle;
- Operator = conditionalFormat.Operator;
- Bottom = conditionalFormat.Bottom;
- Percent = conditionalFormat.Percent;
- ReverseIconOrder = conditionalFormat.ReverseIconOrder;
- ShowIconOnly = conditionalFormat.ShowIconOnly;
- ShowBarOnly = conditionalFormat.ShowBarOnly;
- StopIfTrue = OpenXmlHelper.GetBooleanValueAsBool(conditionalFormat.StopIfTrue, true);
+ targetRanges?.ForEach(range => Ranges.Add(range));
+ CopyFrom(conditionalFormat);
}
+
#endregion Constructors
public Guid Id { get; internal set; }
+
internal Int32 OriginalPriority { get; set; }
+
public Boolean CopyDefaultModify { get; set; }
public override IEnumerable Styles
@@ -179,15 +177,18 @@
{
get { yield break; }
}
-
+
public override IXLRanges RangesUsed
{
get { return new XLRanges(); }
}
public XLDictionary Values { get; private set; }
+
public XLDictionary Colors { get; private set; }
+
public XLDictionary ContentTypes { get; private set; }
+
public XLDictionary IconSetOperators { get; private set; }
public IXLRange Range
@@ -199,16 +200,27 @@
Ranges.Add(value);
}
}
+
public IXLRanges Ranges { get; private set; }
+
public XLConditionalFormatType ConditionalFormatType { get; set; }
+
public XLTimePeriod TimePeriod { get; set; }
+
public XLIconSetStyle IconSetStyle { get; set; }
+
public XLCFOperator Operator { get; set; }
+
public Boolean Bottom { get; set; }
+
public Boolean Percent { get; set; }
+
public Boolean ReverseIconOrder { get; set; }
+
public Boolean ShowIconOnly { get; set; }
+
public Boolean ShowBarOnly { get; set; }
+
public Boolean StopIfTrue { get; set; }
public IXLConditionalFormat SetStopIfTrue()
@@ -216,12 +228,22 @@
return SetStopIfTrue(true);
}
- public IXLConditionalFormat SetStopIfTrue(bool value)
+ public IXLConditionalFormat SetStopIfTrue(bool value)
{
this.StopIfTrue = value;
return this;
}
+ public IXLConditionalFormat CopyTo(IXLWorksheet targetSheet)
+ {
+ if (targetSheet == Range?.Worksheet)
+ throw new InvalidOperationException("Cannot copy conditional format to the worksheet it already belongs to.");
+ var targetRanges = Ranges.Select(r => targetSheet.Range(((XLRangeAddress)r.RangeAddress).WithoutWorksheet()));
+ var newCf = new XLConditionalFormat(this, targetRanges);
+ targetSheet.ConditionalFormats.Add(newCf);
+ return newCf;
+ }
+
public void CopyFrom(IXLConditionalFormat other)
{
InnerStyle = other.Style;
@@ -255,27 +277,32 @@
ConditionalFormatType = XLConditionalFormatType.IsBlank;
return Style;
}
+
public IXLStyle WhenNotBlank()
{
ConditionalFormatType = XLConditionalFormatType.NotBlank;
return Style;
}
+
public IXLStyle WhenIsError()
{
ConditionalFormatType = XLConditionalFormatType.IsError;
return Style;
}
+
public IXLStyle WhenNotError()
{
ConditionalFormatType = XLConditionalFormatType.NotError;
return Style;
}
+
public IXLStyle WhenDateIs(XLTimePeriod timePeriod)
{
TimePeriod = timePeriod;
ConditionalFormatType = XLConditionalFormatType.TimePeriod;
return Style;
}
+
public IXLStyle WhenContains(String value)
{
Values.Initialize(new XLFormula { Value = value });
@@ -283,6 +310,7 @@
Operator = XLCFOperator.Contains;
return Style;
}
+
public IXLStyle WhenNotContains(String value)
{
Values.Initialize(new XLFormula { Value = value });
@@ -290,6 +318,7 @@
Operator = XLCFOperator.NotContains;
return Style;
}
+
public IXLStyle WhenStartsWith(String value)
{
Values.Initialize(new XLFormula { Value = value });
@@ -297,6 +326,7 @@
Operator = XLCFOperator.StartsWith;
return Style;
}
+
public IXLStyle WhenEndsWith(String value)
{
Values.Initialize(new XLFormula { Value = value });
@@ -312,6 +342,7 @@
ConditionalFormatType = XLConditionalFormatType.CellIs;
return Style;
}
+
public IXLStyle WhenNotEquals(String value)
{
Values.Initialize(new XLFormula { Value = value });
@@ -319,6 +350,7 @@
ConditionalFormatType = XLConditionalFormatType.CellIs;
return Style;
}
+
public IXLStyle WhenGreaterThan(String value)
{
Values.Initialize(new XLFormula { Value = value });
@@ -326,6 +358,7 @@
ConditionalFormatType = XLConditionalFormatType.CellIs;
return Style;
}
+
public IXLStyle WhenLessThan(String value)
{
Values.Initialize(new XLFormula { Value = value });
@@ -333,6 +366,7 @@
ConditionalFormatType = XLConditionalFormatType.CellIs;
return Style;
}
+
public IXLStyle WhenEqualOrGreaterThan(String value)
{
Values.Initialize(new XLFormula { Value = value });
@@ -340,6 +374,7 @@
ConditionalFormatType = XLConditionalFormatType.CellIs;
return Style;
}
+
public IXLStyle WhenEqualOrLessThan(String value)
{
Values.Initialize(new XLFormula { Value = value });
@@ -347,6 +382,7 @@
ConditionalFormatType = XLConditionalFormatType.CellIs;
return Style;
}
+
public IXLStyle WhenBetween(String minValue, String maxValue)
{
Values.Initialize(new XLFormula { Value = minValue });
@@ -355,6 +391,7 @@
ConditionalFormatType = XLConditionalFormatType.CellIs;
return Style;
}
+
public IXLStyle WhenNotBetween(String minValue, String maxValue)
{
Values.Initialize(new XLFormula { Value = minValue });
@@ -371,6 +408,7 @@
ConditionalFormatType = XLConditionalFormatType.CellIs;
return Style;
}
+
public IXLStyle WhenNotEquals(Double value)
{
Values.Initialize(new XLFormula(value));
@@ -378,6 +416,7 @@
ConditionalFormatType = XLConditionalFormatType.CellIs;
return Style;
}
+
public IXLStyle WhenGreaterThan(Double value)
{
Values.Initialize(new XLFormula(value));
@@ -385,6 +424,7 @@
ConditionalFormatType = XLConditionalFormatType.CellIs;
return Style;
}
+
public IXLStyle WhenLessThan(Double value)
{
Values.Initialize(new XLFormula(value));
@@ -392,6 +432,7 @@
ConditionalFormatType = XLConditionalFormatType.CellIs;
return Style;
}
+
public IXLStyle WhenEqualOrGreaterThan(Double value)
{
Values.Initialize(new XLFormula(value));
@@ -399,6 +440,7 @@
ConditionalFormatType = XLConditionalFormatType.CellIs;
return Style;
}
+
public IXLStyle WhenEqualOrLessThan(Double value)
{
Values.Initialize(new XLFormula(value));
@@ -406,6 +448,7 @@
ConditionalFormatType = XLConditionalFormatType.CellIs;
return Style;
}
+
public IXLStyle WhenBetween(Double minValue, Double maxValue)
{
Values.Initialize(new XLFormula(minValue));
@@ -414,6 +457,7 @@
ConditionalFormatType = XLConditionalFormatType.CellIs;
return Style;
}
+
public IXLStyle WhenNotBetween(Double minValue, Double maxValue)
{
Values.Initialize(new XLFormula(minValue));
@@ -428,11 +472,13 @@
ConditionalFormatType = XLConditionalFormatType.IsDuplicate;
return Style;
}
+
public IXLStyle WhenIsUnique()
{
ConditionalFormatType = XLConditionalFormatType.IsUnique;
return Style;
}
+
public IXLStyle WhenIsTrue(String formula)
{
String f = formula.TrimStart()[0] == '=' ? formula : "=" + formula;
@@ -440,6 +486,7 @@
ConditionalFormatType = XLConditionalFormatType.Expression;
return Style;
}
+
public IXLStyle WhenIsTop(Int32 value, XLTopBottomType topBottomType = XLTopBottomType.Items)
{
Values.Initialize(new XLFormula(value));
@@ -448,6 +495,7 @@
Bottom = false;
return Style;
}
+
public IXLStyle WhenIsBottom(Int32 value, XLTopBottomType topBottomType = XLTopBottomType.Items)
{
Values.Initialize(new XLFormula(value));
@@ -462,6 +510,7 @@
ConditionalFormatType = XLConditionalFormatType.ColorScale;
return new XLCFColorScaleMin(this);
}
+
public IXLCFDataBarMin DataBar(XLColor color, Boolean showBarOnly = false)
{
Colors.Initialize(color);
@@ -469,6 +518,7 @@
ConditionalFormatType = XLConditionalFormatType.DataBar;
return new XLCFDataBarMin(this);
}
+
public IXLCFDataBarMin DataBar(XLColor positiveColor, XLColor negativeColor, Boolean showBarOnly = false)
{
Colors.Initialize(positiveColor);
@@ -477,6 +527,7 @@
ConditionalFormatType = XLConditionalFormatType.DataBar;
return new XLCFDataBarMin(this);
}
+
public IXLCFIconSet IconSet(XLIconSetStyle iconSetStyle, Boolean reverseIconOrder = false, Boolean showIconOnly = false)
{
IconSetOperators.Clear();
@@ -494,10 +545,12 @@
IEqualityComparer>
{
private readonly IEqualityComparer _valueComparer;
+
public DictionaryComparer(IEqualityComparer valueComparer = null)
{
this._valueComparer = valueComparer ?? EqualityComparer.Default;
}
+
public bool Equals(Dictionary x, Dictionary y)
{
if (x.Count != y.Count)
@@ -521,6 +574,7 @@
internal class EnumerableComparer : IEqualityComparer>
{
private readonly IEqualityComparer _valueComparer;
+
public EnumerableComparer(IEqualityComparer valueComparer = null)
{
this._valueComparer = valueComparer ?? EqualityComparer.Default;
@@ -544,4 +598,3 @@
}
}
}
-
diff --git a/ClosedXML/Excel/Coordinates/XLAddress.cs b/ClosedXML/Excel/Coordinates/XLAddress.cs
index 818ade9..e232a76 100644
--- a/ClosedXML/Excel/Coordinates/XLAddress.cs
+++ b/ClosedXML/Excel/Coordinates/XLAddress.cs
@@ -390,6 +390,11 @@
return GetTrimmedAddress();
}
+ internal XLAddress WithoutWorksheet()
+ {
+ return new XLAddress(RowNumber, ColumnNumber, FixedRow, FixedColumn);
+ }
+
public String ToStringFixed(XLReferenceStyle referenceStyle)
{
return ToStringFixed(referenceStyle, false);
diff --git a/ClosedXML/Excel/Drawings/IXLPicture.cs b/ClosedXML/Excel/Drawings/IXLPicture.cs
index cf167d2..cf2245d 100644
--- a/ClosedXML/Excel/Drawings/IXLPicture.cs
+++ b/ClosedXML/Excel/Drawings/IXLPicture.cs
@@ -9,6 +9,19 @@
IXLAddress BottomRightCellAddress { get; }
///
+ /// Create a copy of the picture on a different worksheet.
+ ///
+ /// The worksheet to which the picture will be copied.
+ /// A created copy of the picture.
+ IXLPicture CopyTo(IXLWorksheet targetSheet);
+
+ ///
+ /// Create a copy of the picture on the same worksheet.
+ ///
+ /// A created copy of the picture.
+ IXLPicture Duplicate();
+
+ ///
/// Type of image. The supported formats are defined by OpenXML's ImagePartType.
/// Default value is "jpeg"
///
diff --git a/ClosedXML/Excel/Drawings/XLPicture.cs b/ClosedXML/Excel/Drawings/XLPicture.cs
index e79bd12..4ec84de 100644
--- a/ClosedXML/Excel/Drawings/XLPicture.cs
+++ b/ClosedXML/Excel/Drawings/XLPicture.cs
@@ -326,6 +326,67 @@
return this;
}
+ ///
+ /// Create a copy of the picture on a different worksheet.
+ ///
+ /// The worksheet to which the picture will be copied.
+ /// A created copy of the picture.
+ public IXLPicture CopyTo(IXLWorksheet targetSheet)
+ {
+ return CopyTo((XLWorksheet) targetSheet);
+ }
+
+ ///
+ /// Create a copy of the picture on the same worksheet.
+ ///
+ /// A created copy of the picture.
+ public IXLPicture Duplicate()
+ {
+ return CopyTo(Worksheet);
+ }
+
+ internal IXLPicture CopyTo(XLWorksheet targetSheet)
+ {
+ if (targetSheet == null)
+ targetSheet = Worksheet as XLWorksheet;
+
+ IXLPicture newPicture;
+ if (targetSheet == Worksheet)
+ newPicture = targetSheet.AddPicture(ImageStream, Format);
+ else
+ newPicture = targetSheet.AddPicture(ImageStream, Format, Name);
+
+ newPicture = newPicture
+ .WithPlacement(XLPicturePlacement.FreeFloating)
+ .WithSize(Width, Height)
+ .WithPlacement(Placement);
+
+ switch (Placement)
+ {
+ case XLPicturePlacement.FreeFloating:
+ newPicture.MoveTo(Left, Top);
+ break;
+
+ case XLPicturePlacement.Move:
+ var newAddress = new XLAddress(targetSheet, TopLeftCellAddress.RowNumber,
+ TopLeftCellAddress.ColumnNumber, false, false);
+ newPicture.MoveTo(newAddress, GetOffset(XLMarkerPosition.TopLeft));
+ break;
+
+ case XLPicturePlacement.MoveAndSize:
+ var newFromAddress = new XLAddress(targetSheet, TopLeftCellAddress.RowNumber,
+ TopLeftCellAddress.ColumnNumber, false, false);
+ var newToAddress = new XLAddress(targetSheet, BottomRightCellAddress.RowNumber,
+ BottomRightCellAddress.ColumnNumber, false, false);
+
+ newPicture.MoveTo(newFromAddress, GetOffset(XLMarkerPosition.TopLeft), newToAddress,
+ GetOffset(XLMarkerPosition.BottomRight));
+ break;
+ }
+
+ return newPicture;
+ }
+
internal void SetName(string value)
{
if (String.IsNullOrWhiteSpace(value))
diff --git a/ClosedXML/Excel/NamedRanges/IXLNamedRange.cs b/ClosedXML/Excel/NamedRanges/IXLNamedRange.cs
index 0c21e4c..d47e3bc 100644
--- a/ClosedXML/Excel/NamedRanges/IXLNamedRange.cs
+++ b/ClosedXML/Excel/NamedRanges/IXLNamedRange.cs
@@ -10,6 +10,16 @@
public interface IXLNamedRange
{
+ #region Public Properties
+
+ ///
+ /// Gets or sets the comment for this named range.
+ ///
+ ///
+ /// The comment for this named range.
+ ///
+ String Comment { get; set; }
+
///
/// Gets or sets the name of the range.
///
@@ -23,14 +33,12 @@
/// Note: A named range can point to multiple ranges.
///
IXLRanges Ranges { get; }
+ String RefersTo { get; set; }
///
- /// Gets or sets the comment for this named range.
+ /// Gets the scope of this named range.
///
- ///
- /// The comment for this named range.
- ///
- String Comment { get; set; }
+ XLNamedRangeScope Scope { get; }
///
/// Gets or sets the visibility of this named range.
@@ -40,10 +48,9 @@
///
Boolean Visible { get; set; }
- ///
- /// Gets the scope of this named range.
- ///
- XLNamedRangeScope Scope { get; }
+ #endregion Public Properties
+
+ #region Public Methods
///
/// Adds the specified range to this named range.
@@ -67,18 +74,18 @@
/// The ranges to add.
IXLRanges Add(IXLRanges ranges);
-
- ///
- /// Deletes this named range (not the cells).
- ///
- void Delete();
-
///
/// Clears the list of ranges associated with this named range.
/// (it does not clear the cells)
///
void Clear();
+ IXLNamedRange CopyTo(IXLWorksheet targetSheet);
+
+ ///
+ /// Deletes this named range (not the cells).
+ ///
+ void Delete();
///
/// Removes the specified range from this named range.
/// Note: A named range can point to multiple ranges.
@@ -100,11 +107,12 @@
/// The ranges to remove.
void Remove(IXLRanges ranges);
-
IXLNamedRange SetRefersTo(String range);
+
IXLNamedRange SetRefersTo(IXLRangeBase range);
+
IXLNamedRange SetRefersTo(IXLRanges ranges);
- String RefersTo { get; set; }
+ #endregion Public Methods
}
}
diff --git a/ClosedXML/Excel/NamedRanges/XLNamedRange.cs b/ClosedXML/Excel/NamedRanges/XLNamedRange.cs
index 6920917..4943a4f 100644
--- a/ClosedXML/Excel/NamedRanges/XLNamedRange.cs
+++ b/ClosedXML/Excel/NamedRanges/XLNamedRange.cs
@@ -149,6 +149,24 @@
}
}
+ public IXLNamedRange CopyTo(IXLWorksheet targetSheet)
+ {
+ if (targetSheet == _namedRanges.Worksheet)
+ throw new InvalidOperationException("Cannot copy named range to the worksheet it already belongs to.");
+
+ var ranges = new XLRanges();
+ foreach (var r in Ranges)
+ {
+ if (_namedRanges.Worksheet == r.Worksheet)
+ // Named ranges on the source worksheet have to point to the new destination sheet
+ ranges.Add(targetSheet.Range(((XLRangeAddress)r.RangeAddress).WithoutWorksheet()));
+ else
+ ranges.Add(r);
+ }
+
+ return targetSheet.NamedRanges.Add(Name, ranges);
+ }
+
internal IList RangeList { get; set; } = new List();
public IXLNamedRange SetRefersTo(String range)
diff --git a/ClosedXML/Excel/PageSetup/XLPageSetup.cs b/ClosedXML/Excel/PageSetup/XLPageSetup.cs
index f241e90..066e6a3 100644
--- a/ClosedXML/Excel/PageSetup/XLPageSetup.cs
+++ b/ClosedXML/Excel/PageSetup/XLPageSetup.cs
@@ -18,6 +18,12 @@
HorizontalDpi = defaultPageOptions.HorizontalDpi;
PageOrientation = defaultPageOptions.PageOrientation;
VerticalDpi = defaultPageOptions.VerticalDpi;
+ FirstRowToRepeatAtTop = defaultPageOptions.FirstRowToRepeatAtTop;
+ LastRowToRepeatAtTop = defaultPageOptions.LastRowToRepeatAtTop;
+ FirstColumnToRepeatAtLeft = defaultPageOptions.FirstColumnToRepeatAtLeft;
+ LastColumnToRepeatAtLeft = defaultPageOptions.LastColumnToRepeatAtLeft;
+ ShowComments = defaultPageOptions.ShowComments;
+
PaperSize = defaultPageOptions.PaperSize;
_pagesTall = defaultPageOptions.PagesTall;
diff --git a/ClosedXML/Excel/Ranges/XLRangeAddress.cs b/ClosedXML/Excel/Ranges/XLRangeAddress.cs
index 08156bd..7ebfb18 100644
--- a/ClosedXML/Excel/Ranges/XLRangeAddress.cs
+++ b/ClosedXML/Excel/Ranges/XLRangeAddress.cs
@@ -196,7 +196,7 @@
public bool Intersects(IXLRangeAddress otherAddress)
{
- var xlOtherAddress = (XLRangeAddress) otherAddress;
+ var xlOtherAddress = (XLRangeAddress)otherAddress;
return Intersects(in xlOtherAddress);
}
@@ -216,6 +216,13 @@
return Contains(in xlAddress);
}
+ internal IXLRangeAddress WithoutWorksheet()
+ {
+ return new XLRangeAddress(
+ FirstAddress.WithoutWorksheet(),
+ LastAddress.WithoutWorksheet());
+ }
+
internal bool Contains(in XLAddress address)
{
return FirstAddress.RowNumber <= address.RowNumber &&
@@ -300,6 +307,7 @@
{
return !(left == right);
}
- #endregion
+
+ #endregion Operators
}
}
diff --git a/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs
index 9c70795..fb76125 100644
--- a/ClosedXML/Excel/Ranges/XLRangeBase.cs
+++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs
@@ -912,13 +912,16 @@
public XLRange Range(IXLRangeAddress rangeAddress)
{
- var newFirstCellAddress = new XLAddress((XLWorksheet)rangeAddress.FirstAddress.Worksheet,
+ var ws = (XLWorksheet) rangeAddress.FirstAddress.Worksheet ??
+ (XLWorksheet) rangeAddress.LastAddress.Worksheet ??
+ Worksheet;
+ var newFirstCellAddress = new XLAddress(ws,
rangeAddress.FirstAddress.RowNumber + RangeAddress.FirstAddress.RowNumber - 1,
rangeAddress.FirstAddress.ColumnNumber + RangeAddress.FirstAddress.ColumnNumber - 1,
rangeAddress.FirstAddress.FixedRow,
rangeAddress.FirstAddress.FixedColumn);
- var newLastCellAddress = new XLAddress((XLWorksheet)rangeAddress.LastAddress.Worksheet,
+ var newLastCellAddress = new XLAddress(ws,
rangeAddress.LastAddress.RowNumber + RangeAddress.FirstAddress.RowNumber - 1,
rangeAddress.LastAddress.ColumnNumber + RangeAddress.FirstAddress.ColumnNumber - 1,
rangeAddress.LastAddress.FixedRow,
diff --git a/ClosedXML/Excel/Rows/XLRow.cs b/ClosedXML/Excel/Rows/XLRow.cs
index 36dbe9b..2ecf297 100644
--- a/ClosedXML/Excel/Rows/XLRow.cs
+++ b/ClosedXML/Excel/Rows/XLRow.cs
@@ -407,6 +407,7 @@
row.Clear();
var newRow = (XLRow)row;
newRow._height = _height;
+ newRow.HeightChanged = HeightChanged;
newRow.InnerStyle = GetStyle();
AsRange().CopyTo(row);
diff --git a/ClosedXML/Excel/Tables/IXLTable.cs b/ClosedXML/Excel/Tables/IXLTable.cs
index 1553973..1b47e86 100644
--- a/ClosedXML/Excel/Tables/IXLTable.cs
+++ b/ClosedXML/Excel/Tables/IXLTable.cs
@@ -129,5 +129,7 @@
///
///
DataTable AsNativeDataTable();
+
+ IXLTable CopyTo(IXLWorksheet targetSheet);
}
-}
\ No newline at end of file
+}
diff --git a/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/Excel/Tables/XLTable.cs
index b7e15f6..b75900d 100644
--- a/ClosedXML/Excel/Tables/XLTable.cs
+++ b/ClosedXML/Excel/Tables/XLTable.cs
@@ -819,5 +819,46 @@
return table;
}
+
+ public IXLTable CopyTo(IXLWorksheet targetSheet)
+ {
+ return CopyTo((XLWorksheet) targetSheet);
+ }
+
+ internal IXLTable CopyTo(XLWorksheet targetSheet, bool copyData = true)
+ {
+ if (targetSheet == Worksheet)
+ throw new InvalidOperationException("Cannot copy table to the worksheet it already belongs to.");
+
+ var targetRange = targetSheet.Range(RangeAddress.WithoutWorksheet());
+ if (copyData)
+ RangeUsed().CopyTo(targetRange);
+ else
+ HeadersRow().CopyTo(targetRange.FirstRow());
+
+ String tableName = Name;
+ var newTable = (XLTable)targetSheet.Table(targetRange, tableName, true);
+
+ newTable.RelId = RelId;
+ newTable.EmphasizeFirstColumn = EmphasizeFirstColumn;
+ newTable.EmphasizeLastColumn = EmphasizeLastColumn;
+ newTable.ShowRowStripes = ShowRowStripes;
+ newTable.ShowColumnStripes = ShowColumnStripes;
+ newTable.ShowAutoFilter = ShowAutoFilter;
+ newTable.Theme = Theme;
+ newTable._showTotalsRow = ShowTotalsRow;
+
+ Int32 fieldCount = ColumnCount();
+ for (Int32 f = 0; f < fieldCount; f++)
+ {
+ var tableField = newTable.Field(f) as XLTableField;
+ var tField = Field(f) as XLTableField;
+ tableField.Index = tField.Index;
+ tableField.Name = tField.Name;
+ tableField.totalsRowLabel = tField.totalsRowLabel;
+ tableField.totalsRowFunction = tField.totalsRowFunction;
+ }
+ return newTable;
+ }
}
}
diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs
index a8946b3..443a48c 100644
--- a/ClosedXML/Excel/XLWorksheet.cs
+++ b/ClosedXML/Excel/XLWorksheet.cs
@@ -567,7 +567,7 @@
var targetSheet = (XLWorksheet)workbook.WorksheetsInternal.Add(newSheetName, position);
Internals.ColumnsCollection.ForEach(kp => kp.Value.CopyTo(targetSheet.Column(kp.Key)));
Internals.RowsCollection.ForEach(kp => kp.Value.CopyTo(targetSheet.Row(kp.Key)));
- Internals.CellsCollection.GetCells().ForEach(c => targetSheet.Cell(c.Address).CopyFrom(c, false));
+ Internals.CellsCollection.GetCells().ForEach(c => targetSheet.Cell(c.Address).CopyFrom(c, false, false));
DataValidations.ForEach(dv => targetSheet.DataValidations.Add(new XLDataValidation(dv)));
targetSheet.Visibility = Visibility;
targetSheet.ColumnWidth = ColumnWidth;
@@ -580,90 +580,18 @@
(targetSheet.PageSetup.Footer as XLHeaderFooter).Changed = true;
targetSheet.Outline = new XLOutline(Outline);
targetSheet.SheetView = new XLSheetView(SheetView);
+ targetSheet.SelectedRanges.RemoveAll();
- foreach (var picture in Pictures)
- {
- var newPic = targetSheet.AddPicture(picture.ImageStream, picture.Format, picture.Name)
- .WithPlacement(XLPicturePlacement.FreeFloating)
- .WithSize(picture.Width, picture.Height)
- .WithPlacement(picture.Placement);
-
- switch (picture.Placement)
- {
- case XLPicturePlacement.FreeFloating:
- newPic.MoveTo(picture.Left, picture.Top);
- break;
-
- case XLPicturePlacement.Move:
- var newAddress = new XLAddress(targetSheet, picture.TopLeftCellAddress.RowNumber,
- picture.TopLeftCellAddress.ColumnNumber, false, false);
- newPic.MoveTo(newAddress, picture.GetOffset(XLMarkerPosition.TopLeft));
- break;
-
- case XLPicturePlacement.MoveAndSize:
- var newFromAddress = new XLAddress(targetSheet, picture.TopLeftCellAddress.RowNumber,
- picture.TopLeftCellAddress.ColumnNumber, false, false);
- var newToAddress = new XLAddress(targetSheet, picture.BottomRightCellAddress.RowNumber,
- picture.BottomRightCellAddress.ColumnNumber, false, false);
-
- newPic.MoveTo(newFromAddress, picture.GetOffset(XLMarkerPosition.TopLeft), newToAddress,
- picture.GetOffset(XLMarkerPosition.BottomRight));
- break;
- }
- }
-
- foreach (var nr in NamedRanges)
- {
- var ranges = new XLRanges();
- foreach (var r in nr.Ranges)
- {
- if (this == r.Worksheet)
- // Named ranges on the source worksheet have to point to the new destination sheet
- ranges.Add(targetSheet.Range(r.RangeAddress.FirstAddress.RowNumber,
- r.RangeAddress.FirstAddress.ColumnNumber, r.RangeAddress.LastAddress.RowNumber,
- r.RangeAddress.LastAddress.ColumnNumber));
- else
- ranges.Add(r);
- }
-
- targetSheet.NamedRanges.Add(nr.Name, ranges);
- }
-
- foreach (var t in Tables.Cast())
- {
- String tableName = t.Name;
- var table = (XLTable)targetSheet.Table(targetSheet.Range(t.RangeAddress.ToString()), tableName, true);
-
- table.RelId = t.RelId;
- table.EmphasizeFirstColumn = t.EmphasizeFirstColumn;
- table.EmphasizeLastColumn = t.EmphasizeLastColumn;
- table.ShowRowStripes = t.ShowRowStripes;
- table.ShowColumnStripes = t.ShowColumnStripes;
- table.ShowAutoFilter = t.ShowAutoFilter;
- table.Theme = t.Theme;
- table._showTotalsRow = t.ShowTotalsRow;
- table._uniqueNames.Clear();
-
- t._uniqueNames.ForEach(n => table._uniqueNames.Add(n));
- Int32 fieldCount = t.ColumnCount();
- for (Int32 f = 0; f < fieldCount; f++)
- {
- var tableField = table.Field(f) as XLTableField;
- var tField = t.Field(f) as XLTableField;
- tableField.Index = tField.Index;
- tableField.Name = tField.Name;
- tableField.totalsRowLabel = tField.totalsRowLabel;
- tableField.totalsRowFunction = tField.totalsRowFunction;
- }
- }
+ Pictures.ForEach(picture => picture.CopyTo(targetSheet));
+ NamedRanges.ForEach(nr => nr.CopyTo(targetSheet));
+ Tables.Cast().ForEach(t => t.CopyTo(targetSheet, false));
+ ConditionalFormats.ForEach(cf => cf.CopyTo(targetSheet));
+ MergedRanges.ForEach(mr => targetSheet.Range(((XLRangeAddress)mr.RangeAddress).WithoutWorksheet()).Merge());
+ SelectedRanges.ForEach(sr => targetSheet.SelectedRanges.Add(targetSheet.Range(((XLRangeAddress)sr.RangeAddress).WithoutWorksheet())));
if (AutoFilter.Enabled)
{
- var range = targetSheet.Range(
- AutoFilter.Range.RangeAddress.FirstAddress.RowNumber,
- AutoFilter.Range.RangeAddress.FirstAddress.ColumnNumber,
- AutoFilter.Range.RangeAddress.LastAddress.RowNumber,
- AutoFilter.Range.RangeAddress.LastAddress.ColumnNumber);
+ var range = targetSheet.Range(((XLRangeAddress)AutoFilter.Range.RangeAddress).WithoutWorksheet());
range.SetAutoFilter();
}
diff --git a/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatCopyTests.cs b/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatCopyTests.cs
index 5ef7095..f76a72c 100644
--- a/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatCopyTests.cs
+++ b/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatCopyTests.cs
@@ -1,4 +1,5 @@
-using System.Linq;
+using System;
+using System.Linq;
using ClosedXML.Excel;
using NUnit.Framework;
@@ -39,7 +40,6 @@
Assert.AreEqual("B2:B2", ws.ConditionalFormats.First().Ranges.Last().RangeAddress.ToString());
}
-
[Test]
public void CopyConditionalFormatSameRange()
{
@@ -76,5 +76,39 @@
Assert.AreEqual("A1:A1", ws1.ConditionalFormats.First().Ranges.First().RangeAddress.ToString());
Assert.AreEqual("B2:B2", ws2.ConditionalFormats.First().Ranges.First().RangeAddress.ToString());
}
+
+ [Test]
+ public void FullCopyConditionalFormatSameWorksheet()
+ {
+ var wb = new XLWorkbook();
+ var ws1 = wb.Worksheets.Add("Sheet1");
+ var format = (XLConditionalFormat)ws1.Range("A1:A1").AddConditionalFormat();
+ format.WhenEquals("=" + format.Ranges.First().FirstCell().CellRight(4).Address.ToStringRelative()).Fill
+ .SetBackgroundColor(XLColor.Blue);
+
+ TestDelegate action = () => format.CopyTo(ws1);
+
+ Assert.Throws(typeof(InvalidOperationException), action);
+
+ }
+ [Test]
+ public void FullCopyConditionalFormatDifferentWorksheets()
+ {
+ var wb = new XLWorkbook();
+ var ws1 = wb.Worksheets.Add("Sheet1");
+ var format = (XLConditionalFormat)ws1.Range("A1:C3").AddConditionalFormat();
+ format.WhenEquals("=" + format.Ranges.First().FirstCell().CellRight(4).Address.ToStringRelative()).Fill
+ .SetBackgroundColor(XLColor.Blue);
+ var ws2 = wb.Worksheets.Add("Sheet2");
+
+ format.CopyTo(ws2);
+
+ Assert.AreEqual(1, ws1.ConditionalFormats.Count());
+ Assert.AreEqual(1, ws2.ConditionalFormats.Count());
+ Assert.AreEqual(1, ws1.ConditionalFormats.First().Ranges.Count);
+ Assert.AreEqual(1, ws2.ConditionalFormats.First().Ranges.Count);
+ Assert.AreEqual("Sheet1!A1:C3", ws1.ConditionalFormats.First().Ranges.First().RangeAddress.ToString(XLReferenceStyle.A1, true));
+ Assert.AreEqual("Sheet2!A1:C3", ws2.ConditionalFormats.First().Ranges.First().RangeAddress.ToString(XLReferenceStyle.A1, true));
+ }
}
}
diff --git a/ClosedXML_Tests/Excel/ImageHandling/PictureTests.cs b/ClosedXML_Tests/Excel/ImageHandling/PictureTests.cs
index a52cef1..c071eb8 100644
--- a/ClosedXML_Tests/Excel/ImageHandling/PictureTests.cs
+++ b/ClosedXML_Tests/Excel/ImageHandling/PictureTests.cs
@@ -329,5 +329,72 @@
}
}
}
+
+ [Test]
+ public void CopyImageSameWorksheet()
+ {
+ var wb = new XLWorkbook();
+ var ws1 = wb.Worksheets.Add("Sheet1");
+
+ IXLPicture original;
+ using (var stream = Assembly.GetExecutingAssembly().GetManifestResourceStream("ClosedXML_Tests.Resource.Images.ImageHandling.png"))
+ {
+ original = (ws1 as XLWorksheet).AddPicture(stream, "Picture 1", 2)
+ .WithPlacement(XLPicturePlacement.FreeFloating)
+ .MoveTo(220, 155) as XLPicture;
+ }
+
+ var copy = original.Duplicate()
+ .MoveTo(300, 200) as XLPicture;
+
+ Assert.AreEqual(2, ws1.Pictures.Count());
+ Assert.AreEqual(ws1, copy.Worksheet);
+ Assert.AreEqual(original.Format, copy.Format);
+ Assert.AreEqual(original.Height, copy.Height);
+ Assert.AreEqual(original.Placement, copy.Placement);
+ Assert.AreEqual(original.TopLeftCellAddress.ToString(), copy.TopLeftCellAddress.ToString());
+ Assert.AreEqual(original.Width, copy.Width);
+ Assert.AreEqual(original.ImageStream.ToArray(), copy.ImageStream.ToArray(), "Image streams differ");
+
+ Assert.AreEqual(200, copy.Top);
+ Assert.AreEqual(300, copy.Left);
+ Assert.AreNotEqual(original.Id, copy.Id);
+ Assert.AreNotEqual(original.Name, copy.Name);
+ }
+
+ [Test]
+ public void CopyImageDifferentWorksheets()
+ {
+ var wb = new XLWorkbook();
+ var ws1 = wb.Worksheets.Add("Sheet1");
+ IXLPicture original;
+ using (var stream = Assembly.GetExecutingAssembly().GetManifestResourceStream("ClosedXML_Tests.Resource.Images.ImageHandling.png"))
+ {
+ original = (ws1 as XLWorksheet).AddPicture(stream, "Picture 1", 2)
+ .WithPlacement(XLPicturePlacement.FreeFloating)
+ .MoveTo(220, 155) as XLPicture;
+
+ }
+ var ws2 = wb.Worksheets.Add("Sheet2");
+
+ var copy = original.CopyTo(ws2);
+
+ Assert.AreEqual(1, ws1.Pictures.Count());
+ Assert.AreEqual(1, ws2.Pictures.Count());
+
+ Assert.AreEqual(ws2, copy.Worksheet);
+
+ Assert.AreEqual(original.Format, copy.Format);
+ Assert.AreEqual(original.Height, copy.Height);
+ Assert.AreEqual(original.Left, copy.Left);
+ Assert.AreEqual(original.Name, copy.Name);
+ Assert.AreEqual(original.Placement, copy.Placement);
+ Assert.AreEqual(original.Top, copy.Top);
+ Assert.AreEqual(original.TopLeftCellAddress.ToString(), copy.TopLeftCellAddress.ToString());
+ Assert.AreEqual(original.Width, copy.Width);
+ Assert.AreEqual(original.ImageStream.ToArray(), copy.ImageStream.ToArray(), "Image streams differ");
+
+ Assert.AreNotEqual(original.Id, copy.Id);
+ }
}
}
diff --git a/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs b/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs
index 1e4bc85..264838a 100644
--- a/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs
+++ b/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs
@@ -285,5 +285,43 @@
Assert.AreEqual(0.6, (double)ws1.Cell(4, 1).Value, XLHelper.Epsilon);
}
}
+
+ [Test]
+ public void CopyNamedRangeSameWorksheet()
+ {
+ var wb = new XLWorkbook();
+ var ws1 = wb.Worksheets.Add("Sheet1");
+ ws1.Range("B2:E6").AddToNamed("Named range", XLScope.Worksheet);
+ var nr = ws1.NamedRange("Named range");
+
+ TestDelegate action = () => nr.CopyTo(ws1);
+
+ Assert.Throws(typeof(InvalidOperationException), action);
+ }
+
+ [Test]
+ public void CopyNamedRangeDifferentWorksheets()
+ {
+ var wb = new XLWorkbook();
+ var ws1 = wb.Worksheets.Add("Sheet1");
+ var ws2 = wb.Worksheets.Add("Sheet2");
+ var ranges = new XLRanges();
+ ranges.Add(ws1.Range("B2:E6"));
+ ranges.Add(ws2.Range("D1:E2"));
+ var original = ws1.NamedRanges.Add("Named range", ranges);
+
+ var copy = original.CopyTo(ws2);
+
+ Assert.AreEqual(1, ws1.NamedRanges.Count());
+ Assert.AreEqual(1, ws2.NamedRanges.Count());
+ Assert.AreEqual(2, original.Ranges.Count);
+ Assert.AreEqual(2, copy.Ranges.Count);
+ Assert.AreEqual(original.Name, copy.Name);
+ Assert.AreEqual(original.Scope, copy.Scope);
+ Assert.AreEqual("Sheet1!B2:E6", original.Ranges.First().RangeAddress.ToString(XLReferenceStyle.A1, true));
+ Assert.AreEqual("Sheet2!D1:E2", original.Ranges.Last().RangeAddress.ToString(XLReferenceStyle.A1, true));
+ Assert.AreEqual("Sheet2!B2:E6", copy.Ranges.First().RangeAddress.ToString(XLReferenceStyle.A1, true));
+ Assert.AreEqual("Sheet2!D1:E2", copy.Ranges.Last().RangeAddress.ToString(XLReferenceStyle.A1, true));
+ }
}
}
diff --git a/ClosedXML_Tests/Excel/Saving/SavingTests.cs b/ClosedXML_Tests/Excel/Saving/SavingTests.cs
index 08587b1..abf1f42 100644
--- a/ClosedXML_Tests/Excel/Saving/SavingTests.cs
+++ b/ClosedXML_Tests/Excel/Saving/SavingTests.cs
@@ -397,5 +397,72 @@
}
}
}
+
+ [Test]
+ public void PreserveHeightOfEmptyRowsOnSaving()
+ {
+ using (var ms = new MemoryStream())
+ {
+ using (var wb = new XLWorkbook())
+ {
+ var ws = wb.AddWorksheet("Sheet1");
+ ws.RowHeight = 50;
+ ws.Row(2).Height = 0;
+ ws.Row(3).Height = 20;
+ ws.Row(4).Height = 100;
+
+ ws.CopyTo("Sheet2");
+ wb.SaveAs(ms);
+ }
+
+ ms.Seek(0, SeekOrigin.Begin);
+
+ using (var wb = new XLWorkbook(ms))
+ {
+ foreach (var sheetName in new []{"Sheet1", "Sheet2"})
+ {
+ var ws = wb.Worksheet(sheetName);
+
+ Assert.AreEqual(50, ws.Row(1).Height);
+ Assert.AreEqual(0, ws.Row(2).Height);
+ Assert.AreEqual(20, ws.Row(3).Height);
+ Assert.AreEqual(100, ws.Row(4).Height);
+ }
+ }
+ }
+ }
+
+ [Test]
+ public void PreserveWidthOfEmptyColumnsOnSaving()
+ {
+ using (var ms = new MemoryStream())
+ {
+ using (var wb = new XLWorkbook())
+ {
+ var ws = wb.AddWorksheet("Sheet1");
+ ws.Column(2).Width = 0;
+ ws.Column(3).Width = 20;
+ ws.Column(4).Width = 100;
+
+ ws.CopyTo("Sheet2");
+ wb.SaveAs(ms);
+ }
+
+ ms.Seek(0, SeekOrigin.Begin);
+
+ using (var wb = new XLWorkbook(ms))
+ {
+ foreach (var sheetName in new[] {"Sheet1", "Sheet2"})
+ {
+ var ws = wb.Worksheet(sheetName);
+
+ Assert.AreEqual(ws.ColumnWidth, ws.Column(1).Width);
+ Assert.AreEqual(0, ws.Column(2).Width);
+ Assert.AreEqual(20, ws.Column(3).Width);
+ Assert.AreEqual(100, ws.Column(4).Width);
+ }
+ }
+ }
+ }
}
}
diff --git a/ClosedXML_Tests/Excel/Tables/TablesTests.cs b/ClosedXML_Tests/Excel/Tables/TablesTests.cs
index 37bd741..193b38f 100644
--- a/ClosedXML_Tests/Excel/Tables/TablesTests.cs
+++ b/ClosedXML_Tests/Excel/Tables/TablesTests.cs
@@ -781,6 +781,137 @@
}
+ [Test]
+ public void CopyTableSameWorksheet()
+ {
+ var wb = new XLWorkbook();
+ var ws1 = wb.Worksheets.Add("Sheet1");
+
+ var table = ws1.Range("A1:C2").AsTable();
+
+ TestDelegate action = () => table.CopyTo(ws1);
+
+ Assert.Throws(typeof(InvalidOperationException), action);
+
+ }
+
+ [Test]
+ public void CopyDetachedTableDifferentWorksheets()
+ {
+ var wb = new XLWorkbook();
+ var ws1 = wb.Worksheets.Add("Sheet1");
+ ws1.Cell("A1").Value = "Custom column 1";
+ ws1.Cell("B1").Value = "Custom column 2";
+ ws1.Cell("C1").Value = "Custom column 3";
+ ws1.Cell("A2").Value = "Value 1";
+ ws1.Cell("B2").Value = 123.45;
+ ws1.Cell("C2").Value = new DateTime(2018, 5, 10);
+ var original = ws1.Range("A1:C2").AsTable("Detached table");
+ var ws2 = wb.Worksheets.Add("Sheet2");
+
+ var copy = original.CopyTo(ws2);
+
+ Assert.AreEqual(0, ws1.Tables.Count()); // We did not add it
+ Assert.AreEqual(1, ws2.Tables.Count());
+
+ AssertTablesAreEqual(original, copy);
+
+ Assert.AreEqual("Sheet2!A1:C2", copy.RangeAddress.ToString(XLReferenceStyle.A1, true));
+ Assert.AreEqual("Custom column 1", ws2.Cell("A1").Value);
+ Assert.AreEqual("Custom column 2", ws2.Cell("B1").Value);
+ Assert.AreEqual("Custom column 3", ws2.Cell("C1").Value);
+ Assert.AreEqual("Value 1", ws2.Cell("A2").Value);
+ Assert.AreEqual(123.45, (double)ws2.Cell("B2").Value, XLHelper.Epsilon);
+ Assert.AreEqual(new DateTime(2018, 5, 10), ws2.Cell("C2").Value);
+ }
+
+ [Test]
+ public void CopyTableDifferentWorksheets()
+ {
+ var wb = new XLWorkbook();
+ var ws1 = wb.Worksheets.Add("Sheet1");
+ ws1.Cell("A1").Value = "Custom column 1";
+ ws1.Cell("B1").Value = "Custom column 2";
+ ws1.Cell("C1").Value = "Custom column 3";
+ ws1.Cell("A2").Value = "Value 1";
+ ws1.Cell("B2").Value = 123.45;
+ ws1.Cell("C2").Value = new DateTime(2018, 5, 10);
+ var original = ws1.Range("A1:C2").AsTable("Attached table");
+ ws1.Tables.Add(original);
+ var ws2 = wb.Worksheets.Add("Sheet2");
+
+ original.CopyTo(ws2);
+
+ Assert.AreEqual(1, ws1.Tables.Count());
+ Assert.AreEqual(1, ws2.Tables.Count());
+
+ var copy = ws2.Tables.First();
+
+ AssertTablesAreEqual(original, copy);
+
+ Assert.AreEqual("Sheet2!A1:C2", copy.RangeAddress.ToString(XLReferenceStyle.A1, true));
+ Assert.AreEqual("Custom column 1", ws2.Cell("A1").Value);
+ Assert.AreEqual("Custom column 2", ws2.Cell("B1").Value);
+ Assert.AreEqual("Custom column 3", ws2.Cell("C1").Value);
+ Assert.AreEqual("Value 1", ws2.Cell("A2").Value);
+ Assert.AreEqual(123.45, (double)ws2.Cell("B2").Value, XLHelper.Epsilon);
+ Assert.AreEqual(new DateTime(2018, 5, 10), ws2.Cell("C2").Value);
+ }
+
+ [Test]
+ public void CopyTableWithoutData()
+ {
+ var wb = new XLWorkbook();
+ var ws1 = wb.Worksheets.Add("Sheet1");
+ ws1.Cell("A1").Value = "Custom column 1";
+ ws1.Cell("B1").Value = "Custom column 2";
+ ws1.Cell("C1").Value = "Custom column 3";
+ ws1.Cell("A2").Value = "Value 1";
+ ws1.Cell("B2").Value = 123.45;
+ ws1.Cell("C2").Value = new DateTime(2018, 5, 10);
+ var original = ws1.Range("A1:C2").AsTable("Attached table");
+ ws1.Tables.Add(original);
+ var ws2 = wb.Worksheets.Add("Sheet2") as XLWorksheet;
+
+ var copy = (original as XLTable).CopyTo(ws2, false);
+
+ AssertTablesAreEqual(original, copy);
+
+ Assert.AreEqual("Sheet2!A1:C2", copy.RangeAddress.ToString(XLReferenceStyle.A1, true));
+ Assert.AreEqual("Custom column 1", ws2.Cell("A1").Value);
+ Assert.AreEqual("Custom column 2", ws2.Cell("B1").Value);
+ Assert.AreEqual("Custom column 3", ws2.Cell("C1").Value);
+ Assert.AreEqual("", ws2.Cell("A2").Value);
+ Assert.AreEqual("", ws2.Cell("B2").Value);
+ Assert.AreEqual("", ws2.Cell("C2").Value);
+ }
+
+ private void AssertTablesAreEqual(IXLTable table1, IXLTable table2)
+ {
+ Assert.AreEqual(table1.RangeAddress.ToString(XLReferenceStyle.A1, false), table2.RangeAddress.ToString(XLReferenceStyle.A1, false));
+ Assert.AreEqual(table1.Fields.Count(), table2.Fields.Count());
+ for (int j = 0; j < table1.Fields.Count(); j++)
+ {
+ var originalField = table1.Fields.ElementAt(j);
+ var copyField = table2.Fields.ElementAt(j);
+ Assert.AreEqual(originalField.Name, copyField.Name);
+ if (table1.ShowTotalsRow)
+ {
+ Assert.AreEqual(originalField.TotalsRowFormulaA1, copyField.TotalsRowFormulaA1);
+ Assert.AreEqual(originalField.TotalsRowFunction, copyField.TotalsRowFunction);
+ }
+ }
+
+ Assert.AreEqual(table1.Name, table2.Name);
+ Assert.AreEqual(table1.ShowAutoFilter, table2.ShowAutoFilter);
+ Assert.AreEqual(table1.ShowColumnStripes, table2.ShowColumnStripes);
+ Assert.AreEqual(table1.ShowHeaderRow, table2.ShowHeaderRow);
+ Assert.AreEqual(table1.ShowRowStripes, table2.ShowRowStripes);
+ Assert.AreEqual(table1.ShowTotalsRow, table2.ShowTotalsRow);
+ Assert.AreEqual((table1.Style as XLStyle).Value, (table2.Style as XLStyle).Value);
+ Assert.AreEqual(table1.Theme, table2.Theme);
+ }
+
//TODO: Delete table (not underlying range)
}
}
diff --git a/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs b/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs
index 632a27c..3e97b4c 100644
--- a/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs
+++ b/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs
@@ -1,8 +1,11 @@
using ClosedXML.Excel;
+using ClosedXML.Excel.Drawings;
using NUnit.Framework;
using System;
+using System.Drawing;
using System.IO;
using System.Linq;
+using System.Reflection;
namespace ClosedXML_Tests
{
@@ -25,7 +28,8 @@
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Sheet1");
- ws.FirstCell().AddConditionalFormat().WhenContains("1").Fill.SetBackgroundColor(XLColor.Blue);
+ ws.Range("A1:C3").AddConditionalFormat().WhenContains("1").Fill.SetBackgroundColor(XLColor.Blue);
+ ws.Range("A1:C3").Value = 1;
IXLWorksheet ws2 = ws.CopyTo("Sheet2");
Assert.AreEqual(1, ws2.ConditionalFormats.Count());
}
@@ -296,5 +300,520 @@
Assert.DoesNotThrow(saveAndOpenWorkbook);
Assert.AreEqual(title, savedTitle);
}
+
+ [Test]
+ public void CopyWorksheetPreservesContents()
+ {
+ using (var wb1 = new XLWorkbook())
+ using (var wb2 = new XLWorkbook())
+ {
+ var ws1 = wb1.Worksheets.Add("Original");
+
+ ws1.Cell("A1").Value = "A1 value";
+ ws1.Cell("A2").Value = 100;
+ ws1.Cell("D4").Value = new DateTime(2018, 5, 1);
+
+ var ws2 = ws1.CopyTo(wb2, "Copy");
+
+ Assert.AreEqual("A1 value", ws2.Cell("A1").Value);
+ Assert.AreEqual(100, ws2.Cell("A2").Value);
+ Assert.AreEqual(new DateTime(2018, 5, 1), ws2.Cell("D4").Value);
+ }
+ }
+
+ [Test]
+ public void CopyWorksheetPreservesFormulae()
+ {
+ using (var wb1 = new XLWorkbook())
+ using (var wb2 = new XLWorkbook())
+ {
+ var ws1 = wb1.Worksheets.Add("Original");
+
+ ws1.Cell("A1").FormulaA1 = "10*10";
+ ws1.Cell("A2").FormulaA1 = "A1 * 2";
+
+ var ws2 = ws1.CopyTo(wb2, "Copy");
+
+ Assert.AreEqual("10*10", ws2.Cell("A1").FormulaA1);
+ Assert.AreEqual("A1 * 2", ws2.Cell("A2").FormulaA1);
+ }
+ }
+
+ [Test]
+ public void CopyWorksheetPreservesRowHeights()
+ {
+ using (var wb1 = new XLWorkbook())
+ {
+ var ws1 = wb1.Worksheets.Add("Original");
+ using (var wb2 = new XLWorkbook())
+ {
+ ws1.RowHeight = 55;
+ ws1.Row(2).Height = 0;
+ ws1.Row(3).Height = 20;
+
+ var ws2 = ws1.CopyTo(wb2, "Copy");
+
+ Assert.AreEqual(ws1.RowHeight, ws2.RowHeight);
+ for (int i = 1; i <= 3; i++)
+ {
+ Assert.AreEqual(ws1.Row(i).Height, ws2.Row(i).Height);
+ }
+ }
+ }
+ }
+
+ [Test]
+ public void CopyWorksheetPreservesColumnWidths()
+ {
+ using (var wb1 = new XLWorkbook())
+ {
+ var ws1 = wb1.Worksheets.Add("Original");
+ using (var wb2 = new XLWorkbook())
+ {
+ ws1.ColumnWidth = 160;
+ ws1.Column(2).Width = 0;
+ ws1.Column(3).Width = 240;
+
+ var ws2 = ws1.CopyTo(wb2, "Copy");
+
+ Assert.AreEqual(ws1.ColumnWidth, ws2.ColumnWidth);
+ for (int i = 1; i <= 3; i++)
+ {
+ Assert.AreEqual(ws1.Column(i).Width, ws2.Column(i).Width);
+ }
+ }
+ }
+ }
+
+ [Test]
+ public void CopyWorksheetPreservesMergedCells()
+ {
+ using (var wb1 = new XLWorkbook())
+ using (var wb2 = new XLWorkbook())
+ {
+ var ws1 = wb1.Worksheets.Add("Original");
+
+ ws1.Range("A:A").Merge();
+ ws1.Range("B1:C2").Merge();
+
+ var ws2 = ws1.CopyTo(wb2, "Copy");
+
+ Assert.AreEqual(ws1.MergedRanges.Count, ws2.MergedRanges.Count);
+ for (int i = 0; i < ws1.MergedRanges.Count; i++)
+ {
+ Assert.AreEqual(ws1.MergedRanges.ElementAt(i).RangeAddress.ToString(),
+ ws2.MergedRanges.ElementAt(i).RangeAddress.ToString());
+ }
+ }
+ }
+
+ [Test]
+ public void CopyWorksheetAcrossWorkbooksPreservesNamedRanges()
+ {
+ using (var wb1 = new XLWorkbook())
+ using (var wb2 = new XLWorkbook())
+ {
+ var ws1 = wb1.Worksheets.Add("Original");
+
+ ws1.Range("A1:A2").AddToNamed("GLOBAL", XLScope.Workbook);
+ ws1.Ranges("B1:B2,D1:D2").AddToNamed("LOCAL", XLScope.Worksheet);
+
+ var ws2 = ws1.CopyTo(wb2, "Copy");
+
+ Assert.AreEqual(ws1.NamedRanges.Count(), ws2.NamedRanges.Count());
+ for (int i = 0; i < ws1.NamedRanges.Count(); i++)
+ {
+ var nr1 = ws1.NamedRanges.ElementAt(i);
+ var nr2 = ws2.NamedRanges.ElementAt(i);
+ Assert.AreEqual(nr1.Ranges.ToString(), nr2.Ranges.ToString());
+ Assert.AreEqual(nr1.Scope, nr2.Scope);
+ Assert.AreEqual(nr1.Name, nr2.Name);
+ Assert.AreEqual(nr1.Visible, nr2.Visible);
+ Assert.AreEqual(nr1.Comment, nr2.Comment);
+ }
+ }
+ }
+
+ [Test]
+ public void CopyWorksheeInsideWorkbookMakesNamedRangesLocal()
+ {
+ using (var wb1 = new XLWorkbook())
+ {
+ var ws1 = wb1.Worksheets.Add("Original");
+
+ ws1.Range("A1:A2").AddToNamed("GLOBAL", XLScope.Workbook);
+ ws1.Ranges("B1:B2,D1:D2").AddToNamed("LOCAL", XLScope.Worksheet);
+
+ var ws2 = ws1.CopyTo("Copy");
+
+ Assert.AreEqual(ws1.NamedRanges.Count(), ws2.NamedRanges.Count());
+ for (int i = 0; i < ws1.NamedRanges.Count(); i++)
+ {
+ var nr1 = ws1.NamedRanges.ElementAt(i);
+ var nr2 = ws2.NamedRanges.ElementAt(i);
+
+ Assert.AreEqual(XLScope.Worksheet, nr2.Scope);
+
+ Assert.AreEqual(nr1.Ranges.ToString(), nr2.Ranges.ToString());
+ Assert.AreEqual(nr1.Name, nr2.Name);
+ Assert.AreEqual(nr1.Visible, nr2.Visible);
+ Assert.AreEqual(nr1.Comment, nr2.Comment);
+ }
+ }
+ }
+
+ [Test]
+ public void CopyWorksheetPreservesStyles()
+ {
+ using (var ms = new MemoryStream())
+ using (var wb1 = new XLWorkbook())
+ {
+ var ws1 = wb1.Worksheets.Add("Original");
+
+ ws1.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
+ ws1.Range("A1:B2").Style.Font.FontSize = 25;
+ ws1.Cell("C3").Style.Fill.BackgroundColor = XLColor.Red;
+ ws1.Cell("C4").Style.Fill.BackgroundColor = XLColor.AliceBlue;
+ ws1.Cell("C4").Value = "Non empty";
+
+ using (var wb2 = new XLWorkbook())
+ {
+ var ws2 = ws1.CopyTo(wb2, "Copy");
+ AssertStylesAreEqual(ws1, ws2);
+ wb2.SaveAs(ms);
+ }
+
+ using (var wb2 = new XLWorkbook(ms))
+ {
+ var ws2 = wb2.Worksheet("Copy");
+ AssertStylesAreEqual(ws1, ws2);
+ }
+ }
+
+ void AssertStylesAreEqual(IXLWorksheet ws1, IXLWorksheet ws2)
+ {
+ Assert.AreEqual((ws1.Style as XLStyle).Value, (ws2.Style as XLStyle).Value,
+ "Worksheet styles differ");
+ var cellsUsed = ws1.Range(ws1.FirstCell(), ws1.LastCellUsed()).Cells();
+ foreach (var cell in cellsUsed)
+ {
+ var style1 = (cell.Style as XLStyle).Value;
+ var style2 = (ws2.Cell(cell.Address.ToString()).Style as XLStyle).Value;
+ Assert.AreEqual(style1, style2, $"Cell {cell.Address} styles differ");
+ }
+ }
+ }
+
+ [Test]
+ public void CopyWorksheetPreservesConditionalFormats()
+ {
+ using (var wb1 = new XLWorkbook())
+ using (var wb2 = new XLWorkbook())
+ {
+ var ws1 = wb1.Worksheets.Add("Original");
+
+ ws1.Range("A:A").AddConditionalFormat()
+ .WhenContains("0").Fill.SetBackgroundColor(XLColor.Red);
+ var cf = ws1.Range("B1:C2").AddConditionalFormat();
+ cf.Ranges.Add(ws1.Range("D4:D5"));
+ cf.WhenEqualOrGreaterThan(100).Font.SetBold();
+
+ var ws2 = ws1.CopyTo(wb2, "Copy");
+
+ Assert.AreEqual(ws1.ConditionalFormats.Count(), ws2.ConditionalFormats.Count());
+ for (int i = 0; i < ws1.ConditionalFormats.Count(); i++)
+ {
+ var original = ws1.ConditionalFormats.ElementAt(i);
+ var copy = ws2.ConditionalFormats.ElementAt(i);
+ Assert.AreEqual(original.Ranges.Count, copy.Ranges.Count);
+ for (int j = 0; j < original.Ranges.Count; j++)
+ {
+ Assert.AreEqual(original.Ranges.ElementAt(j).RangeAddress.ToString(XLReferenceStyle.A1, false),
+ copy.Ranges.ElementAt(j).RangeAddress.ToString(XLReferenceStyle.A1, false));
+ }
+
+ Assert.AreEqual((original.Style as XLStyle).Value, (copy.Style as XLStyle).Value);
+ Assert.AreEqual(original.Values.Single().Value.Value, copy.Values.Single().Value.Value);
+ }
+ }
+ }
+
+ [Test]
+ public void CopyWorksheetPreservesTables()
+ {
+ using (var wb1 = new XLWorkbook())
+ using (var wb2 = new XLWorkbook())
+ {
+ var ws1 = wb1.Worksheets.Add("Original");
+
+ ws1.Cell("A2").Value = "Name";
+ ws1.Cell("B2").Value = "Count";
+ ws1.Cell("A3").Value = "John Smith";
+ ws1.Cell("B3").Value = 50;
+ ws1.Cell("A4").Value = "Ivan Ivanov";
+ ws1.Cell("B4").Value = 40;
+ var table1 = ws1.Range("A2:B4").CreateTable("Test table 1");
+ table1
+ .SetShowAutoFilter(true)
+ .SetShowTotalsRow(true)
+ .SetEmphasizeFirstColumn(true)
+ .SetShowColumnStripes(true)
+ .SetShowRowStripes(true);
+ table1.Theme = XLTableTheme.TableStyleDark8;
+ table1.Field(1).TotalsRowFunction = XLTotalsRowFunction.Sum;
+
+ var ws2 = ws1.CopyTo(wb2, "Copy");
+
+ Assert.AreEqual(ws1.Tables.Count(), ws2.Tables.Count());
+ for (int i = 0; i < ws1.Tables.Count(); i++)
+ {
+ var original = ws1.Tables.ElementAt(i);
+ var copy = ws2.Tables.ElementAt(i);
+ Assert.AreEqual(original.RangeAddress.ToString(XLReferenceStyle.A1, false), copy.RangeAddress.ToString(XLReferenceStyle.A1, false));
+ Assert.AreEqual(original.Fields.Count(), copy.Fields.Count());
+ for (int j = 0; j < original.Fields.Count(); j++)
+ {
+ var originalField = original.Fields.ElementAt(j);
+ var copyField = copy.Fields.ElementAt(j);
+ Assert.AreEqual(originalField.Name, copyField.Name);
+ Assert.AreEqual(originalField.TotalsRowFormulaA1, copyField.TotalsRowFormulaA1);
+ Assert.AreEqual(originalField.TotalsRowFunction, copyField.TotalsRowFunction);
+ }
+
+ Assert.AreEqual(original.Name, copy.Name);
+ Assert.AreEqual(original.ShowAutoFilter, copy.ShowAutoFilter);
+ Assert.AreEqual(original.ShowColumnStripes, copy.ShowColumnStripes);
+ Assert.AreEqual(original.ShowHeaderRow, copy.ShowHeaderRow);
+ Assert.AreEqual(original.ShowRowStripes, copy.ShowRowStripes);
+ Assert.AreEqual(original.ShowTotalsRow, copy.ShowTotalsRow);
+ Assert.AreEqual((original.Style as XLStyle).Value, (copy.Style as XLStyle).Value);
+ Assert.AreEqual(original.Theme, copy.Theme);
+ }
+ }
+ }
+
+ [Test]
+ public void CopyWorksheetPreservesDataValidation()
+ {
+ using (var wb1 = new XLWorkbook())
+ using (var wb2 = new XLWorkbook())
+ {
+ var ws1 = wb1.Worksheets.Add("Original");
+
+ var dv1 = ws1.Range("A:A").SetDataValidation();
+ dv1.WholeNumber.EqualTo(2);
+ dv1.ErrorStyle = XLErrorStyle.Warning;
+ dv1.ErrorTitle = "Number out of range";
+ dv1.ErrorMessage = "This cell only allows the number 2.";
+
+ var dv2 = ws1.Ranges("B2:C3,D4:E5").SetDataValidation();
+ dv2.Decimal.GreaterThan(5);
+ dv2.ErrorStyle = XLErrorStyle.Stop;
+ dv2.ErrorTitle = "Decimal number out of range";
+ dv2.ErrorMessage = "This cell only allows decimals greater than 5.";
+
+ var dv3 = ws1.Cell("D1").SetDataValidation();
+ dv3.TextLength.EqualOrLessThan(10);
+ dv3.ErrorStyle = XLErrorStyle.Information;
+ dv3.ErrorTitle = "Text length out of range";
+ dv3.ErrorMessage = "You entered more than 10 characters.";
+
+ var ws2 = ws1.CopyTo(wb2, "Copy");
+
+ Assert.AreEqual(ws1.DataValidations.Count(), ws2.DataValidations.Count());
+ for (int i = 0; i < ws1.DataValidations.Count(); i++)
+ {
+ var original = ws1.DataValidations.ElementAt(i);
+ var copy = ws2.DataValidations.ElementAt(i);
+
+ Assert.AreEqual(original.Ranges.ToString(), copy.Ranges.ToString());
+ Assert.AreEqual(original.AllowedValues, copy.AllowedValues);
+ Assert.AreEqual(original.Operator, copy.Operator);
+ Assert.AreEqual(original.ErrorStyle, copy.ErrorStyle);
+ Assert.AreEqual(original.ErrorTitle, copy.ErrorTitle);
+ Assert.AreEqual(original.ErrorMessage, copy.ErrorMessage);
+ }
+ }
+ }
+
+ [Test]
+ public void CopyWorksheetPreservesPictures()
+ {
+ using (var ms = new MemoryStream())
+ using (var resourceStream = Assembly.GetAssembly(typeof(ClosedXML_Examples.BasicTable))
+ .GetManifestResourceStream("ClosedXML_Examples.Resources.SampleImage.jpg"))
+ using (var bitmap = Bitmap.FromStream(resourceStream) as Bitmap)
+ using (var wb1 = new XLWorkbook())
+ {
+ var ws1 = wb1.Worksheets.Add("Original");
+
+ var picture = ws1.AddPicture(bitmap, "MyPicture")
+ .WithPlacement(XLPicturePlacement.FreeFloating)
+ .MoveTo(50, 50)
+ .WithSize(200, 200);
+
+ using (var wb2 = new XLWorkbook())
+ {
+ var ws2 = ws1.CopyTo(wb2, "Copy");
+ AssertPicturesAreEqual(ws1, ws2);
+ wb2.SaveAs(ms);
+ }
+
+ using (var wb2 = new XLWorkbook(ms))
+ {
+ var ws2 = wb2.Worksheet("Copy");
+ AssertPicturesAreEqual(ws1, ws2);
+ }
+ }
+
+ void AssertPicturesAreEqual(IXLWorksheet ws1, IXLWorksheet ws2)
+ {
+ Assert.AreEqual(ws1.Pictures.Count(), ws2.Pictures.Count());
+
+ for (int i = 0; i < ws1.Pictures.Count(); i++)
+ {
+ var original = ws1.Pictures.ElementAt(i);
+ var copy = ws2.Pictures.ElementAt(i);
+ Assert.AreEqual(ws2, copy.Worksheet);
+
+ Assert.AreEqual(original.Format, copy.Format);
+ Assert.AreEqual(original.Height, copy.Height);
+ Assert.AreEqual(original.Id, copy.Id);
+ Assert.AreEqual(original.Left, copy.Left);
+ Assert.AreEqual(original.Name, copy.Name);
+ Assert.AreEqual(original.Placement, copy.Placement);
+ Assert.AreEqual(original.Top, copy.Top);
+ Assert.AreEqual(original.TopLeftCellAddress.ToString(), copy.TopLeftCellAddress.ToString());
+ Assert.AreEqual(original.Width, copy.Width);
+ Assert.AreEqual(original.ImageStream.ToArray(), copy.ImageStream.ToArray(), "Image streams differ");
+ }
+ }
+ }
+
+ [Test]
+ public void CopyWorksheetPreservesSelectedRanges()
+ {
+ using (var wb1 = new XLWorkbook())
+ using (var wb2 = new XLWorkbook())
+ {
+ var ws1 = wb1.Worksheets.Add("Original");
+
+ ws1.SelectedRanges.RemoveAll();
+ ws1.SelectedRanges.Add(ws1.Range("E12:H20"));
+ ws1.SelectedRanges.Add(ws1.Range("B:B"));
+ ws1.SelectedRanges.Add(ws1.Range("3:6"));
+
+ var ws2 = ws1.CopyTo(wb2, "Copy");
+
+ Assert.AreEqual(ws1.SelectedRanges.Count, ws2.SelectedRanges.Count);
+ for (int i = 0; i < ws1.SelectedRanges.Count; i++)
+ {
+ Assert.AreEqual(ws1.SelectedRanges.ElementAt(i).RangeAddress.ToString(),
+ ws2.SelectedRanges.ElementAt(i).RangeAddress.ToString());
+ }
+ }
+ }
+
+ [Test]
+ public void CopyWorksheetPreservesPageSetup()
+ {
+ using (var wb1 = new XLWorkbook())
+ using (var wb2 = new XLWorkbook())
+ {
+ var ws1 = wb1.Worksheets.Add("Original");
+
+ ws1.PageSetup.AddHorizontalPageBreak(15);
+ ws1.PageSetup.AddVerticalPageBreak(5);
+ ws1.PageSetup
+ .SetBlackAndWhite()
+ .SetCenterHorizontally()
+ .SetCenterVertically()
+ .SetFirstPageNumber(200)
+ .SetPageOrientation(XLPageOrientation.Landscape)
+ .SetPaperSize(XLPaperSize.A5Paper)
+ .SetScale(89)
+ .SetShowGridlines()
+ .SetHorizontalDpi(200)
+ .SetVerticalDpi(300)
+ .SetPagesTall(5)
+ .SetPagesWide(2)
+ .SetColumnsToRepeatAtLeft(1, 3);
+ ws1.PageSetup.PrintAreas.Clear();
+ ws1.PageSetup.PrintAreas.Add("A1:Z200");
+ ws1.PageSetup.Margins.SetBottom(5).SetTop(6).SetLeft(7).SetRight(8).SetFooter(9).SetHeader(10);
+ ws1.PageSetup.Header.Left.AddText(XLHFPredefinedText.FullPath, XLHFOccurrence.AllPages);
+ ws1.PageSetup.Footer.Right.AddText(XLHFPredefinedText.PageNumber, XLHFOccurrence.OddPages);
+
+ var ws2 = ws1.CopyTo(wb2, "Copy");
+
+ Assert.AreEqual(ws1.PageSetup.FirstRowToRepeatAtTop, ws2.PageSetup.FirstRowToRepeatAtTop);
+ Assert.AreEqual(ws1.PageSetup.LastRowToRepeatAtTop, ws2.PageSetup.LastRowToRepeatAtTop);
+ Assert.AreEqual(ws1.PageSetup.FirstColumnToRepeatAtLeft, ws2.PageSetup.FirstColumnToRepeatAtLeft);
+ Assert.AreEqual(ws1.PageSetup.LastColumnToRepeatAtLeft, ws2.PageSetup.LastColumnToRepeatAtLeft);
+ Assert.AreEqual(ws1.PageSetup.PageOrientation, ws2.PageSetup.PageOrientation);
+ Assert.AreEqual(ws1.PageSetup.PagesWide, ws2.PageSetup.PagesWide);
+ Assert.AreEqual(ws1.PageSetup.PagesTall, ws2.PageSetup.PagesTall);
+ Assert.AreEqual(ws1.PageSetup.Scale, ws2.PageSetup.Scale);
+ Assert.AreEqual(ws1.PageSetup.HorizontalDpi, ws2.PageSetup.HorizontalDpi);
+ Assert.AreEqual(ws1.PageSetup.VerticalDpi, ws2.PageSetup.VerticalDpi);
+ Assert.AreEqual(ws1.PageSetup.FirstPageNumber, ws2.PageSetup.FirstPageNumber);
+ Assert.AreEqual(ws1.PageSetup.CenterHorizontally, ws2.PageSetup.CenterHorizontally);
+ Assert.AreEqual(ws1.PageSetup.CenterVertically, ws2.PageSetup.CenterVertically);
+ Assert.AreEqual(ws1.PageSetup.PaperSize, ws2.PageSetup.PaperSize);
+ Assert.AreEqual(ws1.PageSetup.Margins.Bottom, ws2.PageSetup.Margins.Bottom);
+ Assert.AreEqual(ws1.PageSetup.Margins.Top, ws2.PageSetup.Margins.Top);
+ Assert.AreEqual(ws1.PageSetup.Margins.Left, ws2.PageSetup.Margins.Left);
+ Assert.AreEqual(ws1.PageSetup.Margins.Right, ws2.PageSetup.Margins.Right);
+ Assert.AreEqual(ws1.PageSetup.Margins.Footer, ws2.PageSetup.Margins.Footer);
+ Assert.AreEqual(ws1.PageSetup.Margins.Header, ws2.PageSetup.Margins.Header);
+ Assert.AreEqual(ws1.PageSetup.ScaleHFWithDocument, ws2.PageSetup.ScaleHFWithDocument);
+ Assert.AreEqual(ws1.PageSetup.AlignHFWithMargins, ws2.PageSetup.AlignHFWithMargins);
+ Assert.AreEqual(ws1.PageSetup.ShowGridlines, ws2.PageSetup.ShowGridlines);
+ Assert.AreEqual(ws1.PageSetup.ShowRowAndColumnHeadings, ws2.PageSetup.ShowRowAndColumnHeadings);
+ Assert.AreEqual(ws1.PageSetup.BlackAndWhite, ws2.PageSetup.BlackAndWhite);
+ Assert.AreEqual(ws1.PageSetup.DraftQuality, ws2.PageSetup.DraftQuality);
+ Assert.AreEqual(ws1.PageSetup.PageOrder, ws2.PageSetup.PageOrder);
+ Assert.AreEqual(ws1.PageSetup.ShowComments, ws2.PageSetup.ShowComments);
+ Assert.AreEqual(ws1.PageSetup.PrintErrorValue, ws2.PageSetup.PrintErrorValue);
+
+ Assert.AreEqual(ws1.PageSetup.PrintAreas.Count(), ws2.PageSetup.PrintAreas.Count());
+
+ Assert.AreEqual(ws1.PageSetup.Header.Left.GetText(XLHFOccurrence.AllPages), ws2.PageSetup.Header.Left.GetText(XLHFOccurrence.AllPages));
+ Assert.AreEqual(ws1.PageSetup.Footer.Right.GetText(XLHFOccurrence.OddPages), ws2.PageSetup.Footer.Right.GetText(XLHFOccurrence.OddPages));
+ }
+ }
+
+ [Test, Ignore("Muted until #836 is fixed")]
+ public void CopyWorksheetChangesAbsoluteReferencesInFormulae()
+ {
+ using (var wb1 = new XLWorkbook())
+ using (var wb2 = new XLWorkbook())
+ {
+ var ws1 = wb1.Worksheets.Add("Original");
+
+ ws1.Cell("A1").FormulaA1 = "10*10";
+ ws1.Cell("A2").FormulaA1 = "Original!A1 * 3";
+
+ var ws2 = ws1.CopyTo(wb2, "Copy");
+
+ Assert.AreEqual("Copy!A1 * 3", ws2.Cell("A2").FormulaA1);
+ }
+ }
+
+ [Test, Ignore("Muted until #836 is fixed")]
+ public void RenameWorksheetChangesAbsoluteReferencesInFormulae()
+ {
+ using (var wb1 = new XLWorkbook())
+ {
+ var ws1 = wb1.Worksheets.Add("Original");
+
+ ws1.Cell("A1").FormulaA1 = "10*10";
+ ws1.Cell("A2").FormulaA1 = "Original!A1 * 3";
+
+ ws1.Name = "Renamed";
+
+ Assert.AreEqual("Renamed!A1 * 3", ws1.Cell("A2").FormulaA1);
+ }
+ }
}
}