public static void CreatePivot(string tableSource, int[] pageFields, int[] rowFields, int[] dataFields, string pivotTableName = "Pivot Table", string[] slicerColumns = null) { Microsoft.Office.Interop.Excel.Worksheet worksheet = new Microsoft.Office.Interop.Excel.Worksheet(); Workbook workbook = Globals.ThisAddIn.Application.ActiveWorkbook; worksheet = workbook.Worksheets.Add(After: workbook.ActiveSheet); worksheet.Name = "Pivot"; ListObject table = GetTable(tableSource); Range rng = table.Range; worksheet.PivotTableWizard( XlPivotTableSourceType.xlDatabase, rng, worksheet.Range["A1"], pivotTableName ); PivotTable pivot = (PivotTable)worksheet.PivotTables(pivotTableName); //pivot.HasAutoFormat = true; pivot.ColumnGrand = true; pivot.RowGrand = true; for (int i = 0; i < pageFields.Length; i++) { PivotField field1 = pivot.PivotFields(pageFields[i]); field1.Orientation = XlPivotFieldOrientation.xlPageField; field1.Position = i + 1; field1.CurrentPage = "(All)"; } for (int i = 0; i < rowFields.Length; i++) { PivotField field1 = pivot.PivotFields(rowFields[i]); field1.Orientation = XlPivotFieldOrientation.xlRowField; field1.Position = i + 1; } PivotField columnField = pivot.PivotFields(); columnField.Orientation = XlPivotFieldOrientation.xlColumnField; columnField.Position = 1; for (int i = 0; i < dataFields.Length; i++) { PivotField field1 = pivot.PivotFields(dataFields[i]); field1.Orientation = XlPivotFieldOrientation.xlDataField; field1.Position = 1 + i; field1.Function = XlConsolidationFunction.xlSum; } //Add Slicers SlicerCaches caches = workbook.SlicerCaches; int counter = 1; if (slicerColumns != null) { foreach (string s in slicerColumns) { SlicerCache cache = caches.Add(pivot, s, s); Slicers slicers = cache.Slicers; Slicer slicer = slicers.Add(worksheet, Type.Missing, s, s, 160 * counter, 10, 144, 200); counter++; } } }
private void CreateMainPivotTable(Worksheet inSheet, Worksheet outSheet) { Workbook workbook = ThisAddIn.app.ActiveWorkbook; ColumnInfo durationColumn = this.GetDurationColumnInfo(); ColumnInfo eventTypeColumn = this.GetEventTypeColumnInfo(); string A = durationColumn.columnId; inSheet.Range[A + ":" + A].NumberFormat = "mm:ss"; // Create Pivot Cache from Source Data PivotCache pvtCache = workbook.PivotCaches().Create(SourceType: XlPivotTableSourceType.xlDatabase, //SourceData: srcData); SourceData: inSheet.UsedRange); // Create Pivot table from Pivot Cache //PivotTable pvt = pvtCache.CreatePivotTable(TableDestination: startPvt, TableName: "PivotTable2"); PivotTable pvt = pvtCache.CreatePivotTable(TableDestination: outSheet.Range["A3"], TableName: "PivotTable2"); string durationField = durationColumn.name; string eventTypeField = eventTypeColumn.name; pvt.AddDataField(pvt.PivotFields(eventTypeField), "Count of " + eventTypeField, XlConsolidationFunction.xlCount); pvt.AddDataField(pvt.PivotFields(eventTypeField), "Percent of " + eventTypeField, XlConsolidationFunction.xlCount); pvt.PivotFields("Percent of " + eventTypeField).Calculation = XlPivotFieldCalculation.xlPercentOfColumn; pvt.AddDataField(pvt.PivotFields(durationField), "Average Duration of " + eventTypeField, XlConsolidationFunction.xlAverage); pvt.AddDataField(pvt.PivotFields(durationField), "Max Duration of " + eventTypeField, XlConsolidationFunction.xlMax); pvt.AddDataField(pvt.PivotFields(durationField), "Standard Deviation of " + eventTypeField, XlConsolidationFunction.xlStDev); pvt.PivotFields("Average Duration of " + eventTypeField).NumberFormat = "mm:ss"; pvt.PivotFields("Max Duration of " + eventTypeField).NumberFormat = "mm:ss"; pvt.PivotFields("Standard Deviation of " + eventTypeField).NumberFormat = "mm:ss"; pvt.PivotFields(eventTypeField).Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField; }
/// <summary> /// Crear una tabla pivot. /// <param name="hoja_tabla">Número de la hoja a la que se agregará la tabla pivot</param> /// <param name="nombre_campo_fila">Nombre del campo de fila</param> /// <param name="nombre_campo_datos">Nombre del campo de datos</param> /// </summary> public override void crearTablaPivot(string hoja_tabla, string nombre_campo_fila, string nombre_campo_datos) { XlPivotTableSourceType fuente = XlPivotTableSourceType.xlDatabase; XlPivotFieldOrientation orientacion_fila = XlPivotFieldOrientation.xlRowField; XlPivotFieldOrientation orientacion_datos = XlPivotFieldOrientation.xlDataField; // Crear la tabla pivot _hoja = (Worksheet)_libro.Worksheets[hoja_tabla]; _hoja.PivotTableWizard(fuente, _seleccionado, _missing, "PivTab1"); PivotTable tabla = (PivotTable)_hoja.PivotTables("PivTab1"); PivotField campo_fila = ((PivotField)tabla.PivotFields(nombre_campo_fila)); PivotField campo_datos = ((PivotField)tabla.PivotFields(nombre_campo_datos)); campo_fila.Orientation = orientacion_fila; campo_datos.Orientation = orientacion_datos; }
public void InsertPivotTable(PivotTableData data) { Range dataRange = GetRange(data.Range); PivotCache pivotCache = _workbook.WrappedWorkbook.PivotCaches().Add( XlPivotTableSourceType.xlDatabase, dataRange); Range insertRange = GetCell(data.InsertCell.Start.Row, data.InsertCell.Start.Column); PivotTables sheetPivotTables = _sheet.PivotTables(Type.Missing); PivotTable pivotTable = sheetPivotTables.Add( pivotCache, insertRange, data.Name, Type.Missing, Type.Missing); pivotTable.TableStyle = data.StyleName; foreach (string column in data.Columns) { PivotField pageField = (PivotField)pivotTable.PivotFields(column); pageField.Orientation = XlPivotFieldOrientation.xlColumnField; } foreach (string row in data.Rows) { PivotField rowField = (PivotField)pivotTable.PivotFields(row); rowField.Orientation = XlPivotFieldOrientation.xlRowField; } foreach (string value in data.Values) { PivotField valueField = (PivotField)pivotTable.PivotFields(value); valueField.Orientation = XlPivotFieldOrientation.xlDataField; } pivotTable.GrandTotalName = "Suma"; pivotTable.SmallGrid = true; }
public override void RunCommand(object sender) { var engine = (IAutomationEngineInstance)sender; string vSheetExcelTable = v_SheetNameExcelTable.ConvertUserVariableToString(engine); string vSheetPivotTable = v_SheetNamePivotTable.ConvertUserVariableToString(engine); var vTableName = v_TableName.ConvertUserVariableToString(engine); var vCellLocation = v_CellLocation.ConvertUserVariableToString(engine); var vPivotTable = v_PivotTable.ConvertUserVariableToString(engine); var excelObject = v_InstanceName.GetAppInstance(engine); var excelInstance = (Application)excelObject; var workBook = excelInstance.ActiveWorkbook; var workSheetExcelTable = excelInstance.Sheets[vSheetExcelTable] as Worksheet; var workSheetPivotTable = excelInstance.Sheets[vSheetPivotTable] as Worksheet; var excelTable = workSheetExcelTable.ListObjects[vTableName]; object useDefault = Type.Missing; Range pivotDestination = workSheetPivotTable.Range[vCellLocation, useDefault]; var pivotCache = workBook.PivotCaches().Create(XlPivotTableSourceType.xlDatabase, vTableName, Type.Missing); PivotTable pivotTable = pivotCache.CreatePivotTable(pivotDestination, vPivotTable, Type.Missing, Type.Missing); PivotField pivotField; for (int i = 1; i <= excelTable.ListColumns.Count; i++) { pivotField = pivotTable.PivotFields(i); if (XlPivotFieldDataType.xlText != pivotField.DataType) { pivotTable.AddDataField(pivotTable.PivotFields(i), Type.Missing, Type.Missing); } else { pivotTable.PivotFields(i).Orientation = XlPivotFieldOrientation.xlRowField; } } pivotTable.RefreshTable(); }
public void PivotTable_CollapseSections(string pivotTableName, string fieldName) { PivotTable pT = default(PivotTable); PivotField pF = default(PivotField); //Dim pI As PivotItem pT = Globals.ThisAddIn.Application.ActiveSheet.PivotTables(pivotTableName); // Not sure what the difference is between these two. Both seem to work. pF = pT.PivotFields(fieldName); // Set pF = pT.RowFields(fieldName) // Collapse each of the sections // TODO: Figure out why this throws exception?? Works in VBA?? //For Each pI In pF.PivotItems // pI.ShowDetail = False //Next }
void AddPivotSummary(WorkItemActionRequest request) { long startTicks = Log.EVENT_HANDLER("Enter", Common.PROJECT_NAME); Workbook wb = Globals.ThisAddIn.Application.ActiveWorkbook; Worksheet ws = Globals.ThisAddIn.Application.ActiveSheet; Microsoft.Office.Interop.Excel.Range activeCell = Globals.ThisAddIn.Application.ActiveCell; Options_AZDO_TFS options = new Options_AZDO_TFS(); int workItemID = int.Parse(request.WorkItemID); XlHlp.XlLocation insertAt = CreateNewWorksheet(string.Format("P_WI_{0}", workItemID), GetOptions()); // TODO(crhodes) // Figure out how to get the table name from the active cell. var tableName = activeCell.ListObject.Name; PivotCache pc = wb.PivotCaches().Create( SourceType: XlPivotTableSourceType.xlDatabase, SourceData: tableName, Version: 6); string insertRange = $"{insertAt.workSheet.Name}!R{options.StartingRow}C{options.StartingColumn}"; PivotTable pt = pc.CreatePivotTable(TableDestination: insertRange); // this is from the macro recording. Not all may be needed or desired. pt.ColumnGrand = true; pt.HasAutoFormat = true; pt.DisplayErrorString = false; pt.DisplayNullString = true; pt.EnableDrilldown = true; pt.ErrorString = ""; pt.MergeLabels = false; pt.NullString = ""; pt.PageFieldOrder = 2; pt.PageFieldWrapCount = 0; pt.PreserveFormatting = true; pt.RowGrand = true; pt.SaveData = true; pt.PrintTitles = false; pt.RepeatItemsOnEachPrintedPage = true; pt.TotalsAnnotation = false; pt.CompactRowIndent = 1; pt.InGridDropZones = false; pt.DisplayFieldCaptions = true; pt.DisplayMemberPropertyTooltips = false; pt.DisplayContextTooltips = true; pt.ShowDrillIndicators = true; pt.PrintDrillIndicators = false; pt.AllowMultipleFilters = false; pt.SortUsingCustomLists = true; pt.FieldListSortAscending = false; pt.ShowValuesRow = false; pt.CalculatedMembersInFilters = false; pt.RowAxisLayout(XlLayoutRowType.xlCompactRow); pt.PivotCache().RefreshOnFileOpen = false; pt.PivotCache().MissingItemsLimit = XlPivotTableMissingItems.xlMissingItemsDefault; PivotField pf1 = pt.PivotFields("Source.Type"); PivotField pf2 = pt.PivotFields("Target.Type"); pf1.Orientation = XlPivotFieldOrientation.xlRowField; pf1.Position = 1; pf2.Orientation = XlPivotFieldOrientation.xlRowField; pf2.Position = 2; //pt.AddDataField(pf1, "Count", XlConsolidationFunction.xlCount); //pf2.Orientation = XlPivotFieldOrientation.xlRowField; //pf2.Position = 2; //With ActiveSheet.PivotTables("PivotTable1").PivotFields("Target.Type") // .Orientation = xlRowField // .Position = 1 //End With //ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables(_ // "PivotTable1").PivotFields("Target.Type"), "Count of Target.Type", xlCount //With ActiveSheet.PivotTables("PivotTable1").PivotFields("SourceId") // .Orientation = xlRowField // .Position = 2 //End With insertAt.workSheet.Select(); Log.EVENT_HANDLER("Exit", Common.PROJECT_NAME, startTicks); }
private void ecxelPoster() { DateTime pReadDate = DateTime.Now; string pSheetName = "Poster"; Excel.Application oXL = null;; Excel._Workbook oWB; Excel._Worksheet oSheetPoster; Excel._Worksheet oSheetRegnskab; Excel.Window oWindow; Excel.Range oRng; var rec_regnskab = Program.qryAktivRegnskab(); string SaveAs = rec_regnskab.Eksportmappe + pSheetName + pReadDate.ToString("_yyyyMMdd_hhmmss") + ".xls"; var JournalPoster = from h in Program.karPosteringer join d1 in Program.karKontoplan on h.Konto equals d1.Kontonr into details1 from x in details1.DefaultIfEmpty() orderby h.Nr select new clsJournalposter { ds = (x.Type == "Drift") ? "D" : "S", k = IUAP(x.Type, x.DK), Konto = h.Konto.ToString() + "-" + x.Kontonavn, Dato = h.Dato, Bilag = h.Bilag, Nr = h.Nr, Id = h.Id, Tekst = h.Tekst, Beløb = h.Bruttobeløb, }; var erMedlem = from h in Program.dbData3060.tblMedlems where h.Status == 1 select h; using (new ExcelUILanguageHelper()) { try { //Start Excel and get Application object. oXL = new Excel.Application(); oXL.Visible = true; //oXL.Visible = true; //For debug //Get a new workbook. oWB = oXL.Workbooks.Add((Missing.Value)); oSheetPoster = (Excel._Worksheet)oWB.ActiveSheet; oWindow = oXL.ActiveWindow; if (pSheetName.Length > 0) { oSheetPoster.Name = pSheetName.Substring(0, pSheetName.Length > 34 ? 34 : pSheetName.Length); } int row = 1; this.MainformProgressBar.Value = 0; this.MainformProgressBar.Minimum = 0; this.MainformProgressBar.Maximum = (from h in Program.karPosteringer select h).Count(); this.MainformProgressBar.Step = 1; this.MainformProgressBar.Visible = true; foreach (clsJournalposter m in JournalPoster) { this.MainformProgressBar.PerformStep(); row++; Type objectType = m.GetType(); PropertyInfo[] properties = objectType.GetProperties(); int col = 0; foreach (PropertyInfo property in properties) { col++; string Name = property.Name; //string NamePropertyType = property.GetValue(m, null).GetType().ToString(); oSheetPoster.Cells[row, col] = property.GetValue(m, null); if (row == 2) { object[] CustomAttributes = property.GetCustomAttributes(false); foreach (var att in CustomAttributes) { Type tp = att.GetType(); if (tp.ToString() == "nsPuls3060.Fieldattr") { Fieldattr attr = (Fieldattr)att; string heading = attr.Heading; oSheetPoster.Cells[1, col] = heading; } } } } } oRng = (Excel.Range)oSheetPoster.Rows[1, Missing.Value]; oRng.Font.Name = "Arial"; oRng.Font.Size = 12; oRng.Font.Strikethrough = false; oRng.Font.Superscript = false; oRng.Font.Subscript = false; oRng.Font.OutlineFont = false; oRng.Font.Shadow = false; oRng.Font.Bold = true; oRng.HorizontalAlignment = Excel.Constants.xlCenter; oRng.VerticalAlignment = Excel.Constants.xlBottom; oRng.WrapText = false; oRng.Orientation = 0; oRng.AddIndent = false; oRng.IndentLevel = 0; oRng.ShrinkToFit = false; oRng.MergeCells = false; string BottomRight = "D" + row.ToString(); oRng = oSheetPoster.get_Range("D2", BottomRight); oRng.NumberFormat = "dd-mm-yyyy"; oSheetPoster.Cells.EntireColumn.AutoFit(); oWindow.SplitRow = 1; oWindow.FreezePanes = true; oSheetPoster.get_Range("A1", Missing.Value).Select(); oSheetRegnskab = (Excel._Worksheet)oWB.Worksheets.Add(System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing); oRng = oSheetRegnskab.get_Range("C2", Missing.Value); oRng.Formula = "Antal medlemmer: " + erMedlem.Count().ToString(); //oXL.Visible = true; //For debug PivotField _pvtField = null; PivotTable _pivot = oSheetPoster.PivotTableWizard( XlPivotTableSourceType.xlDatabase, //SourceType oSheetPoster.get_Range(oSheetPoster.Cells[1, 1], oSheetPoster.Cells[row, 9]), //SourceData oSheetRegnskab.get_Range("A3", Missing.Value), //TableDestination "PivotTable1", //TableName System.Type.Missing, //RowGrand System.Type.Missing, //CollumnGrand System.Type.Missing, //SaveData System.Type.Missing, //HasAutoformat System.Type.Missing, //AutoPage System.Type.Missing, //Reserved System.Type.Missing, //BackgroundQuery System.Type.Missing, //OptimizeCache System.Type.Missing, //PageFieldOrder System.Type.Missing, //PageFieldWrapCount System.Type.Missing, //ReadData System.Type.Missing); //Connection _pvtField = (PivotField)_pivot.PivotFields("ds"); _pvtField.Orientation = XlPivotFieldOrientation.xlRowField; _pvtField = (PivotField)_pivot.PivotFields("k"); _pvtField.Orientation = XlPivotFieldOrientation.xlRowField; _pvtField = (PivotField)_pivot.PivotFields("Konto"); _pvtField.Orientation = XlPivotFieldOrientation.xlRowField; _pvtField = (PivotField)_pivot.PivotFields("Dato"); _pvtField.Orientation = XlPivotFieldOrientation.xlColumnField; _pvtField = (PivotField)_pivot.PivotFields("Beløb"); _pvtField.Orientation = XlPivotFieldOrientation.xlDataField; _pvtField.Function = XlConsolidationFunction.xlSum; _pvtField.NumberFormat = "#,##0"; oSheetRegnskab.Name = "Regnskab"; oRng = oSheetRegnskab.get_Range("D3", Missing.Value); oRng.Select(); bool[] Periods = { false, false, false, false, true, false, false }; oRng.Group(true, true, Missing.Value, Periods); oRng = oSheetRegnskab.get_Range("D4", "P4"); oRng.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; oSheetRegnskab.PageSetup.LeftHeader = "&14Regnskab Puls 3060"; oSheetRegnskab.PageSetup.CenterHeader = ""; oSheetRegnskab.PageSetup.RightHeader = "&P af &N"; oSheetRegnskab.PageSetup.LeftFooter = "&Z&F"; oSheetRegnskab.PageSetup.CenterFooter = ""; oSheetRegnskab.PageSetup.RightFooter = "&D&T"; oSheetRegnskab.PageSetup.LeftMargin = oXL.InchesToPoints(0.75); oSheetRegnskab.PageSetup.RightMargin = oXL.InchesToPoints(0.75); oSheetRegnskab.PageSetup.TopMargin = oXL.InchesToPoints(1); oSheetRegnskab.PageSetup.BottomMargin = oXL.InchesToPoints(1); oSheetRegnskab.PageSetup.HeaderMargin = oXL.InchesToPoints(0.5); oSheetRegnskab.PageSetup.FooterMargin = oXL.InchesToPoints(0.5); oSheetRegnskab.PageSetup.PrintHeadings = false; oSheetRegnskab.PageSetup.PrintGridlines = true; oSheetRegnskab.PageSetup.CenterHorizontally = false; oSheetRegnskab.PageSetup.CenterVertically = false; oSheetRegnskab.PageSetup.Orientation = XlPageOrientation.xlLandscape; oSheetRegnskab.PageSetup.Draft = false; oSheetRegnskab.PageSetup.PaperSize = XlPaperSize.xlPaperA4; oSheetRegnskab.PageSetup.FirstPageNumber = 1; oSheetRegnskab.PageSetup.Order = XlOrder.xlDownThenOver; oSheetRegnskab.PageSetup.BlackAndWhite = false; oSheetRegnskab.PageSetup.Zoom = 100; oSheetRegnskab.PageSetup.PrintErrors = XlPrintErrors.xlPrintErrorsDisplayed; oWB.ShowPivotTableFieldList = false; for (var i = oWB.Worksheets.Count; i > 0; i--) { Excel._Worksheet oSheetWrk = (Excel._Worksheet)oWB.Worksheets.get_Item(i); if ((oSheetWrk.Name != "Regnskab") && (oSheetWrk.Name != "Poster")) { oSheetWrk.Delete(); } } oSheetRegnskab.get_Range("A1", Missing.Value).Select(); oWB.SaveAs(SaveAs, Excel.XlFileFormat.xlWorkbookNormal, "", "", false, false, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); oWB.Saved = true; oXL.Visible = true; this.MainformProgressBar.Visible = false; this.emailExcelFile(SaveAs, "Puls3060 Regnskab"); //oXL.Quit(); //oXL = null; } catch (Exception theException) { String errorMessage; errorMessage = "Error: "; errorMessage = String.Concat(errorMessage, theException.Message); errorMessage = String.Concat(errorMessage, " Line: "); errorMessage = String.Concat(errorMessage, theException.Source); MessageBox.Show(errorMessage, "Error"); } } }
public override string Export(string json) { string fileName = ""; try { if (string.IsNullOrEmpty(json)) { throw new KMJXCException("没有销售数据,不能导出"); } JArray jObject = JArray.Parse(json); if (jObject == null || jObject.Count() == 0) { throw new KMJXCException("没有销售数据,不能导出"); } Worksheet sheet = (Worksheet)this.WorkBook.ActiveSheet; sheet.Name = "销售报表"; int startRow = 2; object[,] os = new object[jObject.Count, 6]; for (int i = 0; i < jObject.Count(); i++) { JObject obj = (JObject)jObject[i]; string productName = obj["ProductName"].ToString(); string propName = obj["PropName"].ToString(); string shopName = obj["ShopName"].ToString(); string month = obj["Month"].ToString(); string quantity = obj["Quantity"].ToString(); string amount = obj["Amount"].ToString(); os[i, 0] = productName; os[i, 1] = propName; os[i, 2] = shopName; os[i, 3] = month; os[i, 4] = quantity; os[i, 5] = amount; } Range range1 = sheet.Cells[startRow, 1]; Range range2 = sheet.Cells[startRow + jObject.Count - 1, 6]; Range range = sheet.get_Range(range1, range2); range.Value2 = os; Worksheet pivotTableSheet = (Worksheet)this.WorkBook.Worksheets[2]; pivotTableSheet.Name = "销售透视表"; PivotCaches pch = WorkBook.PivotCaches(); sheet.Activate(); pch.Add(XlPivotTableSourceType.xlDatabase, "'" + sheet.Name + "'!A1:'" + sheet.Name + "'!F" + (jObject.Count() + 1)).CreatePivotTable(pivotTableSheet.Cells[4, 1], "PivTbl_1", Type.Missing, Type.Missing); PivotTable pvt = pivotTableSheet.PivotTables("PivTbl_1") as PivotTable; pvt.Format(XlPivotFormatType.xlTable1); pvt.TableStyle2 = "PivotStyleLight16"; pvt.InGridDropZones = true; foreach (PivotField pf in pvt.PivotFields() as PivotFields) { pf.ShowDetail = false; } PivotField productField = (PivotField)pvt.PivotFields("产品"); productField.Orientation = XlPivotFieldOrientation.xlRowField; productField.set_Subtotals(1, false); PivotField propField = (PivotField)pvt.PivotFields("属性"); propField.Orientation = XlPivotFieldOrientation.xlRowField; propField.set_Subtotals(1, false); PivotField shopField = (PivotField)pvt.PivotFields("店铺"); shopField.Orientation = XlPivotFieldOrientation.xlRowField; shopField.set_Subtotals(1, false); PivotField monthField = (PivotField)pvt.PivotFields("年月"); monthField.Orientation = XlPivotFieldOrientation.xlRowField; monthField.set_Subtotals(1, false); pvt.AddDataField(pvt.PivotFields(5), "销量", XlConsolidationFunction.xlSum); pvt.AddDataField(pvt.PivotFields(6), "销售额", XlConsolidationFunction.xlSum); ((PivotField)pvt.DataFields["销量"]).NumberFormat = "#,##0"; ((PivotField)pvt.DataFields["销售额"]).NumberFormat = "#,##0"; pivotTableSheet.Activate(); this.WorkBook.Saved = true; this.WorkBook.SaveCopyAs(this.ReportFilePath); this.WorkBook.Close(); } catch (Exception ex) { throw ex; } return(fileName); }
private static void AddPivotRemotely( _Workbook eWorkBook, Worksheet pivotWorkSheet, PivotTable pivotTable, IEnumerable <string> fieldRowNames, IEnumerable <string> fieldColumnNames, IEnumerable <string> fieldValueNames, IEnumerable <string> fieldReportNames, IEnumerable <string> slicerfieldNames, string slicerStyle) { PivotField rowField; PivotField dataField; PivotField colField; PivotField repField; pivotTable.InGridDropZones = false; pivotTable.GrandTotalName = "Total"; pivotTable.DisplayNullString = false; pivotTable.ShowTableStyleRowHeaders = true; pivotTable.ShowTableStyleColumnHeaders = true; string masterFieldName = string.Empty; string masterColumnName = string.Empty; int rowCounter = 0; if (fieldRowNames != null) { foreach (var name in fieldRowNames) { rowCounter += 1; rowField = (PivotField)pivotTable.PivotFields(name); if (masterFieldName.Equals(string.Empty)) { var pf = (PivotField)pivotTable.PivotFields(name); masterFieldName = pf.Name; } rowField.Orientation = XlPivotFieldOrientation.xlRowField; } } if (fieldColumnNames != null) { foreach (var name in fieldColumnNames) { colField = (PivotField)pivotTable.PivotFields(name); colField.Orientation = XlPivotFieldOrientation.xlColumnField; if (masterColumnName.Equals(string.Empty)) { var pf = (PivotField)pivotTable.PivotFields(name); masterColumnName = pf.Name; } } } var nameOfSum = string.Empty; if (fieldValueNames != null) { foreach (var name in fieldValueNames) { dataField = (PivotField)pivotTable.PivotFields(name); dataField.Orientation = XlPivotFieldOrientation.xlDataField; dataField.Function = XlConsolidationFunction.xlSum; dataField.NumberFormat = "#,##0.00"; dataField.Name = $"Total {name}"; if (nameOfSum.Equals(string.Empty)) { nameOfSum = dataField.Name; } } } if (!nameOfSum.Equals(string.Empty) && !masterFieldName.Equals(string.Empty)) { var pf = (PivotField)pivotTable.PivotFields(masterFieldName); pf.AutoSort((int)XlSortOrder.xlDescending, nameOfSum); if (rowCounter > 1) { var pf1 = (PivotField)pivotTable.PivotFields(masterFieldName); pf1.ShowDetail = false; } pivotTable.CompactLayoutRowHeader = masterFieldName; } if (fieldReportNames != null) { foreach (var name in fieldReportNames) { repField = (PivotField)pivotTable.PivotFields(name); repField.Orientation = XlPivotFieldOrientation.xlPageField; } } if (slicerfieldNames != null) { const int cTop = 300; const int cWidth = 100; const int cHeight = 100; var dLeft = 0; foreach (var name in slicerfieldNames) { var slicerCurrent = eWorkBook.SlicerCaches.Add(pivotTable, name); var slicer = slicerCurrent.Slicers.Add( pivotWorkSheet, Top: cTop, Left: dLeft, Width: cWidth * slicerCurrent.SlicerItems.Count, Height: cHeight); slicer.NumberOfColumns = slicerCurrent.SlicerItems.Count / 2; slicer.Style = slicerStyle; dLeft += cWidth + 20; } } }
static void Main(string[] args) { // Declare variables that hold references to excel objects Application eApplication = null; Workbook eWorkbook = null; Worksheet sheet = null; PivotTable pivotTable = null; Range pivotData = null; Range pivotDestination = null; PivotField salesRegion = null; PivotField salesAmount = null; ChartObjects chartObjects = null; ChartObject pivotChart = null; SlicerCache salesTypeSlicer = null; SlicerCache salesRegionSlicer = null; SlicerCache salesPersonSlicer = null; // Declare helper variables string workBookName = @"C:\temp\Excel2010Slicers.xlsx"; string pivotTableName = @"Sales By Type"; string workSheetName = @"Quarterly Sales"; try { // Create an instance of Excel eApplication = new Application(); //Create a workbook and add a worksheet. eWorkbook = eApplication.Workbooks.Add( XlWBATemplate.xlWBATWorksheet); sheet = (Worksheet)(eWorkbook.Worksheets[1]); sheet.Name = workSheetName; //Add Data to the worksheet. //The custom SetRow helper function located at the end of this // source file is used to add a row of values to the worksheet. SetRow(sheet, 1, "Sales Region", "Sales Person", "Sales Type", "Sales Amount"); SetRow(sheet, 2, "West", "Joe", "Wholesale", "123"); SetRow(sheet, 3, "West", "Joe", "Retail", "432"); SetRow(sheet, 4, "West", "Joe", "Government", "111"); SetRow(sheet, 5, "East", "Robert", "Wholesale", "564"); SetRow(sheet, 6, "East", "Robert", "Retail", "234"); SetRow(sheet, 7, "East", "Robert", "Government", "321"); SetRow(sheet, 8, "East", "Michelle", "Wholesale", "940"); SetRow(sheet, 9, "East", "Michelle", "Retail", "892"); SetRow(sheet, 10, "East", "Michelle", "Government", "10"); SetRow(sheet, 11, "West", "Erich", "Wholesale", "120"); SetRow(sheet, 12, "West", "Erich", "Retail", "45"); SetRow(sheet, 13, "West", "Erich", "Government", "410"); SetRow(sheet, 14, "West", "Dafna", "Wholesale", "800"); SetRow(sheet, 15, "West", "Dafna", "Retail", "3409"); SetRow(sheet, 16, "West", "Dafna", "Government", "123"); SetRow(sheet, 17, "East", "Rob", "Wholesale", "777"); SetRow(sheet, 18, "East", "Rob", "Retail", "450"); SetRow(sheet, 19, "East", "Rob", "Government", "900"); sheet.Columns.AutoFit(); // Select a range of data for the Pivot Table. pivotData = sheet.get_Range("A1", "D19"); // Select location of the Pivot Table. pivotDestination = sheet.get_Range("F2"); // Add a pivot table to the worksheet. sheet.PivotTableWizard( XlPivotTableSourceType.xlDatabase, pivotData, pivotDestination, pivotTableName ); // Set variables used to manipulate the Pivot Table. pivotTable = (PivotTable)sheet.PivotTables(pivotTableName); salesRegion = ((PivotField)pivotTable.PivotFields(3)); salesAmount = ((PivotField)pivotTable.PivotFields(4)); // Format the Pivot Table. pivotTable.TableStyle2 = "PivotStyleLight16"; pivotTable.InGridDropZones = false; // Set Sales Region as a Row Field. salesRegion.Orientation = XlPivotFieldOrientation.xlRowField; // Set Sum of Sales Amount as a Value Field. salesAmount.Orientation = XlPivotFieldOrientation.xlDataField; salesAmount.Function = XlConsolidationFunction.xlSum; //Add a pivot chart to the work sheet. chartObjects = (ChartObjects)sheet.ChartObjects(); pivotChart = chartObjects.Add(310, 100, 225, 175); //Format the pivot chart. pivotChart.Chart.ChartWizard(pivotData, XlChartType.xlColumnClustered, Title: "Sales", HasLegend: false, CategoryLabels: 3, SeriesLabels: 0); //Add slicers to the pivot table. salesTypeSlicer = eWorkbook.SlicerCaches.Add(pivotTable, "Sales Type"); salesTypeSlicer.Slicers.Add(sheet, Top: 10, Left: 540, Width: 100, Height: 100); salesRegionSlicer = eWorkbook.SlicerCaches.Add(pivotTable, "Sales Region"); salesRegionSlicer.Slicers.Add(sheet, Top: 120, Left: 540, Width: 100, Height: 100); salesPersonSlicer = eWorkbook.SlicerCaches.Add(pivotTable, "Sales Person"); salesPersonSlicer.Slicers.Add(sheet, Top: 10, Left: 645, Width: 100, Height: 200); // Save the workbook. sheet.get_Range("A1").Activate(); eWorkbook.SaveAs(workBookName, AccessMode: XlSaveAsAccessMode.xlNoChange); } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { //Release the references to the Excel objects. salesAmount = null; salesRegion = null; pivotDestination = null; pivotData = null; pivotChart = null; chartObjects = null; pivotTable = null; salesTypeSlicer = null; salesRegionSlicer = null; salesPersonSlicer = null; sheet = null; //Release the Workbook object. if (eWorkbook != null) { eWorkbook = null; } //Release the ApplicationClass object. if (eApplication != null) { eApplication.Quit(); eApplication = null; } GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); } }
public static void WriteData() { Application xlApp = (Application)ExcelDnaUtil.Application; Workbook wb = xlApp.ActiveWorkbook; if (wb == null) { return; } /* Worksheet ws = wb.Worksheets.Item["Sheet1"]; * ws.Activate(); * ws.Range["A1"].Value = "Date"; * ws.Range["B1"].Value = "Value"; * * Range headerRow = ws.Range["A1", "B1"]; * headerRow.Font.Size = 12; * headerRow.Font.Bold = true; * * // Generally it's faster to write an array to a range * var values = new object[100, 2]; * var startDate = new DateTime(2007, 1, 1); * var rand = new Random(); * for (int i = 0; i < 100; i++) * { * values[i, 0] = startDate.AddDays(i); * values[i, 1] = rand.NextDouble(); * } * * ws.Range["A2"].Resize[100, 2].Value = values; * ws.Columns["A:A"].EntireColumn.AutoFit(); * * // Add a chart * Range dataRange= ws.Range["A1:B101"]; * dataRange.Select(); * ws.Shapes.AddChart(XlChartType.xlColumnClustered).Select(); * xlApp.ActiveChart.SetSourceData(Source: dataRange); */ string filename = "D:\\OneDrive\\Alan\\Code\\Visual Studio\\Projects\\Ribbon\\bin\\Debug\\ID1541v1-FUZZY-results-extract.csv"; System.Windows.Forms.OpenFileDialog fd = new System.Windows.Forms.OpenFileDialog(); fd.Filter = "CSV Files|*.csv"; fd.InitialDirectory = wb.Path; if (fd.ShowDialog() == System.Windows.Forms.DialogResult.OK) { filename = fd.FileName; } xlApp.ScreenUpdating = false; Worksheet ws = wb.Worksheets.Add(Type: XlSheetType.xlWorksheet); ws.Name = "data"; var columns = File.ReadLines("D:\\OneDrive\\Alan\\Code\\Visual Studio\\Projects\\Ribbon\\bin\\Debug\\test.txt").First().Split('\t').ToArray(); int cols = columns.Length; var contents = File.ReadAllLines(filename); int rows = contents.Length; var headings = contents.First().Split('\t').ToArray(); var vals = new object[rows, cols + 2]; var colInd = new int[cols]; for (int i = 0; i < cols; i++) { colInd[i] = Array.IndexOf(headings, columns[i]); } int r = 1; int depthCol = Array.IndexOf(headings, "Depth"); int nameCol = Array.IndexOf(headings, "Test Name"); for (int j = 0; j < columns.Length; j++) { vals[0, j] = headings[colInd[j]]; } vals[0, cols] = "Len"; vals[0, cols + 1] = "Words"; for (int i = 1; i < contents.Length; i++) { var thisLine = contents[i].Split('\t').ToArray(); if (Int32.Parse(thisLine[depthCol]) == 1) { for (int j = 0; j < Math.Min(thisLine.Length, cols); j++) { vals[r, j] = thisLine[colInd[j]]; } vals[r, cols] = thisLine[nameCol].Length; vals[r, cols + 1] = NumOccurances(thisLine[nameCol], " "); r++; } } Range data = ws.Range["A1"].Resize[r, cols + 2]; data.Value = vals; Worksheet ws2 = wb.Worksheets.Add(Type: XlSheetType.xlWorksheet); Range pivotDestination = ws2.Range["A1"]; string pivotTableName = @"mypivot"; wb.PivotTableWizard(XlPivotTableSourceType.xlDatabase, data, pivotDestination, pivotTableName, true, true, true, true, Type.Missing, Type.Missing, false, false, XlOrder.xlDownThenOver, 0, Type.Missing, Type.Missing); PivotTable pivotTable = (PivotTable)ws2.PivotTables(pivotTableName); PivotField shortnamePivotField = (PivotField)pivotTable.PivotFields(2); //itemcodePivotField = (PivotField)pivotTable.PivotFields(3); //descriptionPivotField = (PivotField)pivotTable.PivotFields(4); //pricePivotField = (PivotField)pivotTable.PivotFields(7); // Format the Pivot Table. //pivotTable.Format(XlPivotFormatType.xlReport2); //pivotTable.InGridDropZones = false; //pivotTable.SmallGrid = false; //pivotTable.ShowTableStyleRowStripes = true; //pivotTable.TableStyle2 = "PivotStyleLight1"; // Page Field shortnamePivotField.Orientation = XlPivotFieldOrientation.xlPageField; /*shortnamePivotField.Position = 1; * shortnamePivotField.CurrentPage = "(All)"; * * // Row Fields * descriptionPivotField.Orientation = XlPivotFieldOrientation.xlRowField; * descriptionPivotField.Position = 1; * itemcodePivotField.Orientation = XlPivotFieldOrientation.xlRowField; * itemcodePivotField.Position = 2; * * // Data Field * pricePivotField.Orientation = XlPivotFieldOrientation.xlDataField; * pricePivotField.Function = XlConsolidationFunction.xlSum; */ xlApp.ScreenUpdating = true; }
static void makeGraphs(string file) { Excel.Application excelApp = null; Excel.Workbook workbook = null; Excel.Sheets sheets = null; Excel.Worksheet dataSheet = null; Excel.Worksheet newSheet = null; Excel.Worksheet chartSheet = null; Excel.Range range = null; Excel.Range dataR = null; int rowC = 0; try { excelApp = new Excel.Application(); string dir = file.Substring(0, file.LastIndexOf("\\") + 1); string fm = file.Substring(0, file.Length - 4).Substring(file.LastIndexOf("\\") + 1); workbook = excelApp.Workbooks.Open(file, 0, false, 6, Type.Missing, Type.Missing, Type.Missing, XlPlatform.xlWindows, ",", true, false, 0, false, false, false); sheets = workbook.Sheets; dataSheet = sheets[1]; dataSheet.Name = "data"; newSheet = (Worksheet)sheets.Add(Type.Missing, dataSheet, Type.Missing, Type.Missing); newSheet.Name = "table"; chartSheet = (Worksheet)sheets.Add(Type.Missing, dataSheet, Type.Missing, Type.Missing); chartSheet.Name = "graph"; Excel.ChartObjects xlChart = (Excel.ChartObjects)chartSheet.ChartObjects(Type.Missing); dataR = dataSheet.UsedRange; rowC = dataR.Rows.Count; range = newSheet.get_Range("A1"); PivotCaches pCs = workbook.PivotCaches(); PivotCache pC = pCs.Create(XlPivotTableSourceType.xlDatabase, dataR, Type.Missing); PivotTable pT = pC.CreatePivotTable(TableDestination: range, TableName: "PivotTable1"); PivotField fA = pT.PivotFields("Time"); PivotField fB = pT.PivotFields("Command"); fA.Orientation = XlPivotFieldOrientation.xlRowField; fA.Position = 1; fB.Orientation = XlPivotFieldOrientation.xlColumnField; fB.Position = 1; pT.AddDataField(pT.PivotFields("%CPU"), "Sum of %CPU", XlConsolidationFunction.xlSum); ChartObject pChart = (Excel.ChartObject)xlChart.Add(0, 0, 650, 450); Chart chartP = pChart.Chart; chartP.SetSourceData(pT.TableRange1, Type.Missing); chartP.ChartType = XlChartType.xlLine; excelApp.DisplayAlerts = false; workbook.SaveAs(@dir + fm, XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing); workbook.Close(true, Type.Missing, Type.Missing); excelApp.Quit(); } catch { Console.WriteLine("Had issues interacting with your Excel installation...maybe try a restart?"); using (StreamWriter outfile = File.AppendText("output.txt")) { outfile.WriteLine("Did have issues interacting with Excel on " + file); } } finally { /*Excel.Application excelApp = null; * Excel.Workbook workbook = null; * Excel.Sheets sheets = null; * Excel.Worksheet dataSheet = null; * Excel.Worksheet newSheet = null; * Excel.Worksheet chartSheet = null; * Excel.Range range = null; * Excel.Range dataR = null;*/ releaseObject(dataR); releaseObject(range); releaseObject(chartSheet); releaseObject(newSheet); releaseObject(dataSheet); releaseObject(sheets); releaseObject(workbook); releaseObject(excelApp); } }
/// <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(); } }