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; }
public ADOTabularConnection GetPowerPivotConnection() { PivotCache pc = null; string connStr = ""; PivotCaches pvtcaches = _app.ActiveWorkbook.PivotCaches(); if (float.Parse(_app.Version) >= 15) { pc = (from PivotCache pvtc in pvtcaches let conn = pvtc.Connection.ToString() where pvtc.OLAP && pvtc.CommandType == XlCmdType.xlCmdCube && (int)pvtc.WorkbookConnection.Type == 7 // xl15Model select pvtc).First(); connStr = (string)((dynamic)pc.WorkbookConnection).ModelConnection.ADOConnection.ConnectionString; connStr = string.Format("{0};location={1}", connStr, _app.ActiveWorkbook.FullName); // for connections to Excel 2013 or later we need to use the Excel version of ADOMDClient return(new ADOTabularConnection(connStr, AdomdType.Excel)); } else { pc = (from PivotCache pvtc in pvtcaches let conn = pvtc.Connection.ToString() where pvtc.OLAP && pvtc.CommandType == XlCmdType.xlCmdCube //&& (int)pvtc.WorkbookConnection.Type == 7 select pvtc).First(); connStr = ((dynamic)pc.WorkbookConnection).OLEDBConnection.Connection.Replace("OLEDB;", ""); connStr = string.Format("{0};location={1}", connStr, _app.ActiveWorkbook.FullName); // for connections to Excel 2010 we need to use the AnalysisServices version of ADOMDClient return(new ADOTabularConnection(connStr, AdomdType.AnalysisServices)); } }
private void btnRun_Click(object sender, System.EventArgs e) { Workbook workbook = new Workbook(); Worksheet sheet = workbook.Worksheets[0]; // Setting the value to the cells sheet.Range["A1"].Value = "Product"; sheet.Range["B1"].Value = "Month"; sheet.Range["C1"].Value = "Count"; sheet.Range["A2"].Value = "SpireDoc"; sheet.Range["A3"].Value = "SpireDoc"; sheet.Range["A4"].Value = "SpireXls"; sheet.Range["A5"].Value = "SpireDoc"; sheet.Range["A6"].Value = "SpireXls"; sheet.Range["A7"].Value = "SpireXls"; sheet.Range["B2"].Value = "January"; sheet.Range["B3"].Value = "February"; sheet.Range["B4"].Value = "January"; sheet.Range["B5"].Value = "January"; sheet.Range["B6"].Value = "February"; sheet.Range["B7"].Value = "February"; sheet.Range["C2"].Value = "10"; sheet.Range["C3"].Value = "15"; sheet.Range["C4"].Value = "9"; sheet.Range["C5"].Value = "7"; sheet.Range["C6"].Value = "8"; sheet.Range["C7"].Value = "10"; // Adding a PivotTable to the worksheet CellRange dataRange = sheet.Range["A1:C7"]; PivotCache cache = workbook.PivotCaches.Add(dataRange); PivotTable pt = sheet.PivotTables.Add("Pivot Table", sheet.Range["C10"], cache); // Draging the fields to the row area. PivotField pf = pt.PivotFields["Product"] as PivotField; pf.Axis = AxisTypes.Row; PivotField pf2 = pt.PivotFields["Month"] as PivotField; pf2.Axis = AxisTypes.Row; //Draging the field to the data area. pt.DataFields.Add(pt.PivotFields["Count"], "SUM of Count", SubtotalTypes.Sum); //set PivotTable style pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12; //autofit columns generated by the pivot table pt.CalculateData(); sheet.AutoFitColumn(3); sheet.AutoFitColumn(4); workbook.SaveToFile("Sample.xlsx", ExcelVersion.Version2010); ExcelDocViewer(workbook.FileName); }
static void CreatePivotTableFromCache(IWorkbook workbook) { #region #Create from PivotCache Worksheet worksheet = workbook.Worksheets.Add(); workbook.Worksheets.ActiveWorksheet = worksheet; PivotCache cache = workbook.Worksheets["Report1"].PivotTables["PivotTable1"].Cache; PivotTable pivotTable = worksheet.PivotTables.Add(cache, worksheet["B2"]); pivotTable.RowFields.Add(pivotTable.Fields["Category"]); pivotTable.RowFields.Add(pivotTable.Fields["Product"]); pivotTable.DataFields.Add(pivotTable.Fields["Sales"]); pivotTable.Style = workbook.TableStyles.DefaultPivotStyle; #endregion #Create from PivotCache }
static void CreatePivotTableFromCache(IWorkbook workbook) { #region #CreateFromPivotCache Worksheet worksheet = workbook.Worksheets.Add(); workbook.Worksheets.ActiveWorksheet = worksheet; // Create a pivot table based on the specified PivotTable cache. PivotCache cache = workbook.Worksheets["Report1"].PivotTables["PivotTable1"].Cache; PivotTable pivotTable = worksheet.PivotTables.Add(cache, worksheet["B2"]); // Add the "Category" field to the row axis area. pivotTable.RowFields.Add(pivotTable.Fields["Category"]); // Add the "Product" field to the row axis area. pivotTable.RowFields.Add(pivotTable.Fields["Product"]); // Add the "Sales" field to the data area. pivotTable.DataFields.Add(pivotTable.Fields["Sales"]); // Set the default style for the pivot table. pivotTable.Style = workbook.TableStyles.DefaultPivotStyle; #endregion #CreateFromPivotCache }
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; }
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 SaveToStream(IDataStream DataStream, TSaveData SaveData, int SheetIndex) { if ((sBOF == null) || (sEOF == null)) { XlsMessages.ThrowException(XlsErr.ErrSectionNotLoaded); } sBOF.SaveToStream(DataStream, SaveData, 0); FileEncryption.SaveFirstPart(DataStream, SaveData); //WriteProt if (DataStream.Encryption.Engine != null) //FilePass { byte[] Fp = DataStream.Encryption.Engine.GetFilePassRecord(); DataStream.WriteRaw(Fp, Fp.Length); } if (IsXltTemplate) { TTemplateRecord.SaveNewRecord(DataStream); } FileEncryption.SaveSecondPart(DataStream, SaveData); TGlobalRecordSaver g = new TGlobalRecordSaver(DataStream, SaveData); g.SaveRecord(CodePage); FLel.SaveToStream(DataStream, SaveData, 0); TDSFRecord.SaveDSF(DataStream); g.SaveRecord(Excel9File); if (SheetIndex < 0) { FBoundSheets.SaveTabIds(DataStream); } g.SaveRecord(GetMacroRec(ObjProj)); g.SaveRecord(GetMacroRec(ObNoMacros)); g.SaveRecord(CodeNameRecord); FFnGroups.SaveToStream(DataStream, SaveData, 0); g.SaveRecord(OleObjectSize); FWorkbookProtection.SaveToStream(DataStream, SaveData); foreach (TWindow1Record w1 in Window1) { g.SaveRecord(w1); } TBackupRecord.SaveRecord(DataStream, Backup); THideObjRecord.SaveRecord(DataStream, HideObj); T1904Record.SaveRecord(DataStream, Dates1904); TPrecisionRecord.SaveRecord(DataStream, PrecisionAsDisplayed); TRefreshAllRecord.SaveRecord(DataStream, RefreshAll); g.SaveRecord(BookBool); Fonts.SaveToStream(DataStream, SaveData, 0); Formats.SaveToStream(DataStream, SaveData, 0); StyleXF.SaveAllToStream(DataStream, ref SaveData, CellXF); DXF.SaveToStream(DataStream, SaveData, 0); Styles.SaveToStream(DataStream, SaveData, 0); TableStyles.SaveToStream(DataStream, SaveData, 0); g.SaveRecord(Palette); g.SaveRecord(ClrtClient); PivotCache.SaveToStream(DataStream, SaveData, 0); DocRoute.SaveToStream(DataStream, SaveData, 0); UserBView.SaveToStream(DataStream, SaveData, 0); TUsesELFsRecord.SaveRecord(DataStream, UsesELFs); if (SheetIndex < 0) { FBoundSheets.SaveToStream(DataStream, SaveData); MetaData.SaveToStream(DataStream, SaveData, 0); } else { FBoundSheets.SaveRangeToStream(DataStream, SaveData, SheetIndex); } g.SaveRecord(MTRSettings); g.SaveRecord(ForceFullCalculation); g.SaveRecord(Country); FReferences.SaveToStream(DataStream, SaveData); FNames.SaveToStream(DataStream, SaveData, 0); //Should be after FBoundSheets.SaveToStream RealTimeData.SaveToStream(DataStream, SaveData, 0); g.SaveRecord(RecalcId); if (SheetIndex < 0) { FHeaderImages.SaveToStream(DataStream, SaveData); FDrawingGroup.SaveToStream(DataStream, SaveData); } FSST.SaveToStream(DataStream, SaveData); WebPub.SaveToStream(DataStream, SaveData, 0); g.SaveRecord(WOpt); //CrErr is ignored g.SaveRecord(BookExt); FeatHdr.SaveToStream(DataStream, SaveData, 0); DConn.SaveToStream(DataStream, SaveData, 0); ThemeRecord.SaveToStream(DataStream, SaveData, 0); g.SaveRecord(CompressPictures); g.SaveRecord(Compat12); g.SaveRecord(GUIDTypeLib); FFutureRecords.SaveToStream(DataStream, SaveData, 0); sEOF.SaveToStream(DataStream, SaveData, 0); }
//private void GenerateDrawingsPartContent(DrawingsPart drawingsPart, XLWorksheet worksheet) //{ // if (drawingsPart.WorksheetDrawing == null) // drawingsPart.WorksheetDrawing = new Xdr.WorksheetDrawing(); // var worksheetDrawing = drawingsPart.WorksheetDrawing; // if (!worksheetDrawing.NamespaceDeclarations.Contains(new KeyValuePair<string, string>("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"))) // worksheetDrawing.AddNamespaceDeclaration("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"); // if (!worksheetDrawing.NamespaceDeclarations.Contains(new KeyValuePair<string, string>("a", "http://schemas.openxmlformats.org/drawingml/2006/main"))) // worksheetDrawing.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main"); // foreach (var chart in worksheet.Charts.OrderBy(c => c.ZOrder).Select(c => c)) // { // Xdr.TwoCellAnchor twoCellAnchor = new Xdr.TwoCellAnchor(); // worksheetDrawing.AppendChild(twoCellAnchor); // if (chart.Anchor == XLDrawingAnchor.MoveAndSizeWithCells) // twoCellAnchor.EditAs = Xdr.EditAsValues.TwoCell; // else if (chart.Anchor == XLDrawingAnchor.MoveWithCells) // twoCellAnchor.EditAs = Xdr.EditAsValues.OneCell; // else // twoCellAnchor.EditAs = Xdr.EditAsValues.Absolute; // if (twoCellAnchor.FromMarker == null) // twoCellAnchor.FromMarker = new Xdr.FromMarker(); // twoCellAnchor.FromMarker.RowId = new Xdr.RowId((chart.FirstRow - 1).ToString()); // twoCellAnchor.FromMarker.RowOffset = new Xdr.RowOffset(chart.FirstRowOffset.ToString()); // twoCellAnchor.FromMarker.ColumnId = new Xdr.ColumnId((chart.FirstColumn - 1).ToString()); // twoCellAnchor.FromMarker.ColumnOffset = new Xdr.ColumnOffset(chart.FirstColumnOffset.ToString()); // if (twoCellAnchor.ToMarker == null) // twoCellAnchor.ToMarker = new Xdr.ToMarker(); // twoCellAnchor.ToMarker.RowId = new Xdr.RowId((chart.LastRow - 1).ToString()); // twoCellAnchor.ToMarker.RowOffset = new Xdr.RowOffset(chart.LastRowOffset.ToString()); // twoCellAnchor.ToMarker.ColumnId = new Xdr.ColumnId((chart.LastColumn - 1).ToString()); // twoCellAnchor.ToMarker.ColumnOffset = new Xdr.ColumnOffset(chart.LastColumnOffset.ToString()); // Xdr.GraphicFrame graphicFrame = new Xdr.GraphicFrame(); // twoCellAnchor.AppendChild(graphicFrame); // if (graphicFrame.NonVisualGraphicFrameProperties == null) // graphicFrame.NonVisualGraphicFrameProperties = new Xdr.NonVisualGraphicFrameProperties(); // if (graphicFrame.NonVisualGraphicFrameProperties.NonVisualDrawingProperties == null) // graphicFrame.NonVisualGraphicFrameProperties.NonVisualDrawingProperties = new Xdr.NonVisualDrawingProperties() { Id = (UInt32)chart.Id, Name = chart.Name, Description = chart.Description, Hidden = chart.Hidden }; // if (graphicFrame.NonVisualGraphicFrameProperties.NonVisualGraphicFrameDrawingProperties == null) // graphicFrame.NonVisualGraphicFrameProperties.NonVisualGraphicFrameDrawingProperties = new Xdr.NonVisualGraphicFrameDrawingProperties(); // if (graphicFrame.Transform == null) // graphicFrame.Transform = new Xdr.Transform(); // if (chart.HorizontalFlip) // graphicFrame.Transform.HorizontalFlip = true; // else // graphicFrame.Transform.HorizontalFlip = null; // if (chart.VerticalFlip) // graphicFrame.Transform.VerticalFlip = true; // else // graphicFrame.Transform.VerticalFlip = null; // if (chart.Rotation != 0) // graphicFrame.Transform.Rotation = chart.Rotation; // else // graphicFrame.Transform.Rotation = null; // if (graphicFrame.Transform.Offset == null) // graphicFrame.Transform.Offset = new A.Offset(); // graphicFrame.Transform.Offset.X = chart.OffsetX; // graphicFrame.Transform.Offset.Y = chart.OffsetY; // if (graphicFrame.Transform.Extents == null) // graphicFrame.Transform.Extents = new A.Extents(); // graphicFrame.Transform.Extents.Cx = chart.ExtentLength; // graphicFrame.Transform.Extents.Cy = chart.ExtentWidth; // if (graphicFrame.Graphic == null) // graphicFrame.Graphic = new A.Graphic(); // if (graphicFrame.Graphic.GraphicData == null) // graphicFrame.Graphic.GraphicData = new A.GraphicData() { Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart" }; // if (!graphicFrame.Graphic.GraphicData.Elements<C.ChartReference>().Any()) // { // C.ChartReference chartReference = new C.ChartReference() { Id = "rId" + chart.Id.ToStringLookup() }; // chartReference.AddNamespaceDeclaration("c", "http://schemas.openxmlformats.org/drawingml/2006/chart"); // chartReference.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); // graphicFrame.Graphic.GraphicData.AppendChild(chartReference); // } // if (!twoCellAnchor.Elements<Xdr.ClientData>().Any()) // twoCellAnchor.AppendChild(new Xdr.ClientData()); // } //} //private void GenerateChartPartContent(ChartPart chartPart, XLChart xlChart) //{ // if (chartPart.ChartSpace == null) // chartPart.ChartSpace = new C.ChartSpace(); // C.ChartSpace chartSpace = chartPart.ChartSpace; // if (!chartSpace.NamespaceDeclarations.Contains(new KeyValuePair<string, string>("c", "http://schemas.openxmlformats.org/drawingml/2006/chart"))) // chartSpace.AddNamespaceDeclaration("c", "http://schemas.openxmlformats.org/drawingml/2006/chart"); // if (!chartSpace.NamespaceDeclarations.Contains(new KeyValuePair<string, string>("a", "http://schemas.openxmlformats.org/drawingml/2006/main"))) // chartSpace.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main"); // if (!chartSpace.NamespaceDeclarations.Contains(new KeyValuePair<string, string>("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"))) // chartSpace.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); // if (chartSpace.EditingLanguage == null) // chartSpace.EditingLanguage = new C.EditingLanguage() { Val = CultureInfo.CurrentCulture.Name }; // else // chartSpace.EditingLanguage.Val = CultureInfo.CurrentCulture.Name; // C.Chart chart = new C.Chart(); // chartSpace.AppendChild(chart); // if (chart.Title == null) // chart.Title = new C.Title(); // if (chart.Title.Layout == null) // chart.Title.Layout = new C.Layout(); // if (chart.View3D == null) // chart.View3D = new C.View3D(); // if (chart.View3D.RightAngleAxes == null) // chart.View3D.RightAngleAxes = new C.RightAngleAxes(); // chart.View3D.RightAngleAxes.Val = xlChart.RightAngleAxes; // if (chart.PlotArea == null) // chart.PlotArea = new C.PlotArea(); // if (chart.PlotArea.Layout == null) // chart.PlotArea.Layout = new C.Layout(); // OpenXmlElement chartElement = GetChartElement(xlChart); // chart.PlotArea.AppendChild(chartElement); // C.CategoryAxis categoryAxis1 = new C.CategoryAxis(); // C.AxisId axisId4 = new C.AxisId() { Val = (UInt32Value)71429120U }; // C.Scaling scaling1 = new C.Scaling(); // C.Orientation orientation1 = new C.Orientation() { Val = C.OrientationValues.MinMax }; // scaling1.AppendChild(orientation1); // C.AxisPosition axisPosition1 = new C.AxisPosition() { Val = C.AxisPositionValues.Bottom }; // C.TickLabelPosition tickLabelPosition1 = new C.TickLabelPosition() { Val = C.TickLabelPositionValues.NextTo }; // C.CrossingAxis crossingAxis1 = new C.CrossingAxis() { Val = (UInt32Value)71432064U }; // C.Crosses crosses1 = new C.Crosses() { Val = C.CrossesValues.AutoZero }; // C.AutoLabeled autoLabeled1 = new C.AutoLabeled() { Val = true }; // C.LabelAlignment labelAlignment1 = new C.LabelAlignment() { Val = C.LabelAlignmentValues.Center }; // C.LabelOffset labelOffset1 = new C.LabelOffset() { Val = (UInt16Value)100U }; // categoryAxis1.AppendChild(axisId4); // categoryAxis1.AppendChild(scaling1); // categoryAxis1.AppendChild(axisPosition1); // categoryAxis1.AppendChild(tickLabelPosition1); // categoryAxis1.AppendChild(crossingAxis1); // categoryAxis1.AppendChild(crosses1); // categoryAxis1.AppendChild(autoLabeled1); // categoryAxis1.AppendChild(labelAlignment1); // categoryAxis1.AppendChild(labelOffset1); // C.ValueAxis valueAxis1 = new C.ValueAxis(); // C.AxisId axisId5 = new C.AxisId() { Val = (UInt32Value)71432064U }; // C.Scaling scaling2 = new C.Scaling(); // C.Orientation orientation2 = new C.Orientation() { Val = C.OrientationValues.MinMax }; // scaling2.AppendChild(orientation2); // C.AxisPosition axisPosition2 = new C.AxisPosition() { Val = C.AxisPositionValues.Left }; // C.MajorGridlines majorGridlines1 = new C.MajorGridlines(); // C.NumberingFormat numberingFormat1 = new C.NumberingFormat() { FormatCode = "General", SourceLinked = true }; // C.TickLabelPosition tickLabelPosition2 = new C.TickLabelPosition() { Val = C.TickLabelPositionValues.NextTo }; // C.CrossingAxis crossingAxis2 = new C.CrossingAxis() { Val = (UInt32Value)71429120U }; // C.Crosses crosses2 = new C.Crosses() { Val = C.CrossesValues.AutoZero }; // C.CrossBetween crossBetween1 = new C.CrossBetween() { Val = C.CrossBetweenValues.Between }; // valueAxis1.AppendChild(axisId5); // valueAxis1.AppendChild(scaling2); // valueAxis1.AppendChild(axisPosition2); // valueAxis1.AppendChild(majorGridlines1); // valueAxis1.AppendChild(numberingFormat1); // valueAxis1.AppendChild(tickLabelPosition2); // valueAxis1.AppendChild(crossingAxis2); // valueAxis1.AppendChild(crosses2); // valueAxis1.AppendChild(crossBetween1); // plotArea.AppendChild(bar3DChart1); // plotArea.AppendChild(categoryAxis1); // plotArea.AppendChild(valueAxis1); // C.Legend legend1 = new C.Legend(); // C.LegendPosition legendPosition1 = new C.LegendPosition() { Val = C.LegendPositionValues.Right }; // C.Layout layout3 = new C.Layout(); // legend1.AppendChild(legendPosition1); // legend1.AppendChild(layout3); // C.PlotVisibleOnly plotVisibleOnly1 = new C.PlotVisibleOnly() { Val = true }; // chart.AppendChild(legend1); // chart.AppendChild(plotVisibleOnly1); // C.PrintSettings printSettings1 = new C.PrintSettings(); // C.HeaderFooter headerFooter1 = new C.HeaderFooter(); // C.PageMargins pageMargins4 = new C.PageMargins() { Left = 0.70000000000000018D, Right = 0.70000000000000018D, Top = 0.75000000000000022D, Bottom = 0.75000000000000022D, Header = 0.3000000000000001D, Footer = 0.3000000000000001D }; // C.PageSetup pageSetup1 = new C.PageSetup(); // printSettings1.AppendChild(headerFooter1); // printSettings1.AppendChild(pageMargins4); // printSettings1.AppendChild(pageSetup1); // chartSpace.AppendChild(printSettings1); //} //private OpenXmlElement GetChartElement(XLChart xlChart) //{ // if (xlChart.ChartTypeCategory == XLChartTypeCategory.Bar3D) // return GetBar3DChart(xlChart); // else // return null; //} //private OpenXmlElement GetBar3DChart(XLChart xlChart) //{ // C.Bar3DChart bar3DChart = new C.Bar3DChart(); // bar3DChart.BarDirection = new C.BarDirection() { Val = GetBarDirection(xlChart) }; // bar3DChart.BarGrouping = new C.BarGrouping() { Val = GetBarGrouping(xlChart) }; // C.BarChartSeries barChartSeries = new C.BarChartSeries(); // barChartSeries.Index = new C.Index() { Val = (UInt32Value)0U }; // barChartSeries.Order = new C.Order() { Val = (UInt32Value)0U }; // C.SeriesText seriesText1 = new C.SeriesText(); // C.StringReference stringReference1 = new C.StringReference(); // C.Formula formula1 = new C.Formula(); // formula1.Text = "Sheet1!$B$1"; // stringReference1.AppendChild(formula1); // seriesText1.AppendChild(stringReference1); // C.CategoryAxisData categoryAxisData1 = new C.CategoryAxisData(); // C.StringReference stringReference2 = new C.StringReference(); // C.Formula formula2 = new C.Formula(); // formula2.Text = "Sheet1!$A$2:$A$3"; // C.StringCache stringCache2 = new C.StringCache(); // C.PointCount pointCount2 = new C.PointCount() { Val = (UInt32Value)2U }; // C.StringPoint stringPoint2 = new C.StringPoint() { Index = (UInt32Value)0U }; // C.NumericValue numericValue2 = new C.NumericValue(); // numericValue2.Text = "A"; // stringPoint2.AppendChild(numericValue2); // C.StringPoint stringPoint3 = new C.StringPoint() { Index = (UInt32Value)1U }; // C.NumericValue numericValue3 = new C.NumericValue(); // numericValue3.Text = "B"; // stringPoint3.AppendChild(numericValue3); // stringCache2.AppendChild(pointCount2); // stringCache2.AppendChild(stringPoint2); // stringCache2.AppendChild(stringPoint3); // stringReference2.AppendChild(formula2); // stringReference2.AppendChild(stringCache2); // categoryAxisData1.AppendChild(stringReference2); // C.Values values1 = new C.Values(); // C.NumberReference numberReference1 = new C.NumberReference(); // C.Formula formula3 = new C.Formula(); // formula3.Text = "Sheet1!$B$2:$B$3"; // C.NumberingCache numberingCache1 = new C.NumberingCache(); // C.FormatCode formatCode1 = new C.FormatCode(); // formatCode1.Text = "General"; // C.PointCount pointCount3 = new C.PointCount() { Val = (UInt32Value)2U }; // C.NumericPoint numericPoint1 = new C.NumericPoint() { Index = (UInt32Value)0U }; // C.NumericValue numericValue4 = new C.NumericValue(); // numericValue4.Text = "5"; // numericPoint1.AppendChild(numericValue4); // C.NumericPoint numericPoint2 = new C.NumericPoint() { Index = (UInt32Value)1U }; // C.NumericValue numericValue5 = new C.NumericValue(); // numericValue5.Text = "10"; // numericPoint2.AppendChild(numericValue5); // numberingCache1.AppendChild(formatCode1); // numberingCache1.AppendChild(pointCount3); // numberingCache1.AppendChild(numericPoint1); // numberingCache1.AppendChild(numericPoint2); // numberReference1.AppendChild(formula3); // numberReference1.AppendChild(numberingCache1); // values1.AppendChild(numberReference1); // barChartSeries.AppendChild(index1); // barChartSeries.AppendChild(order1); // barChartSeries.AppendChild(seriesText1); // barChartSeries.AppendChild(categoryAxisData1); // barChartSeries.AppendChild(values1); // C.Shape shape1 = new C.Shape() { Val = C.ShapeValues.Box }; // C.AxisId axisId1 = new C.AxisId() { Val = (UInt32Value)71429120U }; // C.AxisId axisId2 = new C.AxisId() { Val = (UInt32Value)71432064U }; // C.AxisId axisId3 = new C.AxisId() { Val = (UInt32Value)0U }; // bar3DChart.AppendChild(barChartSeries); // bar3DChart.AppendChild(shape1); // bar3DChart.AppendChild(axisId1); // bar3DChart.AppendChild(axisId2); // bar3DChart.AppendChild(axisId3); // return bar3DChart; //} //private C.BarGroupingValues GetBarGrouping(XLChart xlChart) //{ // if (xlChart.BarGrouping == XLBarGrouping.Clustered) // return C.BarGroupingValues.Clustered; // else if (xlChart.BarGrouping == XLBarGrouping.Percent) // return C.BarGroupingValues.PercentStacked; // else if (xlChart.BarGrouping == XLBarGrouping.Stacked) // return C.BarGroupingValues.Stacked; // else // return C.BarGroupingValues.Standard; //} //private C.BarDirectionValues GetBarDirection(XLChart xlChart) //{ // if (xlChart.BarOrientation == XLBarOrientation.Vertical) // return C.BarDirectionValues.Column; // else // return C.BarDirectionValues.Bar; //} //-- private static void GeneratePivotTables(WorkbookPart workbookPart, WorksheetPart worksheetPart, XLWorksheet xlWorksheet, SaveContext context) { foreach (var pt in xlWorksheet.PivotTables) { var ptCdp = context.RelIdGenerator.GetNext(RelType.Workbook); var pivotTableCacheDefinitionPart = workbookPart.AddNewPart<PivotTableCacheDefinitionPart>(ptCdp); GeneratePivotTableCacheDefinitionPartContent(pivotTableCacheDefinitionPart, pt); var pivotCaches = new PivotCaches(); var pivotCache = new PivotCache {CacheId = 0U, Id = ptCdp}; pivotCaches.AppendChild(pivotCache); workbookPart.Workbook.AppendChild(pivotCaches); var pivotTablePart = worksheetPart.AddNewPart<PivotTablePart>(context.RelIdGenerator.GetNext(RelType.Workbook)); GeneratePivotTablePartContent(pivotTablePart, pt); pivotTablePart.AddPart(pivotTableCacheDefinitionPart, context.RelIdGenerator.GetNext(RelType.Workbook)); } }
static void Main(string[] args) { // If using Professional version, put your serial key below. SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY"); ExcelFile ef = new ExcelFile(); ExcelWorksheet ws1 = ef.Worksheets.Add("SourceSheet"); // Specify sheet formatting. ws1.Rows[0].Style.Font.Weight = ExcelFont.BoldWeight; ws1.Columns[0].SetWidth(3, LengthUnit.Centimeter); ws1.Columns[1].SetWidth(3, LengthUnit.Centimeter); ws1.Columns[2].SetWidth(3, LengthUnit.Centimeter); ws1.Columns[3].SetWidth(3, LengthUnit.Centimeter); ws1.Columns[3].Style.NumberFormat = "[$$-409]#,##0.00"; var cells = ws1.Cells; // Specify header row. cells[0, 0].Value = "Departments"; cells[0, 1].Value = "Names"; cells[0, 2].Value = "Years of Service"; cells[0, 3].Value = "Salaries"; // Insert random data to sheet. var random = new Random(); var departments = new string[] { "Legal", "Marketing", "Finance", "Planning", "Purchasing" }; var names = new string[] { "John Doe", "Fred Nurk", "Hans Meier", "Ivan Horvat" }; var years = new string[] { "1-10", "11-20", "21-30", "over 30" }; for (int i = 0; i < 100; ++i) { cells[i + 1, 0].Value = departments[random.Next(departments.Length)]; cells[i + 1, 1].Value = names[random.Next(names.Length)] + ' ' + (i + 1).ToString(); cells[i + 1, 2].Value = years[random.Next(years.Length)]; cells[i + 1, 3].SetValue(random.Next(10, 101) * 100); } // Create pivot cache from cell range "SourceSheet!A1:D100". PivotCache cache = ef.PivotCaches.AddWorksheetSource("SourceSheet!A1:D100"); // Create new sheet for pivot table. ExcelWorksheet ws2 = ef.Worksheets.Add("PivotSheet"); // Create pivot table "Company Profile" using the specified pivot cache and add it to the worksheet at the cell location 'A1'. PivotTable table = ws2.PivotTables.Add(cache, "Company Profile", "A1"); // Aggregate 'Names' values into count value and show it as a percentage of row. PivotField field = table.DataFields.Add("Names"); field.Function = PivotFieldCalculationType.Count; field.ShowDataAs = PivotFieldDisplayFormat.PercentageOfRow; field.Name = "% of Empl."; // Aggregate 'Salaries' values into average value. field = table.DataFields.Add("Salaries"); field.Function = PivotFieldCalculationType.Average; field.Name = "Avg. Salary"; field.NumberFormat = "[$$-409]#,##0.00"; // Group rows into 'Departments'. table.RowFields.Add("Departments"); // Group columns first into 'Years of Service' and then into 'Values' (count 'Names' and average 'Salaries'). table.ColumnFields.Add("Years of Service"); table.ColumnFields.Add(table.DataPivotField); // Specify the string to be displayed in row and column header. table.RowHeaderCaption = "Departments"; table.ColumnHeaderCaption = "Years of Service"; // Do not show grand totals for rows. table.RowGrandTotals = false; // Set pivot table style. table.BuiltInStyle = BuiltInPivotStyleName.PivotStyleMedium7; ef.Save("Pivot Tables.xlsx"); }
// Generates content of workbookPart1. private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1) { Workbook workbook1 = new Workbook(); Sheets sheets1 = new Sheets(); Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" }; Sheet sheet2 = new Sheet() { Name = "Sheet2", SheetId = (UInt32Value)2U, Id = "rId2" }; sheets1.Append(sheet1); sheets1.Append(sheet2); PivotCaches pivotCaches1 = new PivotCaches(); PivotCache pivotCache1 = new PivotCache() { CacheId = (UInt32Value)2U, Id = "rId4" }; pivotCaches1.Append(pivotCache1); workbook1.Append(sheets1); workbook1.Append(pivotCaches1); workbookPart1.Workbook = workbook1; }
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); } }
// Generates content of workbookPart1. private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1) { Workbook workbook1 = new Workbook(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x15" } }; workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006"); workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main"); FileVersion fileVersion1 = new FileVersion(){ ApplicationName = "xl", LastEdited = "6", LowestEdited = "6", BuildVersion = "14420" }; WorkbookProperties workbookProperties1 = new WorkbookProperties(); AlternateContent alternateContent1 = new AlternateContent(); alternateContent1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006"); AlternateContentChoice alternateContentChoice1 = new AlternateContentChoice(){ Requires = "x15" }; X15ac.AbsolutePath absolutePath1 = new X15ac.AbsolutePath(){ Url = "D:\\Users\\dito\\Desktop\\TestDocumentResaver\\OpenXmlApiConversion\\Timeline\\" }; absolutePath1.AddNamespaceDeclaration("x15ac", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac"); alternateContentChoice1.Append(absolutePath1); alternateContent1.Append(alternateContentChoice1); BookViews bookViews1 = new BookViews(); WorkbookView workbookView1 = new WorkbookView(){ XWindow = 0, YWindow = 0, WindowWidth = (UInt32Value)26940U, WindowHeight = (UInt32Value)15120U }; bookViews1.Append(workbookView1); Sheets sheets1 = new Sheets(); Sheet sheet1 = new Sheet(){ Name = "data", SheetId = (UInt32Value)1U, Id = "rId1" }; Sheet sheet2 = new Sheet(){ Name = "data2", SheetId = (UInt32Value)3U, Id = "rId2" }; Sheet sheet3 = new Sheet(){ Name = "Cache", SheetId = (UInt32Value)4U, Id = "rId3" }; Sheet sheet4 = new Sheet(){ Name = "Level", SheetId = (UInt32Value)2U, Id = "rId4" }; Sheet sheet5 = new Sheet(){ Name = "Caption", SheetId = (UInt32Value)5U, Id = "rId5" }; Sheet sheet6 = new Sheet(){ Name = "ShowHeader", SheetId = (UInt32Value)6U, Id = "rId6" }; Sheet sheet7 = new Sheet(){ Name = "ShowSelectionLabel", SheetId = (UInt32Value)7U, Id = "rId7" }; Sheet sheet8 = new Sheet(){ Name = "ShowTimeLevel", SheetId = (UInt32Value)8U, Id = "rId8" }; Sheet sheet9 = new Sheet(){ Name = "ShowHorizontalScrollbar", SheetId = (UInt32Value)9U, Id = "rId9" }; Sheet sheet10 = new Sheet(){ Name = "ScrollPosition", SheetId = (UInt32Value)10U, Id = "rId10" }; Sheet sheet11 = new Sheet(){ Name = "Style", SheetId = (UInt32Value)11U, Id = "rId11" }; sheets1.Append(sheet1); sheets1.Append(sheet2); sheets1.Append(sheet3); sheets1.Append(sheet4); sheets1.Append(sheet5); sheets1.Append(sheet6); sheets1.Append(sheet7); sheets1.Append(sheet8); sheets1.Append(sheet9); sheets1.Append(sheet10); sheets1.Append(sheet11); DefinedNames definedNames1 = new DefinedNames(); DefinedName definedName1 = new DefinedName(){ Name = "NativeTimeline_Date" }; definedName1.Text = "#N/A"; DefinedName definedName2 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate" }; definedName2.Text = "#N/A"; DefinedName definedName3 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate1" }; definedName3.Text = "#N/A"; DefinedName definedName4 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate2" }; definedName4.Text = "#N/A"; DefinedName definedName5 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate3" }; definedName5.Text = "#N/A"; DefinedName definedName6 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate4" }; definedName6.Text = "#N/A"; DefinedName definedName7 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate5" }; definedName7.Text = "#N/A"; DefinedName definedName8 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate6" }; definedName8.Text = "#N/A"; DefinedName definedName9 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate7" }; definedName9.Text = "#N/A"; definedNames1.Append(definedName1); definedNames1.Append(definedName2); definedNames1.Append(definedName3); definedNames1.Append(definedName4); definedNames1.Append(definedName5); definedNames1.Append(definedName6); definedNames1.Append(definedName7); definedNames1.Append(definedName8); definedNames1.Append(definedName9); CalculationProperties calculationProperties1 = new CalculationProperties(){ CalculationId = (UInt32Value)152511U }; PivotCaches pivotCaches1 = new PivotCaches(); PivotCache pivotCache1 = new PivotCache(){ CacheId = (UInt32Value)0U, Id = "rId12" }; PivotCache pivotCache2 = new PivotCache(){ CacheId = (UInt32Value)1U, Id = "rId13" }; pivotCaches1.Append(pivotCache1); pivotCaches1.Append(pivotCache2); WorkbookExtensionList workbookExtensionList1 = new WorkbookExtensionList(); WorkbookExtension workbookExtension1 = new WorkbookExtension(){ Uri = "{79F54976-1DA5-4618-B147-4CDE4B953A38}" }; workbookExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"); X14.WorkbookProperties workbookProperties2 = new X14.WorkbookProperties(); workbookExtension1.Append(workbookProperties2); WorkbookExtension workbookExtension2 = new WorkbookExtension(){ Uri = "{D0CA8CA8-9F24-4464-BF8E-62219DCF47F9}" }; workbookExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main"); X15.TimelineCacheReferences timelineCacheReferences1 = new X15.TimelineCacheReferences(); X15.TimelineCacheReference timelineCacheReference1 = new X15.TimelineCacheReference(){ Id = "rId14" }; X15.TimelineCacheReference timelineCacheReference2 = new X15.TimelineCacheReference(){ Id = "rId15" }; X15.TimelineCacheReference timelineCacheReference3 = new X15.TimelineCacheReference(){ Id = "rId16" }; X15.TimelineCacheReference timelineCacheReference4 = new X15.TimelineCacheReference(){ Id = "rId17" }; X15.TimelineCacheReference timelineCacheReference5 = new X15.TimelineCacheReference(){ Id = "rId18" }; X15.TimelineCacheReference timelineCacheReference6 = new X15.TimelineCacheReference(){ Id = "rId19" }; X15.TimelineCacheReference timelineCacheReference7 = new X15.TimelineCacheReference(){ Id = "rId20" }; X15.TimelineCacheReference timelineCacheReference8 = new X15.TimelineCacheReference(){ Id = "rId21" }; X15.TimelineCacheReference timelineCacheReference9 = new X15.TimelineCacheReference(){ Id = "rId22" }; timelineCacheReferences1.Append(timelineCacheReference1); timelineCacheReferences1.Append(timelineCacheReference2); timelineCacheReferences1.Append(timelineCacheReference3); timelineCacheReferences1.Append(timelineCacheReference4); timelineCacheReferences1.Append(timelineCacheReference5); timelineCacheReferences1.Append(timelineCacheReference6); timelineCacheReferences1.Append(timelineCacheReference7); timelineCacheReferences1.Append(timelineCacheReference8); timelineCacheReferences1.Append(timelineCacheReference9); workbookExtension2.Append(timelineCacheReferences1); WorkbookExtension workbookExtension3 = new WorkbookExtension(){ Uri = "{140A7094-0E35-4892-8432-C4D2E57EDEB5}" }; workbookExtension3.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main"); X15.WorkbookProperties workbookProperties3 = new X15.WorkbookProperties(){ ChartTrackingReferenceBase = true }; workbookExtension3.Append(workbookProperties3); workbookExtensionList1.Append(workbookExtension1); workbookExtensionList1.Append(workbookExtension2); workbookExtensionList1.Append(workbookExtension3); workbook1.Append(fileVersion1); workbook1.Append(workbookProperties1); workbook1.Append(alternateContent1); workbook1.Append(bookViews1); workbook1.Append(sheets1); workbook1.Append(definedNames1); workbook1.Append(calculationProperties1); workbook1.Append(pivotCaches1); workbook1.Append(workbookExtensionList1); workbookPart1.Workbook = workbook1; }
// Generates content of workbookPart1. private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1) { Workbook workbook1 = new Workbook(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x15" } }; workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006"); workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main"); FileVersion fileVersion1 = new FileVersion(){ ApplicationName = "xl", LastEdited = "6", LowestEdited = "6", BuildVersion = "14420" }; WorkbookProperties workbookProperties1 = new WorkbookProperties(){ CodeName = "ThisWorkbook", DefaultThemeVersion = (UInt32Value)153222U }; AlternateContent alternateContent1 = new AlternateContent(); alternateContent1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006"); AlternateContentChoice alternateContentChoice1 = new AlternateContentChoice(){ Requires = "x15" }; X15ac.AbsolutePath absolutePath1 = new X15ac.AbsolutePath(){ Url = "D:\\Users\\dito\\Desktop\\TestDocumentResaver\\OpenXmlApiConversion\\Pivot\\" }; absolutePath1.AddNamespaceDeclaration("x15ac", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac"); alternateContentChoice1.Append(absolutePath1); alternateContent1.Append(alternateContentChoice1); BookViews bookViews1 = new BookViews(); WorkbookView workbookView1 = new WorkbookView(){ XWindow = 0, YWindow = 0, WindowWidth = (UInt32Value)26940U, WindowHeight = (UInt32Value)15120U }; bookViews1.Append(workbookView1); Sheets sheets1 = new Sheets(); Sheet sheet1 = new Sheet(){ Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" }; sheets1.Append(sheet1); DefinedNames definedNames1 = new DefinedNames(); DefinedName definedName1 = new DefinedName(){ Name = "Query", LocalSheetId = (UInt32Value)0U, Hidden = true }; definedName1.Text = "Sheet1!$B$2:$I$13"; definedNames1.Append(definedName1); CalculationProperties calculationProperties1 = new CalculationProperties(){ CalculationId = (UInt32Value)152511U }; PivotCaches pivotCaches1 = new PivotCaches(); PivotCache pivotCache1 = new PivotCache(){ CacheId = (UInt32Value)0U, Id = "rId2" }; pivotCaches1.Append(pivotCache1); WorkbookExtensionList workbookExtensionList1 = new WorkbookExtensionList(); WorkbookExtension workbookExtension1 = new WorkbookExtension(){ Uri = "{FCE2AD5D-F65C-4FA6-A056-5C36A1767C68}" }; workbookExtension1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main"); X15.DataModel dataModel1 = new X15.DataModel(); X15.ModelTables modelTables1 = new X15.ModelTables(); X15.ModelTable modelTable1 = new X15.ModelTable(){ Id = "Query_7c41ad89-7105-4c9f-ab5a-881bd3e6a1b9", Name = "Query", Connection = "DAT105 Timestamp - Foodmart 2000 account" }; modelTables1.Append(modelTable1); dataModel1.Append(modelTables1); workbookExtension1.Append(dataModel1); WorkbookExtension workbookExtension2 = new WorkbookExtension(){ Uri = "{69C81A23-63F3-4edf-8378-127667AE99B5}" }; OpenXmlUnknownElement openXmlUnknownElement1 = OpenXmlUnknownElement.CreateOpenXmlUnknownElement("<x15:workbookPr15 chartTrackingRefBase=\"1\" xmlns:x15=\"http://schemas.microsoft.com/office/spreadsheetml/2010/11/main\" />"); workbookExtension2.Append(openXmlUnknownElement1); workbookExtensionList1.Append(workbookExtension1); workbookExtensionList1.Append(workbookExtension2); workbook1.Append(fileVersion1); workbook1.Append(workbookProperties1); workbook1.Append(alternateContent1); workbook1.Append(bookViews1); workbook1.Append(sheets1); workbook1.Append(definedNames1); workbook1.Append(calculationProperties1); workbook1.Append(pivotCaches1); workbook1.Append(workbookExtensionList1); workbookPart1.Workbook = workbook1; }
/// <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(); } }