Pivot table report from dataset in Asp.net Web pages

Aspose.Cells Product Family
1

Hi

I would like to create a pivot table report and associated pie chart from dataset as data source. Can have a sample code to do that?

regards

S.Saba

2

Hi Saba,

Thanks for considering Aspose.

May the following sample code help you for your requirement. I create a pivot table and a pivot chart based on the datasource (I fill some cells with data, if you want to import data from dataset / datatable, you can use Cells.ImportDataTable() method for your need.):

Sample code:

//Instantiating an Workbook object
Workbook workbook = new Workbook();
//Obtaining the reference of the newly added worksheet
Worksheet sheet = workbook.Worksheets[0];
Cells cells = sheet.Cells;
//Setting the value to the cells
Cell cell = cells["A1"];
cell.PutValue("Sport");
cell = cells["B1"];
cell.PutValue("Quarter");
cell = cells["C1"];
cell.PutValue("Sales");


cell = cells["A2"];
cell.PutValue("Golf");
cell = cells["A3"];
cell.PutValue("Golf");
cell = cells["A4"];
cell.PutValue("Tennis");
cell = cells["A5"];
cell.PutValue("Tennis");
cell = cells["A6"];
cell.PutValue("Tennis");
cell = cells["A7"];
cell.PutValue("Tennis");
cell = cells["A8"];
cell.PutValue("Golf");


cell = cells["B2"];
cell.PutValue("Qtr3");
cell = cells["B3"];
cell.PutValue("Qtr4");
cell = cells["B4"];
cell.PutValue("Qtr3");
cell = cells["B5"];
cell.PutValue("Qtr4");
cell = cells["B6"];
cell.PutValue("Qtr3");
cell = cells["B7"];
cell.PutValue("Qtr4");
cell = cells["B8"];
cell.PutValue("Qtr3");

cell = cells["C2"];
cell.PutValue(1500);
cell = cells["C3"];
cell.PutValue(2000);
cell = cells["C4"];
cell.PutValue(600);
cell = cells["C5"];
cell.PutValue(1500);
cell = cells["C6"];
cell.PutValue(4070);
cell = cells["C7"];
cell.PutValue(5000);
cell = cells["C8"];
cell.PutValue(6430);
Worksheet sheet2 = workbook.Worksheets[workbook.Worksheets.Add(SheetType.Chart)];
sheet2.Name = "Pivot_Chart";
PivotTables pivotTables = sheet.PivotTables;
//Adding a PivotTable to the worksheet
int index = pivotTables.Add("=A1:C8", "E20", "PivotTable1");
//Accessing the instance of the newly added PivotTable
PivotTable pivotTable = pivotTables[index];
//Draging the first field to the row area.
pivotTable.AddFieldToArea(PivotFieldType.Row, 0);
//Draging the second field to the column area.
pivotTable.AddFieldToArea(PivotFieldType.Column, 1);
//Draging the third field to the data area.
pivotTable.AddFieldToArea(PivotFieldType.Data, 2);
index = sheet2.Charts.Add(ChartType.Pie, 0, 5, 10, 10);
sheet2.Charts[index].PivotSource = "Sheet1!PivotTable1";
sheet2.Charts[index].HidePivotFieldButtons = false;
//Saving the Excel file
workbook.Save(@"d:\test\pivotreport_chart1.xls");

Kindly also check the docs in the section: http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/worthy-features.html

Thank you.

3

Amjad

Thanks for your quick response. The sample code you sent works great. What i am looking is how to use Apose.Cells to create a pivot table dynamically from dataset on a aspx web page. Something similar to the link below.

http://demos.devexpress.com/ASPxPivotGridDemos/SampleReports/CustomerReports.aspx

regards

S.Saba

4

Hi Saba,

Since you need to provide your pivot table in a grid in WYSIWYG manner, I think you can try our Grid control i.e. Aspose.Grid (GridWeb). The control supports the three kinds of data sources, from which you can build your PivotTable report: e.g., Aspose.Grid.Web.Data.WebWorksheet, System.Data.DataTable and System.Data.DataView. Please check the source codes of the following demos (the demo solutions get intalled automatically on your system when you use Aspose.Grid.MSI installer, so you may open the solutions into VS.NET and check the source codes):

Thank you.

5

Hello Amjad

The samples looks good and that is what i was looking for and i have couple of questions on the PivotTable

1. How interactive the pivot chart, with expanding and collapsing the columns in the row area and making first 2 levels of the report expanded and others collapsed while rendering the report

2. The samlpes i have worked, i did not see the expand collapse options. Is that doable?

3. If interactivity is available with the pivottable, how to link the dynamic state of the pivottable with pivot chart to generate dynamic charts based on pivot table.

hope i will get the quick response a usual.

regards

S.Saba

6

Hi,

Well, expanding / collapsing feature is not available with Pivot Tables currently. we may consider to support it soon, we support this feature only in data binding currently:

3. If interactivity is available with the pivot table, how to link the dynamic state of the pivot table with pivot chart to generate dynamic charts based on pivot table.

The chart feature is not supported yet, we support it in our future versions soon.

Thank you.

7

Amjad

Thanks a lot for the clarifications. It helps me to decide on couple of technical issue.

regards

S.Saba