Exemplo n.º 1
0
        //点击菜单事件
        private void clearCaseAddressCommand_Click(Microsoft.Office.Core.CommandBarButton Ctrl, ref bool CancelDefault)
        {
            Excel.Workbook  workbook  = (Excel.Workbook)Globals.FxtAddIn.Application.ActiveWorkbook;
            Excel.Worksheet worksheet = workbook.Sheets[2];
            worksheet.Activate();
            Range rg = worksheet.UsedRange;

            rg.AdvancedFilter(XlFilterAction.xlFilterCopy, Type.Missing, worksheet.get_Range("B1"), true);
            rg = worksheet.get_Range("b1", "b" + rg.Rows.Count.ToString());
            int lRow = rg.get_End(XlDirection.xlDown).Row;

            rg = worksheet.get_Range("c1", "C" + lRow.ToString());
            rg.Cells[1, 1].Formula = "=Len(B1)";
            rg.FillDown();

            //排序
            rg = worksheet.get_Range("B1", "C" + lRow.ToString());
            rg.Sort(rg.Cells[1, 2], XlSortOrder.xlDescending);

            //处理
            worksheet = workbook.Sheets[1];
            worksheet.Activate();

            Globals.FxtAddIn.Application.DisplayAlerts = false;
            Globals.FxtAddIn.Application.EnableEvents  = true;
            Range rg1 = worksheet.get_Range("A1", "A" + worksheet.UsedRange.Rows.Count.ToString());

            rg1.EntireColumn.Insert(XlInsertShiftDirection.xlShiftDown, false);
            rg1.Copy(worksheet.get_Range("A1"));

            for (int i = 1; i <= rg.Rows.Count; i++)
            {
                string sKey = rg.Cells[i, 1].Value;
                rg1.Replace("*" + sKey + "*", sKey, Type.Missing, Type.Missing, false, Type.Missing, false, false);
            }

            Globals.FxtAddIn.Application.DisplayAlerts = true;
            MessageBox.Show("处理完成!");
        }