Hi Aspose Support team,
Please find the attached sample xlsx file and the below sample code for your kind reference. Please help me ASAP. This is an very urgent for me.
Sample code:
-------------
PivotTableCollection pivotTables = obj.ExcelWorkBook.Worksheets["Pivot"].PivotTables;
string pivotDataSourceArea = "DATA!A8:" + obj.ExcelWorkBook.Worksheets["DATA"].Cells[dtDetails.Rows.Count + 7, dtDetails.Columns.Count - 1].Name;
string startingCell = "A36";
string pivotTableName = "PivotTable";
int index = pivotTables.Add(pivotDataSourceArea, startingCell, pivotTableName);
PivotTable pivotTable = pivotTables[index];
pivotTable.RowFields.AddByBaseIndex(dtDetails.Columns["Terminal/Berth - First line ashore"].Ordinal);
pivotTable.RowFields.AddByBaseIndex(dtDetails.Columns["PortLog - Terminal/Berth/ETA"].Ordinal);
pivotTable.RowFields.AddByBaseIndex(dtDetails.Columns["Cargo Activity"].Ordinal);
pivotTable.RowFields.AddByBaseIndex(dtDetails.Columns["PortLog - Cargo No"].Ordinal);
pivotTable.RowFields.AddByBaseIndex(dtDetails.Columns["PortLog - Trade Name"].Ordinal);
pivotTable.DataFields.AddByBaseIndex(dtDetails.Columns["All Fast"].Ordinal);
pivotTable.DataFields.AddByBaseIndex(dtDetails.Columns["All Fast to hose connected"].Ordinal);
pivotTable.DataFields.AddByBaseIndex(dtDetails.Columns["Hose connected to cargo ops commence"].Ordinal);
pivotTable.DataFields.AddByBaseIndex(dtDetails.Columns["Cargo Ops"].Ordinal);
pivotTable.DataFields.AddByBaseIndex(dtDetails.Columns["Cargo Ops to hose disconnect"].Ordinal);
pivotTable.DataFields.AddByBaseIndex(dtDetails.Columns["Hose disconnected to all lines on deck"].Ordinal);
PivotField pivotDataField1 = pivotTable.DataFields[0];
pivotDataField1.Function = ConsolidationFunction.Min;
pivotDataField1.NumberFormat = "dd-mmm-yy hh:mm";
PivotField pivotDataField2 = pivotTable.DataFields[1];
pivotDataField2.Function = ConsolidationFunction.Average;
pivotDataField2.NumberFormat = "[hh]:mm";
PivotField pivotDataField3 = pivotTable.DataFields[2];
pivotDataField3.Function = ConsolidationFunction.Average;
pivotDataField3.NumberFormat = "[hh]:mm";
PivotField pivotDataField4 = pivotTable.DataFields[3];
pivotDataField4.Function = ConsolidationFunction.Average;
pivotDataField4.NumberFormat = "[hh]:mm";
PivotField pivotDataField5 = pivotTable.DataFields[4];
pivotDataField5.Function = ConsolidationFunction.Average;
pivotDataField5.NumberFormat = "[hh]:mm";
PivotField pivotDataField6 = pivotTable.DataFields[5];
pivotDataField6.Function = ConsolidationFunction.Average;
pivotDataField6.NumberFormat = "[hh]:mm";
pivotTable.AddFieldToArea(PivotFieldType.Column, pivotTable.DataField);
pivotTable.ColumnGrand = false;
Style DataStyle = obj.ExcelWorkBook.CreateStyle();
DataStyle.Font.Size = 8;
DataStyle.Font.Name = "Arial";
DataStyle.Font.Color = Color.Black;
DataStyle.RotationAngle = 90;
pivotTable.AutoFormatType = PivotTableAutoFormatType.Classic;
pivotTable.DataFields[0].DisplayName = "All Fast ";
pivotTable.DataFields[1].DisplayName = "All fast to hose connected";
pivotTable.DataFields[2].DisplayName = "Hose conn - cargo ops commence";
pivotTable.DataFields[3].DisplayName = "Cargo Ops";
pivotTable.DataFields[4].DisplayName = "Cargo Ops complete - hose disconnect";
pivotTable.DataFields[5].DisplayName = "Hose disconnected to all lines on deck";
pivotTable.PageFields.AddByBaseIndex(dtDetails.Columns["Vessel Name"].Ordinal);
pivotTable.PageFields.AddByBaseIndex(dtDetails.Columns["Cargo details-Voyage No"].Ordinal);
pivotTable.PageFields.AddByBaseIndex(dtDetails.Columns["Port Name"].Ordinal);
pivotTable.PageFields.AddByBaseIndex(dtDetails.Columns["Terminal"].Ordinal);
pivotTable.PageFields.AddByBaseIndex(dtDetails.Columns["Berth"].Ordinal);
pivotTable.RowFields[0].IsAutoSort = true;
pivotTable.RowFields[1].IsAutoSort = true;
pivotTable.RowFields[2].IsAutoSort = true;
pivotTable.PageFields[0].IsAutoSort = true;
pivotTable.PageFields[1].IsAutoSort = true;
pivotTable.PageFields[2].IsAutoSort = true;
pivotTable.PageFields[3].IsAutoSort = true;
pivotTable.PageFields[4].IsAutoSort = true;
obj.ExcelWorkBook.Worksheets["Pivot"].PivotTables[0].RowFields[0].IsAutoSubtotals = false;
obj.ExcelWorkBook.Worksheets["Pivot"].PivotTables[0].RowFields[1].IsAutoSubtotals = false;
obj.ExcelWorkBook.Worksheets["Pivot"].PivotTables[0].RowFields[2].IsAutoSubtotals = false;
obj.ExcelWorkBook.Worksheets["Pivot"].PivotTables[0].RowFields[3].IsAutoSubtotals = false;
obj.ExcelWorkBook.Worksheets["Pivot"].PivotTables[0].RowFields[0].ShowInOutlineForm = false;
obj.ExcelWorkBook.Worksheets["Pivot"].PivotTables[0].ShowRowHeaderCaption = false;
obj.ExcelWorkBook.Worksheets["Pivot"].PivotTables[0].RowFields[0].NumberFormat = "m/d/yyyy h:mm";
obj.ExcelWorkBook.Worksheets["Pivot"].PivotTables[0].RowFields[0].ShowInOutlineForm = false;
obj.ExcelWorkBook.Worksheets["Pivot"].PivotTables[0].ShowRowHeaderCaption = false;
obj.ExcelWorkBook.Worksheets["Pivot"].Charts.Add(ChartType.BarStacked, 2, 0, 25, 10);
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].PivotSource = "Pivot!PivotTable";
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].ChartArea.Area.ForegroundColor = Color.White;
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].PlotArea.Area.ForegroundColor = Color.White;
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].Title.Text = "";
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].ShowLegend = true;
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].Legend.Position = LegendPositionType.Top;
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].ValueAxis.TickLabels.Rotation = 45;
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].ValueAxis.TickLabels.Font.IsBold = false;
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].ValueAxis.TickLabelPosition = TickLabelPositionType.NextToAxis;
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].ValueAxis.MajorTickMark = TickMarkType.Outside;
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].ValueAxis.MinorTickMark = TickMarkType.None;
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].CategoryAxis.IsPlotOrderReversed = true;
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].CategoryAxis.HasMultiLevelLabels = true;
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].CategoryAxis.MajorTickMark = TickMarkType.Inside;
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].CategoryAxis.MinorTickMark = TickMarkType.None;
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].CategoryAxis.TickLabelPosition = TickLabelPositionType.NextToAxis;