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