Get Distinct Records from Excel Column using C#

You can get unique records from a excel column by using C#. For this, you need to use freeSpire.xls library which makes it easy to work on excel. Here is simple example that will help you to understand. Suppose you have sample data in excel as shown below.

img

You need to retrieve distinct values from “Customer Name” column means you want below in output.

img

Below is sample C# code by using which you can achieve your desired result.

using Spire.Xls;
using System;
using System.Data;

public void distinctRecordsExample()
{
 string excelPath = "D:/Test/Test Excel.xlsx";
 //Create a Workbook object
 Workbook workbook = new Workbook();
 //Load a xls or xlsx Excel file
 workbook.LoadFromFile(excelPath);
 //Get a specified worksheet
 Worksheet sheet = workbook.Worksheets["Sheet1"];
 DataTable dt = new DataTable();
 //loads Sheet1 data into data table dt
 dt = sheet.ExportDataTable();
 string[] distinctValues = dt.Rows
                    .Cast<DataRow>()
                    .Select(r => r.Field<string>("Customer Name"))
                    .Distinct()
                    .ToArray();
 foreach (string dV in distinctValues)
   {
     // Here we can do processing on distinct values as per need
     MessageBox.Show(dV);
   }
}

In this code, test excel gets loaded in workbook object, then “Sheet1” is assigned to sheet, the worksheet object. After that we export the data in “Sheet1” to a data table dt. After that by using the features of data table we get distinct values in an array. So, finally we are getting distinct records from excel column (Customer Name) in to an array. Then by the help of foreach loop we can do further processing on them.

We get distinct values in data table format as well. We need to use below code for that.

DataView view = new DataView(dt);
DataTable distinctValues = new DataTable();
distinctValues = view.ToTable(true, " Customer Name");