getSheet & getSheetAt throws Null Pointer Exception

Question

I'm having trouble with a method I'm using to load an Excel File and saving new info in it:

public void llenarTemplate(String sht, ResultSet rs){

    try {
        int row;
        row = 1;
        wb = new XSSFWorkbook(template);
        System.out.println(wb.getNumberOfSheets());
        for (int sn=0; sn < wb.getNumberOfSheets(); sn++) {
         System.out.println("Sheet " + sn + " is called " + wb.getSheetName(sn));
        }
      ws = wb.getSheet(sht);

      while(rs.next()){
         row++;
           cell = ws.getRow(row).getCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK);
           cell.setCellValue(rs.getString(1));     
           cell = ws.getRow(row).getCell(2, MissingCellPolicy.CREATE_NULL_AS_BLANK);
           cell.setCellValue(rs.getString(2));
       }       
    } catch (IOException ex) {
        Logger.getLogger(exportExcel.class.getName()).log(Level.SEVERE, null, ex);
    } catch (SQLException ex) {
        Logger.getLogger(exportExcel.class.getName()).log(Level.SEVERE, null, ex);
    } 
}

The problem comes when I try to select any other sheet that is not "0". Even when I try to open sheet 0 by name. Whenever I try to write on it I get the following:

    Exception in thread "AWT-EventQueue-0" java.lang.NullPointerException
at FileGenerators.exportExcel.llenarTemplate(exportExcel.java:65)
at Menus.Update_Filters.jMenuItem7ActionPerformed(Update_Filters.java:1019)
at Menus.Update_Filters.access$2100(Update_Filters.java:25)
at Menus.Update_Filters$28.actionPerformed(Update_Filters.java:761)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2022)
at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2348)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
at javax.swing.AbstractButton.doClick(AbstractButton.java:376)
at javax.swing.plaf.basic.BasicMenuItemUI.doClick(BasicMenuItemUI.java:833)
at javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(BasicMenuItemUI.java:877)
at java.awt.Component.processMouseEvent(Component.java:6533)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3324)
at java.awt.Component.processEvent(Component.java:6298)
at java.awt.Container.processEvent(Container.java:2236)
at java.awt.Component.dispatchEventImpl(Component.java:4889)
at java.awt.Container.dispatchEventImpl(Container.java:2294)
at java.awt.Component.dispatchEvent(Component.java:4711)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4888)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4525)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4466)
at java.awt.Container.dispatchEventImpl(Container.java:2280)
at java.awt.Window.dispatchEventImpl(Window.java:2746)
at java.awt.Component.dispatchEvent(Component.java:4711)
at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:758)
at java.awt.EventQueue.access$500(EventQueue.java:97)
at java.awt.EventQueue$3.run(EventQueue.java:709)
at java.awt.EventQueue$3.run(EventQueue.java:703)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:76)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:86)
at java.awt.EventQueue$4.run(EventQueue.java:731)
at java.awt.EventQueue$4.run(EventQueue.java:729)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:76)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:728)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:201)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)

I tried to see if I was getting something wrong so I wrote this:

System.out.println(wb.getNumberOfSheets());
    for (int sn=0; sn < wb.getNumberOfSheets(); sn++) {
     System.out.println("Sheet " + sn + " is called " + wb.getSheetName(sn));
    }

Which displays this as a result

    Sheet 0 is called CATEGORÍAS
    Sheet 1 is called OBJETIVOS
    Sheet 2 is called TIPO DE CURSO
    Sheet 3 is called CÓDIGOS INSTITUCIONALES
    Sheet 4 is called STPS

Still, no matter if I select it with getSheet("OBJETIVOS") or getSheetAt(1), the second I try to write in it I get the error. Even if I call getSheet("CATEGORÍAS") I get the error.

Does anyone now what am I doing wrong?

thanks in advance!

See [What is a stack trace, and how can I use it to debug my application errors?](http://stackoverflow.com/q/3988788/418556) & [What is a Null Pointer Exception, and how do I fix it?](http://stackoverflow.com/q/218384/418556) — Andrew Thompson, Jul 23 '18 at 23:24
*"I tried that.."* Tried **what** exactly? Part of my reason for posting those links it to point out to newbies that stack traces are wonderful things that can help the programmer track down the exact line number in the code where the error occurred, and provide strategies on how to fix it. It should also be obvious once understanding stack traces that while they're great for someone who has the code in front of them, they are useless for someone else (i.e. us) when we're presented with code snippets (there's no way to align the line numbers in the code snippet with those in the stack trace).. — Andrew Thompson, Jul 24 '18 at 01:58
So two comments in closing. 1) For better help sooner, post a [MCVE] or [Short, Self Contained, Correct Example](http://www.sscce.org/). 2) I'm voting to close as a duplicate. — Andrew Thompson, Jul 24 '18 at 01:59
@Andrew Thompson, I tried following the traces but it points to the line: cell=ws.getRow(row).getCell(0,MissingCellPolicy.CREATE_NULL_AS_BLANK); whichs stops throwing the error once I change the sheet number to 0. Thats why I raised the question — Shiny, Jul 24 '18 at 02:17
*"I tried.."* Try (creating &) posting an MCVE / SSCCE. Then I might a) reopen the question and b) be motivated to look closely at it. -- Failing that, (shrugs) good luck with it. — Andrew Thompson, Jul 24 '18 at 02:30
[Sheet.getRow](https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Sheet.html#getRow-int-) returns null if the row is not defined on the sheet. So `ws.getRow(row)` may return null. Do `... if (ws.getRow(row) == null) ws.createRow(row); cell = ws.getRow(row).getCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK); ...` — Axel Richter, Jul 24 '18 at 03:31
Hint for debugging: The stacktrace shows you the code line which causes the problem: `at FileGenerators.exportExcel.llenarTemplate(exportExcel.java:65)`. There the `65` is the code line in your `exportExcel.java` file which causes the problem. — Axel Richter, Jul 24 '18 at 03:37
Thhank you for the answer @AxelRichter, what I was saying is that line 65 is a line that works on sheet 0 but not on any other, I'm gonna try to do what you said (: — Shiny, Jul 24 '18 at 03:39
@AxelRichter aaaand, IT WORKED! Thank you! Sorry the question is closed so I can't vote you up — Shiny, Jul 24 '18 at 03:43
"line 65 is a line that works on sheet 0 but not on any other": Then on sheet 0 the rows are there already but but not on any other sheet. You seems thinking all Excel sheets stores always all rows. But this is not true. Only used rows are stored. Else all Excel files would must contain all rows in their sheets. That would be huge waste of file size, wouldn't it? — Axel Richter, Jul 24 '18 at 03:46

getSheet & getSheetAt throws Null Pointer Exception

0 Answers0