Pivot Table Automatic Collapse

Aspose.Cells Product Family
1

Hi,

We have been using your product for a while now and find it incredibly useful. The approach we have used so far is to create a template with an existing pivot table pointing to a dynamic data source. We then use smart markers to render out a flat file of sorts and when the report is generated and opened, the pivot table is automatically refreshed with the latest data.

The issue we're running into at the moment is that when the report is opened, the pivot table rows are automatically expanded and I was wondering if there was a simple'ish way to automatically collapse all rows so that the user sees the report at the highest level.

Any suggestions will be appreciated.

Thanks,

Mark Chandler.

This message was posted using Aspose.Live 2 Forum
2

Hi,

Could you post your sample template (containing smart markers for pivot table report) file and output file here, we will check your issue soon.

Thank you.

3

Hi,


Please find the 2 files attached.
Thanks!
4

Hi,

Thanks for the sample files.

I have logged it with an id: CELLSNET-21074. We will look into the issue as if we can collapse the data in the pivot report.

Thank you.

5

Hi,

Please try the attached version, your issue should be fixed now.

Thank you.

6

Hi,


This version doesn’t seem to be making a difference to the problem we were experiencing and is in fact removing the font and formatting of all the text within the pivot table.

Please advise.

Thanks.
7

Hi,

We will further look into your issue and get back to you soon.

Thank you.

8

Hi,


Any news on this?
At the moment, we’re just trying to find out if this is possible to fix as if it isn’t, we will need to re-design some of our reports for our client.

Thanks.
9

Hi,

We will get back to you tomorrow if we can provide some update about it.

Keep in touch.

Thank you.

10

Hi,


Thanks very much for your response, your ongoing support is greatly appreciated.
This issue has become high priority for us as our analysis has stopped mid-cycle to see if we can get this sorted so your urgency is greatly appreciated.

Thanks!
11

Hi,

Could you confirm that you find the issue regarding keeping the font and formatting of all the text within the
pivot table?
Hopefully we can provide you the fixed version within 2-3 working days.

Thank you.

12

Hi,


The issue isn’t really that the new version causes font and formatting issues, it’s that the new version didn’t fix the original problem, it just introduced the formatting issues. The spreadsheet still opens with the pivot table expanded.

Please advise.
13

Hi,

We did find the font formatting issue that would be fixed in the next fix. But for:

"The spreadsheet still opens with the pivot table expanded"

We could not find the issue, kindly do create a simple console demo application, zip it and post it here to show the issue, we will check your issue soon.

Thank you.

14

Hi,


The issue is that when the spreadsheet is generated, the fields are always expanded as per the attached “Sample Report.xlsx”, we would like an option to generate with all the fields collapsed as per the attached “Sample Report - As we want it.xlsx”. Is this possible?

Thanks.
15

Hi,

Please try the attached version, we have fixed the font formatting issue.

Thanks for the sample files.

Regarding your original issue/requirement, we will look into it and get back to you soon.

Thank you.

16

Hi,

For your original issue, we could not find it, all the fields are collapsed in the file using Aspose.Cells API (open/save etc. process).
The output file is attached, the sample code is given below:
Workbook workbook = new Workbook(@“Sample Report - As we want it.xlsx”);
workbook.Save(@“Sample Report_output.xlsx”);

By the way, how your spreadsheet is generated?

Thank you.

17

Hi,

We open a designer using -

FileStream inStream = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);

try

{

this.Designer.Workbook.Open(inStream);

}

finally

{

inStream.Close();

}

Once loaded, we then save the workbook to an outStream which we push to the response -

MemoryStream outStream = new MemoryStream();

try

{

this.Designer.Workbook.Save(outStream, FileFormatType.Excel2007Xlsx);

returnValue = outStream.ToArray();

}

finally

{

outStream.Close();

}

We use smart markers to populate a flat data set which we use as the pivot table's data source. We refresh the pivot table every time the spreadsheet is opened but the pivot table does stay minimized whenever we save and reopen, it's only on the initial open that all fields are expanded.

Hope this makes sense,

Mark.

18

Hi,

Well, the pivot table does stay minimized whenever we save and re-open it, it’s only the initial open that all fields are expanded. Do you want to expand /collapse the field using Aspose.Cells API?

Thanks for providing us details.

19

Yes, that's it. Is that possible?

Thanks.

20

Hi,

OK, we will analyze the feature and try to support it soon.

Thank you.