java 在具有表格的excel（xlsx）表中写入数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28208920/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Write data in excel(xlsx) sheet having a table
提问by NiteshGoel
I am trying to write dynamic data in an excel sheet (xlsx) having a table already created, based on which we plot a chart in excel itself, using a macro.
我正在尝试在已经创建表格的 excel 表格 (xlsx) 中写入动态数据，基于该表格，我们使用宏在 excel 本身中绘制图表。
I am using POI to write the data.
我正在使用 POI 来写入数据。
The table in the sheet has been set to 10 rows. When I write data for more than 10 rows, the table does not get expanded.So, the chart plotted contains data corresponding to 10 rows only.
工作表中的表格已设置为 10 行。当我写入超过 10 行的数据时，表格不会展开。因此，绘制的图表仅包含对应于 10 行的数据。
How can I write data, so that the data always expands the table to number of rows in the data?
如何写入数据，以便数据始终将表扩展到数据中的行数？
回答by Ofer Lando
You should create an XSSFTableobject from sheet.createTable();.
您应该XSSFTable从sheet.createTable();.
Here's an example I found at http://thinktibits.blogspot.co.il/2014/09/Excel-Insert-Format-Table-Apache-POI-Example.html- just make sure the table created covers all the rows/columns you dynamically place in the file.
这是我在http://thinktibits.blogspot.co.il/2014/09/Excel-Insert-Format-Table-Apache-POI-Example.html 上找到的一个例子- 只需确保创建的表格涵盖所有行/列你动态地放置在文件中。
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.*;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
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 insertTable
{
public static void main(String[] args)
throws FileNotFoundException, IOException
{
/* Read the input file that contains the data to be converted to table */
FileInputStream input_document = new FileInputStream(new File("FormatAsTable.xlsx"));
/* Create Workbook */
XSSFWorkbook my_xlsx_workbook = new XSSFWorkbook(input_document);
/* Read worksheet */
XSSFSheet sheet = my_xlsx_workbook.getSheetAt(0);
/* Create Table into Existing Worksheet */
XSSFTable my_table = sheet.createTable();
/* get CTTable object*/
CTTable cttable = my_table.getCTTable();
/* Define Styles */
CTTableStyleInfo table_style = cttable.addNewTableStyleInfo();
table_style.setName("TableStyleMedium9");
/* Define Style Options */
table_style.setShowColumnStripes(false); //showColumnStripes=0
table_style.setShowRowStripes(true); //showRowStripes=1
/* Define the data range including headers */
AreaReference my_data_range = new AreaReference(new CellReference(0, 0), new CellReference(5, 2));
/* Set Range to the Table */
cttable.setRef(my_data_range.formatAsString());
cttable.setDisplayName("MYTABLE"); /* this is the display name of the table */
cttable.setName("Test"); /* This maps to "displayName" attribute in <table>, OOXML */
cttable.setId(1L); //id attribute against table as long value
/* Add header columns */
CTTableColumns columns = cttable.addNewTableColumns();
columns.setCount(3L); //define number of columns
/* Define Header Information for the Table */
for (int i = 0; i < 3; i++)
{
CTTableColumn column = columns.addNewTableColumn();
column.setName("Column" + i);
column.setId(i+1);
}
/* Write output as File */
FileOutputStream fileOut = new FileOutputStream("Excel_Format_As_Table.xlsx");
my_xlsx_workbook.write(fileOut);
fileOut.close();
}
回答by G. Bach
While this question is a bit dated, it cost me more than 4 hours to figure this out, so I thought posting it might help. This alters an existing table instead of creating a new one.
虽然这个问题有点过时，但我花了 4 个多小时才弄明白，所以我认为发布它可能会有所帮助。这会更改现有表而不是创建新表。
// tell your xssfsheet where its content begins and where it ends
((XSSFWorksheet) sheet).getCTWorksheet().getDimension()
.setRef("A1:H" + (sheet.getLastRowNum() + 1));
CTTable ctTable = sheet.getTables().get(0).getCTTable();
ctTable.setRef("A1:H" + (sheet.getLastRowNum() + 1)); // adjust reference as needed
ctTable.unsetSortState(); // if you had sorted the data in Excel before reading the file,
// you may want an unsorted table in your output file
CTTableColumns ctColumns = ctTable.getTableColumns(); // setting new table columns will
// muck everything up,
// so adjust the existing ones
// remove the old columns first if you plan on expanding your table in the column direction
for (int i = 0; i < ctColumns.getCount(); i++) {
ctColumns.removeTableColumn(0);
}
// throw in your new columns
for (int i = 0; i < tableHeaders.size(); i++) {
CTTableColumn column = ctColumns.addNewTableColumn();
column.setName(tableHeaders.get(i));
column.setId(i + 1);
}
// for some reason this isn't being take care of when columns are modified,
// so fix the column count manually
ctColumns.setCount(tableHeaders.size());
A word of warning: all the unsetXmethods in CTTablewill throw IndexOutOfBoundsExceptions if the respective property doesn't exist in the table, so they need to be try-catch-ed. An alternative mentioned by @Gagravarr in the comments is to use isSetXfirst, then there's no need for trying.
警告：如果表中不存在相应的属性，则所有unsetX方法都CTTable将抛出IndexOutOfBoundsExceptions，因此需要尝试捕获它们。@Gagravarr 在评论中提到的另一种方法是先使用isSetX，然后就不需要trying。
If this doesn't work, then you probably have a bunch of other stuff set in your table. Unfortunately, the low-level XML stuff (encapsulated in all those CT objects) is very poorly documented since it's apparently auto-generated from the spec, so the API is pretty opaque. The teeth-grinding process I go through to figure out what to use is to unpack the .xlsx, look at the XML I need to adjust, figure out where it lives in my XSSFSheet and how I need to adjust it, then go hunting for the suitable CT-methods, since the XSSF-API does not really let you do all that much. This way, you can create pretty much anything you would be able to create in Excel, and even alter some stuff you can't there - if you have the time and nerves.
如果这不起作用，那么您的表中可能还有一堆其他东西。不幸的是，低级 XML 内容（封装在所有这些 CT 对象中）的文档非常少，因为它显然是根据规范自动生成的，因此 API 非常不透明。我为弄清楚要使用什么而经历的磨牙过程是解压 .xlsx，查看我需要调整的 XML，找出它在我的 XSSFSheet 中的位置以及我需要如何调整它，然后去寻找合适的 CT 方法，因为 XSSF-API 并没有真正让你做那么多。通过这种方式，您可以创建几乎可以在 Excel 中创建的任何内容，甚至可以更改一些您不能在那里创建的内容 - 如果您有时间和精力。
