private void PivotData()
{
index++;
Excel.Worksheet osheet = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet;
var pch = Globals.ThisAddIn.Application.ActiveWorkbook.PivotCaches();
Excel.Range pivotData = (Excel.Range)osheet.UsedRange;
Excel.PivotCache pc = pch.Create(Excel.XlPivotTableSourceType.xlDatabase, pivotData);
Excel.PivotTable pvt = pc.CreatePivotTable(osheet.Range["J1"], "MyPivotTable" + index);
Excel.PivotField pageField = (Excel.PivotField)pvt.PivotFields("WORKFLOW");
pageField.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
Excel.PivotField rowField = (Excel.PivotField)pvt.PivotFields("Status");
rowField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
Excel.PivotField valueField = (Excel.PivotField)pvt.PivotFields("Status");
valueField.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
Excel.SlicerCaches slicerCaches = Globals.ThisAddIn.Application.ActiveWorkbook.SlicerCaches;
//Month Slicer
string nameDateTime = "Slicer_DATETIME" + index.ToString();
string nameMonth = "Slicer_Month" + index.ToString();
Excel.SlicerCache monthSlicerCache = slicerCaches.Add2(pvt, "DATETIME", nameDateTime, XlSlicerCacheType.xlTimeline);
Excel.Slicers monthSlicers = monthSlicerCache.Slicers;
Excel.Slicer monthSlicer = monthSlicers.Add(osheet, Type.Missing,
nameMonth, "Date Range", 160, 10, 250, 150);
}
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
}
}
/// <summary>
/// Creates all the pivot tables, slicers, and row fields, filters and Description, based on values from
/// their hashTables.
/// </summary>
/// <param name="inputCSVFile"></param>
/// <param name="PivotOutputReportFullPath"></param>
/// <param name="newSheetName"></param>
/// <param name="inputDataSheetName"></param>
/// <param name="PivottableName"></param>
/// <param name="htRowPivotFields"></param>
/// <param name="htPgaefilterFields"></param>
/// <param name="htSlicers"></param>
/// <param name="pivotCountField"></param>
/// <param name="component"></param>
/// <param name="numberOfFilesCount"></param>
/// <param name="otherNodes"></param>
public static void GeneratePivotAndSlicersView(string inputCSVFile, string PivotOutputReportFullPath, ref string newSheetName, string inputDataSheetName, string PivottableName, Hashtable htRowPivotFields, Hashtable htPgaefilterFields, Hashtable htSlicers, string pivotCountField, XmlNode component, int numberOfFilesCount, List <XmlNode> otherNodes)
{
Excel.Application oApp;
Excel.Worksheet oSheet;
Excel.Workbook oBook = null;
Excel.Worksheet oSheet1 = null;
Excel.Worksheet oSheet2 = null;
oApp = new Excel.Application();
var workbooks = oApp.Workbooks;
string sheetName = newSheetName;
XmlNode style = otherNodes.Find(item => item.Name == "Style");
Excel.Range rangeToChange = null;
string exceptionComment = "Processing for CSV :" + inputCSVFile;
Logger.LogInfoMessage(string.Format("[GeneratePivotReports][GeneratePivotAndSlicersView] Processing Started for (" + inputCSVFile + ")"), false);
Excel.Workbook oDiscoveryViewook = null;
try
{
oBook = oApp.Workbooks.Open(inputCSVFile);
//Excel.Workbook oDiscoveryViewook = null;
if (System.IO.File.Exists(PivotOutputReportFullPath))
{
oDiscoveryViewook = workbooks.Open(PivotOutputReportFullPath);
// create multiple sheets
if (oApp.Application.Application.Sheets.Count >= 1)
{
oSheet2 = oDiscoveryViewook.Worksheets.OfType <Excel.Worksheet>().FirstOrDefault(ws => ws.Name == sheetName);
oSheet1 = (Excel.Worksheet)oDiscoveryViewook.Worksheets.Add(After: oDiscoveryViewook.Sheets[oDiscoveryViewook.Sheets.Count]);
if (oSheet2 == null)
{
oSheet1.Name = newSheetName;
}
else
{
if (newSheetName.Length >= Constants.SheetNameMaxLength)
{
newSheetName = newSheetName.Remove(newSheetName.Length - 3);
}
newSheetName = newSheetName + "_" + numberOfFilesCount;
oSheet1.Name = newSheetName;
}
}
else
{
oSheet1 = oApp.Worksheets[2];
}
}
if (inputDataSheetName.Length >= Constants.SheetNameMaxLength)
{
oSheet = (Excel.Worksheet)oBook.Sheets.get_Item(1);
}
else
{
oSheet = (Excel.Worksheet)oBook.Sheets.get_Item(inputDataSheetName);
}
// now capture range of the first sheet
Excel.Range oRange = oSheet.UsedRange;
// specify first cell for pivot table
Excel.Range oRange2 = (Excel.Range)oSheet1.Cells[3, 1];
//Create Pivot Cache
if (oRange.Rows.Count > 1)
{
PivotCache oPivotCache = oDiscoveryViewook.PivotCaches().Create(XlPivotTableSourceType.xlDatabase, oRange, XlPivotTableVersionList.xlPivotTableVersion14);
PivotTable oPivotTable = oPivotCache.CreatePivotTable(TableDestination: oRange2, TableName: PivottableName);
//Creating row pivot fields
foreach (DictionaryEntry rowField in htRowPivotFields)
{
string rowFieldContent = rowField.Value.ToString();
string rowFieldValue = rowFieldContent.Substring(0, rowFieldContent.IndexOf("~"));
string rowFieldLabel = rowFieldContent.Substring(rowFieldContent.IndexOf("~") + 1);
if ((Excel.PivotField)oPivotTable.PivotFields(Convert.ToString(rowFieldValue)) != null)
{
Excel.PivotField oPivotFieldPivotFieldName = (Excel.PivotField)oPivotTable.PivotFields(Convert.ToString(rowFieldValue));
oPivotFieldPivotFieldName.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
oPivotTable.CompactLayoutRowHeader = rowFieldLabel;
}
else
{
Logger.LogInfoMessage(string.Format("[GeneratePivotReports][GeneratePivotAndSlicersView] Error: No data available"), true);
}
}
//page filters
foreach (DictionaryEntry rowPgaeField in htPgaefilterFields)
{
Excel.PivotField scPageFilterFiled = oPivotTable.PivotFields(Convert.ToString(rowPgaeField.Value));
scPageFilterFiled.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
}
//Count Field
Excel.PivotField oPivotField2 = (Excel.PivotField)oPivotTable.PivotFields(pivotCountField);
oPivotTable.AddDataField(oPivotField2, "Count of " + pivotCountField + "", Excel.XlConsolidationFunction.xlCount);
rangeToChange = oPivotTable.TableRange2;
oSheet1.Activate();
rangeToChange.Select();
//excelApp.Selection.Font;
oSheet1.Application.Selection.Font.Name = CommonHelper.CheckAttributes("FontFamily", null, style);
oSheet1.Application.Selection.Font.Size = Convert.ToDouble(CommonHelper.CheckAttributes("FontSize", null, style));
//Create Slicer Cache Object
int Slicerpos = 0, slicersCount = 0;
foreach (DictionaryEntry rowSlicer in htSlicers)
{
slicersCount++;
string rowSlicerValue = rowSlicer.Value.ToString();
if (rowSlicerValue.Contains("~"))
{
string sliceName = rowSlicerValue.Substring(0, rowSlicerValue.IndexOf("~"));
string sliceStyle = rowSlicerValue.Substring(rowSlicerValue.IndexOf("~") + 1);
Excel.SlicerCache oSlicerCache = (Excel.SlicerCache)oDiscoveryViewook.SlicerCaches.Add2(oPivotTable, sliceName);
Excel.Slicer oSlicer = (Excel.Slicer)oSlicerCache.Slicers.Add(oSheet1, Type.Missing, sliceName + "_" + newSheetName, sliceName, Top: 30, Left: 400 + Slicerpos, Width: 144, Height: 200);
oSlicer.Style = sliceStyle;
//To Move Left Position of next slicers(2,3...)
Slicerpos += 190;
}
}
}
Range Line = (Range)oSheet1.Rows[1];
Line.Insert();
XmlNode descriptionTitle = component.SelectSingleNode("DescriptionTitle");
XmlNode description = component.SelectSingleNode("Description");
//Get the range of sheet to fill count
XmlNode styleOfDescription = style.SelectSingleNode("ComponentStyle").SelectSingleNode("Description");
Excel.Range descriptionRange = oSheet1.get_Range("B1", "O1");
descriptionRange.Merge();
string descText = description.InnerText.Trim();
descriptionRange.Value = descText.Replace("\r", "").Replace("\n", "");
descriptionRange.Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop;
descriptionRange.Columns.AutoFit();
descriptionRange.RowHeight = Convert.ToDouble(CommonHelper.CheckAttributes("RowHeight", styleOfDescription, style));
descriptionRange.WrapText = true;
descriptionRange.Interior.Color = CommonHelper.GetColor(CommonHelper.CheckAttributes("BgColor", styleOfDescription, style));
descriptionRange.Font.Color = CommonHelper.GetColor(CommonHelper.CheckAttributes("FontColor", styleOfDescription, style));
descriptionRange.Font.Size = Convert.ToDouble(CommonHelper.CheckAttributes("FontSize", styleOfDescription, style));
descriptionRange.Font.Name = CommonHelper.CheckAttributes("FontFamily", styleOfDescription, style);
descriptionRange.Borders.Color = XlRgbColor.rgbSlateGray;
//Get the range of sheet to fill count
XmlNode styleOfDescTitle = style.SelectSingleNode("ComponentStyle").SelectSingleNode("DescriptionTitle");
Excel.Range descriptionTitleRange = oSheet1.get_Range("A1", "A1");
descriptionTitleRange.Value = descriptionTitle.InnerText.Trim();
//(styleOfDescription.Attributes["FontFamily"] == null || (styleOfDescription.Attributes["FontFamily"].InnerText == null) ? fontFamily : styleOfDescription.Attributes["FontFamily"].InnerText;
descriptionTitleRange.ColumnWidth = Convert.ToDouble(CommonHelper.CheckAttributes("ColumnWidth", styleOfDescTitle, style));
descriptionTitleRange.Interior.Color = CommonHelper.GetColor(CommonHelper.CheckAttributes("BgColor", styleOfDescTitle, style));
descriptionTitleRange.Font.Color = CommonHelper.GetColor(CommonHelper.CheckAttributes("FontColor", styleOfDescTitle, style));
descriptionTitleRange.Borders.Color = XlRgbColor.rgbSlateGray;
descriptionTitleRange.Font.Size = Convert.ToDouble(CommonHelper.CheckAttributes("FontSize", styleOfDescTitle, style));
descriptionTitleRange.Font.Name = CommonHelper.CheckAttributes("FontFamily", styleOfDescTitle, style);
descriptionTitleRange.Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop;
descriptionTitleRange.Font.Bold = true;
//descriptionTitleRange.Columns.AutoFit();
oDiscoveryViewook.Save();
oDiscoveryViewook.Close();
object misValue = System.Reflection.Missing.Value;
oBook.Close(false, misValue, misValue);
oApp.Quit();
oApp.Application.Quit();
Marshal.ReleaseComObject(oBook);
Marshal.ReleaseComObject(workbooks);
Marshal.ReleaseComObject(oDiscoveryViewook);
Logger.LogInfoMessage(string.Format("[GeneratePivotReports][GeneratePivotAndSlicersView] Processing Completed for (" + inputCSVFile + ") and sheet " + newSheetName + " is created in Pivot Output file: " + PivotOutputReportFullPath), true);
}
catch (Exception ex)
{
if (oDiscoveryViewook != null)
{
oDiscoveryViewook.Save();
oDiscoveryViewook.Close();
}
object misValue = System.Reflection.Missing.Value;
if (oBook != null)
{
oBook.Close(false, misValue, misValue);
}
if (oApp != null)
{
oApp.Quit();
oApp.Application.Quit();
}
Marshal.ReleaseComObject(oBook);
Marshal.ReleaseComObject(workbooks);
Marshal.ReleaseComObject(oDiscoveryViewook);
Logger.LogErrorMessage(string.Format("[GeneratePivotReports][GeneratePivotAndSlicersView][Exception]: " + ex.Message + ", " + exceptionComment), true);
ExceptionCsv.WriteException(Constants.NotApplicable, Constants.NotApplicable, Constants.NotApplicable, "Pivot", ex.Message, ex.ToString(),
"[GeneratePivotReports]: GeneratePivotAndSlicersView", ex.GetType().ToString(), exceptionComment);
}
finally
{
Marshal.ReleaseComObject(oApp);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
