private void ExcelApplication_WorkbookBeforeClose(Excel.Workbook Wb, ref bool Cancel)
{
Excel.Sheets excelSheets = Wb.Worksheets;
Excel.Worksheet excelWorksheet = HelperUtil.GetSheetNameFromGroupOfSheets(GlobalMembers.InstanceGlobalMembers.MetaDataSheetName, excelSheets);
Excel.Range excelRange = excelWorksheet.UsedRange;
excelRange.ClearContents();
excelRange.ClearFormats();
excelRange.ClearComments();
}
public void ClearCellFormat(bool clearAll)
{
if (clearAll)
{
Excel.Worksheet ws = app.ActiveWorkbook.ActiveSheet;
ws.UsedRange.ClearFormats();
ws.UsedRange.ClearNotes();
}
else
{
Excel.Range range = app.Selection as Excel.Range;
if (range != null)
{
range.ClearFormats();
range.ClearNotes();
}
}
}
public void FormatStyle()
{
Excel.Worksheet workSheet = app.ActiveWorkbook.ActiveSheet;
Excel.Range range = workSheet.UsedRange;
range.ClearFormats();
range.NumberFormatLocal = "@";
int i = 0, j = 0;
string tmp = "";
try
{
string[,] data = new string[range.Rows.Count, range.Columns.Count];
for (i = 0; i <= range.Rows.Count; i++)
{
for (j = 0; j <= range.Columns.Count; i++)
{
Excel.Range cell = range.Cells[i + 1, j + 1];
//cell.NumberFormatLocal = "@";
data[i, j] = cell.Value2.ToString();
}
}
}
catch (Exception e)
{
MessageBox.Show("Error" + "[" + i + "," + j + "]" + tmp + " " + e.Message);
}
//for (int i = 0; i <= range.Rows.Count; i++)
//{
// for (int j = 0; j <= range.Columns.Count; i++)
// {
// Excel.Range cell = range.Cells[i + 1, j + 1];
// cell.Value = data[i, j];
// cell.NumberFormat = "@";
// }
//}
}
public static void AutoSetYear(Excel.ListObject LO, int interval)
{
Excel.Range yearCol = GetListColumn(LO, EnPIResources.yearColName).Range;
if (yearCol == null)
{
yearCol = AddListColumn(LO, EnPIResources.yearColName, 1).Range;
}
int YearNo;
for (int i = 0; i < yearCol.Rows.Count; i++)
{
if (((Excel.ListRow)yearCol.Rows[i + 1, 1]).Range.ListHeaderRows == 0)
{
YearNo = int.Parse(Math.Round(((decimal)(i / interval)), 0).ToString());
yearCol[i + 1, 1] = YearNo;
}
}
yearCol.ClearFormats();
yearCol.NumberFormat = "@";
}
private void GenerateReport()
{
#region Initialize
Excel.Workbook activeWorkBook = null;
Excel.Worksheet pivotWorkSheet = null;
#endregion
try
{
activeWorkBook = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveWorkbook;
try
{
pivotWorkSheet = (Excel.Worksheet)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Worksheets.get_Item("Application Reports");
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet.Application.DisplayAlerts = false;
pivotWorkSheet.Delete();
pivotWorkSheet = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveWorkbook.Worksheets.Add() as Excel.Worksheet;
pivotWorkSheet.Name = "Application Reports";
pivotWorkSheet.Activate();
}
catch
{
pivotWorkSheet = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveWorkbook.Worksheets.Add() as Excel.Worksheet;
pivotWorkSheet.Name = "Application Reports";
pivotWorkSheet.Activate();
}
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveWindow.DisplayGridlines = false;
#region Create Headings
// Create headings
// =================================================================================================================
Excel.Range title = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A1", "A1");
title.ClearFormats();
title.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color, g_color, b_color));
title.Font.Color = System.Drawing.Color.White;
title.InsertIndent(1);
title.Font.Size = 18;
title.VerticalAlignment = -4108; // xlCenter
Excel.Range titleRow = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A1", "L1");
titleRow.Select();
titleRow.RowHeight = 33;
titleRow.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color, g_color, b_color));
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A2", "D3").Font.Size = "10";
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[1, 1] = "Application Reports";
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[2, 1] = "Generated by: " + userName;
string s = mgmtServer; int start = s.IndexOf("//") + 2; int end = s.IndexOf(".", start); string serverName = s.Substring(start, end - start);
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[3, 1] = "Server: " + serverName;
Excel.Range rangeStamp = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[2, 11];
rangeStamp.Value = DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss") + " (UTC)";
rangeStamp.NumberFormat = "[$-409]yyyy/mm/dd hh:mm AM/PM;@";
rangeStamp.Cells.HorizontalAlignment = -4152; // Right align the Time
rangeStamp.Value = rangeStamp.Value; // Strange technique and workaround to get numbers into Excel. Otherwise, Excel sees them as Text
Excel.Range rowSeparator = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A3", "L3");
rowSeparator.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color, g_color, b_color)); //
rowSeparator.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = 1; // xlContinuous
rowSeparator.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).Weight = 4; // xlThick
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A2", "L3").Font.Size = "8";
#endregion
Globals.PivotBottom = 0;
Globals.ChartBottom = 0;
// int NextPivotRowDefault = 0;
int NextPivotRow = 0;
// int NextChartOffsetDefault = 0;
int NextChartOffet = 0;
// Excel.Range rng = null;
// Chart 1 =========================================================================================
NextPivotRow = 5;
NextChartOffet = 80;
eHelper.CreatePivot("Application Data", colCount, rowCount, "'Application Reports'!R" + NextPivotRow.ToString() + "C11", "PivotTableApplicationName", "Name Version", "Application Name - Version", "Publisher", "Application Count");
eHelper.CreateChart("PivotTableApplicationName", NextChartOffet, "Top 10 Applications");
#region Chart2
// Chart 2 =========================================================================================
/*
* NextPivotRowDefault = 20;
* NextChartOffsetDefault = 305;
* NextChartOffet = NextChartOffsetDefault;
* NextPivotRow = Globals.PivotBottom + 1;
* rng = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A" + NextPivotRow.ToString(), "A" + NextPivotRow.ToString());
* if (Globals.PivotBottom >= NextPivotRowDefault && Globals.ChartBottom < NextChartOffsetDefault)
* {
* Globals.ChartBottom = (int)rng.Top;
* NextChartOffet = Globals.ChartBottom;
* }
* else if (Globals.ChartBottom >= NextChartOffsetDefault)
* {
* NextPivotRow = Globals.PivotBottom + 1;
* bool doMore = true;
* while (doMore)
* {
* rng = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A" + NextPivotRow.ToString(), "A" + NextPivotRow.ToString());
* if (Globals.ChartBottom > rng.Top) NextPivotRow++;
* else { doMore = false; }
* }
* NextChartOffet = (int)rng.Top;
* }
* else
* NextPivotRow = NextPivotRowDefault;
*
* eHelper.CreatePivot("Agent Data", colCount, rowCount, "'Agent Reports'!R" + NextPivotRow.ToString() + "C11", "PivotTableOs", "Software Information.Os Name", "Operating System", "ID", "OS Count");
* eHelper.CreateChart("PivotTableOs", NextChartOffet, "Agent Operating Systems");
*/
#endregion
#region Chart3
// Chart 3 =========================================================================================
/*
* NextPivotRowDefault = 35;
* NextChartOffsetDefault = 530;
* NextChartOffet = NextChartOffsetDefault;
* NextPivotRow = Globals.PivotBottom + 1;
* rng = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A" + NextPivotRow.ToString(), "A" + NextPivotRow.ToString());
* if (Globals.PivotBottom >= NextPivotRowDefault && Globals.ChartBottom < NextChartOffsetDefault)
* {
* Globals.ChartBottom = (int)rng.Top;
* NextChartOffet = Globals.ChartBottom;
* }
* else if (Globals.ChartBottom >= NextChartOffsetDefault)
* {
* NextPivotRow = Globals.PivotBottom + 1;
* bool doMore = true;
* while (doMore)
* {
* rng = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A" + NextPivotRow.ToString(), "A" + NextPivotRow.ToString());
* if (Globals.ChartBottom > rng.Top) NextPivotRow++;
* else { doMore = false; }
* }
* NextChartOffet = (int)rng.Top;
* }
* else
* NextPivotRow = NextPivotRowDefault;
*
* eHelper.CreatePivot("Agent Data", colCount, rowCount, "'Agent Reports'!R" + NextPivotRow.ToString() + "C11", "PivotTableAgent", "Agent Version", "Agent Version", "ID", "Agent Count");
* eHelper.CreateChart("PivotTableAgent", NextChartOffet, "Agent Versions");
*/
#endregion
}
catch (Exception ex)
{
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ScreenUpdating = true;
if (ex.Data.Contains("ExcelHelper"))
{
MessageBox.Show(ex.Data["ExcelHelper"].ToString(), "Error generating chart", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
MessageBox.Show(ex.Message, "Error generating chart", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
finally
{
#region Finally
if (pivotWorkSheet != null)
{
Marshal.ReleaseComObject(pivotWorkSheet);
}
if (activeWorkBook != null)
{
Marshal.ReleaseComObject(activeWorkBook);
}
#endregion
}
}
private void GenerateReport()
{
#region Initialize
Excel.Workbook activeWorkBook = null;
Excel.Worksheet pivotWorkSheet = null;
Excel.PivotCaches pivotCaches = null;
Excel.PivotCache pivotCache = null;
Excel.PivotTable pivotTable = null;
Excel.PivotFields pivotFields = null;
Excel.PivotField monthPivotField = null;
Excel.PivotField statusPivotField = null;
Excel.PivotField resolvedPivotField = null;
Excel.PivotField threatIdPivotField = null;
Excel.PivotField threatIdCountPivotField = null;
Excel.SlicerCaches slicerCaches = null;
Excel.SlicerCache monthSlicerCache = null;
Excel.Slicers monthSlicers = null;
Excel.Slicer monthSlicer = null;
Excel.SlicerCache statusSlicerCache = null;
Excel.Slicers statusSlicers = null;
Excel.Slicer statusSlicer = null;
Excel.SlicerCache resolvedSlicerCache = null;
Excel.Slicers resolvedSlicers = null;
Excel.Slicer resolvedSlicer = null;
#endregion
try
{
activeWorkBook = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveWorkbook;
try
{
pivotWorkSheet = (Excel.Worksheet)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Worksheets.get_Item("Threat Reports");
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet.Application.DisplayAlerts = false;
pivotWorkSheet.Delete();
pivotWorkSheet = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveWorkbook.Worksheets.Add() as Excel.Worksheet;
pivotWorkSheet.Name = "Agent Reports";
pivotWorkSheet.Activate();
}
catch
{
pivotWorkSheet = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveWorkbook.Worksheets.Add() as Excel.Worksheet;
pivotWorkSheet.Name = "Agent Reports";
pivotWorkSheet.Activate();
}
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveWindow.DisplayGridlines = false;
#region Create Headings
// Create headings
// =================================================================================================================
Excel.Range title = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A1", "A1");
title.ClearFormats();
title.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color, g_color, b_color));
title.Font.Color = System.Drawing.Color.White;
title.InsertIndent(1);
title.Font.Size = 18;
title.VerticalAlignment = -4108; // xlCenter
Excel.Range titleRow = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A1", "CA1");
titleRow.Select();
titleRow.RowHeight = 33;
titleRow.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color, g_color, b_color));
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[1, 1] = "Threat Reports";
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[2, 1] = "Generated by: " + userName;
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[3, 1] = DateTime.Now.ToString("f");
Excel.Range rowSeparator = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A3", "CA3");
rowSeparator.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color, g_color, b_color)); //
rowSeparator.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = 1; // xlContinuous
rowSeparator.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).Weight = 4; // xlThick
#endregion
// Create the Pivot Table
pivotCaches = activeWorkBook.PivotCaches();
activeWorkBook.ShowPivotTableFieldList = false;
// pivotCache = pivotCaches.Create(Excel.XlPivotTableSourceType.xlDatabase, "Threats!$A$4:$" + ExcelColumnLetter(colCount) + "$" + rowCount);
// string rangeName = "Threats!$A$4:$T$100";
string rangeName = "'Agent Data'!$A$4:$" + eHelper.ExcelColumnLetter(colCount - 1) + "$" + (rowCount + 4).ToString();
pivotCache = pivotCaches.Create(Excel.XlPivotTableSourceType.xlDatabase, rangeName);
// pivotTable = pivotCache.CreatePivotTable("Reports!R3C1");
pivotTable = pivotCache.CreatePivotTable("'Agent Reports'!R7C1");
pivotTable.NullString = "0";
// Set the Pivot Fields
pivotFields = (Excel.PivotFields)pivotTable.PivotFields();
// Month Pivot Field
monthPivotField = (Excel.PivotField)pivotFields.Item("Created Date");
monthPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
monthPivotField.Position = 1;
monthPivotField.DataRange.Cells[1].Group(true, true, Type.Missing, new bool[] { false, false, false, false, true, true, true });
// Mitigation Status Pivot Field
statusPivotField = (Excel.PivotField)pivotFields.Item("Mitigation Status");
statusPivotField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
// Resolved Pivot Field
resolvedPivotField = (Excel.PivotField)pivotFields.Item("Resolved");
resolvedPivotField.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
// Threat ID Pivot Field
threatIdPivotField = (Excel.PivotField)pivotFields.Item("ID");
// Count of Threat ID Field
threatIdCountPivotField = pivotTable.AddDataField(threatIdPivotField, "# of Threats", Excel.XlConsolidationFunction.xlCount);
slicerCaches = activeWorkBook.SlicerCaches;
// Month Slicer
monthSlicerCache = slicerCaches.Add(pivotTable, "Created Date", "CreatedDate");
monthSlicers = monthSlicerCache.Slicers;
monthSlicer = monthSlicers.Add((ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet, Type.Missing, "Created Date", "Created Date", 80, 480, 144, 100);
// Mitigation Status Slicer
statusSlicerCache = slicerCaches.Add(pivotTable, "Mitigation Status", "MitigationStatus");
statusSlicers = statusSlicerCache.Slicers;
statusSlicer = statusSlicers.Add((ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet, Type.Missing, "Mitigation Status", "Mitigation Status", 80, 634, 144, 100);
// Resolved Slicer
resolvedSlicerCache = slicerCaches.Add(pivotTable, "Resolved", "Resolved");
resolvedSlicers = resolvedSlicerCache.Slicers;
resolvedSlicer = resolvedSlicers.Add((ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet, Type.Missing, "Resolved", "Resolved", 80, 788, 144, 100);
// Slicer original sizes top 15, width 144, height 200
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error generating report", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
#region Finally
if (resolvedSlicer != null)
{
Marshal.ReleaseComObject(resolvedSlicer);
}
if (resolvedSlicers != null)
{
Marshal.ReleaseComObject(resolvedSlicers);
}
if (resolvedSlicerCache != null)
{
Marshal.ReleaseComObject(resolvedSlicerCache);
}
if (statusSlicer != null)
{
Marshal.ReleaseComObject(statusSlicer);
}
if (statusSlicers != null)
{
Marshal.ReleaseComObject(statusSlicers);
}
if (statusSlicerCache != null)
{
Marshal.ReleaseComObject(statusSlicerCache);
}
if (monthSlicer != null)
{
Marshal.ReleaseComObject(monthSlicer);
}
if (monthSlicers != null)
{
Marshal.ReleaseComObject(monthSlicers);
}
if (monthSlicerCache != null)
{
Marshal.ReleaseComObject(monthSlicerCache);
}
if (slicerCaches != null)
{
Marshal.ReleaseComObject(slicerCaches);
}
if (threatIdCountPivotField != null)
{
Marshal.ReleaseComObject(threatIdCountPivotField);
}
if (threatIdPivotField != null)
{
Marshal.ReleaseComObject(threatIdPivotField);
}
if (resolvedPivotField != null)
{
Marshal.ReleaseComObject(resolvedPivotField);
}
if (statusPivotField != null)
{
Marshal.ReleaseComObject(statusPivotField);
}
if (monthPivotField != null)
{
Marshal.ReleaseComObject(monthPivotField);
}
if (pivotFields != null)
{
Marshal.ReleaseComObject(pivotFields);
}
if (pivotTable != null)
{
Marshal.ReleaseComObject(pivotTable);
}
if (pivotCache != null)
{
Marshal.ReleaseComObject(pivotCache);
}
if (pivotCaches != null)
{
Marshal.ReleaseComObject(pivotCaches);
}
if (pivotWorkSheet != null)
{
Marshal.ReleaseComObject(pivotWorkSheet);
}
if (activeWorkBook != null)
{
Marshal.ReleaseComObject(activeWorkBook);
}
#endregion
}
}