# Export Excel from Spring Boot (POI)
###### tags: `spring`

Step1. 宣告全域變數

public class ExcelExtension {
    XSSFWorkbook _workbook = new XSSFWorkbook();
    XSSFCellStyle _styleDouble = _workbook.createCellStyle();
    XSSFCellStyle _styleInt = _workbook.createCellStyle();
    XSSFCellStyle _styleDate = _workbook.createCellStyle();

    public ExcelExtension() {
        XSSFDataFormat dfDouble = _workbook.createDataFormat();
        _styleDouble.setDataFormat(dfDouble.getFormat("#,##0.00####"));
        _styleDouble.setAlignment(HorizontalAlignment.RIGHT);

        XSSFDataFormat dfInt = _workbook.createDataFormat();
        _styleInt.setDataFormat(dfInt.getFormat("#,##0"));
        _styleInt.setAlignment(HorizontalAlignment.RIGHT);

        XSSFDataFormat dfDate = _workbook.createDataFormat();
        _styleDate.setDataFormat(dfDate.getFormat("yyyy/MM/dd"));
    }
    // ...下方方法
}

Step2. 多筆 sheet 方法

public <T> void addSheet(List<T> list, String sheetName) throws IllegalAccessException {
    XSSFSheet sheet = _workbook.createSheet(sheetName);

    sheet.createRow(0);
    // 寫入標頭
    var item = list.get(0);
    var ind = 0;
    for (String title : getEntityColumns(item)){
        sheet.getRow(0).createCell(ind).setCellValue(title);
        ind++;
    }
    // 寫入內容
    for (int i = 0; i < list.size(); i++) {
        sheet.createRow(i+1);
        int j = 0;
        var entity = list.get(i);
        for (Field f : entity.getClass().getDeclaredFields()) {
            ExcelColumn ec = f.getAnnotation(ExcelColumn.class);
            if(ec != null && ec.ignore()) continue;
            XSSFCell cell = sheet.getRow(i+1).createCell(j);
            f.setAccessible(true);
            switch (f.getType().getSimpleName()) {
                case "Double", "Float", "long" -> {
                    cell.setCellValue(new BigDecimal("" + f.get(entity)).doubleValue());
                    cell.setCellStyle(_styleDouble);
                }
                case "Int", "Integer" -> {
                    cell.setCellValue((Integer) f.get(entity));
                    cell.setCellStyle(_styleInt);
                }
                case "Date" -> {
                    cell.setCellValue((Date) f.get(entity));
                    cell.setCellStyle(_styleDate);
                }
                default -> cell.setCellValue((String) f.get(entity));
            }
            j++;
        }
    }
}

private <T> List<String> getEntityColumns(T item){
    List<String> modelMap = new ArrayList<>();
    for (Field f : item.getClass().getDeclaredFields()) {
        // 僅接受有輸入Annotation的欄位
//            if (!f.isAnnotationPresent(SheetColumn.class)) {
//                continue;
//            }
        ExcelColumn ec = f.getAnnotation(ExcelColumn.class);
        if(ec != null && ec.ignore()) continue;
        String header = ec == null || ec.value() == null || ec.value().isEmpty() ? f.getName() : ec.value();
        modelMap.add(header);
    }
    return modelMap;
}

Step3. 轉為ByteArray給前端做下載

public ByteArrayResource getByteArrayResource() throws IOException {
    if(_workbook.getNumberOfSheets() == 0) throw new IOException("plz add sheet first...");
    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    try {
        _workbook.write(bos);
    }
    catch (IOException ex) {
        log.error("Error" + ex);
    }
    finally {
        bos.close();
    }
    byte[] bytes = bos.toByteArray();

    return new ByteArrayResource(bytes);
}

Step4. 單一 sheet 方法

public <T> ByteArrayResource Writer(List<T> list) throws IllegalAccessException, IOException {
    this.addSheet(list, "sheet1");
    return getByteArrayResource();
}

Step5. 實際使用例子
service

//  ByteArrayResource service() {
    List<CcyPairFXRate> ccyList = ccyPairFXRateRepository.findByDataDate("2023/02/23");
    List<SysUser> userList = sysUserRepository.findAll().stream().filter(x -> x.getRoleID().equals("BDPM")).toList();

    ExcelExtension excelExtension = new ExcelExtension();
    // 單一 sheet 的用法
    //        var resource = excelExtension.Writer(ccyList);

    // 多筆 sheet 的用法
    excelExtension.addSheet(ccyList, "ccyList");
    excelExtension.addSheet(userList, "userList");
    return excelExtension.getByteArrayResource();
//  }

controller

//  ResponseEntity<Resource> controller() {
    ByteArrayResource resource = service(); 
    return ResponseEntity.ok()
            .contentType(MediaType.APPLICATION_OCTET_STREAM)
            .contentLength(resource.contentLength())
            .header(HttpHeaders.CONTENT_DISPOSITION,
                    ContentDisposition.attachment()
                            .filename("whatever.xlsx")
                            .build().toString())
            .body(resource);
//  }

Ref
[Stackoverflow1](https://stackoverflow.com/a/66184374)
[Stackoverflow2](https://stackoverflow.com/a/10187954)
[Stackoverflow3](https://stackoverflow.com/a/13401094)