Remove duplicate rows from excel using spire.xls & c#

Jayanta Mukherjee

5y3.4kLearn iOS Programming

Hi all,

I'm trying to remove the duplicate rows from excel files in the fastest way possible.

I initially tried the below code (but it's very slow)

private void RemoveDuplicate(Worksheet sheet)
{
for (int i = 0; i < sheet.Columns[0].Count - 1; i++)
{
for (int j = i + 1; j < sheet.Columns[0].Count; j++)
{
if (sheet.Columns[0].CellList[i].Value == sheet.Columns[0].CellList[j].Value)
{
for (int col = 0; col < sheet.Rows[0].Count; col++)
{
if (col == sheet.Rows[0].Count - 1 && sheet.Rows[i].CellList[col].Value == sheet.Rows[j].CellList[col].Value)
{
sheet.DeleteRow(j + 1);
j--;
}
else if (col != sheet.Rows[0].Count - 1 && sheet.Rows[i].CellList[col].Value == sheet.Rows[j].CellList[col].Value)
{
continue;
}
else
{
break;
}
}
}
}
}
}

Then using it like

testWorkbook.LoadFromFile(filePath);
testSheet = testWorkbook.Worksheets[0];
RemoveDuplicate(testSheet);

Seeing it's slowness I tried a different approach (show below), but it is not working:

private void RemoveDuplicate2(Worksheet sheet, CellRange cellRanges)
{
// get the duplicated row numbers
var duplicatedRows = cellRanges.Rows
.GroupBy(x=> x.Columns[0].Value)
.Where(x=> x.Count() > 1)
.Select(x=> x.Last().Columns[0].Row)
.ToList();
foreach (var element in duplicatedRows)
{
sheet.DeleteRow(element);
}
}

Then using it like

testWorkbook.LoadFromFile(filePath);
testSheet = testWorkbook.Worksheets[0];
var cellRanges = testSheet.Range["A2:G" + testSheet.Columns[0].Count];
RemoveDuplicate2(testSheet, cellRanges);

Please help me on this ...

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Leon D5y ago
Accepted answer
The following code is working:
using Spire.Xls;
using System.Linq;

namespace RemoveDuplicateRows
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a Workbook instance
            Workbook workbook = new Workbook();
            //workbook.LoadFromFile(@"D:\\TEST.xlsx");
            // Load the Excel file
            workbook.LoadFromFile("TEST.xlsx");

            // Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            // Specify the range that you want to remove duplicate records from.
            var range = sheet.Range["A1:A" + sheet.LastRow];
            // Get the duplicated row numbers
            var duplicatedRows = range.Rows
                   .GroupBy(x => x.Columns[0].DisplayedText)
                   .Where(x => x.Count() > 1)
                   .SelectMany(x => x.Skip(1))
                   .Select(x => x.Columns[0].Row)
                   .ToList();

            // Remove the duplicate rows & blank rows if any
            for (int i = 0; i < duplicatedRows.Count; i++)
            {
                sheet.DeleteRow(duplicatedRows[i] - i);
            }

            // Save the result file
            workbook.SaveToFile("out.xlsx", ExcelVersion.Version2013);
            System.Diagnostics.Process.Start("out.xlsx");
        }
    }
}
Visit this link for more information:
https://shawnsmithhh.blogspot.com/2021/05/remove-duplicate-rows-in-excel-in-c-and.html
+1
Jayanta Mukherjee5y ago
@SALMAN BEG I was actually looking for a way to do this using spire.xls and not Microsoft interop
0
Salman Beg5y ago
See these links it may help you, https://stackoverflow.com/questions/12316517/c-sharp-excel-why-is-it-removing-duplicates-from-cells-as-apposed-to-whole-row https://www.c-sharpcorner.com/forums/removing-duplicate-values-in-excel-using-visual-c-sharp
+1