public void compareSheets_graveYard(Excel.Workbook Wkb)
{
Excel.Worksheet Wks1; // get current sheet
Excel.Worksheet Wks2; // get sheet next door
string strClearOrColour = "Colour";
Wks1 = Wkb.ActiveSheet;
Wks2 = Wkb.Sheets[Wks1.Index + 1];
DialogResult dlgResult = MessageBox.Show("Compare: Worksheet: " + Wks1.Name + " against: " + Wks2.Name + " and " + strClearOrColour + " ones which are the same?", "Compare Sheets", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);
if (dlgResult == DialogResult.Yes)
{
int intTargetRow = 0;
int intStartRow = 2;
// how may columns to check ?
int intNoCheckCols = 5; // for later loop?
int intStartColumToCheck = 1;
int intColScore = 1;
string strValue1 = "";
int intSheetLastRow1 = CommonExcelClasses.getLastRow(Wks1);
int intSheetLastRow2 = CommonExcelClasses.getLastRow(Wks2);
for (int intSourceRow = intStartRow; intSourceRow <= intSheetLastRow1; intSourceRow++)
{
// read in vlaue from sheet
// maybe I should ready all into arrayS?
strValue1 = Wks1.Cells[intSourceRow, intStartColumToCheck].Value;
intTargetRow = CommonExcelClasses.searchForValue(Wks2, strValue1, intStartColumToCheck);
if (intTargetRow > 0)
{
// start from correct column
for (int intColCount = intStartColumToCheck; intColCount <= intNoCheckCols; intColCount++)
{
// Compare cells directly
if (Wks1.Cells[intSourceRow, intColCount].Value == Wks2.Cells[intTargetRow, intColCount].Value)
{
intColScore++;
}
}
// Score system = if all the same then can blue it
if (intColScore == intNoCheckCols)
{
for (int intColCount = intStartColumToCheck; intColCount <= intNoCheckCols; intColCount++)
{
if (strClearOrColour == "Colour")
{
Wks1.Cells[intSourceRow, intColCount].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);;
}
}
}
intColScore = 1;
}
}
}
}
// left here - 1gvb9
internal void compareSheets(Excel.Application xls)
{
#region [Declare and instantiate variables for process]
myData = myData.LoadMyData(); // read data from settings file
bool boolDisplayInitialMessage = myData.ProduceInitialMessageBox;
bool boolDisplayCompleteMessage = myData.ProduceCompleteMessageBox;
bool booltimeTaken = myData.DisplayTimeTaken;
string strCompareOrColour = myData.CompareOrColour;
bool boolTurnOffScreen = myData.TurnOffScreenValidation;
bool boolClearFormatting = myData.ClearFormatting;
Color clrColourFore_Found = ColorTranslator.FromHtml(myData.ColourFore_Found);
Color clrColourFore_NotFound = ColorTranslator.FromHtml(myData.ColourFore_NotFound);
bool boolFontBold_Found = myData.ColourBold_Found;
bool boolFontBold_NotFound = myData.ColourBold_NotFound;
Color clrColourBack_Found = ColorTranslator.FromHtml(myData.ColourBack_Found);
Color clrColourBack_NotFound = ColorTranslator.FromHtml(myData.ColourBack_NotFound);
int intStartRow = (int)myData.ComparingStartRow;
bool boolTestCode = myData.TestCode;
#endregion
try
{
#region [Declare and instantiate variables]
Excel.Workbook Wkb = xls.ActiveWorkbook;
Excel.Worksheet Wks1; // get current sheet
Excel.Worksheet Wks2; // get sheet next door
Wks1 = Wkb.ActiveSheet;
Wks2 = Wkb.Sheets[Wks1.Index + 1];
int intSheetLastRow1 = CommonExcelClasses.getLastRow(Wks1);
int intSheetLastRow2 = CommonExcelClasses.getLastRow(Wks2);
#endregion
#region [Declare and instantiate variables for worksheet/book]
if (intSheetLastRow1 >= intStartRow || intSheetLastRow2 >= intStartRow)
{
#region [Ask to display a Message?]
DialogResult dlgResult = DialogResult.Yes;
string strMessage;
if (boolDisplayInitialMessage)
{
strMessage = "Compare: " + Wks1.Name + LF +
" against: " + Wks2.Name + LF +
" and: " + strCompareOrColour + " ones which are the same" + LF +
" (starting at row:" + intStartRow.ToString() + ")";
if (booltimeTaken)
{
strMessage = strMessage + LF + " and display the time taken";
}
strMessage = strMessage + "?";
dlgResult = MessageBox.Show(strMessage, "Compare Sheets", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);
}
int intLastCol = CommonExcelClasses.getLastCol(Wks1);
if (boolTurnOffScreen)
{
CommonExcelClasses.turnAppSettings("Off", xls, myData.TestCode);
}
// remove formatting - format black and white but only if no was selected
if (dlgResult == DialogResult.No)
{
if (boolClearFormatting)
{
CommonExcelClasses.clearFormattingRange(Wks1);
}
}
#endregion
#region [Start of work]
if (dlgResult == DialogResult.Yes)
{
DateTime dteStart = DateTime.Now;
int intTargetRow = 0;
int intStartColumToCheck = 1;
int intColScore = 0;
string strValue1 = "";
for (int intSourceRow = intStartRow; intSourceRow <= intSheetLastRow1; intSourceRow++)
{
// read in vlaue from sheet
// maybe I should ready all into arrays - maybe later?
strValue1 = Wks1.Cells[intSourceRow, intStartColumToCheck].Value;
intTargetRow = CommonExcelClasses.searchForValue(Wks2, strValue1, intStartColumToCheck);
if (intTargetRow > 0)
{
string stringCell1 = ""; string stringCell2 = "";
// start from correct column
for (int intColCount = intStartColumToCheck; intColCount <= intLastCol; intColCount++)
{
if (!CommonExcelClasses.isEmptyCell(Wks1.Cells[intSourceRow, intColCount]))
{
stringCell1 = Wks1.Cells[intSourceRow, intColCount].Value.ToString();
}
// need to handle nulls properly
if (!CommonExcelClasses.isEmptyCell(Wks2.Cells[intTargetRow, intColCount]))
{
stringCell2 = Wks2.Cells[intTargetRow, intColCount].Value.ToString();
}
if (stringCell1 == stringCell2)
{
intColScore++;
}
}
}
// Score system = if all the same then can blue it
if (intColScore == intLastCol)
{
CommonExcelClasses.colourCells(Wks1, intSourceRow, strCompareOrColour, intLastCol, clrColourFore_Found, clrColourBack_Found, boolTestCode);
}
else
{
CommonExcelClasses.colourCells(Wks1, intSourceRow, "Error", intLastCol, clrColourFore_NotFound, clrColourBack_NotFound, boolTestCode);
}
intColScore = 0;
}
if (boolTurnOffScreen)
{
CommonExcelClasses.turnAppSettings("On", xls, myData.TestCode);
}
#endregion
#region [Display Complete Message]
if (boolDisplayCompleteMessage)
{
strMessage = "";
strMessage = strMessage + "Compare Complete ...";
if (booltimeTaken)
{
DateTime dteEnd = DateTime.Now;
int milliSeconds = (int)((TimeSpan)(dteEnd - dteStart)).TotalMilliseconds;
strMessage = strMessage + "that took {TotalMilliseconds} " + milliSeconds;
}
CommonExcelClasses.MsgBox(strMessage); // localisation?
}
#endregion
}
}
else
{
if (boolDisplayCompleteMessage)
{
CommonExcelClasses.MsgBox("No data to compare ...", "Warning"); // localisation?
}
}
if (boolTurnOffScreen)
{
CommonExcelClasses.turnAppSettings("On", xls, myData.TestCode);
}
#endregion
#region [Release memory]
Marshal.ReleaseComObject(Wks1);
Marshal.ReleaseComObject(Wks2);
Marshal.ReleaseComObject(Wkb);
#endregion
}
catch (System.Exception excpt)
{
CommonExcelClasses.MsgBox("Are you on the last sheet?", "Error");
Console.WriteLine(excpt.Message);
}
}