Pivot Table: How to remove report & Other Issues

Aspose.Cells Product Family
1

Hi, I created a pivot table with 8 rowfields (.Net platform), but I do not need the report. After opening the xls file generated, the report in blank is displayed. I'm using Aspose version 7.2.0.0.

I tried doing the following,

1. I set the Autoformat in true and formatType None.

pivotTable.IsAutoFormat = true;

pivotTable.AutoFormatType = PivotTableAutoFormatType.None;

2. I tied to set a new style to apply in an existent format.

Aspose.Cells.Style stylePivot = workbook.CreateStyle();

stylePivot.HorizontalAlignment = TextAlignmentType.Bottom;

stylePivot.VerticalAlignment = TextAlignmentType.General;

pivotTable.FormatAll(stylePivot);

In excel I can remove the report doing right clic on the report or pivot table PivotTable Options=> Display tab => Uncheck Classic Pivot Table Layout (That's why I set the autoformat to None but with no luck). But how can I do it using Aspose?

3. I want to display all the data without plus sign to expand, so I tried using ShowDrill = false; but it didn't work.

pivotTable.ShowDrill = false;

In excel I can remove the plus doing right clic on the pivot table PivotTable Options=> Display Tab=> Show expand/collapse buttons (Uncheck).

4. When opening the saved xsl file a message is displayed saying: "Do you want to replace the contents of the destination cells in "nameOfReport.xsl" Pivot Table? Yes/No buttons".

Why does it appear? How can I remove it?

5. Let me add the code for further information, perhaps I'm missing something.

private void ImportDataTable()
{
Workbook workbook = new Workbook();
Worksheet sheet2 = workbook.Worksheets[0];//workbook.Worksheets[workbook.Worksheets.Add()];
//Naming the sheet
sheet2.Name = "PivotTable";
sheet2.Cells.ImportDataTable(GetPivotTimesData(), true, "A3");

//Getting the pivottables collection in the sheet
Aspose.Cells.Pivot.PivotTableCollection pivotTables = sheet2.PivotTables;
//Adding a PivotTable to the worksheet
int index = pivotTables.Add("=PivotTable!A3:H7", "A3", "PivotTable1", true);
//Accessing the instance of the newly added PivotTable
Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];

//UnShowing the grand totals
pivotTable.RowGrand = false;
pivotTable.ColumnGrand = false;
pivotTable.ShowEmptyCol = false;
pivotTable.ShowPivotStyleRowHeader = true;

////Draging the first field to the row area.
pivotTable.AddFieldToArea(PivotFieldType.Row, 0);
////Draging the second field to the row area.
pivotTable.AddFieldToArea(PivotFieldType.Row, 1);
////Draging the third field to the row area.
pivotTable.AddFieldToArea(PivotFieldType.Row, 2);
pivotTable.AddFieldToArea(PivotFieldType.Row, 3);
pivotTable.AddFieldToArea(PivotFieldType.Row, 4);
pivotTable.AddFieldToArea(PivotFieldType.Row, 5);
pivotTable.AddFieldToArea(PivotFieldType.Row, 6);
pivotTable.AddFieldToArea(PivotFieldType.Row, 7);

//Accessing the row fields.
Aspose.Cells.Pivot.PivotFieldCollection pivotFields = pivotTable.RowFields;
//Accessing the first row field in the row fields.
for (int i = 0; i < pivotFields.Count; i++)
{
Aspose.Cells.Pivot.PivotField pivotField = pivotFields[i];
pivotField.SetSubtotals(Aspose.Cells.Pivot.PivotFieldSubtotalType.None, true);
pivotField.IsAutoShow = false;
pivotField.IsAutoSubtotals = false;
}

//Get the cells collection in the worksheet
setPivotStyle(sheet2, pivotTable, workbook);
setRotatingStyleStage2(sheet2, workbook);

//Saving the Excel file
workbook.Save("C:\\test\\TableTimesReport.xls");

}

private void setPivotStyle(Worksheet sheet2, Aspose.Cells.Pivot.PivotTable pivotTable, Workbook workbook)
{
//Setting the PivotTable report is automatically formatted
pivotTable.IsAutoFormat = true;

//Setting the PivotTable atuoformat type.
pivotTable.PivotTableStyleType = PivotTableStyleType.PivotTableStyleDark1;
pivotTable.AutoFormatType = PivotTableAutoFormatType.None;

Aspose.Cells.Style stylePivot = workbook.CreateStyle();
stylePivot.HorizontalAlignment = TextAlignmentType.Bottom;
stylePivot.VerticalAlignment = TextAlignmentType.General;

pivotTable.FormatAll(stylePivot);
pivotTable.ShowDrill = false;
}

private DataTable GetPivotTimesData()
{
//Create a datatable and add three columns to it
DataTable dataTable = new DataTable("PivotTimes");
dataTable.Columns.Add("PROJECT ID", typeof(Int32));
dataTable.Columns.Add("PROJECT CODE", typeof(string));
dataTable.Columns.Add("PROJECT TITLE", typeof(string));
dataTable.Columns.Add("PROJECT MODE", typeof(string));
dataTable.Columns.Add("PROJECT TYPE", typeof(string));
dataTable.Columns.Add("USER DIRECTOR ID", typeof(string));
dataTable.Columns.Add("MANAGMENT AREA", typeof(string));
dataTable.Columns.Add("AMOUNT TYPE", typeof(string));
dataTable.Columns.Add("RELEASED DATE", typeof (string));
dataTable.Columns.Add("RELEASED MONTH", typeof (string));
dataTable.Columns.Add("RELEASED YEAR", typeof (string));


//Stage Columns
dataTable.Columns.Add("INICIO", typeof(DateTime));
dataTable.Columns.Add("QUALITY ASSURANCE OPTE PM", typeof(string));
dataTable.Columns.Add("QUALITY ASSURANCE PO", typeof(string));
dataTable.Columns.Add("APROBACIÓN KU", typeof(string));
dataTable.Columns.Add("APROBACIÓN IT MNG", typeof(string));
dataTable.Columns.Add("AUTORIZACIÓN USER DIRECTOR", typeof(string));
dataTable.Columns.Add("AUTORIZACIÓN IT DIRECTION", typeof(string));
dataTable.Columns.Add("AUTORIZACIÓN CIO", typeof(string));
dataTable.Columns.Add("AUTORIZACIÓN CEO", typeof(string));
dataTable.Columns.Add("AUTORIZACIÓN AREA MANAGER", typeof(string));
dataTable.Columns.Add("SET-UP ADMINISTRATIVO", typeof(string));
dataTable.Columns.Add("LIBERACIÓN CONTABILIDADES", typeof(string));
dataTable.Columns.Add("COMUNICACIÓN", typeof(string));
dataTable.Columns.Add("REJECT", typeof(string));

//Stage Columns
dataTable.Columns.Add("INICIO ", typeof(DateTime));
dataTable.Columns.Add("QUALITY ASSURANCE OPTE PM " , typeof(string));
dataTable.Columns.Add("QUALITY ASSURANCE PO ", typeof(string));
dataTable.Columns.Add("APROBACIÓN KU ", typeof(string));
dataTable.Columns.Add("APROBACIÓN IT MNG " , typeof(string));
dataTable.Columns.Add("AUTORIZACIÓN USER DIRECTOR ", typeof(string));
dataTable.Columns.Add("AUTORIZACIÓN IT DIRECTION ", typeof(string));
dataTable.Columns.Add("AUTORIZACIÓN CIO ", typeof(string));
dataTable.Columns.Add("AUTORIZACIÓN CEO ", typeof(string));
dataTable.Columns.Add("AUTORIZACIÓN AREA MANAGER ", typeof(string));
dataTable.Columns.Add("SET-UP ADMINISTRATIVO ", typeof(string));
dataTable.Columns.Add("LIBERACIÓN CONTABILIDADES ", typeof(string));
dataTable.Columns.Add("COMUNICACIÓN ", typeof(string));

//Add the first record to it
DataRow dr = dataTable.NewRow();
dr[0] = 4212;
dr[1] = "TEST1";
dr[2] = "Interfaces";
dr[3] = "Project";
dr[4] = "Investment";
dr[5] = "User1";
dr[6] = "Canada";
dr[7] = "1";

dr[8] = "3/7/2012 9:29:20 PM";
dr[9] = "Abril";
dr[10] = "2012";
// Stage values
dr[11] = "3/7/2012 9:29:20 PM";
dr[12] = "3/7/2012 9:29:20 PM";
dr[13] = "3/7/2012 9:29:20 PM";
dr[14] = "3/7/2012 9:29:20 PM";
dr[15] = "3/7/2012 9:29:20 PM";
dr[16] = "3/7/2012 9:29:20 PM";
dr[17] = "3/7/2012 9:29:20 PM";
dr[18] = "3/7/2012 9:29:20 PM";
dr[19] = "3/7/2012 9:29:20 PM";
dr[20] = "3/7/2012 9:29:20 PM";
dr[21] = "3/7/2012 9:29:20 PM";
dr[22] = "3/7/2012 9:29:20 PM";
dr[23] = "3/7/2012 9:29:20 PM";

// Stage values2
dr[24] = "3/7/2012 9:29:20 PM";
dr[25] = "3/7/2012 9:29:20 PM";
dr[26] = "3/7/2012 9:29:20 PM";
dr[27] = "3/7/2012 9:29:20 PM";
dr[28] = "3/7/2012 9:29:20 PM";
dr[29] = "3/7/2012 9:29:20 PM";
dr[30] = "3/7/2012 9:29:20 PM";
dr[31] = "3/7/2012 9:29:20 PM";
dr[32] = "3/7/2012 9:29:20 PM";
dr[33] = "3/7/2012 9:29:20 PM";
dr[34] = "3/7/2012 9:29:20 PM";
dr[35] = "3/7/2012 9:29:20 PM";
dr[36] = "3/7/2012 9:29:20 PM";

dataTable.Rows.Add(dr);

//Add the second record to it
dr = dataTable.NewRow();
dr[0] = 4585;
dr[1] = "Test2";
dr[2] = "Bla12345";
dr[3] = "Project";
dr[4] = "Investment";
dr[5] = "BLA";
dr[6] = "Manufacturing System";
dr[7] = "2";
dr[8] = Convert.ToDateTime("3/7/2012 9:29:20 PM");
dr[9] = "Abril";
dr[10] = "2012";
// Stage values
dr[11] = "3/7/2012 9:29:20 PM";
dr[12] = "3/7/2012 9:29:20 PM";
dr[13] = "3/7/2012 9:29:20 PM";
dr[14] = "3/7/2012 9:29:20 PM";
dr[15] = "3/7/2012 9:29:20 PM";
dr[16] = "3/7/2012 9:29:20 PM";
dr[17] = "3/7/2012 9:29:20 PM";
dr[18] = "3/7/2012 9:29:20 PM";
dr[19] = "3/7/2012 9:29:20 PM";
dr[20] = "3/7/2012 9:29:20 PM";
dr[21] = "3/7/2012 9:29:20 PM";
dr[22] = "3/7/2012 9:29:20 PM";
dr[23] = "3/7/2012 9:29:20 PM";
// Stage values2
dr[24] = "3/7/2012 9:29:20 PM";
dr[25] = "3/7/2012 9:29:20 PM";
dr[26] = "3/7/2012 9:29:20 PM";
dr[27] = "3/7/2012 9:29:20 PM";
dr[28] = "3/7/2012 9:29:20 PM";
dr[29] = "3/7/2012 9:29:20 PM";
dr[30] = "3/7/2012 9:29:20 PM";
dr[31] = "3/7/2012 9:29:20 PM";
dr[32] = "3/7/2012 9:29:20 PM";
dr[33] = "3/7/2012 9:29:20 PM";
dr[34] = "3/7/2012 9:29:20 PM";
dr[35] = "3/7/2012 9:29:20 PM";
dr[36] = "3/7/2012 9:29:20 PM";
dataTable.Rows.Add(dr);

//Add the second record to it
dr = dataTable.NewRow();
dr[0] = 4821;
dr[1] = "TEST5";
dr[2] = "ABCD-";
dr[3] = "Extension";
dr[4] = "Investment";
dr[5] = "123'20010926";
dr[6] = "Argentina";
dr[7] = "3";
dr[8] = "3/7/2012 9:29:20 PM";
dr[9] = "Abril";
dr[10] = "2012";
// Stage values
dr[11] = "3/7/2012 9:29:20 PM";
dr[12] = "3/7/2012 9:29:20 PM";
dr[13] = "3/7/2012 9:29:20 PM";
dr[14] = "3/7/2012 9:29:20 PM";
dr[15] = "3/7/2012 9:29:20 PM";
dr[16] = "3/7/2012 9:29:20 PM";
dr[17] = "3/7/2012 9:29:20 PM";
dr[18] = "3/7/2012 9:29:20 PM";
dr[19] = "3/7/2012 9:29:20 PM";
dr[20] = "3/7/2012 9:29:20 PM";
dr[21] = "3/7/2012 9:29:20 PM";
dr[22] = "3/7/2012 9:29:20 PM";
dr[23] = "3/7/2012 9:29:20 PM";
// Stage values2
dr[24] = "3/7/2012 9:29:20 PM";
dr[25] = "3/7/2012 9:29:20 PM";
dr[26] = "3/7/2012 9:29:20 PM";
dr[27] = "3/7/2012 9:29:20 PM";
dr[28] = "3/7/2012 9:29:20 PM";
dr[29] = "3/7/2012 9:29:20 PM";
dr[30] = "3/7/2012 9:29:20 PM";
dr[31] = "3/7/2012 9:29:20 PM";
dr[32] = "3/7/2012 9:29:20 PM";
dr[33] = "3/7/2012 9:29:20 PM";
dr[34] = "3/7/2012 9:29:20 PM";
dr[35] = "3/7/2012 9:29:20 PM";
dr[36] = "3/7/2012 9:29:20 PM";
dataTable.Rows.Add(dr);

return dataTable;
}

I'll really appreciate any help on this!!

Thanks in Advance!

PS: I attached the error message and the format of the pivot table I need. Please check them out.

2

Hi,


I have commented your line of code first before evaluating or executing your sample codes:
//setRotatingStyleStage2(sheet2, workbook);
as you have not pasted this method/definition i.e. “setRotatingStyleStage2”, so I have to evaluate your issue without this method.

I have evaluated your sample code and other points you mentioned here.

For your points i.e. 1), 2) and 3):

Well, as you are using the sample line(s) of code in the method “setPivotStyle”:
Sample code:

//Setting the PivotTable atuoformat type.
pivotTable.PivotTableStyleType = PivotTableStyleType.PivotTableStyleDark1;
pivotTable.AutoFormatType = PivotTableAutoFormatType.None;

Aspose.Cells.Style stylePivot = workbook.CreateStyle();
stylePivot.HorizontalAlignment = TextAlignmentType.Bottom;
stylePivot.VerticalAlignment = TextAlignmentType.General;

pivotTable.FormatAll(stylePivot);
pivotTable.ShowDrill = false;

but you are saving to XLS file format which does not have this feasibility, e.g you cannot set the PivotTableStyleType as this is Excel 2007/2010 feature/option for XLSX file format, so you are getting the classical style with “+”/"-" nodes in the output file. Please save your final file to XLSX file format, it will work fine accordingly as I tested with v7.5.0.x (latest version/fix).

4) I found the issue as you have mentioned. When I opened the final Excel file (XLS and XLSX) into MS Excel, MS Excel prompts an error: “Do you want to replace the contents of the destination cells in “nameOfReport.xls” Pivot Table?”. I have logged a ticket a ticket with an id “CELLSNET-41788” for your issue. We will look into your issue soon.

Once we have any update on it, we will let you know here.


Thank you.













3

Hi,

Please download and try this fix: Aspose.Cells for .NET v7.5.0.2

Please add these lines of code before saving to workbook:
e.g.

pivotTable.IsGridDropZones = true;
pivotTable.RefreshDataFlag = true;

Thank you.

4

Thank you for the updates on this. I need to check the license expiration date in the company to know whether we can upgrade to 7.5.02 version.

So I tried to fix it based on you comments in 7.2.0 version.

You can find attached the new report I created in .Net in Aspose and the one which is required. AsposeRpt and ReportTest excel files

After I add a column field (stage) the values are not displayed in the excel. Am I missing anything? Also a blank row was added, How can I remove it?

Check the code:

private void ImportMethod()

{

Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];

sheet.Name = "PivotTable";

sheet.Cells.ImportDataTable(GetPivotTimesData(), true, "A3");

//Getting the pivottables collection in the sheet

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

//Adding a PivotTable to the worksheet

int index = pivotTables.Add("=PivotTable!A3:N7", "A3", "PivotTable1", true);

//Accessing the instance of the newly added PivotTable

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

//UnShowing the grand totals

pivotTable.RowGrand = false;

pivotTable.ColumnGrand = false;

pivotTable.ShowEmptyCol = false;

pivotTable.ShowPivotStyleRowHeader = true;

pivotTable.ShowEmptyRow = false;

pivotTable.ShowValuesRow = true;

pivotTable.ShowDrill = false;

pivotTable.AddFieldToArea(PivotFieldType.Row, 0);

pivotTable.AddFieldToArea(PivotFieldType.Row, 1);

pivotTable.AddFieldToArea(PivotFieldType.Row, 2);

pivotTable.AddFieldToArea(PivotFieldType.Row, 3);

pivotTable.AddFieldToArea(PivotFieldType.Row, 4);

pivotTable.AddFieldToArea(PivotFieldType.Row, 5);

pivotTable.AddFieldToArea(PivotFieldType.Row, 6);

pivotTable.AddFieldToArea(PivotFieldType.Row, 7);

pivotTable.AddFieldToArea(PivotFieldType.Row, 8);

pivotTable.AddFieldToArea(PivotFieldType.Row, 9);

pivotTable.AddFieldToArea(PivotFieldType.Row, 10);

// Stage

pivotTable.AddFieldToArea(PivotFieldType.Column, 11);

// Max Date

pivotTable.AddFieldToArea(PivotFieldType.Data, 12);

//Accessing the row fields.

Aspose.Cells.Pivot.PivotFieldCollection pivotFields = pivotTable.RowFields;

// Setting the Value Type Setting

pivotTable.DataFields[0].Function = ConsolidationFunction.Max;

Cells cells = sheet.Cells;

//Accessing the first row field in the row fields.

for (int i = 0; i < pivotFields.Count; i++)

{

Aspose.Cells.Pivot.PivotField pivotField = pivotFields[i];

pivotField.SetSubtotals(Aspose.Cells.Pivot.PivotFieldSubtotalType.None, true);

pivotField.IsAutoShow = false;

pivotField.IsAutoSubtotals = false;

cells.SetColumnWidth(i, 20);

}

//Get the cells collection in the worksheet

setPivotStyle(sheet, pivotTable, workbook);

workbook.RemoveMacro();

//Saving the Excel file

workbook.Save("C:\\test\\Today.xlsx");

}

private void setPivotStyle(Worksheet sheet2, Aspose.Cells.Pivot.PivotTable pivotTable, Workbook workbook)

{

pivotTable.PivotTableStyleType = PivotTableStyleType.PivotTableStyleDark1;

pivotTable.AutoFormatType = PivotTableAutoFormatType.None;

Aspose.Cells.Style stylePivot = workbook.CreateStyle();

stylePivot.HorizontalAlignment = TextAlignmentType.Bottom;

stylePivot.VerticalAlignment = TextAlignmentType.General;

pivotTable.FormatAll(stylePivot);

pivotTable.DisplayNullString = false;

pivotTable.EnableDrilldown = false;

pivotTable.IsGridDropZones = true;

pivotTable.RefreshDataFlag = true;

pivotTable.ShowDrill = false;

}

private DataTable GetPivotTimesData()

{

//Create a datatable and add three columns to it

DataTable dataTable = new DataTable("PivotTimes");

dataTable.Columns.Add("PROJECT ID", typeof(Int32));

dataTable.Columns.Add("PROJECT CODE", typeof(string));

dataTable.Columns.Add("PROJECT TITLE", typeof(string));

dataTable.Columns.Add("PROJECT MODE", typeof(string));

dataTable.Columns.Add("PROJECT TYPE", typeof(string));

dataTable.Columns.Add("USER DIRECTOR ID", typeof(string));

dataTable.Columns.Add("MANAGMENT AREA", typeof(string));

dataTable.Columns.Add("AMOUNT TYPE", typeof(string));

dataTable.Columns.Add("RELEASED DATE", typeof (string));

dataTable.Columns.Add("RELEASED MONTH", typeof (string));

dataTable.Columns.Add("RELEASED YEAR", typeof (string));

//Stage Columns

dataTable.Columns.Add("STAGE", typeof(string));

dataTable.Columns.Add("MAX DATE", typeof(string));

dataTable.Columns.Add("MIN DATE", typeof(string));

//Add the first record to it

DataRow dr = dataTable.NewRow();

dr[0] = 4212;

dr[1] = "CODE1";

dr[2] = "Interfaces";

dr[3] = "Project";

dr[4] = "Investment";

dr[5] = "User";

dr[6] = "IT Canada";

dr[7] = "0-150";

dr[8] = "3/7/2012 9:29:20 PM";

dr[9] = "Abril";

dr[10] = "2012";

// Stage values

dr[11] = "INICIO";

dr[12] = "3/7/2012 9:29:20 PM";

dr[13] = "1/7/2012 9:29:20 PM";

dataTable.Rows.Add(dr);

//Add the second record to it

dr = dataTable.NewRow();

dr[0] = 4585;

dr[1] = "CODE5";

dr[2] = "Threading USA";

dr[3] = "Project";

dr[4] = "Investment";

dr[5] = "ABC";

dr[6] = "System";

dr[7] = "+2M";

dr[8] = Convert.ToDateTime("3/7/2012 9:29:20 PM");

dr[9] = "Abril";

dr[10] = "2012";

// Stage values

dr[11] = "QUALITY ASSURANCE OPTE PM";

dr[12] = "3/7/2012 9:29:20 PM";

dr[13] = "1/6/2012 9:29:20 PM";

dataTable.Rows.Add(dr);

//Add the second record to it

dr = dataTable.NewRow();

dr[0] = 4821;

dr[1] = "CODE1";

dr[2] = "E-commerce-";

dr[3] = "Extension";

dr[4] = "Investment";

dr[5] = "A123";

dr[6] = "IT Argentina";

dr[7] = "150-2M";

dr[8] = Convert.ToDateTime("3/7/2012 9:29:20 PM");

dr[9] = "Abril";

dr[10] = "2012";

// Stage values

dr[11] = "APROBACIN KU";

dr[12] = "3/7/2012 9:29:20 PM";

dr[13] = "1/2/2012 9:29:20 PM";

dataTable.Rows.Add(dr);

return dataTable;

}

I really appreciate your help on this!!

Thanks in Advance! :)

Regards!

5

Hi,


I am afraid, we cannot evaluate your issue based on an older version that you are using. We appreciate if you could try our latest version with the suggested options, you may comment out the licensing code. If you still find the issue, we will look into your issue to try to figure it out soon.

Thank you.
6

Thank you for the quick response, in spite of the fact I'm using a previous version, could you please tell me how to make the values show up after adding a column field?

Because the values are displayed with 0 as value when the max date should be displayed. Please see the attachments.

I'll apreciate any help or hint on this!!!!

Thanks in Advance!

7

Hi,

Could you please change line 8 in the method “ImportMethod()” to the following sample code:

int index = pivotTables.Add("=PivotTable!A3:N6", "A30", "PivotTable1", true);

this will remove the blank row.

You could not display the data value in pivot data fields, it could be only displayed as function which is in enum “ConsolidationFunction”, so if you want to display the max date value, please don’t add it to data field.

If you add field to data scope using MS Excel manually, you will find this issue too.

Thank you.

8

Hi Amjad! Thank you for your time!

This is the result of aspose:

As you can see the values are displayed with 0 when they should be the min date.

Min of MIN DATE Etapa
PROJECT ID START INICIO FINAL Quality Assurance OPTE PM
4212 0 0 0
4585 0
4821 0

I did the following to get that:

Worksheet sheet = workbook.Worksheets["PivotTable"];

DataTable dt =

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

//Adding a PivotTable to the worksheet

int index = pivotTables.Add("=Data!A3:O" + (3 + dt.Rows.Count).ToString(), "Q3", "PivotTable2", true);

//Accessing the instance of the newly added PivotTable

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

unshowTotals(pivotTable2);

//unshowTotals(pivotTable2);

////Draging the first field to the row area.

pivotTable2.AddFieldToArea(PivotFieldType.Row, 0);

pivotTable2.AddFieldToArea(PivotFieldType.Page, 11);

pivotTable2.PageFields[0].InsertBlankRow = false;

// Stage

pivotTable2.AddFieldToArea(PivotFieldType.Column, 12);

pivotTable2.ColumnFields[0].InsertBlankRow = false;

// MinDate

pivotTable2.AddFieldToArea(PivotFieldType.Data, 14);

pivotTable2.DataFields[0].ShowAllItems = true;

SettingFilters(pivotTable2, ConsolidationFunction.Min);

//Accessing the row fields.

Aspose.Cells.Pivot.PivotFieldCollection pivotFields = pivotTable2.RowFields;

Cells cells = sheet.Cells;

//Accessing the first row field in the row fields.

for (int i = 0; i < pivotFields.Count; i++)

{

Aspose.Cells.Pivot.PivotField pivotField = pivotFields[i];

pivotField.SetSubtotals(Aspose.Cells.Pivot.PivotFieldSubtotalType.None, true);

pivotField.IsAutoShow = false;

pivotField.IsAutoSubtotals = false;

cells.SetColumnWidth(i, 20);

}

pivotTable2.IsGridDropZones = true;

pivotTable2.RefreshDataFlag = true;

pivotTable2.RefreshDataOnOpeningFile = true;

Also I created this method:

private void SettingFilters(PivotTable pivotTable, ConsolidationFunction function)

{

PivotFieldCollection col = pivotTable.ColumnFields;

if (col.Count > 0 )

{

pivotTable.ColumnFields[0].Function = function;

pivotTable.ColumnFields[0].SetSubtotals(Aspose.Cells.Pivot.PivotFieldSubtotalType.None, true);

pivotTable.ColumnFields[0].DisplayName = "Etapa";

}

col = pivotTable.DataFields;

if (col.Count > 0)

// Setting the Value Type Setting

pivotTable.DataFields[0].Function = function;

if (pivotTable.DataFields[0].IsCalculatedField)

{

pivotTable.RefreshData();

pivotTable.CalculateData();

}

}

private DataTable GetPivotTimesData()

{

//Create a datatable and add three columns to it

DataTable dataTable = new DataTable("PivotTimes");

dataTable.Columns.Add("PROJECT ID", typeof(Int32));

dataTable.Columns.Add("PROJECT CODE", typeof(string));

dataTable.Columns.Add("PROJECT TITLE", typeof(string));

dataTable.Columns.Add("PROJECT MODE", typeof(string));

dataTable.Columns.Add("PROJECT TYPE", typeof(string));

dataTable.Columns.Add("USER DIRECTOR ID", typeof(string));

dataTable.Columns.Add("MANAGMENT AREA", typeof(string));

dataTable.Columns.Add("AMOUNT TYPE", typeof(string));

dataTable.Columns.Add("RELEASED DATE", typeof (string));

dataTable.Columns.Add("RELEASED MONTH", typeof (string));

dataTable.Columns.Add("RELEASED YEAR", typeof (string));

dataTable.Columns.Add("AMBIT", typeof(string));

//Stage Columns

dataTable.Columns.Add("STAGE", typeof(string));

dataTable.Columns.Add("MAX DATE", typeof(string));

dataTable.Columns.Add("MIN DATE", typeof(string));

//Add the first record to it

DataRow dr = dataTable.NewRow();

dr[0] = 4212;

dr[1] = "CODE04212";

dr[2] = "nterfaces";

dr[3] = "Project";

dr[4] = "Investment";

dr[5] = "User13909";

dr[6] = "IT Canada";

dr[7] = "10";

dr[8] = "3/7/2012 9:29:20 PM";

dr[9] = "Abril";

dr[10] = "2012";

dr[11] = "IT";

// Stage values

dr[12] = "START";

dr[13] = "30/7/2012 9:29:20 PM";

dr[14] = "15/7/2012 9:29:20 PM";

dataTable.Rows.Add(dr);

dr = dataTable.NewRow();

dr[0] = 4212;

dr[1] = "CODE04212";

dr[2] = "nterfaces";

dr[3] = "Project";

dr[4] = "Investment";

dr[5] = "User13909";

dr[6] = "IT Canada";

dr[7] = "10";

dr[8] = "3/7/2012 9:29:20 PM";

dr[9] = "Abril";

dr[10] = "2012";

dr[11] = "IT";

// Stage values

dr[12] = "INICIO";

dr[13] = "30/7/2012 9:29:20 PM";

dr[14] = "15/7/2012 9:29:20 PM";

dataTable.Rows.Add(dr);

dr = dataTable.NewRow();

dr[0] = 4212;

dr[1] = "CODE04212";

dr[2] = "nterfaces";

dr[3] = "Project";

dr[4] = "Investment";

dr[5] = "User13909";

dr[6] = "IT Canada";

dr[7] = "10";

dr[8] = "3/7/2012 9:29:20 PM";

dr[9] = "Abril";

dr[10] = "2012";

dr[11] = "IT";

// Stage values

dr[12] = "FINAL";

dr[13] = "30/7/2012 9:29:20 PM";

dr[14] = "15/7/2012 9:29:20 PM";

dataTable.Rows.Add(dr);

//Add the second record to it

dr = dataTable.NewRow();

dr[0] = 4585;

dr[1] = "CODE04585";

dr[2] = "NMS phase I: BPM and Industrial specifications for base model - Welded / Threading USA";

dr[3] = "Project";

dr[4] = "Investment";

dr[5] = "User123";

dr[6] = "Tenaris Manufacturing System";

dr[7] = "20";

dr[8] = Convert.ToDateTime("3/7/2012 9:29:20 PM");

dr[9] = "Abril";

dr[10] = "2012";

dr[11] = "IT";

// Stage values

dr[12] = "Quality Assurance OPTE PM";

dr[13] = "10/7/2012 9:29:20 PM";

dr[14] = "1/7/2012 9:29:20 PM";

dataTable.Rows.Add(dr);

//Add the second record to it

dr = dataTable.NewRow();

dr[0] = 4821;

dr[1] = "CODE4135";

dr[2] = "Sistema de Ingreso de pedidos de Clientes - e-commerce-";

dr[3] = "Extension";

dr[4] = "Investment";

dr[5] = "TENARIS'\'20010926";

dr[6] = "IT Argentina";

dr[7] = "15";

dr[8] = Convert.ToDateTime("3/7/2012 9:29:20 PM");

dr[9] = "Abril";

dr[10] = "2012";

dr[11] = "Other";

// Stage values

dr[12] = "INICIO";

dr[13] = "2/7/2012 9:29:20 PM";

dr[14] = "1/2/2012 9:29:20 PM";

dataTable.Rows.Add(dr);

return dataTable;

}

According to your comments, you mean in order to display the data (values section in Excel) I do not have to be added to the pivotTable as DataType? How those value could be displayed using Aspose then?

// Max Date

pivotTable2.AddFieldToArea(PivotFieldType.Data, 14);

pivotTable2.DataFields[0].ShowAllItems = true;

SettingFilters(pivotTable2, ConsolidationFunction.Max);

Perhaps I'm doing sth wrong, and it does not depend on the Aspose version.

I really appreciate your help on this.

Regards!

9

Hi Amjad, I downloaded the latest version (Aspose.Cells7.5.0.2) and got the same results, the values were displayed with 0 values. Is it an issue or the values must be added in a different way in the pivot table with Function Min? The same code in .Net. The dataTable has dates for that column to be displayed so I do not understand how to show those values as values section in Excel using Aspose. I thought adding them as data with function Min, but with no luck.

Thanks in Advance!

Regards!

10

Hi,

Well, if you could add this field to the data scope using MS excel manually, you will also find it displays as "0". So, it displays the result after calculating the function, it's a result but not the data itself.


Thank you.

11

I could see the calculated dates in the pivot table. Thanks for your support!

Regards!

12

Hi,


Could you provide more details and also attach your expected Excel file (with your desired PivotTable report in it) that you may create it manually in MS Excel. Also attach some screen shots here. We will look into it further.

Thank you.