Please let me kwow how to add report filter.
How to use "pivotTable.ReportFilters"?
Thanks
Workbook workbook = new Workbook();
workbook.LoadFromFile("sample.xlsx");
Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable pivotTable = workbook.Worksheets["Pivot Table"].PivotTables[0] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable;
PivotReportFilter filter = new PivotReportFilter("Name", true);
pivotTable.ReportFilters.Add(filter);
workbook.SaveToFile("result.xlsx", ExcelVersion.Version2007);
Workbook workbook = new Workbook();
workbook.LoadFromFile("CreatePivotTable.xlsx");
Worksheet sheet = workbook.Worksheets[0];
Worksheet sheet2 = workbook.CreateEmptySheet();
sheet2.Name = "Pivot";
CellRange dataRange = sheet.Range["A1:C17"];
PivotCache cache = workbook.PivotCaches.Add(dataRange);
PivotTable pt = sheet2.PivotTables.Add("Pivot Table", sheet.Range["A1"], cache);
pt.EnableDrilldown = true;
var r1 = pt.PivotFields["Product"];
r1.Axis = AxisTypes.Row;
var r2 = pt.PivotFields["Month"];
r2.Axis = AxisTypes.Row;
pt.DataFields.Add(pt.PivotFields["Count"], "Sum of Count", SubtotalTypes.Sum);
//**********************************************
//select the default value in report filter
PivotReportFilter filter = new PivotReportFilter("Product", true);
filter.IsMultipleSelect = true;
List<string> list = new List<string>();
list.Add("SpireDoc");
filter.FilterItemStrings = list;
pt.ReportFilters.Add(filter);
//**********************************************
pt.CalculateData();
pt.Cache.IsRefreshOnLoad = true;
workbook.SaveToFile("result.xlsx",FileFormat.Version2013);
PivotReportFilter locationFilter = new PivotReportFilter("LOCATION", true);
locationFilter.isMultipleSelect(true);
pivotTable.getReportFilters().addItem(locationFilter);
Workbook workbook = new Workbook();
workbook.loadFromFile("data/pivotTable.xlsx");
Worksheet sheet = workbook.getWorksheets().get(0);
Worksheet sheet2 = workbook.createEmptySheet();
sheet2.setName("Pivot");
CellRange dataRange = sheet.getCellRange("A1:C7");
PivotCache cache = workbook.getPivotCaches().add(dataRange);
PivotTable pivotTable = sheet2.getPivotTables().add("Pivot Table", sheet.getCellRange("A1"), cache);
PivotReportFilter locationFilter = new PivotReportFilter("LOCATION", true);
locationFilter.isMultipleSelect(true);
pivotTable.getReportFilters().add(locationFilter);
The method add(PivotReportFilter) is undefined for the type CollectionExtended
Worksheet pivotSheet = workbook.getWorksheets().get(1);
pivotSheet.setName("PIVOT");
CellRange dataRange = dataSheet.getCellRange("A1:G" + rsUtilDataColumnCount);
PivotCache cache = workbook.getPivotCaches().add(dataRange);
PivotTable pivotTable = pivotSheet.getPivotTables().add("Pivot Table", pivotSheet.getCellRange("A1"), cache);
PivotReportFilter prLocationFilter = new PivotReportFilter("LOC", true);
prLocationFilter.isMultipleSelect(true);
pivotTable.getReportFilters().addItem(prLocationFilter);
PivotField pfPlatformColumn = (PivotField) pivotTable.getPivotFields().get("PLATFORM");
pfPlatformColumn.setAxis(AxisTypes.Column);
PivotField pfStatusColumn = (PivotField) pivotTable.getPivotFields().get("STATUS");
pfStatusColumn.setAxis(AxisTypes.Column);
PivotTableFields ptFields = pivotTable.getPivotFields();
PivotDataFields ptDataFields = pivotTable.getDataFields();
IPivotField iField = null;
String iFieldName = "";
for(int i = 0; i < ptFields.getCount(); i++)
{
iField = ptFields.get(i);
iFieldName = iField.getName();
if(iFieldName.contains("WW"))
{
ptDataFields.add(iField, iFieldName, SubtotalTypes.Average);
}
}
Index is less than 0 or more than or equal to the list count.
Parameter name: index
Actual value was 1.
//Code using the older version
pivotTable.getReportFilters().addItem(prLocationFilter);
//Changes on code after upgrading to the latest version
pivotTable.getReportFilters().add(prLocationFilter);
Besides, I noticed that in your expect file, the "Values" field is in the row labels. I am sorry that our Spire.XLS does not yet support moving this field to row labels.
Workbook workbook = new Workbook();
workbook.loadFromFile("data/Generated_File.xlsx");
Worksheet pivotSheet = workbook.getWorksheets().get(1);
pivotSheet.setName("PIVOT");
CellRange dataRange = workbook.getWorksheets().get(0).getCellRange("A1:G523");
PivotCache cache = workbook.getPivotCaches().add(dataRange);
pivotSheet.getPivotTables().removeAt(0);
PivotTable pivotTable = pivotSheet.getPivotTables().add("Pivot Table", pivotSheet.getCellRange("A1"), cache);
PivotField pfPlatformColumn = (PivotField) pivotTable.getPivotFields().get("PLATFORM");
pfPlatformColumn.setAxis(AxisTypes.Column);
PivotField pfStatusColumn = (PivotField) pivotTable.getPivotFields().get("STATUS");
pfStatusColumn.setAxis(AxisTypes.Column);
PivotReportFilter prLocationFilter = new PivotReportFilter("LOC", true);
prLocationFilter.isMultipleSelect(true);
pivotTable.getReportFilters().add(prLocationFilter);
PivotTableFields ptFields = pivotTable.getPivotFields();
PivotDataFields ptDataFields = pivotTable.getDataFields();
IPivotField iField = null;
String iFieldName = "";
for(int i = 0; i < ptFields.getCount(); i++)
{
iField = ptFields.get(i);
iFieldName = iField.getName();
if(iFieldName.contains("WW"))
{
ptDataFields.add(iField, iFieldName, SubtotalTypes.Average);
}
}
workbook.saveToFile("result.xlsx", ExcelVersion.Version2016);
Besides, regarding moving the "Values" field to row labels, we have added it as a new feature into our upgrade list with the ticket SPIREXLS-3348. If it can be implemented in the future, we will inform you immediately.
Worksheet chartSheet = workbook.getWorksheets().get(2);
chartSheet.setName("CHART");
IPivotTable iPivotTable = pivotSheet.getPivotTables().get(0);
chartSheet.setGridLinesVisible(false);
chartSheet.setRowColumnHeadersVisible(false);
Chart chart = chartSheet.getCharts().add(ExcelChartType.Line, iPivotTable);
chart.setTopRow(2);
chart.setBottomRow(35);
chart.setLeftColumn(6);
chart.setRightColumn(21);
chart.setChartTitle("Platform utilization vs Time");
workbook.saveToFile(excelFilePath, ExcelVersion.Version2013);