Ejemplo n.º 1
0
 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 + ")");
 }
Ejemplo n.º 2
0
        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);
        }
Ejemplo n.º 3
0
        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 ...");
        }
Ejemplo n.º 4
0
        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();
        }
Ejemplo n.º 5
0
 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);
             }
         }
     }
 }
Ejemplo n.º 6
0
        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");
                }
            }
        }
Ejemplo n.º 7
0
        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;
        }
Ejemplo n.º 8
0
        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);
            }
        }
Ejemplo n.º 9
0
        // 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);
            }
        }
Ejemplo n.º 10
0
        /// <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);
            }
        }
Ejemplo n.º 11
0
        /// <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);
            }
        }