Apache POI - Extracting all different values in a

Question

I have an excel file that look like this -

Ticket #      Status      Person
1.            Open        John
2.            Working     Mark
3.            Pending     Betty
4.            Working     Mark
5.            Open        Mark
6.            Open        John
7.            Pending     Betty
8.            Working     John
9.            Working     Mark
10.           Pending     Betty

I want to extract following information

1. Names of all the different people (in column 3, in this case - John, Mark and Betty).

2. How many tickets for a person are a.Open b.Working c.Pending. (Eg - For John, 2 tickets are open, 1 is working and 0 is pending).

I am using Apache POI, please suggest, how I can get the above mentioned information.

Answer 1

Try to load Row by Row using this Class.

public class Person{
  private String name;
  private Map<TypeOfTicket,Integer> tickets;

  //Implements equals based on name property
}

public enum TypeOfTicket{
  Pending,
  Open,
  Working
}

Cell have a method to return the string, create an implementation to read Name and if you dont have in your list create new object, if you have add one to the count of the ticket type.

List<Person> persons = new ArrayList<Person>();
String personName =cell.getStringCellValue();
if(person.contains(new Person(personName){
        //Add logic here to obtain the person object 
        and read the type of the ticket and add one.
}

Add the end the person object should take care of the way you store the information but you need to do it obtaining values cell by cell.

To obtain the information of your question just.

persons.size();

and

person.get(new Person("personName")).getTickets().get(TypeOfTicket)

Answer 2

If you want all the unique members of a data structure, you use a TreeSet, which is wholly intended for that purpose, to wit:

A collection that contains no duplicate elements. The elements are ordered using their
natural ordering, or by a Comparator provided at set creation time, depending on   
which constructor is used.

Define your Comparator and hashCode method that differentiates on name -- the 3rd column and it will do what you'd like.