如何使用Apache Java POI在MS Excel中插入表格 [英] How to insert a table in ms excel using apache java poi

问题描述

我正在尝试使用Java Apache Poi在Excel中插入表格.但是，当我打开xlsx文件时，它抛出以下错误，而我无法解决它:

I am trying to insert a table in Excel using Java Apache Poi. But when I am opening the xlsx file it is throwing the following error and I could not solve it:

Removed Part: /xl/tables/table1.xml part with XML error. (Table) Load error. Line 2
repaired records: table from /xl/tables/table1.xml part (table)

我的代码如下:

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

import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFTable;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTable;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumn;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumns;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableStyleInfo;

public class Test {

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

    XSSFTable table = sheet.createTable();
    CTTable cttable = table.getCTTable();


    cttable.setDisplayName("Table1");
    cttable.setId(1);
    cttable.setName("Test");
    cttable.setRef("A1:C11");
    cttable.setTotalsRowShown(false);

    CTTableStyleInfo styleInfo = cttable.addNewTableStyleInfo();
    styleInfo.setShowColumnStripes(false);
    styleInfo.setShowRowStripes(true);


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

    try (FileOutputStream outputStream = new FileOutputStream("C:\\Office\\TimeSheet\\JavaBooks.xlsx")) {
      workbook.write(outputStream);
    }
  }
}

如何使用Apache Java Poi在Microsft Excel中插入表格?

How can I insert a table in Microsft Excel using Apache Java Poi?

推荐答案

工作表单元格中至少必须有用于表列名称的内容.在您的情况下，工作表Architecture中的单元格A1:C1必须具有内容.在apache poi的早期版本中，此内容必须与表列名匹配.现在，在当前版本中，单元格内容的设置将更新表的列名.

There must be at least content in sheet cells for table column names. In your case cells A1:C1 in sheet Architecture must have content. In former versions of apache poi this content had must match the table column names. In current version now the setting the cell content updates the table column names.

您的代码可以正常工作:

Your code extended to work:

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

import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFTable;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTable;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumn;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumns;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableStyleInfo;

public class ExcelTableTest {

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

    XSSFTable table = sheet.createTable();
    //XSSFTable table = sheet.createTable(null); //since apache poi 4.0.0
    CTTable cttable = table.getCTTable();

    cttable.setDisplayName("Table1");
    cttable.setId(1);
    cttable.setName("Test");
    cttable.setRef("A1:C11");
    cttable.setTotalsRowShown(false);

    CTTableStyleInfo styleInfo = cttable.addNewTableStyleInfo();
    styleInfo.setName("TableStyleMedium2");
    styleInfo.setShowColumnStripes(false);
    styleInfo.setShowRowStripes(true);

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

    for (int r = 0; r < 2; r++) {
      XSSFRow row = sheet.createRow(r);
      for(int c = 0; c < 3; c++) {
        XSSFCell cell = row.createCell(c);
        if(r == 0) { //first row is for column headers
          cell.setCellValue("Column"+ (c+1)); //content **must** be here for table column names
        } else {
          //cell.setCellValue("Data R"+ (r+1) + "C" + (c+1));
        }
      }
    }    

    try (FileOutputStream outputStream = new FileOutputStream("ExcelTableTest.xlsx")) {
      workbook.write(outputStream);
    }
  }
}

这篇关于如何使用Apache Java POI在MS Excel中插入表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！