// repairs sums at bottom of cols A->M then S:
public void repairTimeRecording(Excel.Worksheet Wks, decimal intRowStart, decimal intRowEnd)
{
decimal intCol_Start = CommonExcelClasses.getExcelColumnNumber("A");
decimal intCol_End = CommonExcelClasses.getExcelColumnNumber("M");
// string strDeltaCol, strDeltaRange, strSumString;
string strDeltaCol;
string strSumString;
decimal intDeltaA;
for (intDeltaA = intCol_Start; intDeltaA <= intCol_End; intDeltaA++)
{
strDeltaCol = CommonExcelClasses.getExcelColumnLetter((int)intDeltaA);
strSumString = CommonExcelClasses.createFormula(strDeltaCol, intRowStart, intRowEnd);
Wks.Cells[intRowEnd + 1, intDeltaA].Value = strSumString;
}
// ' finally do S
intDeltaA = 19;
strDeltaCol = CommonExcelClasses.getExcelColumnLetter((int)intDeltaA);
strSumString = CommonExcelClasses.createFormula("S", intRowStart, intRowEnd);
Wks.Cells[intRowEnd + 1, intDeltaA].Value = strSumString;
}
public void fixDateCol(Excel.Worksheet Wks, decimal intRowCount, decimal intStartOfWeekRow)
{
// 1st
// string strDateCol = CommonExcelClasses.getExcelColumnLetter(C_COL_DATE);
// string strRangeDateCol = "=" + strDateCol + intStartOfWeekRow;
// Wks.Cells[intRowCount, C_COL_CATEGORY].Value = strRangeDateCol;
// 2nd
Wks.Cells[intRowCount, C_COL_DATE].Value = "=" + CommonExcelClasses.getExcelColumnLetter(C_COL_DATE) + intStartOfWeekRow;
}
public void pingServersIntoWorksheet(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 boolTestCode = myData.TestCode;
#endregion
#region [Declare and instantiate variables for worksheet/book]
// get worksheet name
Excel.Workbook Wkb = xls.ActiveWorkbook;
Excel.Worksheet Wks; // get current sheet
Wks = Wkb.ActiveSheet;
string strMessage = "Ping Servers in Column: " + CommonExcelClasses.getExcelColumnLetter((int)myData.ColPingRead);
strMessage = strMessage + "and write results into column: " + CommonExcelClasses.getExcelColumnLetter((int)myData.ColPingWrite);
#endregion
#region [Ask to display a Message?]
DialogResult dlgResult = DialogResult.Yes;
if (boolDisplayInitialMessage)
{
if (booltimeTaken)
{
strMessage = strMessage + LF + " and display the time taken";
}
dlgResult = MessageBox.Show(strMessage + "?", "Active Directory", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);
}
#endregion
#region [Start of work]
if (dlgResult == DialogResult.Yes)
{
if (boolTurnOffScreen)
{
CommonExcelClasses.turnAppSettings("Off", xls, myData.TestCode);
}
DateTime dteStart = DateTime.Now;
// do work
if (boolTurnOffScreen)
{
CommonExcelClasses.turnAppSettings("On", xls, myData.TestCode);
}
#region [Display Complete Message]
if (boolDisplayCompleteMessage)
{
strMessage = "";
strMessage = strMessage + "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
}
#endregion
}
/// <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);
}
}
private void delLinesModeB(Excel.Worksheet Wks)
{
var range = Wks.UsedRange;
try
{
range.SpecialCells(XlCellType.xlCellTypeConstants).EntireRow.Hidden = true;
range.SpecialCells(XlCellType.xlCellTypeVisible).Delete(XlDeleteShiftDirection.xlShiftUp);
range.EntireRow.Hidden = false;
Excel.Range xlCell;
// int intRowFirst = 2;
int intRowLast = CommonExcelClasses.getLastRow(Wks);
int intColLast = CommonExcelClasses.getLastCol(Wks);
string strLastCol = CommonExcelClasses.getExcelColumnLetter(intColLast);
int intRowToStartFrom = 1;
int intColScore = 0;
// loop along looking for data
for (int intRows = 2; intRows <= intRowLast; intRows++)
{
Console.WriteLine(intRows);
for (int intCols = 1; intCols <= intColLast; intCols++)
{
Console.WriteLine(intCols);
if (CommonExcelClasses.isEmptyCell(Wks.Cells[intRows, intCols]))
{
intColScore++;
}
}
if (intColScore == intColLast)
{
intRowToStartFrom = intRows;
break;
}
// re initilise the score
intColScore = 0;
}
// ask weather to delete
// create range to the end
if (intRowToStartFrom <= intRowLast)
{
intRowToStartFrom = (intRowToStartFrom + 3);
string strRange = "A" + intRowToStartFrom + ":" + strLastCol + intRowLast;
xlCell = Wks.get_Range(strRange);
// DialogResult dlgResult = MessageBox.Show("Carry out deleting rows? range is: " + strRange, "Question", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);
// if (dlgResult == DialogResult.Yes)
// {
// and delete it
xlCell.EntireRow.Delete(Excel.XlDirection.xlUp);
this.Application.ActiveWorkbook.Save();
// }
}
range.SpecialCells(XlCellType.xlCellTypeConstants).EntireRow.Hidden = false;
}
catch (System.Exception excpt)
{
CommonExcelClasses.MsgBox("There are no lines to delete", "Error");
Console.WriteLine(excpt.Message);
}
}