使用Apache POI生成横向列数据透视表的结果不符问题咨询

Hey Alex, I see the issue with your current pivot table code—you're adding both fields as row labels, which creates a stacked vertical layout. To get that horizontal column structure you want, we need to move one of those fields into the column label area instead of the row area.

Let's Break Down Your Original Code #

First, let's recap what your current setup does:

• pivotTable.addRowLabel(1): Adds the 2nd column (B column, since POI uses 0-based indexing) as a row label
• pivotTable.addRowLabel(2): Adds the 3rd column (C column) as another row label, stacking it vertically under the first
• pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 3): Sums values from the 4th column (D column) as the pivot's value field

This layout keeps all categories aligned vertically. To make one category appear as horizontal columns across the top, we'll use the addColumnLabel(int columnIndex) overload (without the function parameter) to shift that field into the column area.

Modified Code for Horizontal Columns #

Here's the adjusted code—let's move the 3rd column (C column, index 2) to be the horizontal column labels:

XSSFSheet pivot = wb.createSheet("pivot");
AreaReference areaReference = new AreaReference("A2:D" + i, SpreadsheetVersion.EXCEL2007);
CellReference cellReference = new CellReference("A1");
XSSFPivotTable pivotTable = pivot.createPivotTable(areaReference, cellReference, sheet);

// Keep your first category as a vertical row label
pivotTable.addRowLabel(1); 
// Move the second category to the column area (this creates horizontal columns)
pivotTable.addColumnLabel(2); 
// Add the sum of your values column
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 3);

Key Changes Explained #

• pivotTable.addColumnLabel(2): This shifts the C column from the row section to the column section, making its values display as horizontal headers across the top of the pivot table.
• The row label (B column) stays vertical on the left, and the summed D-column values will populate the intersection of each row/column pair—exactly the horizontal layout you're looking for.

Optional Tweaks (If Needed) #

If you want to refine the pivot table further:

• Rename Column Headers: Use pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(2).setCaption("Your Custom Name") to give your horizontal column labels a more descriptive name.
• Format Values: Apply number formatting to the summed values with pivotTable.getCTPivotTableDefinition().getDataFields().getDataFieldArray(0).setNumFmtId((short) 4) (adjust the numFmtId to match your desired format—4 is standard currency, for example).

内容的提问来源于stack exchange，提问作者Alex