如何使用 Apache POI 将 Excel 文档中的区域转换为表格? [英] How do I convert an area in an Excel doc to a table using Apache POI?

问题描述

我编写了一个应用程序，它从数据库中获取数据并使用 Apache POI 库中的 XSSF 类从所述数据创建 Excel 文档.我已经导入了 poi、poi-ooxml 和 poi-ooxml-schemas，所有版本都是 4.1.0.

I've written an application that fetches data from a database and creates an Excel doc from said data using the XSSF classes in the Apache POI library. I've imported poi, poi-ooxml, and poi-ooxml-schemas, all version 4.1.0.

文件写得很好，打开文件时没有错误，直到我取消注释表创建代码，我将粘贴在下面:

The file gets written fine and there are no errors when opening the file until I uncomment the table creation code, which I'll paste below:

CellReference topLeft = new CellReference(sheet.getRow(3).getCell(0));
CellReference bottomRight = new CellReference(sheet.getRow(nextRow-1).getCell(3));
AreaReference tableArea = workbook.getCreationHelper().createAreaReference(topLeft, bottomRight);
XSSFTable dataTable = sheet.createTable(tableArea);

为了排除一些潜在的问题，我添加了一些额外的字段:

I added a few additional fields just to rule out some potential issues:

int test = dataTable.getEndRowIndex(); //Returns 968, as expected
tableArea = dataTable.getArea(); //The area remains A4 to D968, as expected
int testColumns = dataTable.getColumnCount(); //Returns 4, as expected
int testRows = dataTable.getRowCount(); //Returns 968, as expected

当我在取消注释上述代码后尝试打开工作簿时，出现以下错误:

When I attempt to open the workbook after uncommenting the above code, I get the following error:

我们发现 'filename.xlsx' 中的某些内容存在问题.您希望我们尽可能多地尝试恢复吗?如果您信任此工作簿的来源，请单击是."

"We found a problem with some content in 'filename.xlsx'. Do you want us to try and recover as much as we can? If you trust the source of this workbook, click yes."

点击yes后出现数据不存在表格，显示如下错误:删除部分:/xl/tables/table1.xml 部分，带有 XML 错误.(表)加载错误.第 2 行，第 94 列."

After clicking yes, the data appears without the table present and the following error is displayed: "Removed Part: /xl/tables/table1.xml part with XML error. (Table) Load error. Line 2, column 94."

这令人困惑，因为所有迹象都表明表中应该只有 4 列......有人知道可能发生了什么以及如何解决吗?

This is confusing, as all indications show that there should only be 4 columns in the table... Anyone have a clue what might be going on and how to fix it?

推荐答案

您的 XSSFTable 缺少名称.至少必须使用 XSSFTable.setDisplayName.

Your XSSFTable lacks names. At least the display name must be set using XSSFTable.setDisplayName.

所以 dataTable.setDisplayName("Table1"); 应该可以解决您的问题.

So dataTable.setDisplayName("Table1"); should solve your issue.

如何检测?

首先创建一个简单的完整示例.然后，使用Excel打开结果后，注意/xl/tables/table1.xml的哪一行/哪一列出错.然后在解压*.xlsx后打开/xl/tables/table1.xml.现在你应该发现，错误的 XML 是:

First create a simple complete example. Then, after opening the result using Excel, note what row/column of the /xl/tables/table1.xml the error is about. Then do opening the /xl/tables/table1.xml after unzipping the *.xlsx. Now you should find, the erroneous XML is:

<table id="1" ref="A4:D..." xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><tableColumns count="4">

并且错误紧跟在 <table id="1" ref="A4:D..." xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">.所以第一个标签似乎不完整.

And the error is immediately after <table id="1" ref="A4:D..." xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">. So the first tag seems to be incomplete.

现在将其与 XML Excel 本身创建的进行比较.你会发现 Excel table 元素总是有 name="TableN" displayName="TableN" 设置.

Now compare that with XML Excel itself creates. You will find that Excel table elements always will have name="TableN" displayName="TableN" set.

完整示例:

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

import java.util.GregorianCalendar;

class CreateExcelTable {

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

  Object[][] data = new Object[][] {
   new Object[] {"Text", "Date", "Number", "Boolean"},
   new Object[] {"Text 1", new GregorianCalendar(2020, 0, 1), 1234d, true},
   new Object[] {"Text 2", new GregorianCalendar(2020, 1, 15), 5678d, true},
   new Object[] {"Text 3", new GregorianCalendar(2020, 2, 1), 90.1234, false},
   new Object[] {"Text 4", new GregorianCalendar(2020, 3, 15), 567.89, false}
  };

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

   XSSFCellStyle dateCellStyle = workbook.createCellStyle();
   dateCellStyle.setDataFormat(14);

   XSSFSheet sheet = workbook.createSheet();
   XSSFRow row = sheet.createRow(0);
   XSSFCell cell = row.createCell(0);
   cell.setCellValue("Lorem ipsum");
   row = sheet.createRow(1);
   cell = row.createCell(0);
   cell.setCellValue("semit dolor");

   int nextRow = 3;
   int nextCol = 0;
   for (Object[] dataRow : data) {
    row = sheet.createRow(nextRow++);
    nextCol = 0;
    for (Object value : dataRow) {
     cell = row.createCell(nextCol++);
     if (value instanceof String) cell.setCellValue((String)value);
     else if (value instanceof GregorianCalendar) {
      cell.setCellValue((GregorianCalendar)value);
      cell.setCellStyle(dateCellStyle);
     }
     else if (value instanceof Double) cell.setCellValue((Double)value);
     else if (value instanceof Boolean) cell.setCellValue((Boolean)value);
    }
   }

   CellReference topLeft = new CellReference(sheet.getRow(3).getCell(0));
   CellReference bottomRight = new CellReference(sheet.getRow(nextRow-1).getCell(3));
   AreaReference tableArea = workbook.getCreationHelper().createAreaReference(topLeft, bottomRight);
   XSSFTable dataTable = sheet.createTable(tableArea);
   //dataTable.setName("Table1");
   dataTable.setDisplayName("Table1");

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

   dataTable.getCTTable().addNewAutoFilter().setRef(tableArea.formatAsString());
*/

   workbook.write(fileout);
  }

 }
}

这篇关于如何使用 Apache POI 将 Excel 文档中的区域转换为表格?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！