Pivot Tables Help

Aspose.Cells Product Family
1

Hi

I am working on Pivot Tables using MS Excel , but that has lots of issues.

I want to know does your product support Pivot Tables .

If yes , which one Aspose.Cells or Aspose.Chart or any other ...?Please send me the link.

I also want link for online demo , where i can see how u r making Pivot Tables.

Thanks

MG

2

Aspose.Cells supports pivot table in this way: Aspose.Cells PivotTable .

Aspose.Grid also supports pivot table, please check online demo at Aspose.Grid Demos.

3

It seems exactly as of MS Excel ......is there any diff also ?

I am using MS Excel right now and getting these errors

1>Call was rejected by the callee

2>The message filter indicated that the application is busy

So if i use your excel , do i need to face all these problems ?

Any explaination for your reasons ?

Thanks

4

I am not sure about your problems. Could you please send a sample file or screenshot to show them?

Aspose.Cells is a spreadsheet engine to import/export Excel files. You have to use MS Excel to view the files.

Aspose.Grid is a component to mimic Excel UI and it doesn't need MS Excel at all.

5

I have template excel which resides on the server directory and i am using this code to bind all data from dataset/data dreader to my template excel

http://steveorr.net/articles/ExcelExport.aspx.

Excel has 2 sheets 1 for data and other for pivot tables - so everytime data loads and I have done that refresh property clicked ......blah blah.....so i get new pivot tables everytime.

1> If i use Aspose Excel instead of MS Excel - what benefits will I get.

2>I sent you asp.net code - if I use Aspose excel then how will I open the template excel ?

Thanks

6

Aspose.Excel is changed to Aspose.Cells.

Have you checked Pivot Table ?

I think your need can be served with Aspose.Cells.

For the benefits, please check Why Not Automation.

7

@munishmg,

Using the latest Aspose.Cells APIs, it is quite easy to create Pivot tables and pivot charts in Excel spreadsheets. See the sample code for your reference:
e.g
Sample code:

// Instantiating a 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);

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

// Adding a PivotTable to the worksheet
int index = pivotTables.Add("=A1:C8", "E3", "PivotTable2");

// Accessing the instance of the newly added PivotTable
Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];

// Unshowing grand totals for rows.
pivotTable.RowGrand = false;

// Draging the first field to the row area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 0);

// Draging the second field to the column area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 1);

// Draging the third field to the data area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 2);
         
// Saving the Excel file
workbook.Save(dataDir + "pivotTable_test_out.xls");

Also, see the document on how to create pivot chart for your reference: