POI 批量导入的模板下载工具类

最新推荐文章于 2025-08-15 22:56:20 发布

原创最新推荐文章于 2025-08-15 22:56:20 发布 · 197 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java

工具类如下：

package com.ctgs.zion.platform.infrastructure.util;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.regex.Pattern;

/**
 * @author lx
 */

@Slf4j
public class POIExcelUtil {

    private POIExcelUtil() {
    }

    public static final String FONT_TYPE = "楷体";

    public static HSSFWorkbook generalTemplate(String title, String sheetName, Class<?> propertyClass, List<Object> os) throws IllegalAccessException, IOException {
        List<Field> usedFields = new ArrayList<>();
        try (HSSFWorkbook wb = new HSSFWorkbook()) {
            HSSFSheet sheet = wb.createSheet(sheetName);
            Field[] fields = propertyClass.getDeclaredFields();
            HSSFRow row = null;
            int startRow = 0;
            if (StringUtils.isNotBlank(title)) {
                row = sheet.createRow(startRow++);
                POIExcelUtil.createCell(row, 0, title,
                        POIExcelUtil.getCellStyle(wb, (short) 18, true, HorizontalAlignment.CENTER));
            }
            row = sheet.createRow(startRow++);
            int columnIndex = -1;
            for (Field field : fields) {
                if (field.isAnnotationPresent(ExcelProperty.class)) {
                    columnIndex++;
                    ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
                    String[] value = annotation.value();
                    String rowValue = value[value.length - 1];
                    usedFields.add(field);
                    CellStyle cellStyle = POIExcelUtil.getCellStyle(wb, (short) 14, true, HorizontalAlignment.LEFT);
                    if (rowValue.contains("*")) {
                        POIExcelUtil.setColor(cellStyle, wb, Font.COLOR_RED);
                    }
                    POIExcelUtil.createCell(row, columnIndex, rowValue, cellStyle);
                    sheet.setColumnWidth(columnIndex, rowValue.length() * 888);
                }
            }
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnIndex));
            //添加数据
            for (Object o : os) {
                row = sheet.createRow(startRow++);
                for (int i = 0; i < usedFields.size(); i++) {
                    HSSFCell cell = row.createCell(i);
                    Field field = usedFields.get(i);
                    field.setAccessible(true);
                    String value = String.valueOf(field.get(o));
                    String pattern = POIExcelUtil.getDatePattern(value);
                    if (Objects.nonNull(pattern)) {
                        POIExcelUtil.setDataFormat(wb, cell, pattern);
                    }
                    cell.setCellValue(value);
                }
            }
            return wb;

        } catch (IllegalAccessException | IOException e) {
            log.error("生成模板出错:", e);
            throw e;
        }
    }

    public static void setDataFormat(Workbook wb, Cell cell, String pattern) {
        CellStyle cellStyle = wb.createCellStyle();
        DataFormat dataFormat = wb.createDataFormat();
        short format = dataFormat.getFormat(pattern);
        cellStyle.setDataFormat(format);
        cell.setCellStyle(cellStyle);
    }

    public static void createCell(Row row, int index, String value, CellStyle style) {
        Cell cell = row.createCell(index);
        cell.setCellValue(value);
        cell.setCellStyle(style);
    }

    public static CellStyle getCellStyle(Workbook wb, short fontSize, boolean isBlond, HorizontalAlignment alignment) {
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setFont(getFont(wb, fontSize, isBlond));
        cellStyle.setAlignment(alignment);
        return cellStyle;
    }

    public static void setColor(CellStyle style, Workbook wb, short color) {
        Font fontAt = wb.getFontAt(style.getFontIndex());
        fontAt.setColor(color);
    }

    private static Font getFont(Workbook wb, short fontSize, boolean isBlond) {
        Font font = wb.createFont();
        font.setFontName(FONT_TYPE);
        font.setBold(isBlond);
        font.setFontHeightInPoints(fontSize);
        return font;
    }

    private static String getDatePattern(String s) {
        if (Pattern.matches("^[1-9][0-9]{3}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}$", s)) {
            return "yyyy-MM-dd HH:mm";
        }
        return null;
    }

}

controller :

/**
     * 车主信息导入模板下载
     * @param response
     * @throws IOException
     */
    @GetMapping("/import-template")
    @ApiOperation(value = "车主信息导入模板下载",notes = "车主信息导入模板下载")
    public void importTemplate(HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding(StandardCharsets.UTF_8.name());
        String fileName = URLEncoder.encode("车主信息导入模板下载" + System.currentTimeMillis(), StandardCharsets.UTF_8.name());
        response.setHeader("Content-disposition", "attachment;filename="
                + new String((fileName + ".xls").getBytes(), StandardCharsets.ISO_8859_1));
        HSSFWorkbook sheets = bdOwnerInfoService.importTemplate();
        sheets.write(response.getOutputStream());
        // 不关闭流
    }

service：

/**
     * 下载模板
     *
     * @return
     */
    @Override
    public HSSFWorkbook importTemplate() {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("车主基础信息导入模板");
        Class<BdOwnerInfoImportDTO> businessClazz = BdOwnerInfoImportDTO.class;
        Field[] fields = businessClazz.getDeclaredFields();
        HSSFRow row = sheet.createRow(1);
        int columnIndex = -1;
        for (Field field : fields) {
            if (field.isAnnotationPresent(ExcelProperty.class)) {
                columnIndex++;
                ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
                String[] value = annotation.value();
                String rowValue = value[value.length - 1];
                CellStyle cellStyle = POIExcelUtil.getCellStyle(wb, (short) 14, true, HorizontalAlignment.LEFT);
                if (rowValue.contains("*")) {
                    POIExcelUtil.setColor(cellStyle, wb, Font.COLOR_RED);
                }
                POIExcelUtil.createCell(row, columnIndex, rowValue, cellStyle);
                sheet.setColumnWidth(columnIndex, rowValue.length() * 800);
            }
        }
        row = sheet.createRow(0);
        POIExcelUtil.createCell(row, 0, "车主基础信息导入（*为必填项）",
                POIExcelUtil.getCellStyle(wb, (short) 18, true, HorizontalAlignment.CENTER));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnIndex));
        return wb;
    }

BdOwnerInfoImportDTO：

package com.ctgs.zion.platform.domain.owner.excel;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

import javax.validation.constraints.NotBlank;


@Data
public class BdOwnerInfoImportDTO {

    /**
     * 车主姓名
     */
    @NotBlank(message = "车主姓名不能为空")
    @ExcelProperty({"*车主姓名"})
    private String name;

    /**
     * 车主联系电话
     */
    @NotBlank(message = "车主联系电话不能为空")
    @ExcelProperty({"*车主联系电话"})
    private String phone;

    /**
     * 车主身份证号
     */
    @NotBlank(message = "车主身份证号不能为空")
    @ExcelProperty({"*车主身份证号"})
    private String idNumber;
}

导出效果：