I need to write a program that reads a file (from excel) using Java

[Izzywizz]

The program needs to read an excel file that contains a list of data (a list of students, thier module mark, student ID) The data needs to be presented in the following format:
User ID : Name : Module ID : Mark

The program also needs to have a menu system like

A. Read Marks file (I have no idea how to do that)
B. Sort - (I sort of know how to this)
C. Print student marks (no idea)
D. Print average mark for a module (that shouldn't be a problem, calculating the mean)
E. Print the highest and lowest mark for a module (I dont quite get how to acomplish that)
F. Quit -(I know how to do this)

Creating a menu system is simple enough, I am going make a switch statement and call up methods for each menu item. I am going to be using JCreator Pro for making this program. My main question is how do you get java to read an excel file which contains the data, the user needs to be promted to enter a file path to load the marks file, How would you do this? I understand it will be with the use of arrays I think. Also how would I get the program to prompt the user for a student ID , then print the given students marks for all modules which is part C of my menu. For Part B of my menu I need to sort the data into ascending based on userID then write the data to a new file
(sorted_marks.txt) in your program directory. I am a little bit confused with this as well.

I would be greatful if someone could give me some help with this, at the moment all I have is the menu printing out on the screen.

[cale.macdonald]

I would recommend you look at Java Excel API (on sourceforge). It will help you with reading the information from the excel sheet.

Once you have that it is just a matter taking the information and displaying it as needed.

Once you are at this stage please post you code and I will be happy to help.

[Izzywizz]

awww thank you, I will get some reading done tonight on that website n have play with my code and hopefully I will have the code tomorrow to look at, thanks in advance

[Izzywizz]

I am so dopy I just rechecked what I had to do for my coursework and talked to a friend about it and it turns out that the file with all the data in it. Is a comma seperated values file and I need to use fileinoutstream in order use, which makes likfe so much easier. I post up what i have done soon

[Izzywizz]

/**
 * 
 *
 *
 * 
 * 
 */
import java.io.*;

public class csv {

     public static void main( String[] args ) {
	
    try{
	
	int n;

   	FileInputStream fis = new FileInputStream("data1.csv");	
  	 while ( ( n = fis.available() ) > 0 ) {
     	   byte[] b = new byte[ n ];
	   int result = fis.read( b );
	
     	   if ( result != -1 ){
		String s = new String( b );
		System.out.print( s );
	   }		
       } // end while	
    } catch ( IOException e ) { 
  		System.err.println( "Error: " + e.getMessage() ); }
 } // end main

}

Well I got it to read the file but it doesnt prompt the user for the file path, as it is supposed to be entered by the user where the file is located, rather then assuming the data is already in the same location of the program. I have created a menu by using a switch statements and made this into a method but im stucck on the prompting for the file path, any clues?

[JamesCherrill]

... im stucck on the prompting for the file path, any clues?

http://java.sun.com/docs/books/tutorial/uiswing/components/filechooser.html

[cale.macdonald]

Look at using JFileChooser to allow the user to select the file. You will also need to apply a filter to it (only csv) files will be shown, otherwise provide some type of error handling if they specify another file (for example .doc).

[Izzywizz]

I spoke with a friend of mine and he says I was over complicating it again, all I have to do is maker a userinput.readstring and then relate that to the file name, as the data file will be located in the same folder after all

[Izzywizz]

/**
 * Coursework 2
 *
 *
 * 
 * version 1.00 2009/12/11
 */
import java.io.*;
public class ExcelProg {
	
		/**
	 * Method to prompt the user for a file name
	 * it will then send the output line by line to process method 
	 * 
	 */

	static void readFile(){
		String filename;
		String str;

		System.out.print(" Please Enter file name:");
		filename = UserInput.readString();
		
		BufferedReader inputStream;
		try {
			inputStream = new BufferedReader(
					new FileReader(filename));

			while ((str = inputStream.readLine()) != null) {
				process(str);
			}
			inputStream.close();
		} catch (IOException e) {
			System.out.println(" Error Reading file. " + e.getMessage());
			e.printStackTrace();
		} 
	}
	
	
	/**
	 * process Method which takes a String 
	 * Uses a split method to divide the given string into words 
	 * using space " " as the separator
	 * 
	 * @param  str  a String consist of words separated by spaces
	 */
	
	static void process(String str) {
		String[] sar = str.split(" ");
		printArray(sar);
		
	}

	/**
	 * Takes an Array of Strings and print all its elements
	 * 
	 * @param  str  which is a String[] to be printed
	 */
	
	static void printArray(String[] sar) {
		
		for(int i=0;i<sar.length;i++){
			System.out.println(sar[i]);
		}
		System.out.println();	
	}

		static char Choice;
				
 		public static void main(String[] args){
 		
 			System.out.println ("====================================================="+ "\n");
 			System.out.println ("Student Marks Report Creator" + "\n");
 			System.out.println ("====================================================="+ "\n");
 			System.out.println ("---Menu---"+ "\n");
 			System.out.println ("A. Read Marks file: ");
 			System.out.println ("B. Sort: ");
 			System.out.println ("C. Print Student Marks: ");
 			System.out.println ("D. Print average mark for a module: ");
 			System.out.println ("E. Print the highest and lowest mark for a module: ");
 			System.out.println ("F. Quit: " + "\n");	
 			System.out.print ("Enter Choice: ");				
			
			Choice = UserInput.readChar();
 			switch (Choice) {

			case 'a':
			case 'A':readFile();	
			break; 
				
			case 'b':
			case 'B': System.out.print ("Data has been sorted accordingly to userID and saved in sorted_marts.txt in your program directory");
			break; 

			case 'c':
			case 'C': System.out.print ("Please enter Student ID: ");	
			break;
			
			case 'd':
			case 'D': System.out.print ("Please enter Module ID: ");	
			break;
			
			case 'e':
			case 'E': System.out.print ("Please enter Module ID: ");	
			break;
			
			case 'f':
			case 'F': System.out.println("Thank you for using Student Marks Report Creator.");	
			System.exit(0);
			break;
					
			default:
			System.out.println("Error please only choose from the menu choice A-E \n\n");
			break;				
		
		}
 	}
 }

Ok this is what I have don so far, Part A of the menu is done but im a little stuck on part B, I know how to create a file but how do make the data file sort the data according to userId in ascending order then write that into a new file?

[cale.macdonald]

Create a class for holding all you information. Also make this class implement the comparable interface, and complete the compareTo method using the student id number. Before reading your information in create a TreeSet in which you will use your comparable that you created. When you read the information from the marks file load the required fields into the class you created. The TreeSet will look after the sorting for you.

Cheers