CODEWITHSUNDEEP
javaexcelapache-poi
Anh Nguyen
Anh Nguyen

Reputation: 313

How to remove cell content in excel using apache poi

I want to remove the cell content but keeping its style but it's always remove the style and content. Here's my code:

private static void RefreshReport(String sheetName, String resultColumn) {
        try {
            InputStream inp = new FileInputStream(getExcelFile());
            Workbook wb = WorkbookFactory.create(inp);
            Sheet sheet = wb.getSheet(sheetName);
            int colResult = getColumnResult(sheet, resultColumn);
            int rowStart = getRowResult(sheet, resultColumn);
            int rowEnd = Math.max(20, sheet.getLastRowNum());
            for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
                Row r = sheet.getRow(rowNum);
                if (r != null) {
                    Cell cell = r.createCell(colResult);
                    if (cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                        CellStyle cs = wb.createCellStyle();
                        cs = cell.getCellStyle();
                        r.removeCell(cell);
                        cell = r.createCell(colResult);
                        cell.setCellStyle(cs);
                    }
                }
            }
            FileOutputStream fileOut = new FileOutputStream(getExcelFile());
            wb.write(fileOut);
            fileOut.close();
            inp.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

Any help would be great.

Upvotes: 2

Views: 14414

Answers (2)

Tuan Anh Le
Tuan Anh Le

Reputation: 23

Related to the rgettman's comment. But in POI 5.0, you should use cell.setBlank() instead, because cell.setCellType(CellType.BLANK) is deprecated.

Upvotes: 0

rgettman
rgettman

Reputation: 178243

The reason that your method isn't working is that you are always calling createCell if the the row r is not null. This will overwrite any Cell that already exists, including contents and styling.

What you need to do is call getCell instead. This will retrieve the Cell at that column index, provided it exists, or null if it doesn't exist. Then if it exists, you can process the Cell as you are doing.

if (r != null) {
   Cell cell = r.getCell(colResult);
   if (cell != null && cell.getCellType() != CellType.BLANK) {
       // rest of your code here

In an alternative to the delete/re-create method you have, you can just set the cell type to BLANK. This will set the contents to blank, but it will also preserve the cell style currently on that cell. There is no need to delete the cell and re-create it.

if (cell.getCellType() != CellType.BLANK) {
   cell.setCellType(CellType.BLANK);
}

Upvotes: 3

Related Questions