POI将Excel文档中的区域转换为表格

POI将Excel文档中的区域转换为表格

本文介绍了如何使用Apache POI将Excel文档中的区域转换为表格?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我编写了一个应用程序,该应用程序使用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."

单击是"后,将显示不存在表的数据,并显示以下错误:已删除的零件:/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">

并且错误发生在

.因此,第一个标签似乎不完整.

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文档中的区域转换为表格?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-05 21:40