CODEWITHSUNDEEP
javaspring-bootapache-poi
Coder
Coder

Reputation: 235

Apache POI Java - Write to excel and dynamically update cells

My java spring boot app needs to create a new excel file based on the contents of my DB. My current solution places all the data from my DB and inserts it in my excel sheet, but I want to improve it by not stating what the cell values are. For example, although it works, my solution has 34 fields so I am stating the userRow.createCell line 34 times for each field which is repetitive. Ideally I want to say create the cell(n) and take all the values from each row in the DB. How can this be done? Another for loop within this for loop? Every example I looked at online seems to specifically state what the cell value is. 

List<CaseData> cases = (List<CaseData>) model.get("cases");
    Sheet sheet = workbook.createSheet("PIE Cases");
int rowCount = 1;
    for (CaseData pieCase : cases) {
        Row userRow = sheet.createRow(rowCount++);
        userRow.createCell(0).setCellValue(pieCase.getCaseId());
        userRow.createCell(1).setCellValue(pieCase.getAcknowledgementReceivedDate());
    }

Upvotes: 0

Views: 2507

Answers (2)

Cempoalx&#243;chitl
Cempoalx&#243;chitl

Reputation: 176

Use the Reflection API

Example:

try {
    Class caseDataObj = CaseData.class;
    Method [] methods = caseDataObj.getDeclaredMethods();
    Sheet sheet = workbook.createSheet("PIE Cases");
    int rowCount = 1;
    for(CaseData cd : cases) {
        int cellIndex = 0;
        Row userRow = sheet.createRow(rowCount++);
        for (Method method : methods) {
            String methodName = method.getName();
            if(methodName.startsWith("get")) {
                // Assuming all getters return String
                userRow.createCell(cellIndex++).setCellValue((String) method.invoke(cd));
            }
        }
    }
} catch (Exception e) {
    e.printStackTrace();
}

Upvotes: 3

Joe
Joe

Reputation: 1342

There are probably many ways to do this, You can try something like this, this is how I usually go about it for things like what you are doing.

public enum DATA {
     CASE_ID(0), 
     ACK_RECIEVED(1), 
     ETC(2); 
     //ETC(3) and so on

     public int index;

     DATA(int index) { 
         this.index = index; 
     }

     public Object parse(CaseData data) throws Exception {
          switch (this) {
              case CASE_ID:
                 return data.getCaseId();
              case ACK_RECIEVED:
                 return data.getAcknowledgementReceivedDate();
              case ETC:
                 return "etc...";
              default: return null;
         }
     }
}

Then, the implementation is:

List<CaseData> cases = (List<CaseData>) model.get("cases");
Sheet sheet = workbook.createSheet("PIE Cases");
int rowCount = 1;
for (CaseData pieCase : cases) {
    Row userRow = sheet.createRow(rowCount++);
    for (DATA DAT : DATA.values()) {
        userRow.createCell(DAT.index).setCellValue(DAT.parse(pieCase));
    }
}

Upvotes: 1

Related Questions