Pivot Data Mismatch Between Excel Workbook and Generated HTML (Aspose.Cells)

[KarthikMunjam]

We are facing a serious data mismatch issue when converting an Excel workbook (containing a PivotTable) to HTML using Aspose.Cells for Node.js.

The Excel workbook is generated programmatically using Aspose. The PivotTable inside the “PivotTable” sheet is correct when viewed in Excel.

However, after converting the workbook to HTML using HtmlSaveOptions, the PivotTable output differs from what is shown in Excel.

Code Used (for HTML conversion):

const tempFile = ‘pivot_only.html’;

saveWorkbookAsHtml(workBook, tempFile);

function saveWorkbookAsHtml(workBook, fileName) {
const sheets = workBook.getWorksheets();

for (let i = 0; i < sheets.getCount(); i++) {
    const sheet = sheets.get(i);
    if (sheet.getName() !== 'PivotTable') {
        sheet.setVisible(false);
    }
}

const saveOptions = new aspose.cells.HtmlSaveOptions();
saveOptions.setExportActiveWorksheetOnly(true);
saveOptions.setPresentationPreference(true);
saveOptions.setExportGridLines(true);

workBook.save(fileName, saveOptions);

}

Issue Details:

There is a clear data mismatch between the Excel PivotTable and the HTML Pivot generated by Aspose.

1. Missing Pivot Value in HTML

In Excel, the C column contains the value “Jan”, but in the HTML output this value is missing.
It appears that the cell is shifted or misaligned in the HTML rendering.

2. Incorrect Random Value Added in HTML

In the HTML output, a random date-like value — <1900-01-01 — is getting added, which does not exist in the Excel Pivot at all.

These issues indicate a rendering problem with how Aspose exports PivotTables to HTML.

Attachments:

(We will attach screenshots showing the Excel Pivot vs HTML Pivot.)
Screenshot 2025-12-03 at 8.59.30 PM.png (640.7 KB)

Screenshot 2025-12-03 at 8.59.51 PM.png (60.8 KB)

Screenshot 2025-12-03 at 9.01.19 PM.png (614.5 KB)

Request:

Please investigate why:

1. Pivot values (e.g., “Jan” in column C) are missing in HTML,
2. Cells appear shifted in the HTML output, and
3. An incorrect value <1900-01-01 is being added.

We need the HTML Pivot to match the Excel Pivot exactly in terms of data, positioning, and values.

[Professionalize.Discourse]

@KarthikMunjam

Based on limited evidence (treat as hypothesis), here is a cautious take:
Summary
When a workbook that contains a PivotTable is saved as HTML with Aspose.Cells for Node.js, the HTML output shows missing values (e.g., “Jan”) and an unexpected <1900‑01‑01> placeholder.

Why it happens

• The PivotTable cache may not be refreshed before the HTML export, so the exporter reads an empty or stale cache.
• Formula results that feed the PivotTable might not be calculated, causing default placeholders to appear in the HTML.

What to do

1. Upgrade the Aspose.Cells for Node.js package to the latest version available in npm.

2. Refresh the PivotTable cache (and calculate any dependent formulas) before saving:

   // Get the worksheet that contains the PivotTable
   const ws = workBook.getWorksheets().get("PivotTable");   // or use the sheet index
   
   // Refresh all PivotTables on the sheet
   const pivots = ws.getPivotTables();
   for (let i = 0; i < pivots.getCount(); i++) {
       pivots.get(i).refresh();
   }
   
   // Ensure all formulas are evaluated
   workBook.calculateFormula();
   

3. Save the workbook as HTML using HtmlSaveOptions (the options you already set are sufficient):

   const saveOptions = new aspose.cells.HtmlSaveOptions();
   saveOptions.setExportActiveWorksheetOnly(true);
   saveOptions.setPresentationPreference(true);
   saveOptions.setExportGridLines(true);
   
   workBook.save(fileName, saveOptions);
   

4. Verify the output – the missing “Jan” value and the <1900‑01‑01> placeholder should no longer appear, and the cell alignment should match the Excel view.

5. If the problem persists, provide the following information for further analysis:

   • Exact Aspose.Cells version (npm list aspose.cells or aspose.cells.version).
   • A minimal workbook that reproduces the issue.
   • Node.js version (node -v) and operating‑system details.

These details will help the support team isolate the cause and suggest a targeted workaround.

Please validate this behaviour with your exact SDK version and a minimal repro, as support can vary by release.

Assumptions / confirmations needed: SDK + version, file types, minimal sample, and exact error/output.

[KarthikMunjam]

can i use this , i used the below code still the same issue?
function saveWorkbookAsHtml(workBook, fileName) {

const ws = workBook.getWorksheets().get("PivotTable"); 

if (ws) {
    const pivots = ws.getPivotTables();
    for (let i = 0; i < pivots.getCount(); i++) {
        const pivot = pivots.get(i);

        pivot.refreshData();
        pivot.calculateData();
    }
}

workBook.calculateFormula();

const sheets = workBook.getWorksheets();
for (let i = 0; i < sheets.getCount(); i++) {
    const sheet = sheets.get(i);
    if (sheet.getName() !== 'PivotTable') {
        sheet.setVisible(false);
    }
}

const saveOptions = new aspose.cells.HtmlSaveOptions();
saveOptions.setExportActiveWorksheetOnly(true);
saveOptions.setPresentationPreference(true);
saveOptions.setExportGridLines(true);

workBook.save(fileName, saveOptions);

}

[Professionalize.Discourse]

@KarthikMunjam

Based on limited evidence (treat as hypothesis), here is a cautious take:
Summary
When a workbook contains a PivotTable, the HTML converter will output the worksheet data only unless the Pivot cache is refreshed and the ExportPivotTable option is enabled. Refreshing the cache and setting this option usually eliminates the mismatch between the Excel view and the generated HTML.

Why it happens

• The PivotTable cache is stored separately from the worksheet cells. If the cache is not refreshed after the source data changes, the HTML saver reads the old cache, which can appear as missing or incorrect values.
• HtmlSaveOptions.ExportPivotTable is false by default, so the saver writes the underlying data range instead of the formatted PivotTable.

What to do

1. Refresh each PivotTable in the workbook before saving.
2. Re‑calculate formulas that feed the Pivot cache (optional but often helpful).
3. Enable PivotTable export in HtmlSaveOptions.
4. Save the workbook with the configured options.

function saveWorkbookAsHtml(workBook, fileName) {
    // 1️⃣ Refresh every PivotTable
    const ws = workBook.getWorksheets().get("PivotTable");   // use your sheet name
    if (ws) {
        const pivots = ws.getPivotTables();
        for (let i = 0; i < pivots.getCount(); i++) {
            const pivot = pivots.get(i);
            pivot.refreshData();      // reload source data
            pivot.calculateData();    // rebuild the cache
        }
    }

    // 2️⃣ Re‑calculate formulas that may affect the Pivot cache
    workBook.calculateFormula();

    // 3️⃣ (Optional) hide other sheets if you only need the Pivot sheet
    const sheets = workBook.getWorksheets();
    for (let i = 0; i < sheets.getCount(); i++) {
        const sheet = sheets.get(i);
        sheet.setVisible(sheet.getName() === "PivotTable");
    }

    // 4️⃣ Set HTML save options
    const saveOptions = new aspose.cells.HtmlSaveOptions();
    saveOptions.setExportActiveWorksheetOnly(true);   // export only the active sheet
    saveOptions.setExportPivotTable(true);            // render the PivotTable
    saveOptions.setExportGridLines(true);
    saveOptions.setPresentationPreference(true);     // keep original layout

    // 5️⃣ Save as HTML
    workBook.save(fileName, saveOptions);
}

Quick checklist

1. Call pivot.refreshData() and pivot.calculateData() for each PivotTable.
2. Run workBook.calculateFormula() if formulas are involved.
3. Set saveOptions.setExportPivotTable(true).
4. Adjust ExportActiveWorksheetOnly (or use ExportAllWorksheets) as required.
5. Save the workbook with the configured HtmlSaveOptions.

Following these steps should make the HTML output reflect the same PivotTable view you see in Excel. If differences still appear, verify that the source data range used by the PivotTable is fully populated before the refresh step.

Please validate this behaviour with your exact SDK version and a minimal repro, as support can vary by release.

Assumptions / confirmations needed: SDK + version, file types, minimal sample, and exact error/output.

[KarthikMunjam]

@Professionalize.Discourse

function saveWorkbookAsHtml(workBook, fileName) {
    // 1️⃣ Refresh every PivotTable
    const ws = workBook.getWorksheets().get("PivotTable");   // use your sheet name
    if (ws) {
        const pivots = ws.getPivotTables();
        for (let i = 0; i < pivots.getCount(); i++) {
            const pivot = pivots.get(i);
            pivot.refreshData();      // reload source data
            pivot.calculateData();    // rebuild the cache
        }
    }

    // 2️⃣ Re‑calculate formulas that may affect the Pivot cache
    workBook.calculateFormula();

    // 3️⃣ (Optional) hide other sheets if you only need the Pivot sheet
    const sheets = workBook.getWorksheets();
    for (let i = 0; i < sheets.getCount(); i++) {
        const sheet = sheets.get(i);
        sheet.setVisible(sheet.getName() === "PivotTable");
    }

    // 4️⃣ Set HTML save options
    const saveOptions = new aspose.cells.HtmlSaveOptions();
    saveOptions.setExportActiveWorksheetOnly(true);   // export only the active sheet
    saveOptions.setExportPivotTable(true);            // render the PivotTable
    saveOptions.setExportGridLines(true);
    saveOptions.setPresentationPreference(true);     // keep original layout

    // 5️⃣ Save as HTML
    workBook.save(fileName, saveOptions);
}

throwing error
‘saveOptions.setExportPivotTable is not a function’

[amjad.sahi]

@KarthikMunjam,

@KarthikMunjam

throwing error
‘saveOptions.setExportPivotTable is not a function’

We apologize for the inconvenience caused by the AI bot suggesting a method that unfortunately does not exist in the APIs.

[amjad.sahi]

@KarthikMunjam,

@KarthikMunjam

Issue Details:

There is a clear data mismatch between the Excel PivotTable and the HTML Pivot generated by Aspose.

1. Missing Pivot Value in HTML

In Excel, the C column contains the value “Jan”, but in the HTML output this value is missing.
It appears that the cell is shifted or misaligned in the HTML rendering.

2. Incorrect Random Value Added in HTML

In the HTML output, a random date-like value — <1900-01-01 — is getting added, which does not exist in the Excel Pivot at all.

Thank you for sharing the screenshots. I can see the issues you’ve highlighted in the screenshots. To thoroughly evaluate the pivot table problems in the output HTML, we request your template (input) Excel file. Please compress the file into a zip archive and attach it here. We’ll review the issues soon.

[KarthikMunjam]

@amjad.sahi

Thank you for sharing the screenshots. I can see the issues you’ve highlighted in the screenshots. To thoroughly evaluate the pivot table problems in the output HTML, we request your template (input) Excel file. Please compress the file into a zip archive and attach it here. We’ll review the issues soon.

Please find the excel and html file in the below zip file ::
Excel Issue.zip (175.1 KB)

[KarthikMunjam]

@amjad.sahi can you please help to reslove this?

[amjad.sahi]

@KarthikMunjam,

Thanks for the template Excel file.

I tested your scenario/case using your code snippet shared in your previous reply with latest version/fix (please try the latest Aspose.Cells for Node.js via Java v25.10), it works fine and I can get expected results. Since you are using an older version of Aspose.Cells for Node.js via Java (v24.x), so we recommend you to kindly try using latest version and let us know your feedback.

[KarthikMunjam]

if there any possibility for the fix in the older verison , any other work around?

[amjad.sahi]

@KarthikMunjam,

I am sorry and apparently there is no other alternative but to upgrade to latest version or APIs set. See page for availability policy here: Note that we only develop hotfixes for the current version of the software and that if you do not have a subscription you are not entitled to software updates.

If you are not eligible to access the latest version or fixes, we request you to consider upgrading your subscription. Additionally, if you have an active subscription that has not expired, you may report any issues you encounter with the APIs, and we will do our best to address and resolve them soon.

[KarthikMunjam]

currently we are using “aspose.cells”: “24.1.0” , will this work on this?

[John.He]

@KarthikMunjam
By testing on the latest version v25.10 using the following sample code, we can obtain the correct results. Please refer to the attachment. out_nodejs.zip (3.1 KB)

var aspose = aspose || {};

aspose.cells = require("aspose.cells");


console.log(aspose.cells.CellsHelper.getVersion());

const book = new aspose.cells.Workbook('ExcelFile.xlsx');

const sheets = book.getWorksheets();
const sheetcount = sheets.getCount();
for (let i = 0; i < sheetcount; i++)
{
    const sheet = sheets.get(i);
    if (sheet.getName() !== 'PivotTable') 
    {
        sheet.setVisible(false);
    }
    else
    {
    	const pivots = sheet.getPivotTables();
	    const pivotcount = pivots.getCount();
	    for (let i = 0; i < pivotcount; i++) 
	    {
	        const pivot = pivots.get(i);
	
	        pivot.refreshData();
	        pivot.calculateData();
	    }
    }
}

book.calculateFormula();

const saveOptions = new aspose.cells.HtmlSaveOptions();
saveOptions.setExportActiveWorksheetOnly(true);
saveOptions.setPresentationPreference(true);
saveOptions.setExportGridLines(true);

book.save("out_nodejs.html", saveOptions);

We are sorry but we cannot evaluate your issue based on older versions of the APIs (i.e., Aspose.Cells for Node.js via Java 24.1.0). Neither we can include any fixes to older versions the APIs, the fixes are based on latest APIs set only. We recommend you to kindly try using our latest version: Aspose.Cells for Node.js via Java 25.10.

[KarthikMunjam]

so if i use aspose.cells verison 25.10 , then which java version should i use?