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.";
});
}
}