Can't read data from pivot table by using POI 3.11 beta1

Question

I'm currently working on a Java application that uses the excel file(.xlsx) to create a pivot table in it.

From POI 3.11-beta1, I have found it support the creation of pivot tables. It makes me excited and I have tried the example for creating these tables which is available at https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/CreatePivotTable.java . It works! But when I try to read the data from the created pivot table using POI, it doesn't work.

In my testing code, I just add the following codes before the end '}' in the example:

// get the content of Cell "I7", should be "10"
    FileInputStream fileInputStream = new FileInputStream("ooxml-pivottable.xlsx");
    XSSFWorkbook xssfWorkbook = (XSSFWorkbook) WorkbookFactory.create(fileInputStream);
    xssfWorkbook.setForceFormulaRecalculation(true);
    Sheet sheet1 = xssfWorkbook.getSheetAt(0);
    // Cell I7
    Row row = sheet1.getRow(6);
    if (row == null) {
        System.out.println("Not expected: row is null");
        return;
    }
    Cell cellI7 = row.getCell(8);
    if (cellI7 != null && cellI7.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        System.out.println("The content of Cell I7 = "+cellI7.getNumericCellValue());
    } else {
        System.out.println("Not expected: cell is null");
        return;
    }

The console display: "Not expected: row is null". It seems to say "the created pivot table does not exist". I can't understand why it happens. So, is there another way to get/read/change the data from the created pivot tables with the POI api?

Thanks in Advanced!

Amit Gupta Amit Gupta · Accepted Answer · 2015-04-07T11:23:13

Till now the Java POI does not support reading data from pivot table or opening the pivot table. You can make the .exe file using VB.NET which opens the pivot table in a seperate sheet.

Code:

Module Module1

    Sub Main()
        Dim wb1 As Excel.Workbook
        Dim oExcelFile As Object
        Dim grandT As String
        Try
            oExcelFile = GetObject(, "Excel.Application")
        Catch
            oExcelFile = CreateObject("Excel.Application")
        End Try

        Dim mydir As DirectoryInfo = New DirectoryInfo("C:\ReconciliareFiles\ReconPlusFiles\PivotTable_temp")
        Dim f As FileInfo() = mydir.GetFiles()
        Dim file As FileInfo = Nothing
        For Each file In f
            Console.WriteLine("File Name: {0} Size: {1}  ", file.FullName, file.Length)
            Exit For
        Next file

        grandT = "Grand Total"
        Console.WriteLine("Grand Total is written as:{0}  ", grandT)
        wb1 = oExcelFile.Workbooks.Open(file.FullName)
        Dim sheet As Excel.Worksheet = wb1.Worksheets(1)
        For i As Integer = 1 To 100
            Console.WriteLine("cell are:{0}  ", sheet.Cells(i, 2).Value)
            If sheet.Cells(i, 2).Value = grandT Then
                Console.WriteLine("cell 13 2 is  :{0}", wb1.Worksheets(1).Cells(13, 2).Value)
                oExcelFile.Range(sheet.Cells(i, 3), sheet.Cells(i, 3)).ShowDetail = True
                oExcelFile.DisplayAlerts = False
                Exit For
            End If
        Next
        oExcelFile.ActiveWorkbook.Save()
        oExcelFile.ActiveWorkbook.close()
        oExcelFile.Workbooks.Close()
        ReleaseComObject(sheet)
        ReleaseComObject(wb1)


    End Sub

    Private Sub ReleaseComObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        End Try
    End Sub

End Module

Can't read data from pivot table by using POI 3.11 beta1

1 Answers