diff --git a/ClosedXML/ClosedXML/..svnbridge/.svnbridge b/ClosedXML/ClosedXML/..svnbridge/.svnbridge index 3f623dd..42e94b8 100644 --- a/ClosedXML/ClosedXML/..svnbridge/.svnbridge +++ b/ClosedXML/ClosedXML/..svnbridge/.svnbridge @@ -1,2 +1,5 @@ svn:ignore*.suo +bugtraq:numbertruesvn:ignore*.suo +TestResults +*.user \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj index 2248b07..0bd653e 100644 --- a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj @@ -117,6 +117,9 @@ + + + @@ -134,6 +137,7 @@ + diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index 88cc20f..feed098 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -134,15 +134,14 @@ { if (!StringExtensions.IsNullOrWhiteSpace(FormulaA1)) return (T)Convert.ChangeType(String.Empty, typeof(T)); - else if (Value is TimeSpan) + if (Value is TimeSpan) if (typeof(T) == typeof(String)) return (T)Convert.ChangeType(Value.ToString(), typeof(T)); else return (T)Value; - else if (Value is IXLRichText) + if (Value is IXLRichText) return (T)RichText; - else - return (T)Convert.ChangeType(Value, typeof(T)); + return (T)Convert.ChangeType(Value, typeof(T)); } public String GetString() { @@ -268,7 +267,7 @@ } if (m_worksheet.Internals.Workbook.WorksheetsInternal.Any(w => w.Name.ToLower().Equals(sName.ToLower())) - && XLAddress.IsValidA1Address(cAddress) + && ExcelHelper.IsValidA1Address(cAddress) ) { return m_worksheet.Internals.Workbook.Worksheet(sName).Cell(cAddress).Value; @@ -353,7 +352,7 @@ Int32 maxColumns; if (asRange is XLRow || asRange is XLColumn) { - var lastCellUsed = asRange.LastCellUsed(); + var lastCellUsed = asRange.LastCellUsed(true); maxRows = lastCellUsed.Address.RowNumber; maxColumns = lastCellUsed.Address.ColumnNumber; //if (asRange is XLRow) @@ -384,23 +383,20 @@ if (asRange.Contains(mergedRange)) { var initialRo = Address.RowNumber + - (mergedRange.RangeAddress.FirstAddress.RowNumber - asRange.RangeAddress.FirstAddress.RowNumber); + (mergedRange.FirstAddress.RowNumber - asRange.RangeAddress.FirstAddress.RowNumber); var initialCo = Address.ColumnNumber + - (mergedRange.RangeAddress.FirstAddress.ColumnNumber - asRange.RangeAddress.FirstAddress.ColumnNumber); + (mergedRange.FirstAddress.ColumnNumber - asRange.RangeAddress.FirstAddress.ColumnNumber); rangesToMerge.Add(m_worksheet.Range(initialRo, initialCo, - initialRo + mergedRange.RowCount() - 1, - initialCo + mergedRange.ColumnCount() - 1)); + initialRo + mergedRange.RowCount - 1, + initialCo + mergedRange.ColumnCount - 1)); } } rangesToMerge.ForEach(r => r.Merge()); return true; } - else - { - return false; - } + return false; } private Boolean SetEnumerable(Object collectionObject) @@ -646,18 +642,17 @@ Address.RowNumber + ro - 1, Address.ColumnNumber + maxCo - 1); } - else - { - return null; - } + return null; } private void ClearMerged(Int32 rowCount, Int32 columnCount) { - List mergeToDelete = new List(); + var point = Address.GetSheetPoint(); + var range = new SheetRange(point, point); + var mergeToDelete = new List(); foreach (var merge in m_worksheet.Internals.MergedRanges) { - if (merge.Intersects(AsRange())) + if (merge.Intersects(range)) { mergeToDelete.Add(merge); } @@ -1178,7 +1173,7 @@ String columnToReturn; if (columnPart == "C") { - columnToReturn = XLAddress.GetColumnLetterFromNumber(Address.ColumnNumber + columnsToShift); + columnToReturn = ExcelHelper.GetColumnLetterFromNumber(Address.ColumnNumber + columnsToShift); } else { @@ -1186,19 +1181,19 @@ var mIndex = columnPart.IndexOf("-"); if (bIndex >= 0) { - columnToReturn = XLAddress.GetColumnLetterFromNumber( + columnToReturn = ExcelHelper.GetColumnLetterFromNumber( Address.ColumnNumber + Int32.Parse(columnPart.Substring(bIndex + 1, columnPart.Length - bIndex - 2)) + columnsToShift ); } else if (mIndex >= 0) { - columnToReturn = XLAddress.GetColumnLetterFromNumber( + columnToReturn = ExcelHelper.GetColumnLetterFromNumber( Address.ColumnNumber + Int32.Parse(columnPart.Substring(mIndex)) + columnsToShift ); } else { - columnToReturn = "$" + XLAddress.GetColumnLetterFromNumber(Int32.Parse(columnPart.Substring(1)) + columnsToShift); + columnToReturn = "$" + ExcelHelper.GetColumnLetterFromNumber(Int32.Parse(columnPart.Substring(1)) + columnsToShift); } } return columnToReturn; @@ -1244,8 +1239,8 @@ } else { - var column1 = XLAddress.GetColumnNumberFromLetter(p1.Replace("$", "")); - var column2 = XLAddress.GetColumnNumberFromLetter(p2.Replace("$", "")); + var column1 = ExcelHelper.GetColumnNumberFromLetter(p1.Replace("$", "")); + var column2 = ExcelHelper.GetColumnNumberFromLetter(p2.Replace("$", "")); var leftPart = GetR1C1Column(column1, p1.Contains('$'), columnsToShift); var rightPart = GetR1C1Column(column2, p2.Contains('$'), columnsToShift); return leftPart + ":" + rightPart; @@ -1596,13 +1591,13 @@ if (column1String[0] == '$') { column1 = "$" + - XLAddress.GetColumnLetterFromNumber( - XLAddress.GetColumnNumberFromLetter(column1String.Substring(1)) + columnsShifted); + ExcelHelper.GetColumnLetterFromNumber( + ExcelHelper.GetColumnNumberFromLetter(column1String.Substring(1)) + columnsShifted); } else { column1 = - XLAddress.GetColumnLetterFromNumber(XLAddress.GetColumnNumberFromLetter(column1String) + + ExcelHelper.GetColumnLetterFromNumber(ExcelHelper.GetColumnNumberFromLetter(column1String) + columnsShifted); } @@ -1610,13 +1605,13 @@ if (column2String[0] == '$') { column2 = "$" + - XLAddress.GetColumnLetterFromNumber( - XLAddress.GetColumnNumberFromLetter(column2String.Substring(1)) + columnsShifted); + ExcelHelper.GetColumnLetterFromNumber( + ExcelHelper.GetColumnNumberFromLetter(column2String.Substring(1)) + columnsShifted); } else { column2 = - XLAddress.GetColumnLetterFromNumber(XLAddress.GetColumnNumberFromLetter(column2String) + + ExcelHelper.GetColumnLetterFromNumber(ExcelHelper.GetColumnNumberFromLetter(column2String) + columnsShifted); } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinate/..svnbridge/.svnbridge b/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinate/..svnbridge/.svnbridge new file mode 100644 index 0000000..88585f2 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinate/..svnbridge/.svnbridge @@ -0,0 +1 @@ +bugtraq:numbertrue \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinate/SheetPoint.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinate/SheetPoint.cs new file mode 100644 index 0000000..4b0d1b7 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinate/SheetPoint.cs @@ -0,0 +1,99 @@ +using System; +using System.Diagnostics; + +namespace ClosedXML.Excel +{ + /// + /// Lightweight struct for work with sheet coordinates + /// + public struct SheetPoint : IEquatable + { + #region Static + /// + /// Singleton instance + /// +// ReSharper disable RedundantDefaultFieldInitializer + public static readonly SheetPoint Empty = new SheetPoint(); +// ReSharper restore RedundantDefaultFieldInitializer + #endregion + #region Private fields + [DebuggerBrowsable(DebuggerBrowsableState.Never)] + private readonly int m_row; + [DebuggerBrowsable(DebuggerBrowsableState.Never)] + private readonly int m_column; + #endregion + #region Constructor + public SheetPoint(int row, int column) + { + #region Check + if (row < 0) + { + throw new ArgumentOutOfRangeException("row", "Must be more than 0"); + } + if (column < 0) + { + throw new ArgumentOutOfRangeException("column", "Must be more than 0"); + } + #endregion + m_row = row; + m_column = column; + } + #endregion + #region Public properties + public int RowNumber + { + [DebuggerStepThrough] + get { return m_row; } + } + public int ColumnNumber + { + [DebuggerStepThrough] + get { return m_column; } + } + #endregion + #region Public methods + public bool Equals(SheetPoint other) + { + return other.m_row == m_row && other.m_column == m_column; + } + public override bool Equals(object obj) + { + if (ReferenceEquals(null, obj)) + { + return false; + } + if (obj.GetType() != typeof (SheetPoint)) + { + return false; + } + return Equals((SheetPoint) obj); + } + public override int GetHashCode() + { + unchecked + { + return (m_row*397) ^ m_column; + } + } + public override string ToString() + { + return ToStringA1(); + } + public string ToStringA1() + { + return string.Format("{0}{1}", ExcelHelper.GetColumnLetterFromNumber(m_column), m_row); + } + #endregion + #region Internal methods + internal bool Equals(XLAddress other) + { + if (ReferenceEquals(other,null)) + { + return false; + } + return m_row == other.RowNumber && m_column == other.ColumnNumber; + } + #endregion + } + +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinate/SheetRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinate/SheetRange.cs new file mode 100644 index 0000000..fd76fc8 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinate/SheetRange.cs @@ -0,0 +1,147 @@ +using System; +using System.Diagnostics; + +namespace ClosedXML.Excel +{ + /// + /// Lightweight struct for work with sheet coordinate range + /// + public struct SheetRange : IEquatable + { + #region Static + /// + /// Singleton instance + /// +// ReSharper disable RedundantDefaultFieldInitializer + public static readonly SheetRange Empty = new SheetRange(); +// ReSharper restore RedundantDefaultFieldInitializer + #endregion + #region Private fields + [DebuggerBrowsable(DebuggerBrowsableState.Never)] + private readonly SheetPoint m_begin; + [DebuggerBrowsable(DebuggerBrowsableState.Never)] + private readonly SheetPoint m_end; + #endregion + #region Constructor + public SheetRange(int beginRow, int beginColumn, int endRow, int endColumn) + : this(new SheetPoint(beginRow, beginColumn), new SheetPoint(endRow, endColumn)) + { + } + public SheetRange(SheetPoint begin, SheetPoint end) + { + #region Check + if (begin.RowNumber > end.RowNumber) + { + throw new ArgumentOutOfRangeException("begin", "Row part of begin coordinate must be less or equal Row part coordinate of end"); + } + if (begin.ColumnNumber > end.ColumnNumber) + { + throw new ArgumentOutOfRangeException("begin", "Column part of begin coordinate must be less or equal Column part coordinate of end"); + } + #endregion + m_begin = begin; + m_end = end; + } + #endregion + #region Public properties + public SheetPoint FirstAddress + { + [DebuggerStepThrough] + get { return m_begin; } + } + public SheetPoint LastAddress + { + [DebuggerStepThrough] + get { return m_end; } + } + + public bool IsOneCell + { + get { return m_begin.Equals(m_end); } + } + public int RowCount + { + [DebuggerStepThrough] + get { return m_end.RowNumber - m_begin.RowNumber + 1; } + } + public int ColumnCount + { + [DebuggerStepThrough] + get { return m_end.ColumnNumber - m_begin.ColumnNumber + 1; } + } + public int Count + { + [DebuggerStepThrough] + get { return RowCount*ColumnCount; } + } + #endregion + #region Public methods + public bool Equals(SheetRange other) + { + return other.m_begin.Equals(m_begin) && other.m_end.Equals(m_end); + } + public bool Intersects(SheetRange range) + { + return !(range.FirstAddress.ColumnNumber > LastAddress.ColumnNumber + || range.LastAddress.ColumnNumber < FirstAddress.ColumnNumber + || range.FirstAddress.RowNumber > LastAddress.RowNumber + || range.LastAddress.RowNumber < FirstAddress.RowNumber + ); + } + + public override bool Equals(object obj) + { + if (ReferenceEquals(null, obj)) + { + return false; + } + if (obj.GetType() != typeof (SheetRange)) + { + return false; + } + return Equals((SheetRange) obj); + } + public override int GetHashCode() + { + unchecked + { + return (m_begin.GetHashCode()*397) ^ m_end.GetHashCode(); + } + } + internal string ToStringA1() + { + return IsOneCell ? m_begin.ToStringA1() : string.Format("{0}:{1}", m_begin.ToStringA1(), m_end.ToStringA1()); + } + public override string ToString() + { + return ToStringA1(); + } + #endregion + #region Internal methods + internal bool Equals(XLRangeAddress other) + { + if (ReferenceEquals(other, null)) + { + return false; + } + return m_begin.Equals(other.FirstAddress) && m_end.Equals(other.LastAddress); + } + + internal bool Intersects(XLRangeAddress range) + { + if (ReferenceEquals(range, null)) + { + return false; + } + return !(range.FirstAddress.ColumnNumber > LastAddress.ColumnNumber + || range.LastAddress.ColumnNumber < FirstAddress.ColumnNumber + || range.FirstAddress.RowNumber > LastAddress.RowNumber + || range.LastAddress.RowNumber < FirstAddress.RowNumber + ); + } + + #endregion + + + } +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheetInternals.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheetInternals.cs index 82a3e60..dedb188 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheetInternals.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheetInternals.cs @@ -6,7 +6,7 @@ XLCellCollection CellsCollection { get; } XLColumnsCollection ColumnsCollection { get; } XLRowsCollection RowsCollection { get; } - XLRanges MergedRanges { get; } + XLMergedRanges MergedRanges { get; } XLWorkbook Workbook { get; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/XLPageSetup.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/XLPageSetup.cs index 3c9b883..d9f9c81 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/XLPageSetup.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/XLPageSetup.cs @@ -90,7 +90,7 @@ } public void SetColumnsToRepeatAtLeft(String firstColumnToRepeatAtLeft, String lastColumnToRepeatAtLeft) { - SetColumnsToRepeatAtLeft(XLAddress.GetColumnNumberFromLetter(firstColumnToRepeatAtLeft), XLAddress.GetColumnNumberFromLetter(lastColumnToRepeatAtLeft)); + SetColumnsToRepeatAtLeft(ExcelHelper.GetColumnNumberFromLetter(firstColumnToRepeatAtLeft), ExcelHelper.GetColumnNumberFromLetter(lastColumnToRepeatAtLeft)); } public void SetColumnsToRepeatAtLeft(Int32 firstColumnToRepeatAtLeft, Int32 lastColumnToRepeatAtLeft) { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs index b7773ad..614161c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -204,5 +204,6 @@ //IXLChart CreateChart(Int32 firstRow, Int32 firstColumn, Int32 lastRow, Int32 lastColumn); + bool Intersects(SheetRange range); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/Sort/XLSortElements.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/Sort/XLSortElements.cs index a279032..59a698a 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/Sort/XLSortElements.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/Sort/XLSortElements.cs @@ -45,7 +45,7 @@ { elements.Add(new XLSortElement() { - ElementNumber = XLAddress.GetColumnNumberFromLetter(elementNumber), + ElementNumber = ExcelHelper.GetColumnNumberFromLetter(elementNumber), SortOrder = sortOrder, IgnoreBlanks = ignoreBlanks, MatchCase = matchCase diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs index 690675f..1927e1a 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs @@ -219,7 +219,7 @@ } public IXLRangeColumn Column(String column) { - return Column(XLAddress.GetColumnNumberFromLetter(column)); + return Column(ExcelHelper.GetColumnNumberFromLetter(column)); } public XLRangeColumn ColumnQuick(Int32 column) { @@ -258,7 +258,7 @@ } public IXLRangeColumns Columns(String firstColumn, String lastColumn) { - return Columns(XLAddress.GetColumnNumberFromLetter(firstColumn), XLAddress.GetColumnNumberFromLetter(lastColumn)); + return Columns(ExcelHelper.GetColumnNumberFromLetter(firstColumn), ExcelHelper.GetColumnNumberFromLetter(lastColumn)); } public IXLRangeColumns Columns(String columns) { @@ -489,23 +489,26 @@ private void TransposeMerged(Int32 squareSide) { - XLRange rngToTranspose = (XLRange) Worksheet.Range( + + var rngToTranspose = new SheetRange( RangeAddress.FirstAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber, RangeAddress.FirstAddress.RowNumber + squareSide - 1, RangeAddress.FirstAddress.ColumnNumber + squareSide - 1); - List mergeToDelete = new List(); - List mergeToInsert = new List(); + var mranges = new List(); foreach (var merge in (Worksheet).Internals.MergedRanges) { if (Contains(merge)) { - merge.RangeAddress.LastAddress = rngToTranspose.Cell(merge.ColumnCount(), merge.RowCount()).Address; + mranges.Add(new SheetRange(merge.FirstAddress, + new SheetPoint(rngToTranspose.FirstAddress.RowNumber + merge.ColumnCount, + rngToTranspose.FirstAddress.ColumnNumber + merge.RowCount))); + } } - mergeToDelete.ForEach(m => (Worksheet).Internals.MergedRanges.Remove(m)); - mergeToInsert.ForEach(m => (Worksheet).Internals.MergedRanges.Add(m)); + mranges.ForEach(m => Worksheet.Internals.MergedRanges.Remove(m)); + mranges.ForEach(m => Worksheet.Internals.MergedRanges.Add(m)); } private void MoveOrClearForTranspose(XLTransposeOptions transposeOption, int rowCount, int columnCount) @@ -668,7 +671,7 @@ Int32 co; if (!Int32.TryParse(coString, out co)) { - co = XLAddress.GetColumnNumberFromLetter(coString); + co = ExcelHelper.GetColumnNumberFromLetter(coString); } if (order.ToUpper().Equals("ASC")) @@ -707,7 +710,7 @@ Int32 co; if (!Int32.TryParse(coString, out co)) { - co = XLAddress.GetColumnNumberFromLetter(coString); + co = ExcelHelper.GetColumnNumberFromLetter(coString); } if (order.ToUpper().Equals("ASC")) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs index 2013fb4..3af9776 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs @@ -106,6 +106,11 @@ public bool IsInvalid { get; set; } #endregion #region Public methods + public SheetRange GetSheetRange() + { + return new SheetRange(m_firstAddress.GetSheetPoint(), m_lastAddress.GetSheetPoint()); + } + public override string ToString() { return m_firstAddress + ":" + m_lastAddress; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index f58194e..1eb2167 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -344,7 +344,7 @@ Int32 test; if (Int32.TryParse(address, out test)) { - return XLAddress.GetColumnLetterFromNumber(test) + "1"; + return ExcelHelper.GetColumnLetterFromNumber(test) + "1"; } return address; } @@ -377,12 +377,11 @@ public IXLRange Merge() { - var tAddress = RangeAddress.ToString(); Boolean foundOne = false; foreach (var m in (Worksheet).Internals.MergedRanges) { - var mAddress = m.RangeAddress.ToString(); - if (mAddress == tAddress) + + if (m.Equals(RangeAddress)) { foundOne = true; break; @@ -391,26 +390,29 @@ if (!foundOne) { - (Worksheet).Internals.MergedRanges.Add(AsRange()); + Worksheet.Internals.MergedRanges.Add(GetSheetRange()); } return AsRange(); } + public IXLRange Unmerge() { - var tAddress = RangeAddress.ToString(); foreach (var m in (Worksheet).Internals.MergedRanges) { - var mAddress = m.RangeAddress.ToString(); - if (mAddress == tAddress) + if (m.Equals(RangeAddress)) { - (Worksheet).Internals.MergedRanges.Remove(AsRange()); + Worksheet.Internals.MergedRanges.Remove(m); break; } } - return AsRange(); } + public virtual SheetRange GetSheetRange() + { + return RangeAddress.GetSheetRange(); + } + public IXLRangeColumns InsertColumnsAfter(Int32 numberOfColumns) { return InsertColumnsAfter(numberOfColumns, true); @@ -778,7 +780,7 @@ private void ClearMerged() { - List mergeToDelete = new List(); + var mergeToDelete = new List(); foreach (var merge in (Worksheet).Internals.MergedRanges) { if (Intersects(merge)) @@ -822,23 +824,39 @@ return Contains((XLAddress) range.RangeAddress.FirstAddress, (XLAddress) range.RangeAddress.LastAddress); } - public Boolean Contains(XLAddress first, XLAddress last) + public bool Contains(XLAddress first, XLAddress last) { return Contains(first) && Contains(last); } - public Boolean Contains(XLAddress address) + 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 Boolean Contains(SheetRange range) + { + return Contains(range.FirstAddress, range.LastAddress); + } + + public bool Contains(SheetPoint first, SheetPoint last) + { + return Contains(first) && Contains(last); + } + + public bool Contains(SheetPoint point) + { + return RangeAddress.FirstAddress.RowNumber <= point.RowNumber && point.RowNumber <= RangeAddress.LastAddress.RowNumber && + RangeAddress.FirstAddress.ColumnNumber <= point.ColumnNumber && point.ColumnNumber <= RangeAddress.LastAddress.ColumnNumber; + } + public Boolean Intersects(String rangeAddress) { return Intersects(Range(rangeAddress)); } - public Boolean Intersects(IXLRangeBase range) + public bool Intersects(IXLRangeBase range) { if (range.RangeAddress.IsInvalid || RangeAddress.IsInvalid) { @@ -855,6 +873,22 @@ ); } + public bool Intersects(SheetRange range) + { + if (RangeAddress.IsInvalid) + { + return false; + } + var ra = RangeAddress; + + return !( // See if the two ranges intersect... + range.FirstAddress.ColumnNumber > ra.LastAddress.ColumnNumber + || range.LastAddress.ColumnNumber < ra.FirstAddress.ColumnNumber + || range.FirstAddress.RowNumber > ra.LastAddress.RowNumber + || range.LastAddress.RowNumber < ra.FirstAddress.RowNumber + ); + } + public void Delete(XLShiftDeletedCells shiftDeleteCells) { var numberOfRows = RowCount(); @@ -932,7 +966,7 @@ cellsToDelete.ForEach(c => (Worksheet).Internals.CellsCollection.Remove(c)); cellsToInsert.ForEach(c => (Worksheet).Internals.CellsCollection.Add(c.Key, c.Value)); - List mergesToRemove = new List(); + var mergesToRemove = new List(); foreach (var merge in (Worksheet).Internals.MergedRanges) { if (Contains(merge)) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs index cc9cd74..02444a7 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs @@ -111,8 +111,8 @@ public IXLCells Cells(String firstColumn, String lastColumn) { - return Cells(XLAddress.GetColumnNumberFromLetter(firstColumn) + ":" - + XLAddress.GetColumnNumberFromLetter(lastColumn)); + return Cells(ExcelHelper.GetColumnNumberFromLetter(firstColumn) + ":" + + ExcelHelper.GetColumnNumberFromLetter(lastColumn)); } public Int32 CellCount() diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs index 3690bcd..5141e0b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs @@ -201,8 +201,8 @@ public IXLCells Cells(String firstColumn, String lastColumn) { - return Cells(XLAddress.GetColumnNumberFromLetter(firstColumn) + ":" - + XLAddress.GetColumnNumberFromLetter(lastColumn)); + return Cells(ExcelHelper.GetColumnNumberFromLetter(firstColumn) + ":" + + ExcelHelper.GetColumnNumberFromLetter(lastColumn)); } public IXLRow AdjustToContents() { @@ -230,24 +230,14 @@ Double rowMaxHeight = minHeight; foreach (var cell in Row(startColumn, endColumn).CellsUsed()) { - var c = cell as XLCell; - Boolean isMerged = false; - var cellAsRange = c.AsRange(); - foreach (var m in Worksheet.Internals.MergedRanges) - { - if (cellAsRange.Intersects(m)) - { - isMerged = true; - break; - } - } - if (!isMerged) + var c = (XLCell) cell; + if (!Worksheet.Internals.MergedRanges.Intersects(c.Address)) { Double thisHeight; Int32 textRotation = c.Style.Alignment.TextRotation; if (c.HasRichText || textRotation != 0 || c.InnerText.Contains(Environment.NewLine)) { - List> kpList = new List>(); + var kpList = new List>(); if (c.HasRichText) { foreach (var rt in c.RichText) @@ -688,13 +678,13 @@ } public IXLRow CopyTo(IXLRow row) { - var thisRangeUsed = RangeUsed(); + var thisRangeUsed = RangeUsed(true); - Int32 thisColumnCount = ReferenceEquals(thisRangeUsed, null) ? 0 : thisRangeUsed.ColumnCount(); + int thisColumnCount = ReferenceEquals(thisRangeUsed, null) ? 0 : thisRangeUsed.ColumnCount(); //var targetRangeUsed = column target.AsRange().RangeUsed(); var lastCellUsed = row.LastCellUsed(true); - Int32 targetColumnCount = ReferenceEquals(lastCellUsed, null) ? 0 : row.LastCellUsed(true).Address.ColumnNumber; - Int32 maxColumn = thisColumnCount > targetColumnCount ? thisColumnCount : targetColumnCount; + int targetColumnCount = ReferenceEquals(lastCellUsed, null) ? 0 : row.LastCellUsed(true).Address.ColumnNumber; + int maxColumn = thisColumnCount > targetColumnCount ? thisColumnCount : targetColumnCount; CopyToCell(Row(1, maxColumn), (XLCell) row.FirstCell()); var newRow = (XLRow) row; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs index fa736be..b205bfa 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs @@ -250,9 +250,9 @@ } public new IXLRangeColumn Column(String column) { - if (XLAddress.IsValidColumn(column)) + if (ExcelHelper.IsValidColumn(column)) { - Int32 coNum = XLAddress.GetColumnNumberFromLetter(column); + Int32 coNum = ExcelHelper.GetColumnNumberFromLetter(column); if (coNum > ColumnCount()) { return DataRange.Column(GetFieldIndex(column) + 1); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs index 0ad6184..54eb8f7 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs @@ -1,6 +1,5 @@ using System; using System.Diagnostics; -using System.Globalization; namespace ClosedXML.Excel { @@ -40,7 +39,7 @@ columnLetter = cellAddressString.Substring(startPos, rowPos); } - rowNumber = Int32.Parse(cellAddressString.Substring(rowPos + 1), ms_nfi); + rowNumber = int.Parse(cellAddressString.Substring(rowPos + 1), ExcelHelper.NumberFormatForParse); } else { @@ -53,184 +52,10 @@ columnLetter = cellAddressString.Substring(startPos, rowPos); } - rowNumber = Int32.Parse(cellAddressString.Substring(rowPos), ms_nfi); + rowNumber = Int32.Parse(cellAddressString.Substring(rowPos), ExcelHelper.NumberFormatForParse); } return new XLAddress(worksheet, rowNumber, columnLetter, fixedRow, fixedColumn); } - - private static readonly NumberFormatInfo ms_nfi = CultureInfo.InvariantCulture.NumberFormat; - private const Int32 TwoT26 = 26*26; - /// - /// Gets the column number of a given column letter. - /// - /// The column letter to translate into a column number. - public static Int32 GetColumnNumberFromLetter(String columnLetter) - { - if (columnLetter[0] <= '9') - { - return Int32.Parse(columnLetter, ms_nfi); - } - - columnLetter = columnLetter.ToUpper(); - var length = columnLetter.Length; - if (length == 1) - { - return Convert.ToByte(columnLetter[0]) - 64; - } - if (length == 2) - { - return - ((Convert.ToByte(columnLetter[0]) - 64)*26) + - (Convert.ToByte(columnLetter[1]) - 64); - } - if (length == 3) - { - return - ((Convert.ToByte(columnLetter[0]) - 64)*TwoT26) + - ((Convert.ToByte(columnLetter[1]) - 64)*26) + - (Convert.ToByte(columnLetter[2]) - 64); - } - throw new ApplicationException("Column Length must be between 1 and 3."); - } - - public static Boolean IsValidColumn(String column) - { - if (StringExtensions.IsNullOrWhiteSpace(column) || column.Length > 3) - { - return false; - } - - Boolean retVal = true; - String theColumn = column.ToUpper(); - for (Int32 i = 0; i < column.Length; i++) - { - if (theColumn[i] < 'A' || theColumn[i] > 'Z' || (i == 2 && theColumn[i] > 'D')) - { - retVal = false; - break; - } - } - return retVal; - } - - public static Boolean IsValidRow(String rowString) - { - Int32 row; - if (Int32.TryParse(rowString, out row)) - { - return row > 0 && row <= XLWorksheet.MaxNumberOfRows; - } - return false; - } - - public static Boolean IsValidA1Address(String address) - { - address = address.Replace("$", ""); - Int32 rowPos = 0; - Int32 addressLength = address.Length; - while (rowPos < addressLength && (address[rowPos] > '9' || address[rowPos] < '0')) - { - rowPos++; - } - - return - rowPos < addressLength - && IsValidRow(address.Substring(rowPos)) - && IsValidColumn(address.Substring(0, rowPos)); - } - - /// - /// Gets the column letter of a given column number. - /// - /// The column number to translate into a column letter. - public static String GetColumnLetterFromNumber(Int32 columnNumber) - { - String s = String.Empty; - for ( - Int32 i = Convert.ToInt32( - Math.Log( - Convert.ToDouble( - 25*( - Convert.ToDouble(columnNumber) - + 1 - ) - ) - )/Math.Log(26) - ) - 1 - ; i >= 0 - ; i-- - ) - { - Int32 x = Convert.ToInt32(Math.Pow(26, i + 1) - 1)/25 - 1; - if (columnNumber > x) - { - s += (Char) (((columnNumber - x - 1)/Convert.ToInt32(Math.Pow(26, i)))%26 + 65); - } - } - return s; - } - - public static Int32 GetRowFromAddress1(String cellAddressString) - { - Int32 rowPos = 1; - while (cellAddressString[rowPos] > '9') - { - rowPos++; - } - - return Int32.Parse(cellAddressString.Substring(rowPos), ms_nfi); - } - - public static Int32 GetColumnNumberFromAddress1(String cellAddressString) - { - Int32 rowPos = 0; - while (cellAddressString[rowPos] > '9') - { - rowPos++; - } - - return GetColumnNumberFromLetter(cellAddressString.Substring(0, rowPos)); - } - - public static Int32 GetRowFromAddress2(String cellAddressString) - { - Int32 rowPos = 1; - while (cellAddressString[rowPos] > '9') - { - rowPos++; - } - - if (cellAddressString[rowPos] == '$') - { - return Int32.Parse(cellAddressString.Substring(rowPos + 1), ms_nfi); - } - return Int32.Parse(cellAddressString.Substring(rowPos), ms_nfi); - } - - public static Int32 GetColumnNumberFromAddress2(String cellAddressString) - { - Int32 startPos; - if (cellAddressString[0] == '$') - { - startPos = 1; - } - else - { - startPos = 0; - } - - Int32 rowPos = startPos; - while (cellAddressString[rowPos] > '9') - { - rowPos++; - } - - if (cellAddressString[rowPos] == '$') - { - return GetColumnNumberFromLetter(cellAddressString.Substring(startPos, rowPos - 1)); - } - return GetColumnNumberFromLetter(cellAddressString.Substring(startPos, rowPos)); - } #endregion #region Private fields [DebuggerBrowsable(DebuggerBrowsableState.Never)] @@ -258,7 +83,7 @@ /// /// public XLAddress(XLWorksheet worksheet, int rowNumber, string columnLetter, bool fixedRow, bool fixedColumn) - : this(worksheet, rowNumber, GetColumnNumberFromLetter(columnLetter), fixedRow, fixedColumn) + : this(worksheet, rowNumber, ExcelHelper.GetColumnNumberFromLetter(columnLetter), fixedRow, fixedColumn) { m_columnLetter = columnLetter; } @@ -326,7 +151,7 @@ /// public String ColumnLetter { - get { return m_columnLetter ?? (m_columnLetter = GetColumnLetterFromNumber(m_columnNumber)); } + get { return m_columnLetter ?? (m_columnLetter = ExcelHelper.GetColumnLetterFromNumber(m_columnNumber)); } } #endregion #region Overrides @@ -377,6 +202,11 @@ { return "$" + ColumnLetter + "$" + m_rowNumber.ToStringLookup(); } + + public SheetPoint GetSheetPoint() + { + return new SheetPoint(m_rowNumber, m_columnNumber); + } #endregion #region Operator Overloads public static XLAddress operator +(XLAddress left, XLAddress right) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLMergedRanges.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLMergedRanges.cs new file mode 100644 index 0000000..ec79a02 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLMergedRanges.cs @@ -0,0 +1,120 @@ +using System; +using System.Collections; +using System.Collections.Generic; +using System.Diagnostics; + +namespace ClosedXML.Excel +{ + internal sealed class XLMergedRanges:IEnumerable + { + #region Private fields + private readonly SortedDictionary m_dict = new SortedDictionary(SheetPointComparer.Instance); + #endregion + #region Constructor + public XLMergedRanges() + { + } + private XLMergedRanges(XLMergedRanges original) + { + m_dict = new SortedDictionary(original.m_dict, SheetPointComparer.Instance); + } + #endregion + #region Public properties + public int Count + { + [DebuggerStepThrough] + get { return m_dict.Count; } + } + #endregion + #region Public methods + public void Add(SheetRange range) + { + #region Check + if (range.IsOneCell) + { + throw new ArgumentException("One cell can't be merged"); + } + #endregion + m_dict.Add(range.FirstAddress, range); + } + + public bool Remove(SheetPoint point) + { + return m_dict.Remove(point); + } + public bool Remove(SheetRange sheetRange) + { + return m_dict.Remove(sheetRange.FirstAddress); + } + + public bool Intersects(IXLAddress address) + { + var point = new SheetPoint(address.RowNumber, address.ColumnNumber); + return Intersects(new SheetRange(point, point)); + } + + public bool Intersects(SheetPoint point) + { + return Intersects(new SheetRange(point, point)); + } + + public bool Intersects(SheetRange range) + { + foreach (var mergedRange in m_dict.Values) + { + if (mergedRange.Intersects(range)) + { + return true; + } + } + return false; + } + + public void Clear() + { + m_dict.Clear(); + } + + public XLMergedRanges Clone() + { + return new XLMergedRanges(this); + } + #endregion + #region Implementation of IEnumerable + IEnumerator IEnumerable.GetEnumerator() + { + return GetEnumerator(); + } + #endregion + #region Implementation of IEnumerable + public IEnumerator GetEnumerator() + { + return m_dict.Values.GetEnumerator(); + } + #endregion + //-- + #region Nested type: SheetPointComparer + private sealed class SheetPointComparer : Comparer + { + /// + /// Singleton instance + /// + public static readonly SheetPointComparer Instance = new SheetPointComparer(); + #region Constructor + private SheetPointComparer() + { + } + #endregion + #region Public methods + public override int Compare(SheetPoint x, SheetPoint y) + { + return Math.Sign(x.RowNumber - y.RowNumber) * 2 + Math.Sign(x.ColumnNumber - y.ColumnNumber); + } + #endregion + + } + + #endregion + + } +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index 5c946d0..06901c8 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -35,6 +35,7 @@ using Underline = DocumentFormat.OpenXml.Spreadsheet.Underline; using Vt = DocumentFormat.OpenXml.VariantTypes; + namespace ClosedXML.Excel { public partial class XLWorkbook @@ -584,8 +585,8 @@ { var minColumn = worksheet.PageSetup.FirstColumnToRepeatAtLeft; var maxColumn = worksheet.PageSetup.LastColumnToRepeatAtLeft; - definedNameTextColumn = "'" + worksheet.Name + "'!" + XLAddress.GetColumnLetterFromNumber(minColumn) - + ":" + XLAddress.GetColumnLetterFromNumber(maxColumn); + definedNameTextColumn = "'" + worksheet.Name + "'!" + ExcelHelper.GetColumnLetterFromNumber(minColumn) + + ":" + ExcelHelper.GetColumnLetterFromNumber(maxColumn); } if (definedNameTextColumn.Length > 0) @@ -1783,25 +1784,25 @@ UInt32 maxRow = 0; String sheetDimensionReference = "A1"; - if ((xlWorksheet as XLWorksheet).Internals.CellsCollection.Count > 0) + if (xlWorksheet.Internals.CellsCollection.Count > 0) { - maxColumn = (UInt32) (xlWorksheet as XLWorksheet).Internals.CellsCollection.Select(c => c.Key.ColumnNumber).Max(); - maxRow = (UInt32) (xlWorksheet as XLWorksheet).Internals.CellsCollection.Select(c => c.Key.RowNumber).Max(); - sheetDimensionReference = "A1:" + XLAddress.GetColumnLetterFromNumber((Int32) maxColumn) + ((Int32) maxRow).ToStringLookup(); + maxColumn = (UInt32) xlWorksheet.Internals.CellsCollection.Select(c => c.Key.ColumnNumber).Max(); + maxRow = (UInt32) xlWorksheet.Internals.CellsCollection.Select(c => c.Key.RowNumber).Max(); + sheetDimensionReference = "A1:" + ExcelHelper.GetColumnLetterFromNumber((Int32) maxColumn) + ((Int32) maxRow).ToStringLookup(); } - if ((xlWorksheet as XLWorksheet).Internals.ColumnsCollection.Count > 0) + if (xlWorksheet.Internals.ColumnsCollection.Count > 0) { - UInt32 maxColCollection = (UInt32) (xlWorksheet as XLWorksheet).Internals.ColumnsCollection.Keys.Max(); + UInt32 maxColCollection = (UInt32) xlWorksheet.Internals.ColumnsCollection.Keys.Max(); if (maxColCollection > maxColumn) { maxColumn = maxColCollection; } } - if ((xlWorksheet as XLWorksheet).Internals.RowsCollection.Count > 0) + if (xlWorksheet.Internals.RowsCollection.Count > 0) { - UInt32 maxRowCollection = (UInt32) (xlWorksheet as XLWorksheet).Internals.RowsCollection.Keys.Max(); + UInt32 maxRowCollection = (UInt32) xlWorksheet.Internals.RowsCollection.Keys.Max(); if (maxRowCollection > maxRow) { maxRow = maxRowCollection; @@ -1925,7 +1926,7 @@ pane.HorizontalSplit = hSplit; pane.VerticalSplit = ySplit; - pane.TopLeftCell = XLAddress.GetColumnLetterFromNumber(xlWorksheet.SheetView.SplitColumn + 1) + pane.TopLeftCell = ExcelHelper.GetColumnLetterFromNumber(xlWorksheet.SheetView.SplitColumn + 1) + (xlWorksheet.SheetView.SplitRow + 1).ToString(); if (hSplit == 0 && ySplit == 0) @@ -1979,8 +1980,8 @@ #endregion #region Columns Columns columns = null; - if ((xlWorksheet as XLWorksheet).Internals.CellsCollection.Count == 0 && - (xlWorksheet as XLWorksheet).Internals.ColumnsCollection.Count == 0) + if (xlWorksheet.Internals.CellsCollection.Count == 0 && + xlWorksheet.Internals.ColumnsCollection.Count == 0) { worksheetPart.Worksheet.RemoveAllChildren(); } @@ -2002,10 +2003,10 @@ Int32 minInColumnsCollection; Int32 maxInColumnsCollection; - if ((xlWorksheet as XLWorksheet).Internals.ColumnsCollection.Count > 0) + if (xlWorksheet.Internals.ColumnsCollection.Count > 0) { - minInColumnsCollection = (xlWorksheet as XLWorksheet).Internals.ColumnsCollection.Keys.Min(); - maxInColumnsCollection = (xlWorksheet as XLWorksheet).Internals.ColumnsCollection.Keys.Max(); + minInColumnsCollection = xlWorksheet.Internals.ColumnsCollection.Keys.Min(); + maxInColumnsCollection = xlWorksheet.Internals.ColumnsCollection.Keys.Max(); } else { @@ -2041,13 +2042,13 @@ Boolean isHidden = false; Boolean collapsed = false; Int32 outlineLevel = 0; - if ((xlWorksheet as XLWorksheet).Internals.ColumnsCollection.ContainsKey(co)) + if (xlWorksheet.Internals.ColumnsCollection.ContainsKey(co)) { - styleId = context.SharedStyles[(xlWorksheet as XLWorksheet).Internals.ColumnsCollection[co].Style].StyleId; - columnWidth = GetColumnWidth((xlWorksheet as XLWorksheet).Internals.ColumnsCollection[co].Width); - isHidden = (xlWorksheet as XLWorksheet).Internals.ColumnsCollection[co].IsHidden; - collapsed = (xlWorksheet as XLWorksheet).Internals.ColumnsCollection[co].Collapsed; - outlineLevel = (xlWorksheet as XLWorksheet).Internals.ColumnsCollection[co].OutlineLevel; + styleId = context.SharedStyles[xlWorksheet.Internals.ColumnsCollection[co].Style].StyleId; + columnWidth = GetColumnWidth(xlWorksheet.Internals.ColumnsCollection[co].Width); + isHidden = xlWorksheet.Internals.ColumnsCollection[co].IsHidden; + collapsed = xlWorksheet.Internals.ColumnsCollection[co].Collapsed; + outlineLevel = xlWorksheet.Internals.ColumnsCollection[co].OutlineLevel; } else { @@ -2118,7 +2119,7 @@ cm.SetElement(XLWSContentManager.XLWSContents.SheetData, sheetData); var cellsByRow = new Dictionary>(); - foreach (var c in (xlWorksheet as XLWorksheet).Internals.CellsCollection.Values) + foreach (var c in xlWorksheet.Internals.CellsCollection.Values) { Int32 rowNum = c.Address.RowNumber; if (!cellsByRow.ContainsKey(rowNum)) @@ -2139,7 +2140,7 @@ } } - var distinctRows = cellsByRow.Keys.Union((xlWorksheet as XLWorksheet).Internals.RowsCollection.Keys); + var distinctRows = cellsByRow.Keys.Union(xlWorksheet.Internals.RowsCollection.Keys); Boolean noRows = (sheetData.Elements().FirstOrDefault() == null); foreach (var distinctRow in distinctRows.OrderBy(r => r)) { @@ -2182,9 +2183,9 @@ row.StyleIndex = null; row.CustomFormat = null; row.Collapsed = null; - if ((xlWorksheet as XLWorksheet).Internals.RowsCollection.ContainsKey(distinctRow)) + if (xlWorksheet.Internals.RowsCollection.ContainsKey(distinctRow)) { - var thisRow = (xlWorksheet as XLWorksheet).Internals.RowsCollection[distinctRow]; + var thisRow = xlWorksheet.Internals.RowsCollection[distinctRow]; if (thisRow.Height != xlWorksheet.RowHeight) { row.Height = thisRow.Height; @@ -2263,16 +2264,16 @@ } else { - Int32 newColumn = XLAddress.GetColumnNumberFromAddress1(cellReference); + Int32 newColumn = ExcelHelper.GetColumnNumberFromAddress1(cellReference); Cell cellBeforeInsert = null; Int32 lastCo = Int32.MaxValue; foreach ( var c in row.Elements().Where( - c => XLAddress.GetColumnNumberFromAddress1(c.CellReference.Value) > newColumn)) + c => ExcelHelper.GetColumnNumberFromAddress1(c.CellReference.Value) > newColumn)) { - var thidCo = XLAddress.GetColumnNumberFromAddress1(c.CellReference.Value); + var thidCo = ExcelHelper.GetColumnNumberFromAddress1(c.CellReference.Value); if (lastCo > thidCo) { cellBeforeInsert = c; @@ -2422,7 +2423,7 @@ #endregion #region MergeCells MergeCells mergeCells = null; - if ((xlWorksheet as XLWorksheet).Internals.MergedRanges.Any()) + if (xlWorksheet.Internals.MergedRanges.Any()) { if (!worksheetPart.Worksheet.Elements().Any()) { @@ -2434,12 +2435,9 @@ 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())) + foreach (SheetRange mergedRange in xlWorksheet.Internals.MergedRanges) { - MergeCell mergeCell = new MergeCell() {Reference = merged}; + var mergeCell = new MergeCell() { Reference = mergedRange.ToStringA1() }; mergeCells.AppendChild(mergeCell); } @@ -3734,7 +3732,7 @@ if (xlTable.ShowTotalsRow) { autoFilter1.Reference = xlTable.RangeAddress.FirstAddress + ":" + - XLAddress.GetColumnLetterFromNumber(xlTable.RangeAddress.LastAddress.ColumnNumber) + + ExcelHelper.GetColumnLetterFromNumber(xlTable.RangeAddress.LastAddress.ColumnNumber) + (xlTable.RangeAddress.LastAddress.RowNumber - 1).ToStringLookup(); } else diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index e1d71b1..9127580 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -48,7 +48,7 @@ Protection = new XLSheetProtection(); Workbook = workbook; style = new XLStyle(this, workbook.Style); - Internals = new XLWorksheetInternals(new XLCellCollection(), new XLColumnsCollection(), new XLRowsCollection(), new XLRanges(), workbook); + Internals = new XLWorksheetInternals(new XLCellCollection(), new XLColumnsCollection(), new XLRowsCollection(), new XLMergedRanges(), workbook); PageSetup = new XLPageSetup(workbook.PageOptions, this); Outline = new XLOutline(workbook.Outline); ColumnWidth = workbook.ColumnWidth; @@ -72,23 +72,23 @@ private void XLWorksheet_RangeShiftedColumns(XLRange range, int columnsShifted) { - var newMerge = new XLRanges(); + var newMerge = new XLMergedRanges(); foreach (var rngMerged in Internals.MergedRanges) { - if (range.RangeAddress.FirstAddress.ColumnNumber <= rngMerged.RangeAddress.FirstAddress.ColumnNumber - && rngMerged.RangeAddress.FirstAddress.RowNumber >= range.RangeAddress.FirstAddress.RowNumber - && rngMerged.RangeAddress.LastAddress.RowNumber <= range.RangeAddress.LastAddress.RowNumber) + if (range.RangeAddress.FirstAddress.ColumnNumber <= rngMerged.FirstAddress.ColumnNumber + && rngMerged.FirstAddress.RowNumber >= range.RangeAddress.FirstAddress.RowNumber + && rngMerged.LastAddress.RowNumber <= range.RangeAddress.LastAddress.RowNumber) { - var newRng = Range( - rngMerged.RangeAddress.FirstAddress.RowNumber, - rngMerged.RangeAddress.FirstAddress.ColumnNumber + columnsShifted, - rngMerged.RangeAddress.LastAddress.RowNumber, - rngMerged.RangeAddress.LastAddress.ColumnNumber + columnsShifted); + var newRng = new SheetRange( + rngMerged.FirstAddress.RowNumber, + rngMerged.FirstAddress.ColumnNumber + columnsShifted, + rngMerged.LastAddress.RowNumber, + rngMerged.LastAddress.ColumnNumber + columnsShifted); newMerge.Add(newRng); } else if ( - !(range.RangeAddress.FirstAddress.ColumnNumber <= rngMerged.RangeAddress.FirstAddress.ColumnNumber - && range.RangeAddress.FirstAddress.RowNumber <= rngMerged.RangeAddress.LastAddress.RowNumber)) + !(range.RangeAddress.FirstAddress.ColumnNumber <= rngMerged.FirstAddress.ColumnNumber + && range.RangeAddress.FirstAddress.RowNumber <= rngMerged.LastAddress.RowNumber)) { newMerge.Add(rngMerged); } @@ -98,22 +98,22 @@ private void XLWorksheet_RangeShiftedRows(XLRange range, int rowsShifted) { - var newMerge = new XLRanges(); + var newMerge = new XLMergedRanges(); foreach (var rngMerged in Internals.MergedRanges) { - if (range.RangeAddress.FirstAddress.RowNumber <= rngMerged.RangeAddress.FirstAddress.RowNumber - && rngMerged.RangeAddress.FirstAddress.ColumnNumber >= range.RangeAddress.FirstAddress.ColumnNumber - && rngMerged.RangeAddress.LastAddress.ColumnNumber <= range.RangeAddress.LastAddress.ColumnNumber) + if (range.RangeAddress.FirstAddress.RowNumber <= rngMerged.FirstAddress.RowNumber + && rngMerged.FirstAddress.ColumnNumber >= range.RangeAddress.FirstAddress.ColumnNumber + && rngMerged.LastAddress.ColumnNumber <= range.RangeAddress.LastAddress.ColumnNumber) { - var newRng = Range( - rngMerged.RangeAddress.FirstAddress.RowNumber + rowsShifted, - rngMerged.RangeAddress.FirstAddress.ColumnNumber, - rngMerged.RangeAddress.LastAddress.RowNumber + rowsShifted, - rngMerged.RangeAddress.LastAddress.ColumnNumber); + var newRng = new SheetRange( + rngMerged.FirstAddress.RowNumber + rowsShifted, + rngMerged.FirstAddress.ColumnNumber, + rngMerged.LastAddress.RowNumber + rowsShifted, + rngMerged.LastAddress.ColumnNumber); newMerge.Add(newRng); } - else if (!(range.RangeAddress.FirstAddress.RowNumber <= rngMerged.RangeAddress.FirstAddress.RowNumber - && range.RangeAddress.FirstAddress.ColumnNumber <= rngMerged.RangeAddress.LastAddress.ColumnNumber)) + else if (!(range.RangeAddress.FirstAddress.RowNumber <= rngMerged.FirstAddress.RowNumber + && range.RangeAddress.FirstAddress.ColumnNumber <= rngMerged.LastAddress.ColumnNumber)) { newMerge.Add(rngMerged); } @@ -356,7 +356,7 @@ } public IXLColumns Columns(String firstColumn, String lastColumn) { - return Columns(XLAddress.GetColumnNumberFromLetter(firstColumn), XLAddress.GetColumnNumberFromLetter(lastColumn)); + return Columns(ExcelHelper.GetColumnNumberFromLetter(firstColumn), ExcelHelper.GetColumnNumberFromLetter(lastColumn)); } public IXLColumns Columns(Int32 firstColumn, Int32 lastColumn) { @@ -494,7 +494,7 @@ } public IXLColumn Column(String column) { - return Column(XLAddress.GetColumnNumberFromLetter(column)); + return Column(ExcelHelper.GetColumnNumberFromLetter(column)); } IXLCell IXLWorksheet.Cell(int row, int column) @@ -659,7 +659,7 @@ ws.PageSetup = new XLPageSetup(PageSetup, ws); ws.Outline = new XLOutline(Outline); ws.SheetView = new XLSheetView(SheetView); - Internals.MergedRanges.ForEach(kp => ws.Internals.MergedRanges.Add(ws.Range(kp.RangeAddress.ToString()))); + Internals.MergedRanges = ws.Internals.MergedRanges.Clone(); foreach (var r in NamedRanges) { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheetInternals.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheetInternals.cs index f4dcbad..c5af1f3 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheetInternals.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheetInternals.cs @@ -1,5 +1,4 @@ - -namespace ClosedXML.Excel +namespace ClosedXML.Excel { internal class XLWorksheetInternals: IXLWorksheetInternals { @@ -7,7 +6,7 @@ XLCellCollection cellsCollection, XLColumnsCollection columnsCollection, XLRowsCollection rowsCollection, - XLRanges mergedRanges, + XLMergedRanges mergedRanges, XLWorkbook workbook ) { @@ -21,7 +20,7 @@ public XLCellCollection CellsCollection { get; private set; } public XLColumnsCollection ColumnsCollection { get; private set; } public XLRowsCollection RowsCollection { get; private set; } - public XLRanges MergedRanges { get; internal set; } + public XLMergedRanges MergedRanges { get; internal set; } public XLWorkbook Workbook { get; internal set; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs b/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs new file mode 100644 index 0000000..9cc90b9 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs @@ -0,0 +1,181 @@ +using System; +using System.Globalization; +using System.Text; +using ClosedXML.Excel; + +namespace ClosedXML +{ + /// + /// Common methods + /// + public static class ExcelHelper + { + internal static readonly NumberFormatInfo NumberFormatForParse = CultureInfo.InvariantCulture.NumberFormat; + private const Int32 TwoT26 = 26 * 26; + /// + /// Gets the column number of a given column letter. + /// + /// The column letter to translate into a column number. + public static int GetColumnNumberFromLetter(string columnLetter) + { + if (columnLetter[0] <= '9') + { + return Int32.Parse(columnLetter, NumberFormatForParse); + } + + columnLetter = columnLetter.ToUpper(); + var length = columnLetter.Length; + if (length == 1) + { + return Convert.ToByte(columnLetter[0]) - 64; + } + if (length == 2) + { + return + ((Convert.ToByte(columnLetter[0]) - 64) * 26) + + (Convert.ToByte(columnLetter[1]) - 64); + } + if (length == 3) + { + return ((Convert.ToByte(columnLetter[0]) - 64) * TwoT26) + + ((Convert.ToByte(columnLetter[1]) - 64) * 26) + + (Convert.ToByte(columnLetter[2]) - 64); + } + throw new ApplicationException("Column Length must be between 1 and 3."); + } + /// + /// Gets the column letter of a given column number. + /// + /// The column number to translate into a column letter. + public static string GetColumnLetterFromNumber(int column) + { + #region Check + if (column <= 0) + { + throw new ArgumentOutOfRangeException("column", "Must be more than 0"); + } + #endregion + var value = new StringBuilder(6); + while (column > 0) + { + int residue = column % 26; + column /= 26; + if (residue == 0) + { + residue = 26; + column--; + } + value.Insert(0, (char)(64 + residue)); + } + return value.ToString(); + } + + public static bool IsValidColumn(string column) + { + if (StringExtensions.IsNullOrWhiteSpace(column) || column.Length > 3) + { + return false; + } + + Boolean retVal = true; + String theColumn = column.ToUpper(); + for (Int32 i = 0; i < column.Length; i++) + { + if (theColumn[i] < 'A' || theColumn[i] > 'Z' || (i == 2 && theColumn[i] > 'D')) + { + retVal = false; + break; + } + } + return retVal; + } + + public static bool IsValidRow(string rowString) + { + Int32 row; + if (Int32.TryParse(rowString, out row)) + { + return row > 0 && row <= XLWorksheet.MaxNumberOfRows; + } + return false; + } + + public static bool IsValidA1Address(string address) + { + address = address.Replace("$", ""); + Int32 rowPos = 0; + Int32 addressLength = address.Length; + while (rowPos < addressLength && (address[rowPos] > '9' || address[rowPos] < '0')) + { + rowPos++; + } + + return + rowPos < addressLength + && IsValidRow(address.Substring(rowPos)) + && IsValidColumn(address.Substring(0, rowPos)); + } + + public static int GetRowFromAddress1(string cellAddressString) + { + Int32 rowPos = 1; + while (cellAddressString[rowPos] > '9') + { + rowPos++; + } + + return Int32.Parse(cellAddressString.Substring(rowPos), NumberFormatForParse); + } + + public static int GetColumnNumberFromAddress1(string cellAddressString) + { + Int32 rowPos = 0; + while (cellAddressString[rowPos] > '9') + { + rowPos++; + } + + return GetColumnNumberFromLetter(cellAddressString.Substring(0, rowPos)); + } + + public static int GetRowFromAddress2(string cellAddressString) + { + Int32 rowPos = 1; + while (cellAddressString[rowPos] > '9') + { + rowPos++; + } + + if (cellAddressString[rowPos] == '$') + { + return Int32.Parse(cellAddressString.Substring(rowPos + 1), NumberFormatForParse); + } + return Int32.Parse(cellAddressString.Substring(rowPos), NumberFormatForParse); + } + + public static int GetColumnNumberFromAddress2(string cellAddressString) + { + Int32 startPos; + if (cellAddressString[0] == '$') + { + startPos = 1; + } + else + { + startPos = 0; + } + + Int32 rowPos = startPos; + while (cellAddressString[rowPos] > '9') + { + rowPos++; + } + + if (cellAddressString[rowPos] == '$') + { + return GetColumnNumberFromLetter(cellAddressString.Substring(startPos, rowPos - 1)); + } + return GetColumnNumberFromLetter(cellAddressString.Substring(startPos, rowPos)); + } + } +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj index 3668a59..7d0da0d 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -203,6 +203,7 @@ +