xml - Apache POI 格式表未显示总行

Question

我有一个使用以下代码创建格式化表的类:

public class formatAsTable {

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

        Workbook wb = new XSSFWorkbook();
        XSSFSheet sheet = (XSSFSheet) wb.createSheet();


        XSSFTable my_table = sheet.createTable();


        CTTable cttable = my_table.getCTTable();

        /* Let us define the required Style for the table */
        CTTableStyleInfo table_style = cttable.addNewTableStyleInfo();
        table_style.setName("TableStyleMedium9");


        table_style.setShowColumnStripes(false); //showColumnStripes=0
        table_style.setShowRowStripes(true); //showRowStripes=1


        AreaReference my_data_range = wb.getCreationHelper().createAreaReference(new CellReference(0, 0), new CellReference(4, 2));

        cttable.setRef(my_data_range.formatAsString());
        cttable.setDisplayName("MYTABLE");

        cttable.addNewAutoFilter();

        cttable.setName("Test");
        cttable.setId(1L);

        CTTableColumns columns = cttable.addNewTableColumns();
        columns.setCount(3L); //define number of columns

        for (int i = 0; i < 3; i++) {
            CTTableColumn column = columns.addNewTableColumn();
            column.setName("Column" + i);
            column.setId(i + 1);
        }

        /* Add And Show TotelRow */
        cttable.setTotalsRowShown(true);

        for (int x = 0; x < 3; x++) {
            cttable.getTableColumns().getTableColumnArray(x).setId(x + 1);

            switch (x) {
                case 0 ->
                    cttable.getTableColumns().getTableColumnArray(x).setTotalsRowLabel("Totales: ");
                default ->
                    cttable.getTableColumns().getTableColumnArray(x).setTotalsRowFunction(org.openxmlformats.schemas.spreadsheetml.x2006.main.STTotalsRowFunction.SUM);
            }
        }

        for (int i = 0; i <= 4; i++) //we have to populate 4 rows
        {
            XSSFRow row = sheet.createRow(i);
            for (int j = 0; j < 3; j++) //Three columns in each row
            {
                XSSFCell localXSSFCell = row.createCell(j);
                if (i == 0) {
                    localXSSFCell.setCellValue("Heading" + j);
                } else {
                    localXSSFCell.setCellValue(i + j);
                }
            }
        }

        FileOutputStream fileOut = new FileOutputStream("Excel_Format_As_Table.xlsx");
        wb.write(fileOut);
        fileOut.close();
    }
}

它工作正常，但即使我设置了 cttable.setTotalsRowShown(true);它没有显示总行!
并且我必须打开文件，并且表中的总行上的小鸡设计可见:

我更改了名称和内容并尝试添加不同的格式，但没有结果

Answer 1

主要问题是需要设置CTTable.setTotalsRowCount(1)显示一个总计行。您使用过的 CTTable.setTotalsRowShown(true)有别的意思。
根据 ECMA-376 Office Open XML 文件格式属性 totalsRowShown 方法:

A Boolean indicating whether the totals row has ever been shown in thepast for this table. True if the totals row has been shown, falseotherwise.

但是属性 totalsRowCount 方法:

An integer representing the number of totals rows that shall be shownat the bottom of the table.

0 means that the totals row is not shown. It is up to the spreadsheetapplication to determine if numbers greater than 1 are allowed. Unlessthe spreadsheet application has a feature where their might ever bemore than one totals row, this number should not be higher than 1.

但在 Excel表格的值和设置必须始终在表格设置和表格所在的工作表中同步。否则它将无法工作，甚至可能导致工作簿损坏。因此，除了标题行之外，工作表还需要有总计行。并且工作表中该总计行中的值和公式必须与表格设置相对应。
以下完整示例显示了这一切。它使用 apache poi 的高级类， 如果可能。它使用 apache poi 5.0.0 进行测试和工作以及 apache poi 4.1.2 .不支持较低版本，因为那些在表创建中存在错误。

import java.io.FileOutputStream;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;

class CreateExcelTable {

 public static void main(String[] args) throws Exception {

  try (XSSFWorkbook workbook = new XSSFWorkbook();
       FileOutputStream fileout = new FileOutputStream("Excel.xlsx") ) {

   //prepairing the sheet
   XSSFSheet sheet = workbook.createSheet();
   
   String[] tableHeadings = new String[]{"Heading1", "Heading2", "Heading3"};
   String tableName = "Table1";
   int firstRow = 0; //start table in row 1
   int firstCol = 0; //start table in column A
   int rows = 6; //we have to populate headings row, 4 data rows and 1 totals row
   int cols = 3; //three columns in each row
  
   for (int r = 0; r < rows; r++) { 
    XSSFRow row = sheet.createRow(firstRow+r);
    for (int c = 0; c < cols; c++) { 
     XSSFCell localXSSFCell = row.createCell(firstCol+c);
     if (r == 0) {
      localXSSFCell.setCellValue(tableHeadings[c]);
     } else if (r == 5) {
      //totals row content will be set later
     } else {
      localXSSFCell.setCellValue(r + c);
     }
    }
   }
   
   //create the table
   CellReference topLeft = new CellReference(sheet.getRow(firstRow).getCell(firstCol));
   CellReference bottomRight = new CellReference(sheet.getRow(firstRow+rows-1).getCell(firstCol+cols-1));
   AreaReference tableArea = workbook.getCreationHelper().createAreaReference(topLeft, bottomRight);
   XSSFTable dataTable = sheet.createTable(tableArea);
   dataTable.setName(tableName);
   dataTable.setDisplayName(tableName);

   //this styles the table as Excel would do per default
   dataTable.getCTTable().addNewTableStyleInfo();
   XSSFTableStyleInfo style = (XSSFTableStyleInfo)dataTable.getStyle();
   style.setName("TableStyleMedium9");
   style.setShowColumnStripes(false);
   style.setShowRowStripes(true);
   style.setFirstColumn(false);
   style.setLastColumn(false);

   //this sets auto filters
   dataTable.getCTTable().addNewAutoFilter().setRef(tableArea.formatAsString());
   
   //this sets totals properties to table and totals formulas to sheet
   XSSFRow totalsRow = dataTable.getXSSFSheet().getRow(tableArea.getLastCell().getRow());
   for (int c = 0; c < dataTable.getCTTable().getTableColumns().getTableColumnList().size(); c++) {
    if (c == 0) {
     dataTable.getCTTable().getTableColumns().getTableColumnList().get(c).setTotalsRowLabel("Totals: ");
     totalsRow.getCell(tableArea.getFirstCell().getCol()+c).setCellValue("Totals: ");
   } else {
     dataTable.getCTTable().getTableColumns().getTableColumnList().get(c).setTotalsRowFunction(org.openxmlformats.schemas.spreadsheetml.x2006.main.STTotalsRowFunction.SUM); 
     totalsRow.getCell(tableArea.getFirstCell().getCol()+c).setCellFormula("SUBTOTAL(109," + tableName + "[" + tableHeadings[c] + "])");
    }
   }
   //this shows the totals row
   dataTable.getCTTable().setTotalsRowCount(1);

   workbook.write(fileout);
  }

 }
}

所有 org.openxmlformats.schemas.spreadsheetml.x2006.main.*的用法类(class)需求 ooxml-schemas-1.4.jar为 apache poi 4.1.2或 poi-ooxml-full-5.0.0.jar为 apache poi 5.0.0 . ooxml 的轻型版本不包含所有类。见 https://poi.apache.org/help/faq.html#faq-N10025