Excel VSTO 查询重复项

一、需求描述：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("请选择一个或以上的单元格");

            }

        }

    }

}