Hi Aspose
I’ve downloaded a evaluation copy of Aspose.Cells with the purpose to get rid of Excel on our web server.
I’m using Excel to create Pivot tables and now I’m doing my best to create the same result with the API of Aspose.Cell. There’s some documentation regarding Aspose.Cells and pivot tables, but it’s quite trivial and not that detailed and I couldn’t find any answers in forums.
The current problems (using VB.net):
- Excel interop lets me use a cache to create all data from which I create the pivot tables later on.
Dim cache As PivotCache = wb.PivotCaches.Add(Excel.XlPivotTableSourceType.xlDatabase, "PivotReport_Data!" & dataRange.Address(, , Excel.XlReferenceStyle.xlR1C1))
Is there something similar in Aspose.Cells?
- I’m not interested in the grand totals for rows and have tried the pivotTable.RowGrand = False. Despite this, I’m still getting the grand totals for each row.
Could be I’ll get back to you with more questions further on.
I will most certainly purchase 10 developer license of Aspose.Cells, if it’ll live up to my expectations.
Best regards
Anders Nordström
This message was posted using Email2Forum by ShL77.
Hi,
Thank you for considering Aspose.
Anders.Nordstrom:
Excel interop lets me use a cache to create all data from which I create the pivot tables later on.
Dim cache As PivotCache = wb.PivotCaches.Add(Excel.XlPivotTableSourceType.xlDatabase, “PivotReport_Data!” & dataRange.Address(, , Excel.XlReferenceStyle.xlR1C1))
Is there something similar in Aspose.Cells?
You may use Worksheet.PivotTables.Add() method as per your need.
Anders.Nordstrom:
I’m not interested in the grand totals for rows and have tried the pivotTable.RowGrand = False. Despite this, I’m still getting the grand totals for each row.
Which Version of Aspose.Cells are you using. Please try the attached latest version, if you still face any problem, please share your template file and sample code to reproduce the issue and we will check it soon.
Thank You & Best Regards,
I really would like to use a cache feature like the one in my Excel example, because of the fact that the data should not be visible together with the pivot table as in the pivot table example of yours.
I’m using Aspose.Cells for NET 4.8.0.0 (evaluation copy)
Here’s my example code. The commented lines are the ones I’m trying to find equivalence model for in Apose.Cells API. Highlighted in blue are the properties I thought should hide the grand total on a row basis. As you can see I’ve even tried the pivot.ColumnGrand = False without any difference in output.
Private Sub CreatePlanningPivotTable(ByVal wb As Workbook, ByVal dataRange As Cells, ByVal level As ReportLevel)
'Dim cache As PivotCache = wb.PivotCaches.Add(Excel.XlPivotTableSourceType.xlDatabase, “PivotReport_Data!” & dataRange.Address(, , Excel.XlReferenceStyle.xlR1C1))
'Dim wsPivot As Excel.Worksheet = CType(wb.Worksheets.Add(), Excel.Worksheet)
Dim sheet As Worksheet = wb.Worksheets(0)
Dim pivotTables As PivotTables = sheet.PivotTables
'Adding a PivotTable to the worksheet
Dim index As Integer = pivotTables.Add("=A1:" & CellsHelper.ColumnIndexToName(dataRange.MaxColumn) & dataRange.MaxDataRow, “Z1”, “PivotReport”)
'Accessing the instance of the newly added PivotTable
Dim pivot As PivotTable = pivotTables(index)
'wsPivot.Name = “PivotReport”
'Dim pivot As Excel.PivotTable = Cache.CreatePivotTable(wsPivot.Range(“B13”), , , Excel.XlPivotTableVersionList.xlPivotTableVersion2000)
pivot.RowGrand = False
pivot.ColumnGrand = False
Select Case level
Case ReportLevel.Cluster, ReportLevel.All
'Draging the first field to the row area.
pivot.AddFieldToArea(PivotFieldType.Page, “Cluster”)
End Select
‘’------------------------- Page -------------------------------
pivot.AddFieldToArea(PivotFieldType.Page, “Market Company”)
pivot.AddFieldToArea(PivotFieldType.Page, “Country”)
pivot.AddFieldToArea(PivotFieldType.Page, “GiKAM”)
pivot.AddFieldToArea(PivotFieldType.Page, “GiKAM Allocated”)
pivot.AddFieldToArea(PivotFieldType.Page, “Category”)
pivot.AddFieldToArea(PivotFieldType.Page, “Product Group”)
pivot.AddFieldToArea(PivotFieldType.Page, “Project Type”)
‘’------------------------- Rows -------------------------------
pivot.RowFields(index).SetSubtotals(PivotFieldSubtotalType.None, False)
pivot.AddFieldToArea(PivotFieldType.Row, “PreProject Number”)
pivot.AddFieldToArea(PivotFieldType.Row, “PreProject Name”)
pivot.AddFieldToArea(PivotFieldType.Row, “Objective”)
pivot.AddFieldToArea(PivotFieldType.Row, “Target”)
pivot.AddFieldToArea(PivotFieldType.Row, “Sub Category”)
pivot.AddFieldToArea(PivotFieldType.Row, “System”)
pivot.AddFieldToArea(PivotFieldType.Row, “Distribution”)
pivot.AddFieldToArea(PivotFieldType.Row, “Responsible”)
pivot.AddFieldToArea(PivotFieldType.Row, “Marketing Intent”)
pivot.AddFieldToArea(PivotFieldType.Row, “MWB”)
pivot.AddFieldToArea(PivotFieldType.Row, “Initiative”)
pivot.AddFieldToArea(PivotFieldType.Row, “Initiative Priority”)
pivot.AddFieldToArea(PivotFieldType.Row, “Details”)
‘’------------------------- Data -------------------------------
pivot.AddFieldToArea(PivotFieldType.Data, “TPInvestment”)
'With CType(pivot.PivotFields(“TPInvestment”), Excel.PivotField)
’ .SubtotalName = “Total Investment”
'End With
'pivot.AddDataField(pivot.PivotFields(“TPInvestment”), “Tetra Pak Investment (k€)”, Excel.XlConsolidationFunction.xlSum)
'pivot.PivotCache.Refresh()
End Sub
Hope you can help me
Best Regards
Anders
Hi,
Well, I tried creating pivot table with grand rows and columns hidden, it works fine. See the following sample code and attached is the output file.
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
Aspose.Cells.Cell cell = cells[“A1”];
cell.PutValue(“Employee”);
cell = cells[“B1”];
cell.PutValue(“Quarter”);
cell = cells[“C1”];
cell.PutValue(“Product”);
cell = cells[“D1”];
cell.PutValue(“Continent”);
cell = cells[“E1”];
cell.PutValue(“Country”);
cell = cells[“F1”];
cell.PutValue(“Sale”);
cell = cells[“A2”];
cell.PutValue(“David”);
cell = cells[“A3”];
cell.PutValue(“David”);
cell = cells[“A4”];
cell.PutValue(“David”);
cell = cells[“A5”];
cell.PutValue(“David”);
cell = cells[“A6”];
cell.PutValue(“James”);
cell = cells[“A7”];
cell.PutValue(“James”);
cell = cells[“A8”];
cell.PutValue(“James”);
cell = cells[“A9”];
cell.PutValue(“James”);
cell = cells[“A10”];
cell.PutValue(“James”);
cell = cells[“A11”];
cell.PutValue(“Miya”);
cell = cells[“A12”];
cell.PutValue(“Miya”);
cell = cells[“A13”];
cell.PutValue(“Miya”);
cell = cells[“A14”];
cell.PutValue(“Miya”);
cell = cells[“A15”];
cell.PutValue(“Miya”);
cell = cells[“A16”];
cell.PutValue(“Miya”);
cell = cells[“A17”];
cell.PutValue(“Miya”);
cell = cells[“A18”];
cell.PutValue(“Elvis”);
cell = cells[“A19”];
cell.PutValue(“Elvis”);
cell = cells[“A20”];
cell.PutValue(“Elvis”);
cell = cells[“A21”];
cell.PutValue(“Elvis”);
cell = cells[“A22”];
cell.PutValue(“Elvis”);
cell = cells[“A23”];
cell.PutValue(“Elvis”);
cell = cells[“A24”];
cell.PutValue(“Elvis”);
cell = cells[“A25”];
cell.PutValue(“Jean”);
cell = cells[“A26”];
cell.PutValue(“Jean”);
cell = cells[“A27”];
cell.PutValue(“Jean”);
cell = cells[“A28”];
cell.PutValue(“Ada”);
cell = cells[“A29”];
cell.PutValue(“Ada”);
cell = cells[“A30”];
cell.PutValue(“Ada”);
cell = cells[“B2”];
cell.PutValue(“1”);
cell = cells[“B3”];
cell.PutValue(“2”);
cell = cells[“B4”];
cell.PutValue(“3”);
cell = cells[“B5”];
cell.PutValue(“4”);
cell = cells[“B6”];
cell.PutValue(“1”);
cell = cells[“B7”];
cell.PutValue(“2”);
cell = cells[“B8”];
cell.PutValue(“3”);
cell = cells[“B9”];
cell.PutValue(“4”);
cell = cells[“B10”];
cell.PutValue(“4”);
cell = cells[“B11”];
cell.PutValue(“1”);
cell = cells[“B12”];
cell.PutValue(“1”);
cell = cells[“B13”];
cell.PutValue(“2”);
cell = cells[“B14”];
cell.PutValue(“2”);
cell = cells[“B15”];
cell.PutValue(“3”);
cell = cells[“B16”];
cell.PutValue(“4”);
cell = cells[“B17”];
cell.PutValue(“4”);
cell = cells[“B18”];
cell.PutValue(“1”);
cell = cells[“B19”];
cell.PutValue(“1”);
cell = cells[“B20”];
cell.PutValue(“2”);
cell = cells[“B21”];
cell.PutValue(“3”);
cell = cells[“B22”];
cell.PutValue(“3”);
cell = cells[“B23”];
cell.PutValue(“4”);
cell = cells[“B24”];
cell.PutValue(“4”);
cell = cells[“B25”];
cell.PutValue(“1”);
cell = cells[“B26”];
cell.PutValue(“2”);
cell = cells[“B27”];
cell.PutValue(“3”);
cell = cells[“B28”];
cell.PutValue(“1”);
cell = cells[“B29”];
cell.PutValue(“2”);
cell = cells[“B30”];
cell.PutValue(“3”);
cell = cells[“C2”];
cell.PutValue(“Maxilaku”);
cell = cells[“C3”];
cell.PutValue(“Maxilaku”);
cell = cells[“C4”];
cell.PutValue(“Chai”);
cell = cells[“C5”];
cell.PutValue(“Maxilaku”);
cell = cells[“C6”];
cell.PutValue(“Chang”);
cell = cells[“C7”];
cell.PutValue(“Chang”);
cell = cells[“C8”];
cell.PutValue(“Chang”);
cell = cells[“C9”];
cell.PutValue(“Chang”);
cell = cells[“C10”];
cell.PutValue(“Chang”);
cell = cells[“C11”];
cell.PutValue(“Geitost”);
cell = cells[“C12”];
cell.PutValue(“Chai”);
cell = cells[“C13”];
cell.PutValue(“Geitost”);
cell = cells[“C14”];
cell.PutValue(“Geitost”);
cell = cells[“C15”];
cell.PutValue(“Maxilaku”);
cell = cells[“C16”];
cell.PutValue(“Geitost”);
cell = cells[“C17”];
cell.PutValue(“Geitost”);
cell = cells[“C18”];
cell.PutValue(“Ikuru”);
cell = cells[“C19”];
cell.PutValue(“Ikuru”);
cell = cells[“C20”];
cell.PutValue(“Ikuru”);
cell = cells[“C21”];
cell.PutValue(“Ikuru”);
cell = cells[“C22”];
cell.PutValue(“Ipoh Coffee”);
cell = cells[“C23”];
cell.PutValue(“Ipoh Coffee”);
cell = cells[“C24”];
cell.PutValue(“Ipoh Coffee”);
cell = cells[“C25”];
cell.PutValue(“Chocolade”);
cell = cells[“C26”];
cell.PutValue(“Chocolade”);
cell = cells[“C27”];
cell.PutValue(“Chocolade”);
cell = cells[“C28”];
cell.PutValue(“Chocolade”);
cell = cells[“C29”];
cell.PutValue(“Chocolade”);
cell = cells[“C30”];
cell.PutValue(“Chocolade”);
cell = cells[“D2”];
cell.PutValue(“Asia”);
cell = cells[“D3”];
cell.PutValue(“Asia”);
cell = cells[“D4”];
cell.PutValue(“Asia”);
cell = cells[“D5”];
cell.PutValue(“Asia”);
cell = cells[“D6”];
cell.PutValue(“Europe”);
cell = cells[“D7”];
cell.PutValue(“Europe”);
cell = cells[“D8”];
cell.PutValue(“Europe”);
cell = cells[“D9”];
cell.PutValue(“Europe”);
cell = cells[“D10”];
cell.PutValue(“Europe”);
cell = cells[“D11”];
cell.PutValue(“America”);
cell = cells[“D12”];
cell.PutValue(“America”);
cell = cells[“D13”];
cell.PutValue(“America”);
cell = cells[“D14”];
cell.PutValue(“America”);
cell = cells[“D15”];
cell.PutValue(“America”);
cell = cells[“D16”];
cell.PutValue(“America”);
cell = cells[“D17”];
cell.PutValue(“America”);
cell = cells[“D18”];
cell.PutValue(“Europe”);
cell = cells[“D19”];
cell.PutValue(“Europe”);
cell = cells[“D20”];
cell.PutValue(“Europe”);
cell = cells[“D21”];
cell.PutValue(“Oceania”);
cell = cells[“D22”];
cell.PutValue(“Oceania”);
cell = cells[“D23”];
cell.PutValue(“Oceania”);
cell = cells[“D24”];
cell.PutValue(“Oceania”);
cell = cells[“D25”];
cell.PutValue(“Africa”);
cell = cells[“D26”];
cell.PutValue(“Africa”);
cell = cells[“D27”];
cell.PutValue(“Africa”);
cell = cells[“D28”];
cell.PutValue(“Africa”);
cell = cells[“D29”];
cell.PutValue(“Africa”);
cell = cells[“D30”];
cell.PutValue(“Africa”);
cell = cells[“E2”];
cell.PutValue(“China”);
cell = cells[“E3”];
cell.PutValue(“India”);
cell = cells[“E4”];
cell.PutValue(“Korea”);
cell = cells[“E5”];
cell.PutValue(“India”);
cell = cells[“E6”];
cell.PutValue(“France”);
cell = cells[“E7”];
cell.PutValue(“France”);
cell = cells[“E8”];
cell.PutValue(“Germany”);
cell = cells[“E9”];
cell.PutValue(“Italy”);
cell = cells[“E10”];
cell.PutValue(“France”);
cell = cells[“E11”];
cell.PutValue(“U.S.”);
cell = cells[“E12”];
cell.PutValue(“U.S.”);
cell = cells[“E13”];
cell.PutValue(“Brazil”);
cell = cells[“E14”];
cell.PutValue(“U.S.”);
cell = cells[“E15”];
cell.PutValue(“U.S.”);
cell = cells[“E16”];
cell.PutValue(“Canada”);
cell = cells[“E17”];
cell.PutValue(“U.S.”);
cell = cells[“E18”];
cell.PutValue(“Italy”);
cell = cells[“E19”];
cell.PutValue(“France”);
cell = cells[“E20”];
cell.PutValue(“Italy”);
cell = cells[“E21”];
cell.PutValue(“New Zealand”);
cell = cells[“E22”];
cell.PutValue(“Australia”);
cell = cells[“E23”];
cell.PutValue(“Australia”);
cell = cells[“E24”];
cell.PutValue(“New Zealand”);
cell = cells[“E25”];
cell.PutValue(“S.Africa”);
cell = cells[“E26”];
cell.PutValue(“S.Africa”);
cell = cells[“E27”];
cell.PutValue(“S.Africa”);
cell = cells[“E28”];
cell.PutValue(“Egypt”);
cell = cells[“E29”];
cell.PutValue(“Egypt”);
cell = cells[“E30”];
cell.PutValue(“Egypt”);
cell = cells[“F2”];
cell.PutValue(2000);
cell = cells[“F3”];
cell.PutValue(500);
cell = cells[“F4”];
cell.PutValue(1200);
cell = cells[“F5”];
cell.PutValue(1500);
cell = cells[“F6”];
cell.PutValue(500);
cell = cells[“F7”];
cell.PutValue(1500);
cell = cells[“F8”];
cell.PutValue(800);
cell = cells[“F9”];
cell.PutValue(900);
cell = cells[“F10”];
cell.PutValue(500);
cell = cells[“F11”];
cell.PutValue(1600);
cell = cells[“F12”];
cell.PutValue(600);
cell = cells[“F13”];
cell.PutValue(2000);
cell = cells[“F14”];
cell.PutValue(500);
cell = cells[“F15”];
cell.PutValue(900);
cell = cells[“F16”];
cell.PutValue(700);
cell = cells[“F17”];
cell.PutValue(1400);
cell = cells[“F18”];
cell.PutValue(1350);
cell = cells[“F19”];
cell.PutValue(300);
cell = cells[“F20”];
cell.PutValue(500);
cell = cells[“F21”];
cell.PutValue(1000);
cell = cells[“F22”];
cell.PutValue(1500);
cell = cells[“F23”];
cell.PutValue(1500);
cell = cells[“F24”];
cell.PutValue(1600);
cell = cells[“F25”];
cell.PutValue(1000);
cell = cells[“F26”];
cell.PutValue(1200);
cell = cells[“F27”];
cell.PutValue(1300);
cell = cells[“F28”];
cell.PutValue(1500);
cell = cells[“F29”];
cell.PutValue(1400);
cell = cells[“F30”];
cell.PutValue(1000);
PivotTables pivotTables = sheet.PivotTables;
//Adding a PivotTable to the worksheet
int index = pivotTables.Add(“=A1:F30”, “H3”, “PivotTable2”);
//Accessing the instance of the newly added PivotTable
PivotTable pivotTable = pivotTables[index];
//Unshowing grand totals for rows & columns.
pivotTable.RowGrand = false;
pivotTable.ColumnGrand = false;
//Draging the first field to the row area.
pivotTable.AddFieldToArea(PivotFieldType.Row, 0);
//Draging the third field to the row area.
pivotTable.AddFieldToArea(PivotFieldType.Row, 2);
//Draging the second field to the row area.
pivotTable.AddFieldToArea(PivotFieldType.Row, 1);
// pivotTable.RowFields[0].SetSubtotals(PivotFieldSubtotalType.Count, true);
// pivotTable.RowFields[1].SetSubtotals(PivotFieldSubtotalType.None, false);
// pivotTable.RowFields[2].SetSubtotals(PivotFieldSubtotalType.None, false);
//Draging the fourth field to the column area.
pivotTable.AddFieldToArea(PivotFieldType.Column, 3);
pivotTable.ColumnFields[0].IsAutoSort = true;
//Draging the fifth field to the data area.
pivotTable.AddFieldToArea(PivotFieldType.Data, 5);
pivotTable.DataFields[0].NumberFormat = “$#,##0.0000”;
//Saving the Excel file
workbook.Save(“f:\test\mytest_pvtable3.xls”);
Please try the version(attached by Nausherwan) or preferably try the latest version downloading@: http://www.aspose.com/community/files/51/.net-components/aspose.cells-for-.net/entry207989.aspx
If you still find the issue, kindly create a sample console application with output file, zip it and post it here, we will check it soon.
Thank you.
Hi again,
I find this very strange. Having installed the new demo version 4.8.1 (replaced Aspose.Cells.dll file) as suggested and implemented your example code into a test project I tried to run this. Resulted in a pivot table free from grand totals on rows and columns, exactly the way I want. From here I tried a minor modification to visualize the difference:
I set pivotTable.RowGrand = true; pivotTable.ColumnGrand = true; compiled the project. Result as expected. However when I revert the properties pivotTable.RowGrand = false; pivotTable.ColumnGrand = false; and recompile I still get the grand totals on rows (file attached). Nothing else has been changed in the example code you’ve sent me.
Could you please try the procedure above?
This is my number one question at the moment.
I’m still curious about the cache feature in my previous post. Either a Aspose.Cells caching example or a example of placing the data (cell values) into another sheet from which the pivot table is created and after this is done hide this data sheet would be helpful.
B R
Anders
Hi,
Thank you for considering Aspose.
Well, I tried your scenario but I was unable to reproduce the issue. Please use Build --> Clean Solution & Build -->{Your Project} options to clean the project & Solution and try it again. I you still face any problem, please create a sample application and post it here. We will check it soon.
Thank You & Best Regards,
Hi again and thanks for your support
I didn’t get it to work with the “Clean Solution & Build” mentioned. I had to show each RowField’s Subtotals by using:
For i As Integer = 0 To pivot.RowFields.Count - 1
pivot.RowFields(i).SetSubtotals(PivotFieldSubtotalType.None, False)
Next
Moving further I get stuck in layout issues.
1. How do I autofit the pivot table columns? I’ve tried PivotSheet.AutoFitColumns() without any luck.
2. And if I would like to set maximum width for each column, how do I accomplish that? Can’t find anything in API documentation about column width.
3. I’d like to set background color, font size and border lines etc with the style attribute, but I’ve read somewhere in forum this wasn’t supported on pivot tables. Is this correct?
4. I’ve tried two of the report designs of PivotTableAutoFormatType. I didn’t like what they did to my pivot table. Are there some design overview of these ten report types somewhere?
5. When I add my pivot table using
pivotTables.Add("=Data!A1:X1500", “B5”, “PivotReport”)
it starts in cell B1. No matter what column I try it will stick to row 1 in that column . Why?
Hope you can help me with all the questions above. You’re the experts!
Best regards
Anders
Hi,
“I set pivotTable.RowGrand = true;
pivotTable.ColumnGrand = true; compiled the project. Result as
expected. However when I revert the properties pivotTable.RowGrand =
false; pivotTable.ColumnGrand = false; and recompile I still get the
grand totals on rows (file attached). Nothing else has been changed in
the example code you’ve sent me.”
I checked your generated file, it is fine. Actually these are not Row Grand Totals rather subtotals for rows, you may set them to None if you don’t want these subtotals to be shown for row fields.
I have tested this code and it works fine, the row fields subtotals won’t be shown:
For i As Integer = 0 To pivot.RowFields.Count - 1
pivot.RowFields(i).SetSubtotals(PivotFieldSubtotalType.None, False)
Next
1. How do I autofit the pivot table columns? I’ve tried PivotSheet.AutoFitColumns() without any luck.
Well, I am afraid, this (auto-fit operation) won’t work for pivot table report. The reason is we don’t support it. We are currently working on reading and manipulating pivot tables, hopefully the feature would be available in Q1 2010.
2.
And if I would like to set maximum width for each column, how do I
accomplish that? Can’t find anything in API documentation about column
width.
Yes you may set column’s width,
see the following sample code, it will extend the H column width:
sheet.Cells.SetColumnWidth(7, 30);
See the documentation for reference: http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/adjusting-row-height-column-width.html
3. I’d like to set background color, font size and border
lines etc with the style attribute, but I’ve read somewhere in forum
this wasn’t supported on pivot tables. Is this correct?
Yes, that’s true, the feature is not available currently.
4. I’ve
tried two of the report designs of PivotTableAutoFormatType. I didn’t
like what they did to my pivot table. Are there some design overview of
these ten report types somewhere?
Well, I am afraid you have to use existing auto formatting style to format a pivot table report, e.g
pivotTable.AutoFormatType = PivotTableAutoFormatType.Report6;
5. When I add my pivot table using
pivotTables.Add(“=Data!A1:X1500”, “B5”, “PivotReport”)
it starts in cell B1. No matter what column I try it will stick to row 1 in that column . Why?
Well, I have tested and I don’t find this issue at all.
Could you create a sample console application to show the issue we will check it soon.
Thank you.
Anders.Nordstrom: