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); }