c# - Removing duplicate values in a column in DevEpress Spreadsheet

Question

DevEpress Spreadsheet doesn't currently support "Remove Duplicate" function. I want to write a C# code to do this manually. I have a column of values. Some of them are duplicate and those duplicate values may or may not be adjacent. I want to remove corresponding row of duplicate values. I tried this code:

IWorkbook workbook = spreadsheetControl.Document;
        Worksheet worksheet = workbook.Worksheets["Sheet1"];
        CellRange range = worksheet.GetUsedRange();
        int LastRow = range.BottomRowIndex;
        //MessageBox.Show(Convert.ToString(LastRow));
        for (int i = 0; i < LastRow; i++)
        {
            for (int j = i+1; j < LastRow; j++)
            {
                if (worksheet.Cells[i,0].Value == worksheet.Cells[j,0].Value)
                {
                    worksheet.Rows[j].Delete();
                }
            }
        }

It doesn't work properly.

question from:https://stackoverflow.com/questions/65873905/removing-duplicate-values-in-a-column-in-devepress-spreadsheet

Answer 1

It doesn't remove non-adjacent duplicate values.

You have a bug in your loops. The indexes are not accounting for the fact the collection is changing due to the Delete call.

You're also doing more work than necessary by going through all rows multiple times.

This creates a list of DevExpress Row objects that need to be deleted. And it only goes through the rows once.

//Let's keep track of every value seen as we go through the rows
var valuesSeen = new HashSet<string>();
//Rows marked for deletion
var duplicateRows = new List<Row>();
for (int i = 0; i < LastRow; i++)
{
    string cellValue = worksheet.Cells[i, 0].DisplayText;
    //Returns false if the value already exists
    if (!valuesSeen.Add(cellValue))
    {
        //Mark this row for deletion since we've seen the value before
        duplicateRows.Add(worksheet.Rows[i]);
    }
}
//Delete all marked rows only after we're done identifying them.
foreach (var row in duplicateRows)
    row.Delete();

You may need to specify the namespace for Row given it's a fairly common class name.

In the above code I'm comparing the display text (not actual value). Use HashSet<CellValue> instead of HashSet<string> if you don't want this.