diff --git a/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs index 18509dd..96cd2db 100644 --- a/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -1928,7 +1928,7 @@ private void SortingRangeRows(int beg, int end) { - if (end == beg) + if (beg == end) return; int pivot = SortRangeRows(beg, end); if (pivot > beg) diff --git a/ClosedXML/Excel/Ranges/XLRangeRow.cs b/ClosedXML/Excel/Ranges/XLRangeRow.cs index d35a77c..aeb54e1 100644 --- a/ClosedXML/Excel/Ranges/XLRangeRow.cs +++ b/ClosedXML/Excel/Ranges/XLRangeRow.cs @@ -262,7 +262,7 @@ break; case XLDataType.Number: - comparison = Double.Parse(thisCell.InnerText, XLHelper.NumberStyle, XLHelper.ParseCulture).CompareTo(Double.Parse(otherCell.InnerText, XLHelper.NumberStyle, XLHelper.ParseCulture)); + comparison = thisCell.GetDouble().CompareTo(otherCell.GetDouble()); break; case XLDataType.Boolean: diff --git a/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/Excel/Tables/XLTable.cs index f97685a..fca772f 100644 --- a/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/Excel/Tables/XLTable.cs @@ -501,19 +501,21 @@ else { coString = coPairTrimmed; - order = "ASC"; + order = sortOrder == XLSortOrder.Ascending ? "ASC" : "DESC"; } Int32 co; if (!Int32.TryParse(coString, out co)) co = Field(coString).Index + 1; + if (toSortBy.Length > 0) + toSortBy.Append(','); + toSortBy.Append(co); - toSortBy.Append(" "); + toSortBy.Append(' '); toSortBy.Append(order); - toSortBy.Append(","); } - return DataRange.Sort(toSortBy.ToString(0, toSortBy.Length - 1), sortOrder, matchCase, ignoreBlanks); + return DataRange.Sort(toSortBy.ToString(), sortOrder, matchCase, ignoreBlanks); } public new IXLTable Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) diff --git a/ClosedXML_Examples/Ranges/Sorting.cs b/ClosedXML_Examples/Ranges/Sorting.cs index 67ab08f..b61c19c 100644 --- a/ClosedXML_Examples/Ranges/Sorting.cs +++ b/ClosedXML_Examples/Ranges/Sorting.cs @@ -1,166 +1,167 @@ -using System; using ClosedXML.Excel; - +using System; namespace ClosedXML_Examples.Misc { public class Sorting : IXLExample { - #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(); + using (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(); + #region Sort Table - var table = wsTable.RangeUsed().AsTable(); - table.Sort("Column2 Desc, 1, 3 Asc"); - #endregion + var wsTable = wb.Worksheets.Add("Table"); + AddTestTable(wsTable); - #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 + 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(); - #region Sort Columns - var wsColumns = wb.Worksheets.Add("Columns"); - AddTestTable(wsColumns); - wsColumns.LastColumnUsed().Delete(); - wsColumns.Column(1).Sort(); - wsColumns.RangeUsed().Column(2).Sort(); - #endregion + var table = wsTable.RangeUsed().AsTable(); + table.Sort("Column2 Desc, 1, 3 Asc"); - #region Sort Mixed - var wsMixed = wb.Worksheets.Add("Mixed"); - AddTestColumnMixed(wsMixed); - wsMixed.Sort(); - #endregion + // Sort table another way + wsTable = wb.Worksheets.Add("Table2"); + AddTestTable(wsTable); - #region Sort Numbers - var wsNumbers = wb.Worksheets.Add("Numbers"); - AddTestColumnNumbers(wsNumbers); - wsNumbers.Sort(); - #endregion + wsTable.Row(1).InsertRowsAbove(1); + lastCo = wsTable.LastColumnUsed().ColumnNumber(); + for (Int32 co = 1; co <= lastCo; co++) + wsTable.Cell(1, co).Value = "Column" + co.ToString(); - #region Sort TimeSpans - var wsTimeSpans = wb.Worksheets.Add("TimeSpans"); - AddTestColumnTimeSpans(wsTimeSpans); - wsTimeSpans.Sort(); - #endregion + table = wsTable.RangeUsed().AsTable(); + table.Sort("Column2", XLSortOrder.Descending, false, true); - #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(); + #endregion Sort Table - var wsIncludeBlanksColumn = wb.Worksheets.Add("Include Blanks Column"); - AddTestColumn(wsIncludeBlanksColumn); - var rangeIncludeBlanksColumn = wsIncludeBlanksColumn; - rangeIncludeBlanksColumn.SortColumns.Add(1, XLSortOrder.Ascending, false, true); - rangeIncludeBlanksColumn.Sort(); + #region Sort Rows - 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 + var wsRows = wb.Worksheets.Add("Rows"); + AddTestTable(wsRows); + wsRows.Row(1).Sort(); + wsRows.RangeUsed().Row(2).Sort(); + wsRows.Rows(3, wsRows.LastRowUsed().RowNumber()).Delete(); - #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(); + #endregion Sort Rows - var wsCaseSensitiveColumn = wb.Worksheets.Add("Case Sensitive Column"); - AddTestColumn(wsCaseSensitiveColumn); - var rangeCaseSensitiveColumn = wsCaseSensitiveColumn; - rangeCaseSensitiveColumn.SortColumns.Add(1, XLSortOrder.Ascending, true, true); - rangeCaseSensitiveColumn.Sort(); + #region Sort Columns - 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 + var wsColumns = wb.Worksheets.Add("Columns"); + AddTestTable(wsColumns); + wsColumns.LastColumnUsed().Delete(); + wsColumns.Column(1).Sort(); + wsColumns.RangeUsed().Column(2).Sort(); - #region Simple Sorts - var wsSimple = wb.Worksheets.Add("Simple"); - AddTestTable(wsSimple); - wsSimple.Sort(); + #endregion Sort Columns - var wsSimpleDesc = wb.Worksheets.Add("Simple Desc"); - AddTestTable(wsSimpleDesc); - wsSimpleDesc.Sort("", XLSortOrder.Descending); + #region Sort Mixed - var wsSimpleColumns = wb.Worksheets.Add("Simple Columns"); - AddTestTable(wsSimpleColumns); - wsSimpleColumns.Sort("2, A DESC, 3"); + var wsMixed = wb.Worksheets.Add("Mixed"); + AddTestColumnMixed(wsMixed); + wsMixed.Sort(); - var wsSimpleColumn = wb.Worksheets.Add("Simple Column"); - AddTestColumn(wsSimpleColumn); - wsSimpleColumn.Sort(); + #endregion Sort Mixed - var wsSimpleColumnDesc = wb.Worksheets.Add("Simple Column Desc"); - AddTestColumn(wsSimpleColumnDesc); - wsSimpleColumnDesc.Sort(1, XLSortOrder.Descending); - #endregion + #region Sort Numbers - wb.SaveAs(filePath); + var wsNumbers = wb.Worksheets.Add("Numbers"); + AddTestColumnNumbers(wsNumbers); + wsNumbers.Sort(); + + #endregion Sort Numbers + + #region Sort TimeSpans + + var wsTimeSpans = wb.Worksheets.Add("TimeSpans"); + AddTestColumnTimeSpans(wsTimeSpans); + wsTimeSpans.Sort(); + + #endregion Sort TimeSpans + + #region Sort Dates + + var wsDates = wb.Worksheets.Add("Dates"); + AddTestColumnDates(wsDates); + wsDates.Sort(); + + #endregion Sort Dates + + #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 Do Not Ignore Blanks + + #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 Case Sensitive + + #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(1, XLSortOrder.Descending); + + #endregion Simple Sorts + + wb.SaveAs(filePath); + } } private void AddTestColumnMixed(IXLWorksheet ws) @@ -174,6 +175,7 @@ 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); @@ -185,6 +187,7 @@ 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); @@ -196,6 +199,7 @@ 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); @@ -207,6 +211,7 @@ 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); @@ -218,6 +223,7 @@ 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); @@ -247,11 +253,5 @@ 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_Tests/Resource/Examples/Ranges/Sorting.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/Sorting.xlsx index 36b889d..a38d9be 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/Sorting.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/Sorting.xlsx Binary files differ