public bool createBlankXL(string file, string pid, List<WR> data, List<DevT> DevTrack) { bool result = false; List<string> cautions = new List<string>(); Excel.Application excelApp = null; Excel.Workbook workbook = null; Excel.Sheets sheets = null; Excel.Worksheet dataSheet = null; int rowS = 3; int colS = 1; try { excelApp = new Excel.Application(); workbook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet); sheets = workbook.Sheets; dataSheet = sheets[1]; dataSheet.Name = "Data"; int p = 0; #region Header format dataSheet.Cells[rowS - 2, colS] = pid; dataSheet.Cells[rowS - 2, colS + 1] = "Initialized: " + DateTime.Now.Month.ToString("00") + "/" + DateTime.Now.Day.ToString("00") + "/" + DateTime.Now.Year.ToString().Substring(2, 2); dataSheet.Cells[rowS - 1, colS + p] = "JIRA ID"; p++; dataSheet.Cells[rowS - 1, colS + p] = "Variances"; p++; dataSheet.Cells[rowS - 1, colS + p] = "WR"; p++; dataSheet.Cells[rowS - 1, colS + p] = "Issue Type"; p++; dataSheet.Cells[rowS - 1, colS + p] = "Team"; p++; dataSheet.Cells[rowS - 1, colS + p] = "Product"; p++; dataSheet.Cells[rowS - 1, colS + p] = "Release Submitted Against"; p++; dataSheet.Cells[rowS - 1, colS + p] = "Summary"; p++; dataSheet.Cells[rowS - 1, colS + p] = "Proj. Del. Priority"; //dataSheet.Cells[rowS - 1, colS + p] = "Variance Status"; p++; dataSheet.Cells[rowS - 1, colS + p] = "IV Status"; p++; dataSheet.Cells[rowS - 1, colS + p] = "Original Statuses"; p++; dataSheet.Cells[rowS - 1, colS + p] = "Status Change?"; p++; dataSheet.Cells[rowS - 1, colS + p] = "Current Statuses"; p++; dataSheet.Cells[rowS - 1, colS + p] = "Original Commitment"; p++; dataSheet.Cells[rowS - 1, colS + p] = "Change?"; p++; dataSheet.Cells[rowS - 1, colS + p] = "Current Commitment"; p++; dataSheet.Cells[rowS - 1, colS + p] = "Original Release Date"; p++; dataSheet.Cells[rowS - 1, colS + p] = "Date Change?"; p++; dataSheet.Cells[rowS - 1, colS + p] = "Current Release Date"; p++; dataSheet.Cells[rowS - 1, colS + p] = "Original Target Release"; p++; dataSheet.Cells[rowS - 1, colS + p] = "Rel. Change?"; p++; dataSheet.Cells[rowS - 1, colS + p] = "Current Target Release"; p++; dataSheet.Cells[rowS - 1, colS + p] = "Created Date"; p++; dataSheet.Cells[rowS - 1, colS + p] = "Requested Date"; p++; dataSheet.Cells[rowS - 1, colS + p] = "Resolution Date"; p++; dataSheet.Cells[rowS - 1, colS + p] = "Required for Certification"; p++; dataSheet.Cells[rowS - 1, colS + p] = "Justification"; p++; #endregion //string test = null; int i = rowS; double j = 100.0; double k = j / data.Count(); List<string> JKEYS = new List<string>(); JKEYS.Add(" "); bool IVTest = true; bool IsFilterStatus = false; bool ivStatCert = true; Dispatcher.Invoke((System.Action)delegate() { IVTest = IVCheck.IsChecked.Value; IsFilterStatus = StatusFilter.IsChecked.Value; if (ivStats.Text != "DevTrack") { ivStatCert = false; } }); #region data formatting foreach (WR dd in data) { foreach (Jissues info in dd.issues) { //foreach (Jissues info in dd.issues) { WRQuery WRQ = new WRQuery(info, pid); if (!ContainsLoop(JKEYS,WRQ.JKey)) { int o = 0; string IVValue = ""; if (IVTest&&DevTrack!=null) { IVValue = getIV(WRQ.WR, DevTrack); } else { IVValue = WRQ.Variances; } dataSheet.Cells[i, colS + o] = WRQ.JKey;//JIRA ID JKEYS.Add(WRQ.JKey); o++; dataSheet.Cells[i, colS + o] = IVValue; o++; dataSheet.Cells[i, colS + o] = WRQ.WR; o++; dataSheet.Cells[i, colS + o] = WRQ.IssueType; o++; dataSheet.Cells[i, colS + o] = WRQ.Suite;//just team //getSuite(WRQ.JKey); o++; dataSheet.Cells[i, colS + o] = WRQ.Product; o++; dataSheet.Cells[i, colS + o] = WRQ.CurrentRelease; o++; dataSheet.Cells[i, colS + o] = "'"+WRQ.Summary; o++; dataSheet.Cells[i, colS + o] = WRQ.ProjDeliveryPriority; o++; if (ivStatCert&&DevTrack!=null) { dataSheet.Cells[i, colS + o] = getIVStatus(WRQ.WR, DevTrack); } else { dataSheet.Cells[i, colS + o] = WRQ.FirstVarianceStatus; } o++; //if (DevTrack != null) { // dataSheet.Cells[i, colS + o] = "DevT: " + getDStatus(WRQ.WR, DevTrack) + Environment.NewLine + "JIRA as " + WRQ.Resolution + Environment.NewLine + WRQ.Status; //} else { dataSheet.Cells[i, colS + o] = WRQ.Resolution+Environment.NewLine+WRQ.Status; //} o++; dataSheet.Cells[i, colS + o] = "No Change"; o++; //if (DevTrack != null) { // dataSheet.Cells[i, colS + o] = "DevT: " + getDStatus(WRQ.WR, DevTrack) + Environment.NewLine + "JIRA as " + WRQ.Resolution + Environment.NewLine + WRQ.Status; //} else { dataSheet.Cells[i, colS + o] = WRQ.Resolution + Environment.NewLine + WRQ.Status; //} o++; dataSheet.Cells[i, colS + o] = WRQ.Commitment; o++; dataSheet.Cells[i, colS + o] = "No Change"; o++;//change? dataSheet.Cells[i, colS + o] = WRQ.Commitment; o++;//latest commitment dataSheet.Cells[i, colS + o] = WRQ.DueDate; o++; dataSheet.Cells[i, colS + o] = "No Change"; o++;//change? dataSheet.Cells[i, colS + o] = WRQ.DueDate; o++;//latest planned due date dataSheet.Cells[i, colS + o] = WRQ.TargetRelease; o++; dataSheet.Cells[i, colS + o] = "No Change"; o++;//change? dataSheet.Cells[i, colS + o] = WRQ.TargetRelease; o++;//latest target release dataSheet.Cells[i, colS + o] = WRQ.Created; o++; dataSheet.Cells[i, colS + o] = WRQ.RequestedDate; o++; dataSheet.Cells[i, colS + o] = WRQ.ResolutionDate; i++; } } Dispatcher.Invoke((System.Action)delegate() { pBar.Value += k; }); } #endregion #region Color, width, borders, etc int maxRowsA = dataSheet.UsedRange.Rows.Count; Excel.Range rangg = dataSheet.get_Range("A2:AA"+maxRowsA.ToString()); dataSheet.ListObjects.AddEx(XlListObjectSourceType.xlSrcRange, rangg, Type.Missing, Excel.XlYesNoGuess.xlYes, Type.Missing).Name = "MyTableStyle"; dataSheet.ListObjects.get_Item("MyTableStyle").TableStyle = "TableStyleMedium1"; dataSheet.get_Range("A:AA", Type.Missing).EntireColumn.VerticalAlignment = XlVAlign.xlVAlignCenter; dataSheet.get_Range("B:Z", Type.Missing).EntireColumn.WrapText = true; dataSheet.get_Range("A:F", Type.Missing).EntireColumn.ColumnWidth = 12; dataSheet.get_Range("G:G", Type.Missing).EntireColumn.ColumnWidth = 21; dataSheet.get_Range("C:C", Type.Missing).EntireColumn.ColumnWidth = 8; dataSheet.get_Range("H:H", Type.Missing).EntireColumn.ColumnWidth = 32; dataSheet.get_Range("I:Y", Type.Missing).EntireColumn.ColumnWidth = 18; dataSheet.get_Range("Z:Z", Type.Missing).EntireColumn.ColumnWidth = 15; dataSheet.get_Range("AA:AA", Type.Missing).EntireColumn.ColumnWidth = 32; dataSheet.get_Range("H:H", Type.Missing).EntireColumn.HorizontalAlignment = XlHAlign.xlHAlignCenter; dataSheet.get_Range("C:C", Type.Missing).EntireColumn.HorizontalAlignment = XlHAlign.xlHAlignCenter; dataSheet.get_Range("I:Y", Type.Missing).EntireColumn.HorizontalAlignment = XlHAlign.xlHAlignCenter; dataSheet.get_Range("A:AA", Type.Missing).Cells.Font.Size = 8; dataSheet.get_Range("A2", "AA2").Cells.Font.Size = 11; dataSheet.get_Range("A1", Type.Missing).Cells.Font.Size = 11; dataSheet.get_Range("A2", "AA2").Cells.Font.Bold = true; dataSheet.get_Range("A1", Type.Missing).Cells.Font.Bold = true; //dataSheet.get_Range("A:G", Type.Missing).EntireColumn.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.SkyBlue); dataSheet.get_Range("A2", "AA2").HorizontalAlignment = XlHAlign.xlHAlignLeft; dataSheet.get_Range("A2", "AA2").Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black); dataSheet.get_Range("A2", "I2").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DeepSkyBlue); dataSheet.get_Range("J2", "M2").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Orange); dataSheet.get_Range("N2", "Y2").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.MediumSeaGreen); dataSheet.get_Range("Z2", "AA2").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightCoral); dataSheet.get_Range("A1", "AA1").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White); dataSheet.get_Range("A:AA", Type.Missing).Borders.LineStyle = Excel.XlLineStyle.xlContinuous; dataSheet.get_Range("A1", "AA1").Borders.LineStyle = XlLineStyle.xlLineStyleNone; dataSheet.get_Range("I1", "AA1").HorizontalAlignment = XlHAlign.xlHAlignLeft; #endregion //excelApp.Cells.Locked = true; //excelApp.get_Range("A:S",Type.Missing).Locked = true; Dispatcher.Invoke((System.Action)delegate() { status.Content = "Checking for errors..."; }); List<string> NotInJIRA = new List<string>(); if (DevTrack != null) { NotInJIRA = CompareDJ(DevTrack, data); } Excel.Range merg = dataSheet.get_Range("D1", "H1"); Excel.Range mergB = dataSheet.get_Range("I1", "P1"); merg.Merge(Type.Missing); mergB.Merge(Type.Missing); dataSheet.get_Range("A1", "AA1").EntireRow.RowHeight = 50; dataSheet.get_Range("A1", Type.Missing).WrapText = true; if (cautions.Count > 0) { dataSheet.Cells[1, 4] = "Info: " + string.Join(", ", cautions); } else { dataSheet.Cells[1, 4] = ""; } if (NotInJIRA.Count > 0) { string cautCell = dataSheet.get_Range("D1", Type.Missing).Value; if (cautCell == "") { dataSheet.Cells[1, 4] = "DevT WRs not in JIRA: " + string.Join(", ", NotInJIRA); } else { dataSheet.Cells[1, 4] = cautCell + Environment.NewLine + "DevT WRs not in JIRA: " + string.Join(", ", NotInJIRA); } } //dataSheet.Cells[1, 4] = "Info: " + string.Join(", ", cautions); //if (NotInJIRA.Count > 0) { // dataSheet.Cells[1, 9] = "DevT WRs not in JIRA: " + string.Join(", ", NotInJIRA); //} Dispatcher.Invoke((System.Action)delegate() { status.Content = "Saving file..."; }); bool readOnly = true; excelApp.DisplayAlerts = false; workbook.SaveAs(file, XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, readOnly, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing); workbook.Close(true, Type.Missing, Type.Missing); excelApp.Quit(); releaseObject(rangg); releaseObject(merg); releaseObject(mergB); result = true; } catch (Exception e) { string m = e.Message; Dispatcher.Invoke((System.Action)delegate() { status.Content = "ugh Excel failed.. try again?"; }); result = false; } finally { releaseObject(dataSheet); releaseObject(sheets); releaseObject(workbook); releaseObject(excelApp); } return result; }
public bool createWRPR(string file, string pid, List<WR> data, List<DevT> DevTrack) { bool result = false; List<string> cautions = new List<string>(); Excel.Application excelApp = null; Excel.Workbook workbook = null; Excel.Sheets sheets = null; Excel.Worksheet dataSheet = null; Excel.Range xlR = null; //int rowS = 3; //int colS = 1; try { excelApp = new Excel.Application(); workbook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet); sheets = workbook.Sheets; dataSheet = sheets[1]; dataSheet.Name = DateTime.Now.Month.ToString("00") + "-" + DateTime.Now.Day.ToString("00") + "-" + DateTime.Now.Year.ToString().Substring(2, 2);; //int p = 0; #region Header format string[] headers = { "WR#","WR Status", "Variance", "Project", "DevTeam", "Product", "Version", "Severity", "Project Delivery Priority", "Need by Event/Date", "Date Created", "Linked Variances", "Summary", "Description","Certification Assessment","Certification Required" }; object[,] xlHeader = new object[1,headers.Length]; for(int i =0;i<headers.Length;i++){ xlHeader[0,i] = headers[i]; } xlR = dataSheet.Range["A1","P1"];//xlGetStr xlR.Value2 = xlHeader; #endregion dataSheet.get_Range("A:P", Type.Missing).EntireColumn.VerticalAlignment = XlVAlign.xlVAlignCenter; dataSheet.get_Range("A:P", Type.Missing).EntireColumn.WrapText = true; dataSheet.get_Range("A:P", Type.Missing).EntireColumn.ColumnWidth = 12; dataSheet.get_Range("J:L", Type.Missing).EntireColumn.ColumnWidth = 18; dataSheet.get_Range("M:O", Type.Missing).EntireColumn.ColumnWidth = 32; dataSheet.get_Range("N:N", Type.Missing).EntireColumn.ColumnWidth = 50; dataSheet.get_Range("F:F", Type.Missing).EntireColumn.ColumnWidth = 22; int rowCount = 2; //start at row 2 foreach (WR dd in data) { foreach (Jissues info in dd.issues) { WRQuery WRQ = new WRQuery(info, pid); string[] newLine = { WRQ.WR, getDStatus(WRQ.WR, DevTrack), getIV(WRQ.WR, DevTrack), pid, WRQ.Suite, WRQ.Product, WRQ.CurrentRelease, WRQ.IssueType, WRQ.ProjDeliveryPriority, getNBDE(WRQ.WR, DevTrack), WRQ.Created, WRQ.Variances, "'" + WRQ.Summary, "'" + WRQ.Description }; object[,] xlNewLine = new object[1, newLine.Length]; for (int i = 0; i < newLine.Length; i++) { xlNewLine[0, i] = newLine[i]; } xlR = dataSheet.Range["A" + rowCount, "N" + rowCount]; xlR.Value2 = xlNewLine; rowCount++; } } xlR = dataSheet.get_Range("A1:P" + (rowCount-1)); dataSheet.ListObjects.AddEx(XlListObjectSourceType.xlSrcRange, xlR, Type.Missing, Excel.XlYesNoGuess.xlYes, Type.Missing).Name = "MyTableStyle"; dataSheet.ListObjects.get_Item("MyTableStyle").TableStyle = "TableStyleLight8"; dataSheet.get_Range("A1", "P1").EntireRow.RowHeight = 15; bool readOnly = false; excelApp.DisplayAlerts = false; workbook.SaveAs(file, XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, readOnly, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing); workbook.Close(true, Type.Missing, Type.Missing); excelApp.Quit(); //releaseObject(rangg); //releaseObject(merg); //releaseObject(mergB); result = true; } catch (Exception e) { string m = e.Message; Dispatcher.Invoke((System.Action)delegate() { status.Content = "ugh Excel failed.. try again?"; }); result = false; } finally { releaseObject(xlR); releaseObject(dataSheet); releaseObject(sheets); releaseObject(workbook); releaseObject(excelApp); } return result; }
private void updater(string fileName) { bool Jspecial = false; bool ivstatusCert = true; List<string> cautions = new List<string>(); Dispatcher.Invoke((System.Action)delegate() { pBar.Visibility = Visibility.Visible; pBox.IsEnabled = false; ITObtn.IsEnabled = false; updateBtn.IsEnabled = false; pBar.Value = 0; initBtn.IsEnabled = false; status.Content = "Analyzing Spreadsheet Validity..."; }); Excel.Application excelApp = new Excel.Application(); ; excelApp.DisplayAlerts = false; string fn = fileName; Excel.Workbook wrokbak = excelApp.Workbooks.Open(fn, false, false, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Excel.Sheets sheets = wrokbak.Sheets; Excel.Worksheet dataSheet = wrokbak.Sheets[1]; int maxRow = dataSheet.UsedRange.Rows.Count; string PID = "ASDF DNE N/A"; try { PID = dataSheet.Cells[1, 1].Value.ToString(); } catch { } if (PID.Substring(0, 3) == "J*:") { Jspecial = true; PID = PID.Substring(3, PID.Length - 3); } string DDate = "Initialized: 01/01/01"; try { DDate = dataSheet.Cells[1, 2].Value.ToString(); } catch { } List<string> DevTcheck = getPID(); //string tedfr = DDate.Substring(13, 8); if (getIndex(DevTcheck, PID) >= 0 || Jspecial) { // return false; //} int wsC = wrokbak.Sheets.Count; Excel.Worksheet nweshat = (Worksheet)sheets.Add(Type.Missing, sheets[1], Type.Missing, Type.Missing); if (wsC < 2) { nweshat.Name = "Update"; } else { nweshat.Name = "Update" + wsC.ToString(); } int newMaxRow = nweshat.UsedRange.Rows.Count; //get current WRs string speedval = "Gentle"; Dispatcher.Invoke((System.Action)delegate() { pBar.Visibility = Visibility.Visible; pBox.IsEnabled = false; ITObtn.IsEnabled = false; updateBtn.IsEnabled = false; pBar.Value = 0; initBtn.IsEnabled = false; speedval = dbSpeed.Text; if (ivStats.Text != "DevTrack"||Jspecial) { ivstatusCert = false; } if (speedval != "Gentle") { status.Content = "Reading Databases..."; } else { status.Content = "Gently Reading Databases..."; } }); List<DevT> LinkedWRs = new List<DevT>(); if (!Jspecial) { LinkedWRs = getWRs(PID); } List<WR> js = new List<WR>(); List<string> urls = new List<string>(); if (!Jspecial) { urls = makeUrl(LinkedWRs); } int speed = 1000; if (speedval == "Quick") { speed = 260; } else if (speedval == "DDOS") { speed = 50; } double k = 0.0; if (Jspecial) { int total = getJIRAtotal(PID); double j = total / 50.0; k = 100.0 / j; //List<WR> JIRAs = new List<WR>(); for (int i = 0; i <= total; i += 50) { js.Add(pJSON(PID + "&startAt=" + i + "&maxResults=50")); Dispatcher.Invoke((System.Action)delegate() { pBar.Value += k; }); } } else { //get current JIRA double j = 100.0; k = j / urls.Count(); foreach (string qry in urls) { js.Add(pJSON(qry)); Dispatcher.Invoke((System.Action)delegate() { pBar.Value += k; }); Thread.Sleep(speed); } } //paste into new sheet Dispatcher.Invoke((System.Action)delegate() { status.Content = "Wrestling Excel..."; pBar.Value = 0; }); if (js.Count != 0) { int rowS = 3; int colS = 1; #region header for updates int p = 0; nweshat.Cells[rowS - 2, colS] = "Updated: " + DateTime.Now.Month.ToString("00") + "/" + DateTime.Now.Day.ToString("00") + "/" + DateTime.Now.Year.ToString().Substring(2, 2); nweshat.Cells[rowS - 1, colS + p] = "JIRA ID"; //A p++; nweshat.Cells[rowS - 1, colS + p] = "Variances"; //B p++; nweshat.Cells[rowS - 1, colS + p] = "WR"; //C p++; nweshat.Cells[rowS - 1, colS + p] = "Issue Type"; //D p++; nweshat.Cells[rowS - 1, colS + p] = "Team"; //D p++; nweshat.Cells[rowS - 1, colS + p] = "Product"; //E p++; nweshat.Cells[rowS - 1, colS + p] = "Summary"; //F p++; nweshat.Cells[rowS - 1, colS + p] = "Proj Del Priority"; //G //nweshat.Cells[rowS - 1, colS + p] = "Variance Status"; //G p++; nweshat.Cells[rowS - 1, colS + p] = "IV Status"; //H p++; nweshat.Cells[rowS - 1, colS + p] = "Statuses"; //H p++; nweshat.Cells[rowS - 1, colS + p] = "Latest Commitment"; //I +Resolution p++; nweshat.Cells[rowS - 1, colS + p] = "Planned Release Date"; //J p++; nweshat.Cells[rowS - 1, colS + p] = "Target Release"; //L p++; nweshat.Cells[rowS - 1, colS + p] = "Created Date"; //M p++; nweshat.Cells[rowS - 1, colS + p] = "Requested Date"; //N p++; nweshat.Cells[rowS - 1, colS + p] = "Resolution Date"; //O p++; #endregion int i = rowS; bool IVTest = true; bool statusCheck = false; Dispatcher.Invoke((System.Action)delegate() { IVTest = IVCheck.IsChecked.Value; statusCheck = StatusFilter.IsChecked.Value; }); #region data tags for updates List<string> JKeys = getList(dataSheet, 1); foreach (WR dd in js) { //List<WRQuery> WRQl = new List<WRQuery>(); //Parallel.ForEach(dd.issues, info => { // WRQuery WRQ = new WRQuery(info, PID); // WRQl.Add(WRQ); //}); //Parallel.ForEach(js, dd => { foreach (Jissues info in dd.issues) { WRQuery WRQ = new WRQuery(info, PID); int o = 0; string IVvalue = ""; if (IVTest&&!Jspecial) { IVvalue = getIV(WRQ.WR, LinkedWRs); } else { IVvalue = WRQ.Variances; } string VarState = ""; if (ivstatusCert) { VarState = "DevT: "+getIVStatus(WRQ.WR, LinkedWRs); } else { VarState = WRQ.FirstVarianceStatus; } //string jkty = ""; //try { jkty = unnull(info.key); } catch { }//JIRA ID #A nweshat.Cells[i, colS + o] = WRQ.JKey;//JIRA ID #A o++; nweshat.Cells[i, colS + o] = IVvalue; o++; nweshat.Cells[i, colS + o] = WRQ.WR; o++; nweshat.Cells[i, colS + o] = WRQ.IssueType; o++; nweshat.Cells[i, colS + o] = WRQ.Suite; o++; nweshat.Cells[i, colS + o] = WRQ.Product; o++; nweshat.Cells[i, colS + o] = WRQ.Summary; o++; nweshat.Cells[i, colS + o] = WRQ.ProjDeliveryPriority; o++; nweshat.Cells[i, colS + o] = VarState; o++; //if (Jspecial) { nweshat.Cells[i, colS + o] = WRQ.Resolution + Environment.NewLine + WRQ.Status; //} else { // nweshat.Cells[i, colS + o] = "DevT: " + getDStatus(WRQ.WR, LinkedWRs) + Environment.NewLine + "JIRA as " + WRQ.Resolution + Environment.NewLine + WRQ.Status; //} o++; nweshat.Cells[i, colS + o] = WRQ.Commitment; o++; nweshat.Cells[i, colS + o] = WRQ.DueDate; o++; //nweshat.Cells[i, colS + o] = WRQ.CurrentRelease; //o++; nweshat.Cells[i, colS + o] = WRQ.TargetRelease; o++; nweshat.Cells[i, colS + o] = WRQ.Created; o++; nweshat.Cells[i, colS + o] = WRQ.RequestedDate; o++; nweshat.Cells[i, colS + o] = WRQ.ResolutionDate; o++; i++; bool JRKeyExists = updateMain(dataSheet, JKeys, VarState, WRQ.IssueType, WRQ.ProjDeliveryPriority, WRQ.JKey, WRQ.Commitment, WRQ.DueDate, WRQ.TargetRelease, WRQ.Resolution + Environment.NewLine + WRQ.Status, WRQ.WR, LinkedWRs); if (!JRKeyExists&&WRQ.Status!="Closed") { cautions.Add(WRQ.JKey + " is new!"); int MaxRowC = dataSheet.UsedRange.Rows.Count + 1; int newColC = 1; dataSheet.Cells[MaxRowC, newColC] = WRQ.JKey; dataSheet.get_Range("A"+MaxRowC, "Y"+MaxRowC).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Violet); newColC++; dataSheet.Cells[MaxRowC, newColC] = IVvalue; newColC++; dataSheet.Cells[MaxRowC, newColC] = WRQ.WR; newColC++; dataSheet.Cells[MaxRowC, newColC] = WRQ.IssueType; newColC++; dataSheet.Cells[MaxRowC, newColC] = WRQ.Suite;//Team //getSuite(WRQ.JKey); newColC++; dataSheet.Cells[MaxRowC, newColC] = WRQ.Product; newColC++; dataSheet.Cells[MaxRowC, newColC] = WRQ.CurrentRelease; newColC++; dataSheet.Cells[MaxRowC, newColC] = WRQ.Summary; newColC++; dataSheet.Cells[MaxRowC, newColC] = WRQ.ProjDeliveryPriority; newColC++; dataSheet.Cells[MaxRowC, newColC] = VarState; newColC++; //if (Jspecial) { dataSheet.Cells[MaxRowC, newColC] = WRQ.Resolution + Environment.NewLine + WRQ.Status; newColC++; dataSheet.Cells[MaxRowC, newColC] = "New Item"; newColC++; dataSheet.Cells[MaxRowC, newColC] = WRQ.Resolution + Environment.NewLine + WRQ.Status; //} else { // string AnDevTtempstat = getDStatus(WRQ.WR, LinkedWRs); // string AnVarStatus = "notsure"; // if (WRQ.Progress == AnDevTtempstat) { // AnVarStatus = WRQ.Progress; // } else { // AnVarStatus = "JIRA: " + WRQ.Progress + Environment.NewLine + "DevT: " + AnDevTtempstat; // } // dataSheet.Cells[MaxRowC, newColC] = AnVarStatus + Environment.NewLine + WRQ.Resolution + Environment.NewLine + WRQ.Status; // newColC++; // dataSheet.Cells[MaxRowC, newColC] = "New Item"; // newColC++; // dataSheet.Cells[MaxRowC, newColC] = AnVarStatus + Environment.NewLine + WRQ.Resolution + Environment.NewLine + WRQ.Status; //} //WR STATUS newColC++; dataSheet.Cells[MaxRowC, newColC] = WRQ.Commitment; newColC++; dataSheet.Cells[MaxRowC, newColC] = "New Item"; newColC++; dataSheet.Cells[MaxRowC, newColC] = WRQ.Commitment; newColC++; dataSheet.Cells[MaxRowC, newColC] = WRQ.DueDate; newColC++; dataSheet.Cells[MaxRowC, newColC] = "New Item"; newColC++; dataSheet.Cells[MaxRowC, newColC] = WRQ.DueDate; newColC++; dataSheet.Cells[MaxRowC, newColC] = WRQ.TargetRelease; newColC++; dataSheet.Cells[MaxRowC, newColC] = "New Item"; newColC++; dataSheet.Cells[MaxRowC, newColC] = WRQ.TargetRelease; newColC++; dataSheet.Cells[MaxRowC, newColC] = WRQ.Created; newColC++; dataSheet.Cells[MaxRowC, newColC] = WRQ.RequestedDate; newColC++; dataSheet.Cells[MaxRowC, newColC] = WRQ.ResolutionDate; } } Dispatcher.Invoke((System.Action)delegate() { pBar.Value += k; }); //}); } #endregion Dispatcher.Invoke((System.Action)delegate() { status.Content = "Checking for errors..."; }); List<string> newJKeys = getList(nweshat, 1); List<string> newWRcol = getList(nweshat, 3); List<int> removedWRs = new List<int>(); for (int ix = 2; ix < JKeys.Count; ix++) { int vv = getIndex(newJKeys, JKeys[ix]); if (vv < 0) {//couldn't current JKey in new JKeys removedWRs.Add(ix); } } //Parallel.ForEach(JKeys, jkuy => { // int vv = getIndex(newJKeys, jkuy); // if (vv >= 0) { // removedWRs.Add(vv); // } //}); foreach (int lineNum in removedWRs) { int unlink = lineNum + 1; dataSheet.get_Range("A" + unlink, "Y" + unlink).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Violet); //dataSheet.Cells[lineNum + 3, 1] dataSheet.Cells[unlink, 13] = "Issue Unlinked"; dataSheet.Cells[unlink, 16] = "Issue Unlinked"; dataSheet.Cells[unlink, 19] = "Issue Unlinked"; dataSheet.Cells[unlink, 23] = "Issue Unlinked"; string acceptance = ""; try { acceptance = dataSheet.Cells[unlink, 26].Value.ToString(); } catch { } if (acceptance != "") { dataSheet.Cells[unlink, 26] = "Was: " + acceptance; } } List<string> NotInJIRA = new List<string>(); foreach (DevT dt in LinkedWRs) { if (getIndex(newWRcol, dt.WR) < 0) { NotInJIRA.Add(dt.WR); } } List<string> newIssueType = getList(nweshat, 4); List<string> newProjDel = getList(nweshat, 8); string initDate = dataSheet.get_Range("B1", Type.Missing).Value; try { initDate = initDate.Substring(0, initDate.IndexOf(Environment.NewLine)); } catch { } dataSheet.Cells[1, 2] = initDate + Environment.NewLine + "Updated: " + DateTime.Now.Month.ToString("00") + "/" + DateTime.Now.Day.ToString("00") + "/" + DateTime.Now.Year.ToString().Substring(2, 2); if (cautions.Count > 0) { dataSheet.Cells[1, 4] = "New Info: " + string.Join(", ", cautions); } else { dataSheet.Cells[1, 4] = ""; } if (NotInJIRA.Count > 0) { string cautCell = dataSheet.get_Range("D1", Type.Missing).Value; if (cautCell == "") { dataSheet.Cells[1, 4] = "DevT WRs not in JIRA: " + string.Join(", ", NotInJIRA); } else { dataSheet.Cells[1, 4] = cautCell + Environment.NewLine + "DevT WRs not in JIRA: " + string.Join(", ", NotInJIRA); } } dataSheet.Select(); } Dispatcher.Invoke((System.Action)delegate() { status.Content = "Saving Excel..."; }); wrokbak.Save(); wrokbak.Close(); excelApp.Quit(); Dispatcher.Invoke((System.Action)delegate() { pBar.Visibility = Visibility.Hidden; pBar.Value = 0; pBox.IsEnabled = true; ITObtn.IsEnabled = true; //initBtn.IsEnabled = true; updateBtn.IsEnabled = true; status.Content = "Completed!"; }); releaseObject(dataSheet); releaseObject(nweshat); releaseObject(nweshat); releaseObject(sheets); releaseObject(wrokbak); releaseObject(excelApp); } else { wrokbak.Close(); excelApp.Quit(); releaseObject(dataSheet); releaseObject(sheets); releaseObject(wrokbak); releaseObject(excelApp); Dispatcher.Invoke((System.Action)delegate() { pBar.Visibility = Visibility.Hidden; pBar.Value = 0; pBox.IsEnabled = true; ITObtn.IsEnabled = true; initBtn.IsEnabled = true; updateBtn.IsEnabled = true; status.Content = "Could not verify Excel file's format."; }); } }