Create custom Excel sheet

Question

I am trying to generate an Excel sheet populating the data dynamically using Apache POI. I have to populate a list i.

Each object of i contains four string variables and one list j.
Each object of list j contains three string variables.
j.size() is dynamic in each object of i.

The data should appear as:

Sample file screen shot

Answer 1

package a;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

import objs.I;
import objs.J;

public class Main {
    static List<I> listI = new ArrayList<I>();

    public static void main(String[] args) throws IOException {
        // TODO Auto-generated method stub
        populateLists();
        Workbook wb = new HSSFWorkbook();
        CreationHelper createHelper = wb.getCreationHelper();
        Sheet sheet = wb.createSheet("new sheet");
        int absRowNo = 0;
        for(int count=0;count<listI.size();count++)
        {
        I i = listI.get(count); 

        int size = i.getList().size();
        int cellNo = 1;

        Row row1 = sheet.createRow(absRowNo);
        Cell cell1 = row1.createCell(cellNo);
        cell1.setCellValue(i.getA());
        sheet.addMergedRegion(new CellRangeAddress(absRowNo, // first row (0-based)
                absRowNo + (size - 1), // last row (0-based)
                cellNo, // first column (0-based)
                cellNo // last column (0-based)
        ));
        cellNo++;
        Cell cell2 = row1.createCell(cellNo);
        cell2.setCellValue(i.getB());

        sheet.addMergedRegion(new CellRangeAddress(absRowNo, // first row (0-based)
                absRowNo + (size - 1), // last row (0-based)
                cellNo, // first column (0-based)
                cellNo // last column (0-based)
        ));
        cellNo++;
        Cell cell3 = row1.createCell(cellNo);
        cell3.setCellValue(i.getC());

        sheet.addMergedRegion(new CellRangeAddress(absRowNo, // first row (0-based)
                absRowNo + (size - 1), // last row (0-based)
                cellNo, // first column (0-based)
                cellNo // last column (0-based)
        ));
        cellNo++;
        Cell cell4 = row1.createCell(cellNo);
        cell4.setCellValue(i.getC());

        sheet.addMergedRegion(new CellRangeAddress(absRowNo, // first row (0-based)
                absRowNo + (size - 1), // last row (0-based)
                cellNo, // first column (0-based)
                cellNo // last column (0-based)
        ));
        cellNo++;
        int jx = cellNo;
        List<J> listJ = i.getList();
        int z = absRowNo;
        for (int y = 0; y < listJ.size(); y++) {
            J j = (J) listJ.get(y);
            Row rowJ;
            if (y != 0)
                rowJ = sheet.createRow(z);
            else
                rowJ = row1;
            Cell c1 = rowJ.createCell(jx);
            c1.setCellValue(j.getA());
            Cell c2 = rowJ.createCell(jx + 1);
            c2.setCellValue(j.getB());
            Cell c3 = rowJ.createCell(jx + 2);
            c3.setCellValue(j.getC());
            z++;
        }
        absRowNo = absRowNo+i.getList().size();
        }
        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream("workbook.xls");
        wb.write(fileOut);
        fileOut.close();
        System.out.println("done");

    }

    private static void populateLists() {
        // TODO Auto-generated method stub
        I i1 = new I();
        i1.setA("i1a");
        i1.setB("i1b");
        i1.setC("i1c");
        i1.setD("i1d");
        J j1 = new J();
        j1.setA("j1a");
        j1.setB("j1b");
        j1.setC("j1c");

        J j2 = new J();
        j2.setA("j2a");
        j2.setB("j2b");
        j2.setC("j2c");

        J j3 = new J();
        j3.setA("j3a");
        j3.setB("j3b");
        j3.setC("j3c");
        List<J> listJ = new ArrayList<J>();
        listJ.add(j1);
        listJ.add(j2);
        listJ.add(j3);

        i1.setList(listJ);

        I i2 = new I();
        i2.setA("i2a");
        i2.setB("i2b");
        i2.setC("i2c");
        i2.setD("i2d");

        J j5 = new J();
        j5.setA("j5a");
        j5.setB("j5b");
        j5.setC("j5c");

        J j4 = new J();
        j4.setA("j4a");
        j4.setB("j4b");
        j4.setC("j4c");

        List<J> listJ2 = new ArrayList<J>();
        listJ2.add(j4);
        listJ2.add(j5);
        i2.setList(listJ2);

        listI.add(i1);
        listI.add(i2);
    }

}

Class I:
package objs;

import java.util.List;

public class I {
String a;
String b;
String c;
String d;
List<J> list;
public String getA() {
    return a;
}
public void setA(String a) {
    this.a = a;
}
public String getB() {
    return b;
}
public void setB(String b) {
    this.b = b;
}
public String getC() {
    return c;
}
public void setC(String c) {
    this.c = c;
}
public String getD() {
    return d;
}
public void setD(String d) {
    this.d = d;
}
public List<J> getList() {
    return list;
}
public void setList(List<J> list) {
    this.list = list;
}


}

Class J:
package objs;

public class J {
    String a;
    String b;
    String c;
    public String getA() {
        return a;
    }
    public void setA(String a) {
        this.a = a;
    }
    public String getB() {
        return b;
    }
    public void setB(String b) {
        this.b = b;
    }
    public String getC() {
        return c;
    }
    public void setC(String c) {
        this.c = c;
    }
}