Text Wrap setting lost on pivot refresh in values columns

Aspose.Cells Product Family
1

Hi,

We found an inconsistency issue with pivot tables. When the pivot table has more than one (and only with more than one) value field text wrap setting is removed on pivot table refresh. We are refreshing the pivot table with the snippet below

pvt.PreserveFormatting = true;
pvt.RefreshDataFlag = true;
//Refresh the data in the pivot cache.
pvt.RefreshData();
pvt.RefreshDataFlag = false;
//Refresh the pivot table.
pvt.CalculateData();
pvt.RefreshDataOnOpeningFile = true;
I have attached a small project to demonstrate the issue. I have also attached an input file “input file with long headers.xlsx” for you to use. The output xlsx file is called “generated file.xlsx”. By opening the output file you will see the issue.

Additional Information:
- You might need to investigate cell styling on pivot tables in general as similarly, text direction of values columns are also lost.
2

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We were able to observe this issue using the latest version with your sample project and source file. Text wrap setting is removed on pivot table refresh. We have logged this issue in our database. We will look into it and resolve this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-42030.

We have also attached the output xlsx file for a reference.

3

Hi,

This information might help you find what is causing the issue.

When we force a wrap setting on a pivot table’s {Value} column using the code below the issue after the refresh has already been executed the debug shows the setting has been set back to “Text Wrap” on even until after the file has been re-saved. If you open the output PDF the text wrap is there, but if you open the XLSX output, the text wrap is gone.

The code used to force the text wrap is shown below (where “t” is the pivot table):

Hashtable styles = new Hashtable();
int hRow = t.DataBodyRange.StartRow - 1;
for (int c = t.RowRange.StartColumn; c <= t.DataBodyRange.EndColumn; c++)
{
styles.Add(c, sheet.Cells[hRow, c].GetStyle());
}

t.RefreshDataFlag = true;
t.RefreshData();
t.RefreshDataFlag = false;
t.CalculateRange();
t.CalculateData();
t.RefreshDataOnOpeningFile = true;

foreach (int c in styles.Keys)
{
Style style = (Style)styles[c];
//Option 1: Calling PivotTable.Format
style.IsTextWrapped = true;
style.VerticalAlignment = TextAlignmentType.Top;
for (int r = hRow; r <= t.DataBodyRange.EndRow; r++)
{
t.Format(r, c, style);
}

//Option 2: Calling Range.ApplyStyle
Range range = sheet.Cells.CreateRange(hRow, c, (t.DataBodyRange.EndRow - t.DataBodyRange.StartRow) + 1, 1);
style.IsTextWrapped = true;
style.VerticalAlignment = TextAlignmentType.Top;
StyleFlag flag = new StyleFlag();
flag.WrapText = true;
flag.VerticalAlignment = true;
range.ApplyStyle(style, flag);
}
The code above was inserted around the code that refreshes the pivot table.

- With just Option 1 the output PDF will have wrap text on all pivot table columns. However the generated XLSX only has the first {Value} column text wrapped; the other {Value} columns are not wrapped. The Row Labels Columns seem to have the right forced styling in the XLSX output.
- With just Option 2 the output PDF will have wrap text on all pivot table columns. However the generated XLSX file does not have any text wrap on any of the {Value} columns. The Row Labels Columns does not have the forced styling in the XLSX output.
- When the pivot table is not set to refresh on opening, the pivot table in the output XLSX looks just like it does in the output PDF, but as soon as the pivot table is refreshed through MS Excel, the text wrap is lost on the last two {Values} columns, suggesting that the style is not really fully applied on the pivot table’s {Values} column.
4

Hi,

Thanks for your additional information and using Aspose.Cells.

We have checked your issue status from our database and we are glad to inform you that this issue has already been fixed. We will soon provide you a fix for that. Please spare us some time. Once, we will have some update/fix for you, we will let you know asap.

5

Hi,

That sounds great. Would you be able to confirm if this coming fix includes retaining the text wrap on the headers of the {Values} columns as well as the rest of the columns (data and totals row)?

6

Hi,

Thanks for your posting and using Aspose.Cells.

The upcoming fix will fix the text wrap issues. It will be general and hopefully it will fix the issues with text wrap on headers and rest of the columns. We will provide you a fix in next week. Once, we will have it available, we will let you know asap.

7

Hi,


Please download and try our latest version/fix : Aspose.Cells for .NET v7.5.3.4


We have fixed your issue now.

Let us know your feedback.


Thank you.



8

The issues you have found earlier (filed as CELLSNET-42030) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.