import
com.spire.xls.AxisTypes;
import
com.spire.xls.CellRange;
import
com.spire.xls.ExcelVersion;
import
com.spire.xls.PivotBuiltInStyles;
import
com.spire.xls.PivotCache;
import
com.spire.xls.PivotField;
import
com.spire.xls.PivotTable;
import
com.spire.xls.SubtotalTypes;
import
com.spire.xls.Workbook;
import
com.spire.xls.Worksheet;
class
GFG {
public
static
void
main(String[] args)
{
Workbook workbook =
new
Workbook();
Worksheet sheet = workbook.getWorksheets().get(
0
);
sheet.getCellRange(
"A1"
).setValue(
"Student Name"
);
sheet.getCellRange(
"B1"
).setValue(
"Month"
);
sheet.getCellRange(
"C1"
).setValue(
"Attendance"
);
sheet.getCellRange(
"A2"
).setValue(
"Harry"
);
sheet.getCellRange(
"A3"
).setValue(
"Harry"
);
sheet.getCellRange(
"A4"
).setValue(
"Harry"
);
sheet.getCellRange(
"A5"
).setValue(
"Nicole"
);
sheet.getCellRange(
"A6"
).setValue(
"Nicole"
);
sheet.getCellRange(
"A7"
).setValue(
"Nicole"
);
sheet.getCellRange(
"A8"
).setValue(
"Peter"
);
sheet.getCellRange(
"A9"
).setValue(
"Peter"
);
sheet.getCellRange(
"A10"
).setValue(
"Peter"
);
sheet.getCellRange(
"A11"
).setValue(
"Lisa"
);
sheet.getCellRange(
"A12"
).setValue(
"Lisa"
);
sheet.getCellRange(
"A13"
).setValue(
"Lisa"
);
sheet.getCellRange(
"B2"
).setValue(
"January"
);
sheet.getCellRange(
"B3"
).setValue(
"February"
);
sheet.getCellRange(
"B4"
).setValue(
"March"
);
sheet.getCellRange(
"B5"
).setValue(
"January"
);
sheet.getCellRange(
"B6"
).setValue(
"February"
);
sheet.getCellRange(
"B7"
).setValue(
"March"
);
sheet.getCellRange(
"B8"
).setValue(
"January"
);
sheet.getCellRange(
"B9"
).setValue(
"February"
);
sheet.getCellRange(
"B10"
).setValue(
"March"
);
sheet.getCellRange(
"B11"
).setValue(
"January"
);
sheet.getCellRange(
"B12"
).setValue(
"February"
);
sheet.getCellRange(
"B13"
).setValue(
"March"
);
sheet.getCellRange(
"C2"
).setValue(
"25"
);
sheet.getCellRange(
"C3"
).setValue(
"22"
);
sheet.getCellRange(
"C4"
).setValue(
"24"
);
sheet.getCellRange(
"C5"
).setValue(
"24"
);
sheet.getCellRange(
"C6"
).setValue(
"23"
);
sheet.getCellRange(
"C7"
).setValue(
"24"
);
sheet.getCellRange(
"C8"
).setValue(
"22"
);
sheet.getCellRange(
"C9"
).setValue(
"15"
);
sheet.getCellRange(
"C10"
).setValue(
"23"
);
sheet.getCellRange(
"C11"
).setValue(
"25"
);
sheet.getCellRange(
"C12"
).setValue(
"20"
);
sheet.getCellRange(
"C13"
).setValue(
"18"
);
CellRange dataRange = sheet.getCellRange(
"A1:C13"
);
PivotCache cache
= workbook.getPivotCaches().add(dataRange);
PivotTable pivotTable = sheet.getPivotTables().add(
"Pivot Table"
, sheet.getCellRange(
"A16"
),
cache);
PivotField pivotField1 =
null
;
if
(pivotTable.getPivotFields().get(
"Student Name"
)
instanceof
PivotField) {
pivotField1
= (PivotField)pivotTable.getPivotFields()
.get(
"Student Name"
);
}
pivotField1.setAxis(AxisTypes.Row);
PivotField pivotField2 =
null
;
if
(pivotTable.getPivotFields().get(
"Month"
)
instanceof
PivotField) {
pivotField2
= (PivotField)pivotTable.getPivotFields()
.get(
"Month"
);
}
pivotField2.setAxis(AxisTypes.Row);
pivotTable.getDataFields().add(
pivotTable.getPivotFields().get(
"Attendance"
),
"SUM of Attendance"
, SubtotalTypes.Sum);
pivotTable.setBuiltInStyle(
PivotBuiltInStyles.PivotStyleMedium12);
pivotTable.calculateData();
sheet.setColumnWidth(
1
,
14
);
sheet.setColumnWidth(
2
,
14
);
String workbookName =
"Geeks_For_Geeks.xlsx"
;
workbook.saveToFile(workbookName,
ExcelVersion.Version2013);
System.out.println(workbookName
+
" is written successfully"
);
}
}