diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs index 00d1c88..8571774 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs @@ -178,5 +178,7 @@ IXLRichText RichText { get; } Boolean HasRichText { get; } + + Boolean IsMerged(); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index 2786343..514d5ad 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -1836,5 +1836,11 @@ }; #endregion + + + public Boolean IsMerged() + { + return Worksheet.Internals.MergedRanges.Any(AsRange().Intersects); + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs index 5a78bef..2dafc2f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs @@ -8,59 +8,65 @@ internal class XLCells : IXLCells, IXLStylized, IEnumerable { #region Fields - private readonly bool m_usedCellsOnly; - private readonly bool m_includeStyles; - private readonly List m_rangeAddresses = new List(); - private IXLStyle m_style; + + private readonly bool _includeFormats; + private readonly List _rangeAddresses = new List(); + private readonly bool _usedCellsOnly; + private IXLStyle _style; + #endregion + #region Constructor - public XLCells(bool entireWorksheet, bool usedCellsOnly, bool includeStyles) + + public XLCells(bool usedCellsOnly, bool includeFormats) { - m_style = new XLStyle(this, XLWorkbook.DefaultStyle); - m_usedCellsOnly = usedCellsOnly; - m_includeStyles = includeStyles; + _style = new XLStyle(this, XLWorkbook.DefaultStyle); + _usedCellsOnly = usedCellsOnly; + _includeFormats = includeFormats; } + #endregion + + #region IEnumerable Members + public IEnumerator GetEnumerator() { var cellsInRanges = new Dictionary>(); - foreach (var range in m_rangeAddresses) + foreach (XLRangeAddress range in _rangeAddresses) { HashSet hash; if (cellsInRanges.ContainsKey(range.Worksheet)) - { hash = cellsInRanges[range.Worksheet]; - } else { hash = new HashSet(); cellsInRanges.Add(range.Worksheet, hash); } - if (m_usedCellsOnly) + if (_usedCellsOnly) { var tmpRange = range; var addressList = range.Worksheet.Internals.CellsCollection.Keys - .Where(a => a.RowNumber >= tmpRange.FirstAddress.RowNumber && - a.RowNumber <= tmpRange.LastAddress.RowNumber && - a.ColumnNumber >= tmpRange.FirstAddress.ColumnNumber && - a.ColumnNumber <= tmpRange.LastAddress.ColumnNumber); + .Where(a => a.RowNumber >= tmpRange.FirstAddress.RowNumber && + a.RowNumber <= tmpRange.LastAddress.RowNumber && + a.ColumnNumber >= tmpRange.FirstAddress.ColumnNumber && + a.ColumnNumber <= tmpRange.LastAddress.ColumnNumber); - foreach (var a in addressList) + foreach (IXLAddress a in addressList) { if (!hash.Contains(a)) - { hash.Add(a); - } } } else { - var mm = new MinMax(); - mm.MinRow = range.FirstAddress.RowNumber; - mm.MaxRow = range.LastAddress.RowNumber; - mm.MinColumn = range.FirstAddress.ColumnNumber; - mm.MaxColumn = range.LastAddress.ColumnNumber; + var mm = new MinMax + { + MinRow = range.FirstAddress.RowNumber, + MaxRow = range.LastAddress.RowNumber, + MinColumn = range.FirstAddress.ColumnNumber, + MaxColumn = range.LastAddress.ColumnNumber + }; if (mm.MaxRow > 0 && mm.MaxColumn > 0) { for (Int32 ro = mm.MinRow; ro <= mm.MaxRow; ro++) @@ -69,107 +75,65 @@ { var address = new XLAddress(range.Worksheet, ro, co, false, false); if (!hash.Contains(address)) - { hash.Add(address); - } } } } } } - if (m_usedCellsOnly) + if (_usedCellsOnly) { - foreach (var cir in cellsInRanges) + foreach (KeyValuePair> cir in cellsInRanges) { var cellsCollection = cir.Key.Internals.CellsCollection; - foreach (var a in cir.Value) + foreach (IXLAddress a in cir.Value) { if (cellsCollection.ContainsKey(a)) { var cell = cellsCollection[a]; if (!StringExtensions.IsNullOrWhiteSpace((cell).InnerText) - || (m_includeStyles && !cell.Style.Equals(cir.Key.Style))) - { + || (_includeFormats && (!cell.Style.Equals(cir.Key.Style) || cell.IsMerged()))) yield return cell; - } } } - - //foreach (var cell in (cir.Key as XLWorksheet).Internals.CellsCollection - // .Where(kp => cir.Value.Contains(kp.Key) - // && (!StringExtensions.IsNullOrWhiteSpace((kp.Value as XLCell).InnerText) - // || (includeStyles && !kp.Value.Style.Equals(cir.Key.Style)))) - // .Select(kp => kp.Value)) - //{ - // yield return cell; - //} } } else { - foreach (var cir in cellsInRanges) + foreach (KeyValuePair> cir in cellsInRanges) { - foreach (var address in cir.Value) - { + foreach (IXLAddress address in cir.Value) yield return cir.Key.Cell(address); - } } } } + + #endregion + + #region IXLCells Members + IEnumerator IEnumerable.GetEnumerator() { - foreach (var cell in this) - { + foreach (XLCell cell in this) yield return cell; - } } + IEnumerator IEnumerable.GetEnumerator() { return GetEnumerator(); } - public void Add(XLRangeAddress rangeAddress) - { - m_rangeAddresses.Add(rangeAddress); - } - public void Add(XLCell cell) - { - m_rangeAddresses.Add(new XLRangeAddress(cell.Address, cell.Address)); - } - #region IXLStylized Members public IXLStyle Style { - get { return m_style; } + get { return _style; } set { - m_style = new XLStyle(this, value); - this.ForEach(c => c.Style = m_style); + _style = new XLStyle(this, value); + this.ForEach(c => c.Style = _style); } } - public IEnumerable Styles - { - get - { - UpdatingStyle = true; - yield return m_style; - foreach (var c in this) - { - yield return c.Style; - } - UpdatingStyle = false; - } - } - - public Boolean UpdatingStyle { get; set; } - - public IXLStyle InnerStyle - { - get { return m_style; } - set { m_style = new XLStyle(this, value); } - } - #endregion public Object Value { set { this.ForEach(c => c.Value = value); } @@ -206,6 +170,30 @@ set { this.ForEach(c => c.FormulaR1C1 = value); } } + #endregion + + #region IXLStylized Members + + public IEnumerable Styles + { + get + { + UpdatingStyle = true; + yield return _style; + foreach (XLCell c in this) + yield return c.Style; + UpdatingStyle = false; + } + } + + public Boolean UpdatingStyle { get; set; } + + public IXLStyle InnerStyle + { + get { return _style; } + set { _style = new XLStyle(this, value); } + } + public IXLRanges RangesUsed { get @@ -215,15 +203,31 @@ return retVal; } } + + #endregion + + public void Add(XLRangeAddress rangeAddress) + { + _rangeAddresses.Add(rangeAddress); + } + + public void Add(XLCell cell) + { + _rangeAddresses.Add(new XLRangeAddress(cell.Address, cell.Address)); + } + //-- - #region Nested type: MinMax + + #region Nested type: MinMax + private struct MinMax { - public Int32 MinRow; + public Int32 MaxColumn; public Int32 MaxRow; public Int32 MinColumn; - public Int32 MaxColumn; + public Int32 MinRow; } + #endregion } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs index 072375f..a813416 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs @@ -132,7 +132,7 @@ public new IXLCells Cells(String cellsInColumn) { - var retVal = new XLCells(false, false, false); + var retVal = new XLCells( false, false); var rangePairs = cellsInColumn.Split(','); foreach (var pair in rangePairs) { @@ -347,12 +347,9 @@ public IXLColumn AdjustToContents(Int32 startRow, Int32 endRow, Double minWidth, Double maxWidth) { Double colMaxWidth = minWidth; - foreach (var cell in Column(startRow, endRow).CellsUsed()) + foreach (XLCell c in Column(startRow, endRow).CellsUsed()) { - var c = cell as XLCell; - Boolean isMerged = CellIsMerged(c); - - if (!isMerged) + if (!c.IsMerged()) { Double thisWidthMax = 0; Int32 textRotation = c.Style.Alignment.TextRotation; @@ -540,11 +537,7 @@ return this; } - private Boolean CellIsMerged(IXLCell c) - { - var cellAsRange = c.AsRange(); - return Worksheet.Internals.MergedRanges.Any(m => cellAsRange.Intersects(m)); - } + public void Hide() { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs index 393f69f..6177765 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs @@ -209,7 +209,7 @@ public IXLCells Cells() { - var cells = new XLCells(false, false, false); + var cells = new XLCells( false, false); foreach (var container in columns) { cells.Add(container.RangeAddress); @@ -219,7 +219,7 @@ public IXLCells CellsUsed() { - var cells = new XLCells(false, true, false); + var cells = new XLCells( true, false); foreach (var container in columns) { cells.Add(container.RangeAddress); @@ -229,7 +229,7 @@ public IXLCells CellsUsed(Boolean includeStyles) { - var cells = new XLCells(false, true, includeStyles); + var cells = new XLCells( true, includeStyles); foreach (var container in columns) { cells.Add(container.RangeAddress); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs index b7773ad..96850b8 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -2,192 +2,60 @@ namespace ClosedXML.Excel { - public enum XLScope { Workbook, Worksheet }; + public enum XLScope + { + Workbook, + Worksheet + } ; public interface IXLRangeBase { - IXLWorksheet Worksheet { get; } /// - /// Returns the collection of cells. - /// - IXLCells Cells(); - - IXLCells Cells(String cells); - - /// - /// Returns the collection of cells that have a value. - /// - IXLCells CellsUsed(); - - /// - /// Returns the collection of cells that have a value. - /// - /// if set to true will return all cells with a value or a style different than the default. - IXLCells CellsUsed(Boolean includeStyles); - - /// - /// Gets an object with the boundaries of this range. + /// Gets an object with the boundaries of this range. /// IXLRangeAddress RangeAddress { get; } - /// - /// Returns the first cell of this range. - /// - IXLCell FirstCell(); - /// - /// Returns the first cell with a value of this range. - /// The cell's address is going to be ([First Row with a value], [First Column with a value]) - /// - IXLCell FirstCellUsed(); - - /// Returns the first cell with a value of this range. - /// The cell's address is going to be ([First Row with a value], [First Column with a value]) - /// if set to true will return all cells with a value or a style different than the default. - IXLCell FirstCellUsed(Boolean includeStyles); - /// - /// Returns the last cell of this range. - /// - IXLCell LastCell(); - /// - /// Returns the last cell with a value of this range. - /// The cell's address is going to be ([Last Row with a value], [Last Column with a value]) - /// - IXLCell LastCellUsed(); - - /// Returns the last cell with a value of this range. - /// The cell's address is going to be ([Last Row with a value], [Last Column with a value]) - /// if set to true will return all cells with a value or a style different than the default. - IXLCell LastCellUsed(Boolean includeStyles); /// - /// Determines whether this range contains the specified range (completely). - /// For partial matches use the range.Intersects method. - /// - /// The range address. - /// - /// true if this range contains the specified range; otherwise, false. - /// - Boolean Contains(String rangeAddress); - - /// - /// Determines whether this range contains the specified range (completely). - /// For partial matches use the range.Intersects method. - /// - /// The range to match. - /// - /// true if this range contains the specified range; otherwise, false. - /// - Boolean Contains(IXLRangeBase range); - - /// - /// Determines whether this range intersects the specified range. - /// For whole matches use the range.Contains method. - /// - /// The range address. - /// - /// true if this range intersects the specified range; otherwise, false. - /// - Boolean Intersects(String rangeAddress); - - /// - /// Determines whether this range contains the specified range. - /// For whole matches use the range.Contains method. - /// - /// The range to match. - /// - /// true if this range intersects the specified range; otherwise, false. - /// - Boolean Intersects(IXLRangeBase range); - - /// - /// Unmerges this range. - /// - IXLRange Unmerge(); - /// - /// Merges this range. - /// The contents and style of the merged cells will be equal to the first cell. - /// - IXLRange Merge(); - /// - /// Creates a named range out of this range. - /// If the named range exists, it will add this range to that named range. - /// The default scope for the named range is Workbook. - /// - /// Name of the range. - IXLRange AddToNamed(String rangeName); - - /// - /// Creates a named range out of this range. - /// If the named range exists, it will add this range to that named range. - /// Name of the range. - /// The scope for the named range. - IXLRange AddToNamed(String rangeName, XLScope scope); - - /// - /// Creates a named range out of this range. - /// If the named range exists, it will add this range to that named range. - /// Name of the range. - /// The scope for the named range. - /// The comments for the named range. - IXLRange AddToNamed(String rangeName, XLScope scope, String comment); - - /// - /// Clears the contents of this range (including styles). - /// - void Clear(); - - /// - /// Clears the styles of this range (preserving number formats). - /// - void ClearStyles(); - - /// - /// Sets the cells' value. - /// If the object is an IEnumerable ClosedXML will copy the collection's data into a table starting from each cell. - /// If the object is a range ClosedXML will copy the range starting from each cell. - /// Setting the value to an object (not IEnumerable/range) will call the object's ToString() method. - /// ClosedXML will try to translate it to the corresponding type, if it can't then the value will be left as a string. + /// Sets a value to every cell in this range. + /// If the object is an IEnumerable ClosedXML will copy the collection's data into a table starting from each cell. + /// If the object is a range ClosedXML will copy the range starting from each cell. + /// Setting the value to an object (not IEnumerable/range) will call the object's ToString() method. + /// ClosedXML will try to translate it to the corresponding type, if it can't then the value will be left as a string. /// /// - /// The object containing the value(s) to set. + /// The object containing the value(s) to set. /// Object Value { set; } - IXLRangeBase SetValue(T value); - /// - /// Sets the type of the cells' data. - /// Changing the data type will cause ClosedXML to covert the current value to the new data type. - /// An exception will be thrown if the current value cannot be converted to the new data type. + /// Sets the type of the cells' data. + /// Changing the data type will cause ClosedXML to covert the current value to the new data type. + /// An exception will be thrown if the current value cannot be converted to the new data type. /// /// - /// The type of the cell's data. + /// The type of the cell's data. /// - /// + /// XLCellValues DataType { set; } /// - /// Sets the cells' formula with A1 references. + /// Sets the cells' formula with A1 references. /// /// The formula with A1 references. String FormulaA1 { set; } /// - /// Sets the cells' formula with R1C1 references. + /// Sets the cells' formula with R1C1 references. /// /// The formula with R1C1 references. String FormulaR1C1 { set; } - /// - /// Converts this object to a range. - /// - IXLRange AsRange(); - IXLStyle Style { get; set; } /// - /// Gets or sets a value indicating whether this cell's text should be shared or not. + /// Gets or sets a value indicating whether this cell's text should be shared or not. /// /// /// If false the cell's text will not be shared and stored as an inline value. @@ -198,11 +66,157 @@ IXLDataValidation DataValidation { get; } + /// + /// Returns the collection of cells. + /// + IXLCells Cells(); + + IXLCells Cells(String cells); + + /// + /// Returns the collection of cells that have a value. Formats are ignored. + /// + IXLCells CellsUsed(); + + /// + /// Returns the collection of cells that have a value. + /// + /// if set to true will return all cells with a value or a style different than the default. + IXLCells CellsUsed(Boolean includeFormats); + + /// + /// Returns the first cell of this range. + /// + IXLCell FirstCell(); + + /// + /// Returns the first cell with a value of this range. Formats are ignored. + /// The cell's address is going to be ([First Row with a value], [First Column with a value]) + /// + IXLCell FirstCellUsed(); + + /// + /// Returns the first cell with a value of this range. + /// + /// The cell's address is going to be ([First Row with a value], [First Column with a value]) + /// if set to true will return all cells with a value or a style different than the default. + IXLCell FirstCellUsed(Boolean includeFormats); + + /// + /// Returns the last cell of this range. + /// + IXLCell LastCell(); + + /// + /// Returns the last cell with a value of this range. Formats are ignored. + /// The cell's address is going to be ([Last Row with a value], [Last Column with a value]) + /// + IXLCell LastCellUsed(); + + /// + /// Returns the last cell with a value of this range. + /// + /// The cell's address is going to be ([Last Row with a value], [Last Column with a value]) + /// if set to true will return all cells with a value or a style different than the default. + IXLCell LastCellUsed(Boolean includeFormats); + + /// + /// Determines whether this range contains the specified range (completely). + /// For partial matches use the range.Intersects method. + /// + /// The range address. + /// + /// true if this range contains the specified range; otherwise, false. + /// + Boolean Contains(String rangeAddress); + + /// + /// Determines whether this range contains the specified range (completely). + /// For partial matches use the range.Intersects method. + /// + /// The range to match. + /// + /// true if this range contains the specified range; otherwise, false. + /// + Boolean Contains(IXLRangeBase range); + + /// + /// Determines whether this range intersects the specified range. + /// For whole matches use the range.Contains method. + /// + /// The range address. + /// + /// true if this range intersects the specified range; otherwise, false. + /// + Boolean Intersects(String rangeAddress); + + /// + /// Determines whether this range contains the specified range. + /// For whole matches use the range.Contains method. + /// + /// The range to match. + /// + /// true if this range intersects the specified range; otherwise, false. + /// + Boolean Intersects(IXLRangeBase range); + + /// + /// Unmerges this range. + /// + IXLRange Unmerge(); + + /// + /// Merges this range. + /// The contents and style of the merged cells will be equal to the first cell. + /// + IXLRange Merge(); + + /// + /// Creates a named range out of this range. + /// If the named range exists, it will add this range to that named range. + /// The default scope for the named range is Workbook. + /// + /// Name of the range. + IXLRange AddToNamed(String rangeName); + + /// + /// Creates a named range out of this range. + /// If the named range exists, it will add this range to that named range. + /// Name of the range. + /// The scope for the named range. + /// + IXLRange AddToNamed(String rangeName, XLScope scope); + + /// + /// Creates a named range out of this range. + /// If the named range exists, it will add this range to that named range. + /// Name of the range. + /// The scope for the named range. + /// The comments for the named range. + /// + IXLRange AddToNamed(String rangeName, XLScope scope, String comment); + + /// + /// Clears the contents of this range (including styles). + /// + void Clear(); + + /// + /// Clears the styles of this range (preserving number formats). + /// + void ClearStyles(); + + IXLRangeBase SetValue(T value); + + /// + /// Converts this object to a range. + /// + IXLRange AsRange(); + String ToStringRelative(); String ToStringFixed(); - //IXLChart CreateChart(Int32 firstRow, Int32 firstColumn, Int32 lastRow, Int32 lastColumn); } -} +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs index 86383fa..ceecd48 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs @@ -22,7 +22,7 @@ (Worksheet).RangeShiftedColumns += Worksheet_RangeShiftedColumns; xlRangeParameters.IgnoreEvents = true; } - m_defaultStyle = new XLStyle(this, xlRangeParameters.DefaultStyle); + DefaultStyle = new XLStyle(this, xlRangeParameters.DefaultStyle); } #endregion public XLRangeParameters RangeParameters { get; private set; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index 946495c..e78ab6f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -7,24 +7,36 @@ internal abstract class XLRangeBase : IXLRangeBase, IXLStylized { #region Fields - protected IXLStyle m_defaultStyle; + + protected IXLStyle DefaultStyle; + #endregion + #region Constructor + protected XLRangeBase(XLRangeAddress rangeAddress) { RangeAddress = rangeAddress; } + #endregion + #region Public properties + public XLRangeAddress RangeAddress { get; protected set; } - IXLRangeAddress IXLRangeBase.RangeAddress - { - get { return RangeAddress; } - } + public XLWorksheet Worksheet { get { return RangeAddress.Worksheet; } } + + #region IXLRangeBase Members + + IXLRangeAddress IXLRangeBase.RangeAddress + { + get { return RangeAddress; } + } + IXLWorksheet IXLRangeBase.Worksheet { get { return RangeAddress.Worksheet; } @@ -34,10 +46,12 @@ { set { Cells().ForEach(c => c.FormulaA1 = value); } } + public String FormulaR1C1 { set { Cells().ForEach(c => c.FormulaR1C1 = value); } } + public Boolean ShareString { set { Cells().ForEach(c => c.ShareString = value); } @@ -62,36 +76,29 @@ { var thisRange = AsRange(); if (Worksheet.DataValidations.ContainsSingle(thisRange)) - { return Worksheet.DataValidations.Where(dv => dv.Ranges.Contains(thisRange)).Single(); - } var dvEmpty = new List(); - foreach (var dv in Worksheet.DataValidations) + foreach (IXLDataValidation dv in Worksheet.DataValidations) { - foreach (var dvRange in dv.Ranges) + foreach (IXLRange dvRange in dv.Ranges) { if (dvRange.Intersects(this)) { dv.Ranges.Remove(dvRange); - foreach (var c in dvRange.Cells()) + foreach (IXLCell c in dvRange.Cells()) { if (!Contains(c.Address.ToString())) - { dv.Ranges.Add(c.AsRange()); - } } if (dv.Ranges.Count() == 0) - { dvEmpty.Add(dv); - } } } } dvEmpty.ForEach(dv => Worksheet.DataValidations.Delete(dv)); - var newRanges = new XLRanges(); - newRanges.Add(AsRange()); + var newRanges = new XLRanges {AsRange()}; var dataValidation = new XLDataValidation(newRanges, Worksheet); Worksheet.DataValidations.Add(dataValidation); @@ -109,83 +116,298 @@ set { Cells().ForEach(c => c.DataType = value); } } + #endregion + + #region IXLStylized Members + public IXLRanges RangesUsed { get { - var retVal = new XLRanges(); - retVal.Add(AsRange()); + var retVal = new XLRanges {AsRange()}; return retVal; } } + #endregion + + #endregion + + #region IXLRangeBase Members + IXLCell IXLRangeBase.FirstCell() { return FirstCell(); } - public XLCell FirstCell() - { - return Cell(1, 1); - } + IXLCell IXLRangeBase.LastCell() { return LastCell(); } - public XLCell LastCell() - { - return Cell(RowCount(), ColumnCount()); - } IXLCell IXLRangeBase.FirstCellUsed() { return FirstCellUsed(false); } - IXLCell IXLRangeBase.FirstCellUsed(bool includeStyles) - { - return FirstCellUsed(includeStyles); - } - public XLCell FirstCellUsed() - { - return FirstCellUsed(false); - } - public XLCell FirstCellUsed(Boolean includeStyles) - { - var cellsUsed = CellsUsed(includeStyles); - if (!cellsUsed.Any()) - { - return null; - } - var firstRow = cellsUsed.Min(c => c.Address.RowNumber); - var firstColumn = cellsUsed.Min(c => c.Address.ColumnNumber); - return Worksheet.Cell(firstRow, firstColumn); + IXLCell IXLRangeBase.FirstCellUsed(bool includeFormats) + { + return FirstCellUsed(includeFormats); } IXLCell IXLRangeBase.LastCellUsed() { return LastCellUsed(false); } - IXLCell IXLRangeBase.LastCellUsed(bool includeStyles) + + IXLCell IXLRangeBase.LastCellUsed(bool includeFormats) { - return LastCellUsed(includeStyles); + return LastCellUsed(includeFormats); } + + public IXLCells Cells() + { + var cells = new XLCells( false, false) {RangeAddress}; + return cells; + } + + public IXLCells Cells(String cells) + { + return Ranges(cells).Cells(); + } + + public IXLCells CellsUsed() + { + var cells = new XLCells( true, false) {RangeAddress}; + return cells; + } + + IXLCells IXLRangeBase.CellsUsed(Boolean includeFormats) + { + return CellsUsed(includeFormats); + } + + public IXLRange Merge() + { + string tAddress = RangeAddress.ToString(); + Boolean foundOne = + Worksheet.Internals.MergedRanges.Select(m => m.RangeAddress.ToString()).Any( + mAddress => mAddress == tAddress); + + if (!foundOne) + Worksheet.Internals.MergedRanges.Add(AsRange()); + return AsRange(); + } + + public IXLRange Unmerge() + { + string tAddress = RangeAddress.ToString(); + if ( + Worksheet.Internals.MergedRanges.Select(m => m.RangeAddress.ToString()).Any( + mAddress => mAddress == tAddress)) + Worksheet.Internals.MergedRanges.Remove(AsRange()); + + return AsRange(); + } + + public void Clear() + { + // Remove cells inside range + Worksheet.Internals.CellsCollection.RemoveAll(c => + c.Address.ColumnNumber >= + RangeAddress.FirstAddress.ColumnNumber + && + c.Address.ColumnNumber <= + RangeAddress.LastAddress.ColumnNumber + && + c.Address.RowNumber >= RangeAddress.FirstAddress.RowNumber + && c.Address.RowNumber <= RangeAddress.LastAddress.RowNumber + ); + + ClearMerged(); + + var hyperlinksToRemove = Worksheet.Hyperlinks.Where(hl => Contains(hl.Cell.AsRange())).ToList(); + hyperlinksToRemove.ForEach(hl => Worksheet.Hyperlinks.Delete(hl)); + } + + public void ClearStyles() + { + foreach (XLCell cell in CellsUsed(true)) + { + var newStyle = new XLStyle(cell, Worksheet.Style) {NumberFormat = cell.Style.NumberFormat}; + cell.Style = newStyle; + } + } + + public bool Contains(String rangeAddress) + { + string addressToUse = rangeAddress.Contains("!") + ? rangeAddress.Substring(rangeAddress.IndexOf("!") + 1) + : rangeAddress; + + XLAddress firstAddress; + XLAddress lastAddress; + if (addressToUse.Contains(':')) + { + var arrRange = addressToUse.Split(':'); + firstAddress = XLAddress.Create(Worksheet, arrRange[0]); + lastAddress = XLAddress.Create(Worksheet, arrRange[1]); + } + else + { + firstAddress = XLAddress.Create(Worksheet, addressToUse); + lastAddress = XLAddress.Create(Worksheet, addressToUse); + } + return Contains(firstAddress, lastAddress); + } + + public bool Contains(IXLRangeBase range) + { + return Contains((XLAddress)range.RangeAddress.FirstAddress, (XLAddress)range.RangeAddress.LastAddress); + } + + public bool Intersects(string rangeAddress) + { + return Intersects(Range(rangeAddress)); + } + + public bool Intersects(IXLRangeBase range) + { + if (range.RangeAddress.IsInvalid || RangeAddress.IsInvalid) + return false; + var ma = range.RangeAddress; + var ra = RangeAddress; + + return !( // See if the two ranges intersect... + ma.FirstAddress.ColumnNumber > ra.LastAddress.ColumnNumber + || ma.LastAddress.ColumnNumber < ra.FirstAddress.ColumnNumber + || ma.FirstAddress.RowNumber > ra.LastAddress.RowNumber + || ma.LastAddress.RowNumber < ra.FirstAddress.RowNumber + ); + } + + public virtual IXLStyle Style + { + get { return DefaultStyle; } + set { Cells().ForEach(c => c.Style = value); } + } + + public virtual IXLRange AsRange() + { + 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) + { + return AddToNamed(rangeName, XLScope.Workbook); + } + + public IXLRange AddToNamed(String rangeName, XLScope scope) + { + return AddToNamed(rangeName, scope, null); + } + + public IXLRange AddToNamed(String rangeName, XLScope scope, String comment) + { + var namedRanges = scope == XLScope.Workbook + ? Worksheet.Internals.Workbook.NamedRanges + : Worksheet.NamedRanges; + + if (namedRanges.Any(nr => nr.Name.ToLower() == rangeName.ToLower())) + { + var namedRange = namedRanges.Where(nr => nr.Name.ToLower() == rangeName.ToLower()).Single(); + namedRange.Add(Worksheet.Internals.Workbook, ToStringFixed()); + } + else + namedRanges.Add(rangeName, ToStringFixed(), comment); + return AsRange(); + } + + public IXLRangeBase SetValue(T value) + { + Cells().ForEach(c => c.SetValue(value)); + return this; + } + + #endregion + + #region IXLStylized Members + + public virtual IEnumerable Styles + { + get + { + UpdatingStyle = true; + foreach (IXLCell cell in Cells()) + yield return cell.Style; + UpdatingStyle = false; + } + } + + public virtual Boolean UpdatingStyle { get; set; } + + public virtual IXLStyle InnerStyle + { + get { return DefaultStyle; } + set { DefaultStyle = new XLStyle(this, value); } + } + + #endregion + + public XLCell FirstCell() + { + return Cell(1, 1); + } + + public XLCell LastCell() + { + return Cell(RowCount(), ColumnCount()); + } + + public XLCell FirstCellUsed() + { + return FirstCellUsed(false); + } + + public XLCell FirstCellUsed(Boolean includeFormats) + { + var cellsUsed = CellsUsed(includeFormats); + + if (!cellsUsed.Any()) + return null; + int firstRow = cellsUsed.Min(c => c.Address.RowNumber); + int firstColumn = cellsUsed.Min(c => c.Address.ColumnNumber); + return Worksheet.Cell(firstRow, firstColumn); + } + public XLCell LastCellUsed() { return LastCellUsed(false); } - public XLCell LastCellUsed(Boolean includeStyles) + + public XLCell LastCellUsed(Boolean includeFormats) { - var cellsUsed = CellsUsed(includeStyles); + var cellsUsed = CellsUsed(includeFormats); if (!cellsUsed.Any()) - { return null; - } - else - { - var lastRow = cellsUsed.Max(c => c.Address.RowNumber); - var lastColumn = cellsUsed.Max(c => c.Address.ColumnNumber); - return Worksheet.Cell(lastRow, lastColumn); - } + + int lastRow = cellsUsed.Max(c => c.Address.RowNumber); + int lastColumn = cellsUsed.Max(c => c.Address.ColumnNumber); + return Worksheet.Cell(lastRow, lastColumn); } public XLCell Cell(Int32 row, Int32 column) @@ -202,6 +424,7 @@ { return Cell(new XLAddress(Worksheet, row, column, false, false)); } + public XLCell Cell(IXLAddress cellAddressInRange) { return Cell(cellAddressInRange.RowNumber, cellAddressInRange.ColumnNumber); @@ -211,22 +434,18 @@ { var absoluteAddress = cellAddressInRange + RangeAddress.FirstAddress - 1; if (Worksheet.Internals.CellsCollection.ContainsKey(absoluteAddress)) - { return Worksheet.Internals.CellsCollection[absoluteAddress]; - } - IXLStyle style = Style; + var style = Style; if (Style != null && Style.Equals(Worksheet.Style)) { if (Worksheet.Internals.RowsCollection.ContainsKey(absoluteAddress.RowNumber) && !Worksheet.Internals.RowsCollection[absoluteAddress.RowNumber].Style.Equals(Worksheet.Style)) - { style = Worksheet.Internals.RowsCollection[absoluteAddress.RowNumber].Style; - } else if (Worksheet.Internals.ColumnsCollection.ContainsKey(absoluteAddress.ColumnNumber) - && !Worksheet.Internals.ColumnsCollection[absoluteAddress.ColumnNumber].Style.Equals(Worksheet.Style)) - { + && + !Worksheet.Internals.ColumnsCollection[absoluteAddress.ColumnNumber].Style.Equals( + Worksheet.Style)) style = Worksheet.Internals.ColumnsCollection[absoluteAddress.ColumnNumber].Style; - } } var newCell = new XLCell(Worksheet, absoluteAddress, style); Worksheet.Internals.CellsCollection.Add(absoluteAddress, newCell); @@ -237,18 +456,22 @@ { return RangeAddress.LastAddress.RowNumber - RangeAddress.FirstAddress.RowNumber + 1; } + public Int32 RowNumber() { return RangeAddress.FirstAddress.RowNumber; } + public Int32 ColumnCount() { return RangeAddress.LastAddress.ColumnNumber - RangeAddress.FirstAddress.ColumnNumber + 1; } + public Int32 ColumnNumber() { return RangeAddress.FirstAddress.ColumnNumber; } + public String ColumnLetter() { return RangeAddress.FirstAddress.ColumnLetter; @@ -264,47 +487,53 @@ { return Range(firstCell.Address, lastCell.Address); } + public XLRange Range(String firstCellAddress, String lastCellAddress) { - var rangeAddress = new XLRangeAddress(XLAddress.Create(Worksheet, firstCellAddress), XLAddress.Create(Worksheet, lastCellAddress)); + var rangeAddress = new XLRangeAddress(XLAddress.Create(Worksheet, firstCellAddress), + XLAddress.Create(Worksheet, lastCellAddress)); return Range(rangeAddress); } + public XLRange Range(Int32 firstCellRow, Int32 firstCellColumn, Int32 lastCellRow, Int32 lastCellColumn) { var rangeAddress = new XLRangeAddress(new XLAddress(Worksheet, firstCellRow, firstCellColumn, false, false), new XLAddress(Worksheet, lastCellRow, lastCellColumn, false, false)); return Range(rangeAddress); } + public XLRange Range(IXLAddress firstCellAddress, IXLAddress lastCellAddress) { var rangeAddress = new XLRangeAddress(firstCellAddress as XLAddress, lastCellAddress as XLAddress); return Range(rangeAddress); } + public XLRange Range(IXLRangeAddress rangeAddress) { - var newFirstCellAddress = (XLAddress) rangeAddress.FirstAddress + RangeAddress.FirstAddress - 1; + var newFirstCellAddress = (XLAddress)rangeAddress.FirstAddress + RangeAddress.FirstAddress - 1; newFirstCellAddress.FixedRow = rangeAddress.FirstAddress.FixedRow; newFirstCellAddress.FixedColumn = rangeAddress.FirstAddress.FixedColumn; - var newLastCellAddress = (XLAddress) rangeAddress.LastAddress + RangeAddress.FirstAddress - 1; + var newLastCellAddress = (XLAddress)rangeAddress.LastAddress + RangeAddress.FirstAddress - 1; newLastCellAddress.FixedRow = rangeAddress.LastAddress.FixedRow; newLastCellAddress.FixedColumn = rangeAddress.LastAddress.FixedColumn; var newRangeAddress = new XLRangeAddress(newFirstCellAddress, newLastCellAddress); var xlRangeParameters = new XLRangeParameters(newRangeAddress, Style); if ( - newFirstCellAddress.RowNumber < RangeAddress.FirstAddress.RowNumber - || newFirstCellAddress.RowNumber > RangeAddress.LastAddress.RowNumber - || newLastCellAddress.RowNumber > RangeAddress.LastAddress.RowNumber - || newFirstCellAddress.ColumnNumber < RangeAddress.FirstAddress.ColumnNumber - || newFirstCellAddress.ColumnNumber > RangeAddress.LastAddress.ColumnNumber - || newLastCellAddress.ColumnNumber > RangeAddress.LastAddress.ColumnNumber - ) + newFirstCellAddress.RowNumber < RangeAddress.FirstAddress.RowNumber + || newFirstCellAddress.RowNumber > RangeAddress.LastAddress.RowNumber + || newLastCellAddress.RowNumber > RangeAddress.LastAddress.RowNumber + || newFirstCellAddress.ColumnNumber < RangeAddress.FirstAddress.ColumnNumber + || newFirstCellAddress.ColumnNumber > RangeAddress.LastAddress.ColumnNumber + || newLastCellAddress.ColumnNumber > RangeAddress.LastAddress.ColumnNumber + ) { - throw new ArgumentOutOfRangeException(String.Format("The cells {0} and {1} are outside the range '{2}'.", - newFirstCellAddress, - newLastCellAddress, - ToString())); + throw new ArgumentOutOfRangeException(String.Format( + "The cells {0} and {1} are outside the range '{2}'.", + newFirstCellAddress, + newLastCellAddress, + ToString())); } return new XLRange(xlRangeParameters); @@ -314,19 +543,16 @@ { var retVal = new XLRanges(); var rangePairs = ranges.Split(','); - foreach (var pair in rangePairs) - { + foreach (string pair in rangePairs) retVal.Add(Range(pair.Trim())); - } return retVal; } + public IXLRanges Ranges(params String[] ranges) { var retVal = new XLRanges(); - foreach (var pair in ranges) - { + foreach (string pair in ranges) retVal.Add(Range(pair)); - } return retVal; } @@ -334,86 +560,29 @@ { Int32 test; if (Int32.TryParse(address, out test)) - { return "A" + address; - } return address; } + protected String FixRowAddress(String address) { Int32 test; if (Int32.TryParse(address, out test)) - { return ExcelHelper.GetColumnLetterFromNumber(test) + "1"; - } return address; } - public IXLCells Cells() + public XLCells CellsUsed(bool includeFormats) { - var cells = new XLCells(false, false, false); - cells.Add(RangeAddress); + var cells = new XLCells( true, includeFormats) {RangeAddress}; return cells; } - public IXLCells Cells(String cells) - { - return Ranges(cells).Cells(); - } - public IXLCells CellsUsed() - { - var cells = new XLCells(false, true, false); - cells.Add(RangeAddress); - return cells; - } - IXLCells IXLRangeBase.CellsUsed(Boolean includeStyles) - { - return CellsUsed(includeStyles); - } - public XLCells CellsUsed(bool includeStyles) - { - var cells = new XLCells(false, true, includeStyles); - cells.Add(RangeAddress); - return cells; - } - - public IXLRange Merge() - { - var tAddress = this.RangeAddress.ToString(); - Boolean foundOne = false; - foreach (var m in (Worksheet as XLWorksheet).Internals.MergedRanges) - { - var mAddress = m.RangeAddress.ToString(); - if (mAddress == tAddress) - { - foundOne = true; - break; - } - } - - if (!foundOne) - (Worksheet as XLWorksheet).Internals.MergedRanges.Add(this.AsRange()); - return AsRange(); - } - public IXLRange Unmerge() - { - var tAddress = this.RangeAddress.ToString(); - foreach (var m in (Worksheet as XLWorksheet).Internals.MergedRanges) - { - var mAddress = m.RangeAddress.ToString(); - if (mAddress == tAddress) - { - (Worksheet as XLWorksheet).Internals.MergedRanges.Remove(this.AsRange()); - break; - } - } - - return AsRange(); - } public IXLRangeColumns InsertColumnsAfter(Int32 numberOfColumns) { return InsertColumnsAfter(numberOfColumns, true); } + public IXLRangeColumns InsertColumnsAfter(Int32 numberOfColumns, Boolean expandRange) { var retVal = InsertColumnsAfter(false, numberOfColumns); @@ -421,47 +590,44 @@ if (expandRange) { RangeAddress = new XLRangeAddress( - new XLAddress(Worksheet, - RangeAddress.FirstAddress.RowNumber, - RangeAddress.FirstAddress.ColumnNumber, - RangeAddress.FirstAddress.FixedRow, - RangeAddress.FirstAddress.FixedColumn), - new XLAddress(Worksheet, - RangeAddress.LastAddress.RowNumber, - RangeAddress.LastAddress.ColumnNumber + numberOfColumns, - RangeAddress.LastAddress.FixedRow, - RangeAddress.LastAddress.FixedColumn)); + new XLAddress(Worksheet, + RangeAddress.FirstAddress.RowNumber, + RangeAddress.FirstAddress.ColumnNumber, + RangeAddress.FirstAddress.FixedRow, + RangeAddress.FirstAddress.FixedColumn), + new XLAddress(Worksheet, + RangeAddress.LastAddress.RowNumber, + RangeAddress.LastAddress.ColumnNumber + numberOfColumns, + RangeAddress.LastAddress.FixedRow, + RangeAddress.LastAddress.FixedColumn)); } return retVal; } + public IXLRangeColumns InsertColumnsAfter(Boolean onlyUsedCells, Int32 numberOfColumns) { - var columnCount = ColumnCount(); - var firstColumn = RangeAddress.FirstAddress.ColumnNumber + columnCount; + int columnCount = ColumnCount(); + int firstColumn = RangeAddress.FirstAddress.ColumnNumber + columnCount; if (firstColumn > ExcelHelper.MaxColumnNumber) - { firstColumn = ExcelHelper.MaxColumnNumber; - } - var lastColumn = firstColumn + ColumnCount() - 1; + int lastColumn = firstColumn + ColumnCount() - 1; if (lastColumn > ExcelHelper.MaxColumnNumber) - { lastColumn = ExcelHelper.MaxColumnNumber; - } - var firstRow = RangeAddress.FirstAddress.RowNumber; - var lastRow = firstRow + RowCount() - 1; + int firstRow = RangeAddress.FirstAddress.RowNumber; + int lastRow = firstRow + RowCount() - 1; if (lastRow > ExcelHelper.MaxRowNumber) - { lastRow = ExcelHelper.MaxRowNumber; - } var newRange = Worksheet.Range(firstRow, firstColumn, lastRow, lastColumn); return newRange.InsertColumnsBefore(onlyUsedCells, numberOfColumns); } + public IXLRangeColumns InsertColumnsBefore(Int32 numberOfColumns) { return InsertColumnsBefore(numberOfColumns, false); } + public IXLRangeColumns InsertColumnsBefore(Int32 numberOfColumns, Boolean expandRange) { var retVal = InsertColumnsBefore(false, numberOfColumns); @@ -469,117 +635,104 @@ if (expandRange) { RangeAddress = new XLRangeAddress( - new XLAddress(Worksheet, - RangeAddress.FirstAddress.RowNumber, - RangeAddress.FirstAddress.ColumnNumber - numberOfColumns, - RangeAddress.FirstAddress.FixedRow, - RangeAddress.FirstAddress.FixedColumn), - new XLAddress(Worksheet, - RangeAddress.LastAddress.RowNumber, - RangeAddress.LastAddress.ColumnNumber, - RangeAddress.LastAddress.FixedRow, - RangeAddress.LastAddress.FixedColumn)); + new XLAddress(Worksheet, + RangeAddress.FirstAddress.RowNumber, + RangeAddress.FirstAddress.ColumnNumber - numberOfColumns, + RangeAddress.FirstAddress.FixedRow, + RangeAddress.FirstAddress.FixedColumn), + new XLAddress(Worksheet, + RangeAddress.LastAddress.RowNumber, + RangeAddress.LastAddress.ColumnNumber, + RangeAddress.LastAddress.FixedRow, + RangeAddress.LastAddress.FixedColumn)); } return retVal; } + public IXLRangeColumns InsertColumnsBefore(Boolean onlyUsedCells, Int32 numberOfColumns) { - foreach (var ws in (Worksheet).Internals.Workbook.WorksheetsInternal) + foreach (XLWorksheet ws in Worksheet.Internals.Workbook.WorksheetsInternal) { - foreach (var cell in ws.Internals.CellsCollection.Values.Where(c => !StringExtensions.IsNullOrWhiteSpace(c.FormulaA1))) - { - cell.ShiftFormulaColumns((XLRange) AsRange(), numberOfColumns); - } + foreach ( + XLCell cell in + ws.Internals.CellsCollection.Values.Where(c => !StringExtensions.IsNullOrWhiteSpace(c.FormulaA1)) + ) + cell.ShiftFormulaColumns((XLRange)AsRange(), numberOfColumns); } var cellsToInsert = new Dictionary(); var cellsToDelete = new List(); var cellsToBlank = new List(); - var firstColumn = RangeAddress.FirstAddress.ColumnNumber; - var firstRow = RangeAddress.FirstAddress.RowNumber; - var lastRow = RangeAddress.FirstAddress.RowNumber + RowCount() - 1; + int firstColumn = RangeAddress.FirstAddress.ColumnNumber; + int firstRow = RangeAddress.FirstAddress.RowNumber; + int lastRow = RangeAddress.FirstAddress.RowNumber + RowCount() - 1; if (!onlyUsedCells) { - var lastColumn = Worksheet.LastColumnUsed().ColumnNumber(); + int lastColumn = Worksheet.LastColumnUsed().ColumnNumber(); - for (var co = lastColumn; co >= firstColumn; co--) + for (int co = lastColumn; co >= firstColumn; co--) { - for (var ro = lastRow; ro >= firstRow; ro--) + for (int ro = lastRow; ro >= firstRow; ro--) { var oldKey = new XLAddress(Worksheet, ro, co, false, false); - var newColumn = co + numberOfColumns; + int newColumn = co + numberOfColumns; var newKey = new XLAddress(Worksheet, ro, newColumn, false, false); - IXLCell oldCell; - if ((Worksheet).Internals.CellsCollection.ContainsKey(oldKey)) - { - oldCell = (Worksheet).Internals.CellsCollection[oldKey]; - } - else - { - oldCell = Worksheet.Cell(oldKey); - } + IXLCell oldCell = Worksheet.Internals.CellsCollection.ContainsKey(oldKey) + ? Worksheet.Internals.CellsCollection[oldKey] + : Worksheet.Cell(oldKey); var newCell = new XLCell(Worksheet, newKey, oldCell.Style); - newCell.CopyValues((XLCell) oldCell); + newCell.CopyValues((XLCell)oldCell); cellsToInsert.Add(newKey, newCell); cellsToDelete.Add(oldKey); if (oldKey.ColumnNumber < firstColumn + numberOfColumns) - { cellsToBlank.Add(oldKey); - } } } } else { - foreach (var c in (Worksheet).Internals.CellsCollection - .Where(c => - c.Key.ColumnNumber >= firstColumn - && c.Key.RowNumber >= firstRow - && c.Key.RowNumber <= lastRow - )) + foreach (KeyValuePair c in Worksheet.Internals.CellsCollection + .Where(c => + c.Key.ColumnNumber >= firstColumn + && c.Key.RowNumber >= firstRow + && c.Key.RowNumber <= lastRow + )) { - var newColumn = c.Key.ColumnNumber + numberOfColumns; + int newColumn = c.Key.ColumnNumber + numberOfColumns; var newKey = new XLAddress(Worksheet, c.Key.RowNumber, newColumn, false, false); var newCell = new XLCell(Worksheet, newKey, c.Value.Style); newCell.CopyValues(c.Value); cellsToInsert.Add(newKey, newCell); cellsToDelete.Add(c.Key); if (c.Key.ColumnNumber < firstColumn + numberOfColumns) - { cellsToBlank.Add(c.Key); - } } } - cellsToDelete.ForEach(c => (Worksheet).Internals.CellsCollection.Remove(c)); - cellsToInsert.ForEach(c => (Worksheet).Internals.CellsCollection.Add(c.Key, c.Value)); - foreach (var c in cellsToBlank) + cellsToDelete.ForEach(c => Worksheet.Internals.CellsCollection.Remove(c)); + cellsToInsert.ForEach(c => Worksheet.Internals.CellsCollection.Add(c.Key, c.Value)); + foreach (IXLAddress c in cellsToBlank) { - IXLStyle styleToUse; - if ((Worksheet).Internals.RowsCollection.ContainsKey(c.RowNumber)) - { - styleToUse = (Worksheet).Internals.RowsCollection[c.RowNumber].Style; - } - else - { - styleToUse = Worksheet.Style; - } + var styleToUse = Worksheet.Internals.RowsCollection.ContainsKey(c.RowNumber) + ? Worksheet.Internals.RowsCollection[c.RowNumber].Style + : Worksheet.Style; Worksheet.Cell(c.RowNumber, c.ColumnNumber).Style = styleToUse; } - (Worksheet).NotifyRangeShiftedColumns((XLRange) AsRange(), numberOfColumns); + Worksheet.NotifyRangeShiftedColumns((XLRange)AsRange(), numberOfColumns); return Worksheet.Range( - RangeAddress.FirstAddress.RowNumber, - RangeAddress.FirstAddress.ColumnNumber - numberOfColumns, - RangeAddress.LastAddress.RowNumber, - RangeAddress.LastAddress.ColumnNumber - numberOfColumns - ).Columns(); + RangeAddress.FirstAddress.RowNumber, + RangeAddress.FirstAddress.ColumnNumber - numberOfColumns, + RangeAddress.LastAddress.RowNumber, + RangeAddress.LastAddress.ColumnNumber - numberOfColumns + ).Columns(); } public IXLRangeRows InsertRowsBelow(Int32 numberOfRows) { return InsertRowsBelow(numberOfRows, true); } + public IXLRangeRows InsertRowsBelow(Int32 numberOfRows, Boolean expandRange) { var retVal = InsertRowsBelow(false, numberOfRows); @@ -587,39 +740,34 @@ if (expandRange) { RangeAddress = new XLRangeAddress( - new XLAddress(Worksheet, - RangeAddress.FirstAddress.RowNumber, - RangeAddress.FirstAddress.ColumnNumber, - RangeAddress.FirstAddress.FixedRow, - RangeAddress.FirstAddress.FixedColumn), - new XLAddress(Worksheet, - RangeAddress.LastAddress.RowNumber + numberOfRows, - RangeAddress.LastAddress.ColumnNumber, - RangeAddress.LastAddress.FixedRow, - RangeAddress.LastAddress.FixedColumn)); + new XLAddress(Worksheet, + RangeAddress.FirstAddress.RowNumber, + RangeAddress.FirstAddress.ColumnNumber, + RangeAddress.FirstAddress.FixedRow, + RangeAddress.FirstAddress.FixedColumn), + new XLAddress(Worksheet, + RangeAddress.LastAddress.RowNumber + numberOfRows, + RangeAddress.LastAddress.ColumnNumber, + RangeAddress.LastAddress.FixedRow, + RangeAddress.LastAddress.FixedColumn)); } return retVal; } + public IXLRangeRows InsertRowsBelow(Boolean onlyUsedCells, Int32 numberOfRows) { - var rowCount = RowCount(); - var firstRow = RangeAddress.FirstAddress.RowNumber + rowCount; + int rowCount = RowCount(); + int firstRow = RangeAddress.FirstAddress.RowNumber + rowCount; if (firstRow > ExcelHelper.MaxRowNumber) - { firstRow = ExcelHelper.MaxRowNumber; - } - var lastRow = firstRow + RowCount() - 1; + int lastRow = firstRow + RowCount() - 1; if (lastRow > ExcelHelper.MaxRowNumber) - { lastRow = ExcelHelper.MaxRowNumber; - } - var firstColumn = RangeAddress.FirstAddress.ColumnNumber; - var lastColumn = firstColumn + ColumnCount() - 1; + int firstColumn = RangeAddress.FirstAddress.ColumnNumber; + int lastColumn = firstColumn + ColumnCount() - 1; if (lastColumn > ExcelHelper.MaxColumnNumber) - { lastColumn = ExcelHelper.MaxColumnNumber; - } var newRange = Worksheet.Range(firstRow, firstColumn, lastRow, lastColumn); return newRange.InsertRowsAbove(onlyUsedCells, numberOfRows); @@ -629,6 +777,7 @@ { return InsertRowsAbove(numberOfRows, false); } + public IXLRangeRows InsertRowsAbove(Int32 numberOfRows, Boolean expandRange) { var retVal = InsertRowsAbove(false, numberOfRows); @@ -636,244 +785,155 @@ if (expandRange) { RangeAddress = new XLRangeAddress( - new XLAddress(Worksheet, - RangeAddress.FirstAddress.RowNumber - numberOfRows, - RangeAddress.FirstAddress.ColumnNumber, - RangeAddress.FirstAddress.FixedRow, - RangeAddress.FirstAddress.FixedColumn), - new XLAddress(Worksheet, - RangeAddress.LastAddress.RowNumber, - RangeAddress.LastAddress.ColumnNumber, - RangeAddress.LastAddress.FixedRow, - RangeAddress.LastAddress.FixedColumn)); + new XLAddress(Worksheet, + RangeAddress.FirstAddress.RowNumber - numberOfRows, + RangeAddress.FirstAddress.ColumnNumber, + RangeAddress.FirstAddress.FixedRow, + RangeAddress.FirstAddress.FixedColumn), + new XLAddress(Worksheet, + RangeAddress.LastAddress.RowNumber, + RangeAddress.LastAddress.ColumnNumber, + RangeAddress.LastAddress.FixedRow, + RangeAddress.LastAddress.FixedColumn)); } return retVal; } + public IXLRangeRows InsertRowsAbove(Boolean onlyUsedCells, Int32 numberOfRows) { - foreach (var ws in (Worksheet).Internals.Workbook.WorksheetsInternal) + foreach (XLWorksheet ws in Worksheet.Internals.Workbook.WorksheetsInternal) { - foreach (var cell in ws.Internals.CellsCollection.Values.Where(c => !StringExtensions.IsNullOrWhiteSpace(c.FormulaA1))) - { - cell.ShiftFormulaRows((XLRange) AsRange(), numberOfRows); - } + foreach ( + XLCell cell in + ws.Internals.CellsCollection.Values.Where(c => !StringExtensions.IsNullOrWhiteSpace(c.FormulaA1)) + ) + cell.ShiftFormulaRows((XLRange)AsRange(), numberOfRows); } var cellsToInsert = new Dictionary(); var cellsToDelete = new List(); var cellsToBlank = new List(); - var firstRow = RangeAddress.FirstAddress.RowNumber; - var firstColumn = RangeAddress.FirstAddress.ColumnNumber; - var lastColumn = RangeAddress.FirstAddress.ColumnNumber + ColumnCount() - 1; + int firstRow = RangeAddress.FirstAddress.RowNumber; + int firstColumn = RangeAddress.FirstAddress.ColumnNumber; + int lastColumn = RangeAddress.FirstAddress.ColumnNumber + ColumnCount() - 1; if (!onlyUsedCells) { - var lastRow = Worksheet.LastRowUsed().RowNumber(); + int lastRow = Worksheet.LastRowUsed().RowNumber(); - for (var ro = lastRow; ro >= firstRow; ro--) + for (int ro = lastRow; ro >= firstRow; ro--) { - for (var co = lastColumn; co >= firstColumn; co--) + for (int co = lastColumn; co >= firstColumn; co--) { var oldKey = new XLAddress(Worksheet, ro, co, false, false); - var newRow = ro + numberOfRows; + int newRow = ro + numberOfRows; var newKey = new XLAddress(Worksheet, newRow, co, false, false); XLCell oldCell; - if ((Worksheet).Internals.CellsCollection.ContainsKey(oldKey)) - { - oldCell = (Worksheet).Internals.CellsCollection[oldKey]; - } + if (Worksheet.Internals.CellsCollection.ContainsKey(oldKey)) + oldCell = Worksheet.Internals.CellsCollection[oldKey]; else - { oldCell = Worksheet.Cell(oldKey); - } var newCell = new XLCell(Worksheet, newKey, oldCell.Style); newCell.CopyFrom(oldCell); cellsToInsert.Add(newKey, newCell); cellsToDelete.Add(oldKey); if (oldKey.RowNumber < firstRow + numberOfRows) - { cellsToBlank.Add(oldKey); - } } } } else { - foreach (var c in (Worksheet).Internals.CellsCollection - .Where(c => - c.Key.RowNumber >= firstRow - && c.Key.ColumnNumber >= firstColumn - && c.Key.ColumnNumber <= lastColumn - )) + foreach (KeyValuePair c in Worksheet.Internals.CellsCollection + .Where(c => + c.Key.RowNumber >= firstRow + && c.Key.ColumnNumber >= firstColumn + && c.Key.ColumnNumber <= lastColumn + )) { - var newRow = c.Key.RowNumber + numberOfRows; + int newRow = c.Key.RowNumber + numberOfRows; var newKey = new XLAddress(Worksheet, newRow, c.Key.ColumnNumber, false, false); var newCell = new XLCell(Worksheet, newKey, c.Value.Style); newCell.CopyFrom(c.Value); cellsToInsert.Add(newKey, newCell); cellsToDelete.Add(c.Key); if (c.Key.RowNumber < firstRow + numberOfRows) - { cellsToBlank.Add(c.Key); - } } } - cellsToDelete.ForEach(c => (Worksheet).Internals.CellsCollection.Remove(c)); - cellsToInsert.ForEach(c => (Worksheet).Internals.CellsCollection.Add(c.Key, c.Value)); - foreach (var c in cellsToBlank) + cellsToDelete.ForEach(c => Worksheet.Internals.CellsCollection.Remove(c)); + cellsToInsert.ForEach(c => Worksheet.Internals.CellsCollection.Add(c.Key, c.Value)); + foreach (IXLAddress c in cellsToBlank) { IXLStyle styleToUse; - if ((Worksheet).Internals.ColumnsCollection.ContainsKey(c.ColumnNumber)) - { - styleToUse = (Worksheet).Internals.ColumnsCollection[c.ColumnNumber].Style; - } + if (Worksheet.Internals.ColumnsCollection.ContainsKey(c.ColumnNumber)) + styleToUse = Worksheet.Internals.ColumnsCollection[c.ColumnNumber].Style; else - { styleToUse = Worksheet.Style; - } Worksheet.Cell(c.RowNumber, c.ColumnNumber).Style = styleToUse; } - (Worksheet).NotifyRangeShiftedRows((XLRange) AsRange(), numberOfRows); + Worksheet.NotifyRangeShiftedRows((XLRange)AsRange(), numberOfRows); return Worksheet.Range( - RangeAddress.FirstAddress.RowNumber - numberOfRows, - RangeAddress.FirstAddress.ColumnNumber, - RangeAddress.LastAddress.RowNumber - numberOfRows, - RangeAddress.LastAddress.ColumnNumber - ).Rows(); - } - - public void Clear() - { - // Remove cells inside range - (Worksheet).Internals.CellsCollection.RemoveAll(c => - c.Address.ColumnNumber >= RangeAddress.FirstAddress.ColumnNumber - && c.Address.ColumnNumber <= RangeAddress.LastAddress.ColumnNumber - && c.Address.RowNumber >= RangeAddress.FirstAddress.RowNumber - && c.Address.RowNumber <= RangeAddress.LastAddress.RowNumber - ); - - ClearMerged(); - - List hyperlinksToRemove = new List(); - foreach (var hl in Worksheet.Hyperlinks) - { - if (Contains(hl.Cell.AsRange())) - { - hyperlinksToRemove.Add(hl); - } - } - hyperlinksToRemove.ForEach(hl => Worksheet.Hyperlinks.Delete(hl)); - } - - public void ClearStyles() - { - foreach (var cell in CellsUsed(true)) - { - var newStyle = new XLStyle((XLCell) cell, Worksheet.Style); - newStyle.NumberFormat = cell.Style.NumberFormat; - cell.Style = newStyle; - } + RangeAddress.FirstAddress.RowNumber - numberOfRows, + RangeAddress.FirstAddress.ColumnNumber, + RangeAddress.LastAddress.RowNumber - numberOfRows, + RangeAddress.LastAddress.ColumnNumber + ).Rows(); } private void ClearMerged() { - List mergeToDelete = new List(); - foreach (var merge in (Worksheet as XLWorksheet).Internals.MergedRanges) + var mergeToDelete = new List(); + foreach (IXLRange merge in Worksheet.Internals.MergedRanges) { - if (this.Intersects(merge)) - { + if (Intersects(merge)) mergeToDelete.Add(merge); - } } - mergeToDelete.ForEach(m => (Worksheet as XLWorksheet).Internals.MergedRanges.Remove(m)); + mergeToDelete.ForEach(m => Worksheet.Internals.MergedRanges.Remove(m)); } - public bool Contains(String rangeAddress) - { - String addressToUse; - if (rangeAddress.Contains("!")) - { - addressToUse = rangeAddress.Substring(rangeAddress.IndexOf("!") + 1); - } - else - { - addressToUse = rangeAddress; - } - - XLAddress firstAddress; - XLAddress lastAddress; - if (addressToUse.Contains(':')) - { - String[] arrRange = addressToUse.Split(':'); - firstAddress = XLAddress.Create(Worksheet, arrRange[0]); - lastAddress = XLAddress.Create(Worksheet, arrRange[1]); - } - else - { - firstAddress = XLAddress.Create(Worksheet, addressToUse); - lastAddress = XLAddress.Create(Worksheet, addressToUse); - } - return Contains(firstAddress, lastAddress); - } - public bool Contains(IXLRangeBase range) - { - return Contains((XLAddress) range.RangeAddress.FirstAddress, (XLAddress) range.RangeAddress.LastAddress); - } public bool Contains(XLAddress first, XLAddress last) { return Contains(first) && Contains(last); } + public bool Contains(XLAddress address) { - return RangeAddress.FirstAddress.RowNumber <= address.RowNumber && address.RowNumber <= RangeAddress.LastAddress.RowNumber && - RangeAddress.FirstAddress.ColumnNumber <= address.ColumnNumber && address.ColumnNumber <= RangeAddress.LastAddress.ColumnNumber; - } - - public bool Intersects(string rangeAddress) - { - return Intersects(Range(rangeAddress)); - } - public bool Intersects(IXLRangeBase range) - { - if (range.RangeAddress.IsInvalid || RangeAddress.IsInvalid) - { - return false; - } - var ma = range.RangeAddress; - var ra = RangeAddress; - - return !( // See if the two ranges intersect... - ma.FirstAddress.ColumnNumber > ra.LastAddress.ColumnNumber - || ma.LastAddress.ColumnNumber < ra.FirstAddress.ColumnNumber - || ma.FirstAddress.RowNumber > ra.LastAddress.RowNumber - || ma.LastAddress.RowNumber < ra.FirstAddress.RowNumber - ); + return RangeAddress.FirstAddress.RowNumber <= address.RowNumber && + address.RowNumber <= RangeAddress.LastAddress.RowNumber && + RangeAddress.FirstAddress.ColumnNumber <= address.ColumnNumber && + address.ColumnNumber <= RangeAddress.LastAddress.ColumnNumber; } public void Delete(XLShiftDeletedCells shiftDeleteCells) { - var numberOfRows = this.RowCount(); - var numberOfColumns = this.ColumnCount(); + int numberOfRows = RowCount(); + int numberOfColumns = ColumnCount(); IXLRange shiftedRangeFormula; if (shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp) { var lastCell = Worksheet.Cell(ExcelHelper.MaxRowNumber, RangeAddress.LastAddress.ColumnNumber); shiftedRangeFormula = Worksheet.Range(RangeAddress.FirstAddress, lastCell.Address); - if (StringExtensions.IsNullOrWhiteSpace(lastCell.GetString()) && StringExtensions.IsNullOrWhiteSpace(lastCell.FormulaA1)) - (Worksheet as XLWorksheet).Internals.CellsCollection.Remove(lastCell.Address); + if (StringExtensions.IsNullOrWhiteSpace(lastCell.GetString()) && + StringExtensions.IsNullOrWhiteSpace(lastCell.FormulaA1)) + Worksheet.Internals.CellsCollection.Remove(lastCell.Address); } else { var lastCell = Worksheet.Cell(RangeAddress.LastAddress.RowNumber, ExcelHelper.MaxColumnNumber); shiftedRangeFormula = Worksheet.Range(RangeAddress.FirstAddress, lastCell.Address); - if (StringExtensions.IsNullOrWhiteSpace(lastCell.GetString()) && StringExtensions.IsNullOrWhiteSpace(lastCell.FormulaA1)) - (Worksheet as XLWorksheet).Internals.CellsCollection.Remove(lastCell.Address); + if (StringExtensions.IsNullOrWhiteSpace(lastCell.GetString()) && + StringExtensions.IsNullOrWhiteSpace(lastCell.FormulaA1)) + Worksheet.Internals.CellsCollection.Remove(lastCell.Address); } - foreach (var ws in (Worksheet as XLWorksheet).Internals.Workbook.Worksheets) + foreach (IXLWorksheet ws in Worksheet.Internals.Workbook.Worksheets) { var xlWorksheet = (XLWorksheet)ws; - foreach (var cell in (xlWorksheet as XLWorksheet).Internals.CellsCollection.Values.Where(c => !StringExtensions.IsNullOrWhiteSpace(c.FormulaA1))) + foreach ( + XLCell cell in + (xlWorksheet).Internals.CellsCollection.Values.Where( + c => !StringExtensions.IsNullOrWhiteSpace(c.FormulaA1))) { if (shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp) cell.ShiftFormulaRows((XLRange)shiftedRangeFormula, numberOfRows * -1); @@ -885,97 +945,61 @@ // Range to shift... var cellsToInsert = new Dictionary(); var cellsToDelete = new List(); - var shiftLeftQuery = (Worksheet as XLWorksheet).Internals.CellsCollection + var shiftLeftQuery = Worksheet.Internals.CellsCollection .Where(c => - c.Key.RowNumber >= this.RangeAddress.FirstAddress.RowNumber - && c.Key.RowNumber <= this.RangeAddress.LastAddress.RowNumber - && c.Key.ColumnNumber >= this.RangeAddress.FirstAddress.ColumnNumber); + c.Key.RowNumber >= RangeAddress.FirstAddress.RowNumber + && c.Key.RowNumber <= RangeAddress.LastAddress.RowNumber + && c.Key.ColumnNumber >= RangeAddress.FirstAddress.ColumnNumber); - var shiftUpQuery = (Worksheet as XLWorksheet).Internals.CellsCollection + var shiftUpQuery = Worksheet.Internals.CellsCollection .Where(c => - c.Key.ColumnNumber >= this.RangeAddress.FirstAddress.ColumnNumber - && c.Key.ColumnNumber <= this.RangeAddress.LastAddress.ColumnNumber - && c.Key.RowNumber >= this.RangeAddress.FirstAddress.RowNumber); + c.Key.ColumnNumber >= RangeAddress.FirstAddress.ColumnNumber + && c.Key.ColumnNumber <= RangeAddress.LastAddress.ColumnNumber + && c.Key.RowNumber >= RangeAddress.FirstAddress.RowNumber); - var columnModifier = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsLeft ? this.ColumnCount() : 0; - var rowModifier = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp ? this.RowCount() : 0; + int columnModifier = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsLeft ? ColumnCount() : 0; + int rowModifier = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp ? RowCount() : 0; var cellsQuery = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsLeft ? shiftLeftQuery : shiftUpQuery; - foreach (var c in cellsQuery) + foreach (KeyValuePair c in cellsQuery) { - var newKey = new XLAddress(Worksheet, c.Key.RowNumber - rowModifier, c.Key.ColumnNumber - columnModifier, false, false); + var newKey = new XLAddress(Worksheet, c.Key.RowNumber - rowModifier, c.Key.ColumnNumber - columnModifier, + false, false); var newCell = new XLCell(Worksheet, newKey, c.Value.Style); newCell.CopyValues(c.Value); //newCell.ShiftFormula(rowModifier * -1, columnModifier * -1); cellsToDelete.Add(c.Key); - var canInsert = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsLeft ? - c.Key.ColumnNumber > this.RangeAddress.LastAddress.ColumnNumber : - c.Key.RowNumber > this.RangeAddress.LastAddress.RowNumber; + bool canInsert = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsLeft + ? c.Key.ColumnNumber > RangeAddress.LastAddress.ColumnNumber + : c.Key.RowNumber > RangeAddress.LastAddress.RowNumber; if (canInsert) cellsToInsert.Add(newKey, newCell); } - cellsToDelete.ForEach(c => (Worksheet as XLWorksheet).Internals.CellsCollection.Remove(c)); - cellsToInsert.ForEach(c => (Worksheet as XLWorksheet).Internals.CellsCollection.Add(c.Key, c.Value)); + cellsToDelete.ForEach(c => Worksheet.Internals.CellsCollection.Remove(c)); + cellsToInsert.ForEach(c => Worksheet.Internals.CellsCollection.Add(c.Key, c.Value)); - List mergesToRemove = new List(); - foreach (var merge in (Worksheet as XLWorksheet).Internals.MergedRanges) + var mergesToRemove = new List(); + foreach (IXLRange merge in Worksheet.Internals.MergedRanges) { - if (this.Contains(merge)) + if (Contains(merge)) mergesToRemove.Add(merge); } - mergesToRemove.ForEach(r => (Worksheet as XLWorksheet).Internals.MergedRanges.Remove(r)); + mergesToRemove.ForEach(r => Worksheet.Internals.MergedRanges.Remove(r)); - List hyperlinksToRemove = new List(); - foreach (var hl in Worksheet.Hyperlinks) + var hyperlinksToRemove = new List(); + foreach (XLHyperlink hl in Worksheet.Hyperlinks) { - if (this.Contains(hl.Cell.AsRange())) + if (Contains(hl.Cell.AsRange())) hyperlinksToRemove.Add(hl); } hyperlinksToRemove.ForEach(hl => Worksheet.Hyperlinks.Delete(hl)); - var shiftedRange = (XLRange)this.AsRange(); + var shiftedRange = (XLRange)AsRange(); if (shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp) - { - (Worksheet as XLWorksheet).NotifyRangeShiftedRows(shiftedRange, rowModifier * -1); - } + Worksheet.NotifyRangeShiftedRows(shiftedRange, rowModifier * -1); else - { - (Worksheet as XLWorksheet).NotifyRangeShiftedColumns(shiftedRange, columnModifier * -1); - } - } - - #region IXLStylized Members - public virtual IXLStyle Style - { - get { return m_defaultStyle; } - set { Cells().ForEach(c => c.Style = value); } - } - - public virtual IEnumerable Styles - { - get - { - UpdatingStyle = true; - foreach (var cell in Cells()) - { - yield return cell.Style; - } - UpdatingStyle = false; - } - } - - public virtual Boolean UpdatingStyle { get; set; } - - public virtual IXLStyle InnerStyle - { - get { return m_defaultStyle; } - set { m_defaultStyle = new XLStyle(this, value); } - } - #endregion - public virtual IXLRange AsRange() - { - return Worksheet.Range(RangeAddress.FirstAddress, RangeAddress.LastAddress); + Worksheet.NotifyRangeShiftedColumns(shiftedRange, columnModifier * -1); } public override string ToString() @@ -983,97 +1007,63 @@ return String.Format("'{0}'!{1}:{2}", Worksheet.Name, 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) - { - return AddToNamed(rangeName, XLScope.Workbook); - } - public IXLRange AddToNamed(String rangeName, XLScope scope) - { - return AddToNamed(rangeName, scope, null); - } - public IXLRange AddToNamed(String rangeName, XLScope scope, String comment) - { - IXLNamedRanges namedRanges; - if (scope == XLScope.Workbook) - { - namedRanges = (Worksheet).Internals.Workbook.NamedRanges; - } - else - { - namedRanges = Worksheet.NamedRanges; - } - - if (namedRanges.Any(nr => nr.Name.ToLower() == rangeName.ToLower())) - { - var namedRange = namedRanges.Where(nr => nr.Name.ToLower() == rangeName.ToLower()).Single(); - namedRange.Add((Worksheet).Internals.Workbook, ToStringFixed()); - } - else - { - namedRanges.Add(rangeName, ToStringFixed(), comment); - } - return AsRange(); - } - protected void ShiftColumns(IXLRangeAddress thisRangeAddress, XLRange shiftedRange, int columnsShifted) { if (!thisRangeAddress.IsInvalid && !shiftedRange.RangeAddress.IsInvalid) { if ((columnsShifted < 0 // all columns - && thisRangeAddress.FirstAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber - && thisRangeAddress.LastAddress.ColumnNumber <= shiftedRange.RangeAddress.FirstAddress.ColumnNumber - columnsShifted + && + thisRangeAddress.FirstAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber + && + thisRangeAddress.LastAddress.ColumnNumber <= + shiftedRange.RangeAddress.FirstAddress.ColumnNumber - columnsShifted // all rows && thisRangeAddress.FirstAddress.RowNumber >= shiftedRange.RangeAddress.FirstAddress.RowNumber && thisRangeAddress.LastAddress.RowNumber <= shiftedRange.RangeAddress.LastAddress.RowNumber ) || ( - shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= thisRangeAddress.FirstAddress.ColumnNumber - && shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber - && shiftedRange.RangeAddress.LastAddress.RowNumber >= thisRangeAddress.LastAddress.RowNumber - && shiftedRange.ColumnCount() > - (thisRangeAddress.LastAddress.ColumnNumber - thisRangeAddress.FirstAddress.ColumnNumber + 1) - + (thisRangeAddress.FirstAddress.ColumnNumber - shiftedRange.RangeAddress.FirstAddress.ColumnNumber))) - { + shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= + thisRangeAddress.FirstAddress.ColumnNumber + && + shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber + && + shiftedRange.RangeAddress.LastAddress.RowNumber >= thisRangeAddress.LastAddress.RowNumber + && shiftedRange.ColumnCount() > + (thisRangeAddress.LastAddress.ColumnNumber - thisRangeAddress.FirstAddress.ColumnNumber + 1) + + + (thisRangeAddress.FirstAddress.ColumnNumber - + shiftedRange.RangeAddress.FirstAddress.ColumnNumber))) thisRangeAddress.IsInvalid = true; - } else { if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber && shiftedRange.RangeAddress.LastAddress.RowNumber >= thisRangeAddress.LastAddress.RowNumber) { if ( - (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= thisRangeAddress.FirstAddress.ColumnNumber && - columnsShifted > 0) - || - (shiftedRange.RangeAddress.FirstAddress.ColumnNumber < thisRangeAddress.FirstAddress.ColumnNumber && - columnsShifted < 0) - ) + (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= + thisRangeAddress.FirstAddress.ColumnNumber && + columnsShifted > 0) + || + (shiftedRange.RangeAddress.FirstAddress.ColumnNumber < + thisRangeAddress.FirstAddress.ColumnNumber && + columnsShifted < 0) + ) { thisRangeAddress.FirstAddress = new XLAddress(Worksheet, thisRangeAddress.FirstAddress.RowNumber, - thisRangeAddress.FirstAddress.ColumnNumber + columnsShifted, + thisRangeAddress.FirstAddress.ColumnNumber + + columnsShifted, thisRangeAddress.FirstAddress.FixedRow, thisRangeAddress.FirstAddress.FixedColumn); } - if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= thisRangeAddress.LastAddress.ColumnNumber) + if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= + thisRangeAddress.LastAddress.ColumnNumber) { thisRangeAddress.LastAddress = new XLAddress(Worksheet, thisRangeAddress.LastAddress.RowNumber, - thisRangeAddress.LastAddress.ColumnNumber + columnsShifted, + thisRangeAddress.LastAddress.ColumnNumber + + columnsShifted, thisRangeAddress.LastAddress.FixedRow, thisRangeAddress.LastAddress.FixedColumn); } @@ -1088,33 +1078,45 @@ { if ((rowsShifted < 0 // all columns - && thisRangeAddress.FirstAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber + && + thisRangeAddress.FirstAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber && thisRangeAddress.LastAddress.ColumnNumber <= shiftedRange.RangeAddress.FirstAddress.ColumnNumber // all rows && thisRangeAddress.FirstAddress.RowNumber >= shiftedRange.RangeAddress.FirstAddress.RowNumber - && thisRangeAddress.LastAddress.RowNumber <= shiftedRange.RangeAddress.LastAddress.RowNumber - rowsShifted + && + thisRangeAddress.LastAddress.RowNumber <= + shiftedRange.RangeAddress.LastAddress.RowNumber - rowsShifted ) || ( - shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber - && shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= thisRangeAddress.FirstAddress.ColumnNumber - && shiftedRange.RangeAddress.LastAddress.ColumnNumber >= thisRangeAddress.LastAddress.ColumnNumber - && shiftedRange.RowCount() > - (thisRangeAddress.LastAddress.RowNumber - thisRangeAddress.FirstAddress.RowNumber + 1) - + (thisRangeAddress.FirstAddress.RowNumber - shiftedRange.RangeAddress.FirstAddress.RowNumber))) - { + shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber + && + shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= + thisRangeAddress.FirstAddress.ColumnNumber + && + shiftedRange.RangeAddress.LastAddress.ColumnNumber >= + thisRangeAddress.LastAddress.ColumnNumber + && shiftedRange.RowCount() > + (thisRangeAddress.LastAddress.RowNumber - thisRangeAddress.FirstAddress.RowNumber + 1) + + + (thisRangeAddress.FirstAddress.RowNumber - shiftedRange.RangeAddress.FirstAddress.RowNumber))) thisRangeAddress.IsInvalid = true; - } else { - if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= thisRangeAddress.FirstAddress.ColumnNumber - && shiftedRange.RangeAddress.LastAddress.ColumnNumber >= thisRangeAddress.LastAddress.ColumnNumber) + if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= + thisRangeAddress.FirstAddress.ColumnNumber + && + shiftedRange.RangeAddress.LastAddress.ColumnNumber >= thisRangeAddress.LastAddress.ColumnNumber) { if ( - (shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber && rowsShifted > 0) - || (shiftedRange.RangeAddress.FirstAddress.RowNumber < thisRangeAddress.FirstAddress.RowNumber && rowsShifted < 0) - ) + (shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber && + rowsShifted > 0) + || + (shiftedRange.RangeAddress.FirstAddress.RowNumber < thisRangeAddress.FirstAddress.RowNumber && + rowsShifted < 0) + ) { thisRangeAddress.FirstAddress = new XLAddress(Worksheet, - thisRangeAddress.FirstAddress.RowNumber + rowsShifted, + thisRangeAddress.FirstAddress.RowNumber + + rowsShifted, thisRangeAddress.FirstAddress.ColumnNumber, thisRangeAddress.FirstAddress.FixedRow, thisRangeAddress.FirstAddress.FixedColumn); @@ -1123,7 +1125,8 @@ if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.LastAddress.RowNumber) { thisRangeAddress.LastAddress = new XLAddress(Worksheet, - thisRangeAddress.LastAddress.RowNumber + rowsShifted, + thisRangeAddress.LastAddress.RowNumber + + rowsShifted, thisRangeAddress.LastAddress.ColumnNumber, thisRangeAddress.LastAddress.FixedRow, thisRangeAddress.LastAddress.FixedColumn); @@ -1142,19 +1145,11 @@ { var firstCell = FirstCellUsed(includeStyles); if (firstCell == null) - { return null; - } var lastCell = LastCellUsed(includeStyles); return Worksheet.Range(firstCell, lastCell); } - public IXLRangeBase SetValue(T value) - { - Cells().ForEach(c => c.SetValue(value)); - return this; - } - public virtual void CopyTo(IXLRangeBase target) { CopyTo(target.FirstCell()); @@ -1173,18 +1168,14 @@ public void SetAutoFilter(Boolean autoFilter) { if (autoFilter) - { Worksheet.AutoFilterRange = this; - } else - { Worksheet.AutoFilterRange = null; - } } //public IXLChart CreateChart(Int32 firstRow, Int32 firstColumn, Int32 lastRow, Int32 lastColumn) //{ - // IXLChart chart = new XLChart(Worksheet); + // IXLChart chart = new XLChartWorksheet; // chart.FirstRow = firstRow; // chart.LastRow = lastRow; // chart.LastColumn = lastColumn; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs index 34cab9e..92f8ac6 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs @@ -14,7 +14,7 @@ { Worksheet.RangeShiftedRows += WorksheetRangeShiftedRows; Worksheet.RangeShiftedColumns += WorksheetRangeShiftedColumns; - m_defaultStyle = new XLStyle(this, rangeParameters.DefaultStyle); + DefaultStyle = new XLStyle(this, rangeParameters.DefaultStyle); } } @@ -29,7 +29,7 @@ public new IXLCells Cells(string cellsInColumn) { - var retVal = new XLCells(false, false, false); + var retVal = new XLCells( false, false); var rangePairs = cellsInColumn.Split(','); foreach (string pair in rangePairs) retVal.Add(Range(pair.Trim()).RangeAddress); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs index db6ae4a..c6a3c9b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs @@ -93,7 +93,7 @@ public IXLCells Cells() { - var cells = new XLCells(false, false, false); + var cells = new XLCells( false, false); foreach (var container in ranges) { cells.Add(container.RangeAddress); @@ -103,7 +103,7 @@ public IXLCells CellsUsed() { - var cells = new XLCells(false, true, false); + var cells = new XLCells( true, false); foreach (var container in ranges) { cells.Add(container.RangeAddress); @@ -113,7 +113,7 @@ public IXLCells CellsUsed(Boolean includeStyles) { - var cells = new XLCells(false, true, includeStyles); + var cells = new XLCells( true, includeStyles); foreach (var container in ranges) { cells.Add(container.RangeAddress); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs index bc52b43..ddb1ab3 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs @@ -15,7 +15,7 @@ { Worksheet.RangeShiftedRows += WorksheetRangeShiftedRows; Worksheet.RangeShiftedColumns += WorksheetRangeShiftedColumns; - m_defaultStyle = new XLStyle(this, rangeParameters.DefaultStyle); + DefaultStyle = new XLStyle(this, rangeParameters.DefaultStyle); } } @@ -62,7 +62,7 @@ public new IXLCells Cells(string cellsInRow) { - var retVal = new XLCells(false, false, false); + var retVal = new XLCells( false, false); var rangePairs = cellsInRow.Split(','); foreach (string pair in rangePairs) retVal.Add(Range(pair.Trim()).RangeAddress); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs index 117534d..572b8d6 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs @@ -92,7 +92,7 @@ public IXLCells Cells() { - var cells = new XLCells(false, false, false); + var cells = new XLCells( false, false); foreach (var container in ranges) { cells.Add(container.RangeAddress); @@ -102,7 +102,7 @@ public IXLCells CellsUsed() { - var cells = new XLCells(false, true, false); + var cells = new XLCells( true, false); foreach (var container in ranges) { cells.Add(container.RangeAddress); @@ -112,7 +112,7 @@ public IXLCells CellsUsed(Boolean includeStyles) { - var cells = new XLCells(false, true, includeStyles); + var cells = new XLCells( true, includeStyles); foreach (var container in ranges) { cells.Add(container.RangeAddress); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs index d6b77ca..a2ec270 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs @@ -232,7 +232,7 @@ public IXLCells Cells() { - var cells = new XLCells(false, false, false); + var cells = new XLCells( false, false); foreach (var container in ranges) { cells.Add(container.RangeAddress); @@ -242,7 +242,7 @@ public IXLCells CellsUsed() { - var cells = new XLCells(false, true, false); + var cells = new XLCells( true, false); foreach (var container in ranges) { cells.Add(container.RangeAddress); @@ -252,7 +252,7 @@ public IXLCells CellsUsed(Boolean includeStyles) { - var cells = new XLCells(false, true, includeStyles); + var cells = new XLCells( true, includeStyles); foreach (var container in ranges) { cells.Add(container.RangeAddress); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs index cb6aa1a..cea522f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs @@ -161,7 +161,7 @@ public new IXLCells Cells(String cellsInRow) { - var retVal = new XLCells(false, false, false); + var retVal = new XLCells( false, false); var rangePairs = cellsInRow.Split(','); foreach (var pair in rangePairs) { @@ -231,9 +231,7 @@ foreach (var cell in Row(startColumn, endColumn).CellsUsed()) { var c = (XLCell) cell; - var cellAsRange = c.AsRange(); - Boolean isMerged = Worksheet.Internals.MergedRanges.Any(m => cellAsRange.Intersects(m)); - if (!isMerged) + if (!c.IsMerged()) { Double thisHeight; Int32 textRotation = c.Style.Alignment.TextRotation; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs index 0396135..434e67c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs @@ -208,7 +208,7 @@ public IXLCells Cells() { - var cells = new XLCells(false, false, false); + var cells = new XLCells( false, false); foreach (var container in rows) { cells.Add(container.RangeAddress); @@ -218,7 +218,7 @@ public IXLCells CellsUsed() { - var cells = new XLCells(false, true, false); + var cells = new XLCells( true, false); foreach (var container in rows) { cells.Add(container.RangeAddress); @@ -228,7 +228,7 @@ public IXLCells CellsUsed(Boolean includeStyles) { - var cells = new XLCells(false, true, includeStyles); + var cells = new XLCells( true, includeStyles); foreach (var container in rows) { cells.Add(container.RangeAddress); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs index 1fe7564..f54e220 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs @@ -1,39 +1,282 @@ using System; -using System.Drawing; using System.Text; -using System.Windows.Forms; namespace ClosedXML.Excel { internal class XLFont : IXLFont { - IXLStylized container; + private readonly IXLStylized _container; + private Boolean _bold; + private IXLColor _fontColor; + private XLFontFamilyNumberingValues _fontFamilyNumbering; + private String _fontName; + private Double _fontSize; + private Boolean _italic; + private Boolean _shadow; + private Boolean _strikethrough; + private XLFontUnderlineValues _underline; + private XLFontVerticalTextAlignmentValues _verticalAlignment; + public XLFont() : this(null, XLWorkbook.DefaultStyle.Font) { - } + public XLFont(IXLStylized container, IXLFontBase defaultFont) { - this.container = container; + _container = container; if (defaultFont != null) { - bold = defaultFont.Bold; - italic = defaultFont.Italic; - underline = defaultFont.Underline; - strikethrough = defaultFont.Strikethrough; - verticalAlignment = defaultFont.VerticalAlignment; - shadow = defaultFont.Shadow; - fontSize = defaultFont.FontSize; - fontColor = new XLColor(defaultFont.FontColor); - fontName = defaultFont.FontName; - fontFamilyNumbering = defaultFont.FontFamilyNumbering; + _bold = defaultFont.Bold; + _italic = defaultFont.Italic; + _underline = defaultFont.Underline; + _strikethrough = defaultFont.Strikethrough; + _verticalAlignment = defaultFont.VerticalAlignment; + _shadow = defaultFont.Shadow; + _fontSize = defaultFont.FontSize; + _fontColor = new XLColor(defaultFont.FontColor); + _fontName = defaultFont.FontName; + _fontFamilyNumbering = defaultFont.FontFamilyNumbering; } } + #region IXLFont Members + + public Boolean Bold + { + get { return _bold; } + set + { + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Font.Bold = value); + else + _bold = value; + } + } + + public Boolean Italic + { + get { return _italic; } + set + { + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Font.Italic = value); + else + _italic = value; + } + } + + public XLFontUnderlineValues Underline + { + get { return _underline; } + set + { + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Font.Underline = value); + else + _underline = value; + } + } + + public Boolean Strikethrough + { + get { return _strikethrough; } + set + { + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Font.Strikethrough = value); + else + _strikethrough = value; + } + } + + public XLFontVerticalTextAlignmentValues VerticalAlignment + { + get { return _verticalAlignment; } + set + { + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Font.VerticalAlignment = value); + else + _verticalAlignment = value; + } + } + + public Boolean Shadow + { + get { return _shadow; } + set + { + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Font.Shadow = value); + else + _shadow = value; + } + } + + public Double FontSize + { + get { return _fontSize; } + set + { + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Font.FontSize = value); + else + _fontSize = value; + } + } + + public IXLColor FontColor + { + get { return _fontColor; } + set + { + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Font.FontColor = value); + else + _fontColor = value; + } + } + + public String FontName + { + get { return _fontName; } + set + { + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Font.FontName = value); + else + _fontName = value; + } + } + + public XLFontFamilyNumberingValues FontFamilyNumbering + { + get { return _fontFamilyNumbering; } + set + { + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Font.FontFamilyNumbering = value); + else + _fontFamilyNumbering = value; + } + } + + public IXLStyle SetBold() + { + Bold = true; + return _container.Style; + } + + public IXLStyle SetBold(Boolean value) + { + Bold = value; + return _container.Style; + } + + public IXLStyle SetItalic() + { + Italic = true; + return _container.Style; + } + + public IXLStyle SetItalic(Boolean value) + { + Italic = value; + return _container.Style; + } + + public IXLStyle SetUnderline() + { + Underline = XLFontUnderlineValues.Single; + return _container.Style; + } + + public IXLStyle SetUnderline(XLFontUnderlineValues value) + { + Underline = value; + return _container.Style; + } + + public IXLStyle SetStrikethrough() + { + Strikethrough = true; + return _container.Style; + } + + public IXLStyle SetStrikethrough(Boolean value) + { + Strikethrough = value; + return _container.Style; + } + + public IXLStyle SetVerticalAlignment(XLFontVerticalTextAlignmentValues value) + { + VerticalAlignment = value; + return _container.Style; + } + + public IXLStyle SetShadow() + { + Shadow = true; + return _container.Style; + } + + public IXLStyle SetShadow(Boolean value) + { + Shadow = value; + return _container.Style; + } + + public IXLStyle SetFontSize(Double value) + { + FontSize = value; + return _container.Style; + } + + public IXLStyle SetFontColor(IXLColor value) + { + FontColor = value; + return _container.Style; + } + + public IXLStyle SetFontName(String value) + { + FontName = value; + return _container.Style; + } + + public IXLStyle SetFontFamilyNumbering(XLFontFamilyNumberingValues value) + { + FontFamilyNumbering = value; + return _container.Style; + } + + public Boolean Equals(IXLFont other) + { + var otherF = other as XLFont; + if (otherF == null) + return false; + + return + _bold == otherF._bold + && _italic == otherF._italic + && _underline == otherF._underline + && _strikethrough == otherF._strikethrough + && _verticalAlignment == otherF._verticalAlignment + && _shadow == otherF._shadow + && _fontSize == otherF._fontSize + && _fontColor.Equals(otherF._fontColor) + && _fontName == otherF._fontName + && _fontFamilyNumbering == otherF._fontFamilyNumbering + ; + } + + #endregion + public override string ToString() { - StringBuilder sb = new StringBuilder(); + var sb = new StringBuilder(); sb.Append(Bold.ToString()); sb.Append("-"); sb.Append(Italic.ToString()); @@ -56,216 +299,23 @@ return sb.ToString(); } - #region IXLFont Members - - private Boolean bold; - public Boolean Bold - { - get - { - return bold; - } - set - { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Font.Bold = value); - else - bold = value; - } - } - - private Boolean italic; - public Boolean Italic - { - get - { - return italic; - } - set - { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Font.Italic = value); - else - italic = value; - } - } - - private XLFontUnderlineValues underline; - public XLFontUnderlineValues Underline - { - get - { - return underline; - } - set - { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Font.Underline = value); - else - underline = value; - } - } - - private Boolean strikethrough; - public Boolean Strikethrough - { - get - { - return strikethrough; - } - set - { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Font.Strikethrough = value); - else - strikethrough = value; - } - } - - private XLFontVerticalTextAlignmentValues verticalAlignment; - public XLFontVerticalTextAlignmentValues VerticalAlignment - { - get - { - return verticalAlignment; - } - set - { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Font.VerticalAlignment = value); - else - verticalAlignment = value; - } - } - - private Boolean shadow; - public Boolean Shadow - { - get - { - return shadow; - } - set - { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Font.Shadow = value); - else - shadow = value; - } - } - - private Double fontSize; - public Double FontSize - { - get - { - return fontSize; - } - set - { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Font.FontSize = value); - else - fontSize = value; - } - } - - private IXLColor fontColor; - public IXLColor FontColor - { - get - { - return fontColor; - } - set - { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Font.FontColor = value); - else - fontColor = value; - } - } - - private String fontName; - public String FontName - { - get - { - return fontName; - } - set - { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Font.FontName = value); - else - fontName = value; - } - } - - private XLFontFamilyNumberingValues fontFamilyNumbering; - public XLFontFamilyNumberingValues FontFamilyNumbering - { - get - { - return fontFamilyNumbering; - } - set - { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Font.FontFamilyNumbering = value); - else - fontFamilyNumbering = value; - } - } - - public IXLStyle SetBold() { Bold = true; return container.Style; } public IXLStyle SetBold(Boolean value) { Bold = value; return container.Style; } - public IXLStyle SetItalic() { Italic = true; return container.Style; } public IXLStyle SetItalic(Boolean value) { Italic = value; return container.Style; } - public IXLStyle SetUnderline() { Underline = XLFontUnderlineValues.Single; return container.Style; } public IXLStyle SetUnderline(XLFontUnderlineValues value) { Underline = value; return container.Style; } - public IXLStyle SetStrikethrough() { Strikethrough = true; return container.Style; } public IXLStyle SetStrikethrough(Boolean value) { Strikethrough = value; return container.Style; } - public IXLStyle SetVerticalAlignment(XLFontVerticalTextAlignmentValues value) { VerticalAlignment = value; return container.Style; } - public IXLStyle SetShadow() { Shadow = true; return container.Style; } public IXLStyle SetShadow(Boolean value) { Shadow = value; return container.Style; } - public IXLStyle SetFontSize(Double value) { FontSize = value; return container.Style; } - public IXLStyle SetFontColor(IXLColor value) { FontColor = value; return container.Style; } - public IXLStyle SetFontName(String value) { FontName = value; return container.Style; } - public IXLStyle SetFontFamilyNumbering(XLFontFamilyNumberingValues value) { FontFamilyNumbering = value; return container.Style; } - - - #endregion - - public Boolean Equals(IXLFont other) - { - var otherF = other as XLFont; - return - bold == otherF.bold - && italic == otherF.italic - && underline == otherF.underline - && strikethrough == otherF.strikethrough - && verticalAlignment == otherF.verticalAlignment - && shadow == otherF.shadow - && fontSize == otherF.fontSize - && fontColor.Equals(otherF.fontColor) - && fontName == otherF.fontName - && fontFamilyNumbering == otherF.fontFamilyNumbering - ; - } - public override bool Equals(object obj) { - return this.Equals((XLFont)obj); + return Equals((XLFont)obj); } public override int GetHashCode() { return Bold.GetHashCode() - ^ Italic.GetHashCode() - ^ (Int32)Underline - ^ Strikethrough.GetHashCode() - ^ (Int32)VerticalAlignment - ^ Shadow.GetHashCode() - ^ FontSize.GetHashCode() - ^ FontColor.GetHashCode() - ^ FontName.GetHashCode() - ^ (Int32)FontFamilyNumbering; + ^ Italic.GetHashCode() + ^ (Int32)Underline + ^ Strikethrough.GetHashCode() + ^ (Int32)VerticalAlignment + ^ Shadow.GetHashCode() + ^ FontSize.GetHashCode() + ^ FontColor.GetHashCode() + ^ FontName.GetHashCode() + ^ (Int32)FontFamilyNumbering; } } -} +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRows.cs index 6cdc423..ea5fc50 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRows.cs @@ -88,7 +88,7 @@ public IXLCells Cells() { - var cells = new XLCells(false, false, false); + var cells = new XLCells( false, false); foreach (var container in ranges) { cells.Add(container.RangeAddress); @@ -98,7 +98,7 @@ public IXLCells CellsUsed() { - var cells = new XLCells(false, true, false); + var cells = new XLCells( true, false); foreach (var container in ranges) { cells.Add(container.RangeAddress); @@ -108,7 +108,7 @@ public IXLCells CellsUsed(Boolean includeStyles) { - var cells = new XLCells(false, false, includeStyles); + var cells = new XLCells( false, includeStyles); foreach (var container in ranges) { cells.Add(container.RangeAddress); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs index 78aa546..200f8e4 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs @@ -417,7 +417,7 @@ public IXLCells FindCells(Func predicate) { - var cells = new XLCells(false, false, false); + var cells = new XLCells( false, false); foreach (var ws in WorksheetsInternal) { foreach (var cell in ws.CellsUsed(true)) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index 6477729..01503a9 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -567,7 +567,7 @@ columns.Elements().Where(c => c.Max == ExcelHelper.MaxColumnNumber).FirstOrDefault(); if (wsDefaultColumn != null && wsDefaultColumn.Width != null) - ws.ColumnWidth = wsDefaultColumn.Width - COLUMN_WIDTH_OFFSET; + ws.ColumnWidth = wsDefaultColumn.Width - ColumnWidthOffset; Int32 styleIndexDefault = wsDefaultColumn != null && wsDefaultColumn.Style != null ? Int32.Parse(wsDefaultColumn.Style.InnerText) @@ -582,7 +582,7 @@ { var xlColumns = (XLColumns) ws.Columns(col.Min, col.Max); if (col.Width != null) - xlColumns.Width = col.Width - COLUMN_WIDTH_OFFSET; + xlColumns.Width = col.Width - ColumnWidthOffset; else xlColumns.Width = ws.ColumnWidth; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index d47c174..db40014 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -36,16 +36,16 @@ { public partial class XLWorkbook { - private const Double COLUMN_WIDTH_OFFSET = 0.71; + private const Double ColumnWidthOffset = 0.71; //private Dictionary sharedStrings; //private Dictionary context.SharedStyles; - private static readonly EnumValue cvSharedString = new EnumValue(CellValues.SharedString); - private static readonly EnumValue cvInlineString = new EnumValue(CellValues.InlineString); - private static readonly EnumValue cvNumber = new EnumValue(CellValues.Number); - private static readonly EnumValue cvDate = new EnumValue(CellValues.Date); - private static readonly EnumValue cvBoolean = new EnumValue(CellValues.Boolean); + private static readonly EnumValue CvSharedString = new EnumValue(CellValues.SharedString); + private static readonly EnumValue CvInlineString = new EnumValue(CellValues.InlineString); + private static readonly EnumValue CvNumber = new EnumValue(CellValues.Number); + private static readonly EnumValue CvDate = new EnumValue(CellValues.Date); + private static readonly EnumValue CvBoolean = new EnumValue(CellValues.Boolean); private static EnumValue GetCellValue(XLCell xlCell) { @@ -53,16 +53,16 @@ { case XLCellValues.Text: { - return xlCell.ShareString ? cvSharedString : cvInlineString; + return xlCell.ShareString ? CvSharedString : CvInlineString; } case XLCellValues.Number: - return cvNumber; + return CvNumber; case XLCellValues.DateTime: - return cvDate; + return CvDate; case XLCellValues.Boolean: - return cvBoolean; + return CvBoolean; case XLCellValues.TimeSpan: - return cvNumber; + return CvNumber; default: throw new NotImplementedException(); } @@ -71,11 +71,9 @@ private void CreatePackage(String filePath) { PathHelper.CreateDirectory(Path.GetDirectoryName(filePath)); - SpreadsheetDocument package; - if (File.Exists(filePath)) - package = SpreadsheetDocument.Open(filePath, true); - else - package = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook); + var package = File.Exists(filePath) + ? SpreadsheetDocument.Open(filePath, true) + : SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook); using (package) { @@ -86,11 +84,9 @@ private void CreatePackage(Stream stream, Boolean newStream) { - SpreadsheetDocument package; - if (newStream) - package = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook); - else - package = SpreadsheetDocument.Open(stream, true); + var package = newStream + ? SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook) + : SpreadsheetDocument.Open(stream, true); using (package) { @@ -111,24 +107,11 @@ partsToRemove.ForEach(s => workbookPart.DeletePart(s.OpenXmlPart)); context.RelIdGenerator.AddValues(workbookPart.Parts.Select(p => p.RelationshipId).ToList(), RelType.Workbook); - var modifiedSheetNames = worksheets.Select(w => w.Name.ToLower()).ToList(); - - List existingSheetNames; - if (workbookPart.Workbook != null && workbookPart.Workbook.Sheets != null) - { - existingSheetNames = - workbookPart.Workbook.Sheets.Elements().Select(s => s.Name.Value.ToLower()).ToList(); - } - else - existingSheetNames = new List(); - - var allSheetNames = existingSheetNames.Union(modifiedSheetNames); - var extendedFilePropertiesPart = document.ExtendedFilePropertiesPart ?? document.AddNewPart( context.RelIdGenerator.GetNext(RelType.Workbook)); - GenerateExtendedFilePropertiesPartContent(extendedFilePropertiesPart, workbookPart); + GenerateExtendedFilePropertiesPartContent(extendedFilePropertiesPart); GenerateWorkbookPartContent(workbookPart, context); @@ -147,14 +130,15 @@ foreach (XLWorksheet worksheet in WorksheetsInternal.Cast().OrderBy(w => w.Position)) { WorksheetPart worksheetPart; - if (workbookPart.Parts.Any(p => p.RelationshipId == worksheet.RelId)) + string wsRelId = worksheet.RelId; + if (workbookPart.Parts.Any(p => p.RelationshipId == wsRelId)) { - worksheetPart = (WorksheetPart)workbookPart.GetPartById(worksheet.RelId); + worksheetPart = (WorksheetPart)workbookPart.GetPartById(wsRelId); var wsPartsToRemove = worksheetPart.TableDefinitionParts.ToList(); wsPartsToRemove.ForEach(tdp => worksheetPart.DeletePart(tdp)); } else - worksheetPart = workbookPart.AddNewPart(worksheet.RelId); + worksheetPart = workbookPart.AddNewPart(wsRelId); GenerateWorksheetPartContent(worksheetPart, worksheet, context); @@ -187,7 +171,7 @@ SetPackageProperties(document); } - private void GenerateTables(XLWorksheet worksheet, WorksheetPart worksheetPart, SaveContext context) + private static void GenerateTables(XLWorksheet worksheet, WorksheetPart worksheetPart, SaveContext context) { worksheetPart.Worksheet.RemoveAllChildren(); if (worksheet.Tables.Any()) @@ -203,15 +187,12 @@ } } - private void GenerateExtendedFilePropertiesPartContent(ExtendedFilePropertiesPart extendedFilePropertiesPart, - WorkbookPart workbookPart) + private void GenerateExtendedFilePropertiesPartContent(ExtendedFilePropertiesPart extendedFilePropertiesPart) { - //if (extendedFilePropertiesPart.Properties.NamespaceDeclarations.Contains(new KeyValuePair( - Properties properties; if (extendedFilePropertiesPart.Properties == null) extendedFilePropertiesPart.Properties = new Properties(); - properties = extendedFilePropertiesPart.Properties; + var properties = extendedFilePropertiesPart.Properties; if ( !properties.NamespaceDeclarations.Contains(new KeyValuePair("vt", "http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"))) @@ -239,11 +220,9 @@ properties.TitlesOfParts.VTVector = new VTVector {BaseType = VectorBaseValues.Lpstr}; - VTVector vTVector_One; - vTVector_One = properties.HeadingPairs.VTVector; + var vTVectorOne = properties.HeadingPairs.VTVector; - VTVector vTVector_Two; - vTVector_Two = properties.TitlesOfParts.VTVector; + var vTVectorTwo = properties.TitlesOfParts.VTVector; var modifiedWorksheets = ((IEnumerable)WorksheetsInternal).Select(w => new {w.Name, Order = w.Position}).ToList(); @@ -251,21 +230,21 @@ int modifiedWorksheetsCount = modifiedWorksheets.Count(); int modifiedNamedRangesCount = modifiedNamedRanges.Count(); - InsertOnVTVector(vTVector_One, "Worksheets", 0, modifiedWorksheetsCount.ToString()); - InsertOnVTVector(vTVector_One, "Named Ranges", 2, modifiedNamedRangesCount.ToString()); + InsertOnVtVector(vTVectorOne, "Worksheets", 0, modifiedWorksheetsCount.ToString()); + InsertOnVtVector(vTVectorOne, "Named Ranges", 2, modifiedNamedRangesCount.ToString()); - vTVector_Two.Size = (UInt32)(modifiedNamedRangesCount + modifiedWorksheetsCount); + vTVectorTwo.Size = (UInt32)(modifiedNamedRangesCount + modifiedWorksheetsCount); foreach (var w in modifiedWorksheets.OrderBy(w => w.Order)) { - var vTLPSTR3 = new VTLPSTR {Text = w.Name}; - vTVector_Two.AppendChild(vTLPSTR3); + var vTlpstr3 = new VTLPSTR {Text = w.Name}; + vTVectorTwo.AppendChild(vTlpstr3); } foreach (string nr in modifiedNamedRanges) { - var vTLPSTR7 = new VTLPSTR {Text = nr}; - vTVector_Two.AppendChild(vTLPSTR7); + var vTlpstr7 = new VTLPSTR {Text = nr}; + vTVectorTwo.AppendChild(vTlpstr7); } if (Properties.Manager != null) @@ -291,11 +270,11 @@ properties.Company.Text = Properties.Company; } else - properties = null; + properties.Company = null; } } - private void InsertOnVTVector(VTVector vTVector, String property, Int32 index, String text) + private static void InsertOnVtVector(VTVector vTVector, String property, Int32 index, String text) { var m = from e1 in vTVector.Elements() where e1.Elements().Any(e2 => e2.Text == property) @@ -307,8 +286,8 @@ vTVector.Size += 2U; var variant1 = new Variant(); - var vTLPSTR1 = new VTLPSTR {Text = property}; - variant1.AppendChild(vTLPSTR1); + var vTlpstr1 = new VTLPSTR {Text = property}; + variant1.AppendChild(vTlpstr1); vTVector.InsertAt(variant1, index); var variant2 = new Variant(); @@ -329,13 +308,13 @@ } } - private List GetModifiedNamedRanges() + private IEnumerable GetModifiedNamedRanges() { var namedRanges = new List(); foreach (XLWorksheet w in WorksheetsInternal) { - foreach (IXLNamedRange n in w.NamedRanges) - namedRanges.Add(w.Name + "!" + n.Name); + String wName = w.Name; + namedRanges.AddRange(w.NamedRanges.Select(n => wName + "!" + n.Name)); namedRanges.Add(w.Name + "!Print_Area"); namedRanges.Add(w.Name + "!Print_Titles"); } @@ -381,13 +360,11 @@ foreach (Sheet sheet in workbook.Sheets.Elements()) { - string sName = sheet.Name.Value; - //if (Worksheets.Where(w => w.Name.ToLower() == sName.ToLower())) - if (WorksheetsInternal.Any(w => (w).SheetId == (Int32)sheet.SheetId.Value)) + Int32 sheetId = (Int32)sheet.SheetId.Value; + if (WorksheetsInternal.Any(w => (w).SheetId == sheetId)) { var wks = - WorksheetsInternal.Where(w => (w).SheetId == (Int32)sheet.SheetId.Value).Single(); - //wks.SheetId = (Int32)sheet.SheetId.Value; + WorksheetsInternal.Where(w => (w).SheetId == sheetId).Single(); wks.RelId = sheet.Id; sheet.Name = wks.Name; } @@ -398,7 +375,8 @@ WorksheetsInternal.Cast().Where(w => w.SheetId == 0).OrderBy(w => w.Position)) { String rId = context.RelIdGenerator.GetNext(RelType.Workbook); - while (WorksheetsInternal.Cast().Any(w => w.SheetId == Int32.Parse(rId.Substring(3)))) + Int32 rIdSub = Int32.Parse(rId.Substring(3)); + while (WorksheetsInternal.Cast().Any(w => w.SheetId == rIdSub)) rId = context.RelIdGenerator.GetNext(RelType.Workbook); xlSheet.SheetId = Int32.Parse(rId.Substring(3)); @@ -462,7 +440,7 @@ } var definedNames = new DefinedNames(); - foreach (XLWorksheet worksheet in WorksheetsInternal.Cast()) + foreach (XLWorksheet worksheet in WorksheetsInternal) { UInt32 sheetId = 0; foreach (Sheet s in workbook.Sheets.Elements()) @@ -475,13 +453,17 @@ if (worksheet.PageSetup.PrintAreas.Any()) { var definedName = new DefinedName {Name = "_xlnm.Print_Area", LocalSheetId = sheetId}; - string definedNameText = String.Empty; - foreach (IXLRange printArea in worksheet.PageSetup.PrintAreas) - { - definedNameText += "'" + worksheet.Name + "'!" - + printArea.RangeAddress.FirstAddress.ToStringFixed() - + ":" + printArea.RangeAddress.LastAddress.ToStringFixed() + ","; - } + String worksheetName = worksheet.Name; + string definedNameText = worksheet.PageSetup.PrintAreas.Aggregate(String.Empty, + (current, printArea) => + current + + ("'" + worksheetName + "'!" + + printArea.RangeAddress. + FirstAddress.ToStringFixed() + + ":" + + printArea.RangeAddress. + LastAddress.ToStringFixed() + + ",")); definedName.Text = definedNameText.Substring(0, definedNameText.Length - 1); definedNames.AppendChild(definedName); } @@ -499,7 +481,7 @@ definedNames.AppendChild(definedName); } - string titles = String.Empty; + string definedNameTextRow = String.Empty; string definedNameTextColumn = String.Empty; if (worksheet.PageSetup.FirstRowToRepeatAtTop > 0) @@ -516,6 +498,7 @@ + ":" + ExcelHelper.GetColumnLetterFromNumber(maxColumn); } + string titles; if (definedNameTextColumn.Length > 0) { titles = definedNameTextColumn; @@ -527,8 +510,12 @@ if (titles.Length > 0) { - var definedName = new DefinedName {Name = "_xlnm.Print_Titles", LocalSheetId = sheetId}; - definedName.Text = titles; + var definedName = new DefinedName + { + Name = "_xlnm.Print_Titles", + LocalSheetId = sheetId, + Text = titles + }; definedNames.AppendChild(definedName); } } @@ -550,33 +537,35 @@ foreach (DefinedName dn in definedNames) { + String dnName = dn.Name.Value.ToLower(); + var dnLocalSheetId = dn.LocalSheetId; if (workbook.DefinedNames.Elements().Any(d => - d.Name.Value.ToLower() == dn.Name.Value.ToLower() + d.Name.Value.ToLower() == dnName && ( (d.LocalSheetId != null && - dn.LocalSheetId != null && + dnLocalSheetId != null && d.LocalSheetId.InnerText == - dn.LocalSheetId.InnerText) + dnLocalSheetId.InnerText) || d.LocalSheetId == null || - dn.LocalSheetId == null) + dnLocalSheetId == null) )) { var existingDefinedName = (DefinedName)workbook.DefinedNames.Where(d => ((DefinedName)d).Name.Value. ToLower() == - dn.Name.Value.ToLower() + dnName && ( (((DefinedName)d). LocalSheetId != null && - dn.LocalSheetId != null && + dnLocalSheetId != null && ((DefinedName)d). LocalSheetId. InnerText == - dn.LocalSheetId.InnerText) + dnLocalSheetId.InnerText) || ((DefinedName)d). LocalSheetId == null || - dn.LocalSheetId == null) + dnLocalSheetId == null) ).First(); existingDefinedName.Text = dn.Text; existingDefinedName.LocalSheetId = dn.LocalSheetId; @@ -658,8 +647,7 @@ runProperties.Append(fontName); runProperties.Append(fontFamilyNumbering); - var text = new Text(); - text.Text = rt.Text; + var text = new Text {Text = rt.Text}; if (rt.Text.StartsWith(" ") || rt.Text.EndsWith(" ") || rt.Text.Contains(Environment.NewLine)) text.Space = SpaceProcessingModeValues.Preserve; @@ -680,8 +668,7 @@ EndingBaseIndex = (UInt32)p.End }; - var text = new Text(); - text.Text = p.Text; + var text = new Text {Text = p.Text}; phoneticRun.Append(text); sharedStringItem.Append(phoneticRun); @@ -722,8 +709,7 @@ { String s = c.Value.ToString(); var sharedStringItem = new SharedStringItem(); - var text = new Text(); - text.Text = s; + var text = new Text {Text = s}; if (s.StartsWith(" ") || s.EndsWith(" ")) text.Space = SpaceProcessingModeValues.Preserve; sharedStringItem.Append(text); @@ -1442,27 +1428,30 @@ }; if (p.Type == XLCustomPropertyType.Text) { - var vTLPWSTR1 = new VTLPWSTR(); - vTLPWSTR1.Text = p.GetValue(); - customDocumentProperty.AppendChild(vTLPWSTR1); + var vTlpwstr1 = new VTLPWSTR {Text = p.GetValue()}; + customDocumentProperty.AppendChild(vTlpwstr1); } else if (p.Type == XLCustomPropertyType.Date) { - var vTFileTime1 = new VTFileTime(); - vTFileTime1.Text = - p.GetValue().ToUniversalTime().ToString("yyyy'-'MM'-'dd'T'HH':'mm':'ss'Z'"); + var vTFileTime1 = new VTFileTime + { + Text = + p.GetValue().ToUniversalTime().ToString( + "yyyy'-'MM'-'dd'T'HH':'mm':'ss'Z'") + }; customDocumentProperty.AppendChild(vTFileTime1); } else if (p.Type == XLCustomPropertyType.Number) { - var vTDouble1 = new VTDouble(); - vTDouble1.Text = p.GetValue().ToString(CultureInfo.InvariantCulture); + var vTDouble1 = new VTDouble + { + Text = p.GetValue().ToString(CultureInfo.InvariantCulture) + }; customDocumentProperty.AppendChild(vTDouble1); } else { - var vTBool1 = new VTBool(); - vTBool1.Text = p.GetValue().ToString().ToLower(); + var vTBool1 = new VTBool {Text = p.GetValue().ToString().ToLower()}; customDocumentProperty.AppendChild(vTBool1); } properties2.AppendChild(customDocumentProperty); @@ -1511,8 +1500,7 @@ SaveContext context) { context.TableId++; - string reference; - reference = xlTable.RangeAddress.FirstAddress + ":" + xlTable.RangeAddress.LastAddress; + string reference = xlTable.RangeAddress.FirstAddress + ":" + xlTable.RangeAddress.LastAddress; String tableName = GetTableName(xlTable.Name, context); var table = new Table { @@ -1595,15 +1583,20 @@ if (!context.SharedFonts.ContainsKey(defaultStyle.Font)) context.SharedFonts.Add(defaultStyle.Font, new FontInfo {FontId = 0, Font = defaultStyle.Font}); - var sharedFills = new Dictionary(); - sharedFills.Add(defaultStyle.Fill, new FillInfo {FillId = 2, Fill = defaultStyle.Fill}); + var sharedFills = new Dictionary + {{defaultStyle.Fill, new FillInfo {FillId = 2, Fill = defaultStyle.Fill}}}; - var sharedBorders = new Dictionary(); - sharedBorders.Add(defaultStyle.Border, new BorderInfo {BorderId = 0, Border = defaultStyle.Border}); + var sharedBorders = new Dictionary + {{defaultStyle.Border, new BorderInfo {BorderId = 0, Border = defaultStyle.Border}}}; - var sharedNumberFormats = new Dictionary(); - sharedNumberFormats.Add(defaultStyle.NumberFormat, - new NumberFormatInfo {NumberFormatId = 0, NumberFormat = defaultStyle.NumberFormat}); + var sharedNumberFormats = new Dictionary + { + { + defaultStyle.NumberFormat, + new NumberFormatInfo + {NumberFormatId = 0, NumberFormat = defaultStyle.NumberFormat} + } + }; //Dictionary sharedAlignments = new Dictionary(); //sharedAlignments.Add(defaultStyle.Alignment.ToString(), new AlignmentInfo() { AlignmentId = 0, Alignment = defaultStyle.Alignment }); @@ -1706,7 +1699,9 @@ { if (!context.SharedStyles.ContainsKey(xlStyle)) { - int numberFormatId = xlStyle.NumberFormat.NumberFormatId >= 0 ? xlStyle.NumberFormat.NumberFormatId : allSharedNumberFormats[xlStyle.NumberFormat].NumberFormatId; + int numberFormatId = xlStyle.NumberFormat.NumberFormatId >= 0 + ? xlStyle.NumberFormat.NumberFormatId + : allSharedNumberFormats[xlStyle.NumberFormat].NumberFormatId; context.SharedStyles.Add(xlStyle, new StyleInfo @@ -1721,7 +1716,7 @@ } } - var allCellStyleFormats = ResolveCellStyleFormats(workbookStylesPart, context); + ResolveCellStyleFormats(workbookStylesPart, context); ResolveRest(workbookStylesPart, context); if (!workbookStylesPart.Stylesheet.CellStyles.Elements().Any(c => c.Name == "Normal")) @@ -1756,31 +1751,18 @@ //workbookStylesPart.Stylesheet.AppendChild(tableStyles1); } - private void ResolveRest(WorkbookStylesPart workbookStylesPart, SaveContext context) + private static void ResolveRest(WorkbookStylesPart workbookStylesPart, SaveContext context) { if (workbookStylesPart.Stylesheet.CellFormats == null) workbookStylesPart.Stylesheet.CellFormats = new CellFormats(); foreach (StyleInfo styleInfo in context.SharedStyles.Values) { - Int32 styleId = 0; - Boolean foundOne = false; - foreach (CellFormat f in workbookStylesPart.Stylesheet.CellFormats) - { - if (CellFormatsAreEqual(f, styleInfo)) - { - foundOne = true; - break; - } - styleId++; - } + var info = styleInfo; + Boolean foundOne = + workbookStylesPart.Stylesheet.CellFormats.Cast().Any(f => CellFormatsAreEqual(f, info)); if (!foundOne) { - //StyleInfo info = styleInfo; - //styleId += workbookStylesPart.Stylesheet.CellStyleFormats - // .Cast() - // .TakeWhile(f => !CellFormatsAreEqual(f, info)).Count(); - var cellFormat = GetCellFormat(styleInfo); cellFormat.FormatId = 0; var alignment = new Alignment @@ -1806,29 +1788,20 @@ workbookStylesPart.Stylesheet.CellFormats.Count = (UInt32)workbookStylesPart.Stylesheet.CellFormats.Count(); } - private Dictionary ResolveCellStyleFormats(WorkbookStylesPart workbookStylesPart, - SaveContext context) + private static void ResolveCellStyleFormats(WorkbookStylesPart workbookStylesPart, + SaveContext context) { if (workbookStylesPart.Stylesheet.CellStyleFormats == null) workbookStylesPart.Stylesheet.CellStyleFormats = new CellStyleFormats(); - var allSharedStyles = new Dictionary(); foreach (StyleInfo styleInfo in context.SharedStyles.Values) { - Int32 styleId = 0; - Boolean foundOne = false; - foreach (CellFormat f in workbookStylesPart.Stylesheet.CellStyleFormats) - { - if (CellFormatsAreEqual(f, styleInfo)) - { - foundOne = true; - break; - } - styleId++; - } + var info = styleInfo; + Boolean foundOne = + workbookStylesPart.Stylesheet.CellStyleFormats.Cast().Any( + f => CellFormatsAreEqual(f, info)); if (!foundOne) { - //CellFormat cellStyleFormat = new CellFormat() { NumberFormatId = (UInt32)styleInfo.NumberFormatId, FontId = (UInt32)styleInfo.FontId, FillId = (UInt32)styleInfo.FillId, BorderId = (UInt32)styleInfo.BorderId, ApplyNumberFormat = false, ApplyFill = ApplyFill(styleInfo), ApplyBorder = ApplyBorder(styleInfo), ApplyAlignment = false, ApplyProtection = false }; var cellStyleFormat = GetCellFormat(styleInfo); if (cellStyleFormat.ApplyProtection.Value) @@ -1836,12 +1809,9 @@ workbookStylesPart.Stylesheet.CellStyleFormats.AppendChild(cellStyleFormat); } - allSharedStyles.Add(styleInfo.Style, new StyleInfo {Style = styleInfo.Style, StyleId = (UInt32)styleId}); } workbookStylesPart.Stylesheet.CellStyleFormats.Count = (UInt32)workbookStylesPart.Stylesheet.CellStyleFormats.Count(); - - return allSharedStyles; } private static bool ApplyFill(StyleInfo styleInfo) @@ -2271,9 +2241,11 @@ { var nf = new XLFont {Bold = f.Bold != null, Italic = f.Italic != null}; if (f.Underline != null) + { nf.Underline = f.Underline.Val != null ? f.Underline.Val.Value.ToClosedXml() : XLFontUnderlineValues.Single; + } nf.Strikethrough = f.Strike != null; if (f.VerticalTextAlignment != null) { @@ -2354,8 +2326,8 @@ #region GenerateWorksheetPartContent - private void GenerateWorksheetPartContent(WorksheetPart worksheetPart, XLWorksheet xlWorksheet, - SaveContext context) + private static void GenerateWorksheetPartContent(WorksheetPart worksheetPart, XLWorksheet xlWorksheet, + SaveContext context) { #region Worksheet @@ -2928,25 +2900,25 @@ #endregion #region MergeCells - MergeCells mergeCells = null; - if ((xlWorksheet as XLWorksheet).Internals.MergedRanges.Any()) + + if ((xlWorksheet).Internals.MergedRanges.Any()) { if (!worksheetPart.Worksheet.Elements().Any()) { - OpenXmlElement previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.MergeCells); + var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.MergeCells); worksheetPart.Worksheet.InsertAfter(new MergeCells(), previousElement); } - mergeCells = worksheetPart.Worksheet.Elements().First(); + var mergeCells = worksheetPart.Worksheet.Elements().First(); cm.SetElement(XLWSContentManager.XLWSContents.MergeCells, mergeCells); mergeCells.RemoveAllChildren(); foreach ( - var merged in - (xlWorksheet as XLWorksheet).Internals.MergedRanges.Select( - m => m.RangeAddress.FirstAddress.ToString() + ":" + m.RangeAddress.LastAddress.ToString())) + string merged in + (xlWorksheet).Internals.MergedRanges.Select( + m => m.RangeAddress.FirstAddress.ToString() + ":" + m.RangeAddress.LastAddress.ToString())) { - MergeCell mergeCell = new MergeCell() { Reference = merged }; + var mergeCell = new MergeCell {Reference = merged}; mergeCells.AppendChild(mergeCell); } @@ -2957,6 +2929,7 @@ worksheetPart.Worksheet.RemoveAllChildren(); cm.SetElement(XLWSContentManager.XLWSContents.MergeCells, null); } + #endregion #region DataValidations @@ -3330,7 +3303,7 @@ private static double GetColumnWidth(double columnWidth) { if (columnWidth > 0) - return columnWidth + COLUMN_WIDTH_OFFSET; + return columnWidth + ColumnWidthOffset; return columnWidth; }