问题描述
如何使用Apache POI从Excel工作表中的数据透视表访问单元格值?我尝试使用XSSFPivotTable.getPivotTables，但无法访问单元格值.Book1.xlsx是excel工作簿，在工作表Sheet1上有一个数据透视表.
How to access cell value from pivot table from excel sheet using Apache POI?I tried using XSSFPivotTable.getPivotTables, but not able to access cell value.Book1.xlsx is the excel workbook which has a pivot table on sheet Sheet1.
FileInoutStream input = new FileInputStream(new File(Book1.xlsx));XSSFWorkbook wb = new XSSFWorkbook(input);XSSFSheet sh = wb.getSheet("Sheet1");XSSFPivotTable pt = sh.getPivotTables().get(0);
FileInoutStream input = new FileInputStream(new File(Book1.xlsx));XSSFWorkbook wb = new XSSFWorkbook(input);XSSFSheet sh = wb.getSheet("Sheet1");XSSFPivotTable pt = sh.getPivotTables().get(0);
在此之前无法访问pt数据透视表中的单元格.
Not able to access the cells in pt pivot table ahead of this.
推荐答案
下面是一种自定义方式来访问数据透视表头和数据，我不确定但我找不到通过库访问数据的方式，在线搜索我读到一些有关的内容，说尚不支持.
Below a custom way to access to pivot table header and data , im not sure but i couldnt find a way to access to the data via library , searching on line i read something about, which says it is not supported yet .
让我们看看我做了什么:
Lets see what i did :
输入文件:
将数字转换为列中的字母:
Tranform letters in numbers for columns:
Map<String, Integer> map = new HashMap<String, Integer>() {
{
int index =1;
for (char ch = 'A'; ch <= 'Z'; ++ch) {
put(String.valueOf(ch), index);
index++;
}
}
};
获取表对象
java.util.List<XSSFPivotTable> l = sh.getPivotTables();
下面的代码将对结果起到欺骗作用
XML调试视图:
<xml-fragment name="PivotTable7" cacheId="24" applyNumberFormats="0" applyBorderFormats="0" applyFontFormats="0" applyPatternFormats="0" applyAlignmentFormats="0" applyWidthHeightFormats="1" dataCaption="Values" updatedVersion="6" minRefreshableVersion="3" useAutoFormatting="1" itemPrintTitles="1" createdVersion="6" indent="0" outline="1" outlineData="1" multipleFieldFilters="0" xmlns:main="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<main:location ref="F6:G7" firstHeaderRow="0" firstDataRow="1" firstDataCol="0"/>
<main:pivotFields count="2">
<main:pivotField dataField="1" subtotalTop="0" showAll="0">
<main:items count="2">
<main:item x="0"/>
<main:item t="default"/>
</main:items>
</main:pivotField>
<main:pivotField dataField="1" subtotalTop="0" showAll="0"/>
</main:pivotFields>
<main:rowItems count="1">
<main:i/>
</main:rowItems>
<main:colFields count="1">
<main:field x="-2"/>
</main:colFields>
<main:colItems count="2">
<main:i>
<main:x/>
</main:i>
<main:i i="1">
<main:x v="1"/>
</main:i>
</main:colItems>
<main:dataFields count="2">
<main:dataField name="A" fld="0" baseField="0" baseItem="1"/>
<main:dataField name="B" fld="1" baseField="0" baseItem="1"/>
</main:dataFields>
<main:pivotTableStyleInfo name="PivotStyleLight16" showRowHeaders="1" showColHeaders="1" showRowStripes="0" showColStripes="0" showLastColumn="1"/>
<main:extLst>
<main:ext uri="{962EF5D1-5CA2-4c93-8EF4-DBF5C05439D2}" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">
<x14:pivotTableDefinition hideValuesRow="1" xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main"/>
</main:ext>
<main:ext uri="{747A6164-185A-40DC-8AA5-F01512510D54}" xmlns:xpdl="http://schemas.microsoft.com/office/spreadsheetml/2016/pivotdefaultlayout">
<xpdl:pivotTableDefinition16 SubtotalsOnTopDefault="0"/>
</main:ext>
</main:extLst>
</xml-fragment>
看着 ref ="F6:G7" ，我知道可以达到目的的范围
Looking at ref="F6:G7" i know the range which will make the trick
String range = l.get(0).getCTPivotTableDefinition().getLocation().getRef(); //F6:G7 rows/cols reference - case of our sheet
//determinate range of table
int firstcol = map.get(range.substring(0, 1));
int firstrow = Integer.parseInt(range.substring(1, 2));
int lastcol = map.get(range.substring(3, 4));
int lastrow = Integer.parseInt(range.substring(4, 5));
完整代码:
public static void main(String[] args) {
//tranform letters in numbers for columns
Map<String, Integer> map = new HashMap<String, Integer>() {
{
int index =1;
for (char ch = 'A'; ch <= 'Z'; ++ch) {
put(String.valueOf(ch), index);
index++;
}
}
};
try {
FileInputStream input = new FileInputStream(new File("C:\\Desktop\\ook.xlsx"));
XSSFWorkbook wb = new XSSFWorkbook(input);
XSSFSheet sh = wb.getSheet("Sheet1");
Iterator<Row> rowIterator = sh.iterator();
ArrayList columndata = new ArrayList<>();
java.util.List<XSSFPivotTable> l = sh.getPivotTables();
String range = l.get(0).getCTPivotTableDefinition().getLocation().getRef(); //F6:G7 rows/cols reference - case of our sheet
//determinate range of table
int firstcol = map.get(range.substring(0, 1));
int firstrow = Integer.parseInt(range.substring(1, 2));
int lastcol = map.get(range.substring(3, 4));
int lastrow = Integer.parseInt(range.substring(4, 5));
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
if(checkrightrowcol(row.getRowNum()+1, firstrow, lastrow)){
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
if(checkrightrowcol(cell.getColumnIndex()+1,firstcol,lastcol)){
switch(cell.getCellType()){
case Cell.CELL_TYPE_NUMERIC: // numeric value
System.out.println(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING: // String Value
System.out.println(cell.getStringCellValue());
break;
//..add more
}
}
}
}
}
} catch (IOException e) {
e.printStackTrace();
}
}
checkrightrowcol
public static boolean checkrightrowcol(int n , int start, int end){
while (start!=end){
if(n == start || n == end)
return true;
start++;
}
return false;
}
如果它在表数据中返回true im，那么我主要使用"+1"，因为行和列的索引都从"0"开始
If it returns true im in the table data , in the main i use "+1" because rows and columns indexes start from "0"
结果:
这篇关于如何使用Apache POI从Excel工作表中的数据透视表访问单元格值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！