Hi,
private static Boolean doPivot(WorksheetCollection ws)
{
Worksheet wsPivot = ws.get("Pivot");
Worksheet wsData = ws.get("Data");
if(wsData == null) // is there a data tab?
return false;
// so check to see if the pivot tab already exists. if it does, whack it and replace it with a new one
if(wsPivot != null) // it exists...
ws.removeAt("Pivot");
ws.add("Pivot");
wsPivot = ws.get("Pivot");
Cells cells = wsData.getCells();
int lastRow = wsData.getCells().getMaxDataRow()+1;
PivotTableCollection pivotTables = wsPivot.getPivotTables();
int index = pivotTables.add("=Data!A3:E" + lastRow, "B3", "IF_Pivot");
PivotTable pivotTable = pivotTables.get(index);
pivotTable.setRowGrand(false);
pivotTable.setColumnGrand(false);
pivotTable.addFieldToArea(PivotFieldType.ROW, 0);
pivotTable.addFieldToArea(PivotFieldType.COLUMN, 2);
pivotTable.addFieldToArea(PivotFieldType.DATA, 3);
pivotTable.addFieldToArea(PivotFieldType.DATA, 4);
return true;
}
private static Boolean doCharting(WorksheetCollection ws)
{
Worksheet wsChart = ws.get("Chart");
// so check to see if the chart tab already exists. if it does, whack it and replace it with a new one
if(wsChart != null) // it exists...
ws.removeAt("Chart");
ws.add("Chart");
wsChart = ws.get("Chart");
int chartIndex = wsChart.getCharts().add(ChartType.COLUMN, 0, 5, 28, 16);
Chart chart = wsChart.getCharts().get(chartIndex);
// Set the pivot chart data source
chart.setPivotSource("Pivot!IF_Pivot");
chart.setHidePivotFieldButtons(false);
return true;
}