javascript - Excel script for generating pivot tables without subtotals - Stack Overflow

admin管理员组

文章数量:1292149

I have a script that creates a pivot table, but I don't want to display subtotals, only final totals.

function main(workbook: ExcelScript.Workbook) {
    let sheet = workbook.getActiveWorksheet(); // Downloading the active sheet

    // Finding the range with the data (I assume it starts with A1)
    let dataRange = sheet.getRange("A1").getSurroundingRegion();

    // Checking that the scope is correct
    if (!dataRange) {
        console.log("No data found.");
        return;
    }

    // Creating a pivot table on the same sheet
    let pivotTable = sheet.addPivotTable("February", dataRange, sheet.getRange("H1"));
    
    let rowHierarchy = pivotTable.addRowHierarchy(pivotTable.getHierarchy("Posting date"));

    // Pivot table configuration
    pivotTable.addColumnHierarchy(pivotTable.getHierarchy("Project no."));
    pivotTable.addColumnHierarchy(pivotTable.getHierarchy("Name"));
    pivotTable.addColumnHierarchy(pivotTable.getHierarchy("Task No."));


    // Dodanie wartości (Ilość)
    let qtyHierarchy = pivotTable.getHierarchy("Time");
    let dataField = pivotTable.addDataHierarchy(qtyHierarchy);
    dataField.setSummarizeBy(ExcelScript.AggregationFunction.sum);
    // Hiding subtotals for "Project No." and "Name"

    // Alternate formatting (lines)
    let pivotRange = pivotTable.getLayout().getRange();
    let rowCount = pivotRange.getRowCount();

    for (let i = 1; i < rowCount; i += 2) { // Every second line
        let row = pivotRange.getCell(i, 0).getEntireRow();
        row.getFormat().getFill(); 
    }

    console.log("The pivot table has been created.");
}

I would like the grand total not to be shown and I would like the column and row lines to be shown.

I have a script that creates a pivot table, but I don't want to display subtotals, only final totals.

function main(workbook: ExcelScript.Workbook) {
    let sheet = workbook.getActiveWorksheet(); // Downloading the active sheet

    // Finding the range with the data (I assume it starts with A1)
    let dataRange = sheet.getRange("A1").getSurroundingRegion();

    // Checking that the scope is correct
    if (!dataRange) {
        console.log("No data found.");
        return;
    }

    // Creating a pivot table on the same sheet
    let pivotTable = sheet.addPivotTable("February", dataRange, sheet.getRange("H1"));
    
    let rowHierarchy = pivotTable.addRowHierarchy(pivotTable.getHierarchy("Posting date"));

    // Pivot table configuration
    pivotTable.addColumnHierarchy(pivotTable.getHierarchy("Project no."));
    pivotTable.addColumnHierarchy(pivotTable.getHierarchy("Name"));
    pivotTable.addColumnHierarchy(pivotTable.getHierarchy("Task No."));


    // Dodanie wartości (Ilość)
    let qtyHierarchy = pivotTable.getHierarchy("Time");
    let dataField = pivotTable.addDataHierarchy(qtyHierarchy);
    dataField.setSummarizeBy(ExcelScript.AggregationFunction.sum);
    // Hiding subtotals for "Project No." and "Name"

    // Alternate formatting (lines)
    let pivotRange = pivotTable.getLayout().getRange();
    let rowCount = pivotRange.getRowCount();

    for (let i = 1; i < rowCount; i += 2) { // Every second line
        let row = pivotRange.getCell(i, 0).getEntireRow();
        row.getFormat().getFill(); 
    }

    console.log("The pivot table has been created.");
}

I would like the grand total not to be shown and I would like the column and row lines to be shown.

• javascript
• excel
• ms-office

Share Follow asked Feb 13 at 9:58 LucasLucas 311 bronze badge

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

The methods for setting and hiding grand totals are exposed via the PivotLayout interface.

pivotTable.getLayout().setShowRowGrandTotals(false)
pivotTable.getLayout().setShowColumnGrandTotals(false)

PivotTable interface, getLayout() method

PivotLayout interface, setShowRowGrandTotals method

本文标签： javascriptExcel script for generating pivot tables without subtotalsStack Overflow