Sreerag.E Sreerag.E - 24 days ago 10
Java Question

Read from pivot table and write in to a new page

I am trying to create a pivot table using Apache POI. My program reads from an Excel file and creates the pivot using the input data. But while writing the pivot to a new sheet the output obtained is along with input that was provided.
How can I separate the pivot and the data received?

FileInputStream fis = new FileInputStream(new File("D:\\aaa\\Dump.xlsx"));
XSSFWorkbook workbook = new XSSFWorkbook (fis);
XSSFSheet data = workbook.getSheetAt(0);
XSSFSheet sheet = workbook.createSheet("PIVOT SHEET");
System.out.println(data.getPhysicalNumberOfRows());
System.out.println(data.getRow(0).getLastCellNum());
CellReference cr = new CellReference("A1");
CellReference c1 = new CellReference(0, 0);
CellReference c2 = new CellReference(data.getPhysicalNumberOfRows() - 1, data.getRow(0).getLastCellNum() - 1);
//source data
AreaReference ar = new AreaReference(c1, c2);
XSSFPivotTable pivotTable = sheet.createPivotTable(ar, cr, data);

pivotTable.addRowLabel(7);
pivotTable.addColumnLabel(DataConsolidateFunction.COUNT, 6);


FileOutputStream output_file = new FileOutputStream(new File("D:\aaa\out.xlsx"));
workbook.write(output_file);
fis.close();

Answer
    //data sheet
    XSSFSheet data = wb.getSheet("DATA SHEET");
    // sheet you want pivot table in
    XSSFSheet sheet = wb.createSheet("PIVOT SHEET");

    CellReference cr = new CellReference("A1");
    CellReference c1 = new CellReference(0, 0);
    CellReference c2 = new CellReference(data.getPhysicalNumberOfRows() - 1, data.getRow(0).getLastCellNum() - 1);
    //source data
    AreaReference ar = new AreaReference(c1, c2);
    XSSFPivotTable pivotTable = sheet.createPivotTable(ar, cr, data);
    //add row labels and other stuff to pivot table
    pivotTable.addRowLabel(10);
    pivotTable.addRowLabel(11);
    pivotTable.addRowLabel(1);
    pivotTable.addColumnLabel(DataConsolidateFunction.COUNT, 0);

i hope this is what you are looking for. please tell me if i understood your question wrong