Read complex xlsx using Apache poi

Question

Hi i want to read the below complex xlsx file using apache poi.

InputStream inputStream = content.getInputStream();
        XSSFWorkbook wb = new XSSFWorkbook(inputStream);
        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()+" ");
                }
                else if(cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC)
                {
                    System.out.print(cell.getNumericCellValue()+" ");
                }
            }
        }

The above code is used to read the files.

How can i read the xlsx file and prepare object like

Guest Name = abc
Email = [email protected]

XLSX file

Answer 1

Step to follow:
1. Import All Apache POI and related Libraries
2. Use Below mentioned code, below class name is ReadFile.java

import java.io.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadFile {
    public static void main(String[] args) {
        File content = new File("/home/mohit/file.xlsx");
        try {
            FileInputStream stream = new FileInputStream(content);
            XSSFWorkbook wb = new XSSFWorkbook(stream);
            XSSFSheet sheet = wb.getSheetAt(0);
            XSSFRow row;
            XSSFCell cell;
            Iterator rows = sheet.rowIterator();
            int totalIteration = 0;
            String total = "";
            int currentIterationNumber= 0;

            Guest guest = new Guest();
            List<Guest> guestList = new ArrayList<>();
            List<Room> roomList = new ArrayList<>();

            while (rows.hasNext()) {
                totalIteration++;
                currentIterationNumber++;
                int cellIndicator= 0;
                row = (XSSFRow) rows.next();
                Iterator cells = row.cellIterator();
                Room room = new Room();

                // Each cell
                while (cells.hasNext()) {
                    cellIndicator++;
//                    System.out.print("\nCurrent CellNo :"+cellIndicator+"    ");
                    cell = (XSSFCell) cells.next();

                    // Get Name and Invoice Number
                    if (currentIterationNumber==1){
                        if (cellIndicator==2){
                            guest.setGuestName(cell.getStringCellValue());
                        }
                        if (cellIndicator == 4){
                            guest.setInvoiceNumber((int) cell.getNumericCellValue());
                        }
                    }
                    // Get ConfirmationNumber and EmailAddress
                    if (currentIterationNumber==2){
                        if (cellIndicator==2){
                            guest.setGuestEmail(cell.getStringCellValue());
                        }
                        if (cellIndicator == 4){
                            guest.setConfirmationNumber((int) cell.getNumericCellValue());
                        }
                    }
                    // Get Address and Arrival Dates
                    if (currentIterationNumber==3){
                        if (cellIndicator==2){
                            guest.setGuestAddress(cell.getStringCellValue());
                        }
                        if (cellIndicator == 4){
                            cell.setCellType(CellType.STRING);
                            guest.setArrivalDate(cell.getStringCellValue());
                        }
                    }
                    // Get and Append Address & Departure date
                    if (currentIterationNumber==4){
                        if (cellIndicator==2){
                            guest.setGuestAddress(guest.getGuestAddress()+", "+cell.getStringCellValue());
                        }
                        if (cellIndicator == 4){
                            cell.setCellType(CellType.STRING);
                            guest.setDepartureDate(cell.getStringCellValue());
                        }
                    }
                    // Get and Append Address & RoomType
                    if (currentIterationNumber==5){
                        if (cellIndicator==2){
                            cell.setCellType(CellType.STRING);
                            guest.setGuestAddress(guest.getGuestAddress()+", "+cell.getStringCellValue());
                        }
                        if (cellIndicator == 4){
                            cell.setCellType(CellType.STRING);
                            guest.setRoomType(cell.getStringCellValue());
                        }
                    }

                    // Get and Append Address & Reference
                    if (currentIterationNumber==6){
                        if (cellIndicator==2){
                            guest.setGuestAddress(guest.getGuestAddress()+", "+cell.getStringCellValue());
                        }
                        if (cellIndicator == 4){
                            cell.setCellType(CellType.STRING);
                            guest.setReference(cell.getStringCellValue());
                        }
                    }

                    // Get Stays Data and map to Room Class
                    if (currentIterationNumber>7) {
//                        System.out.print("Starting Stay..."+cellIndicator);
                        if (cellIndicator == 1) {
                            cell.setCellType(CellType.STRING);
//                            System.out.print("Stay Date:"+cell.getStringCellValue());
                            room.setStayDate(cell.getStringCellValue());
                        }

                        if (cellIndicator == 2) {
//                            System.out.print("   Item:"+cell.getStringCellValue());
                            room.setItem(cell.getStringCellValue());
                        }
                        if (cellIndicator == 3) {
//                            System.out.print("   Quantity:"+cell.getNumericCellValue());
                            room.setQuantity((int) cell.getNumericCellValue());
                        }
                        if (cellIndicator == 4) {
                            if (cell.getCellType() != XSSFCell.CELL_TYPE_STRING) {
//                                System.out.print("   Rate:"+cell.getNumericCellValue());
                                room.setRate((int) cell.getNumericCellValue());
                            }
                        }
                        if (cellIndicator == 5) {
//                            System.out.print("   Amount:"+cell.getNumericCellValue());
                            room.setAmount((int) cell.getNumericCellValue());
//                        System.out.print("\nStayDate: "+room.getStayDate()+"   Item: "+room.getItem()+"    Quantity:  "+room.getQuantity()
//                        +"  Rate: "+room.getRate()+"    Amount:"+room.getAmount()+" \n");
//
                            System.out.print("\nAdding Room to the list");
                            roomList.add(room);
                        }

                    }

                    // Check if Current Row is total row
                    if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                        total = cell.getStringCellValue();
                    }

                    // End Collecting for Guest here when you see "Total"
                    if (cellIndicator == 4 && total.equals("Total")){
                        guest.setRoomList(roomList);
                        guestList.add(guest);
                        System.out.print("\nSaving "+guest.getGuestName()+" details with count "+roomList.size());


                        // Perform you DB Operation here

                        // Reset Counter and RoomList
                        guest = new Guest();
                        currentIterationNumber=0;
                        roomList.clear();
                    }
                }

            }

            for (Guest guest1:guestList) {
                System.out.print("\n---- Collected Data: \n Name:"+guest1.getGuestName()+"   InvoiceNo: "+guest1.getInvoiceNumber()
                        +"  Email: "+guest1.getGuestEmail()+"    Confirmation No: "+guest1.getConfirmationNumber()+ "     Address: "+guest1.getGuestAddress()
                        +"   Arrival: "+guest1.getArrivalDate()+"    Dep:"+guest1.getDepartureDate()+"    RoomType: "+guest1.getRoomType()
                        +"      Ref:"+guest1.getReference()+"   Room Count: "+guest1.getRoomList().size());
            }




        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}

3.Here is your Guest.java Room Count: 0 import java.util.List;

public class Guest {
    private String guestName;
    private String guestEmail;
    private String guestAddress;
    private int invoiceNumber;
    private int confirmationNumber;
    private String arrivalDate;
    private String departureDate;
    private String roomType;
    private String reference;
    private int totalCharge;
    private List<Room> roomList;

    public List<Room> getRoomList() {
        return roomList;
    }

    public void setRoomList(List<Room> roomList) {
        this.roomList = roomList;
    }

    public String getGuestName() {
        return guestName;
    }

    public void setGuestName(String guestName) {
        this.guestName = guestName;
    }

    public String getGuestEmail() {
        return guestEmail;
    }

    public void setGuestEmail(String guestEmail) {
        this.guestEmail = guestEmail;
    }

    public String getGuestAddress() {
        return guestAddress;
    }

    public void setGuestAddress(String guestAddress) {
        this.guestAddress = guestAddress;
    }

    public int getInvoiceNumber() {
        return invoiceNumber;
    }

    public void setInvoiceNumber(int invoiceNumber) {
        this.invoiceNumber = invoiceNumber;
    }

    public int getConfirmationNumber() {
        return confirmationNumber;
    }

    public void setConfirmationNumber(int confirmationNumber) {
        this.confirmationNumber = confirmationNumber;
    }

    public String getArrivalDate() {
        return arrivalDate;
    }

    public void setArrivalDate(String arrivalDate) {
        this.arrivalDate = arrivalDate;
    }

    public String getDepartureDate() {
        return departureDate;
    }

    public void setDepartureDate(String departureDate) {
        this.departureDate = departureDate;
    }

    public String getRoomType() {
        return roomType;
    }

    public void setRoomType(String roomType) {
        this.roomType = roomType;
    }

    public String getReference() {
        return reference;
    }

    public void setReference(String reference) {
        this.reference = reference;
    }

    public int getTotalCharge() {
        return totalCharge;
    }

    public void setTotalCharge(int totalCharge) {
        this.totalCharge = totalCharge;
    }
}

Step:4 Here is your Room.java

public class Room {
    private String stayDate;
    private String item;
    private int quantity;
    private int rate;
    private int amount;

    public String getStayDate() {
        return stayDate;
    }

    public void setStayDate(String stayDate) {
        this.stayDate = stayDate;
    }

    public String getItem() {
        return item;
    }

    public void setItem(String item) {
        this.item = item;
    }

    public int getQuantity() {
        return quantity;
    }

    public void setQuantity(int quantity) {
        this.quantity = quantity;
    }

    public int getRate() {
        return rate;
    }

    public void setRate(int rate) {
        this.rate = rate;
    }

    public int getAmount() {
        return amount;
    }

    public void setAmount(int amount) {
        this.amount = amount;
    }
}

I can also share a sample project. Please accept the anwser if it works.
I've Tested it here.

My Console Output:

---- Collected Data:
Name:abc InvoiceNo: 0 Email: [email protected] Confirmation No: 123
Address: xxxx, xxxx, 119062, India Arrival: 42911 Dep:42916
RoomType: One Bed Room City View Ref:Management
---- Collected Data:
Name:xtf InvoiceNo: 0 Email: [email protected] Confirmation No: 456
Address: xxxx, xxxx, 119062, India Arrival: 42911 Dep:42916
RoomType: One Bed Room City View Ref:Management

Still you need to take care of conversion of dates and you can modify code according.