在具有表的excel（xlsx）表中写入数据 [英] Write data in excel(xlsx) sheet having a table

问题描述

我正在尝试在已创建表格的Excel工作表（xlsx）中编写动态数据，基于此我们使用宏在Excel中绘制图表。

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.

我正在使用POI来写数据。

I am using POI to write the data.

表格中的表格已设置为10行。当我写入超过10行的数据时，表格不会扩展。因此，绘制的图表包含仅对应10行的数据。

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.

如何写入数据，以便数据总是将表扩展为数据中的行数？

How can I write data, so that the data always expands the table to number of rows in the data?

推荐答案

你应该创建一个 XSSFTable 来自的对象sheet.createTable（）; 。

这是一个例子我在 http：//找到了thinktibits.blogspot.co.il/2014/09/Excel-Insert-Format-Table-Apache-POI-Example.html - 只需确保创建的表格涵盖了您动态放置在文件中的所有行/列。

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.

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();
  }

这篇关于在具有表的excel（xlsx）表中写入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！