hi,
I am attaching this excel can you please tell me basic C# code for this pivot.how do i make rowlabel group by as shown in excel MPU and BU . BU is under MPU with +. and also report filter.
thanks
piyush
Hi Piyush,
Thank you for considering Aspose products.
Below provided is the sample code to drag any field to the Data area after applying the consolidate function.
C#
//Dragging Fields to Data area
PivotField field = pivotTable.BaseFields[“Month to Date”];
field.Function = ConsolidationFunction.Sum;
field.NumberFormat = “#,##0.00”;
pivotTable.AddFieldToArea(PivotFieldType.Data, field);
Below provide snippet adds the field to Report Filter area.
C#
//Dragging Fields to Report Filter area
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, "L/M");
Please note, above snippets are accessing the PivotField through field name, you may also pass the base field index to access the same field.
If you have more questions, please do not hesitate to ask.
hi,
Thank you for your help.
I am able to create Pivot but You have not answer the question how do i make sure that "BU NAME" group under MPU. if you see attached excel file there is treeview for MPU and BU NAME. how do i display this..
this is basically group by under MPU.
Hi Piyush,
The grouping of the fields is done by default depending upon what fields are added to what area and, especially the sequence of adding the fields. Please check the below provided code snippet that I have used to verify the claim by adding a new Pivot Table based on same data. Please note, your provided spreadsheet contains data connections therefore when I added a new Pivot Table to existing spreadsheet, the result becomes corrupted. So the below code uses the data of your provided spreadsheet and adds the Pivot Table to a new Workbook object.
C#
var source = new Workbook(myDir + “Sample+Report+Requirement).xls”);
var workbook = new Workbook();
workbook.Worksheets.Clear();
var data = workbook.Worksheets[workbook.Worksheets.Add()];
data.Copy(source.Worksheets[“Data”]);
Aspose.Cells.Pivot.PivotTableCollection pivotTables = workbook.Worksheets[workbook.Worksheets.Add()].PivotTables;
//Adding a PivotTable to the worksheet
int index = pivotTables.Add("=" + workbook.Worksheets[0].Name + “!A1:AT1032”, “B3”, “table”);
//Accessing the instance of the newly added PivotTable
Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];
//Showing the grand total
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.Report1;
//Showing Drill Down
pivotTable.ShowDrill = true;
//Dragging Fields to Row Labels area
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, “MPU”);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, “BU Name”);
//Dragging Field to Data Area
PivotField field = pivotTable.BaseFields[“Month to Date”];
field.Function = ConsolidationFunction.Sum;
field.NumberFormat = “#,##0.00”;
pivotTable.AddFieldToArea(PivotFieldType.Data, field);
//Dragging Field to Column area
pivotTable.AddFieldToArea(PivotFieldType.Column, “LE”);
//Dragging Field to Report Filter area
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, “L/M”);
workbook.Save(myDir + “output.xlsx”, SaveFormat.Xlsx);
In case I am wrong in my understanding, please elaborate your inquiry with the help of snapshots.
hi,
why below filter when appear on webgrid does not show up dropdown filter.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, "L/M");
i am populating this pivot for WebGrid.
i have attached jpg pics of screenshot. also attach C# code.
pic attachemnt for filter
also i want to know is there way we can Fix the header row while scrolling the
vertical scroll bar on WebGrid.
also if i put report header on top row and it's width is long than column under that header cell also gets expanded is there any way we can keep column width relaed to data but first row with report Title can be expanded but without any affecting column
Hi Piyush,
Thank you for providing the snapshots. It is now clear that you are creating the PivotTable on the fly and importing it in GridWeb. Unfortunately, the GridWeb component does not allow to manipulate the PivotTable therefore all aspects of manipulation (including report filter selection and Pivot Field group buttons) are being removed when imported in GridWeb window. We have logged a ticket CELLSNET-42557 in our bug tracking system to probe further into this matter. Please spare us little time for proper analysis, in the meanwhile we will keep you posted with updates in this regard.
Regarding your recently posted requirement, we would suggest you to use the freeze row(s) option to fix the header row. Please check the detailed article on this subject. For the other part of your inquiry, please note, a cell is part of row as well as a column, therefore if you dynamically increase the width or height of a given cell, it will affect the corresponding row and/or column.
can you please priotise this CELLNET-42557. otherewise i have to remove all my WEbGRid in all webpages. this is critical functionality. otherwise we have to goto DEvExpress or other crapy thrid party tool.
also i am facing issue everytime below code runs web page post multiple times.
i am not sure why it post multiple times. is there a bug.
GridWeb1.WebWorksheets.ImportExcelFile(wb.SaveToStream());
form1.Controls.Add(GridWeb1);
Hi Piyush,
The ticket CELLSNET-42557 has already been logged with highest priority possible. Unfortunately the said ticket is currently pending for analysis and is in the queue with other tasks, therefore we cannot share any updates at the moment. As soon as we have analyzed the task on our end, we will be able to provide more details in this regard.
Regarding your other question, if you have enabled the Async mode for the GridWeb component, there are chances that web page may post several times. Please disable the Async mode if already enabled, otherwise please share a sample executable project for our review.
hi,
i am getting this error when i open Pivot excel file.I have attached screen shot.
it used to work before.i have not changed anything. see C3 code for your reference.
Hi Piyush,
If you haven’t changed your code then the error message shown in your presented snapshot has nothing to do with Aspose.Cells API. You have perhaps accidentally activated the “Group Edit” mode. The said mode is used in MS Excel application to simultaneously edit data on multiple worksheets, that is; when you select several worksheets and then change the data on one of them, the changes are applied to the same cells on all the selected worksheets.
In order to deactivate the mode, please try by right clicking on a sheet and select Ungroup Sheets.
Hi,
Please try our latest version/fix: Aspose.Cells.GridWeb v17.3.4.
Your issue “CELLSNET-42557” should be fixed in it.
Let us know your feedback.
Thank you
The issues you have found earlier (filed as CELLSNET-42557) have been fixed in Aspose.Cells for .NET 17.4.0.
This message was posted using Notification2Forum from Downloads module by Aspose Notifier.