示例#1
0
        public void getUsersGroupMembership(Excel.Worksheet Wks, string strUserName)
        {
            CommonExcelClasses.setCursorToWaiting();

            try
            {
                myData = myData.LoadMyData();               // read data from settings file

                bool boolTestCode = myData.TestCode;

                // Connection information
                // var connectionString = "LDAP://domain.com/DC=domain,DC=com";
                string connectionString = "LDAP://subsea7.net/DC=subsea7,DC=net";

                // Split the LDAP Uri
                var uri       = new Uri(connectionString);
                var host      = uri.Host;
                var container = uri.Segments.Count() >= 1 ? uri.Segments[1] : "";

                // Create context to connect to AD
                var princContext = new PrincipalContext(ContextType.Domain, host, container);

                // Get User
                UserPrincipal user = UserPrincipal.FindByIdentity(princContext, IdentityType.SamAccountName, strUserName);

                int intRow = 2;
                int intCol = 1;
                // Browse user's groups
                foreach (GroupPrincipal group in user.GetGroups())
                {
                    intCol = 1;

                    Wks.Cells[intRow, intCol].Value = group.Name;
                    intCol++;

                    Wks.Cells[intRow, intCol].Value = group.Description;
                    intCol++;

                    Wks.Cells[intRow, intCol].Value = group.IsSecurityGroup;
                    intCol++;

                    Wks.Cells[intRow, intCol].Value = group.GroupScope.ToString();
                    intCol++;

                    intRow++;

                    Console.Out.WriteLine(group.Name);
                }
            }
            catch (Exception excpt)
            {
                CommonExcelClasses.MsgBox("There was a problem: " + excpt.Message + " was the variable a User?");
                Console.WriteLine(excpt.Message);
                // throw;
            }

            CommonExcelClasses.setCursorToDefault();
        }
示例#2
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 ...");
        }
示例#3
0
        public void sortHours(Excel.Worksheet Wks, decimal intRowCount, decimal intStartOfWeekRow)
        {
            string strSearchCat = "";

            if (!CommonExcelClasses.isEmptyCell(Wks.Cells[intRowCount, C_COL_CATEGORY]))
            {
                strSearchCat = Wks.Cells[intRowCount, C_COL_CATEGORY].Value.Trim().ToString();          // get the category
                Wks.Cells[intRowCount, C_COL_CATEGORY].Value = strSearchCat.Trim();                     // put it back trimmed?
            }

            Excel.Range xlCell;

            if (strSearchCat == "!NON WORKING")
            {
                Wks.Cells[intRowCount, C_COL_TOTAL].Value = "";
            }
            else
            {
                // need to put hours sum in if not there
                if (Wks.Cells[intRowCount, C_COL_TOTAL].Value == 0)
                {
                    string strTotalRange    = "P" + intRowCount + "-O" + intRowCount;
                    string strTotalRangeSum = "=SUM(" + strTotalRange + ")";

                    Wks.Cells[intRowCount, C_COL_TOTAL].Value = strTotalRangeSum;
                }

                string strRange = "A" + intRowCount.ToString() + ":" + "M" + intRowCount.ToString();
                xlCell = Wks.get_Range(strRange);
                xlCell.ClearContents();

                strSearchCat = transformCat(strSearchCat);

                if (strSearchCat.Length > 0)
                {
                    decimal intTargetCol = searchForValueInHeaderCol(Wks, strSearchCat.Trim(), intStartOfWeekRow);

                    if (intTargetCol > 0)
                    {
                        strRange = "=S" + intRowCount.ToString();
                        Wks.Cells[intRowCount, intTargetCol].Value = strRange;
                    }
                    else
                    {
                        CommonExcelClasses.MsgBox("undefined - check (probably could not find category) ", "Error");
                    }
                }
            }
        }
示例#4
0
        public void readGroupUsersMembershipIntoWorksheet(Excel.Application xls, string strDoWhat)
        {
            #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   = "Get Membership of Active Directory Group: ";
            string strGroupName = Wks.Name;

            if (strDoWhat == "ActiveSheet")
            {
                strGroupName = Wks.Name;
                strMessage   = strMessage + strGroupName + LF + "into this worksheet";
            }
            else
            {
                Excel.Range xlCell = xls.ActiveCell;
                strGroupName = xlCell.Value.ToString();
                strMessage   = strMessage + strGroupName + LF + "into new worksheet";
            }

            #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;


                if (strDoWhat == "SheetName")
                {
                    CommonExcelClasses.zapWorksheet(Wks);
                }
                else
                {
                    int i = 1;

                    string strSheetName = strGroupName;
                    while (CommonExcelClasses.WorksheetExist(Wkb, strSheetName))
                    {
                        // for (int i = 1; i <= 10; i++)

                        strSheetName = strGroupName + "-0" + i.ToString();
                        i++;
                    }
                    if (!CommonExcelClasses.WorksheetExist(Wkb, strSheetName))
                    {
                        // do a loop here checking for a free or unused name
                        Wks      = Wkb.Worksheets.Add(Type.Missing, Wkb.Worksheets[Wkb.Worksheets.Count], 1, XlSheetType.xlWorksheet);
                        Wks.Name = strSheetName;
                        Wks.Select(true);
                    }
                }


                getGroupUserMembership(Wks, strGroupName);

                writeHeaders(Wks, "ADUsers", false);
                CommonExcelClasses.sortSheet(Wks, 2);

                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
        }
示例#5
0
        public void getGroupUserMembership(Excel.Worksheet Wks, string strGroupName)
        {
            CommonExcelClasses.setCursorToWaiting();

            try
            {
                myData = myData.LoadMyData();               // read data from settings file

                bool boolTestCode = myData.TestCode;

                // can I used the same code for both?

                // Connection information
                // var connectionString = "LDAP://domain.com/DC=domain,DC=com";
                string connectionString = "LDAP://subsea7.net/DC=subsea7,DC=net";

                // Split the LDAP Uri
                var uri       = new Uri(connectionString);
                var host      = uri.Host;
                var container = uri.Segments.Count() >= 1 ? uri.Segments[1] : "";

                int intRow = 2;
                int intCol = 1;

                var princContext = new PrincipalContext(ContextType.Domain, host, container);

                if (boolTestCode)
                {
                    // Create context to connect to AD

                    // Get group
                    GroupPrincipal    qbeGroup = new GroupPrincipal(princContext, strGroupName);
                    PrincipalSearcher srch     = new PrincipalSearcher(qbeGroup);


                    // find all matches
                    foreach (var found in srch.FindAll())
                    {
                        if (found is GroupPrincipal foundGroup)
                        {
                            // iterate over members
                            foreach (Principal user in foundGroup.GetMembers())
                            {
                                intCol = 1;

                                Wks.Cells[intRow, intCol].Value = user.SamAccountName;
                                intCol++;

                                Wks.Cells[intRow, intCol].Value = user.DisplayName;
                                intCol++;

                                Wks.Cells[intRow, intCol].Value = user.Description;
                                intCol++;


                                intRow++;
                            }
                        }
                    }
                }
                else
                {
                    // PrincipalContext princContext = new PrincipalContext(ContextType.Domain);
                    GroupPrincipal group = GroupPrincipal.FindByIdentity(princContext, strGroupName);
                    if (group != null)
                    {
                        // iterate over members
                        foreach (Principal p in group.GetMembers())
                        {
                            Console.WriteLine("{0}: {1}", p.StructuralObjectClass, p.DisplayName);

                            // do whatever you need to do to those members
                            if (p is UserPrincipal User)
                            {
                                intCol = 1;

                                Wks.Cells[intRow, intCol].Value = User.SamAccountName;
                                intCol++;

                                Wks.Cells[intRow, intCol].Value = User.DisplayName;
                                intCol++;

                                Wks.Cells[intRow, intCol].Value = User.Description;
                                intCol++;

                                Wks.Cells[intRow, intCol].Value = User.IsAccountLockedOut();
                                intCol++;

                                intRow++;
                            }
                        }
                    }
                }
            }
            catch (Exception excpt)
            {
                CommonExcelClasses.MsgBox("There was a problem: " + excpt.Message + " was the variable a Group?");
                Console.WriteLine(excpt.Message);

                throw;
            }

            CommonExcelClasses.setCursorToDefault();
        }
示例#6
0
        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
        }
示例#7
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);
            }
        }
示例#8
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);
            }
        }
示例#9
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);
            }
        }
示例#10
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);
            }
        }
示例#11
0
        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);
            }
        }