本文将介绍如何使用Spire.XLS for .NET删除Excel工作表中的重复行。
原Excel文档如下:
C#
using Spire.Xls;
using System.Linq;
namespace RemoveDuplicateRows
{
class Program
{
static void Main(string[] args)
{
//创建Workbook实例
Workbook workbook = new Workbook();
//加载Excel文档
workbook.LoadFromFile("TEST.xlsx");
//获取第一个工作表
Worksheet sheet = workbook.Worksheets[0];
//指定需要删除重复行的单元格范围
var range = sheet.Range["A1:A" + sheet.LastRow];
//获取重复行的行号
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();
//删除重复行和空白行
for (int i = 0; i < duplicatedRows.Count; i++)
{
sheet.DeleteRow(duplicatedRows[i] - i);
}
//保存结果文档
workbook.SaveToFile("Output.xlsx", ExcelVersion.Version2013);
}
}
}
VB.NET
Imports Spire.Xls
Imports System.Linq
Namespace RemoveDuplicateRows
Class Program
Private Shared Sub Main(ByVal args As String())
Dim workbook As Workbook = New Workbook()
workbook.LoadFromFile("TEST.xlsx")
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim range = sheet.Range("A1:A" & sheet.LastRow)
Dim duplicatedRows = range.Rows.GroupBy(Function(x) x.Columns(0).DisplayedText).Where(Function(x) x.Count() > 1).SelectMany(Function(x) x.Skip(1)).[Select](Function(x) x.Columns(0).Row).ToList()
For i As Integer = 0 To duplicatedRows.Count - 1
sheet.DeleteRow(duplicatedRows(i) - i)
Next
workbook.SaveToFile("Output.xlsx", ExcelVersion.Version2013)
End Sub
End Class
End Namespace