Java Excel Import/Export Utility with Custom Annotations

When developing backend systems, handling Excel import and export is a frequent requirement. This guide shows how to build a generic Java utility that leverages custom annotations and Apache POI to simplify these tasks.

Required annotations

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

These annotations mark which classes and fields participate in export, allow column width and name configuration, and specify the column index for import.

Import method

/**
 * 将Excel转换为对象集合
 * @param excel Excel 文件
 * @param clazz pojo类型
 */
public static List
parseExcelToList(File excel, Class clazz) {
    List
res = new ArrayList<>();
    InputStream is = null;
    Sheet sheet = null;
    try {
        is = new FileInputStream(excel.getAbsolutePath());
        if (is != null) {
            Workbook workbook = WorkbookFactory.create(is);
            sheet = workbook.getSheetAt(0);
            if (sheet != null) {
                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);
                            String value = cell.getStringCellValue();
                            values[j] = value;
                        }
                    }
                    Field[] fields = clazz.getDeclaredFields();
                    Object obj = clazz.newInstance();
                    for (Field f : fields) {
                        if (f.isAnnotationPresent(ImportIndex.class)) {
                            ImportIndex annotation = f.getDeclaredAnnotation(ImportIndex.class);
                            int index = annotation.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;
}

The method reads the uploaded Excel file, maps each row to an instance of the supplied class based on @ImportIndex , and returns a list of populated objects.

Export method – main steps

Create a workbook (new Excel file).

Create a sheet.

Set row height and column widths.

Write the title row and header row.

Write data rows.

Add dropdown validation where needed.

Write the workbook to the HttpServletResponse output stream.

Helper methods simplify the above steps:

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((short) 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);
    row = sheet.getRow(0);
    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
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
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 validation = helper.createValidation(constraint, addr);
                if (validation instanceof XSSFDataValidation) {
                    validation.setSuppressDropDownArrow(true);
                    validation.setShowErrorBox(true);
                } else {
                    validation.setSuppressDropDownArrow(false);
                }
                validation.setEmptyCellAllowed(true);
                validation.setShowPromptBox(true);
                validation.createPromptBox("提示", "只能选择下拉框里面的数据");
                sheet.addValidationData(validation);
            }
        });
    }
}

Using these utilities, developers can export a list of objects to Excel with a single line of code and import an uploaded Excel file back into a list of objects similarly.

Source code repository: https://github.com/xyz0101/excelutils