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!
source to share
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).
source to share