Esempio 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);
        }
Esempio n. 2
0
        private void RunCombine()
        {
            if (ctrlRefA.IsEmpty || ctrlRefA.Range == null) throw new InputError("Selection 1 is empty or incorrect.");
            if (ctrlRefB.IsEmpty || ctrlRefB == null) throw new InputError("Selection 2 is empty or incorrect.");

            //Shrink to used range
            Excel.Range rgSel1 = _excelapp.Intersect(ctrlRefA.Range, ctrlRefA.Range.Worksheet.UsedRange);
            if (rgSel1 == null) throw new InputError("Selection 1 is incorrect!");
            Excel.Range rgSel2 = _excelapp.Intersect(ctrlRefB.Range, ctrlRefB.Range.Worksheet.UsedRange);
            if (rgSel2 == null) throw new InputError("Selection 2 is incorrect!");

            //Define data range
            int nbRow1 = rgSel1.Rows.Count, nbCol1 = rgSel1.Columns.Count;
            int nbRow2 = rgSel2.Rows.Count, nbCol2 = rgSel2.Columns.Count;
            int offsetRow1 = ctrlWithHeadersA.Checked ? 1 : 0;
            int offsetRow2 = ctrlWithHeadersB.Checked ? 1 : 0;
            Excel.Range rgData1 = rgSel1.Offset[offsetRow1, Type.Missing].Resize[nbRow1 - offsetRow1, Type.Missing];
            Excel.Range rgData2 = rgSel2.Offset[offsetRow2, Type.Missing].Resize[nbRow2 - offsetRow2, Type.Missing];
            if (rgData1 == null) throw new InputError("Selection 1 is empty.");
            if (rgData2 == null) throw new InputError("Selection 2 is empty.");
            if (rgData1.Cells.Count < 2) throw new InputError("Selection 1 must contain at least 2 cells.");
            if (rgData2.Cells.Count < 2) throw new InputError("Selection 2 must contain at least 2 cells.");

            //read data
            int[] keysColA = ctrlKeysA.CheckedIds;
            int[] keysColB = ctrlKeysB.CheckedIds;
            if (ctrlKeysA.IsEmpty) throw new InputError("Rows identifiers for selection 1 are missing.");
            if (ctrlKeysB.IsEmpty) throw new InputError("Rows identifiers for selection 2 are missing.");
            if (keysColA.Length != keysColB.Length) throw new InputError("Number of identifiers is different.");
            object[,] data1 = Utils.GetDataFromRange(rgData1, false);
            object[,] data2 = Utils.GetDataFromRange(rgData2, false);
            object[,] titles1 = Utils.GetTitlesFromRange(rgSel1, offsetRow1 != 0);
            object[,] titles2 = Utils.GetTitlesFromRange(rgSel2, offsetRow2 != 0);
            int[] valColA = Utils.GetVisibleColumnsPosition(rgData1, null, keysColA);
            int[] valColB = Utils.GetVisibleColumnsPosition(rgData2, null, keysColB);
            object[] formats1 = Utils.GetColumnsFormating(rgData1);
            object[] formats2 = Utils.GetColumnsFormating(rgData2);

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

            RowSet dataCols = compare.CombineColumns(ref data1, ref data2, ref titles1, ref titles2, keysColA, keysColB, valColA, valColB);

            Excel.Range rgTarget = null;
            if (ctrlOptSelection.Checked) {
                worksheet = (Excel.Worksheet)_workbook.ActiveSheet;
                rgSel1.Clear();
                rgSel2.Clear();
                rgSel1.EntireRow.Hidden = false;
                rgSel2.EntireRow.Hidden = false;
                rgSel1.EntireColumn.Hidden = false;
                rgSel2.EntireColumn.Hidden = false;
                rgTarget = rgData1.Resize[dataCols.RowLen, dataCols.ColLen];
            } else if (ctrlOptNewSheet.Checked) {
                worksheet = (Excel.Worksheet)_workbook.Sheets.Add(Missing, _workbook.Sheets[_workbook.Sheets.Count], Missing, Missing);
                rgTarget = ((Excel.Range)worksheet.Cells[1 + offsetRow1, 1]).Resize[dataCols.RowLen, dataCols.ColLen];
                worksheet.Activate();
            }

            //Copy formats
            int c = 0;
            foreach (int i in keysColA)
                ((Excel.Range)rgTarget.Columns[++c]).NumberFormat = formats1[i - 1];
            foreach (int i in valColA)
                ((Excel.Range)rgTarget.Columns[++c]).NumberFormat = formats1[i - 1];
            foreach (int i in valColB)
                ((Excel.Range)rgTarget.Columns[++c]).NumberFormat = formats2[i - 1];

            //copy data
            if (ctrlWithHeadersA.Checked)
                Utils.AddTitlesToRange((Excel.Range)rgTarget.Rows[0], dataCols.Titles);
            Utils.AddDataToRange(rgTarget, dataCols.Data);
            Utils.AddTagsToRange(rgTarget, dataCols.Diff, ctrlInteriorColor.SelectedColor, ctrlFontColor.SelectedColor);
        }
Esempio n. 3
0
        private void RunCompare()
        {
            if (ctrlRefA.IsEmpty) throw new InputError("Selection 1 is empty or incorrect.");
            if (ctrlRefB.IsEmpty) throw new InputError("Selection 2 is empty or incorrect.");

            //Shrink to used range
            Excel.Range rgSel1 = _excelapp.Intersect(ctrlRefA.Range, ctrlRefA.Range.Worksheet.UsedRange);
            if (rgSel1 == null) throw new InputError("Selection 1 is incorrect!");
            Excel.Range rgSel2 = _excelapp.Intersect(ctrlRefB.Range, ctrlRefB.Range.Worksheet.UsedRange);
            if (rgSel2 == null) throw new InputError("Selection 2 is incorrect!");

            //Define data range
            int nbRow1 = rgSel1.Rows.Count, nbCol1 = rgSel1.Columns.Count;
            int nbRow2 = rgSel2.Rows.Count, nbCol2 = rgSel2.Columns.Count;
            int offsetRow1 = ctrlOptRows.Checked && ctrlWithHeadersA.Checked ? 1 : 0;
            int offsetRow2 = ctrlOptRows.Checked && ctrlWithHeadersB.Checked ? 1 : 0;
            Excel.Range rgData1 = rgSel1.Offset[offsetRow1, Type.Missing].Resize[nbRow1 - offsetRow1, Type.Missing];
            Excel.Range rgData2 = rgSel2.Offset[offsetRow2, Type.Missing].Resize[nbRow2 - offsetRow2, Type.Missing];
            if (rgData1 == null) throw new InputError("Selection 1 is empty.");
            if (rgData2 == null) throw new InputError("Selection 2 is empty.");
            if (rgData1.Cells.Count < 2) throw new InputError("Selection 1 must contain at least 2 cells.");
            if (rgData2.Cells.Count < 2) throw new InputError("Selection 2 must contain at least 2 cells.");

            //read data
            bool optionMulti = ctrlOptSingleMatch.Checked == false;
            bool optionformula = ctrlUseFormula.Checked;
            object[,] data1 = Utils.GetDataFromRange(rgData1, ctrlUseFormula.Checked);
            object[,] data2 = Utils.GetDataFromRange(rgData2, ctrlUseFormula.Checked);
            object[,] titles1 = Utils.GetTitlesFromRange(rgSel1, ctrlWithHeadersA.Checked);
            object[,] titles2 = Utils.GetTitlesFromRange(rgSel2, ctrlWithHeadersB.Checked);
            object[] formats1 = Utils.GetColumnsFormating(rgData1);
            object[] formats2 = Utils.GetColumnsFormating(rgData2);

            //Initilise caompare class and save seeting
            var compare = new Compare { IgnoreCase = ctrlIgnoreCase.Checked };
            Excel._Worksheet worksheet;
            XlAlign optionAlign = XlAlign.None;
            object[] res = { };
            SaveSettings();

            //compare
            if (ctrlOptRows.Checked) {
                int[] keysColA = ctrlKeys.IsEmpty ? ctrlKeys.UncheckedIds : ctrlKeys.CheckedIds;
                int[] valColA = Utils.GetVisibleColumnsPosition(rgData1, null, keysColA);
                int[] valColB = Utils.GetVisibleColumnsPosition(rgData2, null, keysColA);
                if (ctrlOptCurrent.Checked) {
                    res = compare.CompareRowsStatic(ref data1, ref data2, keysColA, keysColA, valColA, valColB, optionMulti);
                } else if (ctrlOptNewSheet.Checked) {
                    if (ctrlOptSBSSelections.Checked) optionAlign = XlAlign.Tables;
                    else if (ctrlOptSBSColumns.Checked) optionAlign = XlAlign.Columns;
                    else if (ctrlOptSBSValues.Checked) optionAlign = XlAlign.Values;
                    res = compare.CompareRowsAligned(ref data1, ref data2, ref titles1, ref titles2, keysColA, keysColA, valColA, valColB, optionMulti, optionAlign);
                }
            } else {
                if (ctrlOptDisorderedCells.Checked)
                    res = compare.CompareListOfCells(ref data1, ref data2, optionMulti);
                else if (ctrlOptOppositeCells.Checked)
                    res = compare.CompareOppositeCells(ref data1, ref data2);
            }

            //Add new sheet, formats and headers
            var res1 = res.Length != 0 ? (CellSet)res[0] : null;
            var res2 = res.Length > 1 ? (CellSet)res[1] : null;
            if (ctrlOptNewSheet.Checked) {
                worksheet = (Excel.Worksheet)_workbook.Sheets.Add(Missing, _workbook.Sheets[_workbook.Sheets.Count], Missing, Missing);
                //set data ranges
                int offsetCol = optionAlign == XlAlign.None ? 0 : 1;
                rgData1 = ((Excel.Range)worksheet.Cells[2, 1 + offsetCol]).Resize[res1.RowLen, res1.ColLen];
                if (res2 != null)
                    rgData2 = ((Excel.Range)worksheet.Cells[2, 2 + offsetCol + res1.ColLen]).Resize[res2.RowLen, res2.ColLen];
                else
                    rgData2 = null;
                //set formats
                if (optionAlign == XlAlign.Tables || optionAlign == XlAlign.None) {
                    Utils.SetColumnsFormat(rgData1, res1.RowLen, formats1, 1, 1);
                    Utils.SetColumnsFormat(rgData2, res2.RowLen, formats2, 1, 1);
                } else if (optionAlign == XlAlign.Columns) {
                    Utils.SetColumnsFormat(rgData1, res1.RowLen, formats1, 1, 2);
                    Utils.SetColumnsFormat(rgData1, res1.RowLen, formats2, 2, 2);
                }
                //set col info
                if (optionAlign != XlAlign.None) {
                    AddColumnInfoToWorksheet((RowSet)res1, rgData1);
                    if (optionAlign == XlAlign.Tables)
                        AddColumnInfoToWorksheet((RowSet)res2, rgData2);
                }

                //Add data and titles
                if (ctrlOptRows.Checked) {
                    Utils.AddDataToRange(rgData1, res1.Data, optionformula, ((RowSet)res1).Titles);
                    if (res2 != null)
                        Utils.AddDataToRange(rgData2, res2.Data, optionformula, ((RowSet)res2).Titles);
                    worksheet.UsedRange.AutoFilter(1, Type.Missing, Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);
                } else {
                    Utils.AddDataToRange(rgData1, res1.Data, optionformula, titles1);
                    Utils.AddDataToRange(rgData2, res2.Data, optionformula, titles2);
                }
                worksheet.Activate();
            } else {
                ((Excel._Worksheet)rgData1.Worksheet).Activate();
            }

            Utils.AddTagsToRange(rgData1, res1.Diff, ctrlInteriorColor.SelectedColor, ctrlFontColor.SelectedColor);
            if (res2 != null)
                Utils.AddTagsToRange(rgData2, res2.Diff, ctrlInteriorColor.SelectedColor, ctrlFontColor.SelectedColor);
        }