Apache POI xls column Remove

Question

I don't find how to remove a column with the Apache POI API.
I would appreciate a sample code or help on this point.

score 18 · Answer 1 · edited Feb 10 '15 at 23:18

Alan Williamson on the mailing list wrote a small helper for column removal

package org.alanwilliamson.openbd.plugin.spreadsheet;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;


/*
 * Helper functions to aid in the management of sheets
 */
public class SheetUtility extends Object {


    /**
     * Given a sheet, this method deletes a column from a sheet and moves
     * all the columns to the right of it to the left one cell.
     * 
     * Note, this method will not update any formula references.
     * 
     * @param sheet
     * @param column
     */
    public static void deleteColumn( Sheet sheet, int columnToDelete ){
        int maxColumn = 0;
        for ( int r=0; r < sheet.getLastRowNum()+1; r++ ){
            Row row = sheet.getRow( r );

            // if no row exists here; then nothing to do; next!
            if ( row == null )
                continue;

            // if the row doesn't have this many columns then we are good; next!
            int lastColumn = row.getLastCellNum();
            if ( lastColumn > maxColumn )
                maxColumn = lastColumn;

            if ( lastColumn < columnToDelete )
                continue;

            for ( int x=columnToDelete+1; x < lastColumn + 1; x++ ){
                Cell oldCell    = row.getCell(x-1);
                if ( oldCell != null )
                    row.removeCell( oldCell );

                Cell nextCell   = row.getCell( x );
                if ( nextCell != null ){
                    Cell newCell    = row.createCell( x-1, nextCell.getCellType() );
                    cloneCell(newCell, nextCell);
                }
            }
        }


        // Adjust the column widths
        for ( int c=0; c < maxColumn; c++ ){
            sheet.setColumnWidth( c, sheet.getColumnWidth(c+1) );
        }
    }


    /*
     * Takes an existing Cell and merges all the styles and forumla
     * into the new one
     */
    private static void cloneCell( Cell cNew, Cell cOld ){
        cNew.setCellComment( cOld.getCellComment() );
        cNew.setCellStyle( cOld.getCellStyle() );

        switch ( cNew.getCellType() ){
            case Cell.CELL_TYPE_BOOLEAN:{
                cNew.setCellValue( cOld.getBooleanCellValue() );
                break;
            }
            case Cell.CELL_TYPE_NUMERIC:{
                cNew.setCellValue( cOld.getNumericCellValue() );
                break;
            }
            case Cell.CELL_TYPE_STRING:{
                cNew.setCellValue( cOld.getStringCellValue() );
                break;
            }
            case Cell.CELL_TYPE_ERROR:{
                cNew.setCellValue( cOld.getErrorCellValue() );
                break;
            }
            case Cell.CELL_TYPE_FORMULA:{
                cNew.setCellFormula( cOld.getCellFormula() );
                break;
            }
        }

    }
}

Column widths should be adjusted after the deleted column. So the loop that adjusts column widths should start like: for ( int c=columnToDelete; c < maxColumn; c++ ) ... — mostar, Mar 13 '15 at 09:48
Not bad but doesn't work when merged column in header or wherever — amdev, Jun 27 '16 at 09:52
Just a heads up on that code, as some of the functions used are now deprecated: On "deleteColumn", Instead of "row.createCell( x-1, nextCell.getCellType() )", you have to use "row.createCell(x-1, nextCell.getCellTypeEnum());" Also, in "cloneCell", change "cNew.getCellType" for "cNew.getCellTypeEnum" and the cases for their new counterparts (also, adding a "NONE" case). — Neuromante, Sep 28 '17 at 13:35

score 4 · Answer 2 · edited Oct 07 '19 at 13:41

The answer of cporte is perfectly fine but imho a bit hard to read.

The Idea:

For every row, delete the cell representing the column which shall be deleted and move all cells to the right of this column one to the left.

The simplified Implementation:

//Variables for completeness
Sheet sheet;
int columnToDelete;

for (int rId = 0; rId <= sheet.getLastRowNum(); rId++) {
    Row row = sheet.getRow(rId);
    for (int cID = columnToDelete; cID < row.getLastCellNum(); cID++) {
        Cell cOld = row.getCell(cID);
        if (cOld != null) {
            row.removeCell(cOld);
        }
        Cell cNext = row.getCell(cID + 1);
        if (cNext != null) {
            Cell cNew = row.createCell(cID, cNext.getCellType());
            cloneCell(cNew, cNext);
            sheet.setColumnWidth(cID, sheet.getColumnWidth(cID + 1));
        }
    }
}

The clone cell method copied from the other answer for completeness:

private static void cloneCell( Cell cNew, Cell cOld ){
    cNew.setCellComment( cOld.getCellComment() );
    cNew.setCellStyle( cOld.getCellStyle() );

    switch ( cNew.getCellType() ){
        case Cell.CELL_TYPE_BOOLEAN:{
            cNew.setCellValue( cOld.getBooleanCellValue() );
            break;
        }
        case Cell.CELL_TYPE_NUMERIC:{
            cNew.setCellValue( cOld.getNumericCellValue() );
            break;
        }
        case Cell.CELL_TYPE_STRING:{
            cNew.setCellValue( cOld.getStringCellValue() );
            break;
        }
        case Cell.CELL_TYPE_ERROR:{
            cNew.setCellValue( cOld.getErrorCellValue() );
            break;
        }
        case Cell.CELL_TYPE_FORMULA:{
            cNew.setCellFormula( cOld.getCellFormula() );
            break;
        }
    }

}

for including all line you have to correct this line from `for(int rId = 0; rId < sheet.getLastRowNum(); rId++) ` to `for (int rId = 0; rId <= sheet.getLastRowNum(); rId++)` — Omid Rostami, Aug 23 '18 at 09:17
I've updated it to fix this issue. I kind of remeber that I had to fix that in my code too but forgot to update it here. so thanks for pointing this out — codewing, Aug 23 '18 at 18:48

score 3 · Answer 3 · answered Nov 18 '17 at 01:00

codewing's solution worked for me like a charm with the following minor changes:

When we clone the cell, the call should be cloneCell(cNew, cNext)
We should set the column width only for the first row.
I'm using version 3.17 of the api, so a few things changed (like CellType changed from int to an enum).

Full code is below (for clarity):

private void deleteColumn(Sheet sheet, int columnToDelete) {
    for (int rId = 0; rId < sheet.getLastRowNum(); rId++) {
        Row row = sheet.getRow(rId);
        for (int cID = columnToDelete; cID < row.getLastCellNum(); cID++) {
            Cell cOld = row.getCell(cID);
            if (cOld != null) {
                row.removeCell(cOld);
            }
            Cell cNext = row.getCell(cID + 1);
            if (cNext != null) {
                Cell cNew = row.createCell(cID, cNext.getCellTypeEnum());
                cloneCell(cNew, cNext);
                //Set the column width only on the first row.
                //Other wise the second row will overwrite the original column width set previously.
                if(rId == 0) {
                    sheet.setColumnWidth(cID, sheet.getColumnWidth(cID + 1));

                }
            }
        }
    }
}

private void cloneCell(Cell cNew, Cell cOld) {
    cNew.setCellComment(cOld.getCellComment());
    cNew.setCellStyle(cOld.getCellStyle());

    if (CellType.BOOLEAN == cNew.getCellTypeEnum()) {
        cNew.setCellValue(cOld.getBooleanCellValue());
    } else if (CellType.NUMERIC == cNew.getCellTypeEnum()) {
        cNew.setCellValue(cOld.getNumericCellValue());
    } else if (CellType.STRING == cNew.getCellTypeEnum()) {
        cNew.setCellValue(cOld.getStringCellValue());
    } else if (CellType.ERROR == cNew.getCellTypeEnum()) {
        cNew.setCellValue(cOld.getErrorCellValue());
    } else if (CellType.FORMULA == cNew.getCellTypeEnum()) {
        cNew.setCellValue(cOld.getCellFormula());
    }
}

score 1 · Answer 4 · answered Jul 24 '09 at 21:39

1

I think you have to go down each HSSFRow and call HSSFRow.getCell and then HSSFRow.removeCell. The API is oriented towards rows, rather than columns, and very few operations work at the whole column level.

Sample code (untested):

HSSFSheet sheet = ...
int colToRemove = 5;
Iterator rowIter = sheet.iterator();
while (rowIter.hasNext()) {
   HSSFRow row = (HSSFRow)rowIter.next();
   HSSFCell cell = row.getCell(colToRemove);
   row.removeCell(cell);
}

answered Jul 24 '09 at 21:39

jsight

27,819
25
107
140

3

nope. doesn't work - this just removed all data of this cell (like content, style, ...) but the column is still there. – Alexander Jan 17 '12 at 15:49
1

You would then need to shift all relevant cells one column to the left. – assylias Jan 21 '13 at 07:29
@assylias +1, that works. probably you should have written this in separate answer, I missed you comment and spend 1.5h reading docs while came up with the same idea. – Nazar Jul 31 '20 at 03:07

score 0 · Answer 5 · edited Jul 31 '20 at 13:25

0

There is a term confusion: the action that author author would like to achieve is called column shift it terms of Apache POI interface. org.apache.poi.ss.usermodel.Sheet interface provide a clean method to do such thing:

sheet.shiftColumns(startRangeIndex, endRangeIndex, directionQuantifier);

For instance, moving Column B to one position left is easily achievable by calling:

Sheet sheet = loadRequiredSheet();
sheet.shiftColumns(2, 3, -1);

Column A        Column B                 Column C
Data here    to be removed   <- t should be moved to the left

edited Jul 31 '20 at 13:25

assylias

321,522
82
660
783

answered Jul 31 '20 at 03:00

Nazar

79
2
10

1

This should work in theory, but in practice seems to hit `IndexOutOfBoundException`s on write. – Aaron Silverman Jul 12 '21 at 00:40
I can confirm that proposed approach worked for me, please ensure that your ranges are valid – Nazar Jul 13 '21 at 01:13

score 0 · Answer 6 · edited Nov 29 '22 at 17:38

The code above is working perfectly but I did some modification over the POI version which we are using in case you are using POI version4.0.0. You can refer the code below for deleting column in excel by using java and POI.

public static void deleteColumn(XSSFSheet sheet, int columnToDelete) {
    for (int rId = 0; rId < sheet.getLastRowNum(); rId++) {
        Row row = sheet.getRow(rId);
        for (int cID = columnToDelete; cID < row.getLastCellNum(); cID++) {
            Cell cOld = row.getCell(cID);
            if (cOld != null) {
                row.removeCell(cOld);
            }
            Cell cNext = row.getCell(cID + 1);
            if (cNext != null) {
                Cell cNew = row.createCell(cID, cNext.getCellType());
                cloneCell(cNew, cNext);
                //Set the column width only on the first row.
                //Other wise the second row will overwrite the original column width set previously.
                if(rId == 0) {
                    sheet.setColumnWidth(cID, sheet.getColumnWidth(cID + 1));

                }
            }
        }
    }
}


public static void cloneCell(Cell cNew, Cell cOld) {
    cNew.setCellComment(cOld.getCellComment());
    cNew.setCellStyle(cOld.getCellStyle());

    if (CellType.BOOLEAN == cNew.getCellType()) {
        cNew.setCellValue(cOld.getBooleanCellValue());
    } else if (CellType.NUMERIC == cNew.getCellType()) {
        cNew.setCellValue(cOld.getNumericCellValue());
    } else if (CellType.STRING == cNew.getCellType()) {
        cNew.setCellValue(cOld.getStringCellValue());
    } else if (CellType.ERROR == cNew.getCellType()) {
        cNew.setCellValue(cOld.getErrorCellValue());
    } else if (CellType.FORMULA == cNew.getCellType()) {
        cNew.setCellValue(cOld.getCellFormula());
    }
}

Apache POI xls column Remove

6 Answers6

The Idea:

The simplified Implementation:

Linked

Related