Apache POI 5 generate Word table having 30+ columns

Question

I have a JDBC result set which I am trying to export it as a table in Word document (docx). The JDBC result set has 30+ columns; when the Word document is generated, it's showing only 10 columns and the rest gets hidden. How do I show the remaining 20+ columns? Is there a way to make the Word document orientation landscape or set custom page size?

Here is my Java code:

@Override
public Void extractData(ResultSet rs) throws SQLException, DataAccessException {

    ResultSetMetaData rsmd = rs.getMetaData();
    int columnsNumber = rsmd.getColumnCount();
    System.out.println("Columns : " + columnsNumber);
    XWPFDocument document = new XWPFDocument();
    // Creating Table with 1 row and as many columns as in the result set
    XWPFTable table = document.createTable(1, columnsNumber);
    table.setWidth("100%");
    //Get header Row
    XWPFTableRow header = table.getRow(0);
    long c = header.getTableCells().stream().count();
    System.out.println("Count of cells : "+ c);
    //Set header columns
    for (int col = 0; col < columnsNumber; col++) {
        System.out.println("header col :  " + col);
        header.getCell(col).setText(rsmd.getColumnLabel(col + 1));
    }
    //Set data rows
    while (rs.next()) {
        System.out.println("RS row :  " + rs.getRow());
        XWPFTableRow desRow = table.createRow();
        for (int col = 0; col < columnsNumber; col++) {
            final var value = rs.getObject(col + 1);
            String v = value == null ? "" : value.toString();
            desRow.getCell(col).setText(v);
            System.out.println("RS row col : "+ col);
        }
    }
    try {
        document.write(os);
        document.close();
    } catch (IOException e) {
        log.error("Error occurred: {0}", e);
    }
    return null;
}

Maven dependency being used

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.0.0</version>
</dependency>

Snapshot of table generated

Answer 1

It is possible to set landscape for page setting using Apache POI. See Avoid Word displaying blank page at the beginning of .doc generated by Apache POI for a complete example.

But using paper size Letter, a table having 30 columns might not fit into the page even on landscape mode. A Word table column width cannot be smaller than the widest inseparable word in it. So the only way to fit the table might be to make the font size smaller.

In following complete example, the table only fits into paper size Letter in landscape when font size 6 is used.

Another possibility would be using a bigger paper format. In the example it is shown how to set A3 paper format. There the table fits in landscape using bigger font size too.

import java.io.FileOutputStream;

import org.apache.poi.xwpf.usermodel.*;

import org.openxmlformats.schemas.wordprocessingml.x2006.main.*;

public class CreateWordTableLandscape {
    
 static void setFontSize(XWPFTableCell cell, int size) {
  for (XWPFParagraph paragraph : cell.getParagraphs()) {
   for (XWPFRun run : paragraph.getRuns()) {
    run.setFontSize(size);  
   }
  }
 } 
        
 public static void main(String[] args) throws Exception {

  int columnsNumber = 30;
  
  String[] columLabels = new String[columnsNumber];
  for (int c = 0; c < columnsNumber; c++) {
   columLabels[c] = "Column name " + (c+1);  
  }

  XWPFDocument document= new XWPFDocument();

  CTDocument1 ctDocument = document.getDocument();
  CTBody ctBody = ctDocument.getBody();
  CTSectPr ctSectPr = (ctBody.isSetSectPr())?ctBody.getSectPr():ctBody.addNewSectPr();
  CTPageSz ctPageSz = (ctSectPr.isSetPgSz())?ctSectPr.getPgSz():ctSectPr.addNewPgSz();
  ctPageSz.setOrient(STPageOrientation.LANDSCAPE);
  
  //paper size letter
  ctPageSz.setW(java.math.BigInteger.valueOf(Math.round(11 * 1440))); //11 inches
  ctPageSz.setH(java.math.BigInteger.valueOf(Math.round(8.5 * 1440))); //8.5 inches
  
  //paper size A3
  //ctPageSz.setW(java.math.BigInteger.valueOf(Math.round(16.5 * 1440))); //16.5 inches
  //ctPageSz.setH(java.math.BigInteger.valueOf(Math.round(11.7 * 1440))); //11.7 inches

  XWPFParagraph paragraph = document.createParagraph();
  XWPFRun run=paragraph.createRun();  
  run.setText("The table");
  
  // Creating Table with 1 row and as many columns as in the result set
  XWPFTable table = document.createTable(1, columnsNumber);
  table.setWidth("100%");
  //Get header Row
  XWPFTableRow header = table.getRow(0);
  //Set header columns
  for (int col = 0; col < columnsNumber; col++) {
   header.getCell(col).setText(columLabels[col]);
   //setFontSize(header.getCell(col), 6);
  }
  
  FileOutputStream out = new FileOutputStream("./CreateWordTableLandscape.docx");  
  document.write(out);
  out.close();
  document.close();

 }
}

To run this code you need poi-ooxml-full-5.0.0.jar in class path. See https://poi.apache.org/help/faq.html#faq-N10025.

And you must not have any of the older ooxml-schemas-*.jar in class path.