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();
}
