Results 1 to 4 of 4
  1. #1
    Daedalus357 is offline Member
    Join Date
    Feb 2011
    Posts
    3
    Rep Power
    0

    Angry Excel - (workbook.write();) in IF statement will not work.

    So I haven't done Java since high-school (nearly 8 years now). I was asked to create a java program that would replace the current MS EXCEL macros that we have..

    I have done pretty well with getting back into the groove of programming in Java, however is seem to have run into a slight snag.

    **EDIT** I forgot to mention that i am using the JExcel API + Eclipse. I have JRE6, JDK1.6.0_23, and JExcelAPI_2_6_12.

    first off, let me explain what i'm trying to do right now:

    all the current program is trying to do is check if there is a file that exists and prompt the user to overwrite. if the user clicks yes, the overwrite flag (w) will be set to true, and should stay at false if the user selects no.

    based on this flag (if statement), the program will delete the previous file (located in "./Output/output.xls"), and will create a copy of another xls file, populate a field (1,5) with random data, and then will write and close the program.

    my issue is that the program is NOT writing the data! at least not after i put the write command (workbook.write();) into the flag's if statement near the end. the thing is, i don't want the program to write the file if the flag isnt set, so i want the write command to ONLY run if the w flag is set to true...

    can anyone help me solve this? below are the codes. please note that i do have some currently unused methods in here that are not commented out. i'm pretty sure these are not the issue and will be using them once this problem has been solved

    MAIN METHOD
    Java Code:
    import javax.swing.*;
    
    public class MainApp {
    	public static void main(String[] args) {
    		
    		Gui start = new Gui();
    		start.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    		start.setSize(400,400);
    		start.setVisible(true);
    		
    	}
    
    }
    Gui CLASS (using 'PHP CODE' for color)
    PHP Code:
    import java.awt.*;
    import java.awt.event.*;
    import java.io.*;
    import java.util.Date; 
    import java.beans.*;
    import javax.swing.*;
    import jxl.*;
    import jxl.write.*;
    import jxl.write.Label;
    import jxl.write.biff.RowsExceededException;
    import jxl.Cell;
    import jxl.Sheet;
    import jxl.Workbook;
    import jxl.read.biff.BiffException;
    
    public class Gui extends JFrame{
    
    	private JList input;
    	private String foutput = "Output/output.xls";
    	File f = new File(foutput);
    	private JButton Proc1;
    	private JButton Proc2;
    	private JLabel fexist;
    	
    	public Gui(){
    		super("Operation Complete");
    		setLayout(new FlowLayout());
    		
    		fexist = new JLabel("");
    		Proc1 = new JButton("Process 1");
    		add(fexist);
    		add(Proc1);
    		
    		HandlerClass handler = new HandlerClass();
    		Proc1.addActionListener(handler);
    	}
    	
    	private class HandlerClass implements ActionListener{
    		
    		private Workbook qbbook = null;
    		private Workbook sibook = null;
    		private WritableWorkbook workbook = null;
    		private	Sheet sisheet;
    		private Sheet qbsheet;
    		private WritableSheet sheet0;
    		private boolean e = false; //exist
    		private boolean w = false; //writable
    		private int totrows = 0;
    		private int totcols = 0;
    		private int count = 0;
    		private Label cexist;
    		
    		public void actionPerformed(ActionEvent event){
    			
    			try {
    				qbbook = Workbook.getWorkbook(new File("QuickBooks_Invoice_Table.xls"));
    			} catch (BiffException e1) {
    				e1.printStackTrace();
    			} catch (IOException e1) {
    				e1.printStackTrace();
    			} 
    			try {
    				sibook = Workbook.getWorkbook(new File("SalesInvoiceTemplate.xls"));
    			} catch (BiffException e1) {
    				e1.printStackTrace();
    			} catch (IOException e1) {
    				e1.printStackTrace();
    			} 
    			
    			if(f.exists()){
    				int n = JOptionPane.showConfirmDialog(
    					    rootPane,
    					    "File " + f + " exists, Overwrite?",
    					    "Confirm Overwrite",
    					    JOptionPane.YES_NO_OPTION);
    				e = true;
    				
    				if(n == JOptionPane.YES_OPTION){
    					w = true;
    					f.delete(); //remove old file
    					try {
    						workbook = Workbook.createWorkbook(new File(foutput), sibook);
    					} catch (IOException e) {
    						e.printStackTrace();
    					}
    					JOptionPane.showMessageDialog(rootPane, "Deleted old file and created new.\nProcess has completed.", "Complete", NORMAL);
    				}
    				else if(n == JOptionPane.NO_OPTION){
    					w = false;
    					JOptionPane.showMessageDialog(rootPane, "Operation Cancelled.", "Cancelled", NORMAL);
    				}
    			}else{
    				w = true;
    				JOptionPane.showMessageDialog(rootPane, "File does not exist - continuing.", "Continuing", NORMAL);
    				try {
    					workbook = Workbook.createWorkbook(new File(foutput), sibook);
    				} catch (IOException e) {
    					e.printStackTrace();
    				}
    				JOptionPane.showMessageDialog(rootPane, "Process has completed.", "Complete", NORMAL);
    			}
    			
    			qbsheet = qbbook.getSheet(0);
    			sisheet = sibook.getSheet(0); 
    			
    			if(!f.exists()){
    
    				sheet0 = workbook.getSheet(0);
    			}
    			
    			System.out.println(w);
    			
    			if(w == true){ //if writable flag is set do the following:
    				//Cell a1 = sheet0.getCell(1,1);
    				//String stringa1 = a1.getContents();
    				
    				cexist = new Label(0,5, "OMGWTF I DONT WORK!");
    				
    				try {
    					sheet0.addCell(cexist); //<--- Gui.java:120 (see error code)
    				} catch (RowsExceededException e1) {
    					e1.printStackTrace();
    				} catch (WriteException e1) {
    					e1.printStackTrace();
    				}
    				
    				try {
    					workbook.write();
    				} catch (IOException e) {
    					e.printStackTrace();
    				}
    			}//if
    				
    			
    			
    			try {
    				workbook.close();
    			} catch (WriteException e) {
    				e.printStackTrace();
    			} catch (IOException e) {
    				e.printStackTrace();
    			}
    			
    			
    		}//actionPerformed
    	}//actionListener
    	
    }//Gui Class
    The Error Message I keep getting...
    Quote Originally Posted by Eclipse Java EE IDE for Web Developers.
    Java Code:
    Exception in thread "AWT-EventQueue-0" java.lang.NullPointerException
    	at Gui$HandlerClass.actionPerformed(Gui.java:120)
    	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)true
    
    	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.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)
    Last edited by Daedalus357; 02-18-2011 at 10:58 PM. Reason: forgot to mention what i am using!

  2. #2
    quad64bit's Avatar
    quad64bit is offline Moderator
    Join Date
    Jul 2009
    Location
    VA
    Posts
    1,323
    Rep Power
    6

    Default

    You say
    Java Code:
    if(!f.exists()){
    
                    sheet0 = workbook.getSheet(0);
                }
    So if f does exist, then sheet0 stays null right? I would verify that sheet0 isn't null right before you attempt to work with it. If it is, trace it back to find out why its null.

  3. #3
    Daedalus357 is offline Member
    Join Date
    Feb 2011
    Posts
    3
    Rep Power
    0

    Default

    Quote Originally Posted by quad64bit View Post
    You say
    Java Code:
    if(!f.exists()){
    
                    sheet0 = workbook.getSheet(0);
                }
    So if f does exist, then sheet0 stays null right? I would verify that sheet0 isn't null right before you attempt to work with it. If it is, trace it back to find out why its null.
    .

    How would i go about testing whether sheet0 is null?

    like this?(sorry, not infront of compiler right now...):

    Java Code:
    if(sheet0 == null)
    [INDENT]system.out.println("Sheet0 is NULL!");[/INDENT]
    else
    [INDENT]system.out.println("Sheet0 is FULL!");[/INDENT]

  4. #4
    Daedalus357 is offline Member
    Join Date
    Feb 2011
    Posts
    3
    Rep Power
    0

    Default Got it working!

    So i managed to figure out why it wasn't writing properly...

    stupid me left out a critical part of the code!


    in the section:
    Java Code:
    if(w == true){ //if writable flag is set do the following:
    				//Cell a1 = sheet0.getCell(1,1);
    				//String stringa1 = a1.getContents();
    				
    				try {
    					sheet0.addCell(cexist);
    				} catch (RowsExceededException e) {
    					e.printStackTrace();
    				} catch (WriteException e) {
    					e.printStackTrace();
    				}

    I forgot to add:
    Java Code:
    Label cexist = new Label(0, 2, "A label record");

    to make it:
    Java Code:
    if(w == true){ //if writable flag is set do the following:
    				//Cell a1 = sheet0.getCell(1,1);
    				//String stringa1 = a1.getContents();
    				
    				Label cexist = new Label(0, 2, "A label record"); 
    				
    				try {
    					sheet0.addCell(cexist);
    				} catch (RowsExceededException e) {
    					e.printStackTrace();
    				} catch (WriteException e) {
    					e.printStackTrace();
    				}

    its amazing that such a small and seemingly insignificant part of the code can make such a difference!
    Last edited by Daedalus357; 02-23-2011 at 07:31 PM.

Similar Threads

  1. Repaint calling Statement Doesn't Work in my code ?
    By nitin_daviet88 in forum CLDC and MIDP
    Replies: 2
    Last Post: 07-24-2010, 03:09 PM
  2. Open an Excel workbook
    By ch7787 in forum New To Java
    Replies: 5
    Last Post: 07-20-2010, 05:20 PM
  3. SQL statement doesn't work....
    By pbaudru in forum New To Java
    Replies: 4
    Last Post: 04-08-2010, 09:51 PM
  4. How to Write this if statement?
    By PeterFeng in forum New To Java
    Replies: 1
    Last Post: 01-14-2009, 07:11 AM
  5. Replies: 0
    Last Post: 03-11-2008, 02:20 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •