how to shift column in java xssf poi

Question

How to copy an existing column data and formatting into next column in Apache POI and shift the next column to right.

I tried this. Let say my code is this...

XSSFCell oldCell = worksheet.getRow(0).getCell(1);
XSSFCell newCell =  worksheet.getRow(0).getCell(2);

if(styleMap != null) {   
        if(oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()){   
            newCell.setCellStyle(oldCell.getCellStyle());   
        } else{   
            int stHashCode = oldCell.getCellStyle().hashCode();   
            XSSFCellStyle newCellStyle = styleMap.get(stHashCode);   
            if(newCellStyle == null){   
                newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();   
                newCellStyle.cloneStyleFrom(oldCell.getCellStyle());   
                styleMap.put(stHashCode, newCellStyle);   
            }   
            newCell.setCellStyle(newCellStyle);   
        }   

    }

I able to copy value from old cell to new cell but it doesn't shift the existing column to right.

Thanks in advance for your help.

Answer 1

I cannot believe that it is not in the API.

You can use those 2 handy functions. I will try to make PR to the Apache POI later on.

void shiftColumns(Row row, int startingIndex, int shiftCount) {
    for (int i = row.getPhysicalNumberOfCells()-1;i>=startingIndex;i--){
        Cell oldCell = row.getCell(i);
        Cell newCell = row.createCell(i + shiftCount, oldCell.getCellTypeEnum());
        cloneCellValue(oldCell,newCell);
    }
}

void cloneCellValue(Cell oldCell, Cell newCell) { //TODO test it
    switch (oldCell.getCellTypeEnum()) {
        case STRING:
            newCell.setCellValue(oldCell.getStringCellValue());
            break;
        case NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case FORMULA:
            newCell.setCellFormula(oldCell.getCellFormula());
            break;
        case ERROR:
            newCell.setCellErrorValue(oldCell.getErrorCellValue());
        case BLANK:
        case _NONE:
            break;
    }
}

Answer 2

I haven't worked with POI in several years, but if I remember correctly you must iterate over all the cells in a row and update the column number in each Cell to be what you want. There's no magic "insert column" method. Remember to do this from right to left to avoid completely trashing the worksheet :-)