一、需求描述：EXCEL原有的重复项识别功能，在识别身份证号码上存在识别错误。非重复项也识别为重复项。
二、编写Excel VSTO外接程序
1．创建新项目-Excel VSTO 外接程序，项目名：Dedupe
2.右键项目-添加-新建项-Office/SharePoint-功能区-添加
3.添加功能按钮
4编写功能代码：
using Microsoft.Office.Tools.Ribbon;
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Linq;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
namespace Dedupe
{
public partial class Ribbon1
{
public static Excel.Application App => Globals.ThisAddIn.Application;
public static Excel.Workbook Workbook => App.ActiveWorkbook;
public static Excel.Worksheet Worksheet => Workbook.ActiveSheet;
public static Excel.Range SelectRange => Worksheet.Application.Selection as Excel.Range;
private void Ribbon1_Load(object sender, RibbonUIEventArgs e)
{
}
private void button1_Click(object sender, RibbonControlEventArgs e)
{
var range = SelectRange;
if (range != null && range.Count > 1)
{
if (range.Count>1000000) {
MessageBox.Show("选中单元格数量过大，请分批处理") ;
return;
}
object[,] o = range.SpecialCells(Excel.XlCellType.xlCellTypeVisible).get_Value();
List<string> listAll = new List<string>();
List<string> listRepeat = new List<string>();
foreach (var cell in o)
{
if (cell != null)
{
listAll.Add(cell.ToString().Trim());
}
}
foreach (var cell in o)
{
if (cell != null)
{
List<string> list = listAll.Where(q => q == cell.ToString().Trim()).ToList();
if (list != null && list.Count > 1)
{
if (!listRepeat.Contains(cell.ToString().Trim()))
{
listRepeat.Add(cell.ToString());
}
}
}
}
//标记重复数据
if (listRepeat != null && listRepeat.Count > 0)
{
foreach (var item in listRepeat)
{
foreach (Excel.Range cell in range.SpecialCells(Excel.XlCellType.xlCellTypeVisible))
{
int i = cell.Row;
int j = cell.Column;
if (cell.Value != null && (Convert.ToString(cell.Value)).Trim() == item)
{
cell.Interior.Color = ColorTranslator.ToOle(Color.OrangeRed);
}
}
}
}
}
else
{
MessageBox.Show("请选择一个以上的单元格");
return;
}
}
private void button2_Click(object sender, RibbonControlEventArgs e)
{
var range = SelectRange;
object[,] o = range.SpecialCells(Excel.XlCellType.xlCellTypeVisible).get_Value();
if (o != null)
{
foreach (Excel.Range cell in range.SpecialCells(Excel.XlCellType.xlCellTypeVisible))
{
cell.Interior.ColorIndex = 0;
}
}
else
{
MessageBox.Show("请选择一个或以上的单元格");
return;
}
}
private void button3_Click(object sender, RibbonControlEventArgs e)
{
var range = SelectRange;
if (range != null && range.Count > 0)
{
object[,] o = range.SpecialCells(Excel.XlCellType.xlCellTypeVisible).get_Value();
if (o != null)
{
List<string> listAll = new List<string>();
List<string> listError = new List<string>();
foreach (var cell in o)
{
if (cell != null)
{
listAll.Add(cell.ToString().Trim());
}
}
foreach (var cell in listAll)
{
if (!listError.Contains(cell))
{
if (!Regex.Match(cell, "^[1-9]\\d{5}[1-9]\\d{3}((0\\d)|(1[0-2]))(([0|1|2]\\d)|3[0-1])\\d{4}$").Success)
{
listError.Add(cell);
}
}
}
//标记错误数据
if (listError != null && listError.Count > 0)
{
foreach (var item in listError)
{
foreach (Excel.Range cell in range.SpecialCells(Excel.XlCellType.xlCellTypeVisible))
{
int i = cell.Row;
int j = cell.Column;
if (cell.Value != null && (Convert.ToString(cell.Value)).Trim() == item)
{
cell.Interior.Color = ColorTranslator.ToOle(Color.Yellow);
}
}
}
}
}
else
{
MessageBox.Show("请选择一个或以上的单元格");
}
}
else
{
MessageBox.Show("请选择一个或以上的单元格");
}
}
}
}
4.右键项目发布（F5可以直接调试）
5.安装setup.exe
6.打开EXCEL，先打开EXCEL功能，勾选确认：文件-选项-自定义功能区-开发工具/加载项
7.加载项查看功能
8.开发工具里面卸载
三、实际效果
