Cell data truncated in pivot table

Aspose.Cells Product Family
1

Hello,

i have an issue when updating data for a pivot table. In the pivot table, only the first 257 characters are displayed. If the workbook is refreshed in Excel i can also see the rest of the cell data.

I can not set the option RefreshDataOnOpeningFile because i will either generate a PDF from the spreadsheet or autofit the row height. This is only possible with the whole text in the cell.
Note that we have a sample application that we can send you if needed.

2

Hi Rüdiger,


Thank you for contacting Aspose support.

It would be appropriate that you should share a executable standalone sample application to demonstrate the problem. Please also share the input spreadsheet (if any) along with your environment details (OS, JDK & Aspose.Cells API version).
3

Hello,

i’m using

Windows 7, jdk1.8.0_91, aspose-cells-8.8.2.

Attached you can find a template and a sample application.


4

Hi again,


Thank you for sharing the samples and details.

I have evaluated the presented scenario while using the latest major release of Aspose.Cells for Java 8.8.3, and I am not able to observe any problem while using your exact code. Please note, the long text has expanded the column width to accommodate 255 characters whereas the contents exceeding the 255 characters are trimmed due to the limitation imposed by Excel specifications. Please check the attached snapshot & resultant spreadsheet. Please give a try to the latest version of the API on your side as well.

5

Hi,

i checked the resultant spreadsheet and there are only 255 charackters in the cell. But if i refresh the pivot table in Excel i have the whole text. Please check the attached snapshots.

6

Hi,


Thank you for sharing the snapshots.

You are correct, as soon as I refresh the Pivot Table manually in Excel, all contents are shown in the formula bar. This behaviour needs to be investigated by the product team therefore I have logged a ticket with Id CELLSJAVA-41908. Please spare us little time to properly evaluate the scenario and get back to you with updates in this regard.
7

Hi again,


This is to update you that we have resolved the problem logged earlier as CELLSJAVA-41908. We will shortly share the fix here after ensuring the quality and incorporating other enhancements.
8

Hi,

Please try our latest version/fix: Aspose.Cells for Java v8.9.0, you may download it here:

We add PivotTable.IsExcel2003Compatible property to support your needs. See the following sample code for your reference:

e.g.

Sample code:

Workbook wb = new Workbook(filePath + "pivot_table.xlsx");

wb.calculateFormula(true);

Worksheet sheet = wb.getWorksheets().get(1);

//Access cell "A1" in the sheet.

Cells cells = sheet.getCells();

Cell cell = cells.get("A3");

cell.setValue("FooBar");

cell = cells.get("B3");

cell.setValue("very long text 1. very long text 2. very long text 3. very long text 4. very long text 5. very long text 6. very long text 7. very long text 8. very long text 9. very long text 10. very long text 11. very long text 12. very long text 13. very long text 14. very long text 15. very long text 16. very long text 17. very long text 18. very long text 19. very long text 20.");

cell = cells.get("C3");

cell.setValue("closed");

cell = cells.get("D3");

cell.setValue("2016/07/21");


sheet = wb.getWorksheets().get(0);

//sheet.autoFitColumns();

PivotTable pivotTable = sheet.getPivotTables().get(0);


// Adds this line to specifiy whether the PivotTable is compatible for Excel2003 when refreshing PivotTable,

// if true, a string must be less than or equal to 255 characters, so if the string is greater than 255 characters,

// it will be truncated. if false, a string will not have the aforementioned restriction.

// The default value is true.

pivotTable.setExcel2003Compatible(false);

pivotTable.setRefreshDataFlag(true);

pivotTable.refreshData();

pivotTable.setRefreshDataFlag(false);

pivotTable.calculateData();

//pivotTable.setRefreshDataOnOpeningFile(true);

System.out.println(sheet.getCells().get("B5").getStringValue().length());

wb.save(filePath + "out_java.xlsx");

Let us know your feedback.

Thank you.