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();
}
}
