public Func <bool> GetFunc <T>(T marsObj)
{
return(() => {
RMarschal.ReleaseComObject(marsObj);
return true;
});
}
/// <summary>
/// Gets the name of given type library.
/// </summary>
internal static bool GetTypeLibNameForTypeLibAttrs(TaskLoggingHelper log, bool silent, TYPELIBATTR typeLibAttr, out string typeLibName)
{
typeLibName = "";
ITypeLib typeLib = null;
try
{
// load our type library
try
{
TYPELIBATTR attr = typeLibAttr;
typeLib = (ITypeLib)NativeMethods.LoadRegTypeLib(ref attr.guid, attr.wMajorVerNum, attr.wMinorVerNum, attr.lcid);
}
catch (COMException ex)
{
if (!silent)
{
log.LogWarningWithCodeFromResources("ResolveComReference.CannotLoadTypeLib", typeLibAttr.guid, typeLibAttr.wMajorVerNum, typeLibAttr.wMinorVerNum, ex.Message);
}
return(false);
}
string typeLibId = log.FormatResourceString("ResolveComReference.TypeLibAttrId", typeLibAttr.guid.ToString(), typeLibAttr.wMajorVerNum, typeLibAttr.wMinorVerNum);
return(GetTypeLibNameForITypeLib(log, silent, typeLib, typeLibId, out typeLibName));
}
finally
{
if (typeLib != null)
{
Marshal.ReleaseComObject(typeLib);
}
}
}
public Func <bool> GetFunc(Excel.Workbook oWB)
{
return(() => {
oWB.Close(SaveChanges: false);
RMarschal.ReleaseComObject(oWB);
return true;
});
}
public Func <bool> GetFunc(Excel.Application appXls)
{
return(() => {
appXls.Quit();
RMarschal.ReleaseComObject(appXls);
return true;
});
}
static void releaseObject(object obj, bool gc)
{
Marshal.ReleaseComObject(obj);
obj = null;
if (gc)
{
GC.Collect();
}
}
public static void ReleaseObject(object obj)
{
try
{
if (obj != null && Marshal.IsComObject(obj))
{
Marshal.ReleaseComObject(obj);
}
obj = null;
}
catch (Exception ex)
{
obj = null;
throw new LocalSystemException("Com Object not Released.", ex);
}
finally
{
GC.Collect();
}
}
/// <summary>
/// Uses the telemetry data to create an report in excel.
/// If the file already exists, it MUST be closed for this to work.
/// </summary>
static internal void Create(List <ScenarioRun> telemetryData, MainWindow.UserInput userInput)
{
Excel.Application excelApp = null;
try
{
excelApp = new Excel.Application();
Excel._Worksheet workSheet;
// Checks the provided name to see if it has a valid extension, and adds one if not.
string extension = Path.GetExtension(userInput.ResultFileName);
string excelExtension = ".xlsx";
string outputFilename;
if (Path.GetExtension(userInput.ResultFileName) != excelExtension)
{
outputFilename = Path.ChangeExtension(userInput.ResultFileName, excelExtension);
}
else
{
outputFilename = userInput.ResultFileName;
}
// Checks if that excel file already exists. Use the existing report if it does, otherwise create a new one.
int startColumnPosition = 1;
if (File.Exists(outputFilename))
{
// Makes sure the excel file isn't already open
while (IsFileLocked(new FileInfo(outputFilename)))
{
MessageBox.Show("Excel file is already locked and cannot be edited! Make sure that file is closed.");
}
// Load the existing excel sheet
workSheet = excelApp.Workbooks.Open(outputFilename).Sheets[1];
// Determine where the beginning of unused space is by checking the cells containing the scenario names.
for (int columnIndex = 1; columnIndex <= workSheet.Columns.Count; columnIndex += 5)
{
if (workSheet.Cells[2, columnIndex].Value == null)
{
startColumnPosition = columnIndex;
break;
}
}
}
else
{
excelApp.Workbooks.Add();
workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
}
// Set up column positions. These values are shifted over as more scenarios are added to a report.
int A = startColumnPosition;
int B = startColumnPosition + 1;
int C = startColumnPosition + 2;
int D = startColumnPosition + 3;
int eventCount = telemetryData.Count;
// Generic formatting and labels
workSheet.Cells[1, B] = userInput.BuildName;
workSheet.Cells[2, A] = userInput.ScenarioName;
workSheet.Cells[2, A].Font.Bold = true;
workSheet.Cells[3, B] = "Start";
workSheet.Cells[3, B].Font.Bold = true;
workSheet.Cells[3, C] = "End";
workSheet.Cells[3, C].Font.Bold = true;
workSheet.Cells[3, D] = "Result";
workSheet.Cells[3, D].Font.Bold = true;
Excel.Range formattingRange = workSheet.Range[workSheet.Cells[2, A], workSheet.Cells[eventCount + 8, D]];
formattingRange.NumberFormat = "0";
formattingRange.BorderAround2();
// Fill in the worksheet
for (int y = 0; y < eventCount; y++)
{
int eventRow = y + 4;
workSheet.Cells[eventRow, A] = y + 1;
workSheet.Cells[eventRow, B] = telemetryData[y].Start.Time;
workSheet.Cells[eventRow, C] = telemetryData[y].End.Time;
workSheet.Cells[eventRow, D] = $"=({ExcelColumnFromNumber(C)}{eventRow} - {ExcelColumnFromNumber(B)}{eventRow})";
}
int statsRow = telemetryData.Count + 5;
Excel.Range statsLabelsFormattingRange = workSheet.Range[workSheet.Cells[statsRow, A], workSheet.Cells[statsRow + 3, A]];
statsLabelsFormattingRange.Font.Bold = true;
string range = $"({ExcelColumnFromNumber(D)}{4}:{ExcelColumnFromNumber(D)}{eventCount + 3})";
workSheet.Cells[statsRow, A] = "High";
workSheet.Cells[statsRow, B] = $"=MAX{range}";
statsRow++;
workSheet.Cells[statsRow, A] = "Median";
workSheet.Cells[statsRow, B] = $"=MEDIAN{range}";
statsRow++;
workSheet.Cells[statsRow, A] = "Low";
workSheet.Cells[statsRow, B] = $"=MIN{range}";
statsRow++;
workSheet.Cells[statsRow, A] = $"{eventCount}th - 2nd Diff";
workSheet.Cells[statsRow, B] = $"={ExcelColumnFromNumber(D)}{eventCount + 3}-{ExcelColumnFromNumber(D)}{5}";
// Only merge cells for notes if there are notes to add.
Excel.Range notesCell = workSheet.Cells[statsRow + 2, A];
if (!string.IsNullOrEmpty(userInput.Notes))
{
notesCell.Value = userInput.Notes;
Excel.Range notes = workSheet.Range[workSheet.Cells[statsRow + 2, A], workSheet.Cells[statsRow + 2, D]].Merge();
}
// Adjust column widths to fit the content.
workSheet.Columns[A].ColumnWidth = 12;
for (int x = B; x <= D; x++)
{
workSheet.Columns[x].ColumnWidth = 8;
}
// Save the file.
workSheet.SaveAs(userInput.ResultFileName);
}
finally
{
excelApp.Workbooks.Close();
Marshal.ReleaseComObject(excelApp.Workbooks);
excelApp.Quit();
Marshal.FinalReleaseComObject(excelApp);
}
}
private void updateIncidentTracker(DataTable srcDataset, String destFilePath)
{
String destSheetName = "Incident Tracker$"; // Destination worksheet name in excel
DataTable destDataSet = getAllIncidentsFromDestFile(destSheetName, destFilePath);
// variable declaration
String incidentNumber, incidentShortDesc, incidentLongDesc, severity, status, resolutionSummary, assigneeIndividual,
customerName, incidentOpenDt, incidentAssignDt, incidentResolvedDt, assigneeGroup, originalAssigneeGroup;
// fetching relevant data from resolution summary
String keywordCategory = "CATEGORY: ", keywordRootCause = "ROOT CAUSE: ",
keywordAffItems = "AFFECTED ITEMS/SYSTEMS: ", keywordFacility = "AFFECTED FACILITIES/USERS: ", keywordOutage = "OUTAGE DURATION: ";;
String ticketType = null, category = null, subCategory = null, affctedItem = null, affectedFacility = null;
String existingState;
int noOfIncidentsUpdated = 0, noOfIncidentsAdded = 0, noOfIncidentsSkipped = 0;
DataRow[] foundRows;
Excel.Workbook MyBook = null;
Excel.Workbooks xlWorkBooks = null;
Excel.Application MyApp = null;
Excel.Worksheet MySheet = null;
try
{
MyApp = new Excel.Application();
MyApp.Visible = false;
xlWorkBooks = MyApp.Workbooks;
MyBook = xlWorkBooks.Open(destFilePath);
MySheet = MyBook.Sheets["Incident Tracker"];
int countOfIncidentFound, i = 1;
int totalCount = destDataSet.Rows.Count + 2; // one for blank row and one for getting new row for intertion
foreach (DataRow dataRow in srcDataset.Rows)
{
ticketType = category = subCategory = affctedItem = affectedFacility = null;
toolStripStatusLabel1.Text = "Processing " + i + " of " + srcDataset.Rows.Count + " incidents..";
countOfIncidentFound = -1;
incidentNumber = dataRow["Number"].ToString();
resolutionSummary = dataRow["Close notes"].ToString().Replace("'", "''");
customerName = dataRow["Customer"].ToString();
incidentShortDesc = dataRow["Short description"].ToString().Replace("'", "''");
incidentLongDesc = dataRow["Description"].ToString().Replace("'", "''");
severity = dataRow["Priority"].ToString();
status = dataRow["State"].ToString();
assigneeGroup = dataRow["Assignment group"].ToString();
originalAssigneeGroup = dataRow["Original Assignment Group "].ToString();
assigneeIndividual = dataRow["Assigned to"].ToString();
incidentOpenDt = dataRow["Opened"].ToString(); // previously it was created
incidentAssignDt = dataRow["Actual start"].ToString();
incidentResolvedDt = dataRow["Resolved"].ToString();
if (customerName == "OMI Integration Service")
{
incidentShortDesc = incidentLongDesc;
}
try
{
if (resolutionSummary.Length > 10)
{
if (resolutionSummary.Contains(keywordRootCause))
{
//ticketType = MySubString(keywordCategory.Length, resolutionSummary.IndexOf(keywordRootCause) - 2, resolutionSummary);
ticketType = MySubString(resolutionSummary.IndexOf(keywordCategory) + keywordCategory.Length, resolutionSummary.IndexOf(keywordRootCause) - 2, resolutionSummary);
}
if (resolutionSummary.Contains(keywordCategory))
{
category = MySubString(resolutionSummary.IndexOf(keywordRootCause) + keywordRootCause.Length, resolutionSummary.IndexOf(keywordAffItems) - 2, resolutionSummary);
subCategory = category.Substring(category.IndexOf("-") + 1).Trim();
category = category.Substring(0, category.IndexOf("-")).Trim();
}
if (resolutionSummary.Contains(keywordAffItems))
{
affctedItem = MySubString(resolutionSummary.IndexOf(keywordAffItems) + keywordAffItems.Length, resolutionSummary.IndexOf(keywordFacility) - 2, resolutionSummary);
}
if (resolutionSummary.Contains(keywordFacility))
{
affectedFacility = MySubString(resolutionSummary.IndexOf(keywordFacility) + keywordFacility.Length, resolutionSummary.IndexOf(keywordOutage) - 2, resolutionSummary);
if (affectedFacility.Contains("/")) // this checks if affected facility is having customer name appeneded
{
affectedFacility = affectedFacility.Substring(0, affectedFacility.IndexOf("/"));
}
}
}
}
catch (Exception ex)
{
}
// populating cat. and sub-category if ticket was reassigned to another group
if (category == null && subCategory == null && assigneeGroup != originalAssigneeGroup && (status == "Closed" || status == "Resolved"))
{
category = "Others";
subCategory = "Re-assigned";
}
// setting ticket-type value as not-applicable in case initiate application is selected
if (rbInitiate.Checked)
{
ticketType = "Not Applicable";
}
// searching for the incident from incidents picked from tracker to know its row-index
foundRows = destDataSet.Select("F1 = '" + incidentNumber + "'");
if (foundRows.Count() > 0)
{
countOfIncidentFound = destDataSet.Rows.IndexOf(foundRows[0]);
}
if (countOfIncidentFound <= 0)//countOfIncidentFound < 1) // No match found hence insertion will be performed
{
MySheet.Cells[totalCount, 1] = incidentNumber;
if (ticketType != null)
{
MySheet.Cells[totalCount, 2] = ticketType;
}
MySheet.Cells[totalCount, 3] = incidentShortDesc;
MySheet.Cells[totalCount, 4] = severity;
/* if (category != null)
* MySheet.Cells[totalCount, 5] = category; removed based on Nitish comment on 28 / 03 incident meeting */
if (subCategory != null)
{
MySheet.Cells[totalCount, 6] = subCategory;
}
if (affectedFacility != null)
{
MySheet.Cells[totalCount, 7] = affctedItem;
}
MySheet.Cells[totalCount, 8] = status;
MySheet.Cells[totalCount, 9] = resolutionSummary;
MySheet.Cells[totalCount, 10] = assigneeIndividual;
MySheet.Cells[totalCount, 11] = assigneeIndividual;
if (txtSecName.Text != "")
{
MySheet.Cells[totalCount, 12] = txtSecName.Text;
}
MySheet.Cells[totalCount, 13] = customerName;
MySheet.Cells[totalCount, 14] = incidentOpenDt;
MySheet.Cells[totalCount, 15] = incidentAssignDt;
MySheet.Cells[totalCount, 17] = incidentResolvedDt;
if (affectedFacility != null)
{
MySheet.Cells[totalCount, 31] = affectedFacility;
}
totalCount++;
noOfIncidentsAdded++;
}
else
{ // match found hence update will be performed
//MySheet.Cells[countOfIncidentFound + 2, 1] = incidentNumber;
//MySheet.Cells[countOfIncidentFound + 2, 9] = resolutionSummary;
countOfIncidentFound += 2;
existingState = (MySheet.Cells[countOfIncidentFound, 8]).Value.ToString();
if (existingState != "Closed")
{
MySheet.Cells[countOfIncidentFound, 1] = incidentNumber;
if (ticketType != null)
{
MySheet.Cells[countOfIncidentFound, 2] = ticketType;
}
MySheet.Cells[countOfIncidentFound, 3] = incidentShortDesc;
MySheet.Cells[countOfIncidentFound, 4] = severity;
/* if (category != null)
* MySheet.Cells[countOfIncidentFound, 5] = category; removed based on Nitish comment on 28/03 incident meeting */
if (subCategory != null)
{
MySheet.Cells[countOfIncidentFound, 6] = subCategory;
}
if (affectedFacility != null)
{
MySheet.Cells[countOfIncidentFound, 7] = affctedItem;
}
MySheet.Cells[countOfIncidentFound, 8] = status;
MySheet.Cells[countOfIncidentFound, 9] = resolutionSummary;
MySheet.Cells[countOfIncidentFound, 10] = assigneeIndividual;
MySheet.Cells[countOfIncidentFound, 11] = assigneeIndividual;
if (txtSecName.Text != "")
{
MySheet.Cells[countOfIncidentFound, 12] = txtSecName.Text;
}
MySheet.Cells[countOfIncidentFound, 13] = customerName;
MySheet.Cells[countOfIncidentFound, 14] = incidentOpenDt;
MySheet.Cells[countOfIncidentFound, 15] = incidentAssignDt;
MySheet.Cells[countOfIncidentFound, 17] = incidentResolvedDt;
if (affectedFacility != null)
{
MySheet.Cells[countOfIncidentFound, 31] = affectedFacility;
}
noOfIncidentsUpdated++;
}
else
{
noOfIncidentsSkipped++;
}
}
i++;
}
toolStripStatusLabel1.Text = "Processing completed !!";
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Error while updating..", MessageBoxButtons.OK, MessageBoxIcon.Error);
toolStripStatusLabel1.Text = "Something went wrong..";
Cursor.Current = Cursors.Default;
}
finally {
MyBook.Save();
GC.Collect();
GC.WaitForPendingFinalizers();
MyBook.Close(false, destFilePath, null);
MyApp.Quit();
MyMarshal.ReleaseComObject(MySheet);
MyMarshal.ReleaseComObject(xlWorkBooks);
MyMarshal.ReleaseComObject(MyBook);
MyMarshal.ReleaseComObject(MyApp);
}
int totalTkts = noOfIncidentsUpdated + noOfIncidentsAdded + noOfIncidentsSkipped;
Cursor.Current = Cursors.Default;
MessageBox.Show("Tracker has been updated successfully with: " +
"\n Rows Added: " + noOfIncidentsAdded + " " +
"\n Rows Updated: " + noOfIncidentsUpdated +
"\n Rows Skipped: " + noOfIncidentsSkipped + "" +
"\n Total Rows: " + totalTkts + "", "Status", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
