Free Support Forum - aspose.com

How to change row labels and colum labels in pivot table

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


Screenshot:

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.