Apache POI:如何设置数据透视表显示布局? [英] Apache POI: How can I set the pivot table display layout?
本文介绍了Apache POI:如何设置数据透视表显示布局?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我尝试使用 poi 生成 excel 并在其中构建数据透视表.就像下面的代码.
这不是我想要的.你可以在结果中看到.只有月份有过滤器图标.
但是当您在数据透视表中选择一个单元格时，请选择设计选项卡.选择报告布局"，然后更改为以大纲形式显示过滤器将应用于月份、组 1 和代码.
有什么办法可以用poi来做我之前描述的步骤吗?
import org.apache.poi.ss.SpreadsheetVersion;导入 org.apache.poi.ss.usermodel.DataConsolidateFunction;导入 org.apache.poi.ss.usermodel.Workbook;导入 org.apache.poi.ss.util.AreaReference；导入 org.apache.poi.ss.util.CellRangeAddress；导入 org.apache.poi.ss.util.CellReference；导入 org.apache.poi.xssf.usermodel.*;导入 org.openxmlformats.schemas.spreadsheetml.x2006.main.*;导入 java.io.FileOutputStream;导入 java.io.IOException;导入 java.util.List;导入静态 org.openxmlformats.schemas.spreadsheetml.x2006.main.STShowDataAs.PERCENT_OF_ROW;公共类报告生成器 {public static void main(String[] args) 抛出 IOException {工作簿 wb = new XSSFWorkbook();String[][] data = new String[][] { { "group1", "type", "BOA", "Month", "code", "uuid" },{ "COS1", "type1", "Y", "2017-01", "AC2", "23-2214-232-1" }, { "COS1", "type2", "N", "2017-01", "A3C", "23-2214-232-2" },{ "COS2", "type1", "Y", "2018-01", "A3C", "23-2214-232-3" }, { "COS1", "type2", "Y", "2018-01", "A3C", "23-2214-232-4" },{ "COS1", "type1", "N/A", "2017-01", "A2C", "23-2214-232-5" }, { "COS1", "type2", "Y", "2017-01", "A2C", "23-2214-232-6" },{ "COS1", "type1", "N", "2018-01", "A2C", "23-2214-232-7" }, { "COS1", "type2", "Y", "2018-01", "A2C", "23-2214-232-8" }, };XSSFSheet sheet = (XSSFSheet) wb.createSheet("data");XSSFSheet pivot = (XSSFSheet) wb.createSheet("summary");for (String[] dataRow : 数据) {XSSFRow row = sheet.createRow(sheet.getPhysicalNumberOfRows());for (String dataCell: dataRow) {XSSFCell 单元格 = row.createCell(row.getPhysicalNumberOfCells());cell.setCellValue(dataCell);}}XSSFTable 表 = sheet.createTable();CTTable cttable = table.getCTTable();table.setDisplayName("table");cttable.setRef("A1:F9");cttable.setId(1);CTTableColumns 列 = cttable.addNewTableColumns();列.setCount(6);int i = 1;for (String colName : data[0]) {CTTableColumn column = columns.addNewTableColumn();column.setId(++i);column.setName(colName);}CellRangeAddress c = new CellRangeAddress(0, 0, 0, 5);sheet.setAutoFilter(c);XSSFPivotTable pivotTable = pivot.createPivotTable(new AreaReference("A1:F9", SpreadsheetVersion.EXCEL2007), new CellReference("A1"), sheet);pivotTable.addRowLabel(3);pivotTable.addRowLabel(0);pivotTable.addRowLabel(4);pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(0).setOutline(false);pivotTable.getCTPivotTableDefinition().setColGrandTotals(false);//pivotTable.getCTPivotTableDefinition().setOutline(false);pivotTable.getCTPivotTableDefinition().setColHeaderCaption("BOA");pivotTable.getCTPivotTableDefinition().setRowHeaderCaption("Month");pivotTable.addColumnLabel(DataConsolidateFunction.COUNT, 5, "SVVD 计数");pivotTable.addDataColumn(2, true);pivotTable.addColLabel(2);数据透视表.addReportFilter(1);列表ctDataFields = pivotTable.getCTPivotTableDefinition().getDataFields().getDataFieldList();for (CTDataField ctDataField : ctDataFields) {ctDataField.setShowDataAs(PERCENT_OF_ROW);}for (CTPivotField ctPivotField : pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldList()) {ctPivotField.setSubtotalTop(true);ctPivotField.setCompact(false);}pivotTable.getCTPivotTableDefinition().getPivotTableStyleInfo().setName("PivotStyleMedium7");FileOutputStream fileOut = new FileOutputStream("pivotsample.xlsx");wb.write(fileOut);wb.close();}} 解决方案
关于数据透视表的大纲形式的问题，您需要在代码中更改的是:
不要不设置:
pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(0).setOutline(false);因为这是设置轮廓的错误位置.
改为:
<预><代码>...pivotTable.getCTPivotTableDefinition().setCompact(false);pivotTable.getCTPivotTableDefinition().setCompactData(false);pivotTable.getCTPivotTableDefinition().setOutline(true);pivotTable.getCTPivotTableDefinition().setOutlineData(true);...这会从紧凑布局切换到轮廓布局.
但是您额外的 XSSFTable 代码会导致我的 Excel 文件损坏，因为您是在工作表中而不是在表格中设置自动过滤器.我正在使用 apache poi 4.1.0 并且在打开 Excel 文件时，用于创建您正在使用的表的代码会导致一条警告消息.然后 Excel 通过删除表格来修复文件.不知道为什么这个 XSSFTable 是必要的，但为了完整起见，这里的完整示例使用 apache poi 4.1.0 正确工作.
import org.apache.poi.ss.SpreadsheetVersion;导入 org.apache.poi.ss.usermodel.DataConsolidateFunction;导入 org.apache.poi.ss.usermodel.Workbook;导入 org.apache.poi.ss.util.AreaReference；导入 org.apache.poi.ss.util.CellRangeAddress；导入 org.apache.poi.ss.util.CellReference；导入 org.apache.poi.xssf.usermodel.*;导入 org.openxmlformats.schemas.spreadsheetml.x2006.main.*;导入 java.io.FileOutputStream;导入 java.io.IOException;导入 java.util.List;导入静态 org.openxmlformats.schemas.spreadsheetml.x2006.main.STShowDataAs.PERCENT_OF_ROW;公共类报告生成器 {public static void main(String[] args) 抛出 IOException {工作簿 wb = new XSSFWorkbook();字符串[][] 数据 = 新字符串[][] {{ "group1", "type", "BOA", "Month", "code", "uuid" },{ "COS1", "type1", "Y", "2017-01", "AC2", "23-2214-232-1" },{ "COS1", "type2", "N", "2017-01", "A3C", "23-2214-232-2" },{ "COS2", "type1", "Y", "2018-01", "A3C", "23-2214-232-3" },{ "COS1", "type2", "Y", "2018-01", "A3C", "23-2214-232-4" },{ "COS1", "type1", "N/A", "2017-01", "A2C", "23-2214-232-5" },{ "COS1", "type2", "Y", "2017-01", "A2C", "23-2214-232-6" },{ "COS1", "type1", "N", "2018-01", "A2C", "23-2214-232-7" },{ "COS1", "type2", "Y", "2018-01", "A2C", "23-2214-232-8" }};XSSFSheet sheet = (XSSFSheet) wb.createSheet("data");XSSFSheet pivot = (XSSFSheet) wb.createSheet("summary");XSSFRow 行；XSSFCell 单元格；for (int r = 0; r < data.length; r++) {row = sheet.createRow(r);字符串[] rowData = 数据[r];for (int c = 0; c ctDataFields = pivotTable.getCTPivotTableDefinition().getDataFields().getDataFieldList();for (CTDataField ctDataField : ctDataFields) {ctDataField.setShowDataAs(PERCENT_OF_ROW);}for (CTPivotField ctPivotField : pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldList()) {ctPivotField.setSubtotalTop(true);ctPivotField.setCompact(false);}pivotTable.getCTPivotTableDefinition().getPivotTableStyleInfo().setName("PivotStyleMedium7");sheet.setSelected(false);wb.setActiveSheet(1);FileOutputStream fileOut = new FileOutputStream("pivotsample.xlsx");wb.write(fileOut);wb.close();}} I have tried use the poi to generator excel and build a pivot table in. like the code following.
this is not i want to get. you can see in the result. only month have a filter icon.
but when you select a cell in the pivot table then select the design tab. choose the 'Report Layout' then change to show in outline form the filter will apply to month,group1 and code.
is there any way to use the poi to do the step I described before?
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.DataConsolidateFunction;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.*;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import static org.openxmlformats.schemas.spreadsheetml.x2006.main.STShowDataAs.PERCENT_OF_ROW;
public class ReportGenerator {
public static void main(String[] args) throws IOException {
Workbook wb = new XSSFWorkbook();
String[][] data = new String[][] { { "group1", "type", "BOA", "Month", "code", "uuid" },
{ "COS1", "type1", "Y", "2017-01", "AC2", "23-2214-232-1" }, { "COS1", "type2", "N", "2017-01", "A3C", "23-2214-232-2" },
{ "COS2", "type1", "Y", "2018-01", "A3C", "23-2214-232-3" }, { "COS1", "type2", "Y", "2018-01", "A3C", "23-2214-232-4" },
{ "COS1", "type1", "N/A", "2017-01", "A2C", "23-2214-232-5" }, { "COS1", "type2", "Y", "2017-01", "A2C", "23-2214-232-6" },
{ "COS1", "type1", "N", "2018-01", "A2C", "23-2214-232-7" }, { "COS1", "type2", "Y", "2018-01", "A2C", "23-2214-232-8" }, };
XSSFSheet sheet = (XSSFSheet) wb.createSheet("data");
XSSFSheet pivot = (XSSFSheet) wb.createSheet("summary");
for (String[] dataRow : data) {
XSSFRow row = sheet.createRow(sheet.getPhysicalNumberOfRows());
for (String dataCell : dataRow) {
XSSFCell cell = row.createCell(row.getPhysicalNumberOfCells());
cell.setCellValue(dataCell);
}
}
XSSFTable table = sheet.createTable();
CTTable cttable = table.getCTTable();
table.setDisplayName("table");
cttable.setRef("A1:F9");
cttable.setId(1);
CTTableColumns columns = cttable.addNewTableColumns();
columns.setCount(6);
int i = 1;
for (String colName : data[0]) {
CTTableColumn column = columns.addNewTableColumn();
column.setId(++i);
column.setName(colName);
}
CellRangeAddress c = new CellRangeAddress(0, 0, 0, 5);
sheet.setAutoFilter(c);
XSSFPivotTable pivotTable = pivot.createPivotTable(new AreaReference("A1:F9", SpreadsheetVersion.EXCEL2007), new CellReference("A1"), sheet);
pivotTable.addRowLabel(3);
pivotTable.addRowLabel(0);
pivotTable.addRowLabel(4);
pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(0).setOutline(false);
pivotTable.getCTPivotTableDefinition().setColGrandTotals(false);
// pivotTable.getCTPivotTableDefinition().setOutline(false);
pivotTable.getCTPivotTableDefinition().setColHeaderCaption("BOA");
pivotTable.getCTPivotTableDefinition().setRowHeaderCaption("Month");
pivotTable.addColumnLabel(DataConsolidateFunction.COUNT, 5, "Count of SVVD");
pivotTable.addDataColumn(2, true);
pivotTable.addColLabel(2);
pivotTable.addReportFilter(1);
List<CTDataField> ctDataFields = pivotTable.getCTPivotTableDefinition().getDataFields().getDataFieldList();
for (CTDataField ctDataField : ctDataFields) {
ctDataField.setShowDataAs(PERCENT_OF_ROW);
}
for (CTPivotField ctPivotField : pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldList()) {
ctPivotField.setSubtotalTop(true);
ctPivotField.setCompact(false);
}
pivotTable.getCTPivotTableDefinition().getPivotTableStyleInfo().setName("PivotStyleMedium7");
FileOutputStream fileOut = new FileOutputStream("pivotsample.xlsx");
wb.write(fileOut);
wb.close();
}
}
解决方案
As of your question about the pivot table's outline form, all you needs changing in your code is:
Do not set:
pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(0).setOutline(false);
as this is the wrong place to set outline.
Instead do:
...
pivotTable.getCTPivotTableDefinition().setCompact(false);
pivotTable.getCTPivotTableDefinition().setCompactData(false);
pivotTable.getCTPivotTableDefinition().setOutline(true);
pivotTable.getCTPivotTableDefinition().setOutlineData(true);
...
This switches from compact layout to outline layout.
But your additional XSSFTable code leads to a corrupt Excel file for me because you are setting the auto filter in the sheet instead of in the table. I am using apache poi 4.1.0 and the code for creating a table you are using leads to a warning message when opening the Excel file. Then Excel repairs the file by removing the table. Not sure why this XSSFTable is necessary at all but for completeness here the complete example which works correct using apache poi 4.1.0.
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.DataConsolidateFunction;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.*;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import static org.openxmlformats.schemas.spreadsheetml.x2006.main.STShowDataAs.PERCENT_OF_ROW;
public class ReportGenerator {
public static void main(String[] args) throws IOException {
Workbook wb = new XSSFWorkbook();
String[][] data = new String[][] {
{ "group1", "type", "BOA", "Month", "code", "uuid" },
{ "COS1", "type1", "Y", "2017-01", "AC2", "23-2214-232-1" },
{ "COS1", "type2", "N", "2017-01", "A3C", "23-2214-232-2" },
{ "COS2", "type1", "Y", "2018-01", "A3C", "23-2214-232-3" },
{ "COS1", "type2", "Y", "2018-01", "A3C", "23-2214-232-4" },
{ "COS1", "type1", "N/A", "2017-01", "A2C", "23-2214-232-5" },
{ "COS1", "type2", "Y", "2017-01", "A2C", "23-2214-232-6" },
{ "COS1", "type1", "N", "2018-01", "A2C", "23-2214-232-7" },
{ "COS1", "type2", "Y", "2018-01", "A2C", "23-2214-232-8" }
};
XSSFSheet sheet = (XSSFSheet) wb.createSheet("data");
XSSFSheet pivot = (XSSFSheet) wb.createSheet("summary");
XSSFRow row;
XSSFCell cell;
for (int r = 0; r < data.length; r++) {
row = sheet.createRow(r);
String[] rowData = data[r];
for (int c = 0; c < rowData.length; c++) {
cell = row.createCell(c);
cell.setCellValue(rowData[c]);
}
}
AreaReference areaReference = new AreaReference(
new CellReference(0,0),
new CellReference(data.length-1, data[0].length-1),
SpreadsheetVersion.EXCEL2007);
XSSFTable table = sheet.createTable(areaReference);
table.setName("Table1");
table.setDisplayName("Table1");
table.getCTTable().addNewTableStyleInfo();
XSSFTableStyleInfo style = (XSSFTableStyleInfo) table.getStyle();
style.setName("TableStyleMedium2");
style.setShowColumnStripes(false);
style.setShowRowStripes(true);
table.getCTTable().addNewAutoFilter().setRef(areaReference.formatAsString()); // set AutoFilter in table
//CellRangeAddress c = new CellRangeAddress(0, 0, 0, 5);
//sheet.setAutoFilter(c); // do **not** set AutoFilter in sheet since this range is in a table
XSSFPivotTable pivotTable = pivot.createPivotTable(areaReference , new CellReference("A1"), sheet);
pivotTable.addRowLabel(3);
pivotTable.addRowLabel(0);
pivotTable.addRowLabel(4);
//pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(0).setOutline(false); // don't do this
pivotTable.getCTPivotTableDefinition().setColGrandTotals(false);
// pivotTable.getCTPivotTableDefinition().setOutline(false);
pivotTable.getCTPivotTableDefinition().setColHeaderCaption("BOA");
pivotTable.getCTPivotTableDefinition().setRowHeaderCaption("Month");
// switch from compact layout to outline layout
pivotTable.getCTPivotTableDefinition().setCompact(false);
pivotTable.getCTPivotTableDefinition().setCompactData(false);
pivotTable.getCTPivotTableDefinition().setOutline(true);
pivotTable.getCTPivotTableDefinition().setOutlineData(true);
pivotTable.addColumnLabel(DataConsolidateFunction.COUNT, 5, "Count of SVVD");
pivotTable.addDataColumn(2, true);
pivotTable.addColLabel(2);
pivotTable.addReportFilter(1);
List<CTDataField> ctDataFields = pivotTable.getCTPivotTableDefinition().getDataFields().getDataFieldList();
for (CTDataField ctDataField : ctDataFields) {
ctDataField.setShowDataAs(PERCENT_OF_ROW);
}
for (CTPivotField ctPivotField : pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldList()) {
ctPivotField.setSubtotalTop(true);
ctPivotField.setCompact(false);
}
pivotTable.getCTPivotTableDefinition().getPivotTableStyleInfo().setName("PivotStyleMedium7");
sheet.setSelected(false);
wb.setActiveSheet(1);
FileOutputStream fileOut = new FileOutputStream("pivotsample.xlsx");
wb.write(fileOut);
wb.close();
}
}
这篇关于Apache POI:如何设置数据透视表显示布局?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
