PivotTable Feature

Aspose.Cells Product Family
1

Hi,

We use Aspose.Cells for many things - Thank you!

We have a new thing that we would like to use it for -

We have a DataSet that has some data that we usually use Smart Markers to populate an excel sheet.

Is there a way to take a DataSet, pass it into the PivotTable feature that Aspose has and get back a pivoted DataSet so that we can send it to the Smart Markers? (We dont want to see the Pivot object in the worksheet)

Please let us know - we are waiting to see your response :)

Thanks!

2

Hi,

Anyone know how this can be done? Basically we would like to Pivot the DataSet and then using it to populate Smart Markers.

Thanks!

3

Hi,

Thanks for considering Aspose.

Well, Actually Aspose.Cells does not create / manage pivot tables in the workbook. It only sets the pivot data and forces MS Excel to create and refresh pivot table when you open the output excel file into MS Excel.

So, I think apparently you should fill your worksheet processing the smart markers based on the dataset. You may use Aspose.Cells pivot table related APIs to generate the pivot table based on the data source range but mind you you cannot access / get the pivot data as the pivot table will be only created when you open the file into MS Excel.

Thank you.

4

Hi,

Are there any plans to have Aspose.Cells create a Pivot Table in Excel by specifying which columns should be pivoted in datatable?

Thanks!

5

Hi,

Thank you for considering Aspose.

Well, you can import the data from your datatable into the worksheet and create a pivot table from that

data. Please see the following sample code which creates a pivot table using the data from a database

into a datatable and then creates a pivot table by using the datatable columns.

Sample Code:

System.Data.OleDb.OleDbConnection oleDbConnection1 = new System.Data.OleDb.OleDbConnection();
oleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path +

"\\Database\\Northwind.mdb";
System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter1 = new

System.Data.OleDb.OleDbDataAdapter();
System.Data.OleDb.OleDbCommand oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand();
oleDbSelectCommand1.Connection = oleDbConnection1;
oleDbDataAdapter1.SelectCommand =oleDbSelectCommand1;
DataTable dataTable1 = new DataTable();
oleDbConnection1.Open();
oleDbSelectCommand1.CommandText = @"SELECT DISTINCTROW Products.ProductName,
Categories.CategoryName,
Products.QuantityPerUnit,
Products.UnitsInStock
FROM Categories INNER JOIN Products
ON Categories.CategoryID = Products.CategoryID
WHERE
(((Products.Discontinued) = No))
Order by Products.ProductName";

oleDbDataAdapter1.Fill(dataTable1);

Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];
Cells cells = sheet.Cells;

sheet.Cells.ImportDataTable(dataTable1, true, "A1");

PivotTables pivotTables = sheet.PivotTables;

int index = pivotTables.Add("=A1:D6", "E3", "PivotTable2");

PivotTable pivotTable = pivotTables[index];

pivotTable.RowGrand = false;

pivotTable.AddFieldToArea(PivotFieldType.Row, 0);
pivotTable.AddFieldToArea(PivotFieldType.Data, 1);
pivotTable.AddFieldToArea(PivotFieldType.Data, 2);
pivotTable.AddFieldToArea(PivotFieldType.Data, 3);
pivotTable.AddFieldToArea(PivotFieldType.Column, pivotTable.DataField);

pivotTable.IsAutoFormat = true;
pivotTable.AutoFormatType = PivotTableAutoFormatType.Report4;

//Saving the Excel file
workbook.Save("C:\\PivotTable".xls");

Also, Please see the following documentation links regarding creation of Pivot Tables using Aspose.Cells

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/pivot-table.html

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/how-to-create-a-pivottable.html

Thank You & Best Regards,