Exemple #1
0
        public void compareSheets_graveYard(Excel.Workbook Wkb)
        {
            Excel.Worksheet Wks1;               // get current sheet
            Excel.Worksheet Wks2;               // get sheet next door
            string          strClearOrColour = "Colour";

            Wks1 = Wkb.ActiveSheet;
            Wks2 = Wkb.Sheets[Wks1.Index + 1];

            DialogResult dlgResult = MessageBox.Show("Compare: Worksheet: " + Wks1.Name + " against: " + Wks2.Name + " and " + strClearOrColour + " ones which are the same?", "Compare Sheets", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);

            if (dlgResult == DialogResult.Yes)
            {
                int intTargetRow = 0;
                int intStartRow  = 2;

                // how may columns to check ?
                int intNoCheckCols       = 5;       // for later loop?
                int intStartColumToCheck = 1;
                int intColScore          = 1;

                string strValue1 = "";

                int intSheetLastRow1 = CommonExcelClasses.getLastRow(Wks1);
                int intSheetLastRow2 = CommonExcelClasses.getLastRow(Wks2);

                for (int intSourceRow = intStartRow; intSourceRow <= intSheetLastRow1; intSourceRow++)
                {
                    // read in vlaue from sheet
                    // maybe I should ready all into arrayS?

                    strValue1 = Wks1.Cells[intSourceRow, intStartColumToCheck].Value;

                    intTargetRow = CommonExcelClasses.searchForValue(Wks2, strValue1, intStartColumToCheck);

                    if (intTargetRow > 0)
                    {
                        //  start from correct column
                        for (int intColCount = intStartColumToCheck; intColCount <= intNoCheckCols; intColCount++)
                        {
                            // Compare cells directly
                            if (Wks1.Cells[intSourceRow, intColCount].Value == Wks2.Cells[intTargetRow, intColCount].Value)
                            {
                                intColScore++;
                            }
                        }

                        // Score system = if all the same then can blue it
                        if (intColScore == intNoCheckCols)
                        {
                            for (int intColCount = intStartColumToCheck; intColCount <= intNoCheckCols; intColCount++)
                            {
                                if (strClearOrColour == "Colour")
                                {
                                    Wks1.Cells[intSourceRow, intColCount].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);;
                                }
                            }
                        }

                        intColScore = 1;
                    }
                }
            }
        }
Exemple #2
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);
            }
        }