Excel Removing Pivot Table From Document

Aspose.Cells Product Family
1

I am trying to create a pivot table in my workbook, but when I open the result .xlsx file in Microsoft Excel I get the following error message:

Excel could not open accounts (3).xlsx because some content is unreadable. Do you want to open and repair this workbook?

I click the “Open and Repair” button and get a popup that says:

p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px ‘Helvetica Neue’}

Excel repaired or removed unreadable content from this workbook.

An .xml log file is created with the following content:

p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #ba2da2} p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000} span.s1 {font-variant-ligatures: no-common-ligatures} span.s2 {font-variant-ligatures: no-common-ligatures; color: #d12f1b} span.s3 {font-variant-ligatures: no-common-ligatures; color: #ba2da2} span.s4 {font-variant-ligatures: no-common-ligatures; color: #957e35} span.s5 {font-variant-ligatures: no-common-ligatures; color: #1337ff}

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

Repair Result to accounts (3)3.xml

Errors were detected in file ‘/path/to/accounts (3).xlsx’

Removed Feature: PivotTable report from /xl/pivotCache/pivotCacheDefinition1.xml (PivotTable cache)

Removed Feature: PivotTable report from /xl/pivotTables/pivotTable1.xml (PivotTable view)

Removed Records: Workbook properties from /xl/workbook.xml (Workbook)

Here is an example of my pivot table creation code:

int sheetIndex = workbook.getWorksheets().add();

Worksheet sheet = workbook.getWorksheets().get(sheetIndex);

sheet.setName(“Financial Class”);

PivotTableCollection pivotTables = sheet.getPivotTables();

int index = pivotTables.add("=Data!A1:AE462", “A1”, “Financial Class”);

p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco} p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco; min-height: 15.0px} p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Monaco; color: #3933ff} span.s1 {color: #931a68} span.s2 {color: #7e504f} span.s3 {color: #3933ff} span.s4 {color: #000000} span.s5 {text-decoration: underline ; color: #7e504f} span.s6 {text-decoration: underline ; color: #000000} span.s7 {text-decoration: underline}

2

Hi,


Please try our latest version/fix: Aspose.Cells for Java v16.12.8 (attached).

If you still find the issue with v16.12.8, kindly do paste your sample JAVA program (runnable) to reproduce the issue on our end. Also attached the template files (input file (if any) and output file). We will check it soon.

Thank you.
3

Attached is a .zip file with a sample .java program and output .xlsx file. You will need to add the Aspose.Cells library for the sample to compile.

4

Hi,


Thanks for the sample JAVA program and template file.

I have evaluated your issue a bit. Please change your line of code (in bold) from the code segment:

…
PivotTableCollection pivotTables = sheet.getPivotTables();

int index = pivotTables.add("=Data!" + firstCell.name + “:” + lastCell.name, “A1”, “Financial Class”);

PivotTable pivotTable = pivotTables.get(index);
pivotTable.setRowGrand(true);
pivotTable.addFieldToArea(PivotFieldType.ROW, “FC”);
pivotTable.addFieldToArea(PivotFieldType.DATA, “Denied Amt”);

to:

…
PivotTableCollection pivotTables = sheet.getPivotTables();

int index = pivotTables.add("=‘Patient Detail’!" + firstCell.name + “:” + lastCell.name, “A1”, “Financial Class”);

PivotTable pivotTable = pivotTables.get(index);
pivotTable.setRowGrand(true);
pivotTable.addFieldToArea(PivotFieldType.ROW, “FC”);
pivotTable.addFieldToArea(PivotFieldType.DATA, “Denied Amt”);

it should work fine.

Let us know if you still if you still find any issue.

Thank you.
5

That worked! Thank you so much!

6

Hi,


Good to know that your issue is sorted out now. Feel free to contact us any time if you have further comments or questions, we will be happy to assist you soon.

Thank you.