NumberFormatException: For input string: "null" when trying to read an excel file using apache poi

Question

When it comes time for the controller in my javafx program to call my excel reading method I am given this error:

Exception in thread "JavaFX Application Thread" java.lang.RuntimeException: java.lang.reflect.InvocationTargetException
    at javafx.fxml.FXMLLoader$MethodHandler.invoke(FXMLLoader.java:1774)
    at javafx.fxml.FXMLLoader$ControllerMethodEventHandler.handle(FXMLLoader.java:1657)
    at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:86)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
    at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
    at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:49)
    at javafx.event.Event.fireEvent(Event.java:198)
    at javafx.scene.Node.fireEvent(Node.java:8413)
    at javafx.scene.control.Button.fire(Button.java:185)
    at com.sun.javafx.scene.control.behavior.ButtonBehavior.mouseReleased(ButtonBehavior.java:182)
    at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(BehaviorSkinBase.java:96)
    at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(BehaviorSkinBase.java:89)
    at com.sun.javafx.event.CompositeEventHandler$NormalEventHandlerRecord.handleBubblingEvent(CompositeEventHandler.java:218)
    at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:80)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
    at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
    at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:54)
    at javafx.event.Event.fireEvent(Event.java:198)
    at javafx.scene.Scene$MouseHandler.process(Scene.java:3757)
    at javafx.scene.Scene$MouseHandler.access$1500(Scene.java:3485)
    at javafx.scene.Scene.impl_processMouseEvent(Scene.java:1762)
    at javafx.scene.Scene$ScenePeerListener.mouseEvent(Scene.java:2494)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:381)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:295)
    at java.security.AccessController.doPrivileged(Native Method)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler.lambda$handleMouseEvent$354(GlassViewEventHandler.java:417)
    at com.sun.javafx.tk.quantum.QuantumToolkit.runWithoutRenderLock(QuantumToolkit.java:389)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler.handleMouseEvent(GlassViewEventHandler.java:416)
    at com.sun.glass.ui.View.handleMouseEvent(View.java:555)
    at com.sun.glass.ui.View.notifyMouse(View.java:937)
    at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
    at com.sun.glass.ui.win.WinApplication.lambda$null$148(WinApplication.java:191)
    at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.reflect.InvocationTargetException
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at sun.reflect.misc.Trampoline.invoke(MethodUtil.java:71)
    at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at sun.reflect.misc.MethodUtil.invoke(MethodUtil.java:275)
    at javafx.fxml.FXMLLoader$MethodHandler.invoke(FXMLLoader.java:1769)
    ... 48 more
Caused by: java.lang.NumberFormatException: For input string: "null"
    at sun.misc.FloatingDecimal.readJavaFormatString(FloatingDecimal.java:2043)
    at sun.misc.FloatingDecimal.parseDouble(FloatingDecimal.java:110)
    at java.lang.Double.parseDouble(Double.java:538)
    at java.lang.Double.valueOf(Double.java:502)
    at sample.Tab.setTotal(Tab.java:47)
    at sample.Tab.readFromExcelFile(Tab.java:122)
    at sample.Controller.editTab(Controller.java:121)
    ... 58 more

This is my first javafx project using Apache and I haven't had any trouble with the excel writing method, just the reader. I have gone through my code a few times and can't seem to pinpoint what is causing this error. Be warned, my code may look a little ugly since this is a prototype in addition to me lacking in any hands on experience with Apache, and not much with javafx for that matter.

Here is the relevant code-

Main method:

package sample;

import javafx.application.Application;
import javafx.fxml.FXMLLoader;
import javafx.scene.Parent;
import javafx.scene.Scene;
import javafx.stage.Stage;

import org.apache.poi.xssf.usermodel.*;

public class Main extends Application {

    @Override
    public void start(Stage primaryStage) throws Exception{
        Parent root = FXMLLoader.load(getClass().getResource("TabTrackerUI.fxml"));
        primaryStage.setTitle("The Final Tabs");
        primaryStage.setScene(new Scene(root, 1200, 690));
        primaryStage.show();

        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("Customers");
    }


    public static void main(String[] args) {
        launch(args);
    }
}

The class where I create the methods needed for writing and reading an excel file:

public class Tab{
    private String custName;
    private BigDecimal custTotal;
    private String custLast;




    public Tab(String custName, String custTotal, String custLast){
        this.custName = custName;
        this.custTotal = new BigDecimal(custTotal);
        this.custLast = custLast;
    }

    public Tab() {

    }


    //Getters and Setters
    public String getName(){return custName;}
    public BigDecimal getTotal(){return custTotal;}
    public String getLast(){return custLast;}

    public void setName(String name){this.custName = name;}
    public void setTotal(String price){
        double current = Double.valueOf(String.valueOf(getTotal()));
        this.custTotal = BigDecimal.valueOf(Long.parseLong(current + price));
    }
    public void setLast(String item){this.custLast = item;}

    public static void writeExcel(Tab tab, String excelFilePath) throws IOException {
        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet();

        int rowCount = 0;

        Row row = sheet.createRow(++rowCount);
        writeTab(tab, row);

        try (FileOutputStream outputStream = new FileOutputStream(excelFilePath)) {
            workbook.write(outputStream);
        }
    }

    private static void writeTab(Tab tab, Row row) {
        Cell cell = row.createCell(1);
        cell.setCellValue(tab.getName());

        cell = row.createCell(2);
        cell.setCellValue(String.valueOf(tab.getTotal()));

        cell = row.createCell(3);
        cell.setCellValue(tab.getLast());
    }
    //Read from excel docs
    public String toString() {
        return String.format("%s - %s - %f", custName, custTotal, custLast);
    }

    private Object getCellValue(Cell cell) {
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                return cell.getStringCellValue();

            case Cell.CELL_TYPE_BOOLEAN:
                return cell.getBooleanCellValue();

            case Cell.CELL_TYPE_NUMERIC:
                return cell.getNumericCellValue();
            case Cell.CELL_TYPE_ERROR:
                return null;
        }

        return null;
    }

    List<Tab> readFromExcelFile(String excelFilePath) throws IOException {
        List<Tab> listTab = new ArrayList<>();
        FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

        Workbook workbook = new HSSFWorkbook(inputStream);
        Sheet firstSheet = workbook.getSheetAt(0);
        Iterator<Row> iterator = firstSheet.iterator();

        while (iterator.hasNext()) {
            Row nextRow = iterator.next();
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            Tab aTab = new Tab();

            while (cellIterator.hasNext()) {
                Cell nextCell = cellIterator.next();
                int columnIndex = nextCell.getColumnIndex();

                switch (columnIndex) {
                    case 1:
                        aTab.setName((String) getCellValue(nextCell));
                        break;
                    case 2:
                        aTab.setTotal((String) getCellValue(nextCell));
                        break;
                    case 3:
                        aTab.setLast((String) getCellValue(nextCell));
                        break;
                }


            }
            listTab.add(aTab);
        }

        workbook.close();
        inputStream.close();

        return listTab;
    }

}

Controller class where the method is called(the call is temporary for testing):

@FXML
    public void editTab(ActionEvent event) throws IOException {
        Tab reader = new Tab();
        List<Tab> listTabs = reader.readFromExcelFile(excelFilePath);
        System.out.println(listTabs);
    }

And here is a screenshot of the excel file I generated if you need to see the formatting(it only has one entry, this is because I'm really currently focused on getting the reader to read anything right now.).

http://idownvotedbecau.se/noexceptiondetails/, i.e. the full stacktrace, which shows the *real exception*, i.e. the cause of the `InvocationTargetException`. See [What is a stack trace, and how can I use it to debug my application errors?](https://stackoverflow.com/q/3988788/5221149) — Andreas, May 20 '18 at 03:34
where is your FXMLLoader.load(getClass().getResource("excel.fxml")); file located ?? — rohit thomas, May 20 '18 at 03:35
Thanks so much @Andreas for the link, that was super helpful and I updated it with the full stack trace. — Piemaster316, May 20 '18 at 04:01
@rohitthomas Whoops, I added the main class to my post where the FXML loader is present. — Piemaster316, May 20 '18 at 04:02
So now you know the real error message is `NumberFormatException: For input string: "null"` caused by method `setTotal` at line 47 of `Tab.java`. *You* should then **debug** your code to figure out why `getTotal()` returns null. --- *Hint:* If you use the `Tab()` constructor, `custTotal` is null, so `setTotal` will always fail. — Andreas, May 20 '18 at 04:05
Actually, what on earth are you doing in the `setTotal` method? Why is `custTotal` a `BigDecimal` if you intend to do `double` math? Or `Long` math? Actually, it is doing **string concatenation** *(`current + price` is string concatenation since `price` is a string)*. I'm baffled!!! — Andreas, May 20 '18 at 04:07
@Andreas, some of it is a hit temporary for now, just to get it to work. I'm using a BigDecimal because it's storing a dollar value so I kind of need to, but all of that is irrelevant. — Piemaster316, May 20 '18 at 18:11
@Andreas I also posted the excel file that I'm attempting to read and none of the values are null I'm that file so it can't be from using the Tab() constructer. Maybe the method that reads the file isn't right? From what I can tell there is no reason getTotal should return null in this case unless the reader is reading from the wrong part of the excel file. — Piemaster316, May 20 '18 at 18:17
*"it can't be from using the `Tab()` constructer"* Really, then what is this statement doing? `Tab aTab = new Tab();` --- Oh yeah, it creates a `Tab` object with a null `custTotal`. *Oops!!!* — Andreas, May 20 '18 at 19:12
The error occurs when trying to read the excel file, which has a value of 0.00 for the custTotal. Have you checked out the screenshot linked in the question? I give the file path for that file to the method that reads the excel file which is when the error happens. Opening that excel file shows there is in fact a valid value for custTotal in the excel file and that's where I'm confused. I'm sorry if I'm just being dumb and not understanding the obvious, I'm trying really hard here to understand the problem. Maybe I'm trying too hard. — Piemaster316, May 20 '18 at 20:23
Haha chill out you two :) @Piemaster316 please find the issue which is causing an issue in your code in my answer — rohit thomas, May 21 '18 at 03:02

score 0 · Accepted Answer · answered May 21 '18 at 03:13

public BigDecimal getTotal(){return custTotal;} //culprit --here

     public BigDecimal getTotal(){return custTotal;}//culprit --here
        public String getLast(){return custLast;}

        public void setName(String name){this.custName = name;}
        public void setTotal(String price){
            double current = Double.valueOf(String.valueOf(getTotal())); //culprit --here
            this.custTotal = BigDecimal.valueOf(Long.parseLong(current + price));
        }

The reason you are facing NULL pointer issue is because you haven't assigned a value to custTotal hence NULL pointer when your trying to convert to double value here

double current = Double.valueOf(String.valueOf(getTotal()))

Which in turn calls

public BigDecimal getTotal(){return custTotal;} // custTotal is null still

SOLUTION

In your constructor assign custTotal = 0;

 Tab(){
     custTotal = new BigDecimal(0);
 }

Also you will still face an issue here

this.custTotal = BigDecimal.valueOf(Long.parseLong(current + price));

Because price is still of String and your trying to add double and string which will fail change it to the below and it will work

 double dPrice = Double.valueOf(price);
 this.custTotal = BigDecimal.valueOf(current + dPrice);

Hope this help :)

NumberFormatException: For input string: "null" when trying to read an excel file using apache poi

1 Answers1