Build a One‑Line Excel Import/Export Utility for Java Backend Systems

When developing backend systems, frequent Excel import/export tasks can be streamlined by building a reusable utility. The goal is to provide methods that accept a List and a Class for export, and a File and Class for import, requiring only a single line of code to perform the operation.

Required Annotations

Three annotations are created to control the process:

@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface EnableExport {
    String fileName();
}

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface EnableExportField {
    int colWidth() default 100;
    String colName();
}

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ImportIndex {
    int index();
}

Import Method

The import method reads an uploaded Excel file using Apache POI, maps rows to objects based on the ImportIndex annotation, and returns a List of populated instances.

public static List<Object> parseExcelToList(File excel, Class clazz) {
    List<Object> res = new ArrayList<>();
    InputStream is = null;
    Sheet sheet = null;
    try {
        is = new FileInputStream(excel.getAbsolutePath());
        Workbook workbook = WorkbookFactory.create(is);
        sheet = workbook.getSheetAt(0);
        int i = 2; // skip header rows
        Row row = sheet.getRow(i);
        while (row != null) {
            int cellNum = row.getPhysicalNumberOfCells();
            String[] values = new String[cellNum];
            for (int j = 0; j <= cellNum; j++) {
                Cell cell = row.getCell(j);
                if (cell != null) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    values[j] = cell.getStringCellValue();
                }
            }
            Object obj = clazz.newInstance();
            for (Field f : clazz.getDeclaredFields()) {
                if (f.isAnnotationPresent(ImportIndex.class)) {
                    ImportIndex ann = f.getDeclaredAnnotation(ImportIndex.class);
                    int index = ann.index();
                    f.setAccessible(true);
                    Object val = TypeUtils.cast(values[index], f.getType(), null);
                    f.set(obj, val);
                }
            }
            res.add(obj);
            i++;
            row = sheet.getRow(i);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return res;
}

Export Process

The export method follows several steps:

Create a workbook (new Excel file).

Create a sheet.

Set row height and column widths.

Draw the title and header rows.

Write data rows.

Create dropdown validation lists.

Write the workbook to the HTTP response.

Custom helper methods are provided for styling and validation:

private static HSSFCellStyle getBasicCellStyle(HSSFWorkbook workbook) {
    HSSFCellStyle style = workbook.createCellStyle();
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    style.setWrapText(true);
    return style;
}

private static HSSFCellStyle getTitleCellStyle(HSSFWorkbook workbook) {
    HSSFCellStyle style = getBasicCellStyle(workbook);
    style.setFillForegroundColor(HSSFColor.CORNFLOWER_BLUE.index);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    return style;
}

private static void createTitle(HSSFWorkbook workbook, HSSFRow row, HSSFCell cell, HSSFSheet sheet, int allColNum, String title) {
    CellRangeAddress cra = new CellRangeAddress(0, 0, 0, allColNum);
    sheet.addMergedRegion(cra);
    RegionUtil.setBorderBottom(1, cra, sheet, workbook);
    RegionUtil.setBorderLeft(1, cra, sheet, workbook);
    RegionUtil.setBorderRight(1, cra, sheet, workbook);
    RegionUtil.setBorderTop(1, cra, sheet, workbook);
    cell = row.getCell(0);
    cell.setCellStyle(getTitleCellStyle(workbook));
    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    cell.setCellValue(title);
}

private static void createHeadRow(HSSFWorkbook workbook, HSSFRow row, HSSFCell cell, HSSFSheet sheet, List<String> colNames) {
    row = sheet.createRow(1);
    for (int i = 0; i < colNames.size(); i++) {
        cell = row.createCell(i);
        cell.setCellStyle(getTitleCellStyle(workbook));
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(colNames.get(i));
    }
}

public static void createDataValidation(Sheet sheet, Map<Integer, String[]> selectListMap) {
    if (selectListMap != null) {
        selectListMap.forEach((key, value) -> {
            if (value.length > 0) {
                CellRangeAddressList addr = new CellRangeAddressList(2, 65535, key, key);
                DataValidationHelper helper = sheet.getDataValidationHelper();
                DataValidationConstraint constraint = helper.createExplicitListConstraint(value);
                DataValidation dv = helper.createValidation(constraint, addr);
                if (dv instanceof XSSFDataValidation) {
                    dv.setSuppressDropDownArrow(true);
                    dv.setShowErrorBox(true);
                } else {
                    dv.setSuppressDropDownArrow(false);
                }
                dv.setEmptyCellAllowed(true);
                dv.setShowPromptBox(true);
                dv.createPromptBox("提示", "只能选择下拉框里面的数据");
                sheet.addValidationData(dv);
            }
        });
    }
}

Usage Example

Annotate your POJO classes with the defined annotations, then call the utility methods to export or import data with a single line of code. The article also provides screenshots of the generated Excel files and a link to the source repository:

https://github.com/xyz0101/excelutils