diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs
index 8ce4c66..394fa9d 100644
--- a/ClosedXML/Excel/Cells/XLCell.cs
+++ b/ClosedXML/Excel/Cells/XLCell.cs
@@ -102,18 +102,18 @@
get { return _worksheet; }
}
-
private int _rowNumber;
private int _columnNumber;
private bool _fixedRow;
private bool _fixedCol;
+
public XLAddress Address
{
get
{
return new XLAddress(_worksheet, _rowNumber, _columnNumber, _fixedRow, _fixedCol);
}
- internal set
+ internal set
{
if (value == null)
return;
@@ -146,27 +146,14 @@
}
}
- public XLDataValidation DataValidation
+ ///
+ /// Get the data validation rule containing current cell or create a new one if no rule was defined for cell.
+ ///
+ public IXLDataValidation DataValidation
{
get
{
- using (var asRange = AsRange())
- {
- var dv = asRange.DataValidation; // Call the data validation to break it into pieces
- foreach (var d in Worksheet.DataValidations)
- {
- var rs = d.Ranges;
- if (rs.Count == 1)
- {
- var r = rs.Single();
- var ra1 = r.RangeAddress.ToStringRelative();
- var ra2 = asRange.RangeAddress.ToStringRelative();
- if (ra1.Equals(ra2))
- return d as XLDataValidation;
- }
- }
- }
- return null;
+ return SetDataValidation();
}
}
@@ -341,7 +328,6 @@
if (_dataType == XLDataType.Boolean)
return (cValue != "0").ToExcelFormat(format);
-
else if (_dataType == XLDataType.TimeSpan || _dataType == XLDataType.DateTime || IsDateFormat())
{
double dTest;
@@ -353,7 +339,6 @@
return cValue;
}
-
else if (_dataType == XLDataType.Number)
{
double dTest;
@@ -364,7 +349,6 @@
return cValue;
}
-
else
return cValue;
}
@@ -1039,7 +1023,10 @@
if (clearOptions == XLClearOptions.Formats || clearOptions == XLClearOptions.ContentsAndFormats)
{
if (HasDataValidation)
- DataValidation.Clear();
+ {
+ var validation = NewDataValidation;
+ Worksheet.DataValidations.Delete(validation);
+ }
SetStyle(Worksheet.Style);
}
@@ -1299,19 +1286,38 @@
public Boolean HasDataValidation
{
- get
+ get { return GetDataValidation() != null; }
+ }
+
+ ///
+ /// Get the data validation rule containing current cell.
+ ///
+ /// The data validation rule applying to the current cell or null if there is no such rule.
+ private IXLDataValidation GetDataValidation()
+ {
+ foreach (var xlDataValidation in Worksheet.DataValidations)
{
- using (var asRange = AsRange())
- return Worksheet.DataValidations.Any(dv =>
- {
- using (var rngs = dv.Ranges) return dv.IsDirty() && rngs.Contains(asRange);
- });
+ foreach (var range in xlDataValidation.Ranges)
+ {
+ if (range.Contains(this))
+ return xlDataValidation;
+ }
}
+ return null;
}
public IXLDataValidation SetDataValidation()
{
- return DataValidation;
+ var validation = GetDataValidation();
+ if (validation == null)
+ {
+ using (var range = this.AsRange())
+ {
+ validation = new XLDataValidation(range);
+ Worksheet.DataValidations.Add(validation);
+ }
+ }
+ return validation;
}
public void Select()
@@ -1785,18 +1791,18 @@
foreach (var cf in formats.ToList())
{
var fmtRange = Relative(Intersection(cf.Range, fromRange), fromRange, toRange);
- var c = new XLConditionalFormat((XLRange) fmtRange, true);
+ var c = new XLConditionalFormat((XLRange)fmtRange, true);
c.CopyFrom(cf);
foreach (var v in c.Values.ToList())
{
var f = v.Value.Value;
if (v.Value.IsFormula)
{
- var r1c1 = ((XLCell) cf.Range.FirstCell()).GetFormulaR1C1(f);
+ var r1c1 = ((XLCell)cf.Range.FirstCell()).GetFormulaR1C1(f);
f = ((XLCell)fmtRange.FirstCell()).GetFormulaA1(r1c1);
}
- c.Values[v.Key] = new XLFormula {_value = f, IsFormula = v.Value.IsFormula};
+ c.Values[v.Key] = new XLFormula { _value = f, IsFormula = v.Value.IsFormula };
}
_worksheet.ConditionalFormats.Add(c);
@@ -2113,7 +2119,7 @@
rowNumber += rowsToShift;
var rowDiff = rowNumber - _rowNumber;
if (rowDiff != 0 || fixedRow)
- rowPart = fixedRow ? String.Format("R{0}", rowNumber) : String.Format("R[{0}]", rowDiff);
+ rowPart = fixedRow ? "R" + rowNumber : "R[" + rowDiff + "]";
else
rowPart = "R";
@@ -2126,7 +2132,7 @@
columnNumber += columnsToShift;
var columnDiff = columnNumber - _columnNumber;
if (columnDiff != 0 || fixedColumn)
- columnPart = fixedColumn ? String.Format("C{0}", columnNumber) : String.Format("C[{0}]", columnDiff);
+ columnPart = fixedColumn ? "C" + columnNumber : "C[" + columnDiff + "]";
else
columnPart = "C";
@@ -2214,9 +2220,9 @@
return this;
}
- internal void CopyDataValidation(XLCell otherCell, XLDataValidation otherDv)
+ internal void CopyDataValidation(XLCell otherCell, IXLDataValidation otherDv)
{
- var thisDv = DataValidation;
+ var thisDv = SetDataValidation() as XLDataValidation;
thisDv.CopyFrom(otherDv);
thisDv.Value = GetFormulaA1(otherCell.GetFormulaR1C1(otherDv.Value));
thisDv.MinValue = GetFormulaA1(otherCell.GetFormulaR1C1(otherDv.MinValue));
@@ -2233,7 +2239,7 @@
{
if (String.IsNullOrWhiteSpace(formulaA1)) return String.Empty;
- var value = formulaA1; // ">" + formulaA1 + "<";
+ var value = formulaA1;
var regex = A1SimpleRegex;
@@ -2272,6 +2278,12 @@
&& shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= matchRange.RangeAddress.FirstAddress.ColumnNumber
&& shiftedRange.RangeAddress.LastAddress.ColumnNumber >= matchRange.RangeAddress.LastAddress.ColumnNumber)
{
+ if (useSheetName)
+ {
+ sb.Append(sheetName.EscapeSheetName());
+ sb.Append('!');
+ }
+
if (A1RowRegex.IsMatch(rangeAddress))
{
var rows = rangeAddress.Split(':');
@@ -2295,115 +2307,53 @@
else
row2 = (XLHelper.TrimRowNumber(Int32.Parse(row2String) + rowsShifted)).ToInvariantString();
- sb.Append(useSheetName
- ? String.Format("{0}!{1}:{2}", sheetName.EscapeSheetName(), row1, row2)
- : String.Format("{0}:{1}", row1, row2));
+ sb.Append(row1);
+ sb.Append(':');
+ sb.Append(row2);
}
else if (shiftedRange.RangeAddress.FirstAddress.RowNumber <=
matchRange.RangeAddress.FirstAddress.RowNumber)
{
if (rangeAddress.Contains(':'))
{
- if (useSheetName)
- {
- sb.Append(String.Format("{0}!{1}:{2}",
- sheetName.EscapeSheetName(),
- new XLAddress(worksheetInAction,
- XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted),
- matchRange.RangeAddress.
- FirstAddress.ColumnLetter,
- matchRange.RangeAddress.
- FirstAddress.FixedRow,
- matchRange.RangeAddress.
- FirstAddress.FixedColumn),
- new XLAddress(worksheetInAction,
- XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted),
- matchRange.RangeAddress.
- LastAddress.ColumnLetter,
- matchRange.RangeAddress.
- LastAddress.FixedRow,
- matchRange.RangeAddress.
- LastAddress.FixedColumn)));
- }
- else
- {
- sb.Append(String.Format("{0}:{1}",
- new XLAddress(worksheetInAction,
- XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted),
- matchRange.RangeAddress.
- FirstAddress.ColumnLetter,
- matchRange.RangeAddress.
- FirstAddress.FixedRow,
- matchRange.RangeAddress.
- FirstAddress.FixedColumn),
- new XLAddress(worksheetInAction,
- XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted),
- matchRange.RangeAddress.
- LastAddress.ColumnLetter,
- matchRange.RangeAddress.
- LastAddress.FixedRow,
- matchRange.RangeAddress.
- LastAddress.FixedColumn)));
- }
+ sb.Append(
+ new XLAddress(
+ worksheetInAction,
+ XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted),
+ matchRange.RangeAddress.FirstAddress.ColumnLetter,
+ matchRange.RangeAddress.FirstAddress.FixedRow,
+ matchRange.RangeAddress.FirstAddress.FixedColumn));
+ sb.Append(':');
+ sb.Append(
+ new XLAddress(
+ worksheetInAction,
+ XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted),
+ matchRange.RangeAddress.LastAddress.ColumnLetter,
+ matchRange.RangeAddress.LastAddress.FixedRow,
+ matchRange.RangeAddress.LastAddress.FixedColumn));
}
else
{
- if (useSheetName)
- {
- sb.Append(String.Format("{0}!{1}",
- sheetName.EscapeSheetName(),
- new XLAddress(worksheetInAction,
- XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted),
- matchRange.RangeAddress.
- FirstAddress.ColumnLetter,
- matchRange.RangeAddress.
- FirstAddress.FixedRow,
- matchRange.RangeAddress.
- FirstAddress.FixedColumn)));
- }
- else
- {
- sb.Append(String.Format("{0}",
- new XLAddress(worksheetInAction,
- XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted),
- matchRange.RangeAddress.
- FirstAddress.ColumnLetter,
- matchRange.RangeAddress.
- FirstAddress.FixedRow,
- matchRange.RangeAddress.
- FirstAddress.FixedColumn)));
- }
+ sb.Append(
+ new XLAddress(
+ worksheetInAction,
+ XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted),
+ matchRange.RangeAddress.FirstAddress.ColumnLetter,
+ matchRange.RangeAddress.FirstAddress.FixedRow,
+ matchRange.RangeAddress.FirstAddress.FixedColumn));
}
}
else
{
- if (useSheetName)
- {
- sb.Append(String.Format("{0}!{1}:{2}",
- sheetName.EscapeSheetName(),
- matchRange.RangeAddress.FirstAddress,
- new XLAddress(worksheetInAction,
- XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted),
- matchRange.RangeAddress.
- LastAddress.ColumnLetter,
- matchRange.RangeAddress.
- LastAddress.FixedRow,
- matchRange.RangeAddress.
- LastAddress.FixedColumn)));
- }
- else
- {
- sb.Append(String.Format("{0}:{1}",
- matchRange.RangeAddress.FirstAddress,
- new XLAddress(worksheetInAction,
- XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted),
- matchRange.RangeAddress.
- LastAddress.ColumnLetter,
- matchRange.RangeAddress.
- LastAddress.FixedRow,
- matchRange.RangeAddress.
- LastAddress.FixedColumn)));
- }
+ sb.Append(matchRange.RangeAddress.FirstAddress);
+ sb.Append(':');
+ sb.Append(
+ new XLAddress(
+ worksheetInAction,
+ XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted),
+ matchRange.RangeAddress.LastAddress.ColumnLetter,
+ matchRange.RangeAddress.LastAddress.FixedRow,
+ matchRange.RangeAddress.LastAddress.FixedColumn));
}
}
else
@@ -2418,6 +2368,7 @@
}
else
sb.Append(value.Substring(lastIndex, matchIndex - lastIndex + matchString.Length));
+
lastIndex = matchIndex + matchString.Length;
}
@@ -2425,9 +2376,6 @@
sb.Append(value.Substring(lastIndex));
return sb.ToString();
-
- //string retVal = sb.ToString();
- //return retVal.Substring(1, retVal.Length - 2);
}
internal void ShiftFormulaColumns(XLRange shiftedRange, int columnsShifted)
@@ -2440,7 +2388,7 @@
{
if (String.IsNullOrWhiteSpace(formulaA1)) return String.Empty;
- var value = formulaA1; // ">" + formulaA1 + "<";
+ var value = formulaA1;
var regex = A1SimpleRegex;
@@ -2483,6 +2431,12 @@
shiftedRange.RangeAddress.LastAddress.RowNumber >=
matchRange.RangeAddress.LastAddress.RowNumber)
{
+ if (useSheetName)
+ {
+ sb.Append(sheetName.EscapeSheetName());
+ sb.Append('!');
+ }
+
if (A1ColumnRegex.IsMatch(rangeAddress))
{
var columns = rangeAddress.Split(':');
@@ -2520,115 +2474,53 @@
columnsShifted, true);
}
- sb.Append(useSheetName
- ? String.Format("{0}!{1}:{2}", sheetName.EscapeSheetName(), column1, column2)
- : String.Format("{0}:{1}", column1, column2));
+ sb.Append(column1);
+ sb.Append(':');
+ sb.Append(column2);
}
else if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <=
matchRange.RangeAddress.FirstAddress.ColumnNumber)
{
if (rangeAddress.Contains(':'))
{
- if (useSheetName)
- {
- sb.Append(String.Format("{0}!{1}:{2}",
- sheetName.EscapeSheetName(),
- new XLAddress(worksheetInAction,
- matchRange.RangeAddress.
- FirstAddress.RowNumber,
- XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted),
- matchRange.RangeAddress.
- FirstAddress.FixedRow,
- matchRange.RangeAddress.
- FirstAddress.FixedColumn),
- new XLAddress(worksheetInAction,
- matchRange.RangeAddress.
- LastAddress.RowNumber,
- XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted),
- matchRange.RangeAddress.
- LastAddress.FixedRow,
- matchRange.RangeAddress.
- LastAddress.FixedColumn)));
- }
- else
- {
- sb.Append(String.Format("{0}:{1}",
- new XLAddress(worksheetInAction,
- matchRange.RangeAddress.
- FirstAddress.RowNumber,
- XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted),
- matchRange.RangeAddress.
- FirstAddress.FixedRow,
- matchRange.RangeAddress.
- FirstAddress.FixedColumn),
- new XLAddress(worksheetInAction,
- matchRange.RangeAddress.
- LastAddress.RowNumber,
- XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted),
- matchRange.RangeAddress.
- LastAddress.FixedRow,
- matchRange.RangeAddress.
- LastAddress.FixedColumn)));
- }
+ sb.Append(
+ new XLAddress(
+ worksheetInAction,
+ matchRange.RangeAddress.FirstAddress.RowNumber,
+ XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted),
+ matchRange.RangeAddress.FirstAddress.FixedRow,
+ matchRange.RangeAddress.FirstAddress.FixedColumn));
+ sb.Append(':');
+ sb.Append(
+ new XLAddress(
+ worksheetInAction,
+ matchRange.RangeAddress.LastAddress.RowNumber,
+ XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted),
+ matchRange.RangeAddress.LastAddress.FixedRow,
+ matchRange.RangeAddress.LastAddress.FixedColumn));
}
else
{
- if (useSheetName)
- {
- sb.Append(String.Format("{0}!{1}",
- sheetName.EscapeSheetName(),
- new XLAddress(worksheetInAction,
- matchRange.RangeAddress.
- FirstAddress.RowNumber,
- XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted),
- matchRange.RangeAddress.
- FirstAddress.FixedRow,
- matchRange.RangeAddress.
- FirstAddress.FixedColumn)));
- }
- else
- {
- sb.Append(String.Format("{0}",
- new XLAddress(worksheetInAction,
- matchRange.RangeAddress.
- FirstAddress.RowNumber,
- XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted),
- matchRange.RangeAddress.
- FirstAddress.FixedRow,
- matchRange.RangeAddress.
- FirstAddress.FixedColumn)));
- }
+ sb.Append(
+ new XLAddress(
+ worksheetInAction,
+ matchRange.RangeAddress.FirstAddress.RowNumber,
+ XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted),
+ matchRange.RangeAddress.FirstAddress.FixedRow,
+ matchRange.RangeAddress.FirstAddress.FixedColumn));
}
}
else
{
- if (useSheetName)
- {
- sb.Append(String.Format("{0}!{1}:{2}",
- sheetName.EscapeSheetName(),
- matchRange.RangeAddress.FirstAddress,
- new XLAddress(worksheetInAction,
- matchRange.RangeAddress.
- LastAddress.RowNumber,
- XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted),
- matchRange.RangeAddress.
- LastAddress.FixedRow,
- matchRange.RangeAddress.
- LastAddress.FixedColumn)));
- }
- else
- {
- sb.Append(String.Format("{0}:{1}",
- matchRange.RangeAddress.FirstAddress,
- new XLAddress(worksheetInAction,
- matchRange.RangeAddress.
- LastAddress.RowNumber,
- XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted),
- matchRange.RangeAddress.
- LastAddress.FixedRow,
- matchRange.RangeAddress.
- LastAddress.FixedColumn)));
- }
+ sb.Append(matchRange.RangeAddress.FirstAddress);
+ sb.Append(':');
+ sb.Append(
+ new XLAddress(
+ worksheetInAction,
+ matchRange.RangeAddress.LastAddress.RowNumber,
+ XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted),
+ matchRange.RangeAddress.LastAddress.FixedRow,
+ matchRange.RangeAddress.LastAddress.FixedColumn));
}
}
else
@@ -2650,13 +2542,8 @@
sb.Append(value.Substring(lastIndex));
return sb.ToString();
-
- //string retVal = sb.ToString();
- //return retVal.Substring(1, retVal.Length - 2);
}
- // --
-
private XLCell CellShift(Int32 rowsToShift, Int32 columnsToShift)
{
return Worksheet.Cell(_rowNumber + rowsToShift, _columnNumber + columnsToShift);
diff --git a/ClosedXML/Excel/Coordinates/XLAddress.cs b/ClosedXML/Excel/Coordinates/XLAddress.cs
index 56f3922..d8f5dcd 100644
--- a/ClosedXML/Excel/Coordinates/XLAddress.cs
+++ b/ClosedXML/Excel/Coordinates/XLAddress.cs
@@ -237,22 +237,19 @@
public string ToString(XLReferenceStyle referenceStyle, bool includeSheet)
{
- string address = string.Empty;
+ string address;
if (referenceStyle == XLReferenceStyle.A1)
-
- address = ColumnLetter + _rowNumber.ToInvariantString();
- else if (referenceStyle == XLReferenceStyle.R1C1)
-
- address = String.Format("R{0}C{1}", _rowNumber.ToInvariantString(), ColumnNumber);
- else if (HasWorksheet && Worksheet.Workbook.ReferenceStyle == XLReferenceStyle.R1C1)
-
- address = String.Format("R{0}C{1}", _rowNumber.ToInvariantString(), ColumnNumber);
+ address = GetTrimmedAddress();
+ else if (referenceStyle == XLReferenceStyle.R1C1
+ || HasWorksheet && Worksheet.Workbook.ReferenceStyle == XLReferenceStyle.R1C1)
+ address = "R" + _rowNumber.ToInvariantString() + "C" + ColumnNumber.ToInvariantString();
else
- address = ColumnLetter + _rowNumber.ToInvariantString();
+ address = GetTrimmedAddress();
if (includeSheet)
- return String.Format("{0}!{1}",
+ return String.Concat(
Worksheet.Name.EscapeSheetName(),
+ '!',
address);
return address;
@@ -388,9 +385,11 @@
public String ToStringRelative(Boolean includeSheet)
{
if (includeSheet)
- return String.Format("{0}!{1}",
+ return String.Concat(
Worksheet.Name.EscapeSheetName(),
- GetTrimmedAddress());
+ '!',
+ GetTrimmedAddress()
+ );
return GetTrimmedAddress();
}
@@ -403,18 +402,33 @@
public String ToStringFixed(XLReferenceStyle referenceStyle, Boolean includeSheet)
{
String address;
- if (referenceStyle == XLReferenceStyle.A1)
- address = String.Format("${0}${1}", ColumnLetter, _rowNumber.ToInvariantString());
- else if (referenceStyle == XLReferenceStyle.R1C1)
- address = String.Format("R{0}C{1}", _rowNumber.ToInvariantString(), ColumnNumber);
- else if (HasWorksheet && Worksheet.Workbook.ReferenceStyle == XLReferenceStyle.R1C1)
- address = String.Format("R{0}C{1}", _rowNumber.ToInvariantString(), ColumnNumber);
- else
- address = String.Format("${0}${1}", ColumnLetter, _rowNumber.ToInvariantString());
+
+ if (referenceStyle == XLReferenceStyle.Default && HasWorksheet)
+ referenceStyle = Worksheet.Workbook.ReferenceStyle;
+
+ if (referenceStyle == XLReferenceStyle.Default)
+ referenceStyle = XLReferenceStyle.A1;
+
+ Debug.Assert(referenceStyle != XLReferenceStyle.Default);
+
+ switch (referenceStyle)
+ {
+ case XLReferenceStyle.A1:
+ address = String.Concat('$', ColumnLetter, '$', _rowNumber.ToInvariantString());
+ break;
+
+ case XLReferenceStyle.R1C1:
+ address = String.Concat('R', _rowNumber.ToInvariantString(), 'C', ColumnNumber);
+ break;
+
+ default:
+ throw new NotImplementedException();
+ }
if (includeSheet)
- return String.Format("{0}!{1}",
+ return String.Concat(
Worksheet.Name.EscapeSheetName(),
+ '!',
address);
return address;
diff --git a/ClosedXML/Excel/DataValidation/XLDataValidation.cs b/ClosedXML/Excel/DataValidation/XLDataValidation.cs
index 17164c0..3ee5896 100644
--- a/ClosedXML/Excel/DataValidation/XLDataValidation.cs
+++ b/ClosedXML/Excel/DataValidation/XLDataValidation.cs
@@ -4,20 +4,27 @@
{
internal class XLDataValidation : IXLDataValidation
{
- public XLDataValidation(IXLRanges ranges)
+ private XLDataValidation()
{
-
Ranges = new XLRanges();
- ranges.ForEach(r=>
- {
- var newR =
- new XLRange(new XLRangeParameters(r.RangeAddress as XLRangeAddress,
- r.Worksheet.Style) {IgnoreEvents = true});
- (Ranges as XLRanges).Add(newR);
- } );
Initialize();
}
+ public XLDataValidation(IXLRange range)
+ :this()
+ {
+ Ranges.Add(new XLRange(new XLRangeParameters(range.RangeAddress as XLRangeAddress, range.Worksheet.Style)));
+ }
+
+ public XLDataValidation(IXLRanges ranges)
+ :this()
+ {
+ ranges.ForEach(range =>
+ {
+ Ranges.Add(new XLRange(new XLRangeParameters(range.RangeAddress as XLRangeAddress, range.Worksheet.Style)));
+ });
+ }
+
private void Initialize()
{
AllowedValues = XLAllowedValues.AnyValue;
@@ -191,4 +198,4 @@
Initialize();
}
}
-}
\ No newline at end of file
+}
diff --git a/ClosedXML/Excel/DataValidation/XLDataValidations.cs b/ClosedXML/Excel/DataValidation/XLDataValidations.cs
index eb95d43..fa339f6 100644
--- a/ClosedXML/Excel/DataValidation/XLDataValidations.cs
+++ b/ClosedXML/Excel/DataValidation/XLDataValidations.cs
@@ -51,4 +51,4 @@
_dataValidations.RemoveAll(dv => dv.Ranges.Contains(range));
}
}
-}
\ No newline at end of file
+}
diff --git a/ClosedXML/Excel/Drawings/XLPictures.cs b/ClosedXML/Excel/Drawings/XLPictures.cs
index e5832e0..c539d0e 100644
--- a/ClosedXML/Excel/Drawings/XLPictures.cs
+++ b/ClosedXML/Excel/Drawings/XLPictures.cs
@@ -17,7 +17,7 @@
public XLPictures(XLWorksheet worksheet)
{
_worksheet = worksheet;
- Deleted = new HashSet();
+ Deleted = new HashSet();
}
public int Count
diff --git a/ClosedXML/Excel/Hyperlinks/XLHyperlink_public.cs b/ClosedXML/Excel/Hyperlinks/XLHyperlink_public.cs
index 85b7e89..cbc96f5 100644
--- a/ClosedXML/Excel/Hyperlinks/XLHyperlink_public.cs
+++ b/ClosedXML/Excel/Hyperlinks/XLHyperlink_public.cs
@@ -75,14 +75,18 @@
if (_internalAddress.Contains('!'))
{
return _internalAddress[0] != '\''
- ? String.Format("{0}!{1}",
+ ? String.Concat(
_internalAddress
.Substring(0, _internalAddress.IndexOf('!'))
.EscapeSheetName(),
+ '!',
_internalAddress.Substring(_internalAddress.IndexOf('!') + 1))
: _internalAddress;
}
- return String.Format("{0}!{1}", Worksheet.Name.EscapeSheetName(), _internalAddress);
+ return String.Concat(
+ Worksheet.Name.EscapeSheetName(),
+ '!',
+ _internalAddress);
}
set
{
diff --git a/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs b/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs
index ef16caf..9b35733 100644
--- a/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs
+++ b/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs
@@ -1,11 +1,10 @@
using System;
using System.Collections.Generic;
using System.Linq;
-using System.Text;
namespace ClosedXML.Excel
{
- internal class XLPivotValues: IXLPivotValues
+ internal class XLPivotValues : IXLPivotValues
{
private readonly Dictionary _pivotValues = new Dictionary();
@@ -30,6 +29,7 @@
{
return Add(sourceName, sourceName);
}
+
public IXLPivotValue Add(String sourceName, String customName)
{
if (sourceName != XLConstants.PivotTableValuesSentinalLabel && !this._pivotTable.SourceRangeFieldsAvailable.Contains(sourceName, StringComparer.OrdinalIgnoreCase))
@@ -38,7 +38,7 @@
var pivotValue = new XLPivotValue(sourceName) { CustomName = customName };
_pivotValues.Add(customName, pivotValue);
- if (_pivotValues.Count > 1 && !this._pivotTable.ColumnLabels.Any(cl => cl.SourceName == XLConstants.PivotTableValuesSentinalLabel) && !this._pivotTable.RowLabels.Any(rl => rl.SourceName == XLConstants.PivotTableValuesSentinalLabel))
+ if (_pivotValues.Count > 1 && this._pivotTable.ColumnLabels.All(cl => cl.SourceName != XLConstants.PivotTableValuesSentinalLabel) && this._pivotTable.RowLabels.All(rl => rl.SourceName != XLConstants.PivotTableValuesSentinalLabel))
_pivotTable.ColumnLabels.Add(XLConstants.PivotTableValuesSentinalLabel);
return pivotValue;
@@ -48,6 +48,7 @@
{
_pivotValues.Clear();
}
+
public void Remove(String sourceName)
{
_pivotValues.Remove(sourceName);
diff --git a/ClosedXML/Excel/Ranges/XLRangeAddress.cs b/ClosedXML/Excel/Ranges/XLRangeAddress.cs
index b1d20c4..177677e 100644
--- a/ClosedXML/Excel/Ranges/XLRangeAddress.cs
+++ b/ClosedXML/Excel/Ranges/XLRangeAddress.cs
@@ -142,12 +142,17 @@
public String ToStringRelative(Boolean includeSheet)
{
if (includeSheet)
- return String.Format("{0}!{1}:{2}",
+ return String.Concat(
Worksheet.Name.EscapeSheetName(),
+ '!',
_firstAddress.ToStringRelative(),
+ ':',
_lastAddress.ToStringRelative());
-
- return _firstAddress.ToStringRelative() + ":" + _lastAddress.ToStringRelative();
+ else
+ return string.Concat(
+ _firstAddress.ToStringRelative(),
+ ":",
+ _lastAddress.ToStringRelative());
}
public String ToStringFixed(XLReferenceStyle referenceStyle)
diff --git a/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs
index 20d0b80..11e4554 100644
--- a/ClosedXML/Excel/Ranges/XLRangeBase.cs
+++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs
@@ -74,99 +74,46 @@
get { return RangeAddress.Worksheet; }
}
- public XLDataValidation NewDataValidation
+ public IXLDataValidation NewDataValidation
{
get
{
var newRanges = new XLRanges { AsRange() };
- var dataValidation = new XLDataValidation(newRanges);
+ var dataValidation = DataValidation;
+ if (dataValidation != null)
+ Worksheet.DataValidations.Delete(dataValidation);
+
+ dataValidation = new XLDataValidation(newRanges);
Worksheet.DataValidations.Add(dataValidation);
return dataValidation;
}
}
- public XLDataValidation DataValidation
+ ///
+ /// Get the data validation rule containing current range or create a new one if no rule was defined for range.
+ ///
+ public IXLDataValidation DataValidation
{
get
{
- IXLDataValidation dataValidationToCopy = null;
- var dvEmpty = new List();
- foreach (IXLDataValidation dv in Worksheet.DataValidations)
- {
- foreach (IXLRange dvRange in dv.Ranges.Where(dvRange => dvRange.Intersects(this)))
- {
- if (dataValidationToCopy == null)
- dataValidationToCopy = dv;
-
- dv.Ranges.Remove(dvRange);
- foreach (var column in dvRange.Columns())
- {
- if (column.Intersects(this))
- {
- Int32 dvStart = column.RangeAddress.FirstAddress.RowNumber;
- Int32 dvEnd = column.RangeAddress.LastAddress.RowNumber;
- Int32 thisStart = RangeAddress.FirstAddress.RowNumber;
- Int32 thisEnd = RangeAddress.LastAddress.RowNumber;
-
- if (thisStart > dvStart && thisEnd < dvEnd)
- {
- var r1 = Worksheet.Column(column.ColumnNumber()).Column(dvStart, thisStart - 1);
- r1.Dispose();
- dv.Ranges.Add(r1);
- var r2 = Worksheet.Column(column.ColumnNumber()).Column(thisEnd + 1, dvEnd);
- r2.Dispose();
- dv.Ranges.Add(r2);
- }
- else
- {
- Int32 coStart;
- if (dvStart < thisStart)
- coStart = dvStart;
- else
- coStart = thisEnd + 1;
-
- if (coStart <= dvEnd)
- {
- Int32 coEnd;
- if (dvEnd > thisEnd)
- coEnd = dvEnd;
- else
- coEnd = thisStart - 1;
-
- if (coEnd >= dvStart)
- {
- var r = Worksheet.Column(column.ColumnNumber()).Column(coStart, coEnd);
- r.Dispose();
- dv.Ranges.Add(r);
- }
- }
- }
- }
- else
- {
- column.Dispose();
- dv.Ranges.Add(column);
- }
- }
-
- if (!dv.Ranges.Any())
- dvEmpty.Add(dv);
- }
- }
-
- dvEmpty.ForEach(dv => Worksheet.DataValidations.Delete(dv));
-
- var newRanges = new XLRanges { AsRange() };
- var dataValidation = new XLDataValidation(newRanges);
- if (dataValidationToCopy != null)
- dataValidation.CopyFrom(dataValidationToCopy);
-
- Worksheet.DataValidations.Add(dataValidation);
- return dataValidation;
+ return SetDataValidation();
}
}
+ private IXLDataValidation GetDataValidation()
+ {
+ foreach (var xlDataValidation in Worksheet.DataValidations)
+ {
+ foreach (var range in xlDataValidation.Ranges)
+ {
+ if (range.ToString() == ToString())
+ return xlDataValidation;
+ }
+ }
+ return null;
+ }
+
#region IXLRangeBase Members
IXLRangeAddress IXLRangeBase.RangeAddress
@@ -1071,7 +1018,6 @@
cell.ShiftFormulaColumns(asRange, numberOfColumns);
}
- var cellsDataValidations = new Dictionary();
var cellsToInsert = new Dictionary();
var cellsToDelete = new List();
int firstColumn = RangeAddress.FirstAddress.ColumnNumber;
@@ -1118,39 +1064,21 @@
var newKey = new XLAddress(Worksheet, c.Address.RowNumber, newColumn, false, false);
var newCell = new XLCell(Worksheet, newKey, c.Style);
newCell.CopyValuesFrom(c);
- if (c.HasDataValidation)
- {
- cellsDataValidations.Add(newCell.Address,
- new DataValidationToCopy
- { DataValidation = c.DataValidation, SourceAddress = c.Address });
- c.DataValidation.Clear();
- }
newCell.FormulaA1 = c.FormulaA1;
cellsToInsert.Add(newKey, newCell);
cellsToDelete.Add(c.Address);
}
}
- cellsDataValidations.ForEach(kp =>
- {
- XLCell targetCell;
- if (!cellsToInsert.TryGetValue(kp.Key, out targetCell))
- targetCell = Worksheet.Cell(kp.Key);
-
- targetCell.CopyDataValidation(Worksheet.Cell(kp.Value.SourceAddress), kp.Value.DataValidation);
- });
-
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));
- //cellsDataValidations.ForEach(kp => Worksheet.Cell(kp.Key).CopyDataValidation(Worksheet.Cell(kp.Value.SourceAddress), kp.Value.DataValidation));
Int32 firstRowReturn = RangeAddress.FirstAddress.RowNumber;
Int32 lastRowReturn = RangeAddress.LastAddress.RowNumber;
Int32 firstColumnReturn = RangeAddress.FirstAddress.ColumnNumber;
Int32 lastColumnReturn = RangeAddress.FirstAddress.ColumnNumber + numberOfColumns - 1;
- Worksheet.BreakConditionalFormatsIntoCells(cellsToDelete.Except(cellsToInsert.Keys).ToList());
using (var asRange = AsRange())
Worksheet.NotifyRangeShiftedColumns(asRange, numberOfColumns);
@@ -1313,7 +1241,6 @@
var cellsToInsert = new Dictionary();
var cellsToDelete = new List();
- var cellsDataValidations = new Dictionary();
int firstRow = RangeAddress.FirstAddress.RowNumber;
int firstColumn = RangeAddress.FirstAddress.ColumnNumber;
int lastColumn = Math.Min(
@@ -1362,30 +1289,12 @@
var newKey = new XLAddress(Worksheet, newRow, c.Address.ColumnNumber, false, false);
var newCell = new XLCell(Worksheet, newKey, c.Style);
newCell.CopyValuesFrom(c);
- if (c.HasDataValidation)
- {
- cellsDataValidations.Add(newCell.Address,
- new DataValidationToCopy
- { DataValidation = c.DataValidation, SourceAddress = c.Address });
- c.DataValidation.Clear();
- }
newCell.FormulaA1 = c.FormulaA1;
cellsToInsert.Add(newKey, newCell);
cellsToDelete.Add(c.Address);
}
}
- cellsDataValidations
- .ForEach(kp =>
- {
- XLCell targetCell;
- if (!cellsToInsert.TryGetValue(kp.Key, out targetCell))
- targetCell = Worksheet.Cell(kp.Key);
-
- targetCell.CopyDataValidation(
- Worksheet.Cell(kp.Value.SourceAddress), kp.Value.DataValidation);
- });
-
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));
@@ -1394,7 +1303,6 @@
Int32 firstColumnReturn = RangeAddress.FirstAddress.ColumnNumber;
Int32 lastColumnReturn = RangeAddress.LastAddress.ColumnNumber;
- Worksheet.BreakConditionalFormatsIntoCells(cellsToDelete.Except(cellsToInsert.Keys).ToList());
using (var asRange = AsRange())
Worksheet.NotifyRangeShiftedRows(asRange, numberOfRows);
@@ -1543,7 +1451,6 @@
var hyperlinksToRemove = Worksheet.Hyperlinks.Where(hl => Contains(hl.Cell.AsRange())).ToList();
hyperlinksToRemove.ForEach(hl => Worksheet.Hyperlinks.Delete(hl));
- Worksheet.BreakConditionalFormatsIntoCells(cellsToDelete.Except(cellsToInsert.Keys).ToList());
using (var shiftedRange = AsRange())
{
if (shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp)
@@ -1555,7 +1462,12 @@
public override string ToString()
{
- return String.Format("{0}!{1}:{2}", Worksheet.Name.EscapeSheetName(), RangeAddress.FirstAddress, RangeAddress.LastAddress);
+ return String.Concat(
+ Worksheet.Name.EscapeSheetName(),
+ '!',
+ RangeAddress.FirstAddress,
+ ':',
+ RangeAddress.LastAddress);
}
protected void ShiftColumns(IXLRangeAddress thisRangeAddress, XLRange shiftedRange, int columnsShifted)
@@ -1569,7 +1481,7 @@
return;
bool shiftLeftBoundary = (columnsShifted > 0 && thisRangeAddress.FirstAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber) ||
- (columnsShifted < 0 && thisRangeAddress.FirstAddress.ColumnNumber > shiftedRange.RangeAddress.FirstAddress.ColumnNumber);
+ (columnsShifted < 0 && thisRangeAddress.FirstAddress.ColumnNumber > shiftedRange.RangeAddress.FirstAddress.ColumnNumber);
bool shiftRightBoundary = thisRangeAddress.LastAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber;
@@ -1614,13 +1526,13 @@
if (thisRangeAddress.IsInvalid || shiftedRange.RangeAddress.IsInvalid) return;
bool allColumnsAreCovered = thisRangeAddress.FirstAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber &&
- thisRangeAddress.LastAddress.ColumnNumber <= shiftedRange.RangeAddress.LastAddress.ColumnNumber;
+ thisRangeAddress.LastAddress.ColumnNumber <= shiftedRange.RangeAddress.LastAddress.ColumnNumber;
if (!allColumnsAreCovered)
return;
bool shiftTopBoundary = (rowsShifted > 0 && thisRangeAddress.FirstAddress.RowNumber >= shiftedRange.RangeAddress.FirstAddress.RowNumber) ||
- (rowsShifted < 0 && thisRangeAddress.FirstAddress.RowNumber > shiftedRange.RangeAddress.FirstAddress.RowNumber);
+ (rowsShifted < 0 && thisRangeAddress.FirstAddress.RowNumber > shiftedRange.RangeAddress.FirstAddress.RowNumber);
bool shiftBottomBoundary = thisRangeAddress.LastAddress.RowNumber >= shiftedRange.RangeAddress.FirstAddress.RowNumber;
@@ -1756,7 +1668,6 @@
}
return sb.ToString();
-
}
public IXLRangeBase Sort()
@@ -1770,7 +1681,6 @@
return this;
}
-
public IXLRangeBase Sort(String columnsToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true)
{
SortColumns.Clear();
@@ -2012,7 +1922,83 @@
public IXLDataValidation SetDataValidation()
{
- return DataValidation;
+ var existingValidation = GetDataValidation();
+ if (existingValidation != null) return existingValidation;
+
+ IXLDataValidation dataValidationToCopy = null;
+ var dvEmpty = new List();
+ foreach (IXLDataValidation dv in Worksheet.DataValidations)
+ {
+ foreach (IXLRange dvRange in dv.Ranges.Where(dvRange => dvRange.Intersects(this)))
+ {
+ if (dataValidationToCopy == null)
+ dataValidationToCopy = dv;
+
+ dv.Ranges.Remove(dvRange);
+ foreach (var column in dvRange.Columns())
+ {
+ if (column.Intersects(this))
+ {
+ Int32 dvStart = column.RangeAddress.FirstAddress.RowNumber;
+ Int32 dvEnd = column.RangeAddress.LastAddress.RowNumber;
+ Int32 thisStart = RangeAddress.FirstAddress.RowNumber;
+ Int32 thisEnd = RangeAddress.LastAddress.RowNumber;
+
+ if (thisStart > dvStart && thisEnd < dvEnd)
+ {
+ var r1 = Worksheet.Column(column.ColumnNumber()).Column(dvStart, thisStart - 1);
+ r1.Dispose();
+ dv.Ranges.Add(r1);
+ var r2 = Worksheet.Column(column.ColumnNumber()).Column(thisEnd + 1, dvEnd);
+ r2.Dispose();
+ dv.Ranges.Add(r2);
+ }
+ else
+ {
+ Int32 coStart;
+ if (dvStart < thisStart)
+ coStart = dvStart;
+ else
+ coStart = thisEnd + 1;
+
+ if (coStart <= dvEnd)
+ {
+ Int32 coEnd;
+ if (dvEnd > thisEnd)
+ coEnd = dvEnd;
+ else
+ coEnd = thisStart - 1;
+
+ if (coEnd >= dvStart)
+ {
+ var r = Worksheet.Column(column.ColumnNumber()).Column(coStart, coEnd);
+ r.Dispose();
+ dv.Ranges.Add(r);
+ }
+ }
+ }
+ }
+ else
+ {
+ column.Dispose();
+ dv.Ranges.Add(column);
+ }
+ }
+
+ if (!dv.Ranges.Any())
+ dvEmpty.Add(dv);
+ }
+ }
+
+ dvEmpty.ForEach(dv => Worksheet.DataValidations.Delete(dv));
+
+ var newRanges = new XLRanges { AsRange() };
+ var dataValidation = new XLDataValidation(newRanges);
+ if (dataValidationToCopy != null)
+ dataValidation.CopyFrom(dataValidationToCopy);
+
+ Worksheet.DataValidations.Add(dataValidation);
+ return dataValidation;
}
public IXLConditionalFormat AddConditionalFormat()
diff --git a/ClosedXML/Excel/Ranges/XLRanges.cs b/ClosedXML/Excel/Ranges/XLRanges.cs
index 27e4aaa..eecb266 100644
--- a/ClosedXML/Excel/Ranges/XLRanges.cs
+++ b/ClosedXML/Excel/Ranges/XLRanges.cs
@@ -69,31 +69,9 @@
return _ranges.Any(r => !r.RangeAddress.IsInvalid && r.Contains(range));
}
- public IXLDataValidation DataValidation
+ public IEnumerable DataValidation
{
- get
- {
- foreach (XLRange range in _ranges)
- {
- foreach (IXLDataValidation dv in range.Worksheet.DataValidations)
- {
- foreach (IXLRange dvRange in dv.Ranges.Where(dvRange => dvRange.Intersects(range)))
- {
- dv.Ranges.Remove(dvRange);
- foreach (IXLCell c in dvRange.Cells().Where(c => !range.Contains(c.Address.ToString())))
- {
- var r = c.AsRange();
- r.Dispose();
- dv.Ranges.Add(r);
- }
- }
- }
- }
- var dataValidation = new XLDataValidation(this);
-
- _ranges.First().Worksheet.DataValidations.Add(dataValidation);
- return dataValidation;
- }
+ get { return _ranges.Select(range => range.DataValidation).Where(dv => dv != null); }
}
public IXLRanges AddToNamed(String rangeName)
@@ -224,7 +202,26 @@
public IXLDataValidation SetDataValidation()
{
- return DataValidation;
+ foreach (XLRange range in _ranges)
+ {
+ foreach (IXLDataValidation dv in range.Worksheet.DataValidations)
+ {
+ foreach (IXLRange dvRange in dv.Ranges.Where(dvRange => dvRange.Intersects(range)))
+ {
+ dv.Ranges.Remove(dvRange);
+ foreach (IXLCell c in dvRange.Cells().Where(c => !range.Contains(c.Address.ToString())))
+ {
+ var r = c.AsRange();
+ r.Dispose();
+ dv.Ranges.Add(r);
+ }
+ }
+ }
+ }
+ var dataValidation = new XLDataValidation(this);
+
+ _ranges.First().Worksheet.DataValidations.Add(dataValidation);
+ return dataValidation;
}
public void Select()
diff --git a/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/Excel/Tables/XLTable.cs
index fca772f..b343b51 100644
--- a/ClosedXML/Excel/Tables/XLTable.cs
+++ b/ClosedXML/Excel/Tables/XLTable.cs
@@ -27,8 +27,8 @@
Int32 id = 1;
while (true)
{
- string tableName = String.Format("Table{0}", id);
- if (!Worksheet.Tables.Any(t => t.Name == tableName))
+ string tableName = String.Concat("Table", id);
+ if (Worksheet.Tables.All(t => t.Name != tableName))
{
Name = tableName;
AddToTables(range, addToTables);
@@ -119,7 +119,7 @@
Int32 colCount = ColumnCount();
for (Int32 i = 1; i <= colCount; i++)
{
- if (!_fieldNames.Values.Any(f => f.Index == i - 1))
+ if (_fieldNames.Values.All(f => f.Index != i - 1))
{
var name = "Column" + i;
diff --git a/ClosedXML/Excel/Tables/XLTableField.cs b/ClosedXML/Excel/Tables/XLTableField.cs
index 55efcb0..893d3ff 100644
--- a/ClosedXML/Excel/Tables/XLTableField.cs
+++ b/ClosedXML/Excel/Tables/XLTableField.cs
@@ -27,7 +27,8 @@
{
if (_column == null)
{
- _column = this.table.AsRange().Column(this.Index + 1);
+ using (var range = this.table.AsRange())
+ _column = range.Column(this.Index + 1);
}
return _column;
}
diff --git a/ClosedXML/Excel/Tables/XLTables.cs b/ClosedXML/Excel/Tables/XLTables.cs
index 485bb7c..76e9185 100644
--- a/ClosedXML/Excel/Tables/XLTables.cs
+++ b/ClosedXML/Excel/Tables/XLTables.cs
@@ -6,9 +6,16 @@
{
using System.Collections;
- public class XLTables : IXLTables
+ internal class XLTables : IXLTables
{
- private readonly Dictionary _tables = new Dictionary();
+ private readonly Dictionary _tables;
+ internal ICollection Deleted { get; private set; }
+
+ public XLTables()
+ {
+ _tables = new Dictionary();
+ Deleted = new HashSet();
+ }
#region IXLTables Members
@@ -37,7 +44,7 @@
return _tables[name];
}
- #endregion
+ #endregion IXLTables Members
public IXLTables Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats)
{
@@ -47,11 +54,18 @@
public void Remove(Int32 index)
{
- _tables.Remove(_tables.ElementAt(index).Key);
+ this.Remove(_tables.ElementAt(index).Key);
}
+
public void Remove(String name)
{
+ if (!_tables.ContainsKey(name))
+ throw new ArgumentOutOfRangeException(nameof(name), $"Unable to delete table because the table name {name} could not be found.");
+
+ var table = _tables[name] as XLTable;
_tables.Remove(name);
+
+ if (table.RelId != null) Deleted.Add(table.RelId);
}
}
-}
\ No newline at end of file
+}
diff --git a/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/Excel/XLWorkbook.cs
index 2db2077..59059b1 100644
--- a/ClosedXML/Excel/XLWorkbook.cs
+++ b/ClosedXML/Excel/XLWorkbook.cs
@@ -41,7 +41,7 @@
Simple = 1,
}
- public partial class XLWorkbook: IXLWorkbook
+ public partial class XLWorkbook : IXLWorkbook
{
#region Static
public static IXLStyle DefaultStyle
@@ -65,14 +65,14 @@
Scale = 100,
PaperSize = XLPaperSize.LetterPaper,
Margins = new XLMargins
- {
- Top = 0.75,
- Bottom = 0.5,
- Left = 0.75,
- Right = 0.75,
- Header = 0.5,
- Footer = 0.75
- },
+ {
+ Top = 0.75,
+ Bottom = 0.5,
+ Left = 0.75,
+ Right = 0.75,
+ Header = 0.5,
+ Footer = 0.75
+ },
ScaleHFWithDocument = true,
AlignHFWithMargins = true,
PrintErrorValue = XLPrintErrorValues.Displayed,
@@ -111,7 +111,7 @@
public XLEventTracking EventTracking { get; set; }
- #region Nested Type: XLLoadSource
+ #region Nested Type: XLLoadSource
private enum XLLoadSource
{
@@ -467,7 +467,7 @@
private void checkForWorksheetsPresent()
{
- if (Worksheets.Count() == 0)
+ if (!Worksheets.Any())
throw new InvalidOperationException("Workbooks need at least one worksheet.");
}
@@ -625,7 +625,7 @@
}
}
-#region Fields
+ #region Fields
private XLLoadSource _loadSource = XLLoadSource.New;
private String _originalFile;
@@ -633,13 +633,13 @@
#endregion Fields
-#region Constructor
+ #region Constructor
///
/// Creates a new Excel workbook.
///
public XLWorkbook()
- :this(XLEventTracking.Enabled)
+ : this(XLEventTracking.Enabled)
{
}
@@ -700,7 +700,7 @@
/// Opens an existing workbook from a stream.
///
/// The stream to open.
- public XLWorkbook(Stream stream):this(stream, XLEventTracking.Enabled)
+ public XLWorkbook(Stream stream) : this(stream, XLEventTracking.Enabled)
{
}
@@ -714,7 +714,7 @@
#endregion Constructor
-#region Nested type: UnsupportedSheet
+ #region Nested type: UnsupportedSheet
internal sealed class UnsupportedSheet
{
@@ -916,13 +916,32 @@
{
case XLLoadSource.New:
return "XLWorkbook(new)";
+
case XLLoadSource.File:
return String.Format("XLWorkbook({0})", _originalFile);
+
case XLLoadSource.Stream:
return String.Format("XLWorkbook({0})", _originalStream.ToString());
+
default:
throw new NotImplementedException();
}
}
+
+ public void SuspendEvents()
+ {
+ foreach (var ws in WorksheetsInternal)
+ {
+ ws.SuspendEvents();
+ }
+ }
+
+ public void ResumeEvents()
+ {
+ foreach (var ws in WorksheetsInternal)
+ {
+ ws.ResumeEvents();
+ }
+ }
}
}
diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs
index 2c5fd9d..0d88e30 100644
--- a/ClosedXML/Excel/XLWorkbook_Load.cs
+++ b/ClosedXML/Excel/XLWorkbook_Load.cs
@@ -170,14 +170,14 @@
var sheets = dSpreadsheet.WorkbookPart.Workbook.Sheets;
Int32 position = 0;
- foreach (Sheet dSheet in sheets.OfType())
+ foreach (var dSheet in sheets.OfType())
{
position++;
var sharedFormulasR1C1 = new Dictionary();
- var wsPart = dSpreadsheet.WorkbookPart.GetPartById(dSheet.Id) as WorksheetPart;
+ var worksheetPart = dSpreadsheet.WorkbookPart.GetPartById(dSheet.Id) as WorksheetPart;
- if (wsPart == null)
+ if (worksheetPart == null)
{
UnsupportedSheets.Add(new UnsupportedSheet { SheetId = dSheet.SheetId.Value, Position = position });
continue;
@@ -195,7 +195,7 @@
var styleList = new Dictionary();// {{0, ws.Style}};
PageSetupProperties pageSetupProperties = null;
- using (var reader = OpenXmlReader.Create(wsPart))
+ using (var reader = OpenXmlReader.Create(worksheetPart))
{
Type[] ignoredElements = new Type[]
{
@@ -253,7 +253,7 @@
else if (reader.ElementType == typeof(ConditionalFormatting))
LoadConditionalFormatting((ConditionalFormatting)reader.LoadCurrentElement(), ws, differentialFormats);
else if (reader.ElementType == typeof(Hyperlinks))
- LoadHyperlinks((Hyperlinks)reader.LoadCurrentElement(), wsPart, ws);
+ LoadHyperlinks((Hyperlinks)reader.LoadCurrentElement(), worksheetPart, ws);
else if (reader.ElementType == typeof(PrintOptions))
LoadPrintOptions((PrintOptions)reader.LoadCurrentElement(), ws);
else if (reader.ElementType == typeof(PageMargins))
@@ -278,15 +278,19 @@
#region LoadTables
- foreach (var tablePart in wsPart.TableDefinitionParts)
+ foreach (var tableDefinitionPart in worksheetPart.TableDefinitionParts)
{
- var dTable = tablePart.Table;
+ var relId = worksheetPart.GetIdOfPart(tableDefinitionPart);
+ var dTable = tableDefinitionPart.Table;
+
String reference = dTable.Reference.Value;
String tableName = dTable?.Name ?? dTable.DisplayName ?? string.Empty;
if (String.IsNullOrWhiteSpace(tableName))
throw new InvalidDataException("The table name is missing.");
- XLTable xlTable = ws.Range(reference).CreateTable(tableName, false) as XLTable;
+ var xlTable = ws.Range(reference).CreateTable(tableName, false) as XLTable;
+ xlTable.RelId = relId;
+
if (dTable.HeaderRowCount != null && dTable.HeaderRowCount == 0)
{
xlTable._showHeaderRow = false;
@@ -358,18 +362,18 @@
#endregion
- LoadDrawings(wsPart, ws);
+ LoadDrawings(worksheetPart, ws);
#region LoadComments
- if (wsPart.WorksheetCommentsPart != null)
+ if (worksheetPart.WorksheetCommentsPart != null)
{
- var root = wsPart.WorksheetCommentsPart.Comments;
+ var root = worksheetPart.WorksheetCommentsPart.Comments;
var authors = root.GetFirstChild().ChildElements;
var comments = root.GetFirstChild().ChildElements;
// **** MAYBE FUTURE SHAPE SIZE SUPPORT
- XDocument xdoc = GetCommentVmlFile(wsPart);
+ XDocument xdoc = GetCommentVmlFile(worksheetPart);
foreach (Comment c in comments)
{
@@ -438,15 +442,15 @@
#region Pivot tables
// Delay loading of pivot tables until all sheets have been loaded
- foreach (Sheet dSheet in sheets.OfType())
+ foreach (var dSheet in sheets.OfType())
{
- var wsPart = dSpreadsheet.WorkbookPart.GetPartById(dSheet.Id) as WorksheetPart;
+ var worksheetPart = dSpreadsheet.WorkbookPart.GetPartById(dSheet.Id) as WorksheetPart;
- if (wsPart != null)
+ if (worksheetPart != null)
{
var ws = (XLWorksheet)WorksheetsInternal.Worksheet(dSheet.Name);
- foreach (var pivotTablePart in wsPart.PivotTableParts)
+ foreach (var pivotTablePart in worksheetPart.PivotTableParts)
{
var pivotTableCacheDefinitionPart = pivotTablePart.PivotTableCacheDefinitionPart;
var pivotTableDefinition = pivotTablePart.PivotTableDefinition;
@@ -489,7 +493,7 @@
if (!String.IsNullOrWhiteSpace(StringValue.ToString(pivotTableDefinition?.RowHeaderCaption ?? String.Empty)))
pt.SetRowHeaderCaption(StringValue.ToString(pivotTableDefinition.RowHeaderCaption));
- pt.RelId = wsPart.GetIdOfPart(pivotTablePart);
+ pt.RelId = worksheetPart.GetIdOfPart(pivotTablePart);
pt.CacheDefinitionRelId = pivotTablePart.GetIdOfPart(pivotTableCacheDefinitionPart);
pt.WorkbookCacheRelId = dSpreadsheet.WorkbookPart.GetIdOfPart(pivotTableCacheDefinitionPart);
@@ -545,7 +549,7 @@
var pivotTableStyle = pivotTableDefinition.GetFirstChild();
if (pivotTableStyle != null)
{
- pt.Theme = (XLPivotTableTheme) Enum.Parse(typeof(XLPivotTableTheme), pivotTableStyle.Name);
+ pt.Theme = (XLPivotTableTheme)Enum.Parse(typeof(XLPivotTableTheme), pivotTableStyle.Name);
pt.ShowRowHeaders = pivotTableStyle.ShowRowHeaders;
pt.ShowColumnHeaders = pivotTableStyle.ShowColumnHeaders;
pt.ShowRowStripes = pivotTableStyle.ShowRowStripes;
@@ -586,7 +590,7 @@
if (pivotField != null)
{
- LoadFieldOptions(pf, pivotField);
+ LoadFieldOptions(pf, pivotField);
LoadSubtotals(pf, pivotField);
if (pf.SortType != null)
@@ -623,7 +627,7 @@
if (pivotField != null)
{
- LoadFieldOptions(pf, pivotField);
+ LoadFieldOptions(pf, pivotField);
LoadSubtotals(pf, pivotField);
if (pf.SortType != null)
@@ -1244,12 +1248,12 @@
var comment = definedName.Comment;
if (localSheetId == null)
{
- if (!NamedRanges.Any(nr => nr.Name == name))
+ if (NamedRanges.All(nr => nr.Name != name))
(NamedRanges as XLNamedRanges).Add(name, text, comment, true).Visible = visible;
}
else
{
- if (!Worksheet(Int32.Parse(localSheetId) + 1).NamedRanges.Any(nr => nr.Name == name))
+ if (Worksheet(Int32.Parse(localSheetId) + 1).NamedRanges.All(nr => nr.Name != name))
(Worksheet(Int32.Parse(localSheetId) + 1).NamedRanges as XLNamedRanges).Add(name, text, comment, true).Visible = visible;
}
}
@@ -1559,7 +1563,6 @@
nf.NumberFormatId = (Int32)nfSource.NumberFormatId.Value;
else if (nfSource.FormatCode != null)
nf.Format = nfSource.FormatCode.Value;
-
}
private void LoadBorder(Border borderSource, IXLBorder border)
@@ -1728,7 +1731,7 @@
if (columns == null) return;
var wsDefaultColumn =
- columns.Elements().Where(c => c.Max == XLHelper.MaxColumnNumber).FirstOrDefault();
+ columns.Elements().FirstOrDefault(c => c.Max == XLHelper.MaxColumnNumber);
if (wsDefaultColumn != null && wsDefaultColumn.Width != null)
ws.ColumnWidth = wsDefaultColumn.Width - ColumnWidthOffset;
@@ -2092,21 +2095,25 @@
{
String txt = dvs.SequenceOfReferences.InnerText;
if (String.IsNullOrWhiteSpace(txt)) continue;
- foreach (var dvt in txt.Split(' ').Select(rangeAddress => ws.Range(rangeAddress).DataValidation))
+ foreach (var rangeAddress in txt.Split(' '))
{
- if (dvs.AllowBlank != null) dvt.IgnoreBlanks = dvs.AllowBlank;
- if (dvs.ShowDropDown != null) dvt.InCellDropdown = !dvs.ShowDropDown.Value;
- if (dvs.ShowErrorMessage != null) dvt.ShowErrorMessage = dvs.ShowErrorMessage;
- if (dvs.ShowInputMessage != null) dvt.ShowInputMessage = dvs.ShowInputMessage;
- if (dvs.PromptTitle != null) dvt.InputTitle = dvs.PromptTitle;
- if (dvs.Prompt != null) dvt.InputMessage = dvs.Prompt;
- if (dvs.ErrorTitle != null) dvt.ErrorTitle = dvs.ErrorTitle;
- if (dvs.Error != null) dvt.ErrorMessage = dvs.Error;
- if (dvs.ErrorStyle != null) dvt.ErrorStyle = dvs.ErrorStyle.Value.ToClosedXml();
- if (dvs.Type != null) dvt.AllowedValues = dvs.Type.Value.ToClosedXml();
- if (dvs.Operator != null) dvt.Operator = dvs.Operator.Value.ToClosedXml();
- if (dvs.Formula1 != null) dvt.MinValue = dvs.Formula1.Text;
- if (dvs.Formula2 != null) dvt.MaxValue = dvs.Formula2.Text;
+ using (var range = ws.Range(rangeAddress))
+ {
+ var dvt = range.SetDataValidation();
+ if (dvs.AllowBlank != null) dvt.IgnoreBlanks = dvs.AllowBlank;
+ if (dvs.ShowDropDown != null) dvt.InCellDropdown = !dvs.ShowDropDown.Value;
+ if (dvs.ShowErrorMessage != null) dvt.ShowErrorMessage = dvs.ShowErrorMessage;
+ if (dvs.ShowInputMessage != null) dvt.ShowInputMessage = dvs.ShowInputMessage;
+ if (dvs.PromptTitle != null) dvt.InputTitle = dvs.PromptTitle;
+ if (dvs.Prompt != null) dvt.InputMessage = dvs.Prompt;
+ if (dvs.ErrorTitle != null) dvt.ErrorTitle = dvs.ErrorTitle;
+ if (dvs.Error != null) dvt.ErrorMessage = dvs.Error;
+ if (dvs.ErrorStyle != null) dvt.ErrorStyle = dvs.ErrorStyle.Value.ToClosedXml();
+ if (dvs.Type != null) dvt.AllowedValues = dvs.Type.Value.ToClosedXml();
+ if (dvs.Operator != null) dvt.Operator = dvs.Operator.Value.ToClosedXml();
+ if (dvs.Formula1 != null) dvt.MinValue = dvs.Formula1.Text;
+ if (dvs.Formula2 != null) dvt.MaxValue = dvs.Formula2.Text;
+ }
}
}
}
diff --git a/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs b/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs
index cfe251c..a0423df 100644
--- a/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs
+++ b/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs
@@ -73,7 +73,7 @@
Int32 id = _relIds[relType].Count + 1;
while (true)
{
- String relId = String.Format("rId{0}", id);
+ String relId = String.Concat("rId", id);
if (!_relIds[relType].Contains(relId))
{
_relIds[relType].Add(relId);
diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs
index 887ff16..82b319a 100644
--- a/ClosedXML/Excel/XLWorkbook_Save.cs
+++ b/ClosedXML/Excel/XLWorkbook_Save.cs
@@ -212,6 +212,8 @@
// Adds child parts and generates content of the specified part.
private void CreateParts(SpreadsheetDocument document, SaveOptions options)
{
+ this.SuspendEvents();
+
var context = new SaveContext();
var workbookPart = document.WorkbookPart ?? document.AddWorkbookPart();
@@ -263,15 +265,10 @@
foreach (var worksheet in WorksheetsInternal.Cast().OrderBy(w => w.Position))
{
- //context.RelIdGenerator.Reset(RelType.);
WorksheetPart worksheetPart;
var wsRelId = worksheet.RelId;
if (workbookPart.Parts.Any(p => p.RelationshipId == wsRelId))
- {
worksheetPart = (WorksheetPart)workbookPart.GetPartById(wsRelId);
- var wsPartsToRemove = worksheetPart.TableDefinitionParts.ToList();
- wsPartsToRemove.ForEach(tdp => worksheetPart.DeletePart(tdp));
- }
else
worksheetPart = workbookPart.AddNewPart(wsRelId);
@@ -314,10 +311,10 @@
}
// Remove any orphaned references - maybe more types?
- foreach (var orphan in worksheetPart.Worksheet.OfType().Where(lg => !worksheetPart.Parts.Any(p => p.RelationshipId == lg.Id)))
+ foreach (var orphan in worksheetPart.Worksheet.OfType().Where(lg => worksheetPart.Parts.All(p => p.RelationshipId != lg.Id)))
worksheetPart.Worksheet.RemoveChild(orphan);
- foreach (var orphan in worksheetPart.Worksheet.OfType().Where(d => !worksheetPart.Parts.Any(p => p.RelationshipId == d.Id)))
+ foreach (var orphan in worksheetPart.Worksheet.OfType().Where(d => worksheetPart.Parts.All(p => p.RelationshipId != d.Id)))
worksheetPart.Worksheet.RemoveChild(orphan);
}
@@ -348,6 +345,8 @@
// Clear list of deleted worksheets to prevent errors on multiple saves
worksheets.Deleted.Clear();
+
+ this.ResumeEvents();
}
private void DeleteComments(WorksheetPart worksheetPart, XLWorksheet worksheet, SaveContext context)
@@ -396,22 +395,60 @@
}
}
- private static void GenerateTables(XLWorksheet worksheet, WorksheetPart worksheetPart, SaveContext context)
+ private static void GenerateTables(XLWorksheet worksheet, WorksheetPart worksheetPart, SaveContext context, XLWSContentManager cm)
{
- worksheetPart.Worksheet.RemoveAllChildren();
+ var tables = worksheet.Tables as XLTables;
- if (!worksheet.Tables.Any()) return;
-
- foreach (var table in worksheet.Tables)
+ TableParts tableParts;
+ if (worksheetPart.Worksheet.Elements().Any())
{
- var tableRelId = context.RelIdGenerator.GetNext(RelType.Workbook);
-
- var xlTable = (XLTable)table;
- xlTable.RelId = tableRelId;
-
- var tableDefinitionPart = worksheetPart.AddNewPart(tableRelId);
- GenerateTableDefinitionPartContent(tableDefinitionPart, xlTable, context);
+ tableParts = worksheetPart.Worksheet.Elements().First();
}
+ else
+ {
+ var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.TableParts);
+ tableParts = new TableParts();
+ worksheetPart.Worksheet.InsertAfter(tableParts, previousElement);
+ }
+ cm.SetElement(XLWSContentManager.XLWSContents.TableParts, tableParts);
+
+ foreach (var deletedTableRelId in tables.Deleted)
+ {
+ if (worksheetPart.TableDefinitionParts != null)
+ {
+ var tableDefinitionPart = worksheetPart.GetPartById(deletedTableRelId);
+ worksheetPart.DeletePart(tableDefinitionPart);
+
+ var tablePartsToRemove = tableParts.OfType().Where(tp => tp.Id?.Value == deletedTableRelId).ToList();
+ tablePartsToRemove.ForEach(tp => tableParts.RemoveChild(tp));
+ }
+ }
+
+ tables.Deleted.Clear();
+
+ foreach (var xlTable in worksheet.Tables.Cast())
+ {
+ if (String.IsNullOrEmpty(xlTable.RelId))
+ xlTable.RelId = context.RelIdGenerator.GetNext(RelType.Workbook);
+
+ var relId = xlTable.RelId;
+
+ TableDefinitionPart tableDefinitionPart;
+ if (worksheetPart.HasPartWithId(relId))
+ tableDefinitionPart = worksheetPart.GetPartById(relId) as TableDefinitionPart;
+ else
+ tableDefinitionPart = worksheetPart.AddNewPart(relId);
+
+ GenerateTableDefinitionPartContent(tableDefinitionPart, xlTable, context);
+
+ if (!tableParts.OfType().Any(tp => tp.Id == xlTable.RelId))
+ {
+ var tablePart = new TablePart { Id = xlTable.RelId };
+ tableParts.AppendChild(tablePart);
+ }
+ }
+
+ tableParts.Count = (UInt32)worksheet.Tables.Count();
}
private void GenerateExtendedFilePropertiesPartContent(ExtendedFilePropertiesPart extendedFilePropertiesPart)
@@ -628,14 +665,14 @@
{
if (String.IsNullOrWhiteSpace(xlSheet.RelId))
{
- rId = String.Format("rId{0}", xlSheet.SheetId);
+ rId = String.Concat("rId", xlSheet.SheetId);
context.RelIdGenerator.AddValues(new List { rId }, RelType.Workbook);
}
else
rId = xlSheet.RelId;
}
- if (!workbook.Sheets.Cast().Any(s => s.Id == rId))
+ if (workbook.Sheets.Cast().All(s => s.Id != rId))
{
var newSheet = new Sheet
{
@@ -1793,8 +1830,7 @@
return name;
}
- private static void GenerateTableDefinitionPartContent(TableDefinitionPart tableDefinitionPart, XLTable xlTable,
- SaveContext context)
+ private static void GenerateTableDefinitionPartContent(TableDefinitionPart tableDefinitionPart, XLTable xlTable, SaveContext context)
{
context.TableId++;
var reference = xlTable.RangeAddress.FirstAddress + ":" + xlTable.RangeAddress.LastAddress;
@@ -2323,7 +2359,7 @@
{
pf.SubtotalCaption = xlpf.SubtotalCaption;
}
-
+
if (pt.ClassicPivotTableLayout)
{
pf.Outline = false;
@@ -2531,6 +2567,7 @@
}
#region Excel 2010 Features
+
if (xlpf.RepeatItemLabels)
{
var pivotFieldExtensionList = new PivotFieldExtensionList();
@@ -2545,6 +2582,7 @@
pivotFieldExtensionList.AppendChild(pivotFieldExtension);
pf.AppendChild(pivotFieldExtensionList);
}
+
#endregion Excel 2010 Features
pivotFields.AppendChild(pf);
@@ -2569,7 +2607,7 @@
pivotTableDefinition.AppendChild(rowItems);
}
- if (!pt.ColumnLabels.Any(cl => cl.CustomName != XLConstants.PivotTableValuesSentinalLabel))
+ if (pt.ColumnLabels.All(cl => cl.CustomName == XLConstants.PivotTableValuesSentinalLabel))
{
for (int i = 0; i < pt.Values.Count(); i++)
{
@@ -2779,7 +2817,7 @@
var rowNumber = c.Address.RowNumber;
var columnNumber = c.Address.ColumnNumber;
- var shapeId = String.Format("_x0000_s{0}", c.Comment.ShapeId);
+ var shapeId = String.Concat("_x0000_s", c.Comment.ShapeId);
// Unique per cell (workbook?), e.g.: "_x0000_s1026"
var anchor = GetAnchor(c);
var textBox = GetTextBox(c.Comment.Style);
@@ -2820,7 +2858,7 @@
Style = GetCommentStyle(c),
FillColor = "#" + c.Comment.Style.ColorsAndLines.FillColor.Color.ToHex().Substring(2),
StrokeColor = "#" + c.Comment.Style.ColorsAndLines.LineColor.Color.ToHex().Substring(2),
- StrokeWeight = String.Format(CultureInfo.InvariantCulture, "{0}pt", c.Comment.Style.ColorsAndLines.LineWeight),
+ StrokeWeight = String.Concat(c.Comment.Style.ColorsAndLines.LineWeight.ToInvariantString(), "pt"),
InsetMode = c.Comment.Style.Margins.Automatic ? InsetMarginValues.Auto : InsetMarginValues.Custom
};
if (!String.IsNullOrWhiteSpace(c.Comment.Style.Web.AlternateText))
@@ -3068,11 +3106,11 @@
var retVal = new Vml.TextBox { Style = sb.ToString() };
var dm = ds.Margins;
if (!dm.Automatic)
- retVal.Inset = String.Format("{0}in,{1}in,{2}in,{3}in",
- dm.Left.ToInvariantString(),
- dm.Top.ToInvariantString(),
- dm.Right.ToInvariantString(),
- dm.Bottom.ToInvariantString());
+ retVal.Inset = String.Concat(
+ dm.Left.ToInvariantString(), "in,",
+ dm.Top.ToInvariantString(), "in,",
+ dm.Right.ToInvariantString(), "in,",
+ dm.Bottom.ToInvariantString(), "in");
return retVal;
}
@@ -3109,11 +3147,11 @@
var lrOffset = Convert.ToInt32(lastCell.WorksheetRow().Height - (heightFromRows - cHeight));
return new Anchor
{
- Text = string.Format("{0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}",
- fcNumber, fcOffset,
- frNumber, frOffset,
- lcNumber, lcOffset,
- lrNumber, lrOffset
+ Text = string.Concat(
+ fcNumber, ", ", fcOffset, ", ",
+ frNumber, ", ", frOffset, ", ",
+ lcNumber, ", ", lcOffset, ", ",
+ lrNumber, ", ", lrOffset
)
};
}
@@ -3558,7 +3596,7 @@
&& f.NumberFormatId != null && styleInfo.NumberFormatId == f.NumberFormatId
&& f.ApplyFill != null && f.ApplyFill == ApplyFill(styleInfo)
&& f.ApplyBorder != null && f.ApplyBorder == ApplyBorder(styleInfo)
- && AlignmentsAreEqual(f.Alignment, styleInfo.Style.Alignment)
+ && (f.Alignment == null || AlignmentsAreEqual(f.Alignment, styleInfo.Style.Alignment))
&& ProtectionsAreEqual(f.Protection, styleInfo.Style.Protection)
;
}
@@ -4178,8 +4216,6 @@
if (worksheetPart.Worksheet == null)
worksheetPart.Worksheet = new Worksheet();
- GenerateTables(xlWorksheet, worksheetPart, context);
-
if (
!worksheetPart.Worksheet.NamespaceDeclarations.Contains(new KeyValuePair("r",
"http://schemas.openxmlformats.org/officeDocument/2006/relationships")))
@@ -4597,6 +4633,13 @@
xlWorksheet.Internals.CellsCollection.deleted.Remove(r.Key);
}
+ var tableTotalCells = new HashSet(
+ xlWorksheet.Tables
+ .Where(table => table.ShowTotalsRow)
+ .SelectMany(table =>
+ table.TotalsRow().CellsUsed())
+ .Select(cell => cell.Address));
+
var distinctRows = xlWorksheet.Internals.CellsCollection.RowsCollection.Keys.Union(xlWorksheet.Internals.RowsCollection.Keys);
var noRows = !sheetData.Elements().Any();
foreach (var distinctRow in distinctRows.OrderBy(r => r))
@@ -4742,7 +4785,7 @@
cell.CellValue = null;
}
- else if (xlCell.TableCellType() == XLTableCellType.Total)
+ else if (tableTotalCells.Contains(xlCell.Address))
{
var table = xlWorksheet.Tables.First(t => t.AsRange().Contains(xlCell));
field = table.Fields.First(f => f.Column.ColumnNumber() == xlCell.Address.ColumnNumber) as XLTableField;
@@ -4942,7 +4985,7 @@
}
var exlst = from c in xlWorksheet.ConditionalFormats where c.ConditionalFormatType == XLConditionalFormatType.DataBar && c.Colors.Count > 1 && typeof(IXLConditionalFormat).IsAssignableFrom(c.GetType()) select c;
- if (exlst != null && exlst.Count() > 0)
+ if (exlst != null && exlst.Any())
{
if (!worksheetPart.Worksheet.Elements().Any())
{
@@ -5331,20 +5374,7 @@
#region Tables
- worksheetPart.Worksheet.RemoveAllChildren();
- {
- var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.TableParts);
- worksheetPart.Worksheet.InsertAfter(new TableParts(), previousElement);
- }
-
- var tableParts = worksheetPart.Worksheet.Elements().First();
- cm.SetElement(XLWSContentManager.XLWSContents.TableParts, tableParts);
-
- tableParts.Count = (UInt32)xlWorksheet.Tables.Count();
- foreach (
- var tablePart in
- from XLTable xlTable in xlWorksheet.Tables select new TablePart { Id = xlTable.RelId })
- tableParts.AppendChild(tablePart);
+ GenerateTables(xlWorksheet, worksheetPart, context, cm);
#endregion Tables
@@ -5368,6 +5398,7 @@
if (xlWorksheet.Pictures.Any())
RebasePictureIds(worksheetPart);
+ var tableParts = worksheetPart.Worksheet.Elements().First();
if (xlWorksheet.Pictures.Any() && !worksheetPart.Worksheet.OfType().Any())
{
var worksheetDrawing = new Drawing { Id = worksheetPart.GetIdOfPart(worksheetPart.DrawingsPart) };
@@ -5383,7 +5414,6 @@
worksheetPart.DeletePart(worksheetPart.DrawingsPart);
}
-
#endregion Drawings
#region LegacyDrawing
@@ -5400,7 +5430,6 @@
cm.SetElement(XLWSContentManager.XLWSContents.LegacyDrawing, worksheetPart.Worksheet.Elements().First());
}
-
}
#endregion LegacyDrawing
@@ -5554,12 +5583,14 @@
filterColumn.Append(top101);
break;
+
case XLFilterType.Dynamic:
var dynamicFilter = new DynamicFilter
{ Type = xlFilterColumn.DynamicType.ToOpenXml(), Val = xlFilterColumn.DynamicValue };
filterColumn.Append(dynamicFilter);
break;
+
case XLFilterType.DateTimeGrouping:
var dateTimeGroupFilters = new Filters();
foreach (var filter in kp.Value)
@@ -5594,7 +5625,6 @@
filterColumn.Append(filters);
break;
-
}
autoFilter.Append(filterColumn);
}
diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs
index b888e92..c47cc79 100644
--- a/ClosedXML/Excel/XLWorksheet.cs
+++ b/ClosedXML/Excel/XLWorksheet.cs
@@ -1,13 +1,11 @@
using ClosedXML.Excel.CalcEngine;
using ClosedXML.Excel.Drawings;
using ClosedXML.Excel.Misc;
-using ClosedXML.Extensions;
using System;
using System.Collections.Generic;
using System.Drawing;
using System.IO;
using System.Linq;
-using System.Text;
namespace ClosedXML.Excel
{
@@ -615,7 +613,7 @@
targetSheet.NamedRanges.Add(nr.Name, ranges);
}
- foreach (XLTable t in Tables.Cast())
+ foreach (var t in Tables.Cast())
{
String tableName = t.Name;
var table = targetSheet.Tables.Any(tt => tt.Name == tableName)
@@ -652,34 +650,6 @@
return targetSheet;
}
- private String ReplaceRelativeSheet(string newSheetName, String value)
- {
- if (String.IsNullOrWhiteSpace(value)) return value;
-
- var newValue = new StringBuilder();
- var addresses = value.Split(',');
- foreach (var address in addresses)
- {
- var pair = address.Split('!');
- if (pair.Length == 2)
- {
- String sheetName = pair[0];
- if (sheetName.StartsWith("'"))
- sheetName = sheetName.Substring(1, sheetName.Length - 2);
-
- String name = sheetName.ToLower().Equals(Name.ToLower())
- ? newSheetName
- : sheetName;
- newValue.Append(String.Format("{0}!{1}", name.EscapeSheetName(), pair[1]));
- }
- else
- {
- newValue.Append(address);
- }
- }
- return newValue.ToString();
- }
-
public new IXLHyperlinks Hyperlinks { get; private set; }
IXLDataValidations IXLWorksheet.DataValidations
@@ -1219,30 +1189,36 @@
private void ShiftConditionalFormattingColumns(XLRange range, int columnsShifted)
{
- Int32 firstColumn = range.RangeAddress.FirstAddress.ColumnNumber;
- if (firstColumn == 1) return;
+ if (!ConditionalFormats.Any()) return;
+ Int32 firstCol = range.RangeAddress.FirstAddress.ColumnNumber;
+ if (firstCol == 1) return;
- Int32 lastColumn = range.RangeAddress.FirstAddress.ColumnNumber + columnsShifted - 1;
- Int32 firstRow = range.RangeAddress.FirstAddress.RowNumber;
- Int32 lastRow = range.RangeAddress.LastAddress.RowNumber;
- var insertedRange = Range(firstRow, firstColumn, lastRow, lastColumn);
- var fc = insertedRange.FirstColumn();
- var model = fc.ColumnLeft();
- Int32 modelFirstRow = model.RangeAddress.FirstAddress.RowNumber;
- if (ConditionalFormats.Any(cf => cf.Range.Intersects(model)))
+ int colNum = columnsShifted > 0 ? firstCol - 1 : firstCol;
+ var model = Column(colNum).AsRange();
+
+ foreach (var cf in ConditionalFormats.ToList())
{
- for (Int32 ro = firstRow; ro <= lastRow; ro++)
+ var cfAddress = cf.Range.RangeAddress;
+ if (cf.Range.Intersects(model))
{
- using (var cellModel = model.Cell(ro - modelFirstRow + 1).AsRange())
- foreach (var cf in ConditionalFormats.Where(cf => cf.Range.Intersects(cellModel)).ToList())
- {
- using (var r = Range(ro, firstColumn, ro, lastColumn)) r.AddConditionalFormat(cf);
- }
+ cf.Range = Range(cfAddress.FirstAddress.RowNumber,
+ cfAddress.FirstAddress.ColumnNumber,
+ cfAddress.LastAddress.RowNumber,
+ cfAddress.LastAddress.ColumnNumber + columnsShifted);
}
+ else if (cfAddress.FirstAddress.ColumnNumber >= firstCol)
+ {
+ cf.Range = Range(cfAddress.FirstAddress.RowNumber,
+ Math.Max(cfAddress.FirstAddress.ColumnNumber + columnsShifted, firstCol),
+ cfAddress.LastAddress.RowNumber,
+ cfAddress.LastAddress.ColumnNumber + columnsShifted);
+ }
+ if (cf.Range.RangeAddress.IsInvalid ||
+ cf.Range.RangeAddress.FirstAddress.ColumnNumber > cf.Range.RangeAddress.LastAddress.ColumnNumber)
+ ConditionalFormats.Remove(f => f == cf);
}
- insertedRange.Dispose();
+
model.Dispose();
- fc.Dispose();
}
private void WorksheetRangeShiftedRows(XLRange range, int rowsShifted)
@@ -1287,69 +1263,36 @@
private void ShiftConditionalFormattingRows(XLRange range, int rowsShifted)
{
+ if (!ConditionalFormats.Any()) return;
Int32 firstRow = range.RangeAddress.FirstAddress.RowNumber;
if (firstRow == 1) return;
- SuspendEvents();
- IXLRangeAddress usedAddress;
- using (var rangeUsed = range.Worksheet.RangeUsed(true))
+ int rowNum = rowsShifted > 0 ? firstRow - 1 : firstRow;
+ var model = Row(rowNum).AsRange();
+
+ foreach (var cf in ConditionalFormats.ToList())
{
- usedAddress = rangeUsed == null ? range.RangeAddress : rangeUsed.RangeAddress;
- }
- ResumeEvents();
-
- if (firstRow < usedAddress.FirstAddress.RowNumber) firstRow = usedAddress.FirstAddress.RowNumber;
-
- Int32 lastRow = range.RangeAddress.FirstAddress.RowNumber + rowsShifted - 1;
- if (lastRow > usedAddress.LastAddress.RowNumber) lastRow = usedAddress.LastAddress.RowNumber;
-
- Int32 firstColumn = range.RangeAddress.FirstAddress.ColumnNumber;
- if (firstColumn < usedAddress.FirstAddress.ColumnNumber) firstColumn = usedAddress.FirstAddress.ColumnNumber;
-
- Int32 lastColumn = range.RangeAddress.LastAddress.ColumnNumber;
- if (lastColumn > usedAddress.LastAddress.ColumnNumber) lastColumn = usedAddress.LastAddress.ColumnNumber;
-
- var insertedRange = Range(firstRow, firstColumn, lastRow, lastColumn);
- var fr = insertedRange.FirstRow();
- var model = fr.RowAbove();
- Int32 modelFirstColumn = model.RangeAddress.FirstAddress.ColumnNumber;
- if (ConditionalFormats.Any(cf => cf.Range.Intersects(model)))
- {
- for (Int32 co = firstColumn; co <= lastColumn; co++)
+ var cfAddress = cf.Range.RangeAddress;
+ if (cf.Range.Intersects(model))
{
- using (var cellModel = model.Cell(co - modelFirstColumn + 1).AsRange())
- foreach (var cf in ConditionalFormats.Where(cf => cf.Range.Intersects(cellModel)).ToList())
- {
- using (var r = Range(firstRow, co, lastRow, co)) r.AddConditionalFormat(cf);
- }
+ cf.Range = Range(cfAddress.FirstAddress.RowNumber,
+ cfAddress.FirstAddress.ColumnNumber,
+ cfAddress.LastAddress.RowNumber + rowsShifted,
+ cfAddress.LastAddress.ColumnNumber);
}
+ else if (cfAddress.FirstAddress.RowNumber >= firstRow)
+ {
+ cf.Range = Range(Math.Max(cfAddress.FirstAddress.RowNumber + rowsShifted, firstRow),
+ cfAddress.FirstAddress.ColumnNumber,
+ cfAddress.LastAddress.RowNumber + rowsShifted,
+ cfAddress.LastAddress.ColumnNumber);
+ }
+ if (cf.Range.RangeAddress.IsInvalid ||
+ cf.Range.RangeAddress.FirstAddress.RowNumber > cf.Range.RangeAddress.LastAddress.RowNumber)
+ ConditionalFormats.Remove(f => f == cf);
}
- insertedRange.Dispose();
+
model.Dispose();
- fr.Dispose();
- }
-
- internal void BreakConditionalFormatsIntoCells(List addresses)
- {
- var newConditionalFormats = new XLConditionalFormats();
- SuspendEvents();
- foreach (var conditionalFormat in ConditionalFormats)
- {
- foreach (XLCell cell in conditionalFormat.Range.Cells(c => !addresses.Contains(c.Address)))
- {
- var row = cell.Address.RowNumber;
- var column = cell.Address.ColumnLetter;
- var newConditionalFormat = new XLConditionalFormat(cell.AsRange(), true);
- newConditionalFormat.CopyFrom(conditionalFormat);
- newConditionalFormat.Values.Values.Where(f => f.IsFormula)
- .ForEach(f => f._value = XLHelper.ReplaceRelative(f.Value, row, column));
- newConditionalFormats.Add(newConditionalFormat);
- }
- conditionalFormat.Range.Dispose();
- }
- ResumeEvents();
- newConditionalFormats.Consolidate();
- ConditionalFormats = newConditionalFormats;
}
private void MoveNamedRangesRows(XLRange range, int rowsShifted, IXLNamedRanges namedRanges)
@@ -1586,6 +1529,7 @@
{
return Pictures.Add(imageFile, name);
}
+
public override Boolean IsEntireRow()
{
return true;
@@ -1605,6 +1549,5 @@
else
this.Cell(ro, co).SetValue(value);
}
-
}
}
diff --git a/ClosedXML/Excel/XLWorksheets.cs b/ClosedXML/Excel/XLWorksheets.cs
index cfb0478..94cace4 100644
--- a/ClosedXML/Excel/XLWorksheets.cs
+++ b/ClosedXML/Excel/XLWorksheets.cs
@@ -14,16 +14,16 @@
private readonly XLWorkbook _workbook;
private readonly Dictionary _worksheets = new Dictionary();
+ internal ICollection Deleted { get; private set; }
#endregion Constructor
- public HashSet Deleted = new HashSet();
-
#region Constructor
public XLWorksheets(XLWorkbook workbook)
{
_workbook = workbook;
+ Deleted = new HashSet();
}
#endregion Constructor
diff --git a/ClosedXML/Extensions/StringExtensions.cs b/ClosedXML/Extensions/StringExtensions.cs
index e4a5009..cd2e4db 100644
--- a/ClosedXML/Extensions/StringExtensions.cs
+++ b/ClosedXML/Extensions/StringExtensions.cs
@@ -1,8 +1,4 @@
using System;
-using System.Collections.Generic;
-using System.Linq;
-using System.Text;
-using System.Threading.Tasks;
namespace ClosedXML.Extensions
{
@@ -12,9 +8,9 @@
{
if (sheetName.Contains("'") ||
sheetName.Contains(" "))
- return string.Format("'{0}'", sheetName.Replace("'", "''"));
-
- return sheetName;
+ return string.Concat('\'', sheetName.Replace("'", "''"), '\'');
+ else
+ return sheetName;
}
internal static string UnescapeSheetName(this String sheetName)
@@ -41,6 +37,5 @@
hash ^= pLength;
return hash.ToString("X");
}
-
}
}
diff --git a/ClosedXML/Utils/OpenXmlHelper.cs b/ClosedXML/Utils/OpenXmlHelper.cs
index 6f0c7a2..3e1af4a 100644
--- a/ClosedXML/Utils/OpenXmlHelper.cs
+++ b/ClosedXML/Utils/OpenXmlHelper.cs
@@ -11,7 +11,7 @@
public static bool GetBooleanValueAsBool(BooleanValue value, bool defaultValue)
{
- return value == null ? defaultValue : value.Value;
+ return (value?.HasValue ?? false) ? value.Value : defaultValue;
}
}
-}
\ No newline at end of file
+}
diff --git a/ClosedXML_Sandbox/Program.cs b/ClosedXML_Sandbox/Program.cs
index bbbe81d..e277048 100644
--- a/ClosedXML_Sandbox/Program.cs
+++ b/ClosedXML_Sandbox/Program.cs
@@ -2,7 +2,7 @@
namespace ClosedXML_Sandbox
{
- internal class Program
+ internal static class Program
{
private static void Main(string[] args)
{
@@ -10,9 +10,13 @@
PerformanceRunner.TimeAction(PerformanceRunner.OpenTestFile);
Console.WriteLine();
+ // Disable this block by default - I don't use it often
+#if false
+
Console.WriteLine("Running {0}", nameof(PerformanceRunner.RunInsertTable));
PerformanceRunner.TimeAction(PerformanceRunner.RunInsertTable);
Console.WriteLine();
+#endif
Console.WriteLine("Running {0}", nameof(PerformanceRunner.RunInsertTableWithStyles));
PerformanceRunner.TimeAction(PerformanceRunner.RunInsertTableWithStyles);
diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj
index cff8196..4bb58e0 100644
--- a/ClosedXML_Tests/ClosedXML_Tests.csproj
+++ b/ClosedXML_Tests/ClosedXML_Tests.csproj
@@ -91,6 +91,7 @@
+
diff --git a/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatShiftTests.cs b/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatShiftTests.cs
new file mode 100644
index 0000000..bb1caed
--- /dev/null
+++ b/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatShiftTests.cs
@@ -0,0 +1,109 @@
+using ClosedXML.Excel;
+using NUnit.Framework;
+using System;
+using System.Linq;
+
+namespace ClosedXML_Tests.Excel.ConditionalFormats
+{
+ [TestFixture]
+ public class ConditionalFormatShiftTests
+ {
+ [Test]
+ public void CFShiftedOnColumnInsert()
+ {
+ using (var wb = new XLWorkbook())
+ {
+ var ws = wb.AddWorksheet("CFShift");
+ ws.Range("A1:A1").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.AirForceBlue);
+ ws.Range("A2:B2").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.AliceBlue);
+ ws.Range("A3:C3").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Alizarin);
+ ws.Range("B4:B6").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Almond);
+ ws.Range("C7:D7").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Amaranth);
+ ws.Cells("A1:D7").Value = 1;
+
+ ws.Column(2).InsertColumnsAfter(2);
+ var cf = ws.ConditionalFormats.ToArray();
+
+ Assert.AreEqual(5, cf.Length);
+ Assert.AreEqual("A1:A1", cf[0].Range.RangeAddress.ToString());
+ Assert.AreEqual("A2:D2", cf[1].Range.RangeAddress.ToString());
+ Assert.AreEqual("A3:E3", cf[2].Range.RangeAddress.ToString());
+ Assert.AreEqual("B4:D6", cf[3].Range.RangeAddress.ToString());
+ Assert.AreEqual("E7:F7", cf[4].Range.RangeAddress.ToString());
+ }
+ }
+
+ [Test]
+ public void CFShiftedOnRowInsert()
+ {
+ using (var wb = new XLWorkbook())
+ {
+ var ws = wb.AddWorksheet("CFShift");
+ ws.Range("A1:A1").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.AirForceBlue);
+ ws.Range("B1:B2").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.AliceBlue);
+ ws.Range("C1:C3").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Alizarin);
+ ws.Range("D2:F2").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Almond);
+ ws.Range("G4:G5").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Amaranth);
+ ws.Cells("A1:G5").Value = 1;
+
+ ws.Row(2).InsertRowsBelow(2);
+ var cf = ws.ConditionalFormats.ToArray();
+
+ Assert.AreEqual(5, cf.Length);
+ Assert.AreEqual("A1:A1", cf[0].Range.RangeAddress.ToString());
+ Assert.AreEqual("B1:B4", cf[1].Range.RangeAddress.ToString());
+ Assert.AreEqual("C1:C5", cf[2].Range.RangeAddress.ToString());
+ Assert.AreEqual("D2:F4", cf[3].Range.RangeAddress.ToString());
+ Assert.AreEqual("G6:G7", cf[4].Range.RangeAddress.ToString());
+ }
+ }
+
+ [Test]
+ public void CFShiftedOnColumnDelete()
+ {
+ using (var wb = new XLWorkbook())
+ {
+ var ws = wb.AddWorksheet("CFShift");
+ ws.Range("A1:A1").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.AirForceBlue);
+ ws.Range("A2:B2").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.AliceBlue);
+ ws.Range("A3:C3").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Alizarin);
+ ws.Range("B4:B6").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Almond);
+ ws.Range("C7:D7").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Amaranth);
+ ws.Cells("A1:D7").Value = 1;
+
+ ws.Column(2).Delete();
+ var cf = ws.ConditionalFormats.ToArray();
+
+ Assert.AreEqual(4, cf.Length);
+ Assert.AreEqual("A1:A1", cf[0].Range.RangeAddress.ToString());
+ Assert.AreEqual("A2:A2", cf[1].Range.RangeAddress.ToString());
+ Assert.AreEqual("A3:B3", cf[2].Range.RangeAddress.ToString());
+ Assert.AreEqual("B7:C7", cf[3].Range.RangeAddress.ToString());
+ }
+ }
+
+ [Test]
+ public void CFShiftedOnRowDelete()
+ {
+ using (var wb = new XLWorkbook())
+ {
+ var ws = wb.AddWorksheet("CFShift");
+ ws.Range("A1:A1").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.AirForceBlue);
+ ws.Range("B1:B2").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.AliceBlue);
+ ws.Range("C1:C3").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Alizarin);
+ ws.Range("D2:F2").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Almond);
+ ws.Range("G4:G5").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Amaranth);
+ ws.Cells("A1:G5").Value = 1;
+
+ ws.Row(2).Delete();
+ var cf = ws.ConditionalFormats.ToArray();
+
+ Assert.AreEqual(4, cf.Length);
+ Assert.AreEqual("A1:A1", cf[0].Range.RangeAddress.ToString());
+ Assert.AreEqual("B1:B1", cf[1].Range.RangeAddress.ToString());
+ Assert.AreEqual("C1:C2", cf[2].Range.RangeAddress.ToString());
+ Assert.AreEqual("G3:G4", cf[3].Range.RangeAddress.ToString());
+ }
+ }
+ }
+}
diff --git a/ClosedXML_Tests/Excel/DataValidations/DataValidationTests.cs b/ClosedXML_Tests/Excel/DataValidations/DataValidationTests.cs
index a27fd8a..2a966d7 100644
--- a/ClosedXML_Tests/Excel/DataValidations/DataValidationTests.cs
+++ b/ClosedXML_Tests/Excel/DataValidations/DataValidationTests.cs
@@ -75,7 +75,7 @@
Assert.AreEqual("Sheet1!A1", ws2.Cell("B1").DataValidation.Value);
}
- [Test]
+ [Test, Ignore("Wait for proper formula shifting (#686)")]
public void Validation_3()
{
var wb = new XLWorkbook();
@@ -98,7 +98,7 @@
Assert.AreEqual("A2", ws.Cell("B2").DataValidation.Value);
}
- [Test]
+ [Test, Ignore("Wait for proper formula shifting (#686)")]
public void Validation_5()
{
var wb = new XLWorkbook();
@@ -155,5 +155,95 @@
Assert.AreEqual("Error", ws.DataValidations.Single().ErrorTitle);
}
+
+ [Test]
+ [TestCase("A1:C3", 5, false, "A1:C3")]
+ [TestCase("A1:C3", 2, false, "A1:C4")]
+ [TestCase("A1:C3", 1, false, "A2:C4")]
+ [TestCase("A1:C3", 5, true, "A1:C3")]
+ [TestCase("A1:C3", 2, true, "A1:C4")]
+ [TestCase("A1:C3", 1, true, "A2:C4")]
+ public void DataValidationShiftedOnRowInsert(string initialAddress, int rowNum, bool setValue, string expectedAddress)
+ {
+ //Arrange
+ var wb = new XLWorkbook();
+ var ws = wb.Worksheets.Add("DataValidation");
+ var validation = ws.Range(initialAddress).SetDataValidation();
+ validation.WholeNumber.Between(0, 100);
+ if (setValue)
+ ws.Range(initialAddress).Value = 50;
+
+ //Act
+ ws.Row(rowNum).InsertRowsAbove(1);
+
+ //Assert
+ Assert.AreEqual(1, ws.DataValidations.Count());
+ Assert.AreEqual(1, ws.DataValidations.First().Ranges.Count);
+ Assert.AreEqual(expectedAddress, ws.DataValidations.First().Ranges.First().RangeAddress.ToString());
+ }
+
+ [Test]
+ [TestCase("A1:C3", 5, false, "A1:C3")]
+ [TestCase("A1:C3", 2, false, "A1:D3")]
+ [TestCase("A1:C3", 1, false, "B1:D3")]
+ [TestCase("A1:C3", 5, true, "A1:C3")]
+ [TestCase("A1:C3", 2, true, "A1:D3")]
+ [TestCase("A1:C3", 1, true, "B1:D3")]
+ public void DataValidationShiftedOnColumnInsert(string initialAddress, int columnNum, bool setValue, string expectedAddress)
+ {
+ //Arrange
+ var wb = new XLWorkbook();
+ var ws = wb.Worksheets.Add("DataValidation");
+ var validation = ws.Range(initialAddress).SetDataValidation();
+ validation.WholeNumber.Between(0, 100);
+ if (setValue)
+ ws.Range(initialAddress).Value = 50;
+
+ //Act
+ ws.Column(columnNum).InsertColumnsBefore(1);
+
+ //Assert
+ Assert.AreEqual(1, ws.DataValidations.Count());
+ Assert.AreEqual(1, ws.DataValidations.First().Ranges.Count);
+ Assert.AreEqual(expectedAddress, ws.DataValidations.First().Ranges.First().RangeAddress.ToString());
+ }
+
+ [Test]
+ public void DataValidationClearSplitsRange()
+ {
+ using (var wb = new XLWorkbook())
+ {
+ var ws = wb.Worksheets.Add("DataValidation");
+ var validation = ws.Range("A1:C3").SetDataValidation();
+ validation.WholeNumber.Between(0, 100);
+
+ //Act
+ ws.Cell("B2").Clear(XLClearOptions.ContentsAndFormats);
+
+ //Assert
+ Assert.IsFalse(ws.Cell("B2").HasDataValidation);
+ Assert.IsTrue(ws.Range("A1:C3").Cells().Where(c => c.Address.ToString() != "B2").All(c => c.HasDataValidation));
+ }
+ }
+
+ [Test]
+ public void NewDataValidationSplitsRange()
+ {
+ using (var wb = new XLWorkbook())
+ {
+ var ws = wb.Worksheets.Add("DataValidation");
+ var validation = ws.Range("A1:C3").SetDataValidation();
+ validation.WholeNumber.Between(10, 100);
+
+ //Act
+ ws.Cell("B2").NewDataValidation.WholeNumber.Between(-100, -0);
+
+ //Assert
+ Assert.AreEqual("-100", ws.Cell("B2").DataValidation.MinValue);
+ Assert.IsTrue(ws.Range("A1:C3").Cells().Where(c => c.Address.ToString() != "B2").All(c => c.HasDataValidation));
+ Assert.IsTrue(ws.Range("A1:C3").Cells().Where(c => c.Address.ToString() != "B2")
+ .All(c => c.DataValidation.MinValue == "10"));
+ }
+ }
}
-}
\ No newline at end of file
+}
diff --git a/ClosedXML_Tests/Excel/Tables/TablesTests.cs b/ClosedXML_Tests/Excel/Tables/TablesTests.cs
index 9d30ab8..f8fcddc 100644
--- a/ClosedXML_Tests/Excel/Tables/TablesTests.cs
+++ b/ClosedXML_Tests/Excel/Tables/TablesTests.cs
@@ -441,6 +441,41 @@
}
[Test]
+ public void CanDeleteTable()
+ {
+ var l = new List()
+ {
+ new TestObjectWithAttributes() { Column1 = "a", Column2 = "b", MyField = 4, UnOrderedColumn = 999 },
+ new TestObjectWithAttributes() { Column1 = "c", Column2 = "d", MyField = 5, UnOrderedColumn = 777 }
+ };
+
+ using (var ms = new MemoryStream())
+ {
+ using (var wb = new XLWorkbook())
+ {
+ var ws = wb.AddWorksheet("Sheet1");
+ ws.FirstCell().InsertTable(l);
+ //wb.SaveAs(ms);
+
+ wb.SaveAs(@"c:\temp\deletetable1.xlsx");
+ }
+
+ ms.Seek(0, SeekOrigin.Begin);
+
+ using (var wb = new XLWorkbook(@"c:\temp\deletetable1.xlsx"))
+ {
+ var ws = wb.Worksheets.First();
+ var table = ws.Tables.First();
+
+ ws.Tables.Remove(table.Name);
+ Assert.AreEqual(0, ws.Tables.Count());
+ //wb.Save();
+ wb.SaveAs(@"c:\temp\deletetable2.xlsx");
+ }
+ }
+ }
+
+ [Test]
public void CanDeleteTableField()
{
var l = new List()
diff --git a/ClosedXML_Tests/Resource/Examples/AutoFilter/RegularAutoFilter.xlsx b/ClosedXML_Tests/Resource/Examples/AutoFilter/RegularAutoFilter.xlsx
index 1e430a1..b4d626c 100644
--- a/ClosedXML_Tests/Resource/Examples/AutoFilter/RegularAutoFilter.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/AutoFilter/RegularAutoFilter.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/Loading/ChangingBasicTable.xlsx b/ClosedXML_Tests/Resource/Examples/Loading/ChangingBasicTable.xlsx
index a27f51c..a13f09b 100644
--- a/ClosedXML_Tests/Resource/Examples/Loading/ChangingBasicTable.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Loading/ChangingBasicTable.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/Misc/AddingDataSet.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/AddingDataSet.xlsx
index e7da636..3b92a3c 100644
--- a/ClosedXML_Tests/Resource/Examples/Misc/AddingDataSet.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Misc/AddingDataSet.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/Misc/AddingDataTableAsWorksheet.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/AddingDataTableAsWorksheet.xlsx
index 856717d..d2ccbb3 100644
--- a/ClosedXML_Tests/Resource/Examples/Misc/AddingDataTableAsWorksheet.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Misc/AddingDataTableAsWorksheet.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContents.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContents.xlsx
index 0d98696..7c902ac 100644
--- a/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContents.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContents.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/Misc/BasicTable.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/BasicTable.xlsx
index fc3c33f..3eb10a3 100644
--- a/ClosedXML_Tests/Resource/Examples/Misc/BasicTable.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Misc/BasicTable.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/Misc/Collections.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/Collections.xlsx
index d628b28..b562697 100644
--- a/ClosedXML_Tests/Resource/Examples/Misc/Collections.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Misc/Collections.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/Misc/CopyingRowsAndColumns.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/CopyingRowsAndColumns.xlsx
index f313701..add061f 100644
--- a/ClosedXML_Tests/Resource/Examples/Misc/CopyingRowsAndColumns.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Misc/CopyingRowsAndColumns.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx
index f28d115..4ae8bda 100644
--- a/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/Misc/DataValidation.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/DataValidation.xlsx
index c5979d8..c118e4c 100644
--- a/ClosedXML_Tests/Resource/Examples/Misc/DataValidation.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Misc/DataValidation.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/Misc/InsertingData.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/InsertingData.xlsx
index f5db9c4..eb57e22 100644
--- a/ClosedXML_Tests/Resource/Examples/Misc/InsertingData.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Misc/InsertingData.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/Misc/LambdaExpressions.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/LambdaExpressions.xlsx
index 368f016..b89706c 100644
--- a/ClosedXML_Tests/Resource/Examples/Misc/LambdaExpressions.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Misc/LambdaExpressions.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/Misc/MergeCells.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/MergeCells.xlsx
index 3860d72..eb5761d 100644
--- a/ClosedXML_Tests/Resource/Examples/Misc/MergeCells.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Misc/MergeCells.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/Misc/MergeMoves.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/MergeMoves.xlsx
index f0c9c03..df272ce 100644
--- a/ClosedXML_Tests/Resource/Examples/Misc/MergeMoves.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Misc/MergeMoves.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/Misc/ShowCase.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/ShowCase.xlsx
index c307592..d73b247 100644
--- a/ClosedXML_Tests/Resource/Examples/Misc/ShowCase.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Misc/ShowCase.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx
index 7e934f9..0c5a347 100644
--- a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/AddingRowToTables.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/AddingRowToTables.xlsx
index 2610099..83ae5ed 100644
--- a/ClosedXML_Tests/Resource/Examples/Ranges/AddingRowToTables.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Ranges/AddingRowToTables.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/ClearingRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/ClearingRanges.xlsx
index 95daf5a..9b8602f 100644
--- a/ClosedXML_Tests/Resource/Examples/Ranges/ClearingRanges.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Ranges/ClearingRanges.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/CopyingRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/CopyingRanges.xlsx
index faa24e2..b390e9d 100644
--- a/ClosedXML_Tests/Resource/Examples/Ranges/CopyingRanges.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Ranges/CopyingRanges.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/CurrentRowColumn.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/CurrentRowColumn.xlsx
index 2589a42..44ebfb8 100644
--- a/ClosedXML_Tests/Resource/Examples/Ranges/CurrentRowColumn.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Ranges/CurrentRowColumn.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/DeletingRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/DeletingRanges.xlsx
index bcb904c..05aeb97 100644
--- a/ClosedXML_Tests/Resource/Examples/Ranges/DeletingRanges.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Ranges/DeletingRanges.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/ShiftingRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/ShiftingRanges.xlsx
index 1c6a1ed..982f7c8 100644
--- a/ClosedXML_Tests/Resource/Examples/Ranges/ShiftingRanges.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Ranges/ShiftingRanges.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/SortExample.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/SortExample.xlsx
index ab0e398..3e820aa 100644
--- a/ClosedXML_Tests/Resource/Examples/Ranges/SortExample.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Ranges/SortExample.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRanges.xlsx
index f419dce..2696471 100644
--- a/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRanges.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRanges.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRangesPlus.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRangesPlus.xlsx
index 342a22c..bce7646 100644
--- a/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRangesPlus.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRangesPlus.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/Styles/StyleAlignment.xlsx b/ClosedXML_Tests/Resource/Examples/Styles/StyleAlignment.xlsx
index b2670c0..ec3232f 100644
--- a/ClosedXML_Tests/Resource/Examples/Styles/StyleAlignment.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Styles/StyleAlignment.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/Tables/InsertingTables.xlsx b/ClosedXML_Tests/Resource/Examples/Tables/InsertingTables.xlsx
index b7b2a41..041aded 100644
--- a/ClosedXML_Tests/Resource/Examples/Tables/InsertingTables.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Tables/InsertingTables.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/Tables/ResizingTables.xlsx b/ClosedXML_Tests/Resource/Examples/Tables/ResizingTables.xlsx
index 5d223ac..c86cfe2 100644
--- a/ClosedXML_Tests/Resource/Examples/Tables/ResizingTables.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Tables/ResizingTables.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/Tables/UsingTables.xlsx b/ClosedXML_Tests/Resource/Examples/Tables/UsingTables.xlsx
index 0b3b349..72086c7 100644
--- a/ClosedXML_Tests/Resource/Examples/Tables/UsingTables.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Tables/UsingTables.xlsx
Binary files differ