Create XLS file with Pivot Table

Calistasoft_Calistasoft · April 3, 2014, 6:43am

Dear All,

How to create XLS with Pivot Table using Aspose .Cell?

please help

Thanks & Regards

shakeel.faiz · April 3, 2014, 6:55am

Hi Winanjaya,

Thanks for your posting and considering Aspose.Cells.

You can surely create pivot table using Aspose.Cells API and save your workbook in XLS format.

Please see the example in this article for your needs.

( Pivot Table and Source Data|Documentation )

Calistasoft_Calistasoft · April 3, 2014, 11:14pm

Hello,

I created a PivotTable .. but no data displayed... (see enclosed .xls)

I am running codes below..

need your help..

thanks & regards

// Pivot Table is here!

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

sheet2.Name = "PivotTable";

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

//Adding a PivotTable to the worksheet

int index = pivotTables.Add("=" + REPORTNAME + "!A1:E" + (row-1).ToString(), "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;

//Setting the PivotTable report is automatically formatted

pivotTable.IsAutoFormat = true;

//Setting the PivotTable autoformat type.

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

//Draging the first field to the row area.

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

//Setting the number format of the first data field

//pivotTable.DataFields[0].NumberFormat = "$#,##0.00";

workbook.Save(xls);

babar.raza · April 4, 2014, 12:33am

Hi Winanjaya,

Thank you for writing back.

We have checked your code and attached spreadsheet. The results are correct as per your provided code snippet. The Pivot Table seems to be empty because you are not adding any fields to the Row, Column and Data area. Once you will add relevant statements, the resultant Pivot Table will be automatically populated.

Calistasoft_Calistasoft · April 4, 2014, 12:46am

Hello,

Could you please advise how to add data to the PivotTable?

please help

Thanks

Calistasoft_Calistasoft · April 4, 2014, 1:26am

Hello,

I tried codes below, but it does not shown incorrectly... (what I want is the same as enclosed)

/Draging the first field to the row area.

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

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

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

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

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

babar.raza · April 4, 2014, 1:47am

Hi Winanjaya,

Thank you for providing your desired results.

Please use the following code to meet achieve your goals. Also attached is the resultant spreadsheet generated on our end while using your provided data.

C#

var workbook = new Workbook(myDir + “Daily+Report2014-04-04.xls”);
Worksheet sheet2 = workbook.Worksheets[workbook.Worksheets.Add()];
Aspose.Cells.Pivot.PivotTableCollection pivotTables = sheet2.PivotTables;
//Adding a PivotTable to the worksheet
int index = pivotTables.Add("=" + workbook.Worksheets[0].Name + “!A1:E” + workbook.Worksheets[0].Cells.MaxDataRow + 1, “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;
//Setting the PivotTable report is automatically formatted
pivotTable.IsAutoFormat = true;
//Setting the PivotTable Auto Format type
pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Classic;

//Dragging Fields to Row Labels
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, “Branch”);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, “LOB”);

//Dragging Fields to Data Area
PivotField GPW = pivotTable.BaseFields[“GPW”];
GPW.Function = ConsolidationFunction.Sum;
GPW.NumberFormat = “#,##0.00”;
pivotTable.AddFieldToArea(PivotFieldType.Data, GPW);

PivotField CLM = pivotTable.BaseFields[“CLM”];
CLM.Function = ConsolidationFunction.Sum;
CLM.NumberFormat = “#,##0.00”;
pivotTable.AddFieldToArea(PivotFieldType.Data, CLM);

PivotField COLL = pivotTable.BaseFields[“COLL”];
COLL.Function = ConsolidationFunction.Sum;
COLL.NumberFormat = “#,##0.00”;
pivotTable.AddFieldToArea(PivotFieldType.Data, COLL);

if (pivotTable.DataField != null)
{
//DataField attribute of PivotTable exists only if DataFields contains two or more PivotField
pivotTable.AddFieldToArea(PivotFieldType.Column, pivotTable.DataField);
}
workbook.Save(myDir + “output.xlsx”, SaveFormat.Xlsx);

Please feel free to write back in case you have further questions for us.

Calistasoft_Calistasoft · April 4, 2014, 2:40am

Hello,

Thanks for the codes, but why there are 2 empty rows before Grand Total? and the numberformat also doesnot works (see enclosed)

Regards

Calistasoft_Calistasoft · April 4, 2014, 3:26am

Hello,

please ignore about the 2 rows before Grand Total … its my fault

but could you please check why the numberformat doesnot work?

thanks & regards

Calistasoft_Calistasoft · April 4, 2014, 3:53am

Hello,

Now, I have numberformat works …

… but how to set horizontal allignment for to right? (see enclosed)

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

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

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

thanks

babar.raza · April 4, 2014, 7:23am

Hi Winanjaya,

Thank you for writing back.

Please note, when a pivot table is created using the Aspose.Cells APIs, the number fields are aligned to right whereas text fields are aligned to left, by default. If you still wish to format the pivot table, you may try using the PivotTable.Format method that accepts a Style object along with Cells coordinates.

Please let us know if setting the custom format has changed the field alignment then please provide your code snippet so we could look into it to further guide you in this regard.

Calistasoft_Calistasoft · April 4, 2014, 10:40am

Hello.

Ok, I will do looping through the rows and the set the style on each row…but is there any pivotTable property for get the max row?

Style s = cell.GetStyle();

numformat.Number = 4;

numformat.HorizontalAlignment = TextAlignmentType.Right;

pivotTable.Format(irow, 2, s);

babar.raza · April 4, 2014, 1:43pm

Hi Winanjaya,

You may use the PivotTable.ColumnRange property to get the CellArea containing all the pivot table cells falling under the column area. CellArea object then allows you to get the start/end row/column indexes to be used with PivotTable.Format method. Please check the below provided code snippet for your reference.

C#

index = workbook.Styles.Add();
Style style = workbook.Styles[index];
style.HorizontalAlignment = TextAlignmentType.Right;
CellArea area = pivotTable.ColumnRange;
int start = area.StartRow;
for (int i = start; i <= area.EndRow; i++)
{
//Format the third column field.
pivotTable.Format(i, 3, style);
}

Hope this helps a bit.

Calistasoft_Calistasoft · April 4, 2014, 7:24pm

just want to share :) ..

any way .. Thanks a lot ..

Have a nice day :)

index = workbook.Styles.Add();

Style style = workbook.Styles[index];

style.HorizontalAlignment = TextAlignmentType.Right;

CellArea area = pivotTable.RowRange;

int start = area.StartRow;

for (int i = pivotTable.RowRange.StartRow; i <= pivotTable.RowRange.EndRow; i++)

{

pivotTable.Format(i, 3, style);

pivotTable.Format(i, 4, style);

pivotTable.Format(i, 5, style);

}

babar.raza · April 5, 2014, 4:56am

Hi Winanjaya,

Thank you for the updates. Yes, your provided code is another way to achieve the same results. Please let us know if you need our further assistance with Aspose.Cells APIs.

Calistasoft_Calistasoft · April 7, 2014, 1:49am

Hello,

Could you please advise how to sort Descending to column of PivotTable?

pivotTable.DataFields[0]. —> I dont see the method to descending sort order for this column

many thanks in advance

Regard

Winanjaya

babar.raza · April 7, 2014, 6:36am

Hi Winanjaya,

Thank you for writing back.

You may apply sorting to any field using the below approach,

C#

//Accessing the column fields.
PivotFieldCollection pivotFields = pivotTable.ColumnFields;

//Accessing the first column field from the collection.
PivotField pivotField = pivotFields[0];

//Setting the field auto sort.
pivotField.IsAutoSort = true;

//Setting the field sorting in ascending order.
pivotField.IsAscendSort = true;

//Setting the field auto sort using the field itself.
pivotField.AutoSortField = -1;

//Setting the field auto show.

pivotField.IsAutoShow = true;

Hope this helps a bit.

Calistasoft_Calistasoft · April 13, 2014, 4:21am

Hello Babar Raza,

CellsException caught, --> "You can’t operate the field"

Could you please help… I need to descending sort order for the GPW column … (See enclosed .xls)

also have tried the following … but no luck

PivotFieldCollection dfs = pivotTable.DataFields;

PivotField df = dfs[0];

df.IsAscendShow = false;

df.IsAutoSort = true;

df.IsAutoShow = true;

also had tried the following … but no luck

pivotTable.BaseFields[“GPW”].IsAscendShow = false;

pivotTable.BaseFields[“GPW”].IsAscendSort = false;

pivotTable.BaseFields[“GPW”].IsAutoSort = true;

pivotTable.BaseFields[“GPW”].IsAutoShow = true;

many thanks in advance

regards

babar.raza · April 14, 2014, 2:02am

Hi Winanjaya,

I am afraid, I was unable to achieve your requirement of sorting the GPW field in the Pivot Table (spreadsheet provided by you). I am further looking into this matter to provide some justification for the Aspose.Cells behavior. I will keep you posted with updates in this regard.

Sorry for the inconvenience caused.

Calistasoft_Calistasoft · April 15, 2014, 9:56am

Hello,

do you mean it’s bug or Aspose.Cell limitation?

Thanks