Issue
What im trying to do here is to find .txt file, draw some text out, asign it to its respective cell on excel and then from same folder, bring image directories and asign it to next sheet of same excel workbook.
The first part went well with method FoldFil but the image directory part is throwing error as given below. Ive checked by printing out of the l1l etc got well asigned as string directory and it works. No error is shown if i delete the InputStream.close() to OutputSteram.close() portion of the imgSource method and I cant seem to find what the problem is. Just side not, it would be big help if sb can help me with somehow writing less code for the switch case down there.
public class BringFolderFrame extends JFrame implements ActionListener{
private JPanel contentPane;
static BringFolderFrame frame;
//--------------------------------------------------Main
public static void main(String[] args) {
EventQueue.invokeLater(new Runnable() {
public void run() {
try {
frame = new BringFolderFrame();
frame.setVisible(true);
} catch (Exception e) {
e.printStackTrace();
}
}
});
}
//------------------------------------------constructor class below**
public BringFolderFrame() {
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
setBounds(100, 100, 451, 248);
contentPane = new JPanel();
contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
contentPane.setLayout(new BorderLayout(0, 0));
setContentPane(contentPane);
JPanel panel = new JPanel();
panel.setBackground(SystemColor.textHighlight);
contentPane.add(panel, BorderLayout.CENTER);
JButton btnFolderSelector = new JButton("Select Folder");
panel.add(btnFolderSelector,BorderLayout.CENTER);
btnFolderSelector.addActionListener(this);
setResizable(false);
}//-------------------------------------------------------------------all thats seen is frame is above this--
//--------------------------------------------------------------image directory to excel for excess to next frame
public void imgSource(File gvnfold) throws IOException {
FileInputStream inputStream=new FileInputStream("src/main/java/test.xlsx");
XSSFWorkbook workbook=new XSSFWorkbook(inputStream);
XSSFSheet sheet2=workbook.getSheetAt(1); //2nd sheet
String filenm="a";
File[] folder = gvnfold.listFiles();
for (File file : folder) {
if (file.isFile()) {
filenm=file.getName().toLowerCase();
System.out.println(filenm);
//---------------------putting image directory to respective excel cell by switch case
switch (filenm) {
case "1_1_1.jpg":XSSFCell l1l=sheet2.getRow(1).getCell(1); l1l.setCellValue(gvnfold+"\\"+filenm); break;
case "1_1_2.jpg":XSSFCell l1c=sheet2.getRow(2).getCell(1); l1c.setCellValue(gvnfold+"\\"+filenm); break;
case "1_1_3.jpg":XSSFCell l1r=sheet2.getRow(3).getCell(1); l1r.setCellValue(gvnfold+"\\"+filenm); break;
case "1_2_1.jpg":XSSFCell l2l=sheet2.getRow(4).getCell(1); l2l.setCellValue(gvnfold+"\\"+filenm); break;
case "1_2_2.jpg":XSSFCell l2c=sheet2.getRow(5).getCell(1); l2c.setCellValue(gvnfold+"\\"+filenm); break;
case "1_2_3.jpg":XSSFCell l2r=sheet2.getRow(6).getCell(1); l2r.setCellValue(gvnfold+"\\"+filenm); break;
case "1_3_1.jpg":XSSFCell l3l=sheet2.getRow(7).getCell(1); l3l.setCellValue(gvnfold+"\\"+filenm); break;
case "1_3_2.jpg":XSSFCell l3c=sheet2.getRow(8).getCell(1); l3c.setCellValue(gvnfold+"\\"+filenm); break;
case "1_3_3.jpg":XSSFCell l3r=sheet2.getRow(9).getCell(1); l3r.setCellValue(gvnfold+"\\"+filenm); break;
case "1_4_1.jpg":XSSFCell l4l=sheet2.getRow(10).getCell(1); l4l.setCellValue(gvnfold+"\\"+filenm); break;
case "1_4_2.jpg":XSSFCell l4c=sheet2.getRow(11).getCell(1); l4c.setCellValue(gvnfold+"\\"+filenm); break;
case "1_4_3.jpg":XSSFCell l4r=sheet2.getRow(12).getCell(1); l4r.setCellValue(gvnfold+"\\"+filenm); break;
case "1_5_1.jpg":XSSFCell l5l=sheet2.getRow(13).getCell(1); l5l.setCellValue(gvnfold+"\\"+filenm); break;
case "1_5_2.jpg":XSSFCell l5c=sheet2.getRow(14).getCell(1); l5c.setCellValue(gvnfold+"\\"+filenm); break;
case "1_5_3.jpg":XSSFCell l5r=sheet2.getRow(15).getCell(1); l5r.setCellValue(gvnfold+"\\"+filenm); break;
case "2_1_1.jpg":XSSFCell r1l=sheet2.getRow(16).getCell(1); r1l.setCellValue(gvnfold+"\\"+filenm); break;
case "2_1_2.jpg":XSSFCell r1c=sheet2.getRow(17).getCell(1); r1c.setCellValue(gvnfold+"\\"+filenm); break;
case "2_1_3.jpg":XSSFCell r1r=sheet2.getRow(18).getCell(1); r1r.setCellValue(gvnfold+"\\"+filenm); break;
case "2_2_1.jpg":XSSFCell r2l=sheet2.getRow(19).getCell(1); r2l.setCellValue(gvnfold+"\\"+filenm); break;
case "2_2_2.jpg":XSSFCell r2c=sheet2.getRow(20).getCell(1); r2c.setCellValue(gvnfold+"\\"+filenm); break;
case "2_2_3.jpg":XSSFCell r2r=sheet2.getRow(21).getCell(1); r2r.setCellValue(gvnfold+"\\"+filenm); break;
case "2_3_1.jpg":XSSFCell r3l=sheet2.getRow(22).getCell(1); r3l.setCellValue(gvnfold+"\\"+filenm); break;
case "2_3_2.jpg":XSSFCell r3c=sheet2.getRow(23).getCell(1); r3c.setCellValue(gvnfold+"\\"+filenm); break;
case "2_3_3.jpg":XSSFCell r3r=sheet2.getRow(24).getCell(1); r3r.setCellValue(gvnfold+"\\"+filenm); break;
case "2_4_1.jpg":XSSFCell r4l=sheet2.getRow(25).getCell(1); r4l.setCellValue(gvnfold+"\\"+filenm); break;
case "2_4_2.jpg":XSSFCell r4c=sheet2.getRow(26).getCell(1); r4c.setCellValue(gvnfold+"\\"+filenm); break;
case "2_4_3.jpg":XSSFCell r4r=sheet2.getRow(27).getCell(1); r4r.setCellValue(gvnfold+"\\"+filenm); break;
case "2_5_1.jpg":XSSFCell r5l=sheet2.getRow(28).getCell(1); r5l.setCellValue(gvnfold+"\\"+filenm); break;
case "2_5_2.jpg":XSSFCell r5c=sheet2.getRow(29).getCell(1); r5c.setCellValue(gvnfold+"\\"+filenm); break;
case "2_5_3.jpg":XSSFCell r5r=sheet2.getRow(30).getCell(1); r5r.setCellValue(gvnfold+"\\"+filenm); break;
}
}
}
inputStream.close();
FileOutputStream outputStream = new FileOutputStream("src/main/java/test.xlsx");
workbook.write(outputStream);
workbook.close();
outputStream.close();
}
//-------------------------------------------------method that does ---folder>file.txt>info-->excel
public void foldFil(File gvnfold) throws IOException {
//-------------------------------------------------------------finds .txt file and reader it -------------
String filenm="a";
File[] folder = gvnfold.listFiles();
for (File file : folder) {
if (file.isFile()) {
filenm=file.getName().toUpperCase();
if (filenm.contains(".TXT")){
filenm.toLowerCase();
break;
}
}
}
File personalfile = new File(gvnfold+"/"+filenm);
String name=""; String parent=""; String age=""; String number="";
String school=""; String address=""; String email=""; String line;
int linecount=0; Scanner sc = null;
//----------------------------------------------------------------------saves name etc from txt in respective strings
try {
sc = new Scanner(personalfile);
while (sc.hasNextLine()) {
linecount+=1;
line = sc.nextLine();
if(linecount==4) {name=line;} //--name
if(linecount==6) {parent=line;} //--parent
if(linecount==8) {age=line;} //--parent
if(linecount==10) {number=line;} //--number
if(linecount==14) {school=line;} //--school
if(linecount==16) {address=line;} //--address
if(linecount==18) {email=line;} //--email
}
FileInputStream InputStream=new FileInputStream("src/main/java/test.xlsx");
XSSFWorkbook workbook=new XSSFWorkbook(InputStream);
//-------------------------------------------------------------------------------sends name etc string to its rest excel cells
XSSFSheet sheet1=workbook.getSheetAt(0); //1st sheet
XSSFCell cellname=sheet1.getRow(1).getCell(1); //name
cellname.setCellValue(name);
XSSFCell cellparent=sheet1.getRow(2).getCell(1);//parent
cellparent.setCellValue(parent);
XSSFCell cellage=sheet1.getRow(3).getCell(1); //age
cellage.setCellValue(age);
XSSFCell cellnum=sheet1.getRow(4).getCell(1); //number
cellnum.setCellValue(number);
XSSFCell cellschool=sheet1.getRow(6).getCell(1);//school
cellschool.setCellValue(school);
XSSFCell celladd=sheet1.getRow(7).getCell(1); //address
celladd.setCellValue(address);
XSSFCell celle=sheet1.getRow(8).getCell(1); //email
celle.setCellValue(email);
//---------------------------------------------------clearing and closing inputstream and writing the changes
InputStream.close();
FileOutputStream outputStream = new FileOutputStream("src/main/java/test.xlsx");
workbook.write(outputStream);
workbook.close();
outputStream.close();
}
catch(FileNotFoundException e)
{e.printStackTrace();}
finally {if (sc != null) sc.close();}
}
//--------------------------------------------------------------actionListner overrides
@Override
public void actionPerformed(ActionEvent e) {
JFileChooser fileChoose=new JFileChooser();
fileChoose.setCurrentDirectory(new File("D:\\colz\\java"));
fileChoose.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
int resp =fileChoose.showOpenDialog(null);
if(resp==JFileChooser.APPROVE_OPTION) {
File file=new File(fileChoose.getSelectedFile().getAbsolutePath());
try {imgSource(file);} catch (IOException e2) {e2.printStackTrace();}
try {foldFil(file);} catch (IOException e1) {e1.printStackTrace();}
//new ScanAnlyFrame().setVisible(true);
//new turtleWork(file);
frame.dispose();
}
}
}
the following is the error. and its annoyingly long too.
Exception in thread "AWT-EventQueue-0" java.lang.NullPointerException: Cannot invoke "org.apache.poi.xssf.usermodel.XSSFRow.getCell(int)" because the return value of "org.apache.poi.xssf.usermodel.XSSFSheet.getRow(int)" is null
at BringFolderFrame.imgSource(BringFolderFrame.java:97)
at BringFolderFrame.actionPerformed(BringFolderFrame.java:185)
at java.desktop/javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1972)
at java.desktop/javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2313)
at java.desktop/javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:405)
at java.desktop/javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:262)
at java.desktop/javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:279)
at java.desktop/java.awt.Component.processMouseEvent(Component.java:6626)
at java.desktop/javax.swing.JComponent.processMouseEvent(JComponent.java:3389)
at java.desktop/java.awt.Component.processEvent(Component.java:6391)
at java.desktop/java.awt.Container.processEvent(Container.java:2266)
at java.desktop/java.awt.Component.dispatchEventImpl(Component.java:5001)
at java.desktop/java.awt.Container.dispatchEventImpl(Container.java:2324)
at java.desktop/java.awt.Component.dispatchEvent(Component.java:4833)
at java.desktop/java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4948)
at java.desktop/java.awt.LightweightDispatcher.processMouseEvent(Container.java:4575)
at java.desktop/java.awt.LightweightDispatcher.dispatchEvent(Container.java:4516)
at java.desktop/java.awt.Container.dispatchEventImpl(Container.java:2310)
at java.desktop/java.awt.Window.dispatchEventImpl(Window.java:2780)
at java.desktop/java.awt.Component.dispatchEvent(Component.java:4833)
at java.desktop/java.awt.EventQueue.dispatchEventImpl(EventQueue.java:773)
at java.desktop/java.awt.EventQueue$4.run(EventQueue.java:722)
at java.desktop/java.awt.EventQueue$4.run(EventQueue.java:716)
at java.base/java.security.AccessController.doPrivileged(AccessController.java:399)
at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:86)
at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:97)
at java.desktop/java.awt.EventQueue$5.run(EventQueue.java:746)
at java.desktop/java.awt.EventQueue$5.run(EventQueue.java:744)
at java.base/java.security.AccessController.doPrivileged(AccessController.java:399)
at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:86)
at java.desktop/java.awt.EventQueue.dispatchEvent(EventQueue.java:743)
at java.desktop/java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:203)
at java.desktop/java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:124)
at java.desktop/java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:113)
at java.desktop/java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:109)
at java.desktop/java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
at java.desktop/java.awt.EventDispatchThread.run(EventDispatchThread.java:90)
Solution
If the Exception is:
... java.lang.NullPointerException: Cannot invoke "org.apache.poi.xssf.usermodel.XSSFRow.getCell(int)" because the return value of "org.apache.poi.xssf.usermodel.XSSFSheet.getRow(int)" is null
at BringFolderFrame.imgSource(BringFolderFrame.java:97)
and the code line 97 in BringFolderFrame.java is
case "2_5_3.jpg":XSSFCell r5r=sheet2.getRow(30).getCell(1); r5r.setCellValue(gvnfold+"\\"+filenm); break;
then sheet2.getRow(30) returns null. That can be if that row is not stored in the Excel sheet. Then sheet2.getRow(30).getCell(1) throws that NullPointerException because, of course, null.getCell(1) cannot work.
In Excel sheet storage only used rows are stored. Unused rows, which are totally empty, are not stored.
Thats why, as documented, Sheet.getRow might return null for rows which are not stored.
The same is for cells in rows. There also only used cells are stored and Row.getCell might return null if that cell is not stored.
So writing code like
...
case "1_1_1.jpg":XSSFCell l1l=sheet2.getRow(1).getCell(1); l1l.setCellValue(gvnfold+"\\"+filenm); break;
...
case "2_5_3.jpg":XSSFCell r5r=sheet2.getRow(30).getCell(1); r5r.setCellValue(gvnfold+"\\"+filenm); break;
...
is always error prone.
Always null checks are needed. So something like
...
XSSFRow row = null;
XSSFCell cell= null;
...
case "1_1_1.jpg":
row = sheet2.getRow(1); if (row == null) row = sheet2.createRow(1);
cell = row.getCell(1); if (cell == null) cell = row.createCell(1);
cell.setCellValue(gvnfold+"\\"+filenm);
break;
...
case "2_5_3.jpg":
row = sheet2.getRow(30); if (row == null) row = sheet2.createRow(30);
cell = row.getCell(1); if (cell == null) cell = row.createCell(1);
cell.setCellValue(gvnfold+"\\"+filenm);
break;
...
Answered By - Axel Richter
Answer Checked By - Marie Seifert (JavaFixing Admin)
