diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs
index 6e10787..fc3adf4 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs
@@ -489,7 +489,10 @@
int co = Address.ColumnNumber;
if (m.GetType().IsPrimitive || m is string || m is DateTime)
+ {
SetValue(m, ro, co);
+ co++;
+ }
else if (m.GetType().IsArray)
{
// dynamic arr = m;
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs
index 8fc67b2..ce88c57 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs
@@ -63,21 +63,10 @@
yield return Style;
int column = ColumnNumber();
- Int32 maxRow = 0;
- if (Worksheet.Internals.CellsCollection.ColumnsUsed.ContainsKey(column))
- maxRow = Worksheet.Internals.CellsCollection.MaxRowInColumn(column);
- if (Worksheet.Internals.RowsCollection.Count > 0)
- {
- Int32 maxInCollection = Worksheet.Internals.RowsCollection.Keys.Max();
- if (maxInCollection > maxRow)
- maxRow = maxInCollection;
- }
- if (maxRow > 0)
- {
- for (int ro = 1; ro <= maxRow; ro++)
- yield return Worksheet.Cell(ro, column).Style;
- }
+ foreach (var cell in Worksheet.Internals.CellsCollection.GetCellsInColumn(column))
+ yield return cell.Style;
+
UpdatingStyle = false;
}
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/IXLAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/IXLAddress.cs
index c2a29c1..dd26417 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/IXLAddress.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/IXLAddress.cs
@@ -12,7 +12,10 @@
Boolean FixedRow { get; }
Boolean FixedColumn { get; }
String ToStringRelative();
+ String ToStringRelative(Boolean includeSheet);
String ToStringFixed();
+ String ToStringFixed(XLReferenceStyle referenceStyle);
+ String ToStringFixed(XLReferenceStyle referenceStyle, Boolean includeSheet);
String ToString(XLReferenceStyle referenceStyle);
}
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/XLAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/XLAddress.cs
index 643642a..9aa3821 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/XLAddress.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/XLAddress.cs
@@ -214,11 +214,11 @@
}
if (referenceStyle == XLReferenceStyle.R1C1)
{
- return _rowNumber.ToStringLookup() + "," + ColumnNumber;
+ return String.Format("R{0}C{1}", _rowNumber.ToStringLookup(), ColumnNumber);
}
if (HasWorksheet && Worksheet.Workbook.ReferenceStyle == XLReferenceStyle.R1C1)
{
- return _rowNumber.ToStringLookup() + "," + ColumnNumber;
+ return String.Format("R{0}C{1}", _rowNumber.ToStringLookup(), ColumnNumber);
}
return ColumnLetter + _rowNumber.ToStringLookup();
}
@@ -229,15 +229,7 @@
return _trimmedAddress ?? (_trimmedAddress = ColumnLetter + _rowNumber.ToStringLookup());
}
- public string ToStringRelative()
- {
- return GetTrimmedAddress();
- }
- public string ToStringFixed()
- {
- return "$" + ColumnLetter + "$" + _rowNumber.ToStringLookup();
- }
#endregion
#region Operator Overloads
@@ -283,11 +275,7 @@
{
return true;
}
- if (ReferenceEquals(left, null))
- {
- return false;
- }
- return left.Equals(right);
+ return !ReferenceEquals(left, null) && left.Equals(right);
}
public static Boolean operator !=(XLAddress left, XLAddress right)
@@ -339,5 +327,51 @@
}
#endregion
#endregion
+
+ public String ToStringRelative()
+ {
+ return ToStringRelative(false);
+ }
+
+ public String ToStringFixed()
+ {
+ return ToStringFixed(XLReferenceStyle.Default);
+ }
+
+ public String ToStringRelative(Boolean includeSheet)
+ {
+ if (includeSheet)
+ return String.Format("'{0}'!{1}",
+ Worksheet.Name,
+ GetTrimmedAddress());
+
+ return GetTrimmedAddress();
+ }
+
+ public String ToStringFixed(XLReferenceStyle referenceStyle)
+ {
+ return ToStringFixed(referenceStyle, false);
+ }
+
+ public String ToStringFixed(XLReferenceStyle referenceStyle, Boolean includeSheet)
+ {
+ String address;
+ if (referenceStyle == XLReferenceStyle.A1)
+ address = String.Format("${0}${1}", ColumnLetter, _rowNumber.ToStringLookup());
+ else if (referenceStyle == XLReferenceStyle.R1C1)
+ address = String.Format("R{0}C{1}", _rowNumber.ToStringLookup(), ColumnNumber);
+ else if (HasWorksheet && Worksheet.Workbook.ReferenceStyle == XLReferenceStyle.R1C1)
+ address = String.Format("R{0}C{1}", _rowNumber.ToStringLookup(), ColumnNumber);
+ else
+ address = String.Format("${0}${1}", ColumnLetter, _rowNumber.ToStringLookup());
+
+ if (includeSheet)
+ return String.Format("'{0}'!{1}",
+ Worksheet.Name,
+ address);
+
+ return address;
+ }
+
}
}
\ No newline at end of file
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs
index ef8223b..c32b109 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs
@@ -5,6 +5,9 @@
public enum XLWorksheetVisibility { Visible, Hidden, VeryHidden }
public interface IXLWorksheet : IXLRangeBase
{
+ ///
+ /// Gets the workbook that contains this worksheet
+ ///
XLWorkbook Workbook { get; }
///
@@ -42,8 +45,13 @@
IXLRow FirstRow();
///
/// Gets the first row of the worksheet that contains a cell with a value.
+ /// Formatted empty cells do not count.
///
IXLRow FirstRowUsed();
+ ///
+ /// Gets the first row of the worksheet that contains a cell with a value.
+ ///
+ /// If set to true formatted empty cells will count as used.
IXLRow FirstRowUsed(Boolean includeFormats);
///
/// Gets the last row of the worksheet.
@@ -53,6 +61,10 @@
/// Gets the last row of the worksheet that contains a cell with a value.
///
IXLRow LastRowUsed();
+ ///
+ /// Gets the last row of the worksheet that contains a cell with a value.
+ ///
+ /// If set to true formatted empty cells will count as used.
IXLRow LastRowUsed(Boolean includeFormats);
///
/// Gets the first column of the worksheet.
@@ -62,6 +74,10 @@
/// Gets the first column of the worksheet that contains a cell with a value.
///
IXLColumn FirstColumnUsed();
+ ///
+ /// Gets the first column of the worksheet that contains a cell with a value.
+ ///
+ /// If set to true formatted empty cells will count as used.
IXLColumn FirstColumnUsed(Boolean includeFormats);
///
/// Gets the last column of the worksheet.
@@ -71,6 +87,10 @@
/// Gets the last column of the worksheet that contains a cell with a value.
///
IXLColumn LastColumnUsed();
+ ///
+ /// Gets the last column of the worksheet that contains a cell with a value.
+ ///
+ /// If set to true formatted empty cells will count as used.
IXLColumn LastColumnUsed(Boolean includeFormats);
///
/// Gets a collection of all columns in this worksheet.
@@ -184,10 +204,10 @@
/// .
IXLRange Range(int firstCellRow, int firstCellColumn, int lastCellRow, int lastCellColumn);
- /// Gets the number of rows in this range.
+ /// Gets the number of rows in this worksheet.
int RowCount();
- /// Gets the number of columns in this range.
+ /// Gets the number of columns in this worksheet.
int ColumnCount();
///
@@ -249,10 +269,26 @@
///
IXLSheetView SheetView { get; }
+ ///
+ /// Gets the Excel table of the given index
+ ///
+ /// Index of the table to return
IXLTable Table(Int32 index);
+ ///
+ /// Gets the Excel table of the given name
+ ///
+ /// Name of the table to return
IXLTable Table(String name);
+ ///
+ /// Gets an object to manage this worksheet's Excel tables
+ ///
IXLTables Tables { get; }
+ ///
+ /// Copies the
+ ///
+ ///
+ ///
IXLWorksheet CopyTo(String newSheetName);
IXLWorksheet CopyTo(String newSheetName, Int32 position);
IXLWorksheet CopyTo(XLWorkbook workbook, String newSheetName);
@@ -322,5 +358,7 @@
IXLPivotTable PivotTable(String name);
IXLPivotTables PivotTables { get; }
+
+
}
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs
index df6539c..1480fb2 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs
@@ -6,22 +6,22 @@
{
internal class XLNamedRange: IXLNamedRange
{
- private List rangeList = new List();
- private XLNamedRanges namedRanges;
+ private readonly List _rangeList = new List();
+ private readonly XLNamedRanges _namedRanges;
public XLNamedRange(XLNamedRanges namedRanges , String rangeName, String range, String comment = null)
{
Name = rangeName;
- rangeList.Add(range);
+ _rangeList.Add(range);
Comment = comment;
- this.namedRanges = namedRanges;
+ _namedRanges = namedRanges;
}
public XLNamedRange(XLNamedRanges namedRanges, String rangeName, IXLRanges ranges, String comment = null)
{
Name = rangeName;
- ranges.ForEach(r => rangeList.Add(r.ToStringFixed()));
+ ranges.ForEach(r => _rangeList.Add(r.RangeAddress.ToStringFixed(XLReferenceStyle.A1)));
Comment = comment;
- this.namedRanges = namedRanges;
+ _namedRanges = namedRanges;
}
public String Name { get; set; }
@@ -30,12 +30,8 @@
get
{
var ranges = new XLRanges();
- foreach (var rangeAddress in rangeList)
+ foreach (var rangeToAdd in from rangeAddress in _rangeList select rangeAddress.Split('!') into byExclamation let wsName = byExclamation[0].Replace("'", "") let rng = byExclamation[1] select _namedRanges.Workbook.WorksheetsInternal.Worksheet(wsName).Range(rng))
{
- var byExclamation = rangeAddress.Split('!');
- var wsName = byExclamation[0].Replace("'", "");
- var rng = byExclamation[1];
- var rangeToAdd = namedRanges.Workbook.WorksheetsInternal.Worksheet(wsName).Range(rng);
ranges.Add(rangeToAdd);
}
return ranges;
@@ -63,41 +59,40 @@
}
public IXLRanges Add(IXLRange range)
{
- var ranges = new XLRanges();
- ranges.Add(range);
+ var ranges = new XLRanges {range};
return Add(ranges);
}
public IXLRanges Add(IXLRanges ranges)
{
- ranges.ForEach(r => rangeList.Add(r.ToString()));
+ ranges.ForEach(r => _rangeList.Add(r.ToString()));
return ranges;
}
public void Delete()
{
- namedRanges.Delete(Name);
+ _namedRanges.Delete(Name);
}
public void Clear()
{
- rangeList.Clear();
+ _rangeList.Clear();
}
public void Remove(String rangeAddress)
{
- rangeList.Remove(rangeAddress);
+ _rangeList.Remove(rangeAddress);
}
public void Remove(IXLRange range)
{
- rangeList.Remove(range.ToString());
+ _rangeList.Remove(range.ToString());
}
public void Remove(IXLRanges ranges)
{
- ranges.ForEach(r => rangeList.Remove(r.ToString()));
+ ranges.ForEach(r => _rangeList.Remove(r.ToString()));
}
public override string ToString()
{
- String retVal = rangeList.Aggregate(String.Empty, (agg, r) => agg += r + ",");
+ String retVal = _rangeList.Aggregate(String.Empty, (agg, r) => agg + (r + ","));
if (retVal.Length > 0) retVal = retVal.Substring(0, retVal.Length - 1);
return retVal;
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs
index 87f3b4c..ab2d5ed 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs
@@ -6,19 +6,19 @@
{
internal class XLNamedRanges: IXLNamedRanges
{
- Dictionary namedRanges = new Dictionary();
+ readonly Dictionary _namedRanges = new Dictionary();
internal XLWorkbook Workbook { get; set; }
public XLNamedRanges(XLWorkbook workbook)
{
- this.Workbook = workbook;
+ Workbook = workbook;
}
#region IXLNamedRanges Members
public IXLNamedRange NamedRange(String rangeName)
{
- return namedRanges[rangeName];
+ return _namedRanges[rangeName];
}
public IXLNamedRange Add(String rangeName, String rangeAddress)
@@ -36,33 +36,32 @@
public IXLNamedRange Add(String rangeName, String rangeAddress, String comment )
{
var namedRange = new XLNamedRange(this, rangeName, rangeAddress, comment);
- namedRanges.Add(rangeName, namedRange);
+ _namedRanges.Add(rangeName, namedRange);
return namedRange;
}
public IXLNamedRange Add(String rangeName, IXLRange range, String comment)
{
- var ranges = new XLRanges();
- ranges.Add(range);
+ var ranges = new XLRanges {range};
return Add(rangeName, ranges, comment);
}
public IXLNamedRange Add(String rangeName, IXLRanges ranges, String comment)
{
var namedRange = new XLNamedRange(this, rangeName, ranges, comment);
- namedRanges.Add(rangeName, namedRange);
+ _namedRanges.Add(rangeName, namedRange);
return namedRange;
}
public void Delete(String rangeName)
{
- namedRanges.Remove(rangeName);
+ _namedRanges.Remove(rangeName);
}
public void Delete(Int32 rangeIndex)
{
- namedRanges.Remove(namedRanges.ElementAt(rangeIndex).Key);
+ _namedRanges.Remove(_namedRanges.ElementAt(rangeIndex).Key);
}
public void DeleteAll()
{
- namedRanges.Clear();
+ _namedRanges.Clear();
}
#endregion
@@ -71,7 +70,7 @@
public IEnumerator GetEnumerator()
{
- return namedRanges.Values.GetEnumerator();
+ return _namedRanges.Values.GetEnumerator();
}
#endregion
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeAddress.cs
index a194be7..8354d3d 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeAddress.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeAddress.cs
@@ -29,6 +29,9 @@
Boolean IsInvalid { get; set; }
String ToStringRelative();
+ String ToStringRelative(Boolean includeSheet);
String ToStringFixed();
+ String ToStringFixed(XLReferenceStyle referenceStyle);
+ String ToStringFixed(XLReferenceStyle referenceStyle, Boolean includeSheet);
}
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs
index c4256c4..817bee7 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs
@@ -213,9 +213,6 @@
///
IXLRange AsRange();
- String ToStringRelative();
- String ToStringFixed();
-
Boolean IsMerged();
Boolean IsEmpty();
Boolean IsEmpty(Boolean includeFormats);
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs
index 28252f4..bfba263 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs
@@ -109,12 +109,39 @@
public String ToStringRelative()
{
- return _firstAddress.ToStringRelative() + ":" + _lastAddress.ToStringRelative();
+ return ToStringRelative(false);
}
public String ToStringFixed()
{
- return _firstAddress.ToStringFixed() + ":" + _lastAddress.ToStringFixed();
+ return ToStringFixed(XLReferenceStyle.A1);
+ }
+
+ public String ToStringRelative(Boolean includeSheet)
+ {
+ if (includeSheet)
+ return String.Format("'{0}'!{1}:{2}",
+ Worksheet.Name,
+ _firstAddress.ToStringRelative(),
+ _lastAddress.ToStringRelative());
+
+ return _firstAddress.ToStringRelative() + ":" + _lastAddress.ToStringRelative();
+ }
+
+ public String ToStringFixed(XLReferenceStyle referenceStyle)
+ {
+ return ToStringFixed(referenceStyle, false);
+ }
+
+ public String ToStringFixed(XLReferenceStyle referenceStyle, Boolean includeSheet)
+ {
+ if (includeSheet)
+ return String.Format("'{0}'!{1}:{2}",
+ Worksheet.Name,
+ _firstAddress.ToStringFixed(referenceStyle),
+ _lastAddress.ToStringFixed(referenceStyle));
+
+ return _firstAddress.ToStringFixed(referenceStyle) + ":" + _lastAddress.ToStringFixed(referenceStyle);
}
public override string ToString()
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs
index bf78385..95e04a9 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs
@@ -47,8 +47,51 @@
foreach (IXLRange dvRange in dv.Ranges.Where(dvRange => dvRange.Intersects(this)))
{
dv.Ranges.Remove(dvRange);
- foreach (IXLCell c in dvRange.Cells().Where(c => !Contains(c.Address.ToString())))
- dv.Ranges.Add(c.AsRange());
+ 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)
+ {
+ dv.Ranges.Add(Worksheet.Column(column.ColumnNumber()).Column(
+ dvStart,
+ thisStart - 1));
+ dv.Ranges.Add(Worksheet.Column(column.ColumnNumber()).Column(
+ thisEnd + 1,
+ dvEnd));
+ }
+ 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)
+ dv.Ranges.Add(Worksheet.Column(column.ColumnNumber()).Column(coStart, coEnd));
+ }
+ }
+ }
+ else
+ {
+ dv.Ranges.Add(column);
+ }
+ }
+
if (dv.Ranges.Count() == 0)
dvEmpty.Add(dv);
}
@@ -301,21 +344,7 @@
return Worksheet.Range(RangeAddress.FirstAddress, RangeAddress.LastAddress);
}
-
-
- public string ToStringRelative()
- {
- return String.Format("'{0}'!{1}:{2}",
- Worksheet.Name,
- RangeAddress.FirstAddress.ToStringRelative(),
- RangeAddress.LastAddress.ToStringRelative());
- }
-
- public string ToStringFixed()
- {
- return String.Format("'{0}'!{1}:{2}", Worksheet.Name, RangeAddress.FirstAddress.ToStringFixed(),
- RangeAddress.LastAddress.ToStringFixed());
- }
+
public IXLRange AddToNamed(String rangeName)
{
@@ -336,10 +365,10 @@
if (namedRanges.Any(nr => String.Compare(nr.Name, rangeName, true) == 0))
{
var namedRange = namedRanges.Where(nr => String.Compare(nr.Name, rangeName, true) == 0).Single();
- namedRange.Add(Worksheet.Workbook, ToStringFixed());
+ namedRange.Add(Worksheet.Workbook, RangeAddress.ToStringFixed(XLReferenceStyle.A1, true));
}
else
- namedRanges.Add(rangeName, ToStringFixed(), comment);
+ namedRanges.Add(rangeName, RangeAddress.ToStringFixed(XLReferenceStyle.A1, true), comment);
return AsRange();
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs
index 6c8045d..7163ac9 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs
@@ -65,22 +65,9 @@
int row = RowNumber();
- Int32 maxColumn = 0;
- if (Worksheet.Internals.CellsCollection.RowsUsed.ContainsKey(row))
- maxColumn = Worksheet.Internals.CellsCollection.MaxColumnInRow(row);
+ foreach (var cell in Worksheet.Internals.CellsCollection.GetCellsInRow(row))
+ yield return cell.Style;
- if (Worksheet.Internals.ColumnsCollection.Count > 0)
- {
- Int32 maxInCollection = Worksheet.Internals.ColumnsCollection.Keys.Max();
- if (maxInCollection > maxColumn)
- maxColumn = maxInCollection;
- }
-
- if (maxColumn > 0)
- {
- for (int co = 1; co <= maxColumn; co++)
- yield return Worksheet.Cell(row, co).Style;
- }
UpdatingStyle = false;
}
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLProtection.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLProtection.cs
index bd4ac6f..b54c8bf 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLProtection.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLProtection.cs
@@ -4,37 +4,37 @@
{
internal class XLProtection : IXLProtection
{
- IXLStylized container;
+ readonly IXLStylized _container;
- private Boolean locked;
+ private Boolean _locked;
public Boolean Locked
{
get
{
- return locked;
+ return _locked;
}
set
{
- if (container != null && !container.UpdatingStyle)
- container.Styles.ForEach(s => s.Protection.Locked = value);
+ if (_container != null && !_container.UpdatingStyle)
+ _container.Styles.ForEach(s => s.Protection.Locked = value);
else
- locked = value;
+ _locked = value;
}
}
- private Boolean hidden;
+ private Boolean _hidden;
public Boolean Hidden
{
get
{
- return hidden;
+ return _hidden;
}
set
{
- if (container != null && !container.UpdatingStyle)
- container.Styles.ForEach(s => s.Protection.Hidden = value);
+ if (_container != null && !_container.UpdatingStyle)
+ _container.Styles.ForEach(s => s.Protection.Hidden = value);
else
- hidden = value;
+ _hidden = value;
}
}
@@ -47,12 +47,11 @@
public XLProtection(IXLStylized container, IXLProtection defaultProtection = null)
{
- this.container = container;
- if (defaultProtection != null)
- {
- locked = defaultProtection.Locked;
- hidden = defaultProtection.Hidden;
- }
+ _container = container;
+ if (defaultProtection == null) return;
+
+ _locked = defaultProtection.Locked;
+ _hidden = defaultProtection.Hidden;
}
#endregion
@@ -60,45 +59,36 @@
public bool Equals(IXLProtection other)
{
var otherP = other as XLProtection;
- return locked == otherP.locked
- && hidden == otherP.hidden;
+ if (otherP == null)
+ return false;
+
+ return _locked == otherP._locked
+ && _hidden == otherP._hidden;
}
public override bool Equals(object obj)
{
- return this.Equals((IXLProtection)obj);
+ return Equals((IXLProtection)obj);
}
public override int GetHashCode()
{
if (Locked)
- if (Hidden)
- return 11;
- else
- return 10;
- else
- if (Hidden)
- return 1;
- else
- return 0;
+ return Hidden ? 11 : 10;
+
+ return Hidden ? 1 : 0;
}
public override string ToString()
{
if (Locked)
- if (Hidden)
- return "Locked-Hidden";
- else
- return "Locked";
- else
- if (Hidden)
- return "Hidden";
- else
- return "None";
+ return Hidden ? "Locked-Hidden" : "Locked";
+
+ return Hidden ? "Hidden" : "None";
}
- public IXLStyle SetLocked() { Locked = true; return container.Style; } public IXLStyle SetLocked(Boolean value) { Locked = value; return container.Style; }
- public IXLStyle SetHidden() { Hidden = true; return container.Style; } public IXLStyle SetHidden(Boolean value) { Hidden = value; return container.Style; }
+ public IXLStyle SetLocked() { Locked = true; return _container.Style; } public IXLStyle SetLocked(Boolean value) { Locked = value; return _container.Style; }
+ public IXLStyle SetHidden() { Hidden = true; return _container.Style; } public IXLStyle SetHidden(Boolean value) { Hidden = value; return _container.Style; }
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs
index 1e18e3f..41c21c9 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs
@@ -509,6 +509,7 @@
get { return true; }
}
+ private Dictionary _unsupportedSheets = new Dictionary();
//--
#region Nested Type: XLLoadSource
private enum XLLoadSource
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs
index 1498904..4210dfd 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs
@@ -108,13 +108,19 @@
var fonts = s.Fonts;
var sheets = dSpreadsheet.WorkbookPart.Workbook.Sheets;
-
- foreach (OpenXmlElement sheet in sheets)
+ Int32 position = 0;
+ foreach (Sheet dSheet in sheets.OfType())
{
+ position++;
var sharedFormulasR1C1 = new Dictionary();
- var dSheet = ((Sheet) sheet);
- var wsPart = (WorksheetPart) dSpreadsheet.WorkbookPart.GetPartById(dSheet.Id);
+ var wsPart = dSpreadsheet.WorkbookPart.GetPartById(dSheet.Id) as WorksheetPart;
+
+ if (wsPart == null)
+ {
+ _unsupportedSheets.Add(position, dSheet.SheetId.Value);
+ continue;
+ }
var sheetName = dSheet.Name;
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs
index c0025f0..b2a2c6a 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs
@@ -357,10 +357,10 @@
{
int sheetId = (Int32)sheet.SheetId.Value;
- if (!WorksheetsInternal.Any(w => (w).SheetId == sheetId)) continue;
+ if (!WorksheetsInternal.Any(w => w.SheetId == sheetId)) continue;
var wks =
- WorksheetsInternal.Where(w => (w).SheetId == sheetId).Single();
+ WorksheetsInternal.Where(w => w.SheetId == sheetId).Single();
wks.RelId = sheet.Id;
sheet.Name = wks.Name;
}
@@ -390,24 +390,41 @@
}
var sheetElements = from sheet in workbook.Sheets.Elements()
- join worksheet in ((IEnumerable)WorksheetsInternal) on sheet.Id.Value
- equals worksheet.RelId
+ join worksheet in ((IEnumerable)WorksheetsInternal) on sheet.Id.Value equals worksheet.RelId
orderby worksheet.Position
select sheet;
UInt32 firstSheetVisible = 0;
Boolean foundVisible = false;
+ Int32 position = 0;
foreach (Sheet sheet in sheetElements)
{
- workbook.Sheets.RemoveChild(sheet);
- workbook.Sheets.AppendChild(sheet);
-
- if (foundVisible) continue;
-
- if (sheet.State == null || sheet.State == SheetStateValues.Visible)
- foundVisible = true;
+ position++;
+ if (_unsupportedSheets.ContainsKey(position))
+ {
+ Sheet unsupportedSheet =
+ workbook.Sheets.Elements().Where(s => s.SheetId == _unsupportedSheets[position]).First();
+ workbook.Sheets.RemoveChild(unsupportedSheet);
+ workbook.Sheets.AppendChild(unsupportedSheet);
+ _unsupportedSheets.Remove(position);
+ }
else
- firstSheetVisible++;
+ {
+ workbook.Sheets.RemoveChild(sheet);
+ workbook.Sheets.AppendChild(sheet);
+
+ if (foundVisible) continue;
+
+ if (sheet.State == null || sheet.State == SheetStateValues.Visible)
+ foundVisible = true;
+ else
+ firstSheetVisible++;
+ }
+ }
+ foreach (Sheet unsupportedSheet in _unsupportedSheets.Values.Select(sheetId => workbook.Sheets.Elements().Where(s => s.SheetId == sheetId).First()))
+ {
+ workbook.Sheets.RemoveChild(unsupportedSheet);
+ workbook.Sheets.AppendChild(unsupportedSheet);
}
var workbookView = workbook.BookViews.Elements().FirstOrDefault();
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs
index 2d175db..c032dea 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs
@@ -149,12 +149,22 @@
}
}
+ private const String InvalidNameChars = @":\/?*[]";
public String Name
{
get { return _name; }
set
{
- (Workbook.WorksheetsInternal).Rename(_name, value);
+ if (value.IndexOfAny(InvalidNameChars.ToCharArray()) != -1)
+ throw new ArgumentException("Worksheet names cannot contain any of the following characters: " + InvalidNameChars);
+
+ if (StringExtensions.IsNullOrWhiteSpace(value))
+ throw new ArgumentException("Worksheet names cannot be empty");
+
+ if (value.Length > 31)
+ throw new ArgumentException("Worksheet names cannot be more than 31 characters");
+
+ Workbook.WorksheetsInternal.Rename(_name, value);
_name = value;
}
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs b/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs
index 077cc4c..01898b7 100644
--- a/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs
@@ -1,16 +1,16 @@
using System;
using System.Globalization;
+using System.Runtime.CompilerServices;
using System.Text;
-using ClosedXML.Excel;
-namespace ClosedXML
+namespace ClosedXML.Excel
{
using System.Linq;
///
/// Common methods
///
- public static class ExcelHelper
+ internal static class ExcelHelper
{
public const int MinRowNumber = 1;
public const int MinColumnNumber = 1;
diff --git a/ClosedXML/ClosedXML/ClosedXML/Properties/AssemblyInfo.cs b/ClosedXML/ClosedXML/ClosedXML/Properties/AssemblyInfo.cs
index 0e64d15..cfc5b4c 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Properties/AssemblyInfo.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Properties/AssemblyInfo.cs
@@ -33,3 +33,4 @@
// [assembly: AssemblyVersion("1.0.*")]
[assembly: AssemblyVersion("0.56.3.0")]
[assembly: AssemblyFileVersion("0.56.3.0")]
+[assembly: System.Runtime.CompilerServices.InternalsVisibleTo("ClosedXML_Tests")]
\ No newline at end of file
diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataValidation.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataValidation.cs
index 04449e4..c0f9415 100644
--- a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataValidation.cs
+++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataValidation.cs
@@ -75,18 +75,48 @@
// Validate with multiple ranges
var ws2 = wb.Worksheets.Add("Validate Ranges");
- var rng1Validation = ws2.Ranges("A1:B2,B4:D7,F4:G5").DataValidation;
+ var rng1 = ws2.Ranges("A1:B2,B4:D7,F4:G5");
+ rng1.Style.Fill.SetBackgroundColor(XLColor.YellowGreen);
+ var rng1Validation = rng1.DataValidation;
rng1Validation.Decimal.EqualTo(1);
rng1Validation.IgnoreBlanks = false;
- var rng2Validation = ws2.Range("A11:E14").DataValidation;
+ var rng2 = ws2.Range("A11:E14");
+ rng2.Style.Fill.SetBackgroundColor(XLColor.YellowGreen);
+ var rng2Validation = rng2.DataValidation;
rng2Validation.Decimal.EqualTo(2);
rng2Validation.IgnoreBlanks = false;
- var rng3Validation = ws2.Range("B2:B12").DataValidation;
+ var rng3 = ws2.Range("B2:B12");
+ //rng3.Style.Fill.SetBackgroundColor(XLColor.YellowGreen);
+ var rng3Validation = rng3.DataValidation;
rng3Validation.Decimal.EqualTo(3);
rng3Validation.IgnoreBlanks = true;
+ var rng4 = ws2.Range("D5:D6");
+ //rng4.Style.Fill.SetBackgroundColor(XLColor.YellowGreen);
+ var rng4Validation = rng4.DataValidation;
+ rng4Validation.Decimal.EqualTo(4);
+ rng4Validation.IgnoreBlanks = true;
+
+ var rng5 = ws2.Range("C13:C14");
+ //rng5.Style.Fill.SetBackgroundColor(XLColor.YellowGreen);
+ var rng5Validation = rng5.DataValidation;
+ rng5Validation.Decimal.EqualTo(5);
+ rng5Validation.IgnoreBlanks = true;
+
+ var rng6 = ws2.Range("D11:D12");
+ //rng6.Style.Fill.SetBackgroundColor(XLColor.YellowGreen);
+ var rng6Validation =rng6.DataValidation;
+ rng6Validation.Decimal.EqualTo(5);
+ rng6Validation.IgnoreBlanks = true;
+
+ var rng7 = ws2.Range("G4:G5");
+ //rng7.Style.Fill.SetBackgroundColor(XLColor.YellowGreen);
+ var rng7Validation = rng7.DataValidation;
+ rng7Validation.Decimal.EqualTo(5);
+ rng7Validation.IgnoreBlanks = true;
+
wb.SaveAs(filePath);
}
diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj
index f53922d..f79f52f 100644
--- a/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj
+++ b/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj
@@ -64,7 +64,9 @@
+
+
diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Cells/XLCellTest.cs b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Cells/XLCellTest.cs
index 8ab8b17..41161d1 100644
--- a/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Cells/XLCellTest.cs
+++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Cells/XLCellTest.cs
@@ -95,5 +95,13 @@
var expected = String.Empty;
Assert.AreEqual(expected, actual);
}
+
+ [TestMethod()]
+ public void InsertData1()
+ {
+ var ws = new XLWorkbook().Worksheets.Add("Sheet1");
+ var range = ws.Cell(2, 2).InsertData(new[] { "a", "b", "c" });
+ Assert.AreEqual("'Sheet1'!B2:B4", range.ToString());
+ }
}
}
diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Coordinates/XLAddressTest.cs b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Coordinates/XLAddressTest.cs
new file mode 100644
index 0000000..119b7a3
--- /dev/null
+++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Coordinates/XLAddressTest.cs
@@ -0,0 +1,36 @@
+using ClosedXML.Excel;
+using Microsoft.VisualStudio.TestTools.UnitTesting;
+using System.Linq;
+using System;
+
+namespace ClosedXML_Tests
+{
+ [TestClass()]
+ public class XLAddressTest
+ {
+ [TestMethod()]
+ public void ToStringTest()
+ {
+ var ws = new XLWorkbook().Worksheets.Add("Sheet1");
+ var address = ws.Cell(1, 1).Address;
+
+ Assert.AreEqual("A1", address.ToString());
+ Assert.AreEqual("A1", address.ToString(XLReferenceStyle.A1));
+ Assert.AreEqual("R1C1", address.ToString(XLReferenceStyle.R1C1));
+ Assert.AreEqual("A1", address.ToString(XLReferenceStyle.Default));
+
+ Assert.AreEqual("A1", address.ToStringRelative());
+ Assert.AreEqual("'Sheet1'!A1", address.ToStringRelative(true));
+
+ Assert.AreEqual("$A$1", address.ToStringFixed());
+ Assert.AreEqual("$A$1", address.ToStringFixed(XLReferenceStyle.A1));
+ Assert.AreEqual("R1C1", address.ToStringFixed(XLReferenceStyle.R1C1));
+ Assert.AreEqual("$A$1", address.ToStringFixed(XLReferenceStyle.Default));
+ Assert.AreEqual("'Sheet1'!$A$1", address.ToStringFixed(XLReferenceStyle.A1, true));
+ Assert.AreEqual("'Sheet1'!R1C1", address.ToStringFixed(XLReferenceStyle.R1C1, true));
+ Assert.AreEqual("'Sheet1'!$A$1", address.ToStringFixed(XLReferenceStyle.Default, true));
+ }
+
+
+ }
+}
diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Misc/ExcelHelperTests.cs b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Misc/ExcelHelperTests.cs
index c89ab1b..73382d5 100644
--- a/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Misc/ExcelHelperTests.cs
+++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Misc/ExcelHelperTests.cs
@@ -2,7 +2,6 @@
using System.Text;
using System.Collections.Generic;
using System.Linq;
-using ClosedXML;
using ClosedXML.Excel;
using Microsoft.VisualStudio.TestTools.UnitTesting;
diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Ranges/XLRangeAddressTest.cs b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Ranges/XLRangeAddressTest.cs
new file mode 100644
index 0000000..e1baa58
--- /dev/null
+++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Ranges/XLRangeAddressTest.cs
@@ -0,0 +1,33 @@
+using ClosedXML.Excel;
+using Microsoft.VisualStudio.TestTools.UnitTesting;
+using System.Linq;
+using System;
+
+namespace ClosedXML_Tests
+{
+ [TestClass()]
+ public class XLRangeAddressTest
+ {
+ [TestMethod()]
+ public void ToStringTest()
+ {
+ var ws = new XLWorkbook().Worksheets.Add("Sheet1");
+ var address = ws.Cell(1, 1).AsRange().RangeAddress;
+
+ Assert.AreEqual("A1:A1", address.ToString());
+
+ Assert.AreEqual("A1:A1", address.ToStringRelative());
+ Assert.AreEqual("'Sheet1'!A1:A1", address.ToStringRelative(true));
+
+ Assert.AreEqual("$A$1:$A$1", address.ToStringFixed());
+ Assert.AreEqual("$A$1:$A$1", address.ToStringFixed(XLReferenceStyle.A1));
+ Assert.AreEqual("R1C1:R1C1", address.ToStringFixed(XLReferenceStyle.R1C1));
+ Assert.AreEqual("$A$1:$A$1", address.ToStringFixed(XLReferenceStyle.Default));
+ Assert.AreEqual("'Sheet1'!$A$1:$A$1", address.ToStringFixed(XLReferenceStyle.A1, true));
+ Assert.AreEqual("'Sheet1'!R1C1:R1C1", address.ToStringFixed(XLReferenceStyle.R1C1, true));
+ Assert.AreEqual("'Sheet1'!$A$1:$A$1", address.ToStringFixed(XLReferenceStyle.Default, true));
+ }
+
+
+ }
+}
diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/DataValidation.xlsx b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/DataValidation.xlsx
index cde9185..338b10e 100644
--- a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/DataValidation.xlsx
+++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/DataValidation.xlsx
Binary files differ
diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Ranges/DeletingRanges.xlsx b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Ranges/DeletingRanges.xlsx
index 4f0dce0..a3e8e16 100644
--- a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Ranges/DeletingRanges.xlsx
+++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Ranges/DeletingRanges.xlsx
Binary files differ
diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Styles/StyleRowsColumns.xlsx b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Styles/StyleRowsColumns.xlsx
index a7d2178..552b08a 100644
--- a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Styles/StyleRowsColumns.xlsx
+++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Styles/StyleRowsColumns.xlsx
Binary files differ