Pivot Table Defaults to COUNT instead of SUM with Headers in Data Range

Aspose.Cells Product Family
1

Hi Aspose Team,
We are using Aspose.Cells for Node.js via Java to generate Excel reports with pivot tables.

Our Goal:
We need to create a pivot table that summarizes an “Amount” column from a source data sheet using SUM.

Our Data Structure:
Our source data sheet (InvoiceData) contains transaction details. For readability, we include column headers twice: once at the top for “Current Period” data, and again lower down before the “Prior Period” data starts.

The Problem:
When we create a pivot table using the entire data range (which includes both sets of headers), the pivot table field for “Amount” defaults its calculation to COUNT instead of SUM. We believe this happens because the second row of text headers breaks the numeric continuity of the “Amount” column in the source range.

What We’ve Tried:

We are aware that we can get the pivot table field after creation and force the calculation using:
field.setFunction(aspose.cells.ConsolidationFunction.SUM);
pivotTable.refreshData();
pivotTable.calculateData();

While this does update the displayed value, the initial default is still COUNT, and we are concerned this workaround might mask underlying issues or not be the most reliable approach.

Our Question: Is there a recommended way in Aspose.Cells to handle this scenario? Specifically:

  1. Can we define a pivot table source range that correctly recognizes the numeric data even with text headers interspersed (perhaps by defining multiple ranges or skipping specific rows)?

  2. Or, is forcing the SUM function after creation the intended way to override the default when dealing with such mixed data types in the source range?

We want to keep the second header row in our InvoiceData sheet for user readability, but ensure the pivot table reliably calculates the SUM for the Amount field by default if possible, or through the correct post-creation method.
I have also applied the whole code below
Could you please advise on the best practice for this situation using Aspose.Cells?

Thank you!

export const generateBillingBackupSummary = async (
invoiceTransactionData: InvoiceTransactionModel,
customer: Customer
): Promise => {
const logTag = ‘[generateBillingBackupSummary]’;
console.time(${logTag} - Execution Time);
try {
console.log(logTag, ‘Start’);
// console.log(logTag, ‘invoiceTransactionData’, JSON.stringify(invoiceTransactionData));
console.log(logTag, 'Customer ', JSON.stringify(customer));

const sampleWorkbook = new aspose.cells.Workbook();
sampleWorkbook
  .getSettings()
  .setMemorySetting(aspose.cells.MemorySetting.MEMORY_PREFERENCE);

const worksheet = sampleWorkbook.getWorksheets().get(0);

try {
  const cells = worksheet.getCells();
  // Set the standard width for all columns
  cells.setStandardWidth(15);
} catch (error) {
  console.log('Error Setting Default Width');
}

let lastDataRow = 0;

if (invoiceTransactionData.transactions) {
  console.time(`${logTag} - Sorting Transactions`);

  invoiceTransactionData.transactions.sort((a, b) => {
    // Parse the date strings into Date objects
    const dateA = a?.eventDate ? new Date(a.eventDate) : new Date(0);
    const dateB = b?.eventDate ? new Date(b.eventDate) : new Date(0);

    // Sort by date in descending order (newest to oldest)
    const dateDiff = dateA.getTime() - dateB.getTime();
    if (dateDiff !== 0) {
      return dateDiff;
    }

    // If dates are equal, sort by schoolId in descending order
    const schoolA = a?.schoolId ?? '';
    const schoolB = b?.schoolId ?? '';
    if (schoolA !== schoolB) {
      return schoolA.localeCompare(schoolB);
    }

    // If schoolIds are equal, sort by essEmplIdentifier in descending order
    const subA = a?.essEmplIdentifier || '';
    const subB = b?.essEmplIdentifier || '';
    return subA.localeCompare(subB);
  });

  console.timeEnd(`${logTag} - Sorting Transactions`);
}

console.time(`${logTag} - Billing Backup Fields Transaction Grouping`);
if (customer.customerConfigurationBackupFields.length > 0) {
  customer.customerConfigurationBackupFields.sort(
    (a, b) => a.order - b.order
  );
}

console.log(
  logTag,
  'Customer configuration billing back up',
  JSON.stringify(customer.customerConfigurationBackupFields)
);

const invoiceData: InvoiceTransactionModel[] =
  invoiceTransactionData.type === DOCUMENT_TYPES.INVOICE_TYPE
    ? ([invoiceTransactionData] as Invoice[])
    : ([invoiceTransactionData] as CreditMemo[]);
const customerConfigurationBackupFieldsData: CustomerConfigurationBackupField[] =
  customer.customerConfigurationBackupFields;
const customerConfigurationBackupPivotTablesData: CustomerConfigurationBackupPivotTable[] =
  customer.customerConfigurationBackupPivotTables;

console.log(
  logTag,
  'Customer Pivot Table Data',
  JSON.stringify(customerConfigurationBackupPivotTablesData)
);

const processedTransactionInvoiceData: ProcessedTransactionInvoiceDataModel[] =
  invoiceData.reduce(
    (acc, invoice) => [
      ...acc,
      ...getInvoiceTransactionData(
        invoice,
        customerConfigurationBackupFieldsData
      ),
    ],
    [] as ProcessedTransactionInvoiceDataModel[]
  );

console.log(logTag, 'Value returned from getInvoiceTransactionData');

const processedTransactionInvoiceDataKeys: {
  key: string;
  displayName: string;
  order: number | undefined;
}[] = Object.entries(processedTransactionInvoiceData[0]).map(
  ([key, value]) => ({
    key,
    displayName: value.displayName,
    order: value.order,
  })
);

// Sort the keys based on the order property and handle duplicates
processedTransactionInvoiceDataKeys.sort((a, b) => {
  const orderA = a.order !== undefined ? a.order : Infinity; // Treat undefined as the largest value
  const orderB = b.order !== undefined ? b.order : Infinity; // Treat undefined as the largest value

  if (orderA === orderB) {
    return a.key.localeCompare(b.key); // Sort by key if orders are equal
  }
  return orderA - orderB; // Sort by order
});

const currentPeriodData = processedTransactionInvoiceData.filter(
  (row) => row.priorPeriodValue?.value !== 'Yes'
);
const priorPeriodData = processedTransactionInvoiceData.filter(
  (row) => row.priorPeriodValue?.value === 'Yes'
);

console.timeEnd(`${logTag} - Billing Backup Fields Transaction Grouping`);
console.time(`${logTag} - Adding Values to XL Cells`);

// Get billing interval name
const billingIntervalName = getBillingTableName(customer.billingInterval);

const logDebugInfo = () => {
  const logTag = '[DebugInfo]';
  const sampleCell = worksheet.getCells().get(0, 0); // Get a sample cell
  const sampleStyle = sampleCell.getStyle();

  console.log(
    logTag,
    'Cell methods:',
    JSON.stringify(
      Object.getOwnPropertyNames(Object.getPrototypeOf(sampleCell))
    )
  );
  console.log(
    logTag,
    'Style methods:',
    JSON.stringify(
      Object.getOwnPropertyNames(Object.getPrototypeOf(sampleStyle))
    )
  );
  console.log(
    logTag,
    'Font methods:',
    JSON.stringify(
      Object.getOwnPropertyNames(
        Object.getPrototypeOf(sampleStyle.getFont())
      )
    )
  );
};

//logDebugInfo(); // TODO: Uncomment this to view all available cell, style and font methods in aspose

// Function to add headers
const addHeaders = (startRow: number) => {
  processedTransactionInvoiceDataKeys.forEach(({ displayName }, index) => {
    if (!(displayName === 'Prior Period Value')) {
      console.time(`${logTag} - addHeaders - ${displayName}`);
      addValueToCell(worksheet, startRow, index, displayName, {
        isBold: true,
        fontSize: 8,
        underline: 'single',
      });
      console.timeEnd(`${logTag} - addHeaders - ${displayName}`);
    }
  });
};

// Function to add data rows
const addDataRows = (
  data: ProcessedTransactionInvoiceDataModel[],
  startRow: number
) => {
  data.forEach(
    (row: ProcessedTransactionInvoiceDataModel, rowIndex: number) => {
      processedTransactionInvoiceDataKeys.forEach(
        ({ key }, columnIndex) => {
          const isCurrencyValue =
            key === 'billedTotal' || key === 'billedRate';
          const isDate =
            key === 'eventDate' ||
            key === 'reconciliationApprovalDate' ||
            key === 'periodEndDate';
          if (!(key === 'priorPeriodValue')) {
            addValueToCell(
              worksheet,
              startRow + rowIndex,
              columnIndex,
              row[key].value,
              {
                fontSize: 8,
                isCurrency: isCurrencyValue,
                isDate: isDate,
              }
            );
          }
        }
      );
    }
  );
  return startRow + data.length;
};

// Function to calculate and add total
const addTotal = (
  data: ProcessedTransactionInvoiceDataModel[],
  startRow: number,
  label: string,
  isGrandOrInvoiceTotal: boolean = false
) => {
  const total = data.reduce(
    (sum, row) => sum + (parseFloat(row.billedTotal?.value as string) || 0),
    0
  );
  let amountColumn = processedTransactionInvoiceDataKeys.findIndex(
    (k) => k.key === 'billedTotal'
  );
  amountColumn = amountColumn > 0 ? amountColumn : 1;

  addValueToCell(worksheet, startRow, amountColumn - 1, label, {
    isBold: true,
    fontSize: 10,
  });
  addValueToCell(worksheet, startRow, amountColumn, total, {
    isBold: true,
    isCurrency: true,
    fontSize: 10,
    underline: isGrandOrInvoiceTotal ? 'double' : 'single',
    border: isGrandOrInvoiceTotal,
    isGrandorInvoiceTotal: isGrandOrInvoiceTotal,
  });
  return total;
};

// Main function body
let currentRow = 4; // starts at 5th row
const priorPeriodLabel = 'Prior Period Billing Adjustment';
const currentPeriodLabel = billingIntervalName + ' Billing';
const billingAdjustmentLabel = 'Billing Adjustment Subtotal';
const grandBillingAndAdjustMentLabel = 'Grand Billing & Adjustment Total';
const totalLabel =
  invoiceTransactionData.type === DOCUMENT_TYPES.INVOICE_TYPE
    ? 'Invoice Total'
    : 'Credit Memo Total';

let currentPeriodTotal = 0;
if (currentPeriodData.length > 0) {
  console.time(`${logTag} - currentPeriodData`);
  addValueToCell(worksheet, currentRow, 0, currentPeriodLabel, {
    isBold: true,
    fontSize: 10,
    border: true,
  });
  currentRow += 2;

  addHeaders(currentRow);
  currentRow = addDataRows(currentPeriodData, currentRow + 1);
  lastDataRow = Math.max(lastDataRow, currentRow - 1);
  currentRow++;

  currentPeriodTotal = addTotal(
    currentPeriodData,
    currentRow,
    billingIntervalName + ' Subtotal'
  );
  currentRow += 2;
  console.timeEnd(`${logTag} - currentPeriodData`);
}

let priorPeriodTotal = 0;
if (priorPeriodData.length > 0) {
  console.time(`${logTag} - priorPeriodData`);
  addValueToCell(worksheet, currentRow, 0, priorPeriodLabel, {
    isBold: true,
    fontSize: 10,
    border: true,
  });
  currentRow += 2;

  addHeaders(currentRow);
  currentRow = addDataRows(priorPeriodData, currentRow + 1);
  lastDataRow = Math.max(lastDataRow, currentRow - 1);
  currentRow++;

  priorPeriodTotal = addTotal(
    priorPeriodData,
    currentRow,
    billingAdjustmentLabel
  );
  currentRow += 2;
  console.timeEnd(`${logTag} - priorPeriodData`);
}

// Add grand total and other corresponding total
const grandTotal = currentPeriodTotal + priorPeriodTotal;
addTotal(
  [{ billedTotal: { value: currentPeriodTotal.toString() } }],
  0,
  currentPeriodLabel
);
addTotal(
  [{ billedTotal: { value: priorPeriodTotal.toString() } }],
  1,
  priorPeriodLabel
);
addTotal(
  [{ billedTotal: { value: grandTotal.toString() } }],
  2,
  totalLabel,
  true
);
addTotal(
  [{ billedTotal: { value: grandTotal.toString() } }],
  currentRow,
  grandBillingAndAdjustMentLabel,
  true
);
console.timeEnd(`${logTag} - Adding Values to XL Cells`);

worksheet.setName('InvoiceData');

console.log(
  logTag,
  'Customer Pivot Tables',
  JSON.stringify(customer.customerConfigurationBackupPivotTables)
);

if (customer.customerConfigurationBackupPivotTables) {
  customer.customerConfigurationBackupPivotTables.forEach(
    (pivotTable: CustomerConfigurationBackupPivotTable) => {
      pivotTable.backupPivotTableFields.sort(
        (a: BackupPivotTableFields, b: BackupPivotTableFields) =>
          a.orderRowNumber - b.orderRowNumber
      );
    }
  );
}

const lastColumn = excelColumnAssigner(processedTransactionInvoiceDataKeys.length - 2);
const sourceData = `=${'InvoiceData'}!A7:${lastColumn}${lastDataRow + 1}`;

console.log(logTag, 'Source Data', sourceData);

const combinePivotTablesOnSingleSheet: boolean = customerConfigurationBackupPivotTablesData.every(pt=> pt.combinePivotTables=== true);

console.log(logTag, 'customerConfigurationBackupPivotTablesData: ', customerConfigurationBackupPivotTablesData);
console.log(logTag, 'Combine Pivot Tables on Single Sheet', combinePivotTablesOnSingleSheet);

const pivotTableLayout: string = customer.customerConfigurationBackupPivotTables.every(pt => pt.pivotTableOrientation === PIVOT_TABLE_LAYOUT.HORIZONTAL) ? PIVOT_TABLE_LAYOUT.HORIZONTAL : customer.customerConfigurationBackupPivotTables.every(pt => pt.pivotTableOrientation === PIVOT_TABLE_LAYOUT.VERTICAL) ? PIVOT_TABLE_LAYOUT.VERTICAL : 'MIXED';

console.log(logTag, 'Pivot Table Layout', pivotTableLayout);

if (combinePivotTablesOnSingleSheet && (pivotTableLayout === PIVOT_TABLE_LAYOUT.HORIZONTAL || pivotTableLayout === PIVOT_TABLE_LAYOUT.VERTICAL) && customer.customerConfigurationBackupPivotTables.length > 0) {
    console.log(logTag, `Using single-sheet layout: ${pivotTableLayout}`);
    const pivotSheet = sampleWorkbook.getWorksheets().add('Pivot Tables');
    pivotSheet.getCells().setStandardWidth(20);
    let nextPivotTableRowIndex = 0;
    let nextPivotTableColumnIndex = 0;

    customer.customerConfigurationBackupPivotTables.forEach((pivotTableConfig, index) => {
        console.time(`${logTag} - Pivot table - ${pivotTableConfig.title}`);
        const pivotTables = pivotSheet.getPivotTables();
        let startCell = '';

        if (pivotTableLayout === PIVOT_TABLE_LAYOUT.HORIZONTAL) {
          const hasFilterField = pivotTableConfig.backupPivotTableFields.filter(f => f.isChecked && f.fieldType === PivotFieldType.filter);
          
          console.log(logTag, 'hasFilterField: ', hasFilterField);

          addValueToCell(pivotSheet, 0, nextPivotTableColumnIndex, pivotTableConfig.title, { isBold: true, underline: 'double' });
          startCell = excelColumnAssigner(nextPivotTableColumnIndex) + String(hasFilterField.length+4);
        } else if (pivotTableLayout === PIVOT_TABLE_LAYOUT.VERTICAL) {
          const hasFilterField = pivotTableConfig.backupPivotTableFields.filter(f => f.isChecked && f.fieldType === PivotFieldType.filter);
          addValueToCell(pivotSheet, nextPivotTableRowIndex, 0, pivotTableConfig.title, { isBold: true, underline: 'double' });
          nextPivotTableRowIndex += hasFilterField.length ? (hasFilterField.length + 3) : 2;
          startCell = 'A' + (nextPivotTableRowIndex + 1);
        }

        const pivotTableName = `${pivotTableConfig.title} - ${index}`;
        const pivotTableIndex: number = pivotTables.add(sourceData, startCell, pivotTableName);
        const pivotTable = pivotTables.get(pivotTableIndex);
        
        try {
      pivotTable.setPivotTableStyleType(
        aspose.cells.PivotTableStyleType.PIVOT_TABLE_STYLE_LIGHT_16
      );
      console.log(
        logTag,
        'Applied "Light Blue Pivot Table Style Light 16" to pivot table.'
      );
        } catch (error: any) {
      console.error(
        logTag,
        `Error applying pivot table style: ${error.message}`
      );
        }

    pivotTableConfig.backupPivotTableFields.forEach(
      (fieldConfig: BackupPivotTableFields) => {
        console.log(
          logTag,
          `  - Pivot Table Field: ${JSON.stringify(
            fieldConfig
          )}  - fieldConfig.isChecked: ${fieldConfig.isChecked}`
        );

          if (fieldConfig.isChecked) {
          const fieldIndex = processedTransactionInvoiceDataKeys.findIndex(
            ({ key }) => {
              const matchesFieldName: boolean =
                key === fieldConfig.fieldName;
              return matchesFieldName;
            }
          );

          if (fieldIndex > 0) {
            console.log(
              logTag,
              `  - fieldConfig.fieldName: ${fieldConfig.fieldName}  - fieldConfig.fieldType: ${fieldConfig.fieldType}  - Field Index: ${fieldIndex}`
            );
          }

          if (fieldIndex >= 0) {
            try {
              switch (fieldConfig.fieldType) {
                case PivotFieldType.row:
                  console.log(
                    logTag,
                    `Adding ROW field. fieldIndex: ${fieldIndex}, PivotFieldType: ${aspose.cells.PivotFieldType.ROW}`
                  );
                  pivotTable.addFieldToArea(
                    aspose.cells.PivotFieldType.ROW,
                    fieldIndex
                  );
                  // TODO: Remove this once the fields are hided from the pivot table
                  // addValueNotEmptyFilter(pivotTable, fieldConfig.orderRowNumber - 1);
                  break;
                case PivotFieldType.column:
                  console.log(
                    logTag,
                    `Adding COLUMN field. fieldIndex: ${fieldIndex}, PivotFieldType: ${aspose.cells.PivotFieldType.COLUMN}`
                  );
                  pivotTable.addFieldToArea(
                    aspose.cells.PivotFieldType.COLUMN,
                    fieldIndex
                  );
                  // TODO: Remove this once the fields are hided from the pivot table
                  // addValueNotEmptyFilter(pivotTable, fieldConfig.orderRowNumber - 1);
                  break;
                case PivotFieldType.filter:
                  console.log(
                    logTag,
                    `Adding PAGE field. fieldIndex: ${fieldIndex}, PivotFieldType: ${aspose.cells.PivotFieldType.PAGE}`
                  );
                  pivotTable.addFieldToArea(
                    aspose.cells.PivotFieldType.PAGE,
                    fieldIndex
                  );
                  break;
                case PivotFieldType.value:
                  const dataField = pivotTable.addFieldToArea(aspose.cells.PivotFieldType.DATA, fieldIndex);
                  if (fieldConfig.valueSummarizeMethod.toLowerCase() === 'sum') {
                    dataField.setFunction(aspose.cells.ConsolidationFunction.SUM);
                    dataField.setNumberFormat('$#,##0.00');
                  } else if (fieldConfig.valueSummarizeMethod.toLowerCase() === 'count') {
                    dataField.setFunction(aspose.cells.ConsolidationFunction.COUNT);
                  } else if (fieldConfig.valueSummarizeMethod.toLowerCase() === 'average') {
                    dataField.setFunction(aspose.cells.ConsolidationFunction.AVERAGE);
                  }

                  if (fieldConfig.customName) {
                    dataField.setDisplayName(fieldConfig.customName);
                  }
                  break;
                default:
                  console.error(
                    `Unknown field type: ${fieldConfig.fieldType}`
                  );
              }
            } catch (error: any) {
              console.error(
                `Error in switch case for fieldType ${fieldConfig.fieldType}: ${error.message}`
              );
              console.error(`fieldConfig: ${JSON.stringify(fieldConfig)}`);
              console.error(`fieldIndex: ${fieldIndex}`);
            }
          } else {
            console.error(`Field index out of bounds ${fieldIndex}`);
          }
        }
      }
    );

        pivotTable.setRowGrand(true);
        pivotTable.setColumnGrand(true);
        pivotTable.setShowDrill(false);
        
      for (let i = 0; i < pivotTable.getRowFields().getCount(); i++) {
        try {
          pivotTable.getRowFields().get(i).setSubtotals(aspose.cells.PivotFieldSubtotalType.NONE, true);
        } catch (error) { console.error(`Error setting subtotals for row field index ${i}:`, error); }
      }
      for (let i = 0; i < pivotTable.getColumnFields().getCount(); i++) {
        try {
          pivotTable.getColumnFields().get(i).setSubtotals(aspose.cells.PivotFieldSubtotalType.NONE, true);
        } catch (error) { console.error(`Error setting subtotals for column field index ${i}:`, error); }
      }

      pivotTable.refreshData();
      pivotTable.calculateData();

      const rowFieldNames = processedTransactionInvoiceDataKeys.map(h => h.displayName);
      const requiredRowFieldNames = processedTransactionInvoiceDataKeys.filter(h => REQUIRED_FIELDS_IN_TRANSACTION.includes(h.key.toUpperCase())).map(h => h.displayName);

      try {
        hideEmptyAndHeaderItems(pivotTable, rowFieldNames, requiredRowFieldNames, billingIntervalName);
      } catch (error) { console.error(logTag, 'Error while trying to hide empty and header items:', error); }

      pivotTable.refreshData();
      pivotTable.calculateData();

      try {
        const pivotRange = pivotTable.getTableRange2();
        if (pivotRange) {
          if (pivotTableLayout === PIVOT_TABLE_LAYOUT.HORIZONTAL) {
            nextPivotTableColumnIndex = pivotRange.EndColumn + 3;
          } else {
            nextPivotTableRowIndex = pivotRange.EndRow + 4;
          }
        } else { throw new Error("Could not get pivot table range."); }
      } catch (e: any) {
        console.error(logTag, `${e.message} Using fallback estimation.`);
        if (pivotTableLayout === PIVOT_TABLE_LAYOUT.HORIZONTAL) {
          nextPivotTableColumnIndex += (pivotTable.getRowFields().getCount() || 1) + pivotTable.getDataFields().getCount() + 2;
        } else {
          nextPivotTableRowIndex += 50;
        }
      }
      console.timeEnd(`${logTag} - Pivot table - ${pivotTableConfig.title}`);
    });
} else {
  console.log(logTag, 'Layout not specified, creating separate sheets for each pivot table.');
  customer.customerConfigurationBackupPivotTables.forEach((pivotTableConfig) => {
    console.time(`${logTag} - Pivot table - ${pivotTableConfig.title}`);
    console.log(logTag, 'Working on Pivot Table:', pivotTableConfig.title);

    const sheetName = pivotTableConfig.title.substring(0, 31); // Ensure sheet name is not too long
    const pivotSheet = sampleWorkbook.getWorksheets().add(sheetName);
    const pivotTables = pivotSheet.getPivotTables();

    const pivotTableIndex: number = pivotTables.add(sourceData, 'A1', pivotTableConfig.title);
    const pivotTable = pivotTables.get(pivotTableIndex);

    try {
      pivotTable.setPivotTableStyleType(aspose.cells.PivotTableStyleType.PIVOT_TABLE_STYLE_LIGHT_16);
    } catch (error: any) {
      console.error(logTag, `Error applying pivot table style: ${error.message}`);
    }

    pivotTableConfig.backupPivotTableFields.forEach((fieldConfig) => {
      if (fieldConfig.isChecked) {
        const fieldIndex = processedTransactionInvoiceDataKeys.findIndex(({ key }) => key === fieldConfig.fieldName);
        if (fieldIndex >= 0) {
          try {
            switch (fieldConfig.fieldType) {
              case PivotFieldType.row:
                pivotTable.addFieldToArea(aspose.cells.PivotFieldType.ROW, fieldIndex);
                break;
              case PivotFieldType.column:
                pivotTable.addFieldToArea(aspose.cells.PivotFieldType.COLUMN, fieldIndex);
                break;
              case PivotFieldType.filter:
                pivotTable.addFieldToArea(aspose.cells.PivotFieldType.PAGE, fieldIndex);
                break;
              case PivotFieldType.value:
                const dataField = pivotTable.addFieldToArea(aspose.cells.PivotFieldType.DATA, fieldIndex);
                if (fieldConfig.valueSummarizeMethod.toLowerCase() === 'sum') {
                  dataField.setFunction(aspose.cells.ConsolidationFunction.SUM);
                  dataField.setNumberFormat('$#,##0.00');
                } else if (fieldConfig.valueSummarizeMethod.toLowerCase() === 'count') {
                  dataField.setFunction(aspose.cells.ConsolidationFunction.COUNT);
                } else if (fieldConfig.valueSummarizeMethod.toLowerCase() === 'average') {
                  dataField.setFunction(aspose.cells.ConsolidationFunction.AVERAGE);
                }
                if (fieldConfig.customName) {
                  dataField.setDisplayName(fieldConfig.customName);
                }
                break;
            }
          } catch (error: any) {
            console.error(`Error in switch case for fieldType ${fieldConfig.fieldType}: ${error.message}`);
          }
        }
      }
    });

    pivotTable.setRowGrand(true);
    pivotTable.setColumnGrand(true);
    pivotTable.setShowDrill(false);

    if (pivotTable.getRowFields().getCount() > 0) {
      for (let i = 0; i < pivotTable.getRowFields().getCount(); i++) {
        try {
          pivotTable.getRowFields().get(i).setSubtotals(aspose.cells.PivotFieldSubtotalType.NONE, true);
        } catch (error) { console.error(`Error setting subtotals for row field ${i}`, error); }
      }
    }
    if (pivotTable.getColumnFields().getCount() > 0) {
      for (let i = 0; i < pivotTable.getColumnFields().getCount(); i++) {
        try {
          pivotTable.getColumnFields().get(i).setSubtotals(aspose.cells.PivotFieldSubtotalType.NONE, true);
        } catch (error) {
          console.error(
            `Error setting subtotals for column field ${i}: ${
              pivotTable.getColumnFields().get(i)?.getName() ?? ''
            } : `,
            error
          );
        }
      }
    }

    console.log(logTag, '-->End of setting column subtotals<--');

    pivotTable.refreshData();
    pivotTable.calculateData();

    // Collect all row field names
    const rowFieldNames: string[] = processedTransactionInvoiceDataKeys.map(
      (header: {
        key: string;
        displayName: string;
        order: number | undefined;
      }) => header.displayName
    );

    // Collect all required row  field names
    const requiredRowFieldNames: string[] =
      processedTransactionInvoiceDataKeys
        .filter(
          (header: {
            key: string;
            displayName: string;
            order: number | undefined;
          }) =>
            REQUIRED_FIELDS_IN_TRANSACTION.includes(
              header.key.toUpperCase()
            )
        )
        .map(
          (header: {
            key: string;
            displayName: string;
            order: number | undefined;
          }) => header.displayName
        );

    try {
      // Apply the new logic to hide empty and header items
      hideEmptyAndHeaderItems(
        pivotTable,
        rowFieldNames,
        requiredRowFieldNames,
        billingIntervalName
      );
      console.log(
        logTag,
        'Empty and header items have been hidden successfully'
      );
    } catch (error) {
      console.error(
        logTag,
        'Error while trying to hide empty and header items:',
        error
      );
    }

    pivotTable.refreshData();
    pivotTable.calculateData();

    console.log(
      logTag,
      'Pivot table has been created and empty/header items have been hidden'
    );
        console.timeEnd(`${logTag} - Pivot table - ${pivotTableConfig.title}`);
    }
  );

  console.time(`${logTag} - File Name Configuration`);
  if (
    customer.customerConfigurationFileName &&
    customer.customerConfigurationFileName.length > 0
  ) {
    customer.customerConfigurationFileName.sort(
      (a: CustomerConfigurationFileName, b: CustomerConfigurationFileName) =>
        a.order - b.order
    );
  }
}

let fileName = '';

customer.customerConfigurationFileName.forEach(
  (element: CustomerConfigurationFileName, index: number) => {
    if (element.isChecked) {
      if (element.fieldName === 'Custom String') {
        fileName += element.customValue ?? '';
      } else if (element.fieldName === 'periodEndDate') {
        const endPeriodDateString = (invoiceTransactionData as Invoice)[
          element.fieldName as keyof Invoice
        ] as string;
        const endPeriodDate = new Date(endPeriodDateString);

        const year = endPeriodDate.getFullYear();
        const month = (endPeriodDate.getMonth() + 1)
          .toString()
          .padStart(2, '0');
        const day = endPeriodDate.getDate().toString().padStart(2, '0');

        fileName += `${year} -${month} -${day} `;
      } else if (element.fieldName === 'type') {
        fileName +=
          invoiceTransactionData.type === DOCUMENT_TYPES.INVOICE_TYPE
            ? 'INV'
            : 'CR';
      } else if (element.fieldName === 'demarcationValue') {
        const demarcationType = customer.demarcation || '';
        const response = getDemarcationValue(
          invoiceTransactionData,
          demarcationType
        );
        console.log(logTag, response, 'getDemarcationValue response');
        if (!response) {
          fileName = fileName.slice(0, -1); // truncate the additional hypen(-) in fileName if demarcation is none
        }
        fileName += response;
      } else if (element.fieldName === 'invoice/CreditMemoNumber') {
        const invoiceNumber =
          (invoiceTransactionData as Invoice).invoiceNumber ||
          (invoiceTransactionData as Invoice).documentNumber ||
          '';
        const creditMemoNumber =
          (invoiceTransactionData as CreditMemo).creditMemoNumber ||
          (invoiceTransactionData as CreditMemo).documentNumber ||
          '';
        const documentNumber: string | undefined = invoiceNumber
          ? invoiceNumber
          : creditMemoNumber;
        fileName += documentNumber;
      } else {
        fileName +=
          invoiceTransactionData.type === DOCUMENT_TYPES.INVOICE_TYPE
            ? (invoiceTransactionData as Invoice)[
                element.fieldName as keyof Invoice
              ]
            : (invoiceTransactionData as CreditMemo)[
                element.fieldName as keyof CreditMemo
              ];
      }

      if (index < customer.customerConfigurationFileName.length - 1) {
        fileName += '-';
      }
    }
  }
);

if (fileName.trim().length === 0) {
  const invoiceNumber =
    (invoiceTransactionData as Invoice).invoiceNumber ||
    (invoiceTransactionData as Invoice).documentNumber ||
    '';
  const creditMemoNumber =
    (invoiceTransactionData as CreditMemo).creditMemoNumber ||
    (invoiceTransactionData as CreditMemo).documentNumber ||
    '';
  fileName = `${customer.entityId}-${
    invoiceNumber ? invoiceNumber : creditMemoNumber
  }`;
}

console.log(logTag, 'File Name', fileName);

fileName = `${fileName}.xlsx`;

console.log(logTag, 'Filename before cleanup', fileName);

fileName = cleanNameForS3Bucket(fileName);

console.log(logTag, 'Filename after cleanup', fileName);

sampleWorkbook.save(fileName);

console.timeEnd(`${logTag} - File Name Configuration`);

console.log(logTag, 'Excel File has been created');

console.log(logTag, 'Reading File Buffer');

console.time(`${logTag} - Reading File Buffer`);

const fileBuffer: Buffer = await readFile(fileName);

console.timeEnd(`${logTag} - Reading File Buffer`);

sampleWorkbook.dispose();

console.log(logTag, 'About to convert to string the file buffer');
console.time(`${logTag} - Converting File Buffer to Base64`);
const base64FileContent: string = fileBuffer.toString('base64');
console.timeEnd(`${logTag} - Converting File Buffer to Base64`);

const fileContents: FileContentsModel = {
  fileName,
  base64FileContent,
};

console.timeEnd(`${logTag} - Execution Time`);

return fileContents;

} catch (error: any) {
console.timeEnd(${logTag} - Execution Time);
throw {
message:
'An error occurred while generating a Billing Backup Summary XLSX file: ’ +
error.message,
statusCode: 500,
occurredAt: ‘generateBillingBackupSummary’,
};
}
};

2

@CodeRedff0000

To address the issue of your pivot table defaulting to COUNT instead of SUM when using a data range that includes multiple header rows, consider the following approaches:

  1. Define a Clean Source Range: Instead of using the entire data range that includes both sets of headers, define a source range that only includes the relevant data rows. Specify the starting and ending rows that contain the actual data, excluding the header rows. For example, if your data starts from row 7 and goes to the last data row, set the source range accordingly.

  2. Post-Creation Adjustment: If you prefer to keep the second header row for readability, you can adjust the function to SUM after creating the pivot table. This is a valid approach and is commonly used when dealing with mixed data types in the source range.

Here’s a minimal code snippet to illustrate how to set the source range and adjust the pivot table after creation:

const sourceData = `InvoiceData!A7:${lastColumn}${lastDataRow + 1}`; // Adjust the range to exclude headers
const pivotTableIndex = pivotTables.add(sourceData, 'A1', 'PivotTable1');
const pivotTable = pivotTables.get(pivotTableIndex);

// Set the SUM function for the Amount field
const dataField = pivotTable.addFieldToArea(aspose.cells.PivotFieldType.DATA, amountFieldIndex);
dataField.setFunction(aspose.cells.ConsolidationFunction.SUM);

By defining a clean source range, you can help ensure that the pivot table recognizes the numeric data correctly from the start. If you choose to keep the headers, adjusting the function post-creation is a reliable method to ensure the correct calculation.

If you have further questions or need additional assistance, feel free to ask!

3

@CodeRedff0000,

Thank you for sharing the details and the code snippet.

To evaluate your issue precisely, could you please zip and attach the following resource files/artifacts:

  1. The template Excel file containing the dataset (if applicable).
  2. The current output Excel file generated by Aspose.Cells.
  3. The expected Excel file containing the pivot table (based on your data range in the Excel spreadsheet). You may create or update the pivot table manually in MS Excel and save the file to share with us.

Once we receive these resources, we will start investigating the issue and keep you updated on it.