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

Free Support Forum - aspose.com

Create XLS file with Pivot Table

Dear All,


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

please help

Thanks & Regards

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.

( http://www.aspose.com/docs/display/cellsnet/How+to+Create+a+PivotTable )

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);

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.

Hello,


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


please help

Thanks
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);

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.

Hello,


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

Regards

Hello,


please ignore about the 2 rows before Grand Total … its my fault :slight_smile:
but could you please check why the numberformat doesnot work?

thanks & regards

Hello,


Now, I have numberformat works …:slight_smile: … 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

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.

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);

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.
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);
}

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.

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

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.

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 :frowning:


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 :frowning:

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

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.

Hello,


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

Thanks