使用POI 3.11 beta1无法从数据透视表读取数据

Question

我目前正在开发一个Java应用程序，该应用程序使用excel文件（.xlsx）在其中创建一个数据透视表。

// 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;
    }       

控制台显示：“Not Expected：row is Null”。似乎是说“创建的数据透视表不存在”。我不明白为什么会这样。那么，是否还有其他方法可以使用POI API从创建的数据透视表中获取/读取/更改数据呢？

先进的谢谢！

Answer 1

到目前为止，Java POI还不支持从数据透视表中读取数据，也不支持打开数据透视表。您可以使用VB.NET创建.exe文件，它在单独的工作表中打开数据透视表。

代码：

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