SpringBoot導(dǎo)出Excel表格到指定路徑的代碼詳解

更新時(shí)間：2024年07月17日 09:36:31 作者：龍域、白澤

Spring Boot導(dǎo)出Excel通常涉及到使用第三方庫如Apache POI或者XlsxWriter等,它們能幫助你在Spring應(yīng)用中生成并下載Excel文件,那么SpringBoot如何導(dǎo)出Excel表格到指定路徑,本文將給大家詳細(xì)的介紹一下

導(dǎo)入依賴

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>

自定義 @Excel 注解

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
?
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Excel {
    /**
     * 導(dǎo)出到Excel中的名字
     */
    String name() default "";
?
    /**
     * 日期格式, 如: yyyy-MM-dd
     */
    String dateFormat() default "";
?
    /**
     * 字典的key值
     */
    String dictKey() default "";
?
    /**
     * 讀取內(nèi)容轉(zhuǎn)表達(dá)式 (如: 0=男,1=女,2=未知)
     */
    String dictExp() default "";
}

Excel工具類

/**
 * Excel的工具類
 */
public class ExcelUtil<T> {
?
    /**
     * 工作薄
     */
    private Workbook wb;
?
    /**
     * 工作表
     */
    private Sheet sheet;
?
    /**
     * 需要導(dǎo)出的數(shù)據(jù)
     */
    private List<T> exportList;
?
    /**
     * 對(duì)象的class對(duì)象
     */
    private Class<T> clazz;
?
    /**
     * 被選中需要導(dǎo)出的字段名稱
     */
    private Map<String, Object> checkedFieldsName;
?
    /**
     * 被選中需要導(dǎo)出的字段對(duì)象
     */
    private List<Field> checkedFields;
?
    /**
     * 包含需要字典轉(zhuǎn)換的字段對(duì)象
     */
    private List<Field> fieldsContainDict;
?
    /**
     * 對(duì)象中的字典值
     */
    private Map<String, Map<String, String>> dicts;
?
    private ExcelUtil(){
    }
?
    public ExcelUtil(Class<T> clazz){
        this.clazz = clazz;
    }
?
    /**
     *
     * @param list
     * @param sheetName
     * @param fieldsName
     */
    public void exportExcel(List<T> list, Map<String, Object> fieldsName, String sheetName){
        // 初始化數(shù)據(jù)
        init(list, sheetName, fieldsName);
?
        // 轉(zhuǎn)換字典值
        try {
            convertDict();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
?
        // sheet第一行加入名稱數(shù)據(jù)
        createTopRow();
?
        // sheet其他行，添加目標(biāo)數(shù)據(jù)
        try {
            createOtherRow();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
?
        // 導(dǎo)出wb
        try(OutputStream outFile = new FileOutputStream(generateFileName())){
            wb.write(outFile);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
?
    /**
     * 添加導(dǎo)出數(shù)據(jù)
     */
    private void createOtherRow() throws IllegalAccessException {
        for (int rowNum = 1; rowNum <= exportList.size(); rowNum++) {
            Row row = sheet.createRow(rowNum);
            T t = exportList.get(rowNum - 1);
?
            for (int colNum = 0; colNum < checkedFields.size(); colNum++) {
                Cell cell = row.createCell(colNum);
                Field field = checkedFields.get(colNum);
                field.setAccessible(true);
?
                // 單元格設(shè)置值
                addCell(cell, field, t);
            }
        }
    }
?
    /**
     * 單元格中添加數(shù)據(jù)
     *
     * @param cell  單元格
     * @param field 字段
     * @param t     list中的一條數(shù)據(jù)
     */
    private void addCell(Cell cell, Field field, T t) throws IllegalAccessException {
        Class<?> fieldType = field.getType();
        if (String.class == fieldType) {
            cell.setCellValue((String) field.get(t));
        } else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {
            cell.setCellValue((Integer) field.get(t));
        } else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
            cell.setCellValue((Long) field.get(t));
        } else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) {
            cell.setCellValue((Double) field.get(t));
        } else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {
            cell.setCellValue((Float) field.get(t));
        } else if (Date.class == fieldType) {
            String dateFormat = field.getAnnotation(Excel.class).dateFormat();
            cell.setCellValue(dateFormat((Date) field.get(t), dateFormat));
        }
    }
?
    /**
     * 時(shí)間格式轉(zhuǎn)換
     * @param date 日期
     * @param dateFormat 日期格式
     * @return
     */
    private String dateFormat(Date date, String dateFormat) {
        if (dateFormat == null || "".equals(dateFormat)) {
            dateFormat = "yyyy-MM-dd HH:mm:ss";
        }
?
        SimpleDateFormat df = new SimpleDateFormat(dateFormat);
        return df.format(date);
    }
?
    /**
     * sheet第一行加入名稱數(shù)據(jù)
     */
    private void createTopRow() {
        Row row = sheet.createRow(0);
        Map<String, CellStyle> styles = createStyles(wb);
?
        for (int index = 0; index < checkedFields.size(); index++) {
            Cell cell = row.createCell(index);
            cell.setCellValue(checkedFields.get(index).getAnnotation(Excel.class).name());
            System.out.println(styles.get("header"));
            cell.setCellStyle(styles.get("header"));
        }
    }
?
    /**
     * 轉(zhuǎn)換字典值
     *     將數(shù)據(jù)中字典值轉(zhuǎn)化為對(duì)應(yīng)的值(注:字典值應(yīng)為String格式)
     */
    private void convertDict() throws IllegalAccessException {
        for (Field field : fieldsContainDict) {
            Excel annotation = field.getAnnotation(Excel.class);
            String dictKey = annotation.dictKey();
            field.setAccessible(true);
            for (T t : exportList) {
                // 獲取字段值
                String o = (String) field.get(t);
                field.set(t, dicts.get(dictKey).get(o));
            }
        }
    }
?
    /**
     * 將數(shù)據(jù)導(dǎo)出Excel
     *
     * @param list 需要導(dǎo)出的數(shù)據(jù)
     * @param sheetName 工作表名稱
     */
    public void exportExcel(List<T> list, String sheetName){
        exportExcel(list, null, sheetName);
    }
?
    /**
     * 將數(shù)據(jù)導(dǎo)出Excel
     *
     * @param list 需要導(dǎo)出的數(shù)據(jù)
     */
    public void exportExcel(List<T> list) {
        exportExcel(list, null, "sheet");
    }
?
    /**
     * 初始化
     */
    public void init(List<T> list ,String sheetName,  Map<String, Object> fieldsName){
        this.checkedFieldsName = fieldsName;
?
        this.exportList = list;
?
        // 初始化導(dǎo)出數(shù)據(jù)
        initExportList();
?
        // 初始化工作薄
        initWorkbook();
?
        // 初始化工作表
        initSheet(sheetName);
?
        // 初始化checkedFields, fieldsContainDict
        initFields();
?
        // 根據(jù)注解生成生成字典
        generateObjDict();
    }
?
    /**
     * 初始化導(dǎo)出數(shù)據(jù)
     */
    private void initExportList(){
        // 防止導(dǎo)出過程中出現(xiàn)空指針
        if(Objects.isNull(this.exportList)) {
            this.exportList = new ArrayList<>();
        }
    }
?
    /**
     * 初始化工作簿
     */
    private void initWorkbook(){
        this.wb = new SXSSFWorkbook();
    }
?
    /**
     * 初始化工作表
     */
    private void initSheet(String sheetName){
        this.sheet = wb.createSheet(sheetName);
    }
?
    /**
     * 初始化checkedFields, fieldsContainDict
     *     fieldsContainDict含有字典表達(dá)式的字段
     *     checkedFields用戶選中的字段
     *        1.如果checkedFieldsName沒有定義(未自定義導(dǎo)出字段),所有字段全部導(dǎo)出
     *        2.如果checkedFieldsName進(jìn)行了定義,根據(jù)定義字段進(jìn)行導(dǎo)出
     */
    private void initFields(){
        // 獲取對(duì)象所有字段對(duì)象
        Field[] fields = clazz.getDeclaredFields();
?
        // 過濾出checkedFields
        this.checkedFields = Arrays.
                asList(fields).
                stream().
                filter(item -> {
                    if(!Objects.isNull(this.checkedFieldsName)) {
                        if (item.isAnnotationPresent(Excel.class)) {
                            return checkedFieldsName.containsKey(item.getName());
                        }
                    } else {
                        return item.isAnnotationPresent(Excel.class);
                    }
                    return false;
                })
                .collect(Collectors.toList());
?
        // 過濾出fieldsContainDict
        for (Field declaredField : clazz.getDeclaredFields()) {
            if(declaredField.getAnnotation(Excel.class) != null) {
                System.out.println(declaredField.getAnnotation(Excel.class).dictExp());
            }
        }
        this.fieldsContainDict = Arrays
                .asList(clazz.getDeclaredFields())
                .stream()
                .filter(item -> !"".equals(item.getAnnotation(Excel.class) != null? item.getAnnotation(Excel.class).dictExp() : ""))
                .collect(Collectors.toList());
    }
?
    /**
     * 通過掃描字段注解生成字典數(shù)據(jù)
     */
    private void generateObjDict(){
        if(fieldsContainDict.size() == 0) {
            return;
        }
?
        if(dicts == null) {
            dicts = new HashMap<>(); //  Map<String, List<Map<String, String>>>
        }
?
        for (Field field : fieldsContainDict) {
            String dictKey = field.getAnnotation(Excel.class).dictKey();
            String exps = field.getAnnotation(Excel.class).dictExp();
            String[] exp = exps.split(",");
?
            Map<String, String> keyV = new HashMap<>();
?
            dicts.put(dictKey, keyV);
?
            for (String s : exp) {
                String[] out = s.split("=");
                keyV.put(out[0], out[1]);
            }
?
            System.out.println("字典值:"+ dicts);
        }
    }
?
    /**
     * 創(chuàng)建表格樣式
     *
     * @param wb 工作薄對(duì)象
     * @return 樣式列表
     */
    private Map<String, CellStyle> createStyles(Workbook wb)
    {
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        // 數(shù)據(jù)格式
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        Font dataFont = wb.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short) 10);
        style.setFont(dataFont);
        styles.put("data", style);
?
        // 表頭格式
        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font headerFont = wb.createFont();
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 10);
        headerFont.setBold(true);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(headerFont);
        styles.put("header", style);
?
        return styles;
    }
?
    /**
     * 生成隨機(jī)名稱,防止文件復(fù)寫
     * @return 導(dǎo)出路徑
     */
    private String generateFileName(){
        return "D:\\" + UUID.randomUUID().toString().replace("-", "") + ".xlsx";
    }
}

在實(shí)體類上標(biāo)注自定義注解

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
?
    @Excel(name = "姓名")
    private String name;
?
    @Excel(name = "年齡")
    private Integer age;
?
    @Excel(name = "出生日期", dateFormat = "yyyy-MM-dd")
    private Date birthday;
?
    @Excel(name = "性別", dictKey = "sex", dictExp = "1=男,2=女")
    // 注意這里是用String類型【【坑】】
    private String sex;
}

生成目標(biāo)Excel表格

@Log(title = "用戶管理", businessType = BusinessType.EXPORT)
@RequiresPermissions("system:user:export")
@PostMapping("/export")
@ResponseBody
public AjaxResult export(SysUser user) {
    List<SysUser> list = userService.selectUserList(user);
    ExcelUtil<SysUser> util = new ExcelUtil<SysUser>(SysUser.class);
    return util.exportExcel(list, "用戶數(shù)據(jù)");
}

測(cè)試

public static void main(String[] args) {
    ArrayList<Student> data = new ArrayList<>();
    Student student = new Student();
    student.setName("tom");
    student.setAge(19);
    // 在表格中1會(huì)變成男
    student.setSex("1");
    student.setBirthday(new Date());
    data.add(student);
?
    ExcelUtil<Student> util = new ExcelUtil<>(Student.class);
    util.exportExcel(data, "人員信息表");
}

若不自定義導(dǎo)出的字段,，工具將會(huì)把所有帶有Excel注解的字段進(jìn)行導(dǎo)出，如上

指定導(dǎo)出字段

public static void main(String[] args) {
    ArrayList<Student> data = new ArrayList<>();
    Student student = new Student();
    student.setName("tom");
    student.setAge(19);
    student.setSex("1");
    student.setBirthday(new Date());
    data.add(student);
?
    // 需要導(dǎo)出字段的名稱，放入map的key中即可(這里只導(dǎo)出姓名和性別)
    Map<String, Object> fieldsName = new HashMap<>();
    fieldsName.put("name", null);
    fieldsName.put("sex", null);
?
    ExcelUtil<Student> util = new ExcelUtil<>(Student.class);
    // 將fieldsName放入方法中
    util.exportExcel(data, fieldsName,"人員信息表");
}

到此這篇關(guān)于SpringBoot導(dǎo)出Excel表格到指定路徑的代碼詳解的文章就介紹到這了,更多相關(guān)SpringBoot導(dǎo)出Excel指定路徑內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: