private void Ribbon1_Load(object sender, RibbonUIEventArgs e)
{
myData = myData.LoadMyData(); // read data from settings file
CommonExcelClasses.ButtonUpdateLabel(btnCompareSheets, "Compare: (" + myData.CompareOrColour + ")");
CommonExcelClasses.ButtonUpdateLabel(btnDealWithSingleDuplicates, "Duplicates (Cols: Single): (" + myData.ColourOrDelete + ")");
CommonExcelClasses.ButtonUpdateLabel(btnDealWithManyDuplicates, "Duplicates (Cols: Many): (" + myData.ColourOrDelete + ")");
}
private void Form1_Load(object sender, EventArgs e)
{
myData = myData.LoadMyData(); // read data from settings file
chkLargeButtons.Checked = myData.LargeButtons;
chkHideText.Checked = myData.HideText;
chkLBColorOrCompare.Text = myData.CompareOrColour;
// tick the correct box
if (chkLBColorOrCompare.CheckedItems.Count == 0)
{
if (myData.CompareOrColour == "Colour")
{
chkLBColorOrCompare.SetItemChecked(0, true);
}
else
{
chkLBColorOrCompare.SetItemChecked(1, true);
}
}
// - New
chkLBColourOrDelete.Text = myData.ColourOrDelete;
// tick the correct box
if (chkLBColourOrDelete.CheckedItems.Count == 0)
{
if (myData.ColourOrDelete == "Colour")
{
chkLBColourOrDelete.SetItemChecked(0, true);
}
else if (myData.ColourOrDelete == "Delete")
{
chkLBColourOrDelete.SetItemChecked(1, true);
}
else if (myData.ColourOrDelete == "Clear")
{
chkLBColourOrDelete.SetItemChecked(2, true);
}
}
// -New eof
chkDisplayTimeTaken.Checked = myData.DisplayTimeTaken;
chkProduceInitialMessageBox.Checked = myData.ProduceInitialMessageBox;
chkProduceCompleteMessageBox.Checked = myData.ProduceCompleteMessageBox;
cmboDelModeAorBorC.Text = myData.DelModeAorBorC;
numHighlightRowsOver.Value = myData.HighlightRowsOver;
// ---------
numNoOfColumnsToCheck.Value = myData.NoOfColumnsToCheck;
numComparingStartRow.Value = myData.ComparingStartRow;
numDupliateColumnToCheck.Value = myData.DupliateColumnToCheck;
txtColourFound.ForeColor = ColorTranslator.FromHtml(myData.ColourFore_Found);
txtColourNotFound.ForeColor = ColorTranslator.FromHtml(myData.ColourFore_NotFound);
chkFoundBold.Checked = myData.ColourBold_Found;
txtColourFound.BackColor = ColorTranslator.FromHtml(myData.ColourBack_Found);
txtColourNotFound.BackColor = ColorTranslator.FromHtml(myData.ColourBack_NotFound);
chkNotFoundBold.Checked = myData.ColourBold_NotFound;
// ---------
numTimeSheetRowNo.Value = myData.TimeSheetRowNo;
chkTimeSheetGetRowNo.Checked = myData.TimeSheetGetRowNo;
// ---------
numPingSheetRowNo.Value = myData.PingSheetRowNo;
numColPingRead.Value = myData.ColPingRead;
numColPingWrite.Value = myData.ColPingWrite;
chkTestCode.Checked = myData.TestCode;
chkTurnOffScreenValidation.Checked = myData.TurnOffScreenValidation;
if (cmboDelModeAorBorC.Items.Count != 3)
{
cmboDelModeAorBorC.Items.Add("Mode: A");
cmboDelModeAorBorC.Items.Add("Mode: B");
cmboDelModeAorBorC.Items.Add("Mode: C");
}
checkCompareCombo();
chkClearFormatting.Checked = myData.ClearFormatting;
}
/// <summary>
/// dealWithSingleDuplicates
/// Loops down a single column looking for duplicates
/// </summary>
internal void dealWithSingleDuplicates(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 strColourOrDelete = myData.ColourOrDelete;
bool boolTurnOffScreen = myData.TurnOffScreenValidation;
bool boolClearFormatting = myData.ClearFormatting;
// colours for the Colour or delete option
Color clrFoundForeColour = ColorTranslator.FromHtml(myData.ColourFore_Found);
Color clrFoundBackColour = ColorTranslator.FromHtml(myData.ColourBack_Found);
decimal intStartRow = myData.ComparingStartRow;
decimal intStartColumToCheck = myData.DupliateColumnToCheck;
#endregion
try
{
#region [Declare and instantiate variables for worksheet/book]
Excel.Workbook Wkb = xls.ActiveWorkbook;
Excel.Worksheet Wks; // get current sheet
Wks = Wkb.ActiveSheet;
string strColumnName = CommonExcelClasses.getExcelColumnLetter((int)intStartColumToCheck);
DialogResult dlgResult = DialogResult.Yes;
string strMessage;
int intLastRow = CommonExcelClasses.getLastRow(Wks);
// start of loop
decimal intSourceRow = intStartRow;
#endregion
#region [Display a Message?]
if (boolDisplayInitialMessage)
{
strMessage = "";
strMessage = strMessage + "Worksheet: " + Wks.Name + LF;
strMessage = strMessage + "Column: " + strColumnName + LF;
strMessage = strMessage + "and: " + strColourOrDelete + " ones which are the same";
if (booltimeTaken)
{
strMessage = strMessage + LF + " and display the time taken";
}
strMessage = strMessage + "?";
dlgResult = MessageBox.Show(strMessage, "Duplicate Rows Check", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);
// remove formatting - format black and white but only if no was selected
if (dlgResult == DialogResult.No)
{
if (boolClearFormatting)
{
CommonExcelClasses.clearFormattingRange(Wks);
}
}
}
#endregion
#region [Start of work]
if (dlgResult == DialogResult.Yes)
{
DateTime dteStart = DateTime.Now;
decimal curNoRecords = 0;
if (boolTurnOffScreen)
{
CommonExcelClasses.turnAppSettings("Off", xls, myData.TestCode);
}
#region [Start of loop]
while (!CommonExcelClasses.isEmptyCell(Wks.Cells[intSourceRow, intStartColumToCheck]))
{
// hightlight, delete or clear?
if (Wks.Cells[intSourceRow, intStartColumToCheck].Value == Wks.Cells[intSourceRow + 1, intStartColumToCheck].Value)
{
while (Wks.Cells[intSourceRow, intStartColumToCheck].Value == Wks.Cells[intSourceRow + 1, intStartColumToCheck].Value)
{
if (strColourOrDelete == "Colour")
{
CommonExcelClasses.colourCells(Wks, (intSourceRow + 1), "Error", 1, clrFoundForeColour, clrFoundBackColour, false);
intSourceRow++;
}
else if (strColourOrDelete == "Delete")
{
Wks.Rows[intSourceRow].Delete();
}
else
{
CommonExcelClasses.colourCells(Wks, (intSourceRow), strColourOrDelete, 1, clrFoundForeColour, clrFoundBackColour, false);
intSourceRow++;
}
curNoRecords++;
if (CommonExcelClasses.isEmptyCell(Wks.Cells[intSourceRow + 1, intStartColumToCheck]))
{
break;
}
}
}
intSourceRow++;
}
#endregion [Start of loop]
if (boolTurnOffScreen)
{
CommonExcelClasses.turnAppSettings("On", xls, myData.TestCode);
}
#region [Display Complete Message]
if (boolDisplayCompleteMessage)
{
strMessage = "Complete ...";
if (booltimeTaken)
{
DateTime dteEnd = DateTime.Now;
int milliSeconds = (int)((TimeSpan)(dteEnd - dteStart)).TotalMilliseconds;
strMessage = strMessage + "that took {TotalMilliseconds} " + milliSeconds + LF;
strMessage = strMessage + LF + "And handled: " + curNoRecords.ToString() + " duplicates";
}
CommonExcelClasses.MsgBox(strMessage);
}
#endregion
}
#endregion [Start of work]
#region [Release memory]
Marshal.ReleaseComObject(Wks);
Marshal.ReleaseComObject(Wkb);
#endregion
}
catch (System.Exception excpt)
{
CommonExcelClasses.MsgBox("There was an error?", "Error");
Console.WriteLine(excpt.Message);
}
}
