diff --git a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj index 1dc3636..6c069fa 100644 --- a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj @@ -150,7 +150,10 @@ - + + + + diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs index 98bf6b1..4ca1e98 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs @@ -172,6 +172,6 @@ //IXLCell CopyFrom(IXLCell otherCell); - void CopyTo(IXLCell target); + IXLCell CopyTo(IXLCell target); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index 0c1681d..4c90564 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -645,7 +645,7 @@ } set { - style = new XLStyle(null, value); + style = new XLStyle(this, value); } } @@ -1546,9 +1546,10 @@ } } - public void CopyTo(IXLCell target) + public IXLCell CopyTo(IXLCell target) { target.Value = this; + return target; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs index fc595b0..2d2dd2b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs @@ -150,10 +150,18 @@ Int32 CellCount(); - void CopyTo(IXLCell target); - void CopyTo(IXLRangeBase target); + IXLRangeColumn CopyTo(IXLCell cell); + IXLRangeColumn CopyTo(IXLRangeBase range); + IXLColumn CopyTo(IXLColumn column); void SetAutoFilter(); void SetAutoFilter(Boolean autoFilter); + + IXLColumn Sort(); + IXLColumn Sort(Boolean matchCase); + IXLColumn Sort(XLSortOrder sortOrder); + IXLColumn Sort(XLSortOrder sortOrder, Boolean matchCase); + + IXLRangeColumn Column(Int32 start, Int32 end); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs index c0184c3..4fba1b2 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs @@ -462,5 +462,92 @@ { return this.RangeAddress.LastAddress.ColumnNumber - this.RangeAddress.FirstAddress.ColumnNumber + 1; } + + public IXLColumn Sort() + { + this.RangeUsed().Sort(); + return this; + } + public IXLColumn Sort(XLSortOrder sortOrder) + { + this.RangeUsed().Sort(sortOrder); + return this; + } + public IXLColumn Sort(Boolean matchCase) + { + this.AsRange().Sort(matchCase); + return this; + } + public IXLColumn Sort(XLSortOrder sortOrder, Boolean matchCase) + { + this.AsRange().Sort(sortOrder, matchCase); + return this; + } + + private void CopyToCell(IXLRangeColumn rngColumn, IXLCell cell) + { + Int32 cellCount = rngColumn.CellCount(); + Int32 roStart = cell.Address.RowNumber; + Int32 coStart = cell.Address.ColumnNumber; + for (Int32 ro = roStart; ro <= cellCount + roStart - 1; ro++) + { + (cell.Worksheet.Cell(ro, coStart) as XLCell).CopyFrom(rngColumn.Cell(ro - roStart + 1)); + } + } + + public new IXLRangeColumn CopyTo(IXLCell target) + { + var rngUsed = RangeUsed().Column(1); + CopyToCell(rngUsed, target); + + Int32 lastRowNumber = target.Address.RowNumber + rngUsed.CellCount() - 1; + if (lastRowNumber > XLWorksheet.MaxNumberOfRows) lastRowNumber = XLWorksheet.MaxNumberOfRows; + + return target.Worksheet.Range( + target.Address.RowNumber, + target.Address.ColumnNumber, + lastRowNumber, + target.Address.ColumnNumber) + .Column(1); + } + public new IXLRangeColumn CopyTo(IXLRangeBase target) + { + var thisRangeUsed = RangeUsed(); + Int32 thisRowCount = thisRangeUsed.RowCount(); + var targetRangeUsed = target.AsRange().RangeUsed(); + Int32 targetRowCount = targetRangeUsed.RowCount(); + Int32 maxRow = thisRowCount > targetRowCount ? thisRowCount : targetRowCount; + + CopyToCell(this.Range(1, 1, maxRow, 1).Column(1), target.FirstCell()); + + Int32 lastRowNumber = target.RangeAddress.FirstAddress.RowNumber + maxRow - 1; + if (lastRowNumber > XLWorksheet.MaxNumberOfRows) lastRowNumber = XLWorksheet.MaxNumberOfRows; + + return (target as XLRangeBase).Worksheet.Range( + target.RangeAddress.FirstAddress.RowNumber, + target.RangeAddress.LastAddress.ColumnNumber, + lastRowNumber, + target.RangeAddress.LastAddress.ColumnNumber ) + .Column(1); + } + public IXLColumn CopyTo(IXLColumn column) + { + var thisRangeUsed = RangeUsed(); + Int32 thisRowCount = thisRangeUsed.RowCount(); + //var targetRangeUsed = column target.AsRange().RangeUsed(); + Int32 targetRowCount = column.LastCellUsed(true).Address.RowNumber; + Int32 maxRow = thisRowCount > targetRowCount ? thisRowCount : targetRowCount; + + CopyToCell(this.Column(1, maxRow), column.FirstCell()); + var newColumn = column as XLColumn; + newColumn.width = width; + newColumn.style = new XLStyle(newColumn, Style); + return newColumn; + } + + public IXLRangeColumn Column(Int32 start, Int32 end) + { + return AsRange().Range(start, 1, end, 1).Column(1); + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumnCollection.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumnCollection.cs index e7e5bc8..30d84c2 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumnCollection.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumnCollection.cs @@ -19,11 +19,10 @@ foreach (var ro in dictionary.Keys.Where(k => k >= startingColumn).OrderByDescending(k => k)) { var columnToMove = dictionary[ro]; - var newColumn = ro + columnsToShift; + Int32 newColumn = ro + columnsToShift; if (newColumn <= XLWorksheet.MaxNumberOfColumns) { - var xlColumnParameters = new XLColumnParameters(columnToMove.Worksheet, columnToMove.Style, false); - dictionary.Add(newColumn, new XLColumn(newColumn, xlColumnParameters)); + dictionary.Add(newColumn, new XLColumn(columnToMove, columnToMove.Worksheet)); } dictionary.Remove(ro); } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLTextLengthCriteria.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLTextLengthCriteria.cs index ce25362..9d553cd 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLTextLengthCriteria.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLTextLengthCriteria.cs @@ -13,43 +13,43 @@ } - public void EqualTo(UInt32 value) + public void EqualTo(Int32 value) { dataValidation.Value = value.ToString(); dataValidation.Operator = XLOperator.EqualTo; } - public void NotEqualTo(UInt32 value) + public void NotEqualTo(Int32 value) { dataValidation.Value = value.ToString(); dataValidation.Operator = XLOperator.NotEqualTo; } - public void GreaterThan(UInt32 value) + public void GreaterThan(Int32 value) { dataValidation.Value = value.ToString(); dataValidation.Operator = XLOperator.GreaterThan; } - public void LessThan(UInt32 value) + public void LessThan(Int32 value) { dataValidation.Value = value.ToString(); dataValidation.Operator = XLOperator.LessThan; } - public void EqualOrGreaterThan(UInt32 value) + public void EqualOrGreaterThan(Int32 value) { dataValidation.Value = value.ToString(); dataValidation.Operator = XLOperator.EqualOrGreaterThan; } - public void EqualOrLessThan(UInt32 value) + public void EqualOrLessThan(Int32 value) { dataValidation.Value = value.ToString(); dataValidation.Operator = XLOperator.EqualOrLessThan; } - public void Between(UInt32 minValue, UInt32 maxValue) + public void Between(Int32 minValue, Int32 maxValue) { dataValidation.MinValue = minValue.ToString(); dataValidation.MaxValue = maxValue.ToString(); dataValidation.Operator = XLOperator.Between; } - public void NotBetween(UInt32 minValue, UInt32 maxValue) + public void NotBetween(Int32 minValue, Int32 maxValue) { dataValidation.MinValue = minValue.ToString(); dataValidation.MaxValue = maxValue.ToString(); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs index 795c421..bd9ba23 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs @@ -269,5 +269,22 @@ IXLSheetProtection Unprotect(String password); IXLRangeBase AutoFilterRange { get; set; } + + IXLSortElements SortRows { get; } + IXLSortElements SortColumns { get; } + + IXLRange Sort(); + IXLRange Sort(Boolean matchCase); + IXLRange Sort(XLSortOrder sortOrder); + IXLRange Sort(XLSortOrder sortOrder, Boolean matchCase); + IXLRange Sort(String columnsToSortBy); + IXLRange Sort(String columnsToSortBy, Boolean matchCase); + + IXLRange Sort(XLSortOrientation sortOrientation); + IXLRange Sort(XLSortOrientation sortOrientation, XLSortOrder sortOrder); + IXLRange Sort(XLSortOrientation sortOrientation, String elementsToSortBy); + IXLRange Sort(XLSortOrientation sortOrientation, Boolean matchCase); + IXLRange Sort(XLSortOrientation sortOrientation, XLSortOrder sortOrder, Boolean matchCase); + IXLRange Sort(XLSortOrientation sortOrientation, String elementsToSortBy, Boolean matchCase); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/IXLPageSetup.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/IXLPageSetup.cs index 955d5fc..3ad8f3f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/IXLPageSetup.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/IXLPageSetup.cs @@ -151,11 +151,11 @@ /// /// Gets or sets the horizontal dpi for printing the worksheet. /// - UInt32 HorizontalDpi { get; set; } + Int32 HorizontalDpi { get; set; } /// /// Gets or sets the vertical dpi for printing the worksheet. /// - UInt32 VerticalDpi { get; set; } + Int32 VerticalDpi { get; set; } /// /// Gets or sets the page number that will begin the printout. /// For example, the first page of your printout could be numbered page 5. diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/XLPageSetup.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/XLPageSetup.cs index 4414107..74192da 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/XLPageSetup.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/PageSetup/XLPageSetup.cs @@ -104,8 +104,8 @@ public XLPageOrientation PageOrientation { get; set; } public XLPaperSize PaperSize { get; set; } - public UInt32 HorizontalDpi { get; set; } - public UInt32 VerticalDpi { get; set; } + public Int32 HorizontalDpi { get; set; } + public Int32 VerticalDpi { get; set; } public Int32 FirstPageNumber { get; set; } public Boolean CenterHorizontally { get; set; } public Boolean CenterVertically { get; set; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs index dec3f79..8ff7875 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs @@ -8,7 +8,7 @@ { public enum XLShiftDeletedCells { ShiftCellsUp, ShiftCellsLeft } public enum XLTransposeOptions { MoveCells, ReplaceCells } - public enum XLSortOrder { Ascending, Descending } + public interface IXLRange: IXLRangeBase { /// @@ -217,16 +217,28 @@ IXLRange RangeUsed(); - //IXLRange SortRows(); - //IXLRange SortRows(XLSortOrder sortOrder); - //IXLRange SortRows(String columnToSortBy); - //IXLRange SortRows(XLSortOrder sortOrder, String columnOrder); - - void CopyTo(IXLCell target); - void CopyTo(IXLRangeBase target); + IXLRange CopyTo(IXLCell target); + IXLRange CopyTo(IXLRangeBase target); void SetAutoFilter(); void SetAutoFilter(Boolean autoFilter); + + IXLSortElements SortRows { get; } + IXLSortElements SortColumns { get; } + + IXLRange Sort(); + IXLRange Sort(Boolean matchCase); + IXLRange Sort(XLSortOrder sortOrder); + IXLRange Sort(XLSortOrder sortOrder, Boolean matchCase); + IXLRange Sort(String columnsToSortBy); + IXLRange Sort(String columnsToSortBy, Boolean matchCase); + + IXLRange Sort(XLSortOrientation sortOrientation); + IXLRange Sort(XLSortOrientation sortOrientation, XLSortOrder sortOrder); + IXLRange Sort(XLSortOrientation sortOrientation, String elementsToSortBy); + IXLRange Sort(XLSortOrientation sortOrientation, Boolean matchCase); + IXLRange Sort(XLSortOrientation sortOrientation, XLSortOrder sortOrder, Boolean matchCase); + IXLRange Sort(XLSortOrientation sortOrientation, String elementsToSortBy, Boolean matchCase); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs index 6e8fee0..cf05e2f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -9,6 +9,7 @@ public interface IXLRangeBase { + /// /// Returns the collection of cells. /// diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs index acb0046..d499fb3 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs @@ -6,7 +6,7 @@ namespace ClosedXML.Excel { - public interface IXLRangeColumn: IXLRangeBase + public interface IXLRangeColumn : IXLRangeBase { /// /// Gets the cell in the specified row. @@ -78,11 +78,16 @@ Int32 CellCount(); - void CopyTo(IXLCell target); - void CopyTo(IXLRangeBase target); + IXLRangeColumn CopyTo(IXLCell target); + IXLRangeColumn CopyTo(IXLRangeBase target); void SetAutoFilter(); void SetAutoFilter(Boolean autoFilter); + + IXLRangeColumn Sort(); + IXLRangeColumn Sort(Boolean matchCase); + IXLRangeColumn Sort(XLSortOrder sortOrder); + IXLRangeColumn Sort(XLSortOrder sortOrder, Boolean matchCase); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs index 12d284c..f9f9748 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs @@ -85,11 +85,16 @@ Int32 CellCount(); - void CopyTo(IXLCell target); - void CopyTo(IXLRangeBase target); + IXLRangeRow CopyTo(IXLCell target); + IXLRangeRow CopyTo(IXLRangeBase target); void SetAutoFilter(); void SetAutoFilter(Boolean autoFilter); + + IXLRangeRow Sort(); + IXLRangeRow Sort(Boolean matchCase); + IXLRangeRow Sort(XLSortOrder sortOrder); + IXLRangeRow Sort(XLSortOrder sortOrder, Boolean matchCase); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/Sort/IXLSortElement.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/Sort/IXLSortElement.cs new file mode 100644 index 0000000..226deba --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/Sort/IXLSortElement.cs @@ -0,0 +1,17 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public enum XLSortOrder { Ascending, Descending } + public enum XLSortOrientation { TopToBottom, LeftToRight } + public interface IXLSortElement + { + Int32 ElementNumber { get; set; } + XLSortOrder SortOrder { get; set; } + Boolean IgnoreBlanks { get; set; } + Boolean MatchCase { get; set; } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/Sort/IXLSortElements.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/Sort/IXLSortElements.cs new file mode 100644 index 0000000..8aeff1c --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/Sort/IXLSortElements.cs @@ -0,0 +1,22 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public interface IXLSortElements: IEnumerable + { + void Add(Int32 elementNumber); + void Add(Int32 elementNumber, XLSortOrder sortOrder); + void Add(Int32 elementNumber, XLSortOrder sortOrder, Boolean ignoreBlanks); + void Add(Int32 elementNumber, XLSortOrder sortOrder, Boolean ignoreBlanks, Boolean matchCase); + + void Add(String elementNumber); + void Add(String elementNumber, XLSortOrder sortOrder); + void Add(String elementNumber, XLSortOrder sortOrder, Boolean ignoreBlanks); + void Add(String elementNumber, XLSortOrder sortOrder, Boolean ignoreBlanks, Boolean matchCase); + + void Clear(); + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/Sort/XLSortColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/Sort/XLSortColumn.cs deleted file mode 100644 index c0c6590..0000000 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/Sort/XLSortColumn.cs +++ /dev/null @@ -1,12 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; - -namespace ClosedXML.Excel -{ - public class XLSortColumn - { - - } -} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/Sort/XLSortElement.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/Sort/XLSortElement.cs new file mode 100644 index 0000000..f06b2f6 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/Sort/XLSortElement.cs @@ -0,0 +1,15 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + internal class XLSortElement: IXLSortElement + { + public Int32 ElementNumber { get; set; } + public XLSortOrder SortOrder { get; set; } + public Boolean IgnoreBlanks { get; set; } + public Boolean MatchCase { get; set; } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/Sort/XLSortElements.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/Sort/XLSortElements.cs new file mode 100644 index 0000000..1a42318 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/Sort/XLSortElements.cs @@ -0,0 +1,72 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + internal class XLSortElements: IXLSortElements + { + List elements = new List(); + public void Add(Int32 elementNumber) + { + Add(elementNumber, XLSortOrder.Ascending); + } + public void Add(Int32 elementNumber, XLSortOrder sortOrder) + { + Add(elementNumber, sortOrder, true); + } + public void Add(Int32 elementNumber, XLSortOrder sortOrder, Boolean ignoreBlanks) + { + Add(elementNumber, sortOrder, ignoreBlanks, false); + } + public void Add(Int32 elementNumber, XLSortOrder sortOrder, Boolean ignoreBlanks, Boolean matchCase) + { + elements.Add(new XLSortElement() + { + ElementNumber = elementNumber, + SortOrder = sortOrder, + IgnoreBlanks = ignoreBlanks, + MatchCase = matchCase + }); + } + + public void Add(String elementNumber) + { + Add(elementNumber, XLSortOrder.Ascending); + } + public void Add(String elementNumber, XLSortOrder sortOrder) + { + Add(elementNumber, sortOrder, true); + } + public void Add(String elementNumber, XLSortOrder sortOrder, Boolean ignoreBlanks) + { + Add(elementNumber, sortOrder, ignoreBlanks, false); + } + public void Add(String elementNumber, XLSortOrder sortOrder, Boolean ignoreBlanks, Boolean matchCase) + { + elements.Add(new XLSortElement() + { + ElementNumber = XLAddress.GetColumnNumberFromLetter(elementNumber), + SortOrder = sortOrder, + IgnoreBlanks = ignoreBlanks, + MatchCase = matchCase + }); + } + + public IEnumerator GetEnumerator() + { + return elements.GetEnumerator(); + } + + System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() + { + return GetEnumerator(); + } + + public void Clear() + { + elements.Clear(); + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs index 431029b..7ca48db 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs @@ -14,8 +14,12 @@ { this.RangeParameters = xlRangeParameters; Worksheet = xlRangeParameters.Worksheet; - Worksheet.RangeShiftedRows += new RangeShiftedRowsDelegate(Worksheet_RangeShiftedRows); - Worksheet.RangeShiftedColumns += new RangeShiftedColumnsDelegate(Worksheet_RangeShiftedColumns); + if (!xlRangeParameters.IgnoreEvents) + { + Worksheet.RangeShiftedRows += new RangeShiftedRowsDelegate(Worksheet_RangeShiftedRows); + Worksheet.RangeShiftedColumns += new RangeShiftedColumnsDelegate(Worksheet_RangeShiftedColumns); + xlRangeParameters.IgnoreEvents = true; + } this.defaultStyle = new XLStyle(this, xlRangeParameters.DefaultStyle); } @@ -147,6 +151,16 @@ Worksheet.Style)); } + public IXLRangeRow RowQuick(Int32 row) + { + IXLAddress firstCellAddress = new XLAddress(RangeAddress.FirstAddress.RowNumber + row - 1, RangeAddress.FirstAddress.ColumnNumber, false, false); + IXLAddress lastCellAddress = new XLAddress(RangeAddress.FirstAddress.RowNumber + row - 1, RangeAddress.LastAddress.ColumnNumber, false, false); + return new XLRangeRow( + new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), + Worksheet, + Worksheet.Style), true); + + } public IXLRangeColumn Column(Int32 column) { IXLAddress firstCellAddress = new XLAddress(RangeAddress.FirstAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber + column - 1, false, false); @@ -156,6 +170,15 @@ Worksheet, Worksheet.Style)); } + public IXLRangeColumn ColumnQuick(Int32 column) + { + IXLAddress firstCellAddress = new XLAddress(RangeAddress.FirstAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber + column - 1, false, false); + IXLAddress lastCellAddress = new XLAddress(RangeAddress.LastAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber + column - 1, false, false); + return new XLRangeColumn( + new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), + Worksheet, + Worksheet.Style), true); + } public IXLRangeColumn Column(String column) { return this.Column(XLAddress.GetColumnNumberFromLetter(column)); @@ -343,8 +366,7 @@ var oldCell = rngToTranspose.Cell(ro, co); var newKey = rngToTranspose.Cell(co, ro).Address; // new XLAddress(c.Address.ColumnNumber, c.Address.RowNumber); var newCell = new XLCell(newKey, oldCell.Style, Worksheet); - newCell.Value = oldCell.Value; - newCell.DataType = oldCell.DataType; + newCell.CopyFrom(oldCell); cellsToInsert.Add(newKey, newCell); cellsToDelete.Add(oldCell.Address); } @@ -455,48 +477,304 @@ ^ this.Worksheet.GetHashCode(); } - private void SortRange(XLRange xLRange, string[] columns) + IXLSortElements sortRows; + public IXLSortElements SortRows { - throw new NotImplementedException(); + get + { + if (sortRows == null) sortRows = new XLSortElements(); + return sortRows; + } } - public IXLRange Sort(String columnsToSort) + IXLSortElements sortColumns; + public IXLSortElements SortColumns { - var cols = columnsToSort.Split(',').ToList(); - q_sort(1, this.RowCount(), cols); + get + { + if (sortColumns == null) sortColumns = new XLSortElements(); + return sortColumns; + } + } + + public IXLRange Sort() + { + if (SortColumns.Count() == 0) + return Sort(XLSortOrder.Ascending); + else + { + SortRangeRows(); + return this; + } + } + public IXLRange Sort(Boolean matchCase) + { + if (SortColumns.Count() == 0) + return Sort(XLSortOrder.Ascending, false); + else + { + SortRangeRows(); + return this; + } + } + public IXLRange Sort(XLSortOrder sortOrder) + { + if (SortColumns.Count() == 0) + { + Int32 columnCount = this.ColumnCount(); + for (Int32 co = 1; co <= columnCount; co++) + SortColumns.Add(co, sortOrder); + } + else + { + SortColumns.ForEach(sc => sc.SortOrder = sortOrder); + } + SortRangeRows(); + return this; + } + public IXLRange Sort(XLSortOrder sortOrder, Boolean matchCase) + { + if (SortColumns.Count() == 0) + { + Int32 columnCount = this.ColumnCount(); + for (Int32 co = 1; co <= columnCount; co++) + SortColumns.Add(co, sortOrder, true, matchCase); + } + else + { + SortColumns.ForEach(sc => { sc.SortOrder = sortOrder; sc.MatchCase = matchCase; }); + } + SortRangeRows(); + return this; + } + public IXLRange Sort(String columnsToSortBy) + { + SortColumns.Clear(); + foreach (String coPair in columnsToSortBy.Split(',')) + { + String coPairTrimmed = coPair.Trim(); + String coString; + String order; + if (coPairTrimmed.Contains(' ')) + { + var pair = coPairTrimmed.Split(' '); + coString = pair[0]; + order = pair[1]; + } + else + { + coString = coPairTrimmed; + order = "ASC"; + } + + Int32 co; + if (!Int32.TryParse(coString, out co)) + co = XLAddress.GetColumnNumberFromLetter(coString); + + if (order.ToUpper().Equals("ASC")) + SortColumns.Add(co, XLSortOrder.Ascending); + else + SortColumns.Add(co, XLSortOrder.Descending); + } + + SortRangeRows(); + return this; + } + public IXLRange Sort(String columnsToSortBy, Boolean matchCase) + { + SortColumns.Clear(); + foreach (String coPair in columnsToSortBy.Split(',')) + { + String coPairTrimmed = coPair.Trim(); + String coString; + String order; + if (coPairTrimmed.Contains(' ')) + { + var pair = coPairTrimmed.Split(' '); + coString = pair[0]; + order = pair[1]; + } + else + { + coString = coPairTrimmed; + order = "ASC"; + } + + Int32 co; + if (!Int32.TryParse(coString, out co)) + co = XLAddress.GetColumnNumberFromLetter(coString); + + if (order.ToUpper().Equals("ASC")) + SortColumns.Add(co, XLSortOrder.Ascending, true, matchCase); + else + SortColumns.Add(co, XLSortOrder.Descending, true, matchCase); + } + + SortRangeRows(); return this; } - public void q_sort(int left, int right, List columnsToSort) + public IXLRange Sort(XLSortOrientation sortOrientation) { - int i, j; - XLRangeRow x, y; - - i = left; j = right; - x = (XLRangeRow)Row(((left + right) / 2)); - - do + if (sortOrientation == XLSortOrientation.TopToBottom) { - while ((((XLRangeRow)Row(i)).CompareTo(x, columnsToSort) < 0) && (i < right)) i++; - while ((0 < ((XLRangeRow)Row(j)).CompareTo(x, columnsToSort)) && (j > left)) j--; - - if (i < j) + return Sort(); + } + else + { + if (SortRows.Count() == 0) + return Sort(sortOrientation, XLSortOrder.Ascending); + else { - SwapRows(i, j); - i++; j--; + SortRangeColumns(); + return this; } - else if (i == j) + } + } + public IXLRange Sort(XLSortOrientation sortOrientation, Boolean matchCase) + { + if (sortOrientation == XLSortOrientation.TopToBottom) + { + return Sort(matchCase); + } + else + { + if (SortRows.Count() == 0) + return Sort(sortOrientation, XLSortOrder.Ascending, matchCase); + else { - i++; j--; + SortRangeColumns(); + return this; } - } while (i <= j); + } + } + public IXLRange Sort(XLSortOrientation sortOrientation, XLSortOrder sortOrder) + { + if (sortOrientation == XLSortOrientation.TopToBottom) + { + return Sort(sortOrder); + } + else + { + if (SortRows.Count() == 0) + { + Int32 rowCount = this.RowCount(); + for (Int32 co = 1; co <= rowCount; co++) + SortRows.Add(co, sortOrder); + } + else + { + SortRows.ForEach(sc => sc.SortOrder = sortOrder); + } + SortRangeColumns(); + return this; + } + } + public IXLRange Sort(XLSortOrientation sortOrientation, XLSortOrder sortOrder, Boolean matchCase) + { + if (sortOrientation == XLSortOrientation.TopToBottom) + { + return Sort(sortOrder, matchCase); + } + else + { + if (SortRows.Count() == 0) + { + Int32 rowCount = this.RowCount(); + for (Int32 co = 1; co <= rowCount; co++) + SortRows.Add(co, sortOrder, matchCase); + } + else + { + SortRows.ForEach(sc => { sc.SortOrder = sortOrder; sc.MatchCase = matchCase; }); + } + SortRangeColumns(); + return this; + } + } + public IXLRange Sort(XLSortOrientation sortOrientation, String elementsToSortBy) + { + if (sortOrientation == XLSortOrientation.TopToBottom) + { + return Sort(elementsToSortBy); + } + else + { + SortRows.Clear(); + foreach (String roPair in elementsToSortBy.Split(',')) + { + String roPairTrimmed = roPair.Trim(); + String roString; + String order; + if (roPairTrimmed.Contains(' ')) + { + var pair = roPairTrimmed.Split(' '); + roString = pair[0]; + order = pair[1]; + } + else + { + roString = roPairTrimmed; + order = "ASC"; + } - if (left < j) q_sort(left, j, columnsToSort); - if (i < right) q_sort(i, right, columnsToSort); + Int32 ro = Int32.Parse(roString); + if (order.ToUpper().Equals("ASC")) + SortRows.Add(ro, XLSortOrder.Ascending); + else + SortRows.Add(ro, XLSortOrder.Descending); + } + + SortRangeColumns(); + return this; + } + } + public IXLRange Sort(XLSortOrientation sortOrientation, String elementsToSortBy, Boolean matchCase) + { + if (sortOrientation == XLSortOrientation.TopToBottom) + { + return Sort(elementsToSortBy, matchCase); + } + else + { + SortRows.Clear(); + foreach (String roPair in elementsToSortBy.Split(',')) + { + String roPairTrimmed = roPair.Trim(); + String roString; + String order; + if (roPairTrimmed.Contains(' ')) + { + var pair = roPairTrimmed.Split(' '); + roString = pair[0]; + order = pair[1]; + } + else + { + roString = roPairTrimmed; + order = "ASC"; + } + + Int32 ro = Int32.Parse(roString); + + if (order.ToUpper().Equals("ASC")) + SortRows.Add(ro, XLSortOrder.Ascending,true, matchCase); + else + SortRows.Add(ro, XLSortOrder.Descending, true, matchCase); + } + + SortRangeColumns(); + return this; + } } - public void SwapRows(Int32 row1, Int32 row2) + #region Sort Rows + private void SortRangeRows() + { + SortingRangeRows(1, this.RowCount()); + } + private void SwapRows(Int32 row1, Int32 row2) { Int32 cellCount = ColumnCount(); @@ -504,20 +782,182 @@ for (Int32 co = 1; co <= cellCount; co++) { - var cell1 = (XLCell)Row(row1).Cell(co); + var cell1 = (XLCell)RowQuick(row1).Cell(co); var cell1Address = cell1.Address; - var cell2 = (XLCell)Row(row2).Cell(co); + var cell2 = (XLCell)RowQuick(row2).Cell(co); cell1.Address = cell2.Address; - cell2.Address = cell1Address; - Worksheet.Internals.CellsCollection.Remove(cell1.Address); - Worksheet.Internals.CellsCollection.Remove(cell2.Address); - Worksheet.Internals.CellsCollection.Add(cell1.Address, cell1); - Worksheet.Internals.CellsCollection.Add(cell2.Address, cell2); + Worksheet.Internals.CellsCollection[cell1.Address] = cell1; + Worksheet.Internals.CellsCollection[cell2.Address] = cell2; } } + private int SortRangeRows(int begPoint, int endPoint) + { + int pivot = begPoint; + int m = begPoint + 1; + int n = endPoint; + while ((m < endPoint) && + ((RowQuick(pivot) as XLRangeRow).CompareTo((RowQuick(m) as XLRangeRow), SortColumns) >= 0)) + { + m++; + } + + while ((n > begPoint) && + ((RowQuick(pivot) as XLRangeRow).CompareTo((RowQuick(n) as XLRangeRow), SortColumns) <= 0)) + { + n--; + } + while (m < n) + { + SwapRows(m, n); + + while ((m < endPoint) && + ((RowQuick(pivot) as XLRangeRow).CompareTo((RowQuick(m) as XLRangeRow), SortColumns) >= 0)) + { + m++; + } + + while ((n > begPoint) && + ((RowQuick(pivot) as XLRangeRow).CompareTo((RowQuick(n) as XLRangeRow), SortColumns) <= 0)) + { + n--; + } + + } + if (pivot != n) + { + SwapRows(n, pivot); + } + return n; + + } + private void SortingRangeRows(int beg, int end) + { + if (end == beg) + { + return; + } + else + { + int pivot = SortRangeRows(beg, end); + if (pivot > beg) + SortingRangeRows(beg, pivot - 1); + if (pivot < end) + SortingRangeRows(pivot + 1, end); + } + } + #endregion + + #region Sort Columns + private void SortRangeColumns() + { + SortingRangeColumns(1, this.ColumnCount()); + } + private void SwapColumns(Int32 column1, Int32 column2) + { + + Int32 cellCount = ColumnCount(); + + for (Int32 co = 1; co <= cellCount; co++) + { + + var cell1 = (XLCell)ColumnQuick(column1).Cell(co); + var cell1Address = cell1.Address; + var cell2 = (XLCell)ColumnQuick(column2).Cell(co); + + cell1.Address = cell2.Address; + cell2.Address = cell1Address; + + Worksheet.Internals.CellsCollection[cell1.Address] = cell1; + Worksheet.Internals.CellsCollection[cell2.Address] = cell2; + } + + } + private int SortRangeColumns(int begPoint, int endPoint) + { + int pivot = begPoint; + int m = begPoint + 1; + int n = endPoint; + while ((m < endPoint) && + ((ColumnQuick(pivot) as XLRangeColumn).CompareTo((ColumnQuick(m) as XLRangeColumn), SortRows) >= 0)) + { + m++; + } + + while ((n > begPoint) && + ((ColumnQuick(pivot) as XLRangeColumn).CompareTo((ColumnQuick(n) as XLRangeColumn), SortRows) <= 0)) + { + n--; + } + while (m < n) + { + SwapColumns(m, n); + + while ((m < endPoint) && + ((ColumnQuick(pivot) as XLRangeColumn).CompareTo((ColumnQuick(m) as XLRangeColumn), SortRows) >= 0)) + { + m++; + } + + while ((n > begPoint) && + ((ColumnQuick(pivot) as XLRangeColumn).CompareTo((ColumnQuick(n) as XLRangeColumn), SortRows) <= 0)) + { + n--; + } + + } + if (pivot != n) + { + SwapColumns(n, pivot); + } + return n; + } + private void SortingRangeColumns(int beg, int end) + { + if (end == beg) + { + return; + } + else + { + int pivot = SortRangeColumns(beg, end); + if (pivot > beg) + SortingRangeColumns(beg, pivot - 1); + if (pivot < end) + SortingRangeColumns(pivot + 1, end); + } + } + #endregion + + public new IXLRange CopyTo(IXLCell target) + { + base.CopyTo(target); + + Int32 lastRowNumber = target.Address.RowNumber + this.RowCount() - 1; + if (lastRowNumber > XLWorksheet.MaxNumberOfRows) lastRowNumber = XLWorksheet.MaxNumberOfRows; + Int32 lastColumnNumber = target.Address.ColumnNumber + this.ColumnCount() - 1; + if (lastColumnNumber > XLWorksheet.MaxNumberOfColumns) lastColumnNumber = XLWorksheet.MaxNumberOfColumns; + + return target.Worksheet.Range(target.Address.RowNumber, target.Address.ColumnNumber, + lastRowNumber, lastColumnNumber); + } + public new IXLRange CopyTo(IXLRangeBase target) + { + base.CopyTo(target); + + Int32 lastRowNumber = target.RangeAddress.FirstAddress.RowNumber + this.RowCount() - 1; + if (lastRowNumber > XLWorksheet.MaxNumberOfRows) lastRowNumber = XLWorksheet.MaxNumberOfRows; + Int32 lastColumnNumber = target.RangeAddress.FirstAddress.ColumnNumber + this.ColumnCount() - 1; + if (lastColumnNumber > XLWorksheet.MaxNumberOfColumns) lastColumnNumber = XLWorksheet.MaxNumberOfColumns; + + return (target as XLRangeBase).Worksheet.Range( + target.RangeAddress.FirstAddress.RowNumber, + target.RangeAddress.FirstAddress.ColumnNumber, + lastRowNumber, + lastColumnNumber); + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index 7f060e7..e4a2b34 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -92,7 +92,7 @@ else { IXLStyle style = this.Style; - if (this.Style.Equals(this.Worksheet.Style)) + if (this.Style != null && this.Style.Equals(this.Worksheet.Style)) { if (this.Worksheet.Internals.RowsCollection.ContainsKey(absoluteAddress.RowNumber) && !this.Worksheet.Internals.RowsCollection[absoluteAddress.RowNumber].Style.Equals(this.Worksheet.Style)) @@ -484,7 +484,7 @@ oldCell = this.Worksheet.Cell(oldKey); } var newCell = new XLCell(newKey, oldCell.Style, Worksheet); - newCell.CopyValues((XLCell)oldCell); + newCell.CopyFrom(oldCell); cellsToInsert.Add(newKey, newCell); cellsToDelete.Add(oldKey); if (oldKey.RowNumber < firstRow + numberOfRows) @@ -504,7 +504,7 @@ var newRow = c.Key.RowNumber + numberOfRows; var newKey = new XLAddress(newRow, c.Key.ColumnNumber, false, false); var newCell = new XLCell(newKey, c.Value.Style, Worksheet); - newCell.CopyValues(c.Value); + newCell.CopyFrom(c.Value); cellsToInsert.Add(newKey, newCell); cellsToDelete.Add(c.Key); if (c.Key.RowNumber < firstRow + numberOfRows) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs index 7e31636..24b336e 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs @@ -16,6 +16,11 @@ Worksheet.RangeShiftedColumns += new RangeShiftedColumnsDelegate(Worksheet_RangeShiftedColumns); this.defaultStyle = new XLStyle(this, xlRangeParameters.DefaultStyle); } + public XLRangeColumn(XLRangeParameters xlRangeParameters, Boolean quick) + : base(xlRangeParameters.RangeAddress) + { + Worksheet = xlRangeParameters.Worksheet; + } void Worksheet_RangeShiftedColumns(XLRange range, int columnsShifted) { @@ -99,6 +104,115 @@ { return this.RangeAddress.LastAddress.ColumnNumber - this.RangeAddress.FirstAddress.ColumnNumber + 1; } + + public Int32 CompareTo(XLRangeColumn otherColumn, IXLSortElements rowsToSort) + { + foreach (var e in rowsToSort) + { + var thisCell = (XLCell)this.Cell(e.ElementNumber); + var otherCell = (XLCell)otherColumn.Cell(e.ElementNumber); + Int32 comparison; + Boolean thisCellIsBlank = StringExtensions.IsNullOrWhiteSpace(thisCell.InnerText); + Boolean otherCellIsBlank = StringExtensions.IsNullOrWhiteSpace(otherCell.InnerText); + if (e.IgnoreBlanks && (thisCellIsBlank || otherCellIsBlank)) + { + if (thisCellIsBlank && otherCellIsBlank) + comparison = 0; + else + { + if (thisCellIsBlank) + comparison = e.SortOrder == XLSortOrder.Ascending ? 1 : -1; + else + comparison = e.SortOrder == XLSortOrder.Ascending ? -1 : 1; + } + } + else + { + if (thisCell.DataType == otherCell.DataType) + { + if (thisCell.DataType == XLCellValues.Text) + { + if (e.MatchCase) + comparison = thisCell.InnerText.CompareTo(otherCell.InnerText); + else + comparison = thisCell.InnerText.ToLower().CompareTo(otherCell.InnerText.ToLower()); + } + else if (thisCell.DataType == XLCellValues.TimeSpan) + { + comparison = thisCell.GetTimeSpan().CompareTo(otherCell.GetTimeSpan()); + } + else + { + comparison = Double.Parse(thisCell.InnerText).CompareTo(Double.Parse(otherCell.InnerText)); + } + } + else + if (e.MatchCase) + comparison = thisCell.GetString().ToLower().CompareTo(otherCell.GetString().ToLower()); + else + comparison = thisCell.GetString().CompareTo(otherCell.GetString()); + } + if (comparison != 0) + { + if (e.SortOrder == XLSortOrder.Ascending) + return comparison; + else + return comparison * -1; + } + } + return 0; + } + + public IXLRangeColumn Sort() + { + this.AsRange().Sort(); + return this; + } + public IXLRangeColumn Sort(XLSortOrder sortOrder) + { + this.AsRange().Sort(sortOrder); + return this; + } + public IXLRangeColumn Sort(Boolean matchCase) + { + this.AsRange().Sort(matchCase); + return this; + } + public IXLRangeColumn Sort(XLSortOrder sortOrder, Boolean matchCase) + { + this.AsRange().Sort(sortOrder, matchCase); + return this; + } + + public new IXLRangeColumn CopyTo(IXLCell target) + { + base.CopyTo(target); + + Int32 lastRowNumber = target.Address.RowNumber + this.RowCount() - 1; + if (lastRowNumber > XLWorksheet.MaxNumberOfRows) lastRowNumber = XLWorksheet.MaxNumberOfRows; + Int32 lastColumnNumber = target.Address.ColumnNumber + this.ColumnCount() - 1; + if (lastColumnNumber > XLWorksheet.MaxNumberOfColumns) lastColumnNumber = XLWorksheet.MaxNumberOfColumns; + + return target.Worksheet.Range(target.Address.RowNumber, target.Address.ColumnNumber, + lastRowNumber, lastColumnNumber) + .Column(1); + } + public new IXLRangeColumn CopyTo(IXLRangeBase target) + { + base.CopyTo(target); + + Int32 lastRowNumber = target.RangeAddress.FirstAddress.RowNumber + this.RowCount() - 1; + if (lastRowNumber > XLWorksheet.MaxNumberOfRows) lastRowNumber = XLWorksheet.MaxNumberOfRows; + Int32 lastColumnNumber = target.RangeAddress.FirstAddress.ColumnNumber + this.ColumnCount() - 1; + if (lastColumnNumber > XLWorksheet.MaxNumberOfColumns) lastColumnNumber = XLWorksheet.MaxNumberOfColumns; + + return (target as XLRangeBase).Worksheet.Range( + target.RangeAddress.FirstAddress.RowNumber, + target.RangeAddress.FirstAddress.ColumnNumber, + lastRowNumber, + lastColumnNumber) + .Column(1); + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeParameters.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeParameters.cs index 361f6a1..a6dd5d1 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeParameters.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeParameters.cs @@ -20,6 +20,7 @@ public IXLRangeAddress RangeAddress { get; private set; } public XLWorksheet Worksheet { get; private set; } public IXLStyle DefaultStyle { get; private set; } + public Boolean IgnoreEvents { get; set; } // Private diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs index 72206d5..fa0a8b6 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs @@ -17,6 +17,12 @@ Worksheet.RangeShiftedColumns += new RangeShiftedColumnsDelegate(Worksheet_RangeShiftedColumns); this.defaultStyle = new XLStyle(this, xlRangeParameters.DefaultStyle); } + public XLRangeRow(XLRangeParameters xlRangeParameters, Boolean quick) + : base(xlRangeParameters.RangeAddress) + { + this.RangeParameters = xlRangeParameters; + Worksheet = xlRangeParameters.Worksheet; + } void Worksheet_RangeShiftedColumns(XLRange range, int columnsShifted) { @@ -113,23 +119,113 @@ return this.RangeAddress.LastAddress.ColumnNumber - this.RangeAddress.FirstAddress.ColumnNumber + 1; } - public Int32 CompareTo(XLRangeRow otherRow, List columnsToSort) + public Int32 CompareTo(XLRangeRow otherRow, IXLSortElements columnsToSort) { - foreach (String co in columnsToSort) + foreach (var e in columnsToSort) { - var thisCell = (XLCell)this.Cell(Int32.Parse(co)); - var otherCell = (XLCell)otherRow.Cell(Int32.Parse(co)); + var thisCell = (XLCell)this.Cell(e.ElementNumber); + var otherCell = (XLCell)otherRow.Cell(e.ElementNumber); Int32 comparison; - if (thisCell.DataType == otherCell.DataType) - comparison = thisCell.InnerText.CompareTo(otherCell.InnerText); + Boolean thisCellIsBlank = StringExtensions.IsNullOrWhiteSpace(thisCell.InnerText); + Boolean otherCellIsBlank = StringExtensions.IsNullOrWhiteSpace(otherCell.InnerText); + if (e.IgnoreBlanks && (thisCellIsBlank || otherCellIsBlank)) + { + if (thisCellIsBlank && otherCellIsBlank) + comparison = 0; + else + { + if (thisCellIsBlank) + comparison = e.SortOrder == XLSortOrder.Ascending ? 1 : -1; + else + comparison = e.SortOrder == XLSortOrder.Ascending ? -1 : 1; + } + } else - comparison = thisCell.GetString().CompareTo(otherCell.GetString()); - + { + if (thisCell.DataType == otherCell.DataType) + { + if (thisCell.DataType == XLCellValues.Text) + { + if (e.MatchCase) + comparison = thisCell.InnerText.CompareTo(otherCell.InnerText); + else + comparison = thisCell.InnerText.ToLower().CompareTo(otherCell.InnerText.ToLower()); + } + else if (thisCell.DataType == XLCellValues.TimeSpan) + { + comparison = thisCell.GetTimeSpan().CompareTo(otherCell.GetTimeSpan()); + } + else + { + comparison = Double.Parse(thisCell.InnerText).CompareTo(Double.Parse(otherCell.InnerText)); + } + } + else + if (e.MatchCase) + comparison = thisCell.GetString().ToLower().CompareTo(otherCell.GetString().ToLower()); + else + comparison = thisCell.GetString().CompareTo(otherCell.GetString()); + } if (comparison != 0) - return comparison; + { + if (e.SortOrder == XLSortOrder.Ascending) + return comparison; + else + return comparison * -1; + } } return 0; } + + public IXLRangeRow Sort() + { + this.AsRange().Sort(XLSortOrientation.LeftToRight); + return this; + } + public IXLRangeRow Sort(XLSortOrder sortOrder) + { + this.AsRange().Sort(XLSortOrientation.LeftToRight, sortOrder); + return this; + } + public IXLRangeRow Sort(Boolean matchCase) + { + this.AsRange().Sort(XLSortOrientation.LeftToRight, matchCase); + return this; + } + public IXLRangeRow Sort(XLSortOrder sortOrder, Boolean matchCase) + { + this.AsRange().Sort(XLSortOrientation.LeftToRight, sortOrder, matchCase); + return this; + } + + public new IXLRangeRow CopyTo(IXLCell target) + { + base.CopyTo(target); + + Int32 lastRowNumber = target.Address.RowNumber + this.RowCount() - 1; + if (lastRowNumber > XLWorksheet.MaxNumberOfRows) lastRowNumber = XLWorksheet.MaxNumberOfRows; + Int32 lastColumnNumber = target.Address.ColumnNumber + this.ColumnCount() - 1; + if (lastColumnNumber > XLWorksheet.MaxNumberOfColumns) lastColumnNumber = XLWorksheet.MaxNumberOfColumns; + + return target.Worksheet.Range(target.Address.RowNumber, target.Address.ColumnNumber, + lastRowNumber, lastColumnNumber) + .Row(1); + } + public new IXLRangeRow CopyTo(IXLRangeBase target) + { + base.CopyTo(target); + Int32 lastRowNumber = target.RangeAddress.FirstAddress.RowNumber + this.RowCount() - 1; + if (lastRowNumber > XLWorksheet.MaxNumberOfRows) lastRowNumber = XLWorksheet.MaxNumberOfRows; + Int32 lastColumnNumber = target.RangeAddress.LastAddress.ColumnNumber + this.ColumnCount() - 1; + if (lastColumnNumber > XLWorksheet.MaxNumberOfColumns) lastColumnNumber = XLWorksheet.MaxNumberOfColumns; + + return (target as XLRangeBase).Worksheet.Range( + target.RangeAddress.FirstAddress.RowNumber, + target.RangeAddress.LastAddress.ColumnNumber, + lastRowNumber, + lastColumnNumber) + .Row(1); + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs index 243907a..e918382 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs @@ -150,10 +150,19 @@ Int32 CellCount(); - void CopyTo(IXLCell target); - void CopyTo(IXLRangeBase target); + IXLRangeRow CopyTo(IXLCell cell); + IXLRangeRow CopyTo(IXLRangeBase range); + IXLRow CopyTo(IXLRow row); void SetAutoFilter(); void SetAutoFilter(Boolean autoFilter); + + IXLRow Sort(); + IXLRow Sort(Boolean matchCase); + IXLRow Sort(XLSortOrder sortOrder); + IXLRow Sort(XLSortOrder sortOrder, Boolean matchCase); + + IXLRangeRow Row(Int32 start, Int32 end); + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs index 142920f..072ea7b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs @@ -494,5 +494,92 @@ { return this.RangeAddress.LastAddress.ColumnNumber - this.RangeAddress.FirstAddress.ColumnNumber + 1; } + + public IXLRow Sort() + { + this.RangeUsed().Sort(XLSortOrientation.LeftToRight); + return this; + } + public IXLRow Sort(XLSortOrder sortOrder) + { + this.RangeUsed().Sort(XLSortOrientation.LeftToRight, sortOrder); + return this; + } + public IXLRow Sort(Boolean matchCase) + { + this.AsRange().Sort(XLSortOrientation.LeftToRight, matchCase); + return this; + } + public IXLRow Sort(XLSortOrder sortOrder, Boolean matchCase) + { + this.AsRange().Sort(XLSortOrientation.LeftToRight, sortOrder, matchCase); + return this; + } + + private void CopyToCell(IXLRangeRow rngRow, IXLCell cell) + { + Int32 cellCount = rngRow.CellCount(); + Int32 roStart = cell.Address.RowNumber; + Int32 coStart = cell.Address.ColumnNumber; + for (Int32 co = coStart; co <= cellCount + coStart - 1; co++) + { + (cell.Worksheet.Cell(roStart, co) as XLCell).CopyFrom(rngRow.Cell(co - coStart + 1)); + } + } + + public new IXLRangeRow CopyTo(IXLCell target) + { + var rngUsed = RangeUsed().Row(1); + CopyToCell(rngUsed, target); + + Int32 lastColumnNumber = target.Address.ColumnNumber + rngUsed.CellCount() - 1; + if (lastColumnNumber > XLWorksheet.MaxNumberOfColumns) lastColumnNumber = XLWorksheet.MaxNumberOfColumns; + + return target.Worksheet.Range( + target.Address.RowNumber, + target.Address.ColumnNumber, + target.Address.RowNumber, + lastColumnNumber) + .Row(1); + } + public new IXLRangeRow CopyTo(IXLRangeBase target) + { + var thisRangeUsed = RangeUsed(); + Int32 thisColumnCount = thisRangeUsed.ColumnCount(); + var targetRangeUsed = target.AsRange().RangeUsed(); + Int32 targetColumnCount = targetRangeUsed.ColumnCount(); + Int32 maxColumn = thisColumnCount > targetColumnCount ? thisColumnCount : targetColumnCount; + + CopyToCell(this.Range(1, 1, 1, maxColumn).Row(1), target.FirstCell()); + + Int32 lastColumnNumber = target.RangeAddress.LastAddress.ColumnNumber + maxColumn - 1; + if (lastColumnNumber > XLWorksheet.MaxNumberOfColumns) lastColumnNumber = XLWorksheet.MaxNumberOfColumns; + + return (target as XLRangeBase).Worksheet.Range( + target.RangeAddress.FirstAddress.RowNumber, + target.RangeAddress.LastAddress.ColumnNumber, + target.RangeAddress.FirstAddress.RowNumber, + lastColumnNumber) + .Row(1); + } + public IXLRow CopyTo(IXLRow row) + { + var thisRangeUsed = RangeUsed(); + Int32 thisColumnCount = thisRangeUsed.ColumnCount(); + //var targetRangeUsed = column target.AsRange().RangeUsed(); + Int32 targetColumnCount = row.LastCellUsed(true).Address.ColumnNumber; + Int32 maxColumn = thisColumnCount > targetColumnCount ? thisColumnCount : targetColumnCount; + + CopyToCell(this.Row(1, maxColumn), row.FirstCell()); + var newRow = row as XLRow; + newRow.height = height; + newRow.style = new XLStyle(newRow, Style); + return newRow; + } + + public IXLRangeRow Row(Int32 start, Int32 end) + { + return this.AsRange().Range(1, start, 1, end).Row(1); + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRowCollection.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRowCollection.cs index 3db4abb..354d1cc 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRowCollection.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRowCollection.cs @@ -19,11 +19,10 @@ foreach (var ro in dictionary.Keys.Where(k => k >= startingRow).OrderByDescending(k => k)) { var rowToMove = dictionary[ro]; - var newRow = ro + rowsToShift; + Int32 newRow = ro + rowsToShift; if (newRow <= XLWorksheet.MaxNumberOfRows) { - var xlRowParameters = new XLRowParameters(rowToMove.Worksheet, rowToMove.Style, false); - dictionary.Add(newRow, new XLRow(newRow, xlRowParameters)); + dictionary.Add(newRow, new XLRow(rowToMove, rowToMove.Worksheet)); } dictionary.Remove(ro); } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTable.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTable.cs index 8c8a767..0d8f3e3 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTable.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTable.cs @@ -68,7 +68,7 @@ TableStyleDark2, TableStyleDark1 } - public interface IXLTable: IXLRange + public interface IXLTable: IXLRangeBase { String Name { get; set; } Boolean EmphasizeFirstColumn { get; set; } @@ -86,43 +86,223 @@ /// /// Gets the first data row of the table. /// - new IXLTableRow FirstRow(); + IXLTableRow FirstRow(); /// /// Gets the first data row of the table that contains a cell with a value. /// - new IXLTableRow FirstRowUsed(); + IXLTableRow FirstRowUsed(); /// /// Gets the last data row of the table. /// - new IXLTableRow LastRow(); + IXLTableRow LastRow(); /// /// Gets the last data row of the table that contains a cell with a value. /// - new IXLTableRow LastRowUsed(); + IXLTableRow LastRowUsed(); /// /// Gets the specified row of the table data. /// /// The table row. - new IXLTableRow Row(int row); + IXLTableRow Row(int row); /// /// Gets a collection of all data rows in this table. /// - new IXLTableRows Rows(); + IXLTableRows Rows(); /// /// Gets a collection of the specified data rows in this table. /// /// The first row to return. /// The last row to return. /// - new IXLTableRows Rows(int firstRow, int lastRow); + IXLTableRows Rows(int firstRow, int lastRow); /// /// Gets a collection of the specified data rows in this table, separated by commas. /// e.g. Rows("4:5"), Rows("7:8,10:11"), Rows("13") /// /// The rows to return. - new IXLTableRows Rows(string rows); + IXLTableRows Rows(string rows); - void CopyTo(IXLCell target); - void CopyTo(IXLRangeBase target); + IXLRange Sort(); + IXLRange Sort(Boolean matchCase); + IXLRange Sort(XLSortOrder sortOrder); + IXLRange Sort(XLSortOrder sortOrder, Boolean matchCase); + IXLRange Sort(String columnsToSortBy); + IXLRange Sort(String columnsToSortBy, Boolean matchCase); + + /// + /// Gets the cell at the specified row and column. + /// The cell address is relative to the parent range. + /// + /// The cell's row. + /// The cell's column. + IXLCell Cell(int row, int column); + + /// Gets the cell at the specified address. + /// The cell address is relative to the parent range. + /// The cell address in the parent range. + IXLCell Cell(string cellAddressInRange); + + /// + /// Gets the cell at the specified row and column. + /// The cell address is relative to the parent range. + /// + /// The cell's row. + /// The cell's column. + IXLCell Cell(int row, string column); + /// Gets the cell at the specified address. + /// The cell address is relative to the parent range. + /// The cell address in the parent range. + IXLCell Cell(IXLAddress cellAddressInRange); + + /// + /// Gets the specified column of the range. + /// + /// The range column. + IXLRangeColumn Column(int column); + /// + /// Gets the specified column of the range. + /// + /// The range column. + IXLRangeColumn Column(string column); + /// + /// Gets the first column of the range. + /// + IXLRangeColumn FirstColumn(); + /// + /// Gets the first column of the range that contains a cell with a value. + /// + IXLRangeColumn FirstColumnUsed(); + /// + /// Gets the last column of the range. + /// + IXLRangeColumn LastColumn(); + /// + /// Gets the last column of the range that contains a cell with a value. + /// + IXLRangeColumn LastColumnUsed(); + /// + /// Gets a collection of all columns in this range. + /// + IXLRangeColumns Columns(); + /// + /// Gets a collection of the specified columns in this range. + /// + /// The first column to return. + /// The last column to return. + IXLRangeColumns Columns(int firstColumn, int lastColumn); + /// + /// Gets a collection of the specified columns in this range. + /// + /// The first column to return. + /// The last column to return. + IXLRangeColumns Columns(string firstColumn, string lastColumn); + /// + /// Gets a collection of the specified columns in this range, separated by commas. + /// e.g. Columns("G:H"), Columns("10:11,13:14"), Columns("P:Q,S:T"), Columns("V") + /// + /// The columns to return. + IXLRangeColumns Columns(string columns); + + /// + /// Returns the specified range. + /// + /// The range boundaries. + IXLRange Range(IXLRangeAddress rangeAddress); + + /// Returns the specified range. + /// e.g. Range("A1"), Range("A1:C2") + /// The range boundaries. + IXLRange Range(string rangeAddress); + + /// Returns the specified range. + /// The first cell in the range. + /// The last cell in the range. + IXLRange Range(IXLCell firstCell, IXLCell lastCell); + + /// Returns the specified range. + /// The first cell address in the range. + /// The last cell address in the range. + IXLRange Range(string firstCellAddress, string lastCellAddress); + + /// Returns the specified range. + /// The first cell address in the range. + /// The last cell address in the range. + IXLRange Range(IXLAddress firstCellAddress, IXLAddress lastCellAddress); + + /// Returns a collection of ranges, separated by commas. + /// e.g. Ranges("A1"), Ranges("A1:C2"), Ranges("A1:B2,D1:D4") + /// The ranges to return. + IXLRanges Ranges(string ranges); + + /// Returns the specified range. + /// The first cell's row of the range to return. + /// The first cell's column of the range to return. + /// The last cell's row of the range to return. + /// The last cell's column of the range to return. + /// . + IXLRange Range(int firstCellRow, int firstCellColumn, int lastCellRow, int lastCellColumn); + + /// Gets the number of rows in this range. + int RowCount(); + + /// Gets the number of columns in this range. + int ColumnCount(); + + /// + /// Inserts X number of columns to the right of this range. + /// All cells to the right of this range will be shifted X number of columns. + /// + /// Number of columns to insert. + IXLRangeColumns InsertColumnsAfter(int numberOfColumns); + IXLRangeColumns InsertColumnsAfter(int numberOfColumns, Boolean expandRange); + /// + /// Inserts X number of columns to the left of this range. + /// This range and all cells to the right of this range will be shifted X number of columns. + /// + /// Number of columns to insert. + IXLRangeColumns InsertColumnsBefore(int numberOfColumns); + IXLRangeColumns InsertColumnsBefore(int numberOfColumns, Boolean expandRange); + /// + /// Inserts X number of rows on top of this range. + /// This range and all cells below this range will be shifted X number of rows. + /// + /// Number of rows to insert. + IXLRangeRows InsertRowsAbove(int numberOfRows); + IXLRangeRows InsertRowsAbove(int numberOfRows, Boolean expandRange); + /// + /// Inserts X number of rows below this range. + /// All cells below this range will be shifted X number of rows. + /// + /// Number of rows to insert. + IXLRangeRows InsertRowsBelow(int numberOfRows); + IXLRangeRows InsertRowsBelow(int numberOfRows, Boolean expandRange); + + /// + /// Deletes this range and shifts the surrounding cells accordingly. + /// + /// How to shift the surrounding cells. + void Delete(XLShiftDeletedCells shiftDeleteCells); + + /// + /// Transposes the contents and styles of all cells in this range. + /// + /// How to handle the surrounding cells when transposing the range. + void Transpose(XLTransposeOptions transposeOption); + + IXLTable AsTable(); + IXLTable AsTable(String name); + IXLTable CreateTable(); + IXLTable CreateTable(String name); + + IXLRange RangeUsed(); + + void CopyTo(IXLCell target); + void CopyTo(IXLRangeBase target); + + void SetAutoFilter(); + void SetAutoFilter(Boolean autoFilter); + + IXLSortElements SortRows { get; } + IXLSortElements SortColumns { get; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRow.cs index 18d738e..ee747c3 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRow.cs @@ -9,5 +9,10 @@ { IXLCell Field(Int32 index); IXLCell Field(String name); + + new IXLTableRow Sort(); + new IXLTableRow Sort(Boolean matchCase); + new IXLTableRow Sort(XLSortOrder sortOrder); + new IXLTableRow Sort(XLSortOrder sortOrder, Boolean matchCase); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs index ffb7505..645f178 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs @@ -15,6 +15,7 @@ public Boolean ShowAutoFilter { get; set; } public XLTableTheme Theme { get; set; } + private String name; public String Name { @@ -316,5 +317,37 @@ } } } + + public new IXLRange Sort(String elementsToSortBy) + { + StringBuilder toSortBy = new StringBuilder(); + foreach (String coPair in elementsToSortBy.Split(',')) + { + String coPairTrimmed = coPair.Trim(); + String coString; + String order; + if (coPairTrimmed.Contains(' ')) + { + var pair = coPairTrimmed.Split(' '); + coString = pair[0]; + order = pair[1]; + } + else + { + coString = coPairTrimmed; + order = "ASC"; + } + + Int32 co; + if (!Int32.TryParse(coString, out co)) + co = this.Field(coString).Index + 1; + + toSortBy.Append(co); + toSortBy.Append(" "); + toSortBy.Append(order); + toSortBy.Append(","); + } + return DataRange.Sort(toSortBy.ToString(0, toSortBy.Length - 1)); + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRow.cs index 82aa691..06f123a 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRow.cs @@ -24,5 +24,26 @@ Int32 fieldIndex = table.GetFieldIndex(name); return Cell(fieldIndex + 1); } + + public new IXLTableRow Sort() + { + this.AsRange().Sort(XLSortOrientation.LeftToRight); + return this; + } + public new IXLTableRow Sort(XLSortOrder sortOrder) + { + this.AsRange().Sort(XLSortOrientation.LeftToRight, sortOrder); + return this; + } + public new IXLTableRow Sort(Boolean matchCase) + { + this.AsRange().Sort(XLSortOrientation.LeftToRight, matchCase); + return this; + } + public new IXLTableRow Sort(XLSortOrder sortOrder, Boolean matchCase) + { + this.AsRange().Sort(XLSortOrientation.LeftToRight, sortOrder, matchCase); + return this; + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index 9390c89..5908f58 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -562,7 +562,6 @@ { String tooltip = hl.Tooltip != null ? tooltip = hl.Tooltip.Value : tooltip = String.Empty; var xlRange = ws.Range(hl.Reference.Value); - //var xlCell = (XLCell)ws.CellFast(hl.Reference.Value); foreach (XLCell xlCell in xlRange.Cells()) { xlCell.SettingHyperlink = true; @@ -694,8 +693,8 @@ ws.PageSetup.ShowComments = showCommentsValues.Single(sc => sc.Value == pageSetup.CellComments).Key; if (pageSetup.Errors != null) ws.PageSetup.PrintErrorValue = printErrorValues.Single(p => p.Value == pageSetup.Errors).Key; - if (pageSetup.HorizontalDpi != null) ws.PageSetup.HorizontalDpi = pageSetup.HorizontalDpi.Value; - if (pageSetup.VerticalDpi != null) ws.PageSetup.VerticalDpi = pageSetup.VerticalDpi.Value; + if (pageSetup.HorizontalDpi != null) ws.PageSetup.HorizontalDpi = (Int32)pageSetup.HorizontalDpi.Value; + if (pageSetup.VerticalDpi != null) ws.PageSetup.VerticalDpi = (Int32)pageSetup.VerticalDpi.Value; if (pageSetup.FirstPageNumber != null) ws.PageSetup.FirstPageNumber = Int32.Parse(pageSetup.FirstPageNumber.InnerText); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index eebf1d7..19ec97e 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -1697,7 +1697,9 @@ worksheetPart.Worksheet.SheetFormatProperties.DefaultRowHeight = xlWorksheet.RowHeight; worksheetPart.Worksheet.SheetFormatProperties.DefaultColumnWidth = xlWorksheet.ColumnWidth; - //worksheetPart.Worksheet.SheetFormatProperties.CustomHeight = true; + if (xlWorksheet.RowHeightChanged) + worksheetPart.Worksheet.SheetFormatProperties.CustomHeight = true; + if (maxOutlineColumn > 0) worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelColumn = (byte)maxOutlineColumn; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index d687029..1c957b6 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -39,7 +39,8 @@ PageSetup = new XLPageSetup(workbook.PageOptions, this); Outline = new XLOutline(workbook.Outline); ColumnWidth = workbook.ColumnWidth; - RowHeight = workbook.RowHeight; + rowHeight = workbook.RowHeight; + RowHeightChanged = workbook.RowHeight != XLWorkbook.DefaultRowHeight; this.Name = sheetName; RangeShiftedRows += new RangeShiftedRowsDelegate(XLWorksheet_RangeShiftedRows); RangeShiftedColumns += new RangeShiftedColumnsDelegate(XLWorksheet_RangeShiftedColumns); @@ -152,7 +153,20 @@ #endregion public Double ColumnWidth { get; set; } - public Double RowHeight { get; set; } + internal Boolean RowHeightChanged { get; set; } + Double rowHeight; + public Double RowHeight + { + get + { + return rowHeight; + } + set + { + RowHeightChanged = true; + rowHeight = value; + } + } private String name; public String Name @@ -704,5 +718,94 @@ } public IXLRangeBase AutoFilterRange { get; set; } + + IXLSortElements sortRows; + public IXLSortElements SortRows + { + get + { + if (sortRows == null) sortRows = new XLSortElements(); + return sortRows; + } + } + + IXLSortElements sortColumns; + public IXLSortElements SortColumns + { + get + { + if (sortColumns == null) sortColumns = new XLSortElements(); + return sortColumns; + } + } + + public IXLRange Sort() + { + var range = GetRangeForSort(); + return range.Sort(); + } + public IXLRange Sort(Boolean matchCase) + { + var range = GetRangeForSort(); + return range.Sort(matchCase); + } + public IXLRange Sort(XLSortOrder sortOrder) + { + var range = GetRangeForSort(); + return range.Sort(sortOrder); + } + public IXLRange Sort(XLSortOrder sortOrder, Boolean matchCase) + { + var range = GetRangeForSort(); + return range.Sort(sortOrder, matchCase); + } + public IXLRange Sort(String columnsToSortBy) + { + var range = GetRangeForSort(); + return range.Sort(columnsToSortBy); + } + public IXLRange Sort(String columnsToSortBy, Boolean matchCase) + { + var range = GetRangeForSort(); + return range.Sort(columnsToSortBy, matchCase); + } + public IXLRange Sort(XLSortOrientation sortOrientation) + { + var range = GetRangeForSort(); + return range.Sort(sortOrientation); + } + public IXLRange Sort(XLSortOrientation sortOrientation, Boolean matchCase) + { + var range = GetRangeForSort(); + return range.Sort(sortOrientation, matchCase); + } + public IXLRange Sort(XLSortOrientation sortOrientation, XLSortOrder sortOrder) + { + var range = GetRangeForSort(); + return range.Sort(sortOrientation, sortOrder); + } + public IXLRange Sort(XLSortOrientation sortOrientation, XLSortOrder sortOrder, Boolean matchCase) + { + var range = GetRangeForSort(); + return range.Sort(sortOrientation, sortOrder, matchCase); + } + public IXLRange Sort(XLSortOrientation sortOrientation, String elementsToSortBy) + { + var range = GetRangeForSort(); + return range.Sort(sortOrientation, elementsToSortBy); + } + public IXLRange Sort(XLSortOrientation sortOrientation, String elementsToSortBy, Boolean matchCase) + { + var range = GetRangeForSort(); + return range.Sort(sortOrientation, elementsToSortBy, matchCase); + } + + private IXLRange GetRangeForSort() + { + var range = this.RangeUsed(); + SortColumns.ForEach(e => range.SortColumns.Add(e.ElementNumber, e.SortOrder, e.IgnoreBlanks, e.MatchCase)); + SortRows.ForEach(e => range.SortRows.Add(e.ElementNumber, e.SortOrder, e.IgnoreBlanks, e.MatchCase)); + return range; + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Extensions.cs b/ClosedXML/ClosedXML/ClosedXML/Extensions.cs index 5d0028e..a02373f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Extensions.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Extensions.cs @@ -5,6 +5,8 @@ using System.Drawing; using System.Globalization; + +[assembly: CLSCompliantAttribute(true)] namespace ClosedXML { public static class Extensions diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj index e7e95d6..7def1ed 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -102,6 +102,8 @@ + + diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs index 5c703dc..adb8342 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs @@ -73,6 +73,8 @@ new HideSheets().Create(@"C:\Excel Files\Created\HideSheets.xlsx"); new SheetProtection().Create(@"C:\Excel Files\Created\SheetProtection.xlsx"); new AutoFilter().Create(@"C:\Excel Files\Created\AutoFilter.xlsx"); + new Sorting().Create(@"C:\Excel Files\Created\Sorting.xlsx"); + new SortExample().Create(@"C:\Excel Files\Created\SortExample.xlsx"); } } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/SortExample.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/SortExample.cs new file mode 100644 index 0000000..6d8a388 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/SortExample.cs @@ -0,0 +1,232 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; + +using System.Drawing; + +namespace ClosedXML_Examples.Misc +{ + public class SortExample + { + #region Variables + + // Public + + // Private + + + #endregion + + #region Properties + + // Public + + // Private + + // Override + + + #endregion + + #region Events + + // Public + + // Private + + // Override + + + #endregion + + #region Methods + + // Public + public void Create(String filePath) + { + var wb = new XLWorkbook(); + + #region Sort a table + var wsTable = wb.Worksheets.Add("Table"); + AddTestTable(wsTable); + var header = wsTable.Row(1).InsertRowsAbove(1).First(); + for(Int32 co = 1; co <= wsTable.LastColumnUsed().ColumnNumber(); co++) + { + header.Cell(co).Value = "Column" + co.ToString(); + } + var rangeTable = wsTable.RangeUsed(); + var table = rangeTable.CopyTo(wsTable.Column(wsTable.LastColumnUsed().ColumnNumber() + 3)).CreateTable(); + + table.Sort("Column2, Column3 Desc, Column1 ASC"); + + wsTable.Row(1).InsertRowsAbove(2); + wsTable.Cell(1, 1).SetValue(".Sort(\"Column2, Column3 Desc, Column1 ASC\") = Sort table Top to Bottom, Col 2 Asc, Col 3 Desc, Col 1 Asc, Ignore Blanks, Ignore Case").Style.Font.SetBold(); + #endregion + + #region Sort a simple range left to right + var wsLeftToRight = wb.Worksheets.Add("Sort Left to Right"); + AddTestTable(wsLeftToRight); + wsLeftToRight.RangeUsed().Transpose(XLTransposeOptions.MoveCells); + var rangeLeftToRight = wsLeftToRight.RangeUsed(); + var copyLeftToRight = rangeLeftToRight.CopyTo(wsLeftToRight.Row(wsLeftToRight.LastRowUsed().RowNumber() + 3)); + + copyLeftToRight.Sort(XLSortOrientation.LeftToRight); + + wsLeftToRight.Row(1).InsertRowsAbove(2); + wsLeftToRight.Cell(1, 1).SetValue(".Sort(XLSortOrientation.LeftToRight) = Sort Range Left to Right, Ascendingly, Ignore Blanks, Ignore Case").Style.Font.SetBold(); + #endregion + + #region Sort a range + var wsComplex2 = wb.Worksheets.Add("Complex 2"); + AddTestTable(wsComplex2); + var rangeComplex2 = wsComplex2.RangeUsed(); + var copyComplex2 = rangeComplex2.CopyTo(wsComplex2.Column(wsComplex2.LastColumnUsed().ColumnNumber() + 3)); + + copyComplex2.SortColumns.Add(1, XLSortOrder.Ascending, false, true); + copyComplex2.SortColumns.Add(3, XLSortOrder.Descending); + copyComplex2.Sort(); + + wsComplex2.Row(1).InsertRowsAbove(4); + wsComplex2.Cell(1, 1) + .SetValue(".SortColumns.Add(1, XLSortOrder.Ascending, false, true) = Sort Col 1 Asc, Match Blanks, Match Case").Style.Font.SetBold(); + wsComplex2.Cell(2, 1) + .SetValue(".SortColumns.Add(3, XLSortOrder.Descending) = Sort Col 3 Desc, Ignore Blanks, Ignore Case").Style.Font.SetBold(); + wsComplex2.Cell(3, 1) + .SetValue(".Sort() = Sort range using the parameters defined in SortColumns").Style.Font.SetBold(); + #endregion + + #region Sort a range + var wsComplex1 = wb.Worksheets.Add("Complex 1"); + AddTestTable(wsComplex1); + var rangeComplex1 = wsComplex1.RangeUsed(); + var copyComplex1 = rangeComplex1.CopyTo(wsComplex1.Column(wsComplex1.LastColumnUsed().ColumnNumber() + 3)); + + copyComplex1.Sort("2, 1 DESC", true); + + wsComplex1.Row(1).InsertRowsAbove(2); + wsComplex1.Cell(1, 1) + .SetValue(".Sort(\"2, 1 DESC\", true) = Sort Range Top to Bottom, Col 2 Asc, Col 1 Desc, Ignore Blanks, Match Case").Style.Font.SetBold(); + #endregion + + #region Sort a simple column + var wsSimpleColumn = wb.Worksheets.Add("Simple Column"); + AddTestColumn(wsSimpleColumn); + var rangeSimpleColumn = wsSimpleColumn.RangeUsed(); + var copySimpleColumn = rangeSimpleColumn.CopyTo(wsSimpleColumn.Column(wsSimpleColumn.LastColumnUsed().ColumnNumber() + 3)); + + copySimpleColumn.Sort(XLSortOrder.Descending, true); + + wsSimpleColumn.Row(1).InsertRowsAbove(2); + wsSimpleColumn.Cell(1, 1) + .SetValue(".Sort(XLSortOrder.Descending, true) = Sort Range Top to Bottom, Descendingly, Ignore Blanks, Match Case").Style.Font.SetBold(); + #endregion + + #region Sort a simple range + var wsSimple = wb.Worksheets.Add("Simple"); + AddTestTable(wsSimple); + var rangeSimple = wsSimple.RangeUsed(); + var copySimple = rangeSimple.CopyTo(wsSimple.Column(wsSimple.LastColumnUsed().ColumnNumber() + 3)); + + copySimple.Sort(); + + wsSimple.Row(1).InsertRowsAbove(2); + wsSimple.Cell(1, 1).SetValue(".Sort() = Sort Range Top to Bottom, Ascendingly, Ignore Blanks, Ignore Case").Style.Font.SetBold(); + #endregion + + wb.SaveAs(filePath); + } + + private void AddTestColumnMixed(IXLWorksheet ws) + { + ws.Cell("A1").SetValue(new DateTime(2011, 1, 30)).Style.Fill.SetBackgroundColor(XLColor.LightGreen); + ws.Cell("A2").SetValue(1.15).Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise); + ws.Cell("A3").SetValue(new TimeSpan(1, 1, 12, 30)).Style.Fill.SetBackgroundColor(XLColor.BurlyWood); + ws.Cell("A4").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkGray); + ws.Cell("A5").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon); + ws.Cell("A6").SetValue(9).Style.Fill.SetBackgroundColor(XLColor.DodgerBlue); + ws.Cell("A7").SetValue(new TimeSpan(9, 4, 30)).Style.Fill.SetBackgroundColor(XLColor.IndianRed); + ws.Cell("A8").SetValue(new DateTime(2011, 4, 15)).Style.Fill.SetBackgroundColor(XLColor.DeepPink); + } + private void AddTestColumnNumbers(IXLWorksheet ws) + { + ws.Cell("A1").SetValue(1.30).Style.Fill.SetBackgroundColor(XLColor.LightGreen); + ws.Cell("A2").SetValue(1.15).Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise); + ws.Cell("A3").SetValue(1230).Style.Fill.SetBackgroundColor(XLColor.BurlyWood); + ws.Cell("A4").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkGray); + ws.Cell("A5").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon); + ws.Cell("A6").SetValue(9).Style.Fill.SetBackgroundColor(XLColor.DodgerBlue); + ws.Cell("A7").SetValue(4.30).Style.Fill.SetBackgroundColor(XLColor.IndianRed); + ws.Cell("A8").SetValue(4.15).Style.Fill.SetBackgroundColor(XLColor.DeepPink); + } + private void AddTestColumnTimeSpans(IXLWorksheet ws) + { + ws.Cell("A1").SetValue(new TimeSpan(0, 12, 35, 21)).Style.Fill.SetBackgroundColor(XLColor.LightGreen); + ws.Cell("A2").SetValue(new TimeSpan(45, 1, 15)).Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise); + ws.Cell("A3").SetValue(new TimeSpan(1, 1, 12, 30)).Style.Fill.SetBackgroundColor(XLColor.BurlyWood); + ws.Cell("A4").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkGray); + ws.Cell("A5").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon); + ws.Cell("A6").SetValue(new TimeSpan(0, 12, 15)).Style.Fill.SetBackgroundColor(XLColor.DodgerBlue); + ws.Cell("A7").SetValue(new TimeSpan(1, 4, 30)).Style.Fill.SetBackgroundColor(XLColor.IndianRed); + ws.Cell("A8").SetValue(new TimeSpan(1, 4, 15)).Style.Fill.SetBackgroundColor(XLColor.DeepPink); + } + private void AddTestColumnDates(IXLWorksheet ws) + { + ws.Cell("A1").SetValue(new DateTime(2011, 1, 30)).Style.Fill.SetBackgroundColor(XLColor.LightGreen); + ws.Cell("A2").SetValue(new DateTime(2011, 1, 15)).Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise); + ws.Cell("A3").SetValue(new DateTime(2011, 12, 30)).Style.Fill.SetBackgroundColor(XLColor.BurlyWood); + ws.Cell("A4").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkGray); + ws.Cell("A5").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon); + ws.Cell("A6").SetValue(new DateTime(2011, 12, 15)).Style.Fill.SetBackgroundColor(XLColor.DodgerBlue); + ws.Cell("A7").SetValue(new DateTime(2011, 4, 30)).Style.Fill.SetBackgroundColor(XLColor.IndianRed); + ws.Cell("A8").SetValue(new DateTime(2011, 4, 15)).Style.Fill.SetBackgroundColor(XLColor.DeepPink); + } + private void AddTestColumn(IXLWorksheet ws) + { + ws.Cell("A1").SetValue("B").Style.Fill.SetBackgroundColor(XLColor.LightGreen); + ws.Cell("A2").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise); + ws.Cell("A3").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.BurlyWood); + ws.Cell("A4").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkGray); + ws.Cell("A5").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon); + ws.Cell("A6").SetValue("b").Style.Fill.SetBackgroundColor(XLColor.DodgerBlue); + ws.Cell("A7").SetValue("B").Style.Fill.SetBackgroundColor(XLColor.IndianRed); + ws.Cell("A8").SetValue("c").Style.Fill.SetBackgroundColor(XLColor.DeepPink); + } + private void AddTestTable(IXLWorksheet ws) + { + ws.Cell("A1").SetValue("B").Style.Fill.SetBackgroundColor(XLColor.LightGreen); + ws.Cell("A2").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise); + ws.Cell("A3").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.BurlyWood); + ws.Cell("A4").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DarkGray); + ws.Cell("A5").SetValue(" ").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon); + ws.Cell("A6").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DodgerBlue); + ws.Cell("A7").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.IndianRed); + ws.Cell("A8").SetValue("B").Style.Fill.SetBackgroundColor(XLColor.DeepPink); + + ws.Cell("B1").SetValue(" ").Style.Fill.SetBackgroundColor(XLColor.LightGreen); + ws.Cell("B2").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise); + ws.Cell("B3").SetValue("B").Style.Fill.SetBackgroundColor(XLColor.BurlyWood); + ws.Cell("B4").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DarkGray); + ws.Cell("B5").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon); + ws.Cell("B6").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DodgerBlue); + ws.Cell("B7").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.IndianRed); + ws.Cell("B8").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.DeepPink); + + ws.Cell("C1").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.LightGreen); + ws.Cell("C2").SetValue(" ").Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise); + ws.Cell("C3").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.BurlyWood); + ws.Cell("C4").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.DarkGray); + ws.Cell("C5").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon); + ws.Cell("C6").SetValue("b").Style.Fill.SetBackgroundColor(XLColor.DodgerBlue); + ws.Cell("C7").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.IndianRed); + ws.Cell("C8").SetValue(" ").Style.Fill.SetBackgroundColor(XLColor.DeepPink); + } + // Private + + // Override + + + #endregion + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/Sorting.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/Sorting.cs new file mode 100644 index 0000000..da4180b --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/Sorting.cs @@ -0,0 +1,261 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; + +using System.Drawing; + +namespace ClosedXML_Examples.Misc +{ + public class Sorting + { + #region Variables + + // Public + + // Private + + + #endregion + + #region Properties + + // Public + + // Private + + // Override + + + #endregion + + #region Events + + // Public + + // Private + + // Override + + + #endregion + + #region Methods + + // Public + public void Create(String filePath) + { + var wb = new XLWorkbook(); + + #region Sort Table + var wsTable = wb.Worksheets.Add("Table"); + AddTestTable(wsTable); + var header = wsTable.Row(1).InsertRowsAbove(1); + Int32 lastCo = wsTable.LastColumnUsed().ColumnNumber(); + for (Int32 co = 1; co <= lastCo; co++) + wsTable.Cell(1, co).Value = "Column" + co.ToString(); + + var table = wsTable.RangeUsed().AsTable(); + table.Sort("Column2 Desc, 1, 3 Asc"); + #endregion + + #region Sort Rows + var wsRows = wb.Worksheets.Add("Rows"); + AddTestTable(wsRows); + wsRows.Row(1).Sort(); + wsRows.RangeUsed().Row(2).Sort(); + wsRows.Rows(3, wsRows.LastRowUsed().RowNumber()).Delete(); + #endregion + + #region Sort Columns + var wsColumns = wb.Worksheets.Add("Columns"); + AddTestTable(wsColumns); + wsColumns.LastColumnUsed().Delete(); + wsColumns.Column(1).Sort(); + wsColumns.RangeUsed().Column(2).Sort(); + #endregion + + #region Sort Mixed + var wsMixed = wb.Worksheets.Add("Mixed"); + AddTestColumnMixed(wsMixed); + wsMixed.Sort(); + #endregion + + #region Sort Numbers + var wsNumbers = wb.Worksheets.Add("Numbers"); + AddTestColumnNumbers(wsNumbers); + wsNumbers.Sort(); + #endregion + + #region Sort TimeSpans + var wsTimeSpans = wb.Worksheets.Add("TimeSpans"); + AddTestColumnTimeSpans(wsTimeSpans); + wsTimeSpans.Sort(); + #endregion + + #region Sort Dates + var wsDates = wb.Worksheets.Add("Dates"); + AddTestColumnDates(wsDates); + wsDates.Sort(); + #endregion + + #region Do Not Ignore Blanks + var wsIncludeBlanks = wb.Worksheets.Add("Include Blanks"); + AddTestTable(wsIncludeBlanks); + var rangeIncludeBlanks = wsIncludeBlanks; + rangeIncludeBlanks.SortColumns.Add(1, XLSortOrder.Ascending, false, true); + rangeIncludeBlanks.SortColumns.Add(2, XLSortOrder.Descending, false, true); + rangeIncludeBlanks.Sort(); + + var wsIncludeBlanksColumn = wb.Worksheets.Add("Include Blanks Column"); + AddTestColumn(wsIncludeBlanksColumn); + var rangeIncludeBlanksColumn = wsIncludeBlanksColumn; + rangeIncludeBlanksColumn.SortColumns.Add(1, XLSortOrder.Ascending, false, true); + rangeIncludeBlanksColumn.Sort(); + + var wsIncludeBlanksColumnDesc = wb.Worksheets.Add("Include Blanks Column Desc"); + AddTestColumn(wsIncludeBlanksColumnDesc); + var rangeIncludeBlanksColumnDesc = wsIncludeBlanksColumnDesc; + rangeIncludeBlanksColumnDesc.SortColumns.Add(1, XLSortOrder.Descending, false, true); + rangeIncludeBlanksColumnDesc.Sort(); + #endregion + + #region Case Sensitive + var wsCaseSensitive = wb.Worksheets.Add("Case Sensitive"); + AddTestTable(wsCaseSensitive); + var rangeCaseSensitive = wsCaseSensitive; + rangeCaseSensitive.SortColumns.Add(1, XLSortOrder.Ascending, true, true); + rangeCaseSensitive.SortColumns.Add(2, XLSortOrder.Descending, true, true); + rangeCaseSensitive.Sort(); + + var wsCaseSensitiveColumn = wb.Worksheets.Add("Case Sensitive Column"); + AddTestColumn(wsCaseSensitiveColumn); + var rangeCaseSensitiveColumn = wsCaseSensitiveColumn; + rangeCaseSensitiveColumn.SortColumns.Add(1, XLSortOrder.Ascending, true, true); + rangeCaseSensitiveColumn.Sort(); + + var wsCaseSensitiveColumnDesc = wb.Worksheets.Add("Case Sensitive Column Desc"); + AddTestColumn(wsCaseSensitiveColumnDesc); + var rangeCaseSensitiveColumnDesc = wsCaseSensitiveColumnDesc; + rangeCaseSensitiveColumnDesc.SortColumns.Add(1, XLSortOrder.Descending, true, true); + rangeCaseSensitiveColumnDesc.Sort(); + #endregion + + #region Simple Sorts + var wsSimple = wb.Worksheets.Add("Simple"); + AddTestTable(wsSimple); + wsSimple.Sort(); + + var wsSimpleDesc = wb.Worksheets.Add("Simple Desc"); + AddTestTable(wsSimpleDesc); + wsSimpleDesc.Sort(XLSortOrder.Descending); + + var wsSimpleColumns = wb.Worksheets.Add("Simple Columns"); + AddTestTable(wsSimpleColumns); + wsSimpleColumns.Sort("2, A DESC, 3"); + + var wsSimpleColumn = wb.Worksheets.Add("Simple Column"); + AddTestColumn(wsSimpleColumn); + wsSimpleColumn.Sort(); + + var wsSimpleColumnDesc = wb.Worksheets.Add("Simple Column Desc"); + AddTestColumn(wsSimpleColumnDesc); + wsSimpleColumnDesc.Sort(XLSortOrder.Descending); + #endregion + + wb.SaveAs(filePath); + } + + private void AddTestColumnMixed(IXLWorksheet ws) + { + ws.Cell("A1").SetValue(new DateTime(2011, 1, 30)).Style.Fill.SetBackgroundColor(XLColor.LightGreen); + ws.Cell("A2").SetValue(1.15).Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise); + ws.Cell("A3").SetValue(new TimeSpan(1, 1, 12, 30)).Style.Fill.SetBackgroundColor(XLColor.BurlyWood); + ws.Cell("A4").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkGray); + ws.Cell("A5").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon); + ws.Cell("A6").SetValue(9).Style.Fill.SetBackgroundColor(XLColor.DodgerBlue); + ws.Cell("A7").SetValue(new TimeSpan(9, 4, 30)).Style.Fill.SetBackgroundColor(XLColor.IndianRed); + ws.Cell("A8").SetValue(new DateTime(2011, 4, 15)).Style.Fill.SetBackgroundColor(XLColor.DeepPink); + } + private void AddTestColumnNumbers(IXLWorksheet ws) + { + ws.Cell("A1").SetValue(1.30).Style.Fill.SetBackgroundColor(XLColor.LightGreen); + ws.Cell("A2").SetValue(1.15).Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise); + ws.Cell("A3").SetValue(1230).Style.Fill.SetBackgroundColor(XLColor.BurlyWood); + ws.Cell("A4").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkGray); + ws.Cell("A5").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon); + ws.Cell("A6").SetValue(9).Style.Fill.SetBackgroundColor(XLColor.DodgerBlue); + ws.Cell("A7").SetValue(4.30).Style.Fill.SetBackgroundColor(XLColor.IndianRed); + ws.Cell("A8").SetValue(4.15).Style.Fill.SetBackgroundColor(XLColor.DeepPink); + } + private void AddTestColumnTimeSpans(IXLWorksheet ws) + { + ws.Cell("A1").SetValue(new TimeSpan(0, 12, 35, 21)).Style.Fill.SetBackgroundColor(XLColor.LightGreen); + ws.Cell("A2").SetValue(new TimeSpan(45, 1, 15)).Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise); + ws.Cell("A3").SetValue(new TimeSpan(1, 1, 12, 30)).Style.Fill.SetBackgroundColor(XLColor.BurlyWood); + ws.Cell("A4").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkGray); + ws.Cell("A5").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon); + ws.Cell("A6").SetValue(new TimeSpan(0, 12, 15)).Style.Fill.SetBackgroundColor(XLColor.DodgerBlue); + ws.Cell("A7").SetValue(new TimeSpan(1, 4, 30)).Style.Fill.SetBackgroundColor(XLColor.IndianRed); + ws.Cell("A8").SetValue(new TimeSpan(1, 4, 15)).Style.Fill.SetBackgroundColor(XLColor.DeepPink); + } + private void AddTestColumnDates(IXLWorksheet ws) + { + ws.Cell("A1").SetValue(new DateTime(2011, 1, 30)).Style.Fill.SetBackgroundColor(XLColor.LightGreen); + ws.Cell("A2").SetValue(new DateTime(2011, 1, 15)).Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise); + ws.Cell("A3").SetValue(new DateTime(2011, 12, 30)).Style.Fill.SetBackgroundColor(XLColor.BurlyWood); + ws.Cell("A4").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkGray); + ws.Cell("A5").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon); + ws.Cell("A6").SetValue(new DateTime(2011, 12, 15)).Style.Fill.SetBackgroundColor(XLColor.DodgerBlue); + ws.Cell("A7").SetValue(new DateTime(2011, 4, 30)).Style.Fill.SetBackgroundColor(XLColor.IndianRed); + ws.Cell("A8").SetValue(new DateTime(2011, 4, 15)).Style.Fill.SetBackgroundColor(XLColor.DeepPink); + } + private void AddTestColumn(IXLWorksheet ws) + { + ws.Cell("A1").SetValue("B").Style.Fill.SetBackgroundColor(XLColor.LightGreen); + ws.Cell("A2").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise); + ws.Cell("A3").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.BurlyWood); + ws.Cell("A4").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkGray); + ws.Cell("A5").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon); + ws.Cell("A6").SetValue("b").Style.Fill.SetBackgroundColor(XLColor.DodgerBlue); + ws.Cell("A7").SetValue("B").Style.Fill.SetBackgroundColor(XLColor.IndianRed); + ws.Cell("A8").SetValue("c").Style.Fill.SetBackgroundColor(XLColor.DeepPink); + } + private void AddTestTable(IXLWorksheet ws) + { + ws.Cell("A1").SetValue("B").Style.Fill.SetBackgroundColor(XLColor.LightGreen); + ws.Cell("A2").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise); + ws.Cell("A3").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.BurlyWood); + ws.Cell("A4").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DarkGray); + ws.Cell("A5").SetValue(" ").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon); + ws.Cell("A6").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DodgerBlue); + ws.Cell("A7").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.IndianRed); + ws.Cell("A8").SetValue("B").Style.Fill.SetBackgroundColor(XLColor.DeepPink); + + ws.Cell("B1").SetValue(" ").Style.Fill.SetBackgroundColor(XLColor.LightGreen); + ws.Cell("B2").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise); + ws.Cell("B3").SetValue("B").Style.Fill.SetBackgroundColor(XLColor.BurlyWood); + ws.Cell("B4").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DarkGray); + ws.Cell("B5").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon); + ws.Cell("B6").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DodgerBlue); + ws.Cell("B7").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.IndianRed); + ws.Cell("B8").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.DeepPink); + + ws.Cell("C1").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.LightGreen); + ws.Cell("C2").SetValue(" ").Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise); + ws.Cell("C3").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.BurlyWood); + ws.Cell("C4").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.DarkGray); + ws.Cell("C5").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon); + ws.Cell("C6").SetValue("b").Style.Fill.SetBackgroundColor(XLColor.DodgerBlue); + ws.Cell("C7").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.IndianRed); + ws.Cell("C8").SetValue(" ").Style.Fill.SetBackgroundColor(XLColor.DeepPink); + } + // Private + + // Override + + + #endregion + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj index fb7862d..2b15d01 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj @@ -295,6 +295,18 @@ Excel\Ranges\IXLRanges.cs + + Excel\Ranges\Sort\IXLSortElement.cs + + + Excel\Ranges\Sort\IXLSortElements.cs + + + Excel\Ranges\Sort\XLSortElement.cs + + + Excel\Ranges\Sort\XLSortElements.cs + Excel\Ranges\XLRange.cs diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs index e9a160c..64d1a66 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs @@ -19,14 +19,15 @@ //var fileName = "Issue_6429"; //var wb = new XLWorkbook(String.Format(@"c:\Excel Files\ForTesting\{0}.xlsx", fileName)); var wb = new XLWorkbook(); - var wsData = wb.Worksheets.Add("Data"); - var xcell = wsData.Cell(1, 1); - xcell.Value = "Test"; - wsData.NamedRanges.Add("RangeName", xcell.AsRange(), "Comment"); + var ws = wb.Worksheets.Add("Sheet1"); + ws.Row(5).Height = 50; + ws.Row(2).InsertRowsAbove(1); wb.SaveAs(String.Format(@"c:\Excel Files\ForTesting\{0}_Saved.xlsx", fileName)); } + + static void CopyWorksheets(String source, XLWorkbook target) { var wb = new XLWorkbook(source); @@ -50,35 +51,42 @@ foreach (var i in Enumerable.Range(1, 1)) { var ws = wb.Worksheets.Add("Sheet" + i); - foreach (var ro in Enumerable.Range(1, 1000)) + foreach (var ro in Enumerable.Range(1, 10000)) { - foreach (var co in Enumerable.Range(1, 100)) + foreach (var co in Enumerable.Range(1, 5)) { - ws.Cell(ro, co).Style = GetRandomStyle(); + //ws.Cell(ro, co).Style = GetRandomStyle(); //if (rnd.Next(1, 5) == 1) - ws.Cell(ro, co).FormulaA1 = ws.Cell(ro + 1, co + 1).Address.ToString() + " & \"-Copy\""; + //ws.Cell(ro, co).FormulaA1 = ws.Cell(ro + 1, co + 1).Address.ToString() + " & \"-Copy\""; //else - // ws.Cell(ro, co).Value = GetRandomValue(); + ws.Cell(ro, co).Value = GetRandomValue(); } //System.Threading.Thread.Sleep(10); } - ws.RangeUsed().Style.Border.BottomBorder = XLBorderStyleValues.DashDot; - ws.RangeUsed().Style.Border.BottomBorderColor = XLColor.AirForceBlue; - ws.RangeUsed().Style.Border.TopBorder = XLBorderStyleValues.DashDotDot; - ws.RangeUsed().Style.Border.TopBorderColor = XLColor.AliceBlue; - ws.RangeUsed().Style.Border.LeftBorder = XLBorderStyleValues.Dashed; - ws.RangeUsed().Style.Border.LeftBorderColor = XLColor.Alizarin; - ws.RangeUsed().Style.Border.RightBorder = XLBorderStyleValues.Dotted; - ws.RangeUsed().Style.Border.RightBorderColor = XLColor.Almond; + //ws.RangeUsed().Style.Border.BottomBorder = XLBorderStyleValues.DashDot; + //ws.RangeUsed().Style.Border.BottomBorderColor = XLColor.AirForceBlue; + //ws.RangeUsed().Style.Border.TopBorder = XLBorderStyleValues.DashDotDot; + //ws.RangeUsed().Style.Border.TopBorderColor = XLColor.AliceBlue; + //ws.RangeUsed().Style.Border.LeftBorder = XLBorderStyleValues.Dashed; + //ws.RangeUsed().Style.Border.LeftBorderColor = XLColor.Alizarin; + //ws.RangeUsed().Style.Border.RightBorder = XLBorderStyleValues.Dotted; + //ws.RangeUsed().Style.Border.RightBorderColor = XLColor.Almond; - ws.RangeUsed().Style.Font.Bold = true; - ws.RangeUsed().Style.Font.FontColor = XLColor.Amaranth; - ws.RangeUsed().Style.Font.FontSize = 10; - ws.RangeUsed().Style.Font.Italic = true; + //ws.RangeUsed().Style.Font.Bold = true; + //ws.RangeUsed().Style.Font.FontColor = XLColor.Amaranth; + //ws.RangeUsed().Style.Font.FontSize = 10; + //ws.RangeUsed().Style.Font.Italic = true; - ws.RangeUsed().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; - ws.RangeUsed().Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; - ws.RangeUsed().Style.Alignment.WrapText = true; + //ws.RangeUsed().Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; + //ws.RangeUsed().Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; + //ws.RangeUsed().Style.Alignment.WrapText = true; + + var startS = DateTime.Now; + ws.Sort(); + var endS = DateTime.Now; + var savedS = (endS - startS).TotalSeconds; + runningSave.Add(savedS); + Console.WriteLine("Sorted in {0} secs.", savedS); } @@ -91,14 +99,14 @@ //Console.WriteLine("Bolded all cells in {0} secs.", (end3 - start3).TotalSeconds); var start = DateTime.Now; - wb.SaveAs(@"C:\Excel Files\ForTesting\Benchmark.xlsx"); + //wb.SaveAs(@"C:\Excel Files\ForTesting\Benchmark.xlsx"); var end = DateTime.Now; var saved = (end - start).TotalSeconds; runningSave.Add(saved); Console.WriteLine("Saved in {0} secs.", saved); var start1 = DateTime.Now; - var wb1 = new XLWorkbook(@"C:\Excel Files\ForTesting\Benchmark.xlsx"); + //var wb1 = new XLWorkbook(@"C:\Excel Files\ForTesting\Benchmark.xlsx"); var end1 = DateTime.Now; var loaded = (end1 - start1).TotalSeconds; runningLoad.Add(loaded);