JAVA Excel files get corrupted

Question

I try to write a second table in the file containing the 1st one i added. No overlapping rows, file gets corrupted but it inserts the table, formatted in excel formatAsTable style all fine. How can i eliminate this problem.? I cant have a corrupted file even though the data are all good in it.

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

The DisplayName, the Name and the Id of the CTTable needs to be unique for each table. So you must make sure that they are unique if you append new tables to sheets having tables already.

Example using your code:

...
    /* 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
...