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 + ")");
}
public InformationForSettingsForm LoadMyData()
{
XmlSerializer xS = new XmlSerializer(typeof(InformationForSettingsForm));
FileStream fsRead = new FileStream(strFileName, FileMode.Open, FileAccess.Read, FileShare.Read);
InformationForSettingsForm infoLocal = (InformationForSettingsForm)xS.Deserialize(fsRead);
fsRead.Close();
return(infoLocal);
}
public void deleteBlankLines(Excel.Workbook Wkb, string strMode)
{
Excel.Worksheet Wks;
Wks = Wkb.ActiveSheet;
string strResultsCol = "F";
myData = myData.LoadMyData(); // read data from settings file
bool boolRecordTimes = myData.RecordTimes;
DateTime dteStart = DateTime.Now;
if (boolRecordTimes)
{
// record times
Wks.Range[strResultsCol + "1"].Value = dteStart;
}
if (Wks.Name != "InternalParameters")
{
if (strMode == "A")
{
delLinesModeA(Wks);
}
if (strMode == "B")
{
delLinesModeB(Wks);
}
if (strMode == "C")
{
delLinesModeC(Wks);
}
}
else
{
CommonExcelClasses.MsgBox("Cannot run in worksheet: InternalParameters", "Error");
}
if (boolRecordTimes)
{
DateTime dteEnd = DateTime.Now;
int milliSeconds = (int)((TimeSpan)(dteEnd - dteStart)).TotalMilliseconds;
Wks.Range[strResultsCol + "2"].Value = dteEnd;
Wks.Range[strResultsCol + "3"].Value = milliSeconds;
}
CommonExcelClasses.MsgBox("Finshed ...");
}
private void btnApply_Click(object sender, EventArgs e)
{
myData.LargeButtons = chkLargeButtons.Checked;
myData.HideText = chkHideText.Checked;
myData.CompareOrColour = chkLBColorOrCompare.Text; // new item
myData.ColourOrDelete = chkLBColourOrDelete.Text; // new item
myData.DisplayTimeTaken = chkDisplayTimeTaken.Checked;
myData.ProduceInitialMessageBox = chkProduceInitialMessageBox.Checked;
myData.ProduceCompleteMessageBox = chkProduceCompleteMessageBox.Checked;
myData.DelModeAorBorC = cmboDelModeAorBorC.Text;
myData.HighlightRowsOver = numHighlightRowsOver.Value;
// ---------
myData.NoOfColumnsToCheck = numNoOfColumnsToCheck.Value;
myData.ComparingStartRow = numComparingStartRow.Value;
myData.DupliateColumnToCheck = numDupliateColumnToCheck.Value;
myData.ColourFore_Found = ColorTranslator.ToHtml(txtColourFound.ForeColor);
myData.ColourFore_NotFound = ColorTranslator.ToHtml(txtColourNotFound.ForeColor);
myData.ColourBold_Found = chkFoundBold.Checked;
myData.ColourBack_Found = ColorTranslator.ToHtml(txtColourFound.BackColor);
myData.ColourBack_NotFound = ColorTranslator.ToHtml(txtColourNotFound.BackColor);
myData.ColourBold_NotFound = chkNotFoundBold.Checked;
// ---------
myData.TimeSheetRowNo = numTimeSheetRowNo.Value;
myData.TimeSheetGetRowNo = chkTimeSheetGetRowNo.Checked;
// ---------
myData.PingSheetRowNo = numPingSheetRowNo.Value;
myData.ColPingRead = numColPingRead.Value;
myData.ColPingWrite = numColPingWrite.Value;
//---- misc
myData.TestCode = chkTestCode.Checked;
// myData.RecordTimes = chkRecordTimes.Checked;
myData.TurnOffScreenValidation = chkTurnOffScreenValidation.Checked;
myData.ClearFormatting = chkClearFormatting.Checked;
InformationForSettingsForm.SaveData(myData);
this.Hide();
}
public void startOfWeekCheck(Excel.Worksheet Wks, decimal intRowCount, decimal intTimeSheetRowNo)
{
if (Wks.Cells[intRowCount, 14].Value.ToString() == "Date")
{
if (Wks.Cells[intRowCount, 2].Value.ToString() == "Week")
{
if (intRowCount != intTimeSheetRowNo)
{
myData.TimeSheetRowNo = intRowCount; // set the value
InformationForSettingsForm.SaveData(myData);
}
}
}
}
public void btnSettings_Click(object sender, RibbonControlEventArgs e)
{
DialogResult dr = frmSettings.ShowDialog();
if (dr == DialogResult.OK)
{
boolDisplayMessage = frmSettings.chkProduceInitialMessageBox.Checked;
boolLargeButton = frmSettings.chkLargeButtons.Checked;
boolHideText = frmSettings.chkHideText.Checked;
boolHideSeperator = frmSettings.chkHideSeperator.Checked;
CommonExcelClasses.ButtonSetSize(btnSettings, boolLargeButton);
CommonExcelClasses.ButtonSetSize(btnReadFolders, boolLargeButton);
CommonExcelClasses.ButtonSetSize(btnCompareSheets, boolLargeButton);
CommonExcelClasses.ButtonSetSize(btnZap, boolLargeButton);
CommonExcelClasses.SplitButtonSetSize(splitButtonDeleteLines, boolLargeButton);
CommonExcelClasses.ButtonSetSize(btnDeleteBlankLinesA, boolLargeButton);
CommonExcelClasses.ButtonSetSize(btnDeleteBlankLinesB, boolLargeButton);
CommonExcelClasses.ButtonSetSize(btnDeleteBlankLinesC, boolLargeButton);
CommonExcelClasses.ButtonSetSize(btnDealWithSingleDuplicates, boolLargeButton);
CommonExcelClasses.ButtonSetSize(btnDealWithManyDuplicates, boolLargeButton);
CommonExcelClasses.ButtonSetSize(btnLoadADGroupIntoSpreadsheet, boolLargeButton);
CommonExcelClasses.ButtonSetSize(btnLoadADGroupIntoSpreadsheetActiveCell, boolLargeButton);
CommonExcelClasses.ButtonSetSize(btnReadUsersGroupMembership, boolLargeButton);
CommonExcelClasses.ButtonSetSize(btnReadUsersGroupMembershipActiveCell, boolLargeButton);
CommonExcelClasses.ButtonSetSize(btnWriteTimeSheet, boolLargeButton);
CommonExcelClasses.ButtonSetSize(btnPingServers, boolLargeButton);
separator1.Visible = boolHideSeperator;
separator2.Visible = boolHideSeperator;
separator3.Visible = boolHideSeperator;
separator4.Visible = boolHideSeperator;
separator5.Visible = boolHideSeperator;
separator6.Visible = boolHideSeperator;
if (boolHideText)
{
CommonExcelClasses.ButtonUpdateLabel(btnSettings, "");
CommonExcelClasses.ButtonUpdateLabel(btnReadFolders, "");
CommonExcelClasses.ButtonUpdateLabel(btnCompareSheets, "");
CommonExcelClasses.ButtonUpdateLabel(btnZap, "");
CommonExcelClasses.SplitButtonUpdateLabel(splitButtonDeleteLines, "");
CommonExcelClasses.ButtonUpdateLabel(btnDeleteBlankLinesA, "");
CommonExcelClasses.ButtonUpdateLabel(btnDeleteBlankLinesB, "");
CommonExcelClasses.ButtonUpdateLabel(btnDeleteBlankLinesC, "");
CommonExcelClasses.ButtonUpdateLabel(btnDealWithSingleDuplicates, "");
CommonExcelClasses.ButtonUpdateLabel(btnDealWithManyDuplicates, "");
CommonExcelClasses.ButtonUpdateLabel(btnLoadADGroupIntoSpreadsheet, "");
CommonExcelClasses.ButtonUpdateLabel(btnLoadADGroupIntoSpreadsheetActiveCell, "");
CommonExcelClasses.ButtonUpdateLabel(btnReadUsersGroupMembership, "");
CommonExcelClasses.ButtonUpdateLabel(btnReadUsersGroupMembershipActiveCell, "");
CommonExcelClasses.ButtonUpdateLabel(btnWriteTimeSheet, "");
CommonExcelClasses.ButtonUpdateLabel(btnPingServers, "");
}
else
{
CommonExcelClasses.ButtonUpdateLabel(btnSettings, "Settings");
CommonExcelClasses.ButtonUpdateLabel(btnReadFolders, "Read Folders");
myData = myData.LoadMyData(); // read data from settings file
CommonExcelClasses.ButtonUpdateLabel(btnCompareSheets, "Compare: (" + myData.CompareOrColour + ")");
CommonExcelClasses.ButtonUpdateLabel(btnZap, "Zap Worksheet");
CommonExcelClasses.SplitButtonUpdateLabel(splitButtonDeleteLines, "Delete Blank Lines");
CommonExcelClasses.ButtonUpdateLabel(btnDeleteBlankLinesA, "Mode: A");
CommonExcelClasses.ButtonUpdateLabel(btnDeleteBlankLinesB, "Mode: B");
CommonExcelClasses.ButtonUpdateLabel(btnDeleteBlankLinesC, "Mode: C");
CommonExcelClasses.ButtonUpdateLabel(btnDealWithSingleDuplicates, "Duplicates (Cols: Single): (" + myData.ColourOrDelete + ")");
CommonExcelClasses.ButtonUpdateLabel(btnDealWithManyDuplicates, "Duplicates (Cols: Many): (" + myData.ColourOrDelete + ")");
// CommonExcelClasses.ButtonUpdateLabel(btnDealWithManyDuplicates, "Duplicates (Cols: Many)");
CommonExcelClasses.ButtonUpdateLabel(btnLoadADGroupIntoSpreadsheet, "AD Group Members");
CommonExcelClasses.ButtonUpdateLabel(btnLoadADGroupIntoSpreadsheetActiveCell, "AD Members - Active Cell");
CommonExcelClasses.ButtonUpdateLabel(btnReadUsersGroupMembership, "Users AD Membership");
CommonExcelClasses.ButtonUpdateLabel(btnReadUsersGroupMembershipActiveCell, "Details from AD Name");
CommonExcelClasses.ButtonUpdateLabel(btnWriteTimeSheet, "Update timesheet");
CommonExcelClasses.ButtonUpdateLabel(btnPingServers, "Ping Servers");
}
}
}
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;
}
internal void updateTimeSheet(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;
bool boolTurnOffScreen = myData.TurnOffScreenValidation;
bool boolSaveLastRowNo = myData.TimeSheetGetRowNo;
decimal intRowCount = myData.TimeSheetRowNo;
#endregion
try
{
#region [Declare and instantiate variables for worksheet/book]
// need to loop entire workbook
Excel.Workbook Wkb = xls.ActiveWorkbook;
Excel.Worksheet Wks;
Wks = Wkb.ActiveSheet;
// will define start row later
decimal intLastRow = CommonExcelClasses.getLastRow(Wks);
int intExaminCol = CommonExcelClasses.getExcelColumnNumber("N");
string strMessage;
DialogResult dlgResult = DialogResult.Yes;
#endregion
#region [Display a Message?]
if (boolDisplayInitialMessage)
{
strMessage = "Correct: " + Wks.Name + LF +
" (starting at row:" + intRowCount.ToString() + ")";
if (booltimeTaken)
{
strMessage = strMessage + LF + " and display the time taken";
}
strMessage = strMessage + "?";
dlgResult = MessageBox.Show(strMessage, "Correct Timesheet", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);
}
#endregion
#region [Start of work]
if (dlgResult == DialogResult.Yes)
{
DateTime dteStart = DateTime.Now;
// might put this under an option
if (boolTurnOffScreen)
{
CommonExcelClasses.turnAppSettings("Off", xls, myData.TestCode);
}
#region [Start of loop]
while (intRowCount <= intLastRow)
{
if (!CommonExcelClasses.isEmptyCell(Wks.Cells[intRowCount, intExaminCol]))
{
if (boolSaveLastRowNo)
{
startOfWeekCheck(Wks, intRowCount, myData.TimeSheetRowNo);
}
// is it a valid day
if (CommonExcelClasses.dayCheck(Wks.Cells[intRowCount, intExaminCol].Value.ToString()))
{
decimal intStartOfWeekRow = intRowCount;
string strDay = Wks.Cells[intRowCount, intExaminCol].Value.ToString();
// jump down 1 row
intRowCount++;
while (Wks.Cells[intRowCount, intExaminCol].Value.ToString() == strDay)
{
sortHours(Wks, intRowCount, intStartOfWeekRow);
fixDateCol(Wks, intRowCount, intStartOfWeekRow);
intRowCount++;
}
intRowCount--;
repairTimeRecording(Wks, intStartOfWeekRow + 1, intRowCount);
// this will add validation to the entire date section / range
CommonExcelClasses.addValidationToColumn(Wks, "Q", intStartOfWeekRow + 1, intRowCount, "=rangeCategory");
}
}
intRowCount++;
}
#endregion
#endregion
#region [Display Complete Message]
if (boolDisplayCompleteMessage)
{
strMessage = "";
strMessage = strMessage + "Process Complete ...";
if (booltimeTaken)
{
DateTime dteEnd = DateTime.Now;
int milliSeconds = (int)((TimeSpan)(dteEnd - dteStart)).TotalMilliseconds;
strMessage = strMessage + "that took {TotalMilliseconds} " + milliSeconds;
}
CommonExcelClasses.MsgBox(strMessage); // localisation?
}
}
if (boolTurnOffScreen)
{
CommonExcelClasses.turnAppSettings("On", xls, myData.TestCode);
}
#endregion
#region [Release memory]
Marshal.ReleaseComObject(Wks);
Marshal.ReleaseComObject(Wkb);
#endregion
}
catch (System.Exception excpt)
{
CommonExcelClasses.MsgBox("Ther was an error - around row number" + intRowCount.ToString(), "Error");
CommonExcelClasses.turnAppSettings("On", xls, myData.TestCode);
Console.WriteLine(excpt.Message);
}
}
// 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);
}
}
/// <summary>
/// dealWithManyDuplicates
/// Loops down many columns looking for duplicates
/// </summary>
internal void dealWithManyDuplicates(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 intColumToCheck = myData.DupliateColumnToCheck;
decimal intNoCheckCols = myData.NoOfColumnsToCheck; // will replacce with last row? or option on settings
#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)intColumToCheck);
DialogResult dlgResult = DialogResult.Yes;
string strMessage;
// start of loop
int intLastRow = CommonExcelClasses.getLastRow(Wks);
decimal intSourceRow = intStartRow;
decimal intLastCol = CommonExcelClasses.getLastCol(Wks);
#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);
// just for all cols this once
// 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 intStartCol = 1;
decimal intSourceCol = 1;
decimal curNoRecords = 0;
decimal[] arrRows = new decimal[100];
int intRowArrayPointer = 0;
if (boolTurnOffScreen)
{
CommonExcelClasses.turnAppSettings("Off", xls, myData.TestCode);
}
#region {start of loop into array}
while (!CommonExcelClasses.isEmptyCell(Wks.Cells[intSourceRow, intColumToCheck]))
{
// col loop here
for (intSourceCol = intStartCol; intSourceCol <= intLastCol; intSourceCol++)
{
if (Wks.Cells[intSourceRow, intSourceCol].Value != Wks.Cells[intSourceRow + 1, intSourceCol].Value)
{
break;
}
}
// if all columns were the same
if (intSourceCol == (intColumToCheck + intNoCheckCols))
{
curNoRecords++;
if (strColourOrDelete == "Colour")
{
CommonExcelClasses.colourCells(Wks, (intSourceRow + 1), "Error", intNoCheckCols, clrFoundForeColour, clrFoundBackColour, false);
}
else if (strColourOrDelete == "Delete")
{
Wks.Rows[intSourceRow].Delete();
intSourceRow--;
}
else
{
arrRows[intRowArrayPointer] = (intSourceRow + 1);
intRowArrayPointer++;
// save row numbers to array to clear after
// colourCells(Wks, (intSourceRow + 1), strColourOrDelete, intNoCheckCols, clrFoundForeColour, clrFoundBackColour, false);
}
if (CommonExcelClasses.isEmptyCell(Wks.Cells[intSourceRow + 1, intColumToCheck]))
{
break;
}
}
intSourceRow++;
}
#endregion
// ok now have an array
#region [Deal with result array]
if (strColourOrDelete == "Clear")
{
for (int i = arrRows.GetLowerBound(0); i <= arrRows.GetUpperBound(0); i++)
{
if (arrRows[i] > 0)
{
CommonExcelClasses.colourCells(Wks, arrRows[i], strColourOrDelete, intNoCheckCols, clrFoundForeColour, clrFoundBackColour, false);
}
else
{
break;
}
}
}
#endregion
#region [Say complete?]
if (boolTurnOffScreen)
{
CommonExcelClasses.turnAppSettings("On", xls, myData.TestCode);
}
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
#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);
}
}
/// <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);
}
}
