Apply format to valueField column in a pivot table

Apply format to valueField column in a pivot table

Posted by: Fabrice.Mainguene on 12 October 2021, 8:57 am EST

• 

  Fabrice.Mainguene

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 12 October 2021, 8:57 am EST

  Hi,

  I create a pivot table with this code:

  Sheet2.tables.addFromDataSource('Table1',0,0,data,GC.Spread.Sheets.Tables.TableThemes.light10); 
  
  pivotTable = sheet1.pivotTables.add("PivotTable", "Table1", 0, 0, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.medium1);
  pivotTable.suspendLayout(); 
  
  pivotTable.add('department', 'Department', GC.Spread.Pivot.PivotTableFieldType.rowField); 
  pivotTable.add('agency', 'Agency', GC.Spread.Pivot.PivotTableFieldType.rowField); 
  pivotTable.add('prices', 'Prices', GC.Spread.Pivot.PivotTableFieldType.valueField,GC.Pivot.SubtotalType.sum);
  pivotTable.add('distance', 'Distance', GC.Spread.Pivot.PivotTableFieldType.valueField,GC.Pivot.SubtotalType.sum);
  
  pivotTable.resumeLayout(); 
  pivotTable.autoFitColumn(); 
  

  I want to format my “Prices” colunm with “### ### €”, but I don’t find the way to add a style on a specific valuefield column by programming. Is there a solution?

• 

  avinash.pathak

  • Post Options:
  • Link
    Copy

  Posted 13 October 2021, 7:50 am EST

  Hi,

  For this, you may use the setStyle method. Please refer to the following code snippet and let us know if you face any issues.

  
  var pivotTable = sheet.pivotTables.add("pivotTable1", 'sourceData', 1, 1, layout, theme, options);
  pivotTable.add("Buyer", "Buyer", GC.Spread.Pivot.PivotTableFieldType.rowField);
  pivotTable.add("Type", "Type", GC.Spread.Pivot.PivotTableFieldType.rowField);
  pivotTable.add("Date", "Date", GC.Spread.Pivot.PivotTableFieldType.columnField);
  pivotTable.add("Amount", "Amount", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
  var pivotArea = {
      dataOnly: false,
      references: [
          {
              fieldName: "Buyer",
          }
      ]
  };
  var style = new GC.Spread.Sheets.Style();
  style.formatter= '##,###';
  pivotTable.setStyle(pivotArea, style);
  
  

  SetStyle: https://www.grapecity.com/spreadjs/docs/v14/online/SpreadJS~GC.Spread.Pivot.PivotTable~setStyle.html

  Regards
  
  Avinash

• 

  Fabrice.Mainguene

  • Post Options:
  • Link
    Copy

  Posted 21 February 2022, 9:37 am EST

  Hi!

  Thanks for your answer but unfortunatly your example show how to apply a style on a GC.Spread.Pivot.PivotTableFieldType.rowField and not on a GC.Spread.Pivot.PivotTableFieldType.valueField.
  
  I can’t make it work with GC.Spread.Pivot.PivotTableFieldType.valueField.

• 

  avinash.pathak

  • Post Options:
  • Link
    Copy

  Posted 22 February 2022, 11:42 pm EST

  Hi,

  For this, you may need to define the Field Items. Please refer to the following code snippet and attached sample that explains the same.

  
      let valuefield = {
          references: [
              {
                  fieldName: "Salesperson",
              },
              {
                  fieldName: "Cars",
              },
              {
                  fieldName: "Qt",
                  items: ["Qtr1", "Qtr2", "Qtr3", "Qtr4"]
              }
          ]
      };
      pivotTable.setStyle(valuefield, style);
  
  

  YOu may also use following code snippet to get the picot area and modify it to achieve your requirement.

  
  sheet.pivotTables.getRangePivotAreas(new GC.Spread.Sheets.Range(4,3,5,4)) 
  
  

  Regards,
  
  Avinash

  sample.zip