Ejemplo n.º 1
0
        private void RunDistinct()
        {
            if (ctrlRef.IsEmpty) throw new InputError("Selection is empty!");

            //Shrink to used range
            Excel.Range rgSel = _excelapp.Intersect(ctrlRef.Range, ctrlRef.Worksheet.UsedRange);
            if (rgSel == null) throw new InputError("Selection is empty!");
            if (rgSel.Rows.Count == 1) throw new InputError("Selection must contain at least 2 rows!");

            //Define data range
            bool optionformula = ctrlUseFormula.Checked;
            int nbRow = rgSel.Rows.Count, nbCol = rgSel.Columns.Count;
            int offset = ctrlOptRows.Checked && ctrlWithHeaders.Checked ? 1 : 0;
            Excel.Range rgData = rgSel.Offset[offset, Type.Missing].Resize[nbRow - offset, Type.Missing];
            if (rgData.Cells.Count < 2) throw new InputError("Range 1 must contain at least 2 cells!");

            //read data
            object[,] data = Utils.GetDataFromRange(rgData, optionformula);
            object[] formats = Utils.GetColumnsFormating(rgData);

            //Initilise caompare class and save seeting
            var compare = new Compare { IgnoreCase = ctrlIgnoreCase.Checked };
            SaveSettings();

            //Compare
            CellSet result = null;
            if (ctrlOptRows.Checked) {
                int[] keysCol = ctrlKeys.IsEmpty ? ctrlKeys.UncheckedIds : ctrlKeys.CheckedIds;
                int[] valCol = Utils.GetVisibleColumnsPosition(rgData, null, keysCol);
                if (ctrlOptDelete.Checked)
                    result = compare.DeleteDuplicatedRows(ref data, keysCol, valCol);
                else if (ctrlOptHighligth.Checked)
                    result = compare.GetDuplicatedRows(ref data, keysCol, valCol);
            } else {
                if (ctrlOptDelete.Checked)
                    result = compare.DeleteDuplicatedCells(ref data);
                else if (ctrlOptHighligth.Checked)
                    result = compare.GetDuplicatedCells(ref data);
            }

            //Add new sheet, formats and headers
            if (ctrlOptNewSheet.Checked) {
                var worksheet = (Excel.Worksheet)_workbook.Sheets.Add(Missing, _workbook.Sheets[_workbook.Sheets.Count], Type.Missing, Type.Missing);
                var headers = offset != 0 ? rgSel.Resize[1, Type.Missing].Value : null;
                rgData = ((Excel.Range)worksheet.Cells[1 + offset, 1]).Resize[nbRow - offset, nbCol];
                Utils.SetColumnsFormat(rgData, nbRow - offset, formats, 1, 1);
                if (headers != null) {
                    var rgHeader = ((Excel.Range)rgData.Rows[1]).Offset[offset, Type.Missing];
                    rgHeader.Value = headers;
                    rgHeader.Font.Bold = true;
                }
                ((Excel._Worksheet)worksheet).Activate();
            }

            //Add data and diff tags
            if (result == null) return;
            if (ctrlOptDelete.Checked || ctrlOptNewSheet.Checked)
                Utils.AddDataToRange(rgData, result.Data, optionformula);
            Utils.AddTagsToRange(rgData, result.Diff, ctrlInteriorColor.SelectedColor, ctrlFontColor.SelectedColor);
        }