CODEWITHSUNDEEP
javaeclipseexcelvbaapache-poi
Carol.Kar
Carol.Kar

Reputation: 5355

Write rows in a new excel sheet

I have an excel sheet which looks like that:

enter image description here

As you can see in the sheet there is more than one value foe for Name.

I want to create for every unique Name element a new excel sheet which looks like that:

enter image description here

enter image description here

I am using apache poi and I can read the excel sheet and I saved the distinct values of the colume in an arraylist. However, I am struggeling with the algorithm to save each unique name elements row in a new sheet.

I would appreciate your answer!!!

UPDATE

Tried to implement it like that:

for (Row r : sheet) {
            Cell c = r.getCell(4);
            c.setCellType(Cell.CELL_TYPE_STRING);
//          System.out.println(c.getStringCellValue().toString());
            if (c.getStringCellValue().equals(nameList.contains(c.getRowIndex()))) {
                System.out.println(sheet.getRow(r.getRowNum()));
                System.out.println("++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++");

            }
        }

However, it does not work...

Upvotes: 0

Views: 260

Answers (1)

Rahul
Rahul

Reputation: 45070

You can read all the rows one by one and create a Map which can be populated where the key is the firstname and the value will be a list of objects(an object contains the details read from each row in the excel).

Once done, for each entry in the Map, create a new sheet and write the list of objects corresponding to the key in the map in the respective sheet.

I don't have everything setup to show an example in POI, but since you're using that, I can show you where, what could be done.

class SomeObject { // This class contains all the details a row in an excel can contain}

Map<String, List<SomeObject>> myMap = new HashMap<String, List<SomeObject>>(); // Create your hashmap

For every row read from the excel using POI {
    1. Create a new SomeObject with the data read from the row
        a. if the firstname value already exists in myMap, then add this new object to the already existing list for that key
        b. if not present, create a new arraylist, add this element and put a new entry in map with firstname and list
    2. continue this, till there are more rows to be read.
}

Once this is done and the map is fully populated,

For every Entry in the map {
    1. Create a new sheet
    2.  Write all the objects present as the value for this Entry, to the sheet.
    3. Close the sheet
    4. Continue till there are more entries in the map
}

I could not elaborate the algorithm more than this. It should help you get your solution(you need to handle the coding part).

Upvotes: 1

Related Questions