NullPointer Exception while using Apache POI

[Paras Ahuja]

Hi everone,
I am stuck while inserting values in an excel sheet. A NullPointer Exceptio keeps on generating..
java.lang.NullPointerException
at ATRY.RtrEv.actionPerformed(RtrEv.java:394)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$000(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

My row variable contains the row below the last filled row in excel sheet.

Cell[] cell=new Cell[10];
for(int i=0;i<10;i++)		
	cell[i]=row.getCell(i);  //Error generated here

Please help...

[Maneesh Godbole]

Looks like row is null. How have you initialized it?
This question has got nothing to do with Swing. Moving to a more appropriate forum.

[Paras Ahuja]

How do i initailize a row?
I couldn't think of a forum for a question having Apache POI.

[Nicola Garofalo]

Paras Ahuja wrote:How do i initailize a row?
I couldn't think of a forum for a question having Apache POI.

Post the code of the class

ATRY.RtrEv

in particular the lines where you first use variable row.

There's something missing there

[Paras Ahuja]

I can't post the whole class. is really big.
Basically, i have a button by clicking on which, data is entered into excel sheet.
Here's the listener code for that button

if(ae.getSource()==ok1)
		{
			try
			{
				InputStream inp=new FileInputStream("Book1.xlsx");
				Workbook wb=WorkbookFactory.create(inp);
				XSSFSheet sheet=(XSSFSheet)wb.getSheetAt(0);
				int lastr=sheet.getLastRowNum();
				Row row=sheet.getRow(lastr+1);
				int sel=table.getSelectedRow();
				Cell[] cell=new Cell[10];
				if(row==null)
				{
					for(int i=0;i<10;i++)
						cell[i]=row.createCell(i);
					
					if(cell[0]==null)
					{
						cell[0].setCellType(Cell.CELL_TYPE_NUMERIC);
						cell[0].setCellValue(lastr+1);
					}
					if(cell[1]==null)
					{
						cell[0].setCellType(Cell.CELL_TYPE_STRING);
						cell[1].setCellValue(sel1);
					}
					if(cell[2]==null)
					{
						cell[2].setCellType(Cell.CELL_TYPE_STRING);
						cell[2].setCellValue(sel3);	
					}
					if(cell[3]==null)
					{
						cell[3].setCellType(Cell.CELL_TYPE_STRING);
						cell[3].setCellValue((String)table.getValueAt(sel, 1));		
					}
					if(cell[4]==null)
					{
						cell[4].setCellType(Cell.CELL_TYPE_STRING);
						cell[4].setCellValue(sel2);		
					}
					if(cell[5]==null)
					{
						cell[5].setCellType(Cell.CELL_TYPE_STRING);
						cell[5].setCellValue((String)table.getValueAt(sel, 2));		
					}
					if(cell[6]==null)
					{
						cell[6].setCellType(Cell.CELL_TYPE_STRING);
						cell[6].setCellValue((String)table.getValueAt(sel, 3));			
					}
					if(cell[7]==null)
					{
						cell[7].setCellType(Cell.CELL_TYPE_STRING);
						cell[7].setCellValue(tf[1].getText());				
					}
					if(cell[8]==null)
					{
						cell[8].setCellType(Cell.CELL_TYPE_STRING);
						cell[8].setCellValue(tf[0].getText());				
					}
					if(cell[9]==null)
					{
						cell[9].setCellType(Cell.CELL_TYPE_STRING);
						cell[9].setCellValue((String)table.getValueAt(sel, 0));					
					}
				}
				
				FileOutputStream fs=new FileOutputStream("Book1.xlsx");
				wb.write(fs);
				fs.close();
				
				getContentPane().setBackground(Color.GREEN);
				revalidate();
				repaint();
			}	
			catch (Exception e)
			{
				e.printStackTrace();
			}
		}

[Maneesh Godbole]

                int lastr=sheet.getLastRowNum();  
                Row row=sheet.getRow(lastr+1); 

Does getRow(lastr+1) exist? i.e. is it non null?

[Nicola Garofalo]

Ok, this chunk of code can be enough.

The point is that row is null or you would not receive a NullPointerException at the line you stated above

cell[i]=row.getCell(i);

I see that in lastr variable you store the last row, don't you?

then you assign to variable row the next row after the last row...

I mean at line 09

Row row=sheet.getRow(lastr+1);

could it be null? I don't know, just an hypothesis.

can you print the row value?

[Paras Ahuja]

If you mean by null that row row is empty,then yes, the row is null. I want to add data in that row.
Now, how to correctify it?

[Maneesh Godbole]

What does the POI API doc say about creating a Row

[Nicola Garofalo]

First thing

At line 12

if(row==null){...}

will send you straight to a NullPointerException

You would write

if(row!=null){...}

if the row is null you must create a new row.
To create a row in POI you may write

row = sheet.createRow(short rownumber)

[Paras Ahuja]

Thanks man, you solved my problem but now there is another one:
Now i am able to set contents of the cells of the new row. But when i try to write it into the excel sheet. it shows following error:

java.io.FileNotFoundException: Book1.xlsx (The process cannot access the file because it is being used by another process)
at java.io.FileOutputStream.open(Native Method)
at java.io.FileOutputStream.<init>(Unknown Source)
at java.io.FileOutputStream.<init>(Unknown Source)
at ATRY.RtrEv.actionPerformed(RtrEv.java:449)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$000(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

Here' the code i am using t write the row to file:

	FileOutputStream fs=new FileOutputStream("Book1.xlsx");
				wb.write(fs);
				fs.close();

[Nicola Garofalo]

Close all your excel instances and try it again

[Paras Ahuja]

I have closed all the excel instances but it still keeps on coming.
I am having one connection to DSN containing the excel file and one InputStream file reading the sheet.
I closed both of them. But still nothing happens...
Please help...

[Nicola Garofalo]

A process is using your file. If you find out what, kill it, or try a system reboot.
Sometimes it happens that antivirus software locks files. Try to disable it if you have one installed, just for a try.

But i am just guessing, sorry, hope someone can help you.

[Paras Ahuja]

Thanks Nicola for all your help. I will figure this out.
When i insert data in another excel sheet(copy of Book1.xlsx), it hapens perfectly, thanks to you..