Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 3 of 3

Thread: JExcelAPI Error

  1. July 15th, 2011, 08:16 AM #1
    esplanade56
    esplanade56 is offline
    Junior Member
    Join Date
    May 2011
    Posts
    16
    My Mood
    Depressed
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default JExcelAPI Error

    Hi guys,

    I need some help here. My program is supposed to read a selected XLS file and it will run the keystroke (auto entering). However, it got the following error:

    Warning: Cannot read name ranges for OLE_LINK5 - setting to empty
    Warning: Usage of a local non-builtin name
    Warning: Usage of a local non-builtin name
    Warning: Usage of a local non-builtin name
    Warning: Usage of a local non-builtin name
    Warning: Usage of a local non-builtin name
    Warning: Usage of a local non-builtin name

    This happened when I enter the range like C30:C50?

    *NOTE*
    fyi: jxl.jar file is needed to run the program.
    My program can only (that is for now. I know there's improvement needed):
    - select range of the same column.
    - able to read till column F.
    - have to close the program and re-run it after doing it once (be it fail or pass).

    I really need this help. I know my coding is not very well done. But I've put in a lot of effort, yet I ended up being told that my program was useless, a failed program. :'( It's very demoralising to be said that way.


    Thanks guys! Really appreciate all helps given!


    import java.awt.*;
import javax.swing.*;
 
import java.awt.event.*;
import java.io.IOException;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
 
import javax.swing.JFileChooser;
 
import java.io.File;
public class CustomiseKeystroke {
 
		static String key;
		static String fullFileName;
		static JTextField jtfFile, jtfCell;
	    private Robot robot;
	    static char colNumber;
 
 
 
		static String columnBegin="";
		static String rowBegin="";
 
		static String columnEnd="";
		static String rowEnd ="";
 
 
 
		static	int columnNo;
		static int rowNo = 0;
 
    public static void main(String... args) throws Exception {
 
       JFrame jf = new JFrame("Auto Writer");
 
       JPanel p = new JPanel();
       JLabel lblTitle = new JLabel("Auto Marks Entering Application");
		p.add(lblTitle);
 
		JPanel p1 = new JPanel();
 
			JPanel subp1 = new JPanel();
 
				JPanel sub2p1 = new JPanel();
						sub2p1.setLayout(new BorderLayout());
 
							JLabel lblFile = new JLabel("File: ");
							final JTextField jtfFile = new JTextField(20);
							JButton jbtnBrowse = new JButton("Browse");
							jbtnBrowse.addActionListener(new ActionListener() {
					        public void actionPerformed(ActionEvent e)   {
 
				      		 JFileChooser chooser = new JFileChooser();
					            chooser.showOpenDialog(null);
					            File file = chooser.getSelectedFile();
					            String fileName = file.getName();
					            String strDirectory = file.getParent();
					            String con = "\\";
					            fullFileName = strDirectory + con + fileName;
					            jtfFile.setText(fullFileName);
					           	System.out.println(fullFileName);
				    		  }});
						sub2p1.add(lblFile, BorderLayout.WEST);
						sub2p1.add(jtfFile, BorderLayout.CENTER);
						sub2p1.add(jbtnBrowse, BorderLayout.EAST);
				subp1.add(sub2p1, BorderLayout.NORTH);
 
				JPanel subp2 = new JPanel();
					JPanel sub2p2 = new JPanel();
						JLabel lblType = new JLabel("Move between Cells: ");
 
CheckboxGroup radio = new CheckboxGroup();
 
final Checkbox	enterButton = new Checkbox("Enter", radio, true);
final Checkbox	tabButton = new Checkbox("Tab", radio, false);
 
						sub2p2.add(lblType, BorderLayout.WEST);
						sub2p2.add(enterButton, BorderLayout.EAST);
						sub2p2.add(tabButton, BorderLayout.EAST);
				subp2.add(sub2p2,BorderLayout.SOUTH);
 
				JPanel subp3 = new JPanel();
					JPanel sub2p3 = new JPanel();
						JLabel lblCell = new JLabel("Cell: ");
						final JTextField jtfCell = new JTextField(10);
						sub2p3.add(lblCell, BorderLayout.WEST);
						sub2p3.add(jtfCell, BorderLayout.EAST);
				subp3.add(sub2p3,BorderLayout.SOUTH);
 
 
		p1.add(sub2p1, BorderLayout.NORTH);
		p1.add(sub2p2, BorderLayout.CENTER);
		p1.add(sub2p3, BorderLayout.SOUTH);
 
		JPanel p2 = new JPanel();
        JButton jb = new JButton("Start");
 
        jb.addActionListener(new ActionListener() {
      public void actionPerformed(ActionEvent e)   {
    	  		String filepath = jtfFile.getText();
    	  		String range = jtfCell.getText();
 
				System.out.println(range);
		       int posColon= range.indexOf(":");
 
         String begin = range.substring(0, posColon);
   		String end = range.substring(posColon+1, range.length() ) ;
    //    System.out.println(begin+"   "+end);
 
 
 		 for (int i=0; i<begin.length(); i++)
 		  isNumber(""+begin.substring(i,i+1) )  ;
         System.out.println("col is "+columnBegin + "\t row is " + rowBegin );
 
         for (int i=0; i<end.length(); i++)
 		 isNumber1(""+end.substring(i,i+1) ) ;
 
		 int rowNo1 = Integer.parseInt(rowBegin) - 1;
 
 		 int rowNo = Integer.parseInt(rowEnd) ;
 
         System.out.println("col is "+columnEnd + "\t row is " + rowNo );
 
 
 
 
		Character c = new Character(columnBegin.charAt(0));
		Character c1 = new Character(columnEnd.charAt(0));
 
		  colNumber(c);
		  colNumber(c1);
 
    try
    {
    	Thread.sleep(1000);
    	CustomiseKeystroke keyboard = new CustomiseKeystroke();
 
        Workbook workbook = Workbook.getWorkbook(new File(filepath));
        Sheet sheet = workbook.getSheet(0);
 
  		 for (int x=rowNo1; x<rowNo; x++) {
  		    Cell excel1 = sheet.getCell(columnNo, x);
 
  		     String data1 = excel1.getContents();
  		     System.out.println(data1);
  		     keyboard.type(data1);
 
 				if (tabButton.getState() == true) {
			           	     keyboard.type("\t");
			           	System.out.println("tab");
			        } else{
			           keyboard.type("\n");
			          	System.out.println("enter");
			        }
 
  		     //keyboard.type(key);
  		 }
		  keyboard.type("\n");
          }
    catch (Exception ex) {}
 
      }
    });
 
      	p2.add(jb);
		jf.add(p, BorderLayout.NORTH);
		jf.add(p1, BorderLayout.CENTER);
        jf.add(p2, BorderLayout.SOUTH);
        jf.setSize(500,180);
        jf.setVisible(true);
        jf.setDefaultCloseOperation(WindowConstants.DISPOSE_ON_CLOSE);
    }
 
		static boolean isNumber(String c)
	{
    	try
    	{
    	  	Integer.parseInt(c);
    	  	 rowBegin +=c;
    		return true;
    	}
    	catch (Exception e)
    	{
    			   columnBegin +=c;
    		  		return false;
    	}
    }
 
 
    		static boolean isNumber1(String c1)
	{
    	try
    	{
    	  	Integer.parseInt(c1);
    	  	 rowEnd +=c1;
    		return true;
    	}
    	catch (Exception e)
    	{
    			   columnEnd +=c1;
    		  		return false;
    	}
    }
 
	static void colNumber (char character)
	{
	//	int columnNo = 0;
 
	switch (character)
		{
			case 'A': columnNo =0; break;
			case 'B': columnNo = 1; break;
			case 'C': columnNo = 2; break;
			case 'D': columnNo = 3; break;
			case 'E': columnNo = 4; break;
			case 'F': columnNo = 5; break;
		}
	}
 
   public CustomiseKeystroke() throws AWTException {
        this.robot = new Robot();
    }
 
    public CustomiseKeystroke(Robot robot) {
        this.robot = robot;
    }
 
    public void type(CharSequence characters) {
        int length = characters.length();
        for (int i = 0; i < length; i++) {
                char character = characters.charAt(i);
                type(character);
        }
    }
 
 
 
   public void type(char character) {
        switch (character) {
        case 'a': doType(KeyEvent.VK_A); break;
        case 'b': doType(KeyEvent.VK_B); break;
        case 'c': doType(KeyEvent.VK_C); break;
        case 'd': doType(KeyEvent.VK_D); break;
        case 'e': doType(KeyEvent.VK_E); break;
        case 'f': doType(KeyEvent.VK_F); break;
        case 'g': doType(KeyEvent.VK_G); break;
        case 'h': doType(KeyEvent.VK_H); break;
        case 'i': doType(KeyEvent.VK_I); break;
        case 'j': doType(KeyEvent.VK_J); break;
        case 'k': doType(KeyEvent.VK_K); break;
        case 'l': doType(KeyEvent.VK_L); break;
        case 'm': doType(KeyEvent.VK_M); break;
        case 'n': doType(KeyEvent.VK_N); break;
        case 'o': doType(KeyEvent.VK_O); break;
        case 'p': doType(KeyEvent.VK_P); break;
        case 'q': doType(KeyEvent.VK_Q); break;
        case 'r': doType(KeyEvent.VK_R); break;
        case 's': doType(KeyEvent.VK_S); break;
        case 't': doType(KeyEvent.VK_T); break;
        case 'u': doType(KeyEvent.VK_U); break;
        case 'v': doType(KeyEvent.VK_V); break;
        case 'w': doType(KeyEvent.VK_W); break;
        case 'x': doType(KeyEvent.VK_X); break;
        case 'y': doType(KeyEvent.VK_Y); break;
        case 'z': doType(KeyEvent.VK_Z); break;
        case 'A': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_A); break;
        case 'B': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_B); break;
        case 'C': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_C); break;
        case 'D': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_D); break;
        case 'E': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_E); break;
        case 'F': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_F); break;
        case 'G': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_G); break;
        case 'H': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_H); break;
        case 'I': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_I); break;
        case 'J': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_J); break;
        case 'K': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_K); break;
        case 'L': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_L); break;
        case 'M': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_M); break;
        case 'N': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_N); break;
        case 'O': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_O); break;
        case 'P': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_P); break;
        case 'Q': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_Q); break;
        case 'R': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_R); break;
        case 'S': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_S); break;
        case 'T': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_T); break;
        case 'U': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_U); break;
        case 'V': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_V); break;
        case 'W': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_W); break;
        case 'X': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_X); break;
        case 'Y': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_Y); break;
        case 'Z': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_Z); break;
        case '`': doType(KeyEvent.VK_BACK_QUOTE); break;
        case '0': doType(KeyEvent.VK_0); break;
        case '1': doType(KeyEvent.VK_1); break;
        case '2': doType(KeyEvent.VK_2); break;
        case '3': doType(KeyEvent.VK_3); break;
        case '4': doType(KeyEvent.VK_4); break;
        case '5': doType(KeyEvent.VK_5); break;
        case '6': doType(KeyEvent.VK_6); break;
        case '7': doType(KeyEvent.VK_7); break;
        case '8': doType(KeyEvent.VK_8); break;
        case '9': doType(KeyEvent.VK_9); break;
        case '-': doType(KeyEvent.VK_MINUS); break;
        case '=': doType(KeyEvent.VK_EQUALS); break;
        case '~': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_BACK_QUOTE); break;
        case '!': doType(KeyEvent.VK_EXCLAMATION_MARK); break;
        case '@': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_2); break;
 
        //case '@': doType(KeyEvent.VK_AT); break;
        case '#': doType(KeyEvent.VK_NUMBER_SIGN); break;
        case '$': doType(KeyEvent.VK_DOLLAR); break;
        case '%': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_5); break;
        case '^': doType(KeyEvent.VK_CIRCUMFLEX); break;
        case '&': doType(KeyEvent.VK_AMPERSAND); break;
        case '*': doType(KeyEvent.VK_ASTERISK); break;
        case '(': doType(KeyEvent.VK_LEFT_PARENTHESIS); break;
        case ')': doType(KeyEvent.VK_RIGHT_PARENTHESIS); break;
        case '_': doType(KeyEvent.VK_UNDERSCORE); break;
        case '+': doType(KeyEvent.VK_PLUS); break;
        case '\t': doType(KeyEvent.VK_TAB); break;
        case '\n': doType(KeyEvent.VK_ENTER); break;
        case '[': doType(KeyEvent.VK_OPEN_BRACKET); break;
        case ']': doType(KeyEvent.VK_CLOSE_BRACKET); break;
        case '\\': doType(KeyEvent.VK_BACK_SLASH); break;
        case '{': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_OPEN_BRACKET); break;
        case '}': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_CLOSE_BRACKET); break;
        case '|': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_BACK_SLASH); break;
        case ';': doType(KeyEvent.VK_SEMICOLON); break;
        case ':': doType(KeyEvent.VK_COLON); break;
        case '\'': doType(KeyEvent.VK_QUOTE); break;
        case '"': doType(KeyEvent.VK_QUOTEDBL); break;
        case ',': doType(KeyEvent.VK_COMMA); break;
        case '<': doType(KeyEvent.VK_LESS); break;
        case '.': doType(KeyEvent.VK_PERIOD); break;
        case '>': doType(KeyEvent.VK_GREATER); break;
        case '/': doType(KeyEvent.VK_SLASH); break;
        case '?': doType(KeyEvent.VK_SHIFT, KeyEvent.VK_SLASH); break;
        case ' ': doType(KeyEvent.VK_SPACE); break;
 
        //Arrow Keys
 
 
 
        default:
                throw new IllegalArgumentException("Cannot type character " + character);
        }
    }
 
     public void typeArrow(String d) {
 
     	if (d.equals("Up")) doType(KeyEvent.VK_UP); else
     	if (d.equals("Down")) doType(KeyEvent.VK_DOWN); else
     	if (d.equals("Left")) doType(KeyEvent.VK_LEFT); else
     	if (d.equals("Right")) doType(KeyEvent.VK_RIGHT);
 
    }
    private void doType(int... keyCodes) {
        doType(keyCodes, 0, keyCodes.length);
    }
 
    private void doType(int[] keyCodes, int offset, int length) {
        if (length == 0) {
                return;
        }
        robot.keyPress(keyCodes[offset]);
        robot.delay(150);
        doType(keyCodes, offset + 1, length - 1);
        robot.keyRelease(keyCodes[offset]);
    }
 
}
    Reply With Quote Reply With Quote

  2. Default Related threads:

  3. July 17th, 2011, 08:38 PM #2
    esplanade56
    esplanade56 is offline
    Junior Member
    Join Date
    May 2011
    Posts
    16
    My Mood
    Depressed
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: JExcelAPI Error

    bumps

    Anyone can help?
    Reply With Quote Reply With Quote
  4. July 18th, 2011, 07:48 AM #3
    JavaPF
    JavaPF is offline
    mmm.. coffee JavaPF's Avatar
    Join Date
    May 2008
    Location
    United Kingdom
    Posts
    3,336
    My Mood
    Mellow
    Thanks
    258
    Thanked 294 Times in 227 Posts
    Blog Entries
    4

    Default Re: JExcelAPI Error

    Providing a link to the jxl.jar download would of been useful. Not many people are going to go off searching for this..

    As it's a non standard library, this also restricts the replies you will receive.
    Have you tried to Google the error?
    Please use [highlight=Java] code [/highlight] tags when posting your code.
    Forum Tip: Add to peoples reputation by clicking the button on their useful posts.
    Reply With Quote Reply With Quote
« calling a method from other object: thred | What's wrong with my code for remembering JFileChooser's Last Directory? »

Similar Threads

  1. JExcelAPI
    By esplanade56 in forum Java Theory & Questions
    Replies: 5
    Last Post: July 8th, 2011, 07:53 AM
  2. how to copy cells to a new sheet of excel with JexcelAPI??
    By 19world in forum File I/O & Other I/O Streams
    Replies: 0
    Last Post: June 15th, 2010, 03:49 AM