如何使用Apache POI从Excel工作表中的数据透视表访问单元格值? [英] How to access cell value from pivot table from excel sheet using Apache POI?
问题描述
如何使用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工作表中的数据透视表访问单元格值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!