Excel Columns into Java Using API

Question

I had to create a program that calculates the GPA, using apache poi that reads the xlsx excel file. It coints 220 rows and 4 columns, such as

Course Number    Course Name    Credit Hours Course Multipler
110              Eng 1 CP        5.0          1.0

There are 220 other courses. However, I was able to print those data using cell.getStringCellValue and cell.getNumericCellValue, but I can't get these printed data into each array.

I wanted to create an array called courseNumList and put courseNumList[0] the first course Number, and the second course number in courseNumList[1].. on and on..

I want to create 4 arrays, but what is a good way?

            private static ArrayList<Object> c = new ArrayList <Object>();

            public static void readXLSXFile() throws IOException {

    InputStream ExcelFileToRead = new FileInputStream("C:/Users/14KimTa/Desktop/Downloads/Course_List.xlsx");

    XSSFWorkbook  wb = new XSSFWorkbook(ExcelFileToRead);

    XSSFWorkbook test = new XSSFWorkbook(); 

    XSSFSheet sheet = wb.getSheetAt(0);
    XSSFRow row; 
    XSSFCell cell;

    Iterator rows = sheet.rowIterator();

    while (rows.hasNext())
    {
        row=(XSSFRow) rows.next();
        Iterator cells = row.cellIterator();

        while (cells.hasNext())
        {
            cell=(XSSFCell) cells.next();

            if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING)
            {               
                System.out.print(cell.getStringCellValue()+" ");
                                    c.add(getStringCellValue());
            }

            else if(cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC)
            {
                System.out.print(cell.getNumericCellValue()+" ");
            }
        }           

    System.out.println();
    }

}

this is my code so far.

I tried to create each columns into arrays, but it is not working at all.

Thanks!

rgettman · Accepted Answer · 2013-11-20 20:35:27Z

2

I would create a new class to define your data, Course, with one field for each of your columns (4 fields). Then I would create some kind of List (ArrayList<Course> looks good) to hold all Courses. An array of Courses would work too, since you know how many there are from the start. In a loop, I would create one Course object for each row, setting the fields based on the values from cell.getStringCellValue() and cell.getNumericCellValue(), adding the Course to the List (or array) after processing each row.

edited Nov 20, 2013 at 20:35

answered Nov 20, 2013 at 20:23

rgettman

179k30 gold badges282 silver badges365 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user2760357 Over a year ago

Each row doesn't go into one array like I intend to, but all the data just simply goes into arrayList[0].. If i try arrayList[1], it would return an error

rgettman Over a year ago

Please show your code that creates your objects and inserts them into your ArrayList.

user2760357 Over a year ago

private static ArrayList<Object> c = new ArrayList <Object>(); this instantiates the array list of c and c.add(cell.getStringCellValue()) adds it

rgettman Over a year ago

Create your ArrayList before your first while loop. Create a Course object inside the first while loop. Don't have a second inner while loop, because you know what each of the 4 columns are. Get each of the 4 expected cells, and set the attributes in your Course from each of the 4 values, then add the Course to your ArrayList at the bottom of the while loop.

user2760357 Over a year ago

I have trouble following your lines... could you show me an example please? I really appreciate it

Andrei Nicusan · Accepted Answer · 2013-11-20 20:43:09Z

0

I don't think creating one array per each column is a good idea. Keeping track of data in the same row by following the same indexes in the 4 arrays may be cumbersome and bad practice.

I would rather create a Java class - Course - with 4 fields -courseNumber, courseName, creditHours and courseMultiplier. Then, I would create a collection of such objects, e.g. Collection<Course> courses = new ArrayList<Course>();, and populate it according to the data read from Excel - one object per row.

EDIT:

I'd suggest you create a custom type instead of using Object for your ArrayList type parameter. You're not gaining much by using Object.

Then, for each row, you'd do the following:

//...obtain your values from cells and populate `courseNumber`, `courseName`,`creditHours` and `courseMultiplier` accordingly
Course course = new Course();
course.setCourseNumber(courseNumber);
course.setCourseName(courseName);
course.setCreditHours(creditHours);
course.setCourseMultiplier(courseMultiplier);
c.add(course);

This snippet of code should be placed inside the loop you use for iterating through rows.

edited Nov 20, 2013 at 20:43

answered Nov 20, 2013 at 20:23

Andrei Nicusan

4,6191 gold badge27 silver badges37 bronze badges

5 Comments

user2760357 Over a year ago

I'm having trouble with putting one object per one row

user2760357 Over a year ago

How do I "obtain" the values? should I use add function?

Andrei Nicusan Over a year ago

e.g. double courseNumber = row.getCell(0).getNumericCellValue();

Andrei Nicusan Over a year ago

Forget about the inner while loop.

Andrei Nicusan Over a year ago

Try to use the POI API properly. Check the type of the cell beforehand. Or set the cell type accordingly.

Collectives™ on Stack Overflow

Excel Columns into Java Using API

2 Answers 2

5 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related