JAVA Excel文件损坏

Question

我尝试在包含我添加的第一个表的文件中写入第二个表。没有重叠的行，文件被损坏，但它插入表格，格式为excel formatAsTable样式都很好。我怎样才能消除这个问题？我不能有一个损坏的文件，即使它的数据都是好的。

public class formatAsTable2
{
  public static void main(String[] args)
    throws FileNotFoundException, IOException, InvalidFormatException
  {
      String[][] multi = new String[][]{
  { "Advisor", "notContacted", "appointmentsUTD", "totalWorkLoad", "notResponsiveCalls", "successfulCalls", "totalCalls", "totalIncomingEmails", "totalCommunication", "emailsSent","offerLetters","appFees","deposits" },
  { "Sharon Brown", "42", "44", "86", "62", "27", "89", "21", "220", "131" , "0", "6", "2", "0"},
  { "Elena Soteriou", "40", "44", "86", "62", "27", "89", "21", "230", "131" , "0", "7", "2", "0"},
  { "Helen Christou","45", "44", "86", "62", "27", "89", "21", "210", "131" , "0", "8", "2", "0"},
  { "Maria Georgiou", "48", "44", "86", "62", "27", "89", "21", "240", "131" , "0", "45", "2", "0"}
};
      //(indexes start from 0) areaStart should be added to arenaRow
      int rowStart =55;   //From which row the table to start +1
      int columnStart =15; // From which column the table to start first column value 0

      int len = multi.length;
      int wid = multi[0].length;

      int areaRow =len+rowStart-1; // how many rows the table has
      int areaColumn=wid+columnStart-1; //how many columns the table has
    /* Start with Creating a workbook and worksheet object */
        InputStream inp = new FileInputStream("Excel_Format_As_Table.xlsx");

        XSSFWorkbook wb = (XSSFWorkbook) WorkbookFactory.create(inp);
        XSSFSheet sheet = (XSSFSheet) wb.getSheetAt(0);

    /* Create an object of type XSSFTable */
    XSSFTable my_table = sheet.createTable();


        /* get CTTable object*/
    CTTable cttable = my_table.getCTTable();



    /* Let us define the required Style for the table */    
    CTTableStyleInfo table_style = cttable.addNewTableStyleInfo();
    table_style.setName("TableStyleMedium20");   

        /* Set Table 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(rowStart, columnStart), new CellReference(areaRow, areaColumn));

    /* Set Range to the Table */
        cttable.setRef(my_data_range.formatAsString());
        cttable.setDisplayName("MYTABLE");      /* this is the display name of the table */
    cttable.setName("Test1");    /* This maps to "displayName" attribute in <table>, OOXML */            
    cttable.setId(1L); //id attribute against table as long value
    //cttable.addNewAutoFilter();

    CTTableColumns columns =cttable.addNewTableColumns();
    columns.setCount(areaColumn); //define number of columns

        /* Define Header Information for the Table */
    for (int i = columnStart; i <= areaColumn; i++)
    {
    CTTableColumn column = columns.addNewTableColumn();

    column.setName("Column" + i);      
        column.setId(i+1);
    }
          int x =-1;
          int y =-1;

         /* Add remaining Table Data */
         for (int i=rowStart;i<=areaRow;i++) //we have to populate 4 rows
         {
             ++x;
             y=-1;
         /* Create a Row */
            XSSFRow row = sheet.createRow(i);
            for (int j = columnStart; j <= areaColumn; j++) //Three columns in each row
            {
                ++y;

                 XSSFCell localXSSFCell = row.createCell(j);
                 if (i == rowStart) 
                 {
                   localXSSFCell.setCellValue("Heading" + j);
                 } 
                 else
                   {
                    localXSSFCell.setCellValue(multi[x][y]);
                   } 

            }

         } 

         System.out.println("X"+x);
         System.out.println("y"+y);

         inp.close();
   /* Write output as File */
    FileOutputStream fileOut = new FileOutputStream("Excel_Format_As_Table.xlsx");
    wb.write(fileOut);
    fileOut.close();

  }
}

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.WorkbookFactory;
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;

Answer 1

对于每个表，CTTable的DisplayName、Name和Id必须是唯一的。因此，如果将新表追加到已有表的工作表中，则必须确保它们是唯一的。

使用代码的示例：

...
    /* Define the data range including headers */
    AreaReference my_data_range = new AreaReference(new CellReference(rowStart, columnStart), new CellReference(areaRow, areaColumn));

    /* Set Range to the Table */
    String wantedDisplayName = "MYTABLE";
    String wantedName = "Test1";
    long id = 0L;

    java.util.List<XSSFTable> all_tables = sheet.getTables();
    for (XSSFTable a_table : all_tables) {
     if (wantedDisplayName.equals(a_table.getDisplayName())) wantedDisplayName += "_1";
     if (wantedName.equals(a_table.getName())) wantedName += "_1";
     if (a_table.getCTTable().getId() > id) id = a_table.getCTTable().getId();

     System.out.println(wantedDisplayName);
     System.out.println(wantedName);
     System.out.println(id);
    }

    id++;

    cttable.setRef(my_data_range.formatAsString());
    cttable.setDisplayName(wantedDisplayName);      /* this is the display name of the table */
    cttable.setName(wantedName);    /* This maps to "displayName" attribute in <table>, OOXML */            
    cttable.setId(id); //id attribute against table as long value
...