How to replace blank cell values with a word in excel Java POI

Question

How can I fill all the blank cells in an excel file with the word "Not Available" using Java Apache POI?

I have used the following code but it only works for some of the columns only I can't figure out what is the reason? To be exact from the 1000nd row it works. (I have a very large excel document)

XSSFRow r1;
for(Row r : firstSheet) 
{
  r1=(XSSFRow) r;                    
  for (Cell cell : r) {                           
   if(cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK)
  {
XSSFCell cell2 = r1.createCell(cell.getColumnIndex());
cell.setCellValue("Not Available");
   }                          
  }
  }

What have you tried already? Do you have any code you can post? Very Closely related: http://stackoverflow.com/questions/4929646/how-to-get-an-excel-blank-cell-value-in-apache-poi?rq=1 — geneSummons, Apr 28 '17 at 17:01

score 0 · Answer 1 · answered Apr 28 '17 at 17:05

0

Without your code, its hard to figure out. Sample code

CellReference cr = new CellReference(entry.getKey());
int r = cr.getRow();
int c = cr.getCol();

Row row = sheet.getRow(r);
if (row == null)
    row = sheet.createRow(r);
Cell cell = row.getCell(c, Row.CREATE_NULL_AS_BLANK);
cell.setCellValue("Not Available");

answered Apr 28 '17 at 17:05

user3684675

381
4
8
32

thankyou for the quick reply, can I know what is entry.getKey() in this? – Sachi.Dila Apr 28 '17 at 17:18
check this http://stackoverflow.com/questions/32637441/set-new-cell-value-by-cell-name-in-apache-poi. should help. – user3684675 Apr 28 '17 at 18:01

How to replace blank cell values with a word in excel Java POI

1 Answers1