We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

InsertBlankRow does not work

case PivotFieldType.Row:
	PivotField pivotFieldRow = pivot.RowFields[pivot.RowFields.Count - 1];
	pivotFieldRow.ShowSubtotalAtTop = false;
	pivotFieldRow.InsertBlankRow = false;
	break;

ShowSubtotalAtTop works fine in the code.

But for InsertBlankRow it does not work.

After generating an excel Pivot table,
I right click in Excel at the PIVOT table and choose Layout & Print the
Checkbox [Insert blank line after each item label] stays unchanged.
What do I need to do to change this setting via the code?

Hi Ron,

Thanks for your posting and using Aspose.Cells.

Please download and use the latest version: Aspose.Cells
for .NET v8.2.0.3
it is working fine.

I have tested this issue with the following sample code and it checked the “Insert blank line after each item label” checkbox successfully.

I have attached the source Excel file used in this code, output Excel file generated by it and screenshot showing the output for your reference.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\sample.xlsx”;


Workbook wb = new Workbook(filePath);


Worksheet ws = wb.Worksheets[0];


PivotTable pt = ws.PivotTables[0];


pt.RowFields[0].InsertBlankRow = true;


wb.Save(filePath + “.out.xlsx”);

Hi Shakeel,


Thank you for your quick response.
Can you try to make it unchecked (false), because the default is checked?

That is not working.

Best Regards,
Ron van Mourik

Hi Ron,

Thanks for your posting and using Aspose.Cells.

It is working fine. I unchecked the PivotField using the following code and it was unchecked successfully. I have attached the source Excel file used in this code and the output Excel file generated by it for your reference.

If you open the source Excel file, you will see PivotField “Test3” is checked and if you open the output Excel file, you will see PivotField “Test3” is unchecked.

Kindly download and try the latest version: Aspose.Cells
for .NET v8.2.0.3
it should fix your issue.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\sample.xlsx”;


Workbook wb = new Workbook(filePath);


Worksheet ws = wb.Worksheets[0];


PivotTable pt = ws.PivotTables[0];


pt.RowFields[0].InsertBlankRow = false;


wb.Save(filePath + “.out.xlsx”);

Thank you,

We are using v8.1.0.0.
Was there a fix in the newer version?

Otherwise we need another new license.

Hi,

Thanks for your posting and using Aspose.Cells.

I have tried it with the v7.5.2.1 and v8.1.0.1 with the given code in the above post and it worked fine. I have attached the output files for your reference.

So, I think, this issue is not replicable with my source Excel file and this issue is occurring with your specific scenario and Excel file only

Could you please try the latest version: Aspose.Cells
for .NET v8.2.0.3
without setting your license (in evaluation mode) and check the results?

If your issue is still occurring, then we request you to kindly create a console application project with your source Excel file replicating this issue with the latest version, so that we could investigate it at our end. And if it is a bug, we will log it our database and fix it in our next versions.

Hi Shakeel,


It is not working.
I show you the example I’m using.

I’m doing something wrong, but what…


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Aspose.Cells;
using Aspose.Cells.Pivot;

namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
AsposeInitializer.Initialize();
}

	<span style="color:blue;">private</span> <span style="color:blue;">void</span> button1_Click(<span style="color:blue;">object</span> sender, <span style="color:#2b91af;">EventArgs</span> e)
	{
		<span style="color:#2b91af;">Workbook</span> workbook = <span style="color:blue;">new</span> <span style="color:#2b91af;">Workbook</span>();
		<span style="color:green;">//Obtaining the reference of the first worksheet</span>
		<span style="color:#2b91af;">Worksheet</span> sheet = workbook.Worksheets[0];
		<span style="color:green;">//Name the sheet</span>
		sheet.Name = <span style="color:#a31515;">"Data"</span>;
		<span style="color:#2b91af;">Cells</span> cells = sheet.Cells;

		AddData(cells);

		<span style="color:green;">//Adding a new sheet</span>
		<span style="color:#2b91af;">Worksheet</span> sheet2 = workbook.Worksheets[workbook.Worksheets.Add()];
		<span style="color:green;">//Naming the sheet</span>
		sheet2.Name = <span style="color:#a31515;">"PivotTable"</span>;
		<span style="color:green;">//Getting the pivottables collection in the sheet</span>
		Aspose.Cells.Pivot.<span style="color:#2b91af;">PivotTableCollection</span> pivotTables = sheet2.PivotTables;
		<span style="color:green;">//Adding a PivotTable to the worksheet</span>
		<span style="color:blue;">int</span> index = pivotTables.Add(<span style="color:#a31515;">"=Data!A1:F30"</span>, <span style="color:#a31515;">"B3"</span>, <span style="color:#a31515;">"PivotTable1"</span>);
		<span style="color:green;">//Accessing the instance of the newly added PivotTable</span>
		Aspose.Cells.Pivot.<span style="color:#2b91af;">PivotTable</span> pivotTable = pivotTables[index];
		<span style="color:green;">//Showing the grand totals</span>
		pivotTable.RowGrand = <span style="color:blue;">true</span>;
		pivotTable.ColumnGrand = <span style="color:blue;">true</span>;
		pivotTable.IsAutoFormat = <span style="color:blue;">true</span>;
		pivotTable.AutoFormatType = Aspose.Cells.Pivot.<span style="color:#2b91af;">PivotTableAutoFormatType</span>.Report6;
		pivotTable.AddFieldToArea(Aspose.Cells.Pivot.<span style="color:#2b91af;">PivotFieldType</span>.Row, 0);
		pivotTable.RowFields[0].InsertBlankRow = <span style="color:blue;">false</span>;
		pivotTable.AddFieldToArea(Aspose.Cells.Pivot.<span style="color:#2b91af;">PivotFieldType</span>.Row, 2);
		pivotTable.RowFields[1].InsertBlankRow = <span style="color:blue;">false</span>;
		pivotTable.AddFieldToArea(Aspose.Cells.Pivot.<span style="color:#2b91af;">PivotFieldType</span>.Row, 1);
		pivotTable.RowFields[2].InsertBlankRow = <span style="color:blue;">false</span>;
		pivotTable.AddFieldToArea(Aspose.Cells.Pivot.<span style="color:#2b91af;">PivotFieldType</span>.Column, 3);
		pivotTable.AddFieldToArea(Aspose.Cells.Pivot.<span style="color:#2b91af;">PivotFieldType</span>.Data, 5);
		pivotTable.DataFields[0].NumberFormat = <span style="color:#a31515;">"$#,##0.00"</span>;

		workbook.Save(<span style="color:#a31515;">@"c:\sample.xlsx"</span>);

		<span style="color:green;">//pt.RowFields[0].InsertBlankRow = false;</span>
	}

	<span style="color:blue;">private</span> <span style="color:blue;">static</span> <span style="color:blue;">void</span> AddData(<span style="color:#2b91af;">Cells</span> cells)
	{
		<span style="color:green;">//Setting the values to the cells</span>
		<span style="color:#2b91af;">Cell</span> cell = cells[<span style="color:#a31515;">"A1"</span>];
		cell.PutValue(<span style="color:#a31515;">"Employee"</span>);
		cell = cells[<span style="color:#a31515;">"B1"</span>];
		cell.PutValue(<span style="color:#a31515;">"Quarter"</span>);
		cell = cells[<span style="color:#a31515;">"C1"</span>];
		cell.PutValue(<span style="color:#a31515;">"Product"</span>);
		cell = cells[<span style="color:#a31515;">"D1"</span>];
		cell.PutValue(<span style="color:#a31515;">"Continent"</span>);
		cell = cells[<span style="color:#a31515;">"E1"</span>];
		cell.PutValue(<span style="color:#a31515;">"Country"</span>);
		cell = cells[<span style="color:#a31515;">"F1"</span>];
		cell.PutValue(<span style="color:#a31515;">"Sale"</span>);

		cell = cells[<span style="color:#a31515;">"A2"</span>];
		cell.PutValue(<span style="color:#a31515;">"David"</span>);
		cell = cells[<span style="color:#a31515;">"A3"</span>];
		cell.PutValue(<span style="color:#a31515;">"David"</span>);
		cell = cells[<span style="color:#a31515;">"A4"</span>];
		cell.PutValue(<span style="color:#a31515;">"David"</span>);
		cell = cells[<span style="color:#a31515;">"A5"</span>];
		cell.PutValue(<span style="color:#a31515;">"David"</span>);
		cell = cells[<span style="color:#a31515;">"A6"</span>];
		cell.PutValue(<span style="color:#a31515;">"James"</span>);
		cell = cells[<span style="color:#a31515;">"A7"</span>];
		cell.PutValue(<span style="color:#a31515;">"James"</span>);
		cell = cells[<span style="color:#a31515;">"A8"</span>];
		cell.PutValue(<span style="color:#a31515;">"James"</span>);
		cell = cells[<span style="color:#a31515;">"A9"</span>];
		cell.PutValue(<span style="color:#a31515;">"James"</span>);
		cell = cells[<span style="color:#a31515;">"A10"</span>];
		cell.PutValue(<span style="color:#a31515;">"James"</span>);
		cell = cells[<span style="color:#a31515;">"A11"</span>];
		cell.PutValue(<span style="color:#a31515;">"Miya"</span>);
		cell = cells[<span style="color:#a31515;">"A12"</span>];
		cell.PutValue(<span style="color:#a31515;">"Miya"</span>);
		cell = cells[<span style="color:#a31515;">"A13"</span>];
		cell.PutValue(<span style="color:#a31515;">"Miya"</span>);
		cell = cells[<span style="color:#a31515;">"A14"</span>];
		cell.PutValue(<span style="color:#a31515;">"Miya"</span>);
		cell = cells[<span style="color:#a31515;">"A15"</span>];
		cell.PutValue(<span style="color:#a31515;">"Miya"</span>);
		cell = cells[<span style="color:#a31515;">"A16"</span>];
		cell.PutValue(<span style="color:#a31515;">"Miya"</span>);
		cell = cells[<span style="color:#a31515;">"A17"</span>];
		cell.PutValue(<span style="color:#a31515;">"Miya"</span>);
		cell = cells[<span style="color:#a31515;">"A18"</span>];
		cell.PutValue(<span style="color:#a31515;">"Elvis"</span>);
		cell = cells[<span style="color:#a31515;">"A19"</span>];
		cell.PutValue(<span style="color:#a31515;">"Elvis"</span>);
		cell = cells[<span style="color:#a31515;">"A20"</span>];
		cell.PutValue(<span style="color:#a31515;">"Elvis"</span>);
		cell = cells[<span style="color:#a31515;">"A21"</span>];
		cell.PutValue(<span style="color:#a31515;">"Elvis"</span>);
		cell = cells[<span style="color:#a31515;">"A22"</span>];
		cell.PutValue(<span style="color:#a31515;">"Elvis"</span>);
		cell = cells[<span style="color:#a31515;">"A23"</span>];
		cell.PutValue(<span style="color:#a31515;">"Elvis"</span>);
		cell = cells[<span style="color:#a31515;">"A24"</span>];
		cell.PutValue(<span style="color:#a31515;">"Elvis"</span>);
		cell = cells[<span style="color:#a31515;">"A25"</span>];
		cell.PutValue(<span style="color:#a31515;">"Jean"</span>);
		cell = cells[<span style="color:#a31515;">"A26"</span>];
		cell.PutValue(<span style="color:#a31515;">"Jean"</span>);
		cell = cells[<span style="color:#a31515;">"A27"</span>];
		cell.PutValue(<span style="color:#a31515;">"Jean"</span>);
		cell = cells[<span style="color:#a31515;">"A28"</span>];
		cell.PutValue(<span style="color:#a31515;">"Ada"</span>);
		cell = cells[<span style="color:#a31515;">"A29"</span>];
		cell.PutValue(<span style="color:#a31515;">"Ada"</span>);
		cell = cells[<span style="color:#a31515;">"A30"</span>];
		cell.PutValue(<span style="color:#a31515;">"Ada"</span>);

		cell = cells[<span style="color:#a31515;">"B2"</span>];
		cell.PutValue(<span style="color:#a31515;">"1"</span>);
		cell = cells[<span style="color:#a31515;">"B3"</span>];
		cell.PutValue(<span style="color:#a31515;">"2"</span>);
		cell = cells[<span style="color:#a31515;">"B4"</span>];
		cell.PutValue(<span style="color:#a31515;">"3"</span>);
		cell = cells[<span style="color:#a31515;">"B5"</span>];
		cell.PutValue(<span style="color:#a31515;">"4"</span>);
		cell = cells[<span style="color:#a31515;">"B6"</span>];
		cell.PutValue(<span style="color:#a31515;">"1"</span>);
		cell = cells[<span style="color:#a31515;">"B7"</span>];
		cell.PutValue(<span style="color:#a31515;">"2"</span>);
		cell = cells[<span style="color:#a31515;">"B8"</span>];
		cell.PutValue(<span style="color:#a31515;">"3"</span>);
		cell = cells[<span style="color:#a31515;">"B9"</span>];
		cell.PutValue(<span style="color:#a31515;">"4"</span>);
		cell = cells[<span style="color:#a31515;">"B10"</span>];
		cell.PutValue(<span style="color:#a31515;">"4"</span>);
		cell = cells[<span style="color:#a31515;">"B11"</span>];
		cell.PutValue(<span style="color:#a31515;">"1"</span>);
		cell = cells[<span style="color:#a31515;">"B12"</span>];
		cell.PutValue(<span style="color:#a31515;">"1"</span>);
		cell = cells[<span style="color:#a31515;">"B13"</span>];
		cell.PutValue(<span style="color:#a31515;">"2"</span>);
		cell = cells[<span style="color:#a31515;">"B14"</span>];
		cell.PutValue(<span style="color:#a31515;">"2"</span>);
		cell = cells[<span style="color:#a31515;">"B15"</span>];
		cell.PutValue(<span style="color:#a31515;">"3"</span>);
		cell = cells[<span style="color:#a31515;">"B16"</span>];
		cell.PutValue(<span style="color:#a31515;">"4"</span>);
		cell = cells[<span style="color:#a31515;">"B17"</span>];
		cell.PutValue(<span style="color:#a31515;">"4"</span>);
		cell = cells[<span style="color:#a31515;">"B18"</span>];
		cell.PutValue(<span style="color:#a31515;">"1"</span>);
		cell = cells[<span style="color:#a31515;">"B19"</span>];
		cell.PutValue(<span style="color:#a31515;">"1"</span>);
		cell = cells[<span style="color:#a31515;">"B20"</span>];
		cell.PutValue(<span style="color:#a31515;">"2"</span>);
		cell = cells[<span style="color:#a31515;">"B21"</span>];
		cell.PutValue(<span style="color:#a31515;">"3"</span>);
		cell = cells[<span style="color:#a31515;">"B22"</span>];
		cell.PutValue(<span style="color:#a31515;">"3"</span>);
		cell = cells[<span style="color:#a31515;">"B23"</span>];
		cell.PutValue(<span style="color:#a31515;">"4"</span>);
		cell = cells[<span style="color:#a31515;">"B24"</span>];
		cell.PutValue(<span style="color:#a31515;">"4"</span>);
		cell = cells[<span style="color:#a31515;">"B25"</span>];
		cell.PutValue(<span style="color:#a31515;">"1"</span>);
		cell = cells[<span style="color:#a31515;">"B26"</span>];
		cell.PutValue(<span style="color:#a31515;">"2"</span>);
		cell = cells[<span style="color:#a31515;">"B27"</span>];
		cell.PutValue(<span style="color:#a31515;">"3"</span>);
		cell = cells[<span style="color:#a31515;">"B28"</span>];
		cell.PutValue(<span style="color:#a31515;">"1"</span>);
		cell = cells[<span style="color:#a31515;">"B29"</span>];
		cell.PutValue(<span style="color:#a31515;">"2"</span>);
		cell = cells[<span style="color:#a31515;">"B30"</span>];
		cell.PutValue(<span style="color:#a31515;">"3"</span>);

		cell = cells[<span style="color:#a31515;">"C2"</span>];
		cell.PutValue(<span style="color:#a31515;">"Maxilaku"</span>);
		cell = cells[<span style="color:#a31515;">"C3"</span>];
		cell.PutValue(<span style="color:#a31515;">"Maxilaku"</span>);
		cell = cells[<span style="color:#a31515;">"C4"</span>];
		cell.PutValue(<span style="color:#a31515;">"Chai"</span>);
		cell = cells[<span style="color:#a31515;">"C5"</span>];
		cell.PutValue(<span style="color:#a31515;">"Maxilaku"</span>);
		cell = cells[<span style="color:#a31515;">"C6"</span>];
		cell.PutValue(<span style="color:#a31515;">"Chang"</span>);
		cell = cells[<span style="color:#a31515;">"C7"</span>];
		cell.PutValue(<span style="color:#a31515;">"Chang"</span>);
		cell = cells[<span style="color:#a31515;">"C8"</span>];
		cell.PutValue(<span style="color:#a31515;">"Chang"</span>);
		cell = cells[<span style="color:#a31515;">"C9"</span>];
		cell.PutValue(<span style="color:#a31515;">"Chang"</span>);
		cell = cells[<span style="color:#a31515;">"C10"</span>];
		cell.PutValue(<span style="color:#a31515;">"Chang"</span>);
		cell = cells[<span style="color:#a31515;">"C11"</span>];
		cell.PutValue(<span style="color:#a31515;">"Geitost"</span>);
		cell = cells[<span style="color:#a31515;">"C12"</span>];
		cell.PutValue(<span style="color:#a31515;">"Chai"</span>);
		cell = cells[<span style="color:#a31515;">"C13"</span>];
		cell.PutValue(<span style="color:#a31515;">"Geitost"</span>);
		cell = cells[<span style="color:#a31515;">"C14"</span>];
		cell.PutValue(<span style="color:#a31515;">"Geitost"</span>);
		cell = cells[<span style="color:#a31515;">"C15"</span>];
		cell.PutValue(<span style="color:#a31515;">"Maxilaku"</span>);
		cell = cells[<span style="color:#a31515;">"C16"</span>];
		cell.PutValue(<span style="color:#a31515;">"Geitost"</span>);
		cell = cells[<span style="color:#a31515;">"C17"</span>];
		cell.PutValue(<span style="color:#a31515;">"Geitost"</span>);
		cell = cells[<span style="color:#a31515;">"C18"</span>];
		cell.PutValue(<span style="color:#a31515;">"Ikuru"</span>);
		cell = cells[<span style="color:#a31515;">"C19"</span>];
		cell.PutValue(<span style="color:#a31515;">"Ikuru"</span>);
		cell = cells[<span style="color:#a31515;">"C20"</span>];
		cell.PutValue(<span style="color:#a31515;">"Ikuru"</span>);
		cell = cells[<span style="color:#a31515;">"C21"</span>];
		cell.PutValue(<span style="color:#a31515;">"Ikuru"</span>);
		cell = cells[<span style="color:#a31515;">"C22"</span>];
		cell.PutValue(<span style="color:#a31515;">"Ipoh Coffee"</span>);
		cell = cells[<span style="color:#a31515;">"C23"</span>];
		cell.PutValue(<span style="color:#a31515;">"Ipoh Coffee"</span>);
		cell = cells[<span style="color:#a31515;">"C24"</span>];
		cell.PutValue(<span style="color:#a31515;">"Ipoh Coffee"</span>);
		cell = cells[<span style="color:#a31515;">"C25"</span>];
		cell.PutValue(<span style="color:#a31515;">"Chocolade"</span>);
		cell = cells[<span style="color:#a31515;">"C26"</span>];
		cell.PutValue(<span style="color:#a31515;">"Chocolade"</span>);
		cell = cells[<span style="color:#a31515;">"C27"</span>];
		cell.PutValue(<span style="color:#a31515;">"Chocolade"</span>);
		cell = cells[<span style="color:#a31515;">"C28"</span>];
		cell.PutValue(<span style="color:#a31515;">"Chocolade"</span>);
		cell = cells[<span style="color:#a31515;">"C29"</span>];
		cell.PutValue(<span style="color:#a31515;">"Chocolade"</span>);
		cell = cells[<span style="color:#a31515;">"C30"</span>];
		cell.PutValue(<span style="color:#a31515;">"Chocolade"</span>);

		cell = cells[<span style="color:#a31515;">"D2"</span>];
		cell.PutValue(<span style="color:#a31515;">"Asia"</span>);
		cell = cells[<span style="color:#a31515;">"D3"</span>];
		cell.PutValue(<span style="color:#a31515;">"Asia"</span>);
		cell = cells[<span style="color:#a31515;">"D4"</span>];
		cell.PutValue(<span style="color:#a31515;">"Asia"</span>);
		cell = cells[<span style="color:#a31515;">"D5"</span>];
		cell.PutValue(<span style="color:#a31515;">"Asia"</span>);
		cell = cells[<span style="color:#a31515;">"D6"</span>];
		cell.PutValue(<span style="color:#a31515;">"Europe"</span>);
		cell = cells[<span style="color:#a31515;">"D7"</span>];
		cell.PutValue(<span style="color:#a31515;">"Europe"</span>);
		cell = cells[<span style="color:#a31515;">"D8"</span>];
		cell.PutValue(<span style="color:#a31515;">"Europe"</span>);
		cell = cells[<span style="color:#a31515;">"D9"</span>];
		cell.PutValue(<span style="color:#a31515;">"Europe"</span>);
		cell = cells[<span style="color:#a31515;">"D10"</span>];
		cell.PutValue(<span style="color:#a31515;">"Europe"</span>);
		cell = cells[<span style="color:#a31515;">"D11"</span>];
		cell.PutValue(<span style="color:#a31515;">"America"</span>);
		cell = cells[<span style="color:#a31515;">"D12"</span>];
		cell.PutValue(<span style="color:#a31515;">"America"</span>);
		cell = cells[<span style="color:#a31515;">"D13"</span>];
		cell.PutValue(<span style="color:#a31515;">"America"</span>);
		cell = cells[<span style="color:#a31515;">"D14"</span>];
		cell.PutValue(<span style="color:#a31515;">"America"</span>);
		cell = cells[<span style="color:#a31515;">"D15"</span>];
		cell.PutValue(<span style="color:#a31515;">"America"</span>);
		cell = cells[<span style="color:#a31515;">"D16"</span>];
		cell.PutValue(<span style="color:#a31515;">"America"</span>);
		cell = cells[<span style="color:#a31515;">"D17"</span>];
		cell.PutValue(<span style="color:#a31515;">"America"</span>);
		cell = cells[<span style="color:#a31515;">"D18"</span>];
		cell.PutValue(<span style="color:#a31515;">"Europe"</span>);
		cell = cells[<span style="color:#a31515;">"D19"</span>];
		cell.PutValue(<span style="color:#a31515;">"Europe"</span>);
		cell = cells[<span style="color:#a31515;">"D20"</span>];
		cell.PutValue(<span style="color:#a31515;">"Europe"</span>);
		cell = cells[<span style="color:#a31515;">"D21"</span>];
		cell.PutValue(<span style="color:#a31515;">"Oceania"</span>);
		cell = cells[<span style="color:#a31515;">"D22"</span>];
		cell.PutValue(<span style="color:#a31515;">"Oceania"</span>);
		cell = cells[<span style="color:#a31515;">"D23"</span>];
		cell.PutValue(<span style="color:#a31515;">"Oceania"</span>);
		cell = cells[<span style="color:#a31515;">"D24"</span>];
		cell.PutValue(<span style="color:#a31515;">"Oceania"</span>);
		cell = cells[<span style="color:#a31515;">"D25"</span>];
		cell.PutValue(<span style="color:#a31515;">"Africa"</span>);
		cell = cells[<span style="color:#a31515;">"D26"</span>];
		cell.PutValue(<span style="color:#a31515;">"Africa"</span>);
		cell = cells[<span style="color:#a31515;">"D27"</span>];
		cell.PutValue(<span style="color:#a31515;">"Africa"</span>);
		cell = cells[<span style="color:#a31515;">"D28"</span>];
		cell.PutValue(<span style="color:#a31515;">"Africa"</span>);
		cell = cells[<span style="color:#a31515;">"D29"</span>];
		cell.PutValue(<span style="color:#a31515;">"Africa"</span>);
		cell = cells[<span style="color:#a31515;">"D30"</span>];
		cell.PutValue(<span style="color:#a31515;">"Africa"</span>);

		cell = cells[<span style="color:#a31515;">"E2"</span>];
		cell.PutValue(<span style="color:#a31515;">"China"</span>);
		cell = cells[<span style="color:#a31515;">"E3"</span>];
		cell.PutValue(<span style="color:#a31515;">"India"</span>);
		cell = cells[<span style="color:#a31515;">"E4"</span>];
		cell.PutValue(<span style="color:#a31515;">"Korea"</span>);
		cell = cells[<span style="color:#a31515;">"E5"</span>];
		cell.PutValue(<span style="color:#a31515;">"India"</span>);
		cell = cells[<span style="color:#a31515;">"E6"</span>];
		cell.PutValue(<span style="color:#a31515;">"France"</span>);
		cell = cells[<span style="color:#a31515;">"E7"</span>];
		cell.PutValue(<span style="color:#a31515;">"France"</span>);
		cell = cells[<span style="color:#a31515;">"E8"</span>];
		cell.PutValue(<span style="color:#a31515;">"Germany"</span>);
		cell = cells[<span style="color:#a31515;">"E9"</span>];
		cell.PutValue(<span style="color:#a31515;">"Italy"</span>);
		cell = cells[<span style="color:#a31515;">"E10"</span>];
		cell.PutValue(<span style="color:#a31515;">"France"</span>);
		cell = cells[<span style="color:#a31515;">"E11"</span>];
		cell.PutValue(<span style="color:#a31515;">"U.S."</span>);
		cell = cells[<span style="color:#a31515;">"E12"</span>];
		cell.PutValue(<span style="color:#a31515;">"U.S."</span>);
		cell = cells[<span style="color:#a31515;">"E13"</span>];
		cell.PutValue(<span style="color:#a31515;">"Brazil"</span>);
		cell = cells[<span style="color:#a31515;">"E14"</span>];
		cell.PutValue(<span style="color:#a31515;">"U.S."</span>);
		cell = cells[<span style="color:#a31515;">"E15"</span>];
		cell.PutValue(<span style="color:#a31515;">"U.S."</span>);
		cell = cells[<span style="color:#a31515;">"E16"</span>];
		cell.PutValue(<span style="color:#a31515;">"Canada"</span>);
		cell = cells[<span style="color:#a31515;">"E17"</span>];
		cell.PutValue(<span style="color:#a31515;">"U.S."</span>);
		cell = cells[<span style="color:#a31515;">"E18"</span>];
		cell.PutValue(<span style="color:#a31515;">"Italy"</span>);
		cell = cells[<span style="color:#a31515;">"E19"</span>];
		cell.PutValue(<span style="color:#a31515;">"France"</span>);
		cell = cells[<span style="color:#a31515;">"E20"</span>];
		cell.PutValue(<span style="color:#a31515;">"Italy"</span>);
		cell = cells[<span style="color:#a31515;">"E21"</span>];
		cell.PutValue(<span style="color:#a31515;">"New Zealand"</span>);
		cell = cells[<span style="color:#a31515;">"E22"</span>];
		cell.PutValue(<span style="color:#a31515;">"Australia"</span>);
		cell = cells[<span style="color:#a31515;">"E23"</span>];
		cell.PutValue(<span style="color:#a31515;">"Australia"</span>);
		cell = cells[<span style="color:#a31515;">"E24"</span>];
		cell.PutValue(<span style="color:#a31515;">"New Zealand"</span>);
		cell = cells[<span style="color:#a31515;">"E25"</span>];
		cell.PutValue(<span style="color:#a31515;">"S.Africa"</span>);
		cell = cells[<span style="color:#a31515;">"E26"</span>];
		cell.PutValue(<span style="color:#a31515;">"S.Africa"</span>);
		cell = cells[<span style="color:#a31515;">"E27"</span>];
		cell.PutValue(<span style="color:#a31515;">"S.Africa"</span>);
		cell = cells[<span style="color:#a31515;">"E28"</span>];
		cell.PutValue(<span style="color:#a31515;">"Egypt"</span>);
		cell = cells[<span style="color:#a31515;">"E29"</span>];
		cell.PutValue(<span style="color:#a31515;">"Egypt"</span>);
		cell = cells[<span style="color:#a31515;">"E30"</span>];
		cell.PutValue(<span style="color:#a31515;">"Egypt"</span>);

		cell = cells[<span style="color:#a31515;">"F2"</span>];
		cell.PutValue(2000);
		cell = cells[<span style="color:#a31515;">"F3"</span>];
		cell.PutValue(500);
		cell = cells[<span style="color:#a31515;">"F4"</span>];
		cell.PutValue(1200);
		cell = cells[<span style="color:#a31515;">"F5"</span>];
		cell.PutValue(1500);
		cell = cells[<span style="color:#a31515;">"F6"</span>];
		cell.PutValue(500);
		cell = cells[<span style="color:#a31515;">"F7"</span>];
		cell.PutValue(1500);
		cell = cells[<span style="color:#a31515;">"F8"</span>];
		cell.PutValue(800);
		cell = cells[<span style="color:#a31515;">"F9"</span>];
		cell.PutValue(900);
		cell = cells[<span style="color:#a31515;">"F10"</span>];
		cell.PutValue(500);
		cell = cells[<span style="color:#a31515;">"F11"</span>];
		cell.PutValue(1600);
		cell = cells[<span style="color:#a31515;">"F12"</span>];
		cell.PutValue(600);
		cell = cells[<span style="color:#a31515;">"F13"</span>];
		cell.PutValue(2000);
		cell = cells[<span style="color:#a31515;">"F14"</span>];
		cell.PutValue(500);
		cell = cells[<span style="color:#a31515;">"F15"</span>];
		cell.PutValue(900);
		cell = cells[<span style="color:#a31515;">"F16"</span>];
		cell.PutValue(700);
		cell = cells[<span style="color:#a31515;">"F17"</span>];
		cell.PutValue(1400);
		cell = cells[<span style="color:#a31515;">"F18"</span>];
		cell.PutValue(1350);
		cell = cells[<span style="color:#a31515;">"F19"</span>];
		cell.PutValue(300);
		cell = cells[<span style="color:#a31515;">"F20"</span>];
		cell.PutValue(500);
		cell = cells[<span style="color:#a31515;">"F21"</span>];
		cell.PutValue(1000);
		cell = cells[<span style="color:#a31515;">"F22"</span>];
		cell.PutValue(1500);
		cell = cells[<span style="color:#a31515;">"F23"</span>];
		cell.PutValue(1500);
		cell = cells[<span style="color:#a31515;">"F24"</span>];
		cell.PutValue(1600);
		cell = cells[<span style="color:#a31515;">"F25"</span>];
		cell.PutValue(1000);
		cell = cells[<span style="color:#a31515;">"F26"</span>];
		cell.PutValue(1200);
		cell = cells[<span style="color:#a31515;">"F27"</span>];
		cell.PutValue(1300);
		cell = cells[<span style="color:#a31515;">"F28"</span>];
		cell.PutValue(1500);
		cell = cells[<span style="color:#a31515;">"F29"</span>];
		cell.PutValue(1400);
		cell = cells[<span style="color:#a31515;">"F30"</span>];
		cell.PutValue(1000);
	}
}

}




Hi Ron,

Thanks for your sample code and using Aspose.Cells.

I did not find any problem with your bug, so it is actually a bug which should be fixed. I have attached the output xlsx file generated by your code and screenshot highlighting this issue for a reference.

We have logged this issue in our database. We will look into it and fix 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-42994 - Insert blank line… check box should be unchecked

Hi Ron,

Thanks for using Aspose.Cells.

You need to change the order of the operations.

So please change your following code

pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Report6;
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 0);
pivotTable.RowFields[0].InsertBlankRow = false;
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 2);
pivotTable.RowFields[1].InsertBlankRow = false;
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 1);
pivotTable.RowFields[2].InsertBlankRow = false;

into following

pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Report6;
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 0);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 2);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 1);
pivotTable.RowFields[0].InsertBlankRow = false;
pivotTable.RowFields[1].InsertBlankRow = false;
pivotTable.RowFields[2].InsertBlankRow = false

Here is complete code for your reference. I have attached the output xlsx file generated by it for you to view.

C#

private void CreatePivotTable()

{

Workbook workbook = new Workbook();

//Obtaining the reference of the first worksheet

Worksheet sheet = workbook.Worksheets[0];

//Name the sheet

sheet.Name = “Data”;

Cells cells = sheet.Cells;


AddData(cells);


//Adding a new sheet

Worksheet sheet2 = workbook.Worksheets[workbook.Worksheets.Add()];

//Naming the sheet

sheet2.Name = “PivotTable”;

//Getting the pivottables collection in the sheet

Aspose.Cells.Pivot.PivotTableCollection pivotTables = sheet2.PivotTables;

//Adding a PivotTable to the worksheet

int index = pivotTables.Add("=Data!A1:F30", “B3”, “PivotTable1”);

//Accessing the instance of the newly added PivotTable

Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];

//Showing the grand totals

pivotTable.RowGrand = true;

pivotTable.ColumnGrand = true;

pivotTable.IsAutoFormat = true;


//change codes order

//If the AutoFormatType of PivotTable is PivotTableAutoFormatType.Report6,

//the InsertBlankRow of PivotFiled in RowFields will be changed when calling

//PivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, position index variable)


//pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Report6;

//pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 0);

//pivotTable.RowFields[0].InsertBlankRow = false;

//pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 2);

//pivotTable.RowFields[1].InsertBlankRow = false;

//pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 1);

//pivotTable.RowFields[2].InsertBlankRow = false;


pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Report6;

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 0);

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 2);

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 1);

pivotTable.RowFields[0].InsertBlankRow = false;

pivotTable.RowFields[1].InsertBlankRow = false;

pivotTable.RowFields[2].InsertBlankRow = false;


pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 3);

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 5);

pivotTable.DataFields[0].NumberFormat = “$#,##0.00”;


workbook.Save(@“output.xlsx”);



}


private static void AddData(Cells cells)

{

//Setting the values to the cells

Cell cell = cells[“A1”];

cell.PutValue(“Employee”);

cell = cells[“B1”];

cell.PutValue(“Quarter”);

cell = cells[“C1”];

cell.PutValue(“Product”);

cell = cells[“D1”];

cell.PutValue(“Continent”);

cell = cells[“E1”];

cell.PutValue(“Country”);

cell = cells[“F1”];

cell.PutValue(“Sale”);


cell = cells[“A2”];

cell.PutValue(“David”);

cell = cells[“A3”];

cell.PutValue(“David”);

cell = cells[“A4”];

cell.PutValue(“David”);

cell = cells[“A5”];

cell.PutValue(“David”);

cell = cells[“A6”];

cell.PutValue(“James”);

cell = cells[“A7”];

cell.PutValue(“James”);

cell = cells[“A8”];

cell.PutValue(“James”);

cell = cells[“A9”];

cell.PutValue(“James”);

cell = cells[“A10”];

cell.PutValue(“James”);

cell = cells[“A11”];

cell.PutValue(“Miya”);

cell = cells[“A12”];

cell.PutValue(“Miya”);

cell = cells[“A13”];

cell.PutValue(“Miya”);

cell = cells[“A14”];

cell.PutValue(“Miya”);

cell = cells[“A15”];

cell.PutValue(“Miya”);

cell = cells[“A16”];

cell.PutValue(“Miya”);

cell = cells[“A17”];

cell.PutValue(“Miya”);

cell = cells[“A18”];

cell.PutValue(“Elvis”);

cell = cells[“A19”];

cell.PutValue(“Elvis”);

cell = cells[“A20”];

cell.PutValue(“Elvis”);

cell = cells[“A21”];

cell.PutValue(“Elvis”);

cell = cells[“A22”];

cell.PutValue(“Elvis”);

cell = cells[“A23”];

cell.PutValue(“Elvis”);

cell = cells[“A24”];

cell.PutValue(“Elvis”);

cell = cells[“A25”];

cell.PutValue(“Jean”);

cell = cells[“A26”];

cell.PutValue(“Jean”);

cell = cells[“A27”];

cell.PutValue(“Jean”);

cell = cells[“A28”];

cell.PutValue(“Ada”);

cell = cells[“A29”];

cell.PutValue(“Ada”);

cell = cells[“A30”];

cell.PutValue(“Ada”);


cell = cells[“B2”];

cell.PutValue(“1”);

cell = cells[“B3”];

cell.PutValue(“2”);

cell = cells[“B4”];

cell.PutValue(“3”);

cell = cells[“B5”];

cell.PutValue(“4”);

cell = cells[“B6”];

cell.PutValue(“1”);

cell = cells[“B7”];

cell.PutValue(“2”);

cell = cells[“B8”];

cell.PutValue(“3”);

cell = cells[“B9”];

cell.PutValue(“4”);

cell = cells[“B10”];

cell.PutValue(“4”);

cell = cells[“B11”];

cell.PutValue(“1”);

cell = cells[“B12”];

cell.PutValue(“1”);

cell = cells[“B13”];

cell.PutValue(“2”);

cell = cells[“B14”];

cell.PutValue(“2”);

cell = cells[“B15”];

cell.PutValue(“3”);

cell = cells[“B16”];

cell.PutValue(“4”);

cell = cells[“B17”];

cell.PutValue(“4”);

cell = cells[“B18”];

cell.PutValue(“1”);

cell = cells[“B19”];

cell.PutValue(“1”);

cell = cells[“B20”];

cell.PutValue(“2”);

cell = cells[“B21”];

cell.PutValue(“3”);

cell = cells[“B22”];

cell.PutValue(“3”);

cell = cells[“B23”];

cell.PutValue(“4”);

cell = cells[“B24”];

cell.PutValue(“4”);

cell = cells[“B25”];

cell.PutValue(“1”);

cell = cells[“B26”];

cell.PutValue(“2”);

cell = cells[“B27”];

cell.PutValue(“3”);

cell = cells[“B28”];

cell.PutValue(“1”);

cell = cells[“B29”];

cell.PutValue(“2”);

cell = cells[“B30”];

cell.PutValue(“3”);


cell = cells[“C2”];

cell.PutValue(“Maxilaku”);

cell = cells[“C3”];

cell.PutValue(“Maxilaku”);

cell = cells[“C4”];

cell.PutValue(“Chai”);

cell = cells[“C5”];

cell.PutValue(“Maxilaku”);

cell = cells[“C6”];

cell.PutValue(“Chang”);

cell = cells[“C7”];

cell.PutValue(“Chang”);

cell = cells[“C8”];

cell.PutValue(“Chang”);

cell = cells[“C9”];

cell.PutValue(“Chang”);

cell = cells[“C10”];

cell.PutValue(“Chang”);

cell = cells[“C11”];

cell.PutValue(“Geitost”);

cell = cells[“C12”];

cell.PutValue(“Chai”);

cell = cells[“C13”];

cell.PutValue(“Geitost”);

cell = cells[“C14”];

cell.PutValue(“Geitost”);

cell = cells[“C15”];

cell.PutValue(“Maxilaku”);

cell = cells[“C16”];

cell.PutValue(“Geitost”);

cell = cells[“C17”];

cell.PutValue(“Geitost”);

cell = cells[“C18”];

cell.PutValue(“Ikuru”);

cell = cells[“C19”];

cell.PutValue(“Ikuru”);

cell = cells[“C20”];

cell.PutValue(“Ikuru”);

cell = cells[“C21”];

cell.PutValue(“Ikuru”);

cell = cells[“C22”];

cell.PutValue(“Ipoh Coffee”);

cell = cells[“C23”];

cell.PutValue(“Ipoh Coffee”);

cell = cells[“C24”];

cell.PutValue(“Ipoh Coffee”);

cell = cells[“C25”];

cell.PutValue(“Chocolade”);

cell = cells[“C26”];

cell.PutValue(“Chocolade”);

cell = cells[“C27”];

cell.PutValue(“Chocolade”);

cell = cells[“C28”];

cell.PutValue(“Chocolade”);

cell = cells[“C29”];

cell.PutValue(“Chocolade”);

cell = cells[“C30”];

cell.PutValue(“Chocolade”);


cell = cells[“D2”];

cell.PutValue(“Asia”);

cell = cells[“D3”];

cell.PutValue(“Asia”);

cell = cells[“D4”];

cell.PutValue(“Asia”);

cell = cells[“D5”];

cell.PutValue(“Asia”);

cell = cells[“D6”];

cell.PutValue(“Europe”);

cell = cells[“D7”];

cell.PutValue(“Europe”);

cell = cells[“D8”];

cell.PutValue(“Europe”);

cell = cells[“D9”];

cell.PutValue(“Europe”);

cell = cells[“D10”];

cell.PutValue(“Europe”);

cell = cells[“D11”];

cell.PutValue(“America”);

cell = cells[“D12”];

cell.PutValue(“America”);

cell = cells[“D13”];

cell.PutValue(“America”);

cell = cells[“D14”];

cell.PutValue(“America”);

cell = cells[“D15”];

cell.PutValue(“America”);

cell = cells[“D16”];

cell.PutValue(“America”);

cell = cells[“D17”];

cell.PutValue(“America”);

cell = cells[“D18”];

cell.PutValue(“Europe”);

cell = cells[“D19”];

cell.PutValue(“Europe”);

cell = cells[“D20”];

cell.PutValue(“Europe”);

cell = cells[“D21”];

cell.PutValue(“Oceania”);

cell = cells[“D22”];

cell.PutValue(“Oceania”);

cell = cells[“D23”];

cell.PutValue(“Oceania”);

cell = cells[“D24”];

cell.PutValue(“Oceania”);

cell = cells[“D25”];

cell.PutValue(“Africa”);

cell = cells[“D26”];

cell.PutValue(“Africa”);

cell = cells[“D27”];

cell.PutValue(“Africa”);

cell = cells[“D28”];

cell.PutValue(“Africa”);

cell = cells[“D29”];

cell.PutValue(“Africa”);

cell = cells[“D30”];

cell.PutValue(“Africa”);


cell = cells[“E2”];

cell.PutValue(“China”);

cell = cells[“E3”];

cell.PutValue(“India”);

cell = cells[“E4”];

cell.PutValue(“Korea”);

cell = cells[“E5”];

cell.PutValue(“India”);

cell = cells[“E6”];

cell.PutValue(“France”);

cell = cells[“E7”];

cell.PutValue(“France”);

cell = cells[“E8”];

cell.PutValue(“Germany”);

cell = cells[“E9”];

cell.PutValue(“Italy”);

cell = cells[“E10”];

cell.PutValue(“France”);

cell = cells[“E11”];

cell.PutValue(“U.S.”);

cell = cells[“E12”];

cell.PutValue(“U.S.”);

cell = cells[“E13”];

cell.PutValue(“Brazil”);

cell = cells[“E14”];

cell.PutValue(“U.S.”);

cell = cells[“E15”];

cell.PutValue(“U.S.”);

cell = cells[“E16”];

cell.PutValue(“Canada”);

cell = cells[“E17”];

cell.PutValue(“U.S.”);

cell = cells[“E18”];

cell.PutValue(“Italy”);

cell = cells[“E19”];

cell.PutValue(“France”);

cell = cells[“E20”];

cell.PutValue(“Italy”);

cell = cells[“E21”];

cell.PutValue(“New Zealand”);

cell = cells[“E22”];

cell.PutValue(“Australia”);

cell = cells[“E23”];

cell.PutValue(“Australia”);

cell = cells[“E24”];

cell.PutValue(“New Zealand”);

cell = cells[“E25”];

cell.PutValue(“S.Africa”);

cell = cells[“E26”];

cell.PutValue(“S.Africa”);

cell = cells[“E27”];

cell.PutValue(“S.Africa”);

cell = cells[“E28”];

cell.PutValue(“Egypt”);

cell = cells[“E29”];

cell.PutValue(“Egypt”);

cell = cells[“E30”];

cell.PutValue(“Egypt”);


cell = cells[“F2”];

cell.PutValue(2000);

cell = cells[“F3”];

cell.PutValue(500);

cell = cells[“F4”];

cell.PutValue(1200);

cell = cells[“F5”];

cell.PutValue(1500);

cell = cells[“F6”];

cell.PutValue(500);

cell = cells[“F7”];

cell.PutValue(1500);

cell = cells[“F8”];

cell.PutValue(800);

cell = cells[“F9”];

cell.PutValue(900);

cell = cells[“F10”];

cell.PutValue(500);

cell = cells[“F11”];

cell.PutValue(1600);

cell = cells[“F12”];

cell.PutValue(600);

cell = cells[“F13”];

cell.PutValue(2000);

cell = cells[“F14”];

cell.PutValue(500);

cell = cells[“F15”];

cell.PutValue(900);

cell = cells[“F16”];

cell.PutValue(700);

cell = cells[“F17”];

cell.PutValue(1400);

cell = cells[“F18”];

cell.PutValue(1350);

cell = cells[“F19”];

cell.PutValue(300);

cell = cells[“F20”];

cell.PutValue(500);

cell = cells[“F21”];

cell.PutValue(1000);

cell = cells[“F22”];

cell.PutValue(1500);

cell = cells[“F23”];

cell.PutValue(1500);

cell = cells[“F24”];

cell.PutValue(1600);

cell = cells[“F25”];

cell.PutValue(1000);

cell = cells[“F26”];

cell.PutValue(1200);

cell = cells[“F27”];

cell.PutValue(1300);

cell = cells[“F28”];

cell.PutValue(1500);

cell = cells[“F29”];

cell.PutValue(1400);

cell = cells[“F30”];

cell.PutValue(1000);

}