Adding column to Excel using Apache POI

Question

I was wondering about adding new column in a .xlsx file using apache poi. But I could not found anything. Is there any way to do this? Or is there exists any other library to solve this? Thanks in advance.

Answer 1

If you have excel file with existing rows, well defined, the fastest way to add column is to iterate once over the rows and in each iterations add a column at end as beflow code

    FileInputStream excelFile = new FileInputStream(new File(fileDirectory+file));
    Workbook workbook = new XSSFWorkbook(excelFile);
    Sheet datatypeSheet = workbook.getSheetAt(0);
    Iterator<Row> iterator = datatypeSheet.iterator();

    // Add additional column for results
    while (iterator.hasNext()) {
        Row currentRow = iterator.next();
        Cell cell = currentRow.createCell(currentRow.getLastCellNum(), CellType.STRING);
        if(currentRow.getRowNum() == 0)
            cell.setCellValue("NEW-COLUMN");
    }

Hope it helps, I assume your first row is header, the rest will be empty for future modifications

Answer 2

There is no explicit way of doing this using apache POI. If you know the number of rows and the number of columns that you need, you can first create the required number of rows and then create the corresponding cells in the rows. You can refer to the code below if needed.

for(row=0;row<maxRowLimit;row++){
    myRow = sheet.getRow(row);
          if (myRow == null) {
            myRow = sheet.createRow(row);
            myCell=myRow.getCell(columnNumber);
            if (myCell == null)
              myRow.createCell(columnNumber); 
          }
}