2

I need help regarding a recurring problem. Earlier I asked a similar question but it was partially answered. Question here The problem is that everytime I wanted to write on a row that doesn't have any data or blank I always get a null pointer error problem. My temporary solution was to populate those rows with data so I can write something on each column. Also the solution given by one of the volunteers who helped me was only a temporary one. I made a few alterations regarding my code (below). Null pointer error points to the line with double asterisk.

public void writeSomething(XSSFWorkbook wb){
        for (String elem: listOfSheetNames){
            if (elem.equals("Sheet2")){

                sheet = wb.getSheet(elem);
                XSSFRow row = sheet.getRow(2);
                **XSSFCell cell = row.getCell(3, Row.CREATE_NULL_AS_BLANK );

                if (cell.getCellType() == Cell.CELL_TYPE_BLANK){
                    cell = row.createCell(3);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cell.setCellValue("this was blank");
                }
            }
        }
    }

I went on to research the problem and I saw that they're using MissingCellPolicy indicated here I went on to try that code but it doesn't work so I checked on Apache Docs and they have Row.Create_Null_as_Blank but am not sure if it is the same with MissingCellPolicy.Create_Null_as_Blank or the latter was just used in previous poi version. Although this should have solved the problem regarding columns or rows which are blank. I still got a null pointer exception error. I am using Apache poi version 3.9 btw if this helps. Any help is greatly appreciate. Thanks

UPDATE 1

I updated my code using jims solution. I added a method that will check if a row exists or not. But I still have the same error show with two asterisks. Here is my code:

public void writeSomething(XSSFWorkbook wb){
        for (String elem: listOfSheetNames){
            if (elem.equals("Sheet2")){
                int y=1;
                sheet = wb.getSheet(elem); 
                XSSFRow row = null; //create row variable outside if scope

                if (isRowEmpty(sheet.getRow(4))== false){
                    row = sheet.createRow(4);
                }   

                **XSSFCell cell = row.getCell(y, Row.CREATE_NULL_AS_BLANK );

                if (cell.getCellType() == Cell.CELL_TYPE_BLANK){
                    cell = row.createCell(y);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cell.setCellValue("Hooooy this was blank");
                }

            }
        }
    }

isRowEmpty

public boolean isRowEmpty(Row row){

        if (row == null){
            return true;
        }
        else {
            return false;
        }
    }

Update 2

I found a workaround for my code just in case someone would find it useful. I didn't use the row.getCell since I always encounter NUll pointer error. See code below

public void writeSomething(XSSFWorkbook wb){
        for (String elem: listOfSheetNames){
            if (elem.equals("Sheet2")){
                int y=1; //sets column number
                sheet = wb.getSheet(elem); 
                XSSFRow row = null; //create row variable outside if scope

                if (isRowEmpty(sheet.getRow(19))== false){
                    row = sheet.createRow(19);
                    XSSFCell cell = row.createCell(y);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cell.setCellValue("Hooooy this was blank");
                }   

            }
        }
    }
Community
  • 1
  • 1
dimas
  • 2,487
  • 6
  • 40
  • 66
  • By using following code get the values and do null check http://stackoverflow.com/a/12529872/1211000 – swamy Jan 09 '13 at 05:52
  • Hi am just wondering how I could add that lines of code. Because I need to declare first XSSFCell cell = row.getCell... and then check the cell type. But the code stops running when I declare XSSFCell cell so can't check what type of cell it contains. – dimas Jan 09 '13 at 06:11
  • use getCellValue(row.getCell(3) method from my posted link//it returns String,check for null.No need again wat cell contains – swamy Jan 09 '13 at 06:28
  • Rows can be null too! Are you sure the row you're using exists? – Gagravarr Jan 09 '13 at 07:11
  • hi gagravarr i believe the row doesn't exist as well so am gonna try Jims solution. Thanks – dimas Jan 09 '13 at 22:09

1 Answers1

2

If the cell you need does not exist you must create it with 

HSSFRow.createCell(int columnIndex, int type)

Likewise, if the row does not exist you create it with

HSSFSheet.createRow(int rownum)

XSSF has identical methods as well.

Jim Garrison
  • 85,615
  • 20
  • 155
  • 190
  • Hi Jim, thanks for answering. Am just wondering what version of Apache POI you're using coz am using version 3.9. I can't find the HSSFSHeet.createRow(int ) method. – dimas Jan 09 '13 at 22:39
  • [3.9 javadocs for createRow(int)](http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFSheet.html#createRow%28int%29) – Jim Garrison Jan 09 '13 at 23:54
  • hi jim, thanks i found it. I just forgot to answer my own question. However I have edited my code as shown above and the error is still being shown. Thanks for your time – dimas Jan 10 '13 at 00:00
  • actually am trying to create row 4 and cell 1 which is y=1. I have this source for that XSSFCell cell = row.getCell(y, Row.CREATE_NULL_AS_BLANK ) – dimas Jan 10 '13 at 00:09
  • What is the definition of `isRowEmpty()`? Are you sure it does what you expect? – Jim Garrison Jan 10 '13 at 00:21
  • hi jim, i placed the code for isRowEmpty. It checks if a row is null and returns value. I tried it on rows that doesn't have data yet and has one and it works. – dimas Jan 10 '13 at 00:26
  • `if (isRowEmpty(sheet.getRow(19))== false){ row = sheet.createRow(19);...` the call to `isRowEmpty()` returns `true` if the row is empty. You have the test backwards, you want to create the row if `isRowEmpty(...)` returns `true`. Change the test to just `if (isRowEmpty(sheet.getRow(19))) { ...` – Jim Garrison Jan 10 '13 at 03:13