如何使用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.

您的代码扩展到工作:

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屋！