java - 如何隐藏数据透视表中而不是工作表中的特定列？

Question

Text above pivot 我想隐藏数据透视表中的一列。如果我删除该列，那么该功能就会生效。如何通过删除或隐藏数据透视表中的该列来实现该功能？

`  public class Createxlsx {

   public static void main(String[] args) throws               FileNotFoundException, IOException,    InvalidFormatException {
   createPivotTable();

}


  private static void createPivotTable() throws       IOException, FileNotFoundException {
  try (XSSFWorkbook wb = new XSSFWorkbook()) {
    XSSFSheet sheet1 = wb.createSheet("1e");
    XSSFSheet sheet = wb.createSheet("1econtent");
    XSSFFont font = wb.createFont();
    font.setFontHeightInPoints((short) 15);
    font.setColor(IndexedColors.WHITE.getIndex());

     //  sheet.setTabColor(10);


   /* CTColor color = CTColor.Factory.newInstance(); 
    color.setIndexed(IndexedColors.RED.getIndex()); 
    sheet.getCTWorksheet().getSheetPr().setTabColor(color);*/
   // sheet1.setTabColor( new XSSFColor( Color.RED ) );
  //  sheet.setTabColor(1) 


    /*   CTColor color = CTColor.Factory.newInstance(); 
    color.setIndexed(IndexedColors.GREEN.getIndex()); 
    sheet1.getCTWorksheet().getSheetPr().setTabColor(color); */


 //   sheet1.setTabColor(0);



    //Set the tab color
//  sheet.setTabColor(Color.getRed());

    //Save the Excel file
    //workbook.save(dataDir + "AsposeColoredTab_Out.xls");

    CellStyle fontStyle = wb.createCellStyle();
    fontStyle.setFont(font);
    fontStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    fontStyle.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
    fontStyle.setWrapText(true);



    CellStyle fontStyle1 = wb.createCellStyle();
    fontStyle1.setFont(font);
    fontStyle1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    fontStyle1.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    fontStyle1.setWrapText(true);


    CellStyle fontStyle2 = wb.createCellStyle();
    fontStyle2.setFont(font);
    fontStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    XSSFColor color = new XSSFColor();
    color.setARGBHex("fcd5b4");
    fontStyle2.setFillForegroundColor(color.getIndexed());
    fontStyle2.setWrapText(true);

    CellStyle fontStyle3 = wb.createCellStyle();
    fontStyle3.setFont(font);
    fontStyle3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    XSSFColor color1 = new XSSFColor();
    color1.setARGBHex("fcd5b4");
    fontStyle3.setFillForegroundColor(color1.getIndexed());
    fontStyle3.setWrapText(true);


    sheet1.setDisplayGridlines(false);
    sheet1.addMergedRegion(new CellRangeAddress(4,7,0,5));
    sheet1.addMergedRegion(new CellRangeAddress(0,0,0,5));
    sheet1.addMergedRegion(new CellRangeAddress(1,1,1,5));
    sheet1.addMergedRegion(new CellRangeAddress(2,2,1,5));
    sheet1.addMergedRegion(new CellRangeAddress(3,3,0,5));
    sheet1.setColumnWidth(1, 25*256);
    sheet1.setColumnWidth(2, 45*256);

    Row row1 = sheet1.createRow(0);
    Cell cell11 = row1.createCell(0);
    cell11.setCellStyle(fontStyle);
    cell11.setCellValue("XXX");



    Row row2 = sheet1.createRow(1);
   // row2.setRowStyle(fontStyle);
    //row2.setRowStyle(fontStyle2);
    Cell cell21 = row2.createCell(0);
    //CellStyle alignCellStyle = cell21.getCellStyle();
    CellStyle headerStyle = wb.createCellStyle();
    headerStyle.setAlignment(HorizontalAlignment.RIGHT);
    //alignCellStyle.setAlignment(HorizontalAlignment.RIGHT);
    cell21.setCellValue("Preparued for:");
    cell21.setCellStyle(fontStyle2);
    Cell cell22 = row2.createCell(1);
    cell22.setCellValue("Rerrrrts Tests");
    cell22.setCellStyle(fontStyle2);


    Row row3 = sheet1.createRow(2);
   // row3.setRowStyle(fontStyle2);
    Cell cell31 = row3.createCell(0);
    CellStyle alignCellStyle1 = cell31.getCellStyle();
    alignCellStyle1.setAlignment(HorizontalAlignment.RIGHT);
    cell31.setCellValue("Time Period:");
    cell31.setCellStyle(fontStyle2);
    Cell cell32 = row3.createCell(1);
    DataFormat poiFormat = wb.createDataFormat();
    CellStyle cellStyle = wb.createCellStyle();
    String excelFormatPattern = DateFormatConverter.convert(Locale.US, "mm/dd/yyyy");
    cellStyle.setDataFormat(poiFormat.getFormat(excelFormatPattern));
    cell32.setCellValue(new Date());
    cell32.setCellStyle(fontStyle2);


    Row row4 = sheet1.createRow(3);
    Cell cell41 = row4.createCell(0);
    cell41.setCellValue("Table 1e: Confs Created ");
    //row4.setRowStyle(fontStyle1);
    cell41.setCellStyle(fontStyle1);

    Row row5 = sheet1.createRow(4);
    //row5.setRowStyle(fontStyle2);
    Cell cell51 = row5.createCell(0);
    cell51.setCellValue("xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx");
    cell51.setCellStyle(fontStyle3);


    setCellData(sheet,wb);
    wb.getSheet("1econtent").setSelected(false);
    wb.setSheetVisibility(wb.getSheetIndex("1econtent"),SheetVisibility.VISIBLE);

    AreaReference source = new AreaReference("A1:D5", SpreadsheetVersion.EXCEL2007);
    CellReference position = new CellReference(10,0);

     XSSFPivotTable pivotTable = sheet1.createPivotTable(source, position,wb.getSheet("1econtent"));

     pivotTable.addReportFilter(2);
     pivotTable.addRowLabel(0);

     pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 1);
     pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 1,"% of value");

     pivotTable.getCTPivotTableDefinition().getDataFields().getDataFieldArray(1).setShowDataAs(org.openxmlformats.schemas.spreadsheetml.x2006.main.STShowDataAs.PERCENT_OF_COL);
     DataFormat dataformat = wb.createDataFormat();
     short numFmtId = dataformat.getFormat("0.00%");
     pivotTable.getCTPivotTableDefinition().getDataFields().getDataFieldArray(1).setNumFmtId(numFmtId); 

     pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(0).setAutoShow(false);
     pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(0).getItems().getItemArray(0).unsetT();
     pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(0).getItems().getItemArray(0).setX((long)0);
     pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(0).getSharedItems().addNewS().setV("Jane");
     pivotTable.getCTPivotTableDefinition().getPageFields().getPageFieldArray(0).setItem(0);


     sheet1.getRow(10);
     pivotTable.getCTPivotTableDefinition().getPivotTableStyleInfo().setName("PivotStyleMedium10");




    try (FileOutputStream fileOut = new FileOutputStream("ooxml-pivottablesa.xlsx")) {
        wb.write(fileOut);
    }
}

}

public static void setCellData(XSSFSheet 表，XSSFWorkbook wb){

    Row row1 = sheet.createRow(0);
    // Create a cell and put a value in it.
    Cell cell11 = row1.createCell(0);
    cell11.setCellValue("Names");
    Cell cell12 = row1.createCell(1);
    cell12.setCellValue("Confts");
    Cell cell13 = row1.createCell(2);
    cell13.setCellValue("ConftsAS");
    Cell cell14 = row1.createCell(3);
    cell14.setCellValue("Human");


    Row row2 = sheet.createRow(1);
    Cell cell21 = row2.createCell(0);
    cell21.setCellValue("Jane");
    Cell cell22 = row2.createCell(1);
    cell22.setCellValue(10);
    Cell cell23 = row2.createCell(2);
    cell23.setCellValue(100);
    Cell cell24 = row2.createCell(3);
    cell24.setCellValue("Yes");

    Row row3 = sheet.createRow(2);
    Cell cell31 = row3.createCell(0);
    cell31.setCellValue("Tarzan");
    Cell cell32 = row3.createCell(1);
    cell32.setCellValue(5);
    Cell cell33 = row3.createCell(2);
    cell33.setCellValue(100);
    Cell cell34 = row3.createCell(3);
    cell34.setCellValue("Yes");

    Row row4 = sheet.createRow(3);
    Cell cell41 = row4.createCell(0);
    cell41.setCellValue("Terk");
    Cell cell42 = row4.createCell(1);
    cell42.setCellValue(10);
    Cell cell43 = row4.createCell(2);
    cell43.setCellValue(90);
    Cell cell44 = row4.createCell(3);
    cell44.setCellValue("No");
    cell44.getCellStyle().setHidden(false);

    Row row5 = sheet.createRow(4);
    Cell cell211 = row5.createCell(0);
    cell211.setCellValue("Jane");
    Cell cell221 = row5.createCell(1);
    cell221.setCellValue(10);
    Cell cell231 = row5.createCell(2);
    cell231.setCellValue(60);
    Cell cell241 = row5.createCell(3);
    cell241.setCellValue("No");
    cell241.getCellStyle().setHidden(false);

}

}`

我想隐藏数据透视表中的一列而不影响功能

Answer 1

您可以使用 setColumnHidden(int columnIndex, boolean hide) 隐藏列，其中 A 列的 columnIndex 值为 0。 sheet.setColumnHidden(1, true) 将隐藏 B 列。

隐藏列的另一种方法是使用 setColumnWidth(int columnIndex, int width) 例如sheet.setColumnWidth(1, 0) 也会执行与上面相同的操作。

我仍然会使用第一种特定于此目的的方法。

编辑 - 添加了以下说明以说明它对数据透视表的有效性:

请注意，无论您是否有正常单元格或数据透视表，这都有效。在以下程序中，sheet.setColumnHidden(9, true) 将隐藏列 J(其列标签为 Average)。

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataConsolidateFunction;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFPivotTable;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CreatePivotTable {

    public static void main(String[] args) throws FileNotFoundException, IOException, InvalidFormatException {
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet();

        // Create some data to build the pivot table on
        setCellData(sheet);

        AreaReference source = new AreaReference("A1:D4", SpreadsheetVersion.EXCEL2007);
        CellReference position = new CellReference("H5");
        // Create a pivot table on this sheet, with H5 as the top-left cell..
        // The pivot table's data source is on the same sheet in A1:D4
        XSSFPivotTable pivotTable = sheet.createPivotTable(source, position);
        // Configure the pivot table
        // Use first column as row label
        pivotTable.addRowLabel(0);
        // Sum up the second column
        pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 1);
        // Set the third column as filter
        pivotTable.addColumnLabel(DataConsolidateFunction.AVERAGE, 2);
        // Add filter on forth column
        pivotTable.addReportFilter(3);

        sheet.setColumnHidden(9, true);

        FileOutputStream fileOut = new FileOutputStream("ooxml-pivottable.xlsx");
        wb.write(fileOut);

    }

    public static void setCellData(XSSFSheet sheet) {
        Row row1 = sheet.createRow(0);
        // Create a cell and put a value in it.
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("Names");
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("#");
        Cell cell13 = row1.createCell(2);
        cell13.setCellValue("%");
        Cell cell14 = row1.createCell(3);
        cell14.setCellValue("Human");

        Row row2 = sheet.createRow(1);
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("Jane");
        Cell cell22 = row2.createCell(1);
        cell22.setCellValue(10);
        Cell cell23 = row2.createCell(2);
        cell23.setCellValue(100);
        Cell cell24 = row2.createCell(3);
        cell24.setCellValue("Yes");

        Row row3 = sheet.createRow(2);
        Cell cell31 = row3.createCell(0);
        cell31.setCellValue("Tarzan");
        Cell cell32 = row3.createCell(1);
        cell32.setCellValue(5);
        Cell cell33 = row3.createCell(2);
        cell33.setCellValue(90);
        Cell cell34 = row3.createCell(3);
        cell34.setCellValue("Yes");

        Row row4 = sheet.createRow(3);
        Cell cell41 = row4.createCell(0);
        cell41.setCellValue("Terk");
        Cell cell42 = row4.createCell(1);
        cell42.setCellValue(10);
        Cell cell43 = row4.createCell(2);
        cell43.setCellValue(90);
        Cell cell44 = row4.createCell(3);
        cell44.setCellValue("No");
    }
}

注意:我已经修改了 http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/CreatePivotTable.java 中给出的程序来说明解决方案。