how do i change row label and colum label from default value “Row Label” & “Column Label” to any string value and also changing the font type and color of the pivot
Hi,
For setting font and other things of the PivotTable , kindly refer to this document: Formatting Pivot Table
For your Row Label and Column Label question, please create a sample input and output workbooks manually using Ms-Excel and attach them here. We will see how to achieve such a thing programmatically using Aspose.Cells .
i want to change spirit.xlsx to signoffreport.xlsx.
the "row labels" in the oil sheet to "Activity Entity" and "colum label" to "Year" in oil sheet in signoff report.xlsx and also the formatting of the oil sheet in spirit.xlsx to format of signoffreport.xlsx
simply , the spirit.xlsx is the input and signoffreport.xlxs is the output. thanks
Hi,
Thanks for your help and files.
Please see the code below. I have changed the Row Label and Column Label to ACTIVITY ENTITY and YEAR respectively.
Also, I have auto format the pivot table. Kindly see the output xlsx file generated by the following code and the screenshot.
C#
string path = @“F:\Shak-Data-RW\Downloads\spirit1.xlsx”;
Workbook workbook = new Workbook(path);
Worksheet worksheet = workbook.Worksheets[“GAS”];
PivotTable pvtTable = worksheet.PivotTables[0];
pvtTable.RowHeaderCaption = “ACTIVITY ENTITY”;
pvtTable.ColumnHeaderCaption = “YEAR”;
pvtTable.IsAutoFormat = true;
pvtTable.PivotTableStyleType = PivotTableStyleType.PivotTableStyleDark2;
pvtTable.ShowPivotStyleColumnHeader = true;
pvtTable.ShowPivotStyleColumnStripes = false;
pvtTable.ShowPivotStyleLastColumn = true;
pvtTable.ShowPivotStyleRowHeader = true;
pvtTable.ShowPivotStyleRowStripes = true;
pvtTable.ShowRowHeaderCaption = true;
pvtTable.SubtotalHiddenPageItems = true;
workbook.Save(path + “.out.xlsx”, SaveFormat.Xlsx);
Thanks very much Man. it is working now.
thanks you very much ,it is working now
am having an error
Error 1 'ColumnHeaderCaption' is not a member of 'Aspose.Cells.Pivot.PivotTable'. C:\Users\Augustine.Ugochinyer\Desktop\spirit\FBIReport2\FBIReport\Class1.vb 1141 9 FBIReport
these is my vb code
function GetpivotReport() as workbook
dim wrk as new workbook
Dim BoeINDEX As Integer = wrk.Worksheets.Add()
Dim Boewrksht As Worksheet = wrk.Worksheets(BoeINDEX)
Boewrksht.Name = "BoeData"
' Boeds.Tables(0).TableName = "FBI_Boe_VOLUME_BOPD"
Boewrksht.Cells.ImportDataTable(olapds.Tables(0), True, "A1")
Dim Boeratepivotsheetindex As Integer = wrk.Worksheets.Add()
Dim Boeratepivotsheet As Worksheet = wrk.Worksheets(Boeratepivotsheetindex)
Boeratepivotsheet.Name = "Boe"
Dim BoeratePivots As Pivot.PivotTableCollection = Boeratepivotsheet.PivotTables
Dim BoepivotSourcedata As String = "=BoeData!A1:M" & Boewrksht.Cells.MaxDataRow
'MsgBox(BoepivotSourcedata)
Dim Boeratepivotindex As Integer = BoeratePivots.Add(BoepivotSourcedata, "A1", "BoeRatePivot")
Dim BoeratePivot As Pivot.PivotTable = BoeratePivots(Boeratepivotindex)
BoeratePivot.AutoFormatType = Pivot.PivotTableAutoFormatType.Table10
BoeratePivot.AddFieldToArea(Pivot.PivotFieldType.Row, 0)
BoeratePivot.AddFieldToArea(Pivot.PivotFieldType.Column, 2)
'pdg
BoeratePivot.AddFieldToArea(Pivot.PivotFieldType.Data, 10)
'dg1
BoeratePivot.AddFieldToArea(Pivot.PivotFieldType.Data, 9)
'dg2
BoeratePivot.AddFieldToArea(Pivot.PivotFieldType.Data, 8)
'dg3
BoeratePivot.AddFieldToArea(Pivot.PivotFieldType.Data, 7)
'2puda
BoeratePivot.AddFieldToArea(Pivot.PivotFieldType.Data, 6)
'2pdra
BoeratePivot.AddFieldToArea(Pivot.PivotFieldType.Data, 5)
'puda
BoeratePivot.AddFieldToArea(Pivot.PivotFieldType.Data, 4)
'pdra
BoeratePivot.AddFieldToArea(Pivot.PivotFieldType.Data, 3)
' BoeratePivot.DataField.DragToColumn = True
BoeratePivot.AddFieldToArea(Pivot.PivotFieldType.Column, BoeratePivot.DataField)
BoeratePivot.DataFields(0).Function = ConsolidationFunction.Sum
BoeratePivot.DataFields(1).Function = ConsolidationFunction.Sum
BoeratePivot.DataFields(2).Function = ConsolidationFunction.Sum
BoeratePivot.DataFields(3).Function = ConsolidationFunction.Sum
BoeratePivot.DataFields(4).Function = ConsolidationFunction.Sum
BoeratePivot.DataFields(5).Function = ConsolidationFunction.Sum
BoeratePivot.DataFields(6).Function = ConsolidationFunction.Sum
BoeratePivot.DataFields(7).Function = ConsolidationFunction.Sum
BoeratePivot.AutoFormatType = Pivot.PivotTableAutoFormatType.Table10
BoeratePivot.DataFields(0).DisplayName = "PDG"
BoeratePivot.DataFields(1).DisplayName = "DG1"
BoeratePivot.DataFields(2).DisplayName = "DG2"
BoeratePivot.DataFields(3).DisplayName = "DG3"
BoeratePivot.DataFields(4).DisplayName = "2PUDA"
BoeratePivot.DataFields(5).DisplayName = "2PDRA"
BoeratePivot.DataFields(6).DisplayName = "PUDA"
BoeratePivot.DataFields(7).DisplayName = "PDRA"
Dim BoerateppsSortfields As Pivot.PivotFieldCollection = BoeratePivot.RowFields
Dim BoePPsSort As Pivot.PivotField = BoerateppsSortfields(0)
BoePPsSort.IsAutoSort = True
BoePPsSort.IsAscendSort = True
BoePPsSort.AutoSortField = -1
Dim BoeratecolSortfields As Pivot.PivotFieldCollection = BoeratePivot.ColumnFields
Dim BoecolSort As Pivot.PivotField = BoeratecolSortfields(0)
BoecolSort.IsAutoSort = True
BoecolSort.IsAscendSort = True
BoecolSort.AutoSortField = -1
BoeratePivot.CalculateData()
BoeratePivot.ShowPivotStyleColumnHeader = False
BoeratePivot.EnableFieldDialog = False
BoeratePivot.EnableFieldList = False
BoeratePivot.EnableWizard = False
BoeratePivot.IsAutoFormat = True
BoeratePivot.ColumnGrand = True
BoeratePivot.RowGrand = False
Boewrksht.IsVisible = False
BoeratePivot.RowHeaderCaption = Reportgroup
BoeratePivot.ColumnHeaderCaption = "YEAR"
return wrk
End function
all the others are working fine except the columheadercaption
Hi,
I think, you should download the latest version because it is working fine.
Please download: Aspose.Cells for .NET (Latest Version)
In case your issue is still not resolved, let me know please.