SortEdgeTable ( ListObject oEdgeTable, Range oRangeToSortOn ) { Debug.Assert(oEdgeTable != null); Debug.Assert(oRangeToSortOn != null); AssertValid(); Sort oSort = oEdgeTable.Sort; SortFields oSortFields = oSort.SortFields; oSortFields.Clear(); oSortFields.Add(oRangeToSortOn, XlSortOn.xlSortOnValues, XlSortOrder.xlAscending, Missing.Value, XlSortDataOption.xlSortNormal); oSort.Apply(); oSortFields.Clear(); }
DeleteDuplicateRows ( ListObject oEdgeTable, ListColumn oEdgeWeightColumn, Range oEdgeWeightData, Object [,] aoEdgeWeightValues ) { Debug.Assert(oEdgeTable != null); Debug.Assert(oEdgeWeightColumn != null); Debug.Assert(oEdgeWeightData != null); Debug.Assert(aoEdgeWeightValues != null); AssertValid(); Range oDuplicateRows = null; // Find the rows with null edge weights, which are the duplicates. To // avoid multiple areas, which can slow things down signficantly, sort // the table on the edge weight column. That forces the duplicates // to be contiguous. // // But first, add a temporary column and set its values to the // worksheet row numbers. This will be used later to restore the // original sort order. ListColumn oTemporaryColumn; if (!ExcelUtil.TryAddTableColumnWithRowNumbers(oEdgeTable, "Temporary for Sort", 5F, null, out oTemporaryColumn)) { return; } Sort oSort = oEdgeTable.Sort; SortFields oSortFields = oSort.SortFields; oSortFields.Clear(); oSortFields.Add(oEdgeWeightColumn.Range, XlSortOn.xlSortOnValues, XlSortOrder.xlAscending, Missing.Value, XlSortDataOption.xlSortNormal); oSort.Apply(); if (oEdgeWeightData.Rows.Count != 1) { try { oDuplicateRows = oEdgeWeightData.SpecialCells( XlCellType.xlCellTypeBlanks, Missing.Value); } catch (COMException) { // There are no such rows. oDuplicateRows = null; } } else { // Range.SpecialCells() can't be used in the one-cell case, for // which it behaves in a bizarre manner. See this posting: // // http://ewbi.blogs.com/develops/2006/03/determine_if_a_.html // // ...of which this is an excerpt: // // "SpecialCells ignores any source Range consisting of only one // cell. When executing SpecialCells on a Range having only one // cell, it will instead consider all of the cells falling within // the boundary marked by the bottom right cell of the source Range // sheet's UsedRange." // // Instead, just check the single row. if (aoEdgeWeightValues[1, 1] == null) { oDuplicateRows = oEdgeWeightData.EntireRow; } } if (oDuplicateRows != null) { // Delete the duplicate rows, which are contiguous. Debug.Assert(oDuplicateRows.Areas.Count == 1); oDuplicateRows.EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp); } // Restore the original sort order by sorting on the temporary column // that contains the original worksheet row numbers. Debug.Assert(oTemporaryColumn != null); oSortFields.Clear(); oSortFields.Add(oTemporaryColumn.Range, XlSortOn.xlSortOnValues, XlSortOrder.xlAscending, Missing.Value, XlSortDataOption.xlSortNormal); oSort.Apply(); oSortFields.Clear(); oTemporaryColumn.Delete(); oSort.Apply(); }