Default pivot table formatting seems to have changed

Aspose.Cells Product Family
1

We recently upgraded from 4.4.3.1 to 4.6 and have noticed that our pivot tables look quite a lot different.

In particular it seems that the default mode for the Row Fields has changed them from being laid out in a tabular form to an outline form.

We can make our reports look the old way by right clicking on each of the Row Field Headers and choosing “Field Settings” > “Layout” > “Show Items in Tabular Form” and also unchecking “Insert Blank Line”.

How can I do this in code? I can’t find the equivalent properties.

thanks
Graham

2

Hi,

Well, PivotTable's field's layout is in tubular form by default. Could you give us your test code with template files (one file containing pivot table created by older version and other with latest (4.6) version) and provide us the details to differentiate b/w the two. We will check it soon.

Thank you.

3

Amjad,

Thanks for the quick reply. I made a mistake in my original statement, the problem actually manifest itself when moving from 4.2 to 4.6.

Here’s some code that will create a pivot table that looks quite different under each version of aspose and i’ve attached the spreadsheets that the respective versions create.

The 4.6 version has a lot of additional blank rows and, while this isn’t very pronounced on this simple test case, when you have 5 or 6 fields in the row pivot, it adds up to a lot of wasted space.

For continuity we need our reports on 4.6 to look like the 4.2 reports - please let me know how to do this.

thanks,
Graham


public void CreatePivotBook()
{

Workbook wb = new Workbook();
wb.Worksheets.Add();

Worksheet data = wb.Worksheets[0];
int row = 0;

data.Cells[row, 0].PutValue(“Customer”);
data.Cells[row, 1].PutValue(“Sku”);
data.Cells[row, 2].PutValue(“Qty”);
Random r = new Random();
for (row=1;row<50;row++)
{
data.Cells[row, 0].PutValue(r.Next(3)>1 ? “Customer A”: “Customer B”);
data.Cells[row, 1].PutValue(r.Next(3) > 1 ? “Sku A” : “Sku B”);
data.Cells[row, 2].PutValue(r.Next(1,100));
}

Worksheet pivot = wb.Worksheets[1];

PivotTable pt = pivot.PivotTables[pivot.PivotTables.Add("=Sheet1!A1:C51", 0, 0, “Test pivot”)];


pt.EnableWizard = true;

pt.AddFieldToArea(PivotFieldType.Row, 0);
pt.RowFields[0].IsAutoSubtotals = false;

pt.AddFieldToArea(PivotFieldType.Row, 1);
pt.RowFields[1].IsAutoSubtotals = false;

pt.AddFieldToArea(PivotFieldType.Data, 2);

pt.IsAutoFormat = true;
pt.AutoFormatType = PivotTableAutoFormatType.Report5;

wb.Save(“c:\temp\test.xls”,FileFormatType.Excel2003);

}

4

Hi,

Thanks for providing us the template files.

After checking the two files you attached, we think the new version 4.6.0 works similar to MS Excel and it was actually a bug in the previous versions (e.g.., 4.2). Please check it in MS Excel manually, select PivotTable->Format report(on the Pivottable toolbar)->select Report 5, you will see some empty rows are inserted by default.

We will add a new property(PivotTable.HasBlankRows) to check whether to add blank rows into the pivot table or not. We can provide you the supported version (which will include this attribute) soon.

Thank you.

5

Thanks!

Can you also look into the outline vs tabular choice, because that looks different too

regards,
Graham

6

Hi,

Thank you for Considering Aspose.

Please try the attached Latest version of Aspose.Cells. Please use PivotTable.HasBlankRows to remove all blank rows.

See following codes:

Workbook wb = new Workbook();
wb.Worksheets.Add();

Worksheet data = wb.Worksheets[0];
int row = 0;

data.Cells[row, 0].PutValue("Customer");
data.Cells[row, 1].PutValue("Sku");
data.Cells[row, 2].PutValue("Qty");
Random r = new Random();
for (row = 1; row < 50; row++)
{
data.Cells[row, 0].PutValue(r.Next(3) > 1 ? "Customer A" : "Customer B");
data.Cells[row, 1].PutValue(r.Next(3) > 1 ? "Sku A" : "Sku B");
data.Cells[row, 2].PutValue(r.Next(1, 100));
}

Worksheet pivot = wb.Worksheets[1];

PivotTable pt = pivot.PivotTables[pivot.PivotTables.Add("=Sheet1!A1:C50", 0, 0, "Test pivot")];

pt.EnableWizard = true;

pt.AddFieldToArea(PivotFieldType.Row, 0);
pt.RowFields[0].IsAutoSubtotals = false;

pt.AddFieldToArea(PivotFieldType.Row, 1);
pt.RowFields[1].IsAutoSubtotals = false;

pt.AddFieldToArea(PivotFieldType.Data, 2);

pt.IsAutoFormat = true;
pt.AutoFormatType = PivotTableAutoFormatType.Report5;
pt.HasBlankRows = false;

wb.Save(@"C:\Test_156592.xls");

Thank You & Best Regards,