Apache POI Java - Write to excel and dynamically update cells

Question

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());
    }

"Another for loop within this for loop?": Yes. See https://stackoverflow.com/questions/56556275/how-to-generate-aggregations-like-sum-average-at-row-labels-instead-of-column-l/56568854#56568854 for an example where a `Excel` sheet is filled from an `Object[][] data`. — Axel Richter, Nov 21 '19 at 14:43
Repetitive to write once, but is it easy to read and maintain in the future? You can just copy and paste `userRow.createCell().setCellValue(pieCase.);` 34 times and fill in the number and autocomplete the getter. Probably faster even than posting this question ;-) — Thomas Timbul, Nov 21 '19 at 15:50

Cempoalxóchitl · Answer 1 · 2019-11-21T23:44:23.257

3

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();
}

edited Nov 21 '19 at 23:44

answered Nov 21 '19 at 16:32

Cempoalxóchitl

176
3
15

Nice! Never considered Reflection. Where is userRow getting initialized here? – Coder Nov 21 '19 at 19:32
1

@Coder I've updated the code to include both the row and sheet. Hope it helps. – Cempoalxóchitl Nov 21 '19 at 23:37
thanks for this. For setting the cellvalue as an object, I see CellType has now deprecated. Any ideas how I would do this? I tried casting and toString but didnt work for setting the cell value @Cempoalxóchitl – Coder Nov 22 '19 at 11:25
In that case, you can create a getter method and return the data you need as a String or make use of the [toString()](https://stackoverflow.com/a/3615881/5522536) method. The main idea is, extract the data you need and transform it into an acceptable class. – Cempoalxóchitl Nov 22 '19 at 16:49

score 1 · Answer 2 · answered Nov 21 '19 at 15:36

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));
    }
}

Thanks for this @Joe, this would work but it means specifying all the fields. looking for a dynamic way of doing this to save me updating classes in case more fields come in the future — Coder, Nov 22 '19 at 11:08
All you would have to do is add an enum value for more fields as they come — Joe, Nov 22 '19 at 14:15
Also the reflection example above is good as well :) I upvoted it — Joe, Nov 22 '19 at 14:15

Apache POI Java - Write to excel and dynamically update cells

2 Answers2