Loop for class members and writing to Excel Apache POI

I have a list of arrays of custom objects. I am trying to loop over these objects to write my output to an excel file.

In my code below, in the first loop, I am setting the title bar in the excel file by iterating over the class member variables. In the second loop, I write the values ​​of the object.

My code:

class ChecklistOutput {
    //Instantiating class data members
String a, b, c;

public ChecklistOutput() { 
     a = ""; b = ""; c = ""; }
}

private static ArrayList<ChecklistOutput> MasterOutput = new ArrayList<ChecklistOutput>();

private static void writeToMasterExcel() {
    ChecklistOutput obj1 = new ChecklistOutput();
    obj1.a = "AA"; obj1.b = "BB"; obj1.c = "CC"; 
    ChecklistOutput obj1 = new ChecklistOutput();
    obj2.a = "AA"; obj2.b = "BB"; obj2.c = "CC";
    ChecklistOutput obj1 = new ChecklistOutput();
    obj3.a = "AA"; obj3.b = "BB"; obj3.c = "CC"; 
    ChecklistOutput obj1 = new ChecklistOutput();
    obj4.a = "AA"; obj4.b = "BB"; obj4.c = "CC"; 

    MasterOutput.add(obj1);
    MasterOutput.add(obj2);
    MasterOutput.add(obj3);
    MasterOutput.add(obj4);
    System.out.println(MasterOutput.size());

    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = null;
    HSSFRow row = null;
    HSSFCell cell = null;
    int rownum = 0, cellnum = 0;
    sheet = workbook.createSheet("Master Spreadsheet");
    row = sheet.createRow(rownum);
    System.out.println("rownum " + rownum);
    Class<?> c = new ChecklistOutput().getClass();
    Field[] fields = c.getDeclaredFields();

    // First loop
    for (Field field : fields) {
        cell = row.createCell(cellnum);
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue(field.getName());
        cellnum += 1;
    }
    System.out.println(MasterOutput.size());
    // Second loop
    for (ChecklistOutput x : MasterOutput) {
        // This prints 4 times meaning that there are 4 values in
        // MasterOutput
        System.out.println("Hell");
        rownum += 1;
        cellnum = 0;
        row = sheet.createRow(rownum);
        for (Field field : fields) {
            cell = row.createCell(cellnum);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            try {
                // I can see values here
                System.out.println(field.get(x).toString());
                cell.setCellValue(field.get(x).toString());
            } catch (IllegalArgumentException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
            cellnum += 1;
        }
    }
    BufferedOutputStream bos;
    try {
        bos = new BufferedOutputStream(new FileOutputStream(
                "C:\\Users\\ABC\\Documents\\Checklist-Output.xls",
                true));
        workbook.write(bos);
        bos.close();
        workbook.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

      

However, I am only getting header values ​​in my excel file.

Output:

| a | b | c |

      

Can anyone help me? Thank!

+3


source to share


1 answer


Try the following:

for (int col = 0; col < fields.length; col++) {
    Field field = fields[col];
    HSSFRow header = sheet.getRow(rownum);
    if (header == null) {
        header = sheet.createRow(rownum);
    }
    HSSFCell cell = header.createCell(col);
    cell.setCellType(Cell.CELL_TYPE_STRING);
    cell.setCellValue(field.getName());
}
for (ChecklistOutput x : MasterOutput) {
    rownum += 1;
    HSSFRow rowData = sheet.getRow(rownum);
    if (rowData == null) {
        rowData = sheet.createRow(rownum);
    }

    for (int col = 0; col < fields.length; col++) {
        Field field = fields[col];
        HSSFCell cellData = rowData.createCell(col);
        cellData.setCellType(Cell.CELL_TYPE_STRING);
        try {
            // I can see values here
            System.out.println(field.get(x).toString());
            cellData.setCellValue(field.get(x).toString());
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
    }
}

      



Also I recommend using POJOs instead of ChecklistOutput (private fields with getters and setters). I did the same as you. If you need field values ​​from pojo, you can use reflection to call getters. You can see my question . It asks for something else, but gives a working example for getting values ​​with reflection (I also used it for POI).

0


source







All Articles