Java Null Pointer Exception for Apache POI in reading cell data for CSV conversion

Question

I'm trying to do two things for this application:one when the class is called upon, it will run from top to bottom and two, I can call the public method "xlxTOcsvConverter" and supply the three needed parameters for future use...that said, if the class is run, 4 files will be input, and 4 files will be written. The first three are working beautifully, but the last one, the one with the comment, seems to fail with the given error. Now the issue I have is that the data in, is maintained by an outside company and we have read privileges only, so all I can do is ask them to maintain a set cell formatting, but that may not stick. What I am noticing for the cell that is failing, is that it is formatted as a date, but is empty. All the other empty cells are general formats; it is literally the only cell around it formatted as such. I'm guessing this is why it failing. Am I missing some other if for the cell_types?

/*
* To change this license header, choose License Headers in Project     Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package datarefresh;

import java.io.FileInputStream;
import java.io.FileWriter;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

/**
*
* @author null
*/
public class DataRefresh {

public static String EOL = System.getProperty("line.separator"); //Finding out OS specific EOL and setting it to a string named EOL

/**
* @param args the command line arguments
*/
public static void main(String[] args) {
    String StrInfoXLS = "\\\\path\\Dashboard.xls";
    String POSPwXLS = "\\\\path\\POS Passwords.xls";
    String NetPwXLS = "\\\\path\\Dashboard Data.xls";
    String SnLPwXLS = "\\\\path\\AccessVia ID & Passcodes - Helpdesk.xls";
    String StrInfoCSV = "\\\\path\\StoreInfoTst.csv";
    String POSPwCSV = "\\\\path\\POS PasswordsTst.csv";
    String NetPwCSV = "\\\\path\\Dashboard DataTst.csv";
    String SnLPwCSV = "\\\\path\\AccessVia ID & Passcodes - HelpdeskTst.csv";       
    String StrInfoSht = "Store List";
    String POSPwSht = "Sheet1";
    String NetPwSht = "Network";
    String SnLPwSht = "S & L Store List w Passcode";
    xlxTOcsvConverter(StrInfoXLS,StrInfoSht,StrInfoCSV);
    xlxTOcsvConverter(POSPwXLS,POSPwSht,POSPwCSV);
    xlxTOcsvConverter(NetPwXLS,NetPwSht,NetPwCSV);
    xlxTOcsvConverter(SnLPwXLS,SnLPwSht,SnLPwCSV);   //THIS ONE IS NOT WORKING
}

public static void xlxTOcsvConverter(String inFile, String inSheet, String outFile) {
    DataRefresh GetIt = new DataRefresh();
    String data = "";
    try{
        FileWriter output = new FileWriter(outFile);
        Workbook wb = WorkbookFactory.create(new FileInputStream(inFile));
        Sheet sheet = wb.getSheet(inSheet);
        Row row = null;
        for(int i = 0; i<sheet.getLastRowNum(); i++){
            row = sheet.getRow(i);
            for(int j = 0; j<row.getLastCellNum(); j++){
                Cell cell = row.getCell(j);
                data = GetIt.getCellValue(cell);
                output.write(data + ";");
            }
            output.write(EOL);
        }
        output.close();
    }
    catch(Exception e){
        System.out.println(e);
    }
}

private String getCellValue(Cell cell){
    String data = "";
    if(cell == null){
        return "";
    }
    if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
        return "";
    }
    if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){
        switch (cell.getCachedFormulaResultType()){
            case Cell.CELL_TYPE_NUMERIC:
                if(DateUtil.isCellDateFormatted(cell)){
                    data = String.valueOf(cell.getDateCellValue());
                    return data;
                }else{
                    double temp = cell.getNumericCellValue();
                    data = String.format("%.0f", temp);
                    return data;
                }
            case Cell.CELL_TYPE_STRING:
                data = cell.getStringCellValue();
                return data;
        }
    }
    if(cell.getCellType() == Cell.CELL_TYPE_STRING){
        data = cell.getStringCellValue();
        return data;
    }
    if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
        if(DateUtil.isCellDateFormatted(cell)){
            data = String.valueOf(cell.getDateCellValue());
            return data;
        }else{
            double temp = cell.getNumericCellValue();
            data = String.format("%.0f", temp);
            return data;
        }
    }
    return "";
}

}

This is the stacktrace for it:

Exception in thread "main" java.lang.NullPointerException
    at jdk.nashorn.internal.runtime.ECMAErrors.typeError(ECMAErrors.java:214)
    at jdk.nashorn.internal.runtime.ECMAErrors.typeError(ECMAErrors.java:186)
    at jdk.nashorn.internal.runtime.ECMAErrors.typeError(ECMAErrors.java:173)
    at jdk.nashorn.internal.objects.Global.checkObject(Global.java:1500)
    at jdk.nashorn.internal.objects.NativeError.printStackTrace(NativeError.java:187)
    at datarefresh.DataRefresh.xlxTOcsvConverter(DataRefresh.java:68)
    at datarefresh.DataRefresh.main(DataRefresh.java:45)
Java Result: 1
BUILD SUCCESSFUL (total time: 5 seconds)

I changed the catch to:

    catch(IOException | InvalidFormatException e){
        printStackTrace(e);
    }

Now the stacktrace returns:

Exception in thread "main" java.lang.NullPointerException
    at datarefresh.DataRefresh.xlxTOcsvConverter(DataRefresh.java:60)
    at datarefresh.DataRefresh.main(DataRefresh.java:47)
Java Result: 1

Your error is on line 68 as told from the stack trace. Take a look at stackoverflow.com/questions/218384/… — Forseth11
– Forseth11, Commented May 24, 2015 at 5:08
With the InvalidFormatException, line 60 is: for(int j = 0; j<row.getLastCellNum(); j++){ — user3377627
– user3377627, Commented May 24, 2015 at 5:40

Axel Richter · Accepted Answer · 2015-05-24 08:17:48Z

1

If there are totally empty rows between row 0 and row sheet.getLastRowNum() then the row will be NULL after row = sheet.getRow(i). This is because totally empty rows are not physically stored within the XLS file.

This you should consider. Example:

public static void xlxTOcsvConverter(String inFile, String inSheet, String outFile) {
    DataRefresh GetIt = new DataRefresh();
    String data = "";
    try {
        FileWriter output = new FileWriter(outFile);
        Workbook wb = WorkbookFactory.create(new FileInputStream(inFile));
        Sheet sheet = wb.getSheet(inSheet);

        Row row = null;
        for(int i = 0; i<=sheet.getLastRowNum(); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                data = "empty row";
                output.write(data);
            } else {
                for(int j = 0; j<row.getLastCellNum(); j++) {
                    Cell cell = row.getCell(j);
                    data = GetIt.getCellValue(cell);
                    output.write(data + ((j<row.getLastCellNum()-1) ? ";" : ""));
                }
            }
            output.write(EOL);
        }


        output.close();
    }
    catch(Exception e) {
        e.printStackTrace();
    }
}

answered May 24, 2015 at 8:17

Axel Richter

62.5k7 gold badges70 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3377627 Over a year ago

I was unaware of that. I was checking in getting the cell data, whether the cell was null, but I was unaware that the row itself could be null. I did add some extra code to be able to run the jar from the command-line, to call only the xlsTOcsvConverter method. This way if I run the program from top to bottom, ie no args, it does only the 4 files, and if I call it from another jar or command-line and I supply an initial "convert" parameter and pass along the 3 strings I need, it can run only the method using said parameters. THANKS for the help.

Collectives™ on Stack Overflow

Java Null Pointer Exception for Apache POI in reading cell data for CSV conversion

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related